|
|
|
|
|
|
|
Page Views :
|
29749
|
|
Downloads :
|
0
|
|
Rating :
|
Rate it
|
|
Level :
|
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 OracleXmlType |
GetOracleString
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.
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...
|
|
Comment Request!
Thank you for reading this post. Please post your feedback, question, or comments about this post
Here.
|
|
|
|
|
Login
to add your contents and source code to this article
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional
consulting company, our consultants are well-known experts in .NET and many of them
are MVPs, authors, and trainers. We specialize in Microsoft .NET development and
utilize Agile Development and Extreme Programming practices to provide fast pace
quick turnaround results. Our software development model is a mix of Agile Development,
traditional SDLC, and Waterfall models.
|
|
Click here to learn more about C# Consulting. |
|
|
|
|
|
|
|
Introducing MaxV - one click. infinite control. Hyper-V Hosting from MaximumASP.
Finally – a virtual platform that delivers next-generation Windows Server 2008 Hyper-V virtualization technology from a managed hosting partner you can truly depend on. Visit www.maximumasp.com/max for a FREE 30 day trial. Hurry offer ends soon.
Climb aboard the MaxV platform and take advantage of High Availability, Intelligent Monitoring, Recurrent Backups, and Scalability – with no hassle or hidden fees.
As a managed hosting partner focused solely on Microsoft technologies since 2000, MaximumASP is uniquely qualified to provide the superior support that our business is built on. Unparalleled expertise with Microsoft technologies lead to working directly with Microsoft as first to offer IIS 7 and SQL 2008 betas in a hosted environment; partnering in the Go Live Program for Hyper-V; and product co-launches built on WS 2008 with Hyper-V technology.
|
Dynamic PDF
ceTE software specializes in components for dynamic PDF generation and manipulation. The DynamicPDF™ product line allows you to dynamically generate PDF documents, merge PDF documents and new content to existing PDF documents from within your applications.
|
Nevron Chart for .NET 2010.1 Now Available
The leading .NET charting control now features PDF, Flash and Silverlight export, visualization of large datasets and more. Deliver true charting functionality to your BI, Scorecard, Presentation or Scientific apps. Download evaluation now.
|
ASP.NET 4 Hosting
Get 2 Months Free of ASP.NET Hosting for Only $4.95/month! Receive FREE MS SQL and MySQL Databases Including ASP.NET 4/3.5, MVC 3.0, Silverlight 4, Windows 2008/IIS 7.0 Plus FREE IIS 7 Modules. Host UNLIMITED ASP.NET Web Sites – Click Here!
|
|
|
|
|
|
|
|
|
|
|
|
|