ANTS Profiler 2.0
 
Welcome Guest      
Oracle Data Provider for .NET
Author Date Of Submission User Level
Rama Mohan G. 02/20/2004 Intermediate


ODP.NET Types Overview

ODP.NET types represent Oracle native types as a structure or as a class. For example, an OracleDecimal holds up to 38 precisions while a .NET Decimal holds only up to 28 precisions.

Table 3.2 lists all the Oracle native types supported by ODP.NET and their corresponding ODP.NET type. The third column lists the .NET Framework datatype that corresponds to the Value property of each ODP.NET Type.

Table 3-2 Oracle Native Types Supported by ODP.NET  

Oracle Native Type

ODP.NET Type

.NET Framework Datatypes

BFILE

OracleBFile class

System.Byte[]

BLOB

OracleBlob class

System.Byte[]

CHAR

OracleString structure

System.String

CLOB

OracleClob class

System.String

DATE

OracleDate structure

System.DateTime

INTERVAL DAY TO SECOND

OracleIntervalDS structure

System.TimeSpan

INTERVAL YEAR TO MONTH

OracleIntervalYM structure

System.Int64

LONG

OracleString structure

System.String

LONG RAW

OracleBinary structure

System.Byte[]

NCLOB

OracleClob class

System.String

NCHAR

OracleString structure

System.String

NUMBER

OracleDecimal structure

System.Decimal

NVARCHAR2

OracleString structure

System.String

RAW

OracleBinary structure

System.Byte[]

REF CURSOR

OracleRefCursor class

Not Applicable

TIMESTAMP

OracleTimeStamp structure

System.DateTime

TIMESTAMP WITH LOCAL TIME ZONE

OracleTimeStampLTZ structure

System.DateTime

TIMESTAMP WITH TIME ZONE

OracleTimeStampTZ structure

System.DateTime

UROWID

OracleString structure

System.String

VARCHAR2

OracleString structure

System.String

XMLType

OracleXmlType class

System.String


Obtaining Data From an OracleDataReader
The ExecuteReader method of the OracleCommand object returns an OracleDataReader object, which is a read-only, forward-only result set.
Typed OracleDataReader Accessors

The OracleDataReader provides two types of typed accessors:

.NET Type Accessors

Table 3.3 lists all the Oracle native database types that ODP.NET supports and the corresponding .NET Type that best represents the Oracle native type. The third column indicates the valid typed accessor that can be invoked for an Oracle native type to be obtained as a .NET type. If an invalid typed accessor is used for a column, an InvalidCastException is thrown. Oracle native datatypes depend on the version of the database; therefore, some datatypes are not available in earlier versions of Oracle.
 

Table 3-3 .NET Type Accessors  

Oracle Native Datatype

.NET Type

Typed Accessor

BFILE

System.Byte[ ]

GetBytes

BLOB

System.Byte[ ]

GetBytes

CHAR

System.String

GetString

GetChars

CLOB

System.String

GetString

GetChars

DATE

System.DateTime

GetDateTime

INTERVAL (DS)

System.Interval

GetTimeSpan

INTERVAL (YM)

System.Interval

GetTimeSpan

LONG

System.String

GetString

GetChars

LONG RAW

System.Byte[ ]

GetBytes

NCHAR

System.String

GetString

GetChars

NCLOB

System.String

GetString

GetChars

NUMBER

System.Decimal

GetDecimal

NVARCHAR2

System.String

GetString

GetChars

RAW

System.Byte[ ]

GetBytes

ROWID

System.String

GetString

GetChars

TIMESTAMP

System.TimeStamp

GetTimeStamp

TIMESTAMP WITH LOCAL TIME ZONE

System.TimeStamp

GetTimeStamp

TIMESTAMP WITH TIME ZONE

System.TimeStamp

GetTimeStamp

UROWID

System.String

GetString

GetChars

VARCHAR2

System.String

GetString

GetChars

XMLType

System.String

System.Xml.XmlReader

GetString

GetXmlReader

ODP.NET Type Accessors

ODP.NET exposes provider-specific types that natively represent the datatypes in Oracle. In some cases, these ODP.NET types provide better performance and functionality that is not available to the corresponding .NET types. The ODP.NET types can be obtained from the OracleDataReader by calling their respective typed accessor.

Table 3.4 lists the valid type accessors that ODP.NET uses to obtain ODP.NET Types for an Oracle native type.

Table 3-4 ODP.NET Type Accessors  

Oracle Native Database Type

ODP.NET Type

Typed Accessor

BFILE

OracleBFile

GetOracleBFile

BLOB

OracleBlob

OracleBlob

OracleBinary

GetOracleBlob

GetOracleBlobForUpdate

GetOracleBinary

CHAR

OracleString

GetOracleString

CLOB

OracleClob

OracleClob

OracleString

GetOracleClob

GetOracleClobForUpdate

GetOracleString

DATE

OracleDate

GetOracleDate

INTERVAL (DS)

OracleIntervalDS

GetOracleIntervalDS

INTERVAL (YM)

OracleIntervalYM

GetOracleIntervalYM

LONG

OracleString

GetOracleString

LONG RAW

OracleBinary

GetOracleBinary

NCHAR

OracleString

GetOracleString

NCLOB

OracleString

GetOracleString

NUMBER

OracleDecimal

GetOracleDecimal

NVARCHAR2

OracleString

GetOracleString

RAW

OracleBinary

GetOracleBinary

ROWID

OracleString

GetOracleString

TIMESTAMP

OracleTimeStamp

GetOracleTimeStamp

TIMESTAMP WITH LOCAL TIME ZONE

OracleTimeStampLTZ

GetOracleTimeStampLTZ

TIMESTAMP WITH TIME ZONE

OracleTimeStampTZ

GetOracleTimeStampTZ

UROWID

OracleString

GetOracleString

VARCHAR2

OracleString

GetOracleString

XMLType

OracleString

GetOracleString

 

OracleXmlType

GetOracleXmlType


Obtaining LONG and LONG RAW Data

When an OracleDataReader is created containing a LONG or LONG RAW column type, OracleDataReader determines whether this column data needs to be fetched immediately or not, by checking the value of the InitialLONGFetchSize property of the OracleCommand that created the OracleDataReader.

By default, InitialLONGFetchSize is set to 0. If the InitialLONGFetchSize property value of the OracleCommand is left as 0, the entire LONG or LONG RAW data retrieval is deferred until that data is explicitly requested by the application. If the InitialLONGFetchSize property is set to a nonzero value, the LONG or LONG RAW data is immediately fetched up to the number of characters or bytes that the InitialLONGFetchSize property specifies.

ODP.NET does not support CommandBehavior.SequentialAccess. Therefore, LONG and LONG RAW data can be fetched in a random fashion.

o obtain data beyond InitialLONGFetchSize bytes or characters, one of the following must be in the select list:

primary key
ROWID
unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT NULL constraint defined on it)

The requested data is fetched from the database when the appropriate typed accessor method (GetOracleString or GetString for LONG or GetOracleBinary or GetBytes for LONG RAW) is called on the OracleDataReader object.

In order to fetch the data in a non-defer mode or when the columns in the select list do not have a primary key column, a ROWID, or unique columns, set the size of the InitialLONGFetchSize property on the OracleCommand object to equal or greater than the amount of bytes or characters needed to be retrieved.
 

Continue article...
 


Rama Mohan G
Rama Mohan G.


We are a free community web site. You can help us by visiting our sponsors:

Secure your ASP.NET Web Service and Web Applications using Thawte
Get thawtes new introductory guide to SSL security which covers the basics of how it operates. A discussion of the various applications of SSL certificates and their appropriate deployment is also included along with details of how to test SSL on your web server. Download free test version.

Featured Book: Graphics Programming with GDI+ By Mahesh Chand, founder C# Corner
Graphics Programming in GDI+
is the most in-depth treatment available on writing effective graphics applications for the .NET Framework. Graphics Programming with GDI+ is the .NET developer's guide to writing graphics applications for Windows and the Web. Through the use of detailed examples it provides experienced programmers with a deep understanding of the entire GDI+ API defined in the .NET Framework class library.
The book begins with an introduction to GDI+ and the basics of graphics programming in Windows. The core of the book is a hands-on guide to practical topics, including how to use Windows Forms and optimize GDI+ performance. Chapters demonstrate how to develop real-world tools such as GDI+Painter, GDI+Editor, ImageViewer, and ImageAnimator. The author provides extensive reusable sample code in C# throughout, and complete downloadable source code in C# and Visual Basic .NET is available online, as are color versions of screen shots from the book.

Premium Sponsors
Red-Gate  Visit Red-Gate's Homepage
.netECOMMERCE  Visit .netCOMMERCE Homepage
visit sponsors  
 
     
   
 
 Hosted by MaximumASP. Found a broken link? Terms and conditions 2000-2004 C# Corner. All Rights Reserved.