Export (0) Print
Expand All
ADO.NET and System.Transactions
Contrasting the ADO.NET DataReader and DataSet
Data Access Strategies Using ADO.NET and SQL
Data Points: Efficient Coding With Strongly Typed DataSets
Teach Old Data New Tricks with the Fully Loaded Advances in ADO.NET 2.0
Handling Data Concurrency Using ADO.NET, Part 2
Handling Data Concurrency Using ADO.NET
Migrating from ADO to ADO.NET, Part 2
Migrating from ADO to ADO.NET
Saving Parent-child Data in a Multitiered App Using ADO.NET
Expand Minimize
21 out of 29 rated this helpful - Rate this topic

Comparing the Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET

 

Bill Hamilton

October 2004

Applies to:
   .NET Framework
   Oracle Data Provider for .NET (ODP.NET) 9i
   Microsoft .NET Framework 1.1 Data Provider for Oracle

Summary: Get a comparison of the strengths of the two most common .NET data providers for Oracle, and be prepared to select the right one for your next development project. (54 printed pages)

Contents

Introduction
Data Types
Connection
Command
CommandBuilder
DataReader
Retrieving Data with REF CURSORs
Safe Mapping
Array Binding
PL/SQL Associative Arrays
Transactions
Transaction Application Failover
Information Messages and Errors
Globalization
Microsoft Visual Studio .NET IDE
Conclusion
Appendix: Detailed Comparison of Data Provider Types

Introduction

This article explores the differences between the Oracle Data Provider for .NET (ODP.NET) 9i and the Microsoft .NET Framework 1.1 Data Provider for Oracle. The two providers have much in common, but there are also important differences.

The Microsoft provider ships with the .NET Framework 1.1. All types are contained in the System.Data.OracleClient namespace. The Microsoft provider requires Oracle client version 8.1.7 or above.

ODP.NET is freely available from the Oracle Technology Network (OTN) at http://www.oracle.com/technology/software/tech/windows/odpnet/index.html. The types are contained in two namespaces—Oracle.DataAccess.Client (data access classes and enumerations) and Oracle.DataAccess.Types (classes and structures for Oracle data types). Both namespaces are in the assembly Oracle.DataAccess.dll located in the oracle\ora92\bin directory. ODP.NET requires Oracle client version 9.2 or later.

This article is divided into two sections. The first section discusses key differences between the functionality of the providers. An appendix follows that examines and compares each data provider type detailing differences between the public constructors, fields, methods, properties, properties, and operators between types in the providers; aspects of the data provider types that are similar are not discussed.

For detailed information about the Microsoft .NET Framework Data Provider for Oracle, consult MSDN. For detailed information about the ODP.NET provider, consult the documentation installed with the provider.

Data Types

ODP.NET supports all native Oracle data types in the Oracle.DataAccess.Types namespace. The ODP.NET data types—either classes or structures—more closely map to the native types than the .NET Framework data types. The ODP.NET has additional Oracle-specific types compared with the Microsoft provider and those types expose additional methods and properties to interrogate, manipulate, and convert data.

The ODP.NET OracleDataAdapter allows you to safely map Oracle data types to .NET data types where there is a potential for data loss. This is discussed in more detail in the Safe Mapping section later in this article.

Other differences in data type support between the providers are discussed in the following subsections:

Boolean

The Microsoft provider has an OracleBoolean structure that represents the result of comparison operations between Oracle data types. The OracleBoolean structure does not map to an Oracle data type but rather is included as a helper. While a standard Boolean value has two possible values—true and false—the OracleBoolean type can contain True, False, Null, One, and Zero. The OracleBoolean type provides methods, properties, and operators to facilitate working with the data.

LOBs

Oracle LOBs are used to store large-character or binary data such as text documents and images including the BFILE data type that used to store LOBs outside of the database using the file system.

The classes that support the LOB data types in each data provider are shown in the following table:

Table 1. Classes that support the LOB data types

Oracle LOB Data Type ODP.NET data type Microsoft Provider Data Type
BFILE OracleBFile OracleBFile
BLOB OracleBlob OracleLob
CLOB OracleClob String
NCLOB OracleClob String

In general, ODP.NET has more extensive methods for retrieving and managing LOBs than the Microsoft provider.

The ODP.NET OracleBFile class contains two constructors that you can use to create an OracleBFile object. The ODP.NET OracleBFile class, when compared with the Microsoft provider class, contains additional functionality for interrogating, copying, and comparing BFILEs.

REF CURSOR

ODP.NET provides an OracleRefCursor data type class that represents a REF CURSOR—a reference to an Oracle result set. An OracleRefCursor cannot be constructed—it can only be obtained as a parameter from a stored procedure, function, or anonymous block. Once retrieved, an OracleRefCursor can be used to create an OracleDataReader or to fill a DataSet using an OracleDataAdapter—this is discussed in the Retrieving Data section later in this article.

It is important to understand that a REF CURSOR is a connected object and cannot be used once the underlying connection to the database is closed.

Timestamp

ODP.NET provides three structures to support Oracle Timestamp data types: OracleTimeStamp, OracleTimeStampTZ, and OracleTimeStampLTZ. These represent the TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE Oracle data types. These classes provide methods, properties, and operators to create, compare, convert, extract, and manipulate Timestamp data.

String

In addition to the functionality in the Microsoft provider OracleString class, the ODP.NET implementation provides the GetNonUnicodeBytes method that returns the string value as a byte array using the client character set formatting, and the GetUnicodeBytes method that returns the string value as a byte array in Unicode format.

XML

ODP.NET adds support for Oracle XML data types with the OracleXmlType and OracleXmlStream classes.

The OracleXmlType class lets you store and change XML data natively in an Oracle database. You construct an instance of XMLType by specifying a CLOB or by specifying the connection to retrieve and store the XML data together with a string containing XML data, an XMLReader, or an XmlDocument. The OracleXmlType provides methods to interrogate, transform, and update the XML data.

The OracleXmlStream class represents XML data stored in an OracleXmlType object as a read-only stream.

The ODP.NET OracleCommand class supports XML operations through methods that are discussed in the Command section later in this article.

Connection

The supported connection string attributes differ between the providers. The two tables that follow describe attributes that are specific to each provider.

Table 2. ODP.NET Connection String Attributes

Name Description
Connection Timeout Maximum time in seconds to wait for a connection from the pool.
DBA Privilege Specifies administrative privilege—SYSDBA or SYSOPER.
Decr Pool Size The number of connections that are closed when an excessive number of established connections in the pool are not used. The number of connections is never reduced below the value specified by the Min Pool Size attribute.
Incr Pool Size The number of connections that are established when all of the connections in the pool are used. The number of connections is never increased above the value specified by the Max Pool Size attribute.
Proxy User Id The user name of the proxy user. Proxy authentication preserves the client identity and passes it through to the database server while using the proxy credentials to connect.

ODP.NET attempts to establish a proxy connection if either the Proxy User Id or Proxy Password attribute is set.

Proxy Password The password of the proxy user.

Table 3. Microsoft Oracle Data Provider Connection String Attributes

Name Description
Integrated Security Specifies whether integrated security is used to authenticate the user rather than a user name and password combination.

To use integrated security with ODP.NET, set the User Id attribute to "/". In this case, the Password attribute is ignored.

Unicode Specifies whether the provider uses UTF16 mode API calls. Oracle 9i client software is required.

The ODP.NET OracleConnection provides a ConnectionTimeout property that specifies how long the Open method will wait for a pooled connection before terminating the request. The Microsoft provider does not let you specify the connection timeout using a connection string attribute or through the OracleConnection.

The ODP.NET OracleConnection provides the OpenWithNewPassword method that allows you open a connection with a new password after the password in the connection string has expired. The original password must be provided in the connection string as the value for the Password attribute.

Command

ODP.NET lets you retrieve data either as ODP.NET or .NET Framework data types. The data type returned is determined by whether the data type for the output parameter is set to a DbType or an OracleDbType. The OracleDbType and DbType properties for the OracleParameter are linked—setting one causes the other to be inferred to an appropriate type.

By default, ODP.NET parameters for OracleCommand.CommandType = Text commands are bound by position. The OracleCommand class provides a BindByName property—set this property to true to bind parameters by name instead of by position. The Microsoft provider always binds parameters by name.

ODP.NET provides the AddRowid property that, when set to true, automatically returns the ROWID as part of the result set.

You can access the ROWID with the Microsoft provider by explicitly requesting the ROWID in the query. For example:

select ROWID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME from EMPLOYEES;

The ExecuteStream method of the ODP.NET OracleCommand returns the result set for an XML command as a stream containing an XML document. The ExecuteXmlReader method of the ODP.NET OracleCommand returns the result set for an XML command as an XmlTextReader.

ODP.NET OracleCommand supports XML operation with three methods—XmlCommandType, XmlQueryProperties, and XmlSaveProperties. The XmlQueryProperties and XmlSaveProperties classes support the XML operation specified by the XmlCommandType property—either Query, Insert, Update, or Delete—by defining the schema and XSL transformations for the XML document. The CommandText property for a Query XML command is a SQL SELECT statement. The CommandText together with the XmlQueryProperties property (specified by an XmlQueryProperties object) are used to perform the query operation and return the results as an XML document. The CommandText property for Insert, Update, and Delete XML is an XML document that contains the changes to be made to the database. The CommandText together with the XmlSaveProperties properties (specified by an XmlSaveProperties object) are used to perform the operation. In the above cases, the CommandType property of the OracleCommand is ignored.

CommandBuilder

The ODP.NET CommandBuilder is optimized to eliminate the additional round-trip required to obtain metadata used to build its commands. ODP.NET uses the Extended Properties collection of the DataColumn and DataTable to store name-value pairs that provide the CommandBuilder with enough schema information to build its commands without requiring a separate round trip.

The Microsoft provider has a DeriveParameters method that retrieves information about the parameters for an Oracle-stored procedure specified by the OracleCommand object into an OracleParameter collection. This method is of limited usefulness in production code because of the performance impact of the extra roundtrip and database processing required.

DataReader

ODP.NET provides typed accessors that map to all native Oracle data types. This includes accessors that are not available with the Microsoft provider.

ODP.NET determines whether LONG and LONG RAW data type values need to be fetched immediately by checking the value of the InitalLONGFetchSize property of the OracleCommand. If the InitalLONGFetchSize is set to the default value of 0, retrieval of the data for the column is delayed until the data is explicitly requested using the appropriate OracleDataReader typed accessor. If InitialLONGFetchSize is nonzero, the specified number of bytes or characters is immediately fetched.

Similarly, ODP.NET fetches LOB data types based on the setting of the InitialLOBFetchSize property of the OracleCommand. If the InitialLOBFetchSize property is nonzero, the GetOracleBlob and GetOracleLob typed accessor methods are disabled—the LOB data must be fetched using either the GetBytes or GetChars accessor.

ODP.NET provides a FetchSize property for the OracleCommand and the OracleDataReader that specifies the size of the OracleDataReader internal cache in bytes and can be used to optimize communication between the application and the database. The OracleDataReader inherits the FetchSize value from the OracleCommand used to create the reader—the value can be changed in the reader if necessary. The cache is allocated after the Read method of the OracleDataReader is first called—changing the FetchSize after the first Read has no effect.

ODP.NET provides a RowSize property for the OracleCommand that represents the number of bytes that the internal cache of the OracleDataReader needs to store a single row of data. ODP.NET sets the value from database metadata after you execute a command that returns data. Subsequently, the value can be changed to optimize data access. The RowSize property can be used to determine an appropriate value for the FetchSize property at runtime in applications that do not know the row size in advance. Optimize the FetchSize value by setting it to the product of the row size and the number of rows that the application wants to retrieve in each round-trip.

Retrieving Data with REF CURSORs

The examples in this section use the HR schema that is included as part of the default installation and the following package specification and package body:

CREATE OR REPLACE  PACKAGE "HR"."GET_EMPLOYEES" AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR);
END GET_EMPLOYEES;

CREATE OR REPLACE  PACKAGE BODY "HR"."GET_EMPLOYEES" AS
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR)
IS
BEGIN
    OPEN cur_Employees FOR
    SELECT * FROM EMPLOYEES;
END GetEmployees;
END GET_EMPLOYEE;

ODP.NET lets you create a DataReader from an OracleRefCursor object as shown in the following example:

OracleConnection conn = new OracleConnection(
    "Data Source=orcl; User Id=HR; Password=password;");

OracleCommand cmd = new OracleCommand("GET_EMPLOYEES.GetEmployees", conn);   
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("cur_Employees", OracleDbType.RefCursor,
    ParameterDirection.Output);

conn.Open();
cmd.ExecuteNonQuery();
         
// get the OracleRefCursor from the output parameter
OracleRefCursor refcur =
    (OracleRefCursor)cmd.Parameters["cur_Employees"].Value;

// get the DataReader using the OracleRefCursor
OracleDataReader dr = refcur.GetDataReader();
while(dr.Read())
   Console.WriteLine(dr["EMPLOYEE_ID"] + "; " + dr["FIRST_NAME"] + "; " +
       dr["LAST_NAME"]);

conn.Close();

The first five lines of the console output are:

100; Steven; King
101; Neena; Kochhar
102; Lex; De Haan
103; Alexander; Hunold
104; Bruce; Ernst

If a query returns multiple REF CURSORs, the OracleDataReader.NextResult method will access the result sets returned by the REF CURSORs in the order in which the OracleRefCursor parameters are bound.

ODP.NET provides overloads for the Fill method of the DataAdapter that let you fill a DataSet or DataTable using a REF CURSOR. This is shown in the following example:

OracleConnection conn = new OracleConnection(
    "Data Source=orcl; User Id=HR; Password=password;");

OracleCommand cmd = new OracleCommand("GET_EMPLOYEES.GetEmployees", conn);   
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("cur_Employees", OracleDbType.RefCursor, ParameterDirection.Output);

conn.Open();
cmd.ExecuteNonQuery();
         
// get the OracleRefCursor from the output parameter
OracleRefCursor refcur =
    (OracleRefCursor)cmd.Parameters["cur_Employees"].Value;
OracleDataAdapter da = new OracleDataAdapter("", conn);
DataTable dt = new DataTable();
// fill the DataTable using the OracleRefCursor
da.Fill(dt, refcur);

foreach(DataRow row in dt.Rows)
   Console.WriteLine(row["EMPLOYEE_ID"] + "; " +
       row["FIRST_NAME"] + "; " + row["LAST_NAME"]);

conn.Close();

The first five lines of the console output are:

100; Steven; King
101; Neena; Kochhar
102; Lex; De Haan
103; Alexander; Hunold
104; Bruce; Ernst

If a query returns multiple REF CURSORs, they will automatically populate multiple DataTable objects in the DataSet. These tables are named TableN where N is an integer starting at 0 for the result set returned by the first REF CURSOR.

Safe Mapping

The Oracle data types can potentially lose data during conversion to .NET data types. These types are NUMBER, DATE, all Timestamp types, and INTERVAL DAY TO SECOND.

The ODP.NET provider has a SafeMapping property in the OracleDataAdapter class, which you can use to map Oracle data types that have no .NET equivalent data types to either a String or byte array in Oracle format. The SafeMapping property accesses a Hashtable that stores name-value pairs that map column names to either string or byte[] types:

da.SafeMapping.Add("EMPNO", typeof(byte[]));

An asterisk can be specified for the column name to map all columns that can potentially lose data:

da.SafeMapping.Add("*", typeof(byte[])); 

A mapping specified by column name takes precedence over any asterisk mapping.

Array Binding

ODP.NET supports multiple parameter sets. This technique lets you execute a query multiple times in a single round trip. This is done by setting each parameter value to an array instead of a single value. Then set the ArrayBindCount of the OracleCommand object to the number of parameter sets. The following example uses array binding to insert two rows into the COUNTRIES table in the HR schema:

// create an array of values for each column
string[] aCountryId = new string[2]{"AA", "AB"};
string[] aCountryName = new string[2]{"Country AA", "Country AB"};
int[] aRegionId = new int[2]{1, 2};

// create the insert command
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "INSERT INTO COUNTRIES(COUNTRY_ID, COUNTRY_NAME,
    REGION_ID) VALUES (:countryId, :countryName, :regionId)";

// set the number of values (rows) in the arrays
cmd.ArrayBindCount = 2; 

// Create parameters for the array operations 
cmd.Parameters.Add("countryId", OracleDbType.Char, 2,
    ParameterDirection.Input).Value = aCountryId;
cmd.Parameters.Add("countryName", OracleDbType.Varchar2, 40,
    ParameterDirection.Input).Value = aCountryName;
cmd.Parameters.Add("regionId", OracleDbType.Int32, 0,
    ParameterDirection.Input).Value = aRegionId;

// create the connection and execute the command
OracleConnection conn = new OracleConnection(
    "Data Source=orcl; User Id=HR; Password=password;");
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

It is important to note that array binding can lead to partial failures. If necessary, wrap the command in a local transaction and roll it back in the event of a partial failure.

The ArrayBindStatus property is used to specify the status of each value in a parameter array using the values in the OracleParameterStatus enumeration. ArrayBindStatus is populated by the application to indicate that a null value is to be inserted into a column, and by ODP.NET after execution to indicate that a value has been successfully retrieved, that a null value was fetched into a column, or that the fetched value was truncated.

If an error occurs during an array bind execution, the ArrayBindIndex property of the OracleError object indicates the row number that caused the error.

PL/SQL Associative Arrays

ODP.NET supports PL/SQL Associative Array binding, letting you bind an associative array OracleParameter to a PL/SQL stored procedure.

To use PL/SQL associative arrays:

  1. Specify the CollectionType property of the OracleParameter as OracleCollectionType.PLSQLAssociativeArray.
  2. Specify the maximum number of elements in the array using the Size property of each OracleParameter.
  3. Specify the size of each element in the array using the ArrayBindSize property.
  4. Set the ArrayBindStatus for each input parameter value to OracleParameterStatus.Success.
  5. Execute the command.

Transactions

The ODP.NET OracleCommand class supports creating transaction savepoints and rolling back to those savepoints. This is easy enough to do with the Microsoft provider by executing an Oracle SAVEPOINT or ROLLBACK TO SAVEPOINT transaction as shown in the following code:

new OracleCommand("SAVEPOINT " + savePointName, tran.Connection,
    tran).ExecuteNonQuery(); 

new OracleCommand("ROLLBACK TO SAVEPOINT " + savePointName,
    tran.Connection, tran).ExecuteNonQuery();

The Oracle database does not support multiple transactions on a single connection—they are always scoped at the connection level. Accordingly, ODP.NET propagates a transaction to commands executed on the connection. The Microsoft provider requires you to explicitly assign the transaction to each command.

The Microsoft provider allows the connection to be enlisted in a distributed transaction using the EnlistDistributedTransaction method of the OracleConnection class. ODP.NET supports distributed transactions with Oracle Services for MTS (OraMTS) available as a free download from Oracle Technology Network (OTN) at http://www.oracle.com/technology/tech/windows/ora_mts/index.html.

Transaction Application Failover

Oracle uses Transaction Application Failover (TAF) to provide a high availability by automatically reconnecting to a database using a different node if the connection fails—the new database connection is identical to the original. Active transactions are rolled back as part of the reconnection process.

When a failover occurs, the ODP.NET OracleConnection raises a Failover event. This allows an application to be notified when a failover occurs so that failover delays or failures can be dealt with. The Failover events raised indicate when loss of connection is detected, when a connection is successfully reestablished, and if the failover does not succeed. You can check the OracleFailoverEventArgs to determine whether the Failover succeeded.

The following code shows how to register an OracleFailoverEvent handler and a code fragment for the associated Failover handler:

// register the handler for the connection Failover event
conn.Failover += new OracleFailoverEventHandler(OnFailover);

// handler for the Failover event
public FailoverReturnCode OnFailover(object sender,
    OracleFailoverEventArgs e)
{
    switch(e.FailoverEvent)
    case FailoverEvent.Begin:  // failover is starting
        ...
        break;
    case FailoverEvent.Abort:  // failover unsuccessful - no retry option
        ...
        break;
    case FailoverEvent.End:    // failover successfully completed
        ...
        break;
    case FailoverEvent.Reauth: // user handle has been reauthenticated
        ...
        break;
    case FailoverEvent.Error:  // failover unsuccessful - application can
        ...                    // return FailoverReturnCode.Retry to retry
        break;
    
    return FailoverReturnCode.Success;
}

Information Messages and Errors

The OracleConnection class in both the ODP.NET and Microsoft provider raise an InfoMessage event when Oracle sends a warning or information message.

The OracleException class is common to both the ODP.NET and Microsoft provider. It represents an exception that is thrown when the data provider encounters an error.

ODP.NET provides properties that expose more detail about information messages (InfoMessage) and provider errors (OracleException) than the Microsoft provider. Additional detail is provided through an OracleErrorCollection object that contains a collection of OracleError objects, which expose details about the error.

The InfoMessage event exposes the OracleErrorCollection through the Errors property of the OracleInfoMessageEventArgs. The OracleException exposes the OracleErrorCollection through its Errors property.

Globalization

Oracle globalization settings include the character set, currency formatting, data formatting, language, sort order, calendar, and time stamp formatting. The OracleGlobalization class is used to represent these settings.

Client globalization settings are derived from the globalization settings in the Windows registry of the client computer. These settings cannot be changed during the lifetime of the application. ODP.NET provides the GetClientInfo static method of the OracleGlobalization class to retrieve these settings. For example:

OracleGlobalization cg = OracleGlobalization.GetClientInfo();

Session globalization settings are initially set to the client globalization settings, but can be changed once a connection to the database has been established. The ODP.NET OracleConnection class provides GetSessionInfo and SetSession info methods that let you retrieve and modify globalization settings for the current session. The following example retrieves the session globalization settings and changes the DataFormat setting for the session:

OracleConnection conn = new OracleConnection(
    "Data Source=orcl; User Id=HR; Password=password;");
conn.Open();
OracleGlobalization sg = conn.GetSessionInfo();
sg.DateFormat = "MM/DD/YYYY";
conn.SetSessionInfo(sg);

Thread-based globalization settings are specific to each thread. Like session globalization settings, these are set initially to the client globalization settings and can be retrieved and changed using the GetThreadInfo and SetThreadInfo static methods of the OracleGlobalization class as shown in the following example:

OracleGlobalization tg = OracleGlobalization.GetThreadInfo();
tg.DateFormat = "MM/DD/YYYY";
OracleGlobalization.SetThreadInfo(tg);

Microsoft Visual Studio .NET IDE

The Microsoft provider supports drag and drop of the OracleConnection, OracleCommand, and OracleDataAdapter classes from the Data toolbox in the Microsoft Visual Studio .NET IDE to use the Visual Studio .NET code generation wizards. This functionality is not supported by ODP.NET.

Conclusion

The Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET (ODP.NET) 9i are both freely available and very often provide comparable functionality. Application development using either provider is more similar than different since most corresponding classes in the two providers have identical names and implement the same .NET Framework interfaces. There are some important differences, however:

In some cases, ODP.NET data types more closely map to native Oracle types. ODP.NET also provides helper methods beyond those available in the Microsoft provider for working with complex data types such as LOBs, Timestamps, REF CURSORs, and Oracle XML data types. These differences might facilitate software development if you are working extensively with those types, although it is generally possible to accomplish the same task with the Microsoft provider. ODP.NET also provides some functionality not available in the Microsoft provider for working with database connections, information messages and error detail, array binding, PL/SQL associative arrays, transaction application failover (TAF), and globalization.

The Microsoft provider is more tightly integrated into the Visual Studio .NET IDE, allowing you to use the code generation wizards for connections, commands, and data adapters—this is currently not possible with ODP.NET. Deployment of the Microsoft provider is simpler because it is installed as part of the .NET Framework version 1.1 or later. The Microsoft provider is supported by Oracle client version 8.1.7 or higher while ODP.NET requires Oracle client version 9.2 or higher.

Use the detailed comparison between the Microsoft data provider and ODP.NET presented in this article together with your application development and deployment requirements to choose the right data provider.

Related Books

ADO.NET Cookbook

ADO.NET in a Nutshell

About the author

Bill Hamilton is a software architect specializing in designing, developing, and implementing distributed applications using Microsoft .NET and J2EE technologies. An early technology adopter, he frequently evaluates, recommends, and helps his clients use new technologies effectively. Bill has written the two ADO.NET books referenced above in the Related Books section.

 

Appendix: Detailed Comparison of Data Provider Types

This appendix begins by mapping the types in each data provider to each other. A comparison of each type follows, which details differences between the public constructors, fields, methods, properties, and operators between types in the providers; similar aspects of the types are not discussed.

Data Provider Type Mapping

The following two tables map the types in the ODP.NET to corresponding Microsoft Oracle Data provider types:

Oracle Data Access Classes

ODP.NET Microsoft Oracle Data Provider
OracleCommand class OracleCommand
OracleCommandBuilder class OracleCommandBuilder
OracleConnection class OracleConnection
OracleDataAdapter class OracleDataAdapter
OracleDataReader class OracleDataReader
OracleError class N/A
OracleErrorCollection class N/A
OracleException class OracleException
OracleFailoverEventArgs class N/A
OracleFailoverEventHandler delegate N/A
OracleGlobalization class N/A
OracleInfoMessageEventHandler delegate OracleInfoMessageEventHandler
OracleInfoMessageEventArgs class OracleInfoMessageEventArgs
OracleParameter class OracleParameter
OracleParameterCollection class OracleParameterCollection
OracleRowUpdatedEventArgs class OracleRowUpdatedEventArgs
OracleRowUpdatedEventHandler delegate OracleRowUpdatedEventHandler
OracleRowUpdatingEventArgs class OracleRowUpdatingEventArgs
OracleRowUpdatingEventHandler delegate OracleRowUpdatingEventHandler
OracleTransaction class OracleTransaction
OracleXmlQueryProperties class N/A
OracleXmlSaveProperties class N/A
N/A OraclePermission
N/A OraclePermissionAttribute

Oracle Data Type Classes and Structures

ODP.NET Microsoft Oracle Data Provider
OracleBFile class OracleBFile
OracleBinary structure OracleBinary
N/A OracleBoolean structure
OracleBlob class OracleLob
OracleClob class OracleLob
OracleDate structure OracleDateTime
OracleDecimal structure OracleNumber
OracleIntervalDS structure OracleTimeSpan
OracleIntervalYM structure OracleMonthSpan
OracleRefCursor class N/A
OracleString structure OracleString
OracleTimeStamp structure N/A
OracleTimeStampLTZ structure N/A
OracleTimeStampTZ structure N/A
OracleXmlStream class N/A
OracleXmlType class N/A

Data Access Classes

The ODP.NET and Microsoft providers both have a set of classes used to access an Oracle data source. The following subsections discuss the differences in the implementation of these classes by the providers.

OracleCommand

The OracleCommand class is common to both the ODP.NET and Microsoft data providers. The OracleCommand class represents a SQL statement, stored procedure, or table name. It creates the request, passing it to the database, and returning the results as an instance of the appropriate class. The OracleCommand class in each provider implements the System.Data.IDbCommand interface.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Constructors Constructors

OracleCommand(string query, OracleConnection conn, OracleTransaction tran)

Methods

ExecuteStream()

Executes a command using the XmlCommandType and CommandText properties and returns a Stream containing the result set XML document.

ExecuteToStream(Stream s)

Executes a command using the XmlCommandType and CommandText properties and appends the result set XML document to the existing Stream.

ExecuteXmlReader()

Executes a command using the XmlCommandType and CommandText properties and returns the result set XML document as an XmlTextReader.

Methods

ExecuteOracleNonQuery(out OracleString rowId)

Executes a PL/SQL statement against the database and returns the number of rows affected and the Oracle server ROWID of the affected row for an INSERT, UPDATE, or DELETE command where exactly one row is affected.

ExecuteOracleScalar()

Executes the query and returns the first column in the first row as an Oracle-specific data type. If the result is a REF CURSOR, the method returns a null reference.

Properties

AddRowid {get; set;}

Adds the ROWID as part of the result set returned by a SELECT query. By default, the ROWID is hidden.

ArrayBindCount {get; set;}

Specifies if array binding is used and the number of elements to be bound in the OracleParameter Value property.

BindByName {get; set;}

Specifies the binding method in the collection. A value of true is by name while false is by position. The default is false. This property is supported only for CommandType.Text.

FetchSize {get; set;}

Specifies the size of the OracleDataReader internal cache in bytes to store the result set for each server round-trip. The default is 65536 bytes.

InitialLOBFetchSize {get; set;}

Specifies the size in bytes of the initial amount of LOB data immediately fetched by the OracleDataReader. The value is the number of characters for CLOB and NCLOB data and the number of bytes for BLOB data. The default is 0 and the maximum value is 32767. With the default of 0, data retrieval is deferred until specifically requested.

InitialLONGFetchSize {get; set;}

Specifies the size in bytes of the initial amount of LONG and LONG RAW columns. The default is 0 and the maximum value is 32767. With the default of 0, data retrieval is deferred until specifically requested.

RowSize {get; set;}

Gets the amount of memory in bytes needed by the OracleDataReader internal cache to store one row of data. The value is set after the execution of command that returns a result set. This property can be used to set the FetchSize property to retrieve multiple rows (FetchSize = RowSize * number of rows) for each server round trip.

XmlCommandType {get; set;}

Specifies the type of XML operation as an OracleXmlCommandType value.

XmlQueryProperties {get; set;}

Specifies the properties used when an XML document is created from a result set as an OracleXmlQueryProperties value.

XmlSaveProperties {get; set;}

Specifies the properties used when an XML document is used to save changes to the database as an OracleXmlSaveProperties value.

Properties

Transaction {get; set;}

Specifies the OracleTransaction in which the command executes.

OracleCommandBuilder

The OracleCommandBuilder class is common to both the ODP.NET and Microsoft data providers. It automatically generates single-table SQL statements that reconcile changes made to a DataSet with the underlying database.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Constructors Constructors
Methods Methods

static DeriveParameters(OracleCommand cmd)

Retrieves parameter information from the stored procedure specified by the OracleCommand and populates the OracleParameterCollection for the OracleCommand object.

Properties

CaseSensitive {get; set;}

Specifies whether double quotes are used around the names of Oracle objects. The default is false.

Properties

QuotePrefix {get; set;}

Specifies the characters used as the prefix for names of Oracle objects.

QuoteSuffix {get; set;}

Specifies the characters used as the suffix for names of Oracle objects.

OracleConnection

The OracleConnection class is common to both the ODP.NET and Microsoft data providers. It represents a connection to an Oracle database created using a connection string. The OracleConnection class in each provider implements the System.Data.IDbConnection interface.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Constructors Constructors
Methods

Clone()

Creates a copy of the OracleConnection object.

GetSessionInfo()

Returns a new OracleGlobalization object representing the globalization settings of the session.

GetSessionInfo(OracleGlobalization og)

Refreshes the OracleGlobalization object representing the globalization settings of the session.

OpenWithNewPassword(string newPassword)

Opens a closed OracleConnection with the new password. The old password must be provided in the connection string as the Password attribute value.

SetSessionInfo(OracleGlobalization og)

Sets the globalization settings with the properties specified in the OracleGlobalization object parameter.

Methods

EnlistDistributedTransaction(ITransaction distributedTransaction)

Enlists the connection in the specified distributed transaction. This method lets you enlist in an existing distributed transaction if auto-enlistment is disabled.

Properties

ConnectionTimeout {get;}

Gets the maximum time in seconds that the Open() method will wait for a pooled connection before terminating the request.

Properties
Events

Failover

Raised when an Oracle failover occurs.

Events

OracleDataAdapter

The OracleDataAdapter class is common to both the ODP.NET and Microsoft data providers. It represents a connection and a set of commands that fill a DataSet or DataTable and reconcile changes with the underlying database. The OracleDataAdapter class in each provider extends System.Data.Common.DbAdapter and implements the System.Data.IDbAdapter and System.Data.IDataAdapter interfaces.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Constructors Constructors
Methods

Fill(DataTable dt, OracleRefCursor cur)

Fill(DataSet ds, OracleRefCursor cur)

Fill(DataSet ds, string srcTable, OracleRefCursor cur)

Fill(DataSet ds, int startRecord, int maxRecords, string srcTable, OracleRefCursor cur)

Populates or refreshes the DataTable or DataSet. The overloads let you use an OracleRefCursor to specify the rows.

Methods
Properties

Requery {get; set;}

Specifies whether the SelectCommand is executed for subsequent calls to Fill().

SafeMapping {get; set;}

Specifies the mapping between the result set columns and .NET data types to preserve data for DATE, TimeStamp, INTERVAL DAY TO SECOND, and NUMBER data types.

Properties

OracleDataReader

The OracleDataReader class is common to both the ODP.NET and Microsoft data providers. It represents a forward-only, read-only stream of a result set from a database. The OracleDataReader in each provider implements the System.Data.IDataReader and System.Data.IDataRecord interfaces.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Constructors

N/A

Constructors

N/A

Methods

GetOracleBlob(int colIndex)

Returns an OracleBlob object for specified column.

GetOracleBlobForUpdate(int colIndex)

GetOracleBlobForUpdate(int colIndex, int waitLockSec)

Returns an updateable OracleBlob object for specified column.

GetOracleClob(int colIndex)

Returns an OracleClob object for specified column.

GetOracleClobForUpdate(int colIndex)

GetOracleClobForUpdate(int colIndex, int waitlockSec)

Returns an updateable OracleClob object for specified column.

GetOracleDate(int colIndex)

Returns an OracleDate structure for specified DATE column.

GetOracleDecimal(int colIndex)

Returns an OracleDate structure for specified NUMBER column.

GetOracleIntervalDS(int colIndex)

Returns an OracleIntervalDS structure for specified INTERVAL DAY TO SECOND column.

GetOracleIntervalYM(int colIndex)

Returns an OracleIntervalYM structure for specified INTERVAL YEAR TO MONTH column.

GetOracleTimeStamp(int colIndex)

Returns OracleTimeStamp structure for specified TimeStamp column.

GetOracleTimeStampLTZ(int colIndex)

Returns an OracleTimeStampLTZ structure for specified TimeStamp WITH LOCAL TIME ZONE column.

GetOracleTimeStampTZ(int colIndex)

Returns an OracleTimeStampTZ structure for specified TimeStamp WITH TIME ZONE column.

GetOracleXmlType(int colIndex)

Returns an OracleXmlType object for specified XMLType column.

GetXmlReader(int colIndex)

Returns an XMLType column as a .NET XmlTextReader object.

Methods

GetOracleDateTime(int colIndex)

Returns an OracleDateTime object for the specified column.

GetOracleLob(int colIndex)

Returns an OracleLob object for the specified column.

GetOracleMonthSpan(int ColIndex)

Returns an OracleMonthSpan object for the specified column.

GetOracleNumber(int colIndex)

Returns an OracleNumber object for the specified column.

GetOracleTimeSpan(int colIndex)

Returns an OracleTimeSpan object for the specified column.

Properties

FetchSize {get; set;}

Specifies the size of the OracleDataReader internal cache in bytes. The default value is the OracleCommand FetchSize property value.

InitialLOBFetchSize {get; set;}

Specifies the size that the OracleDataReader initially fetches for LOB columns. The default value is the OracleCommand InitialLOBFetchSize property value.

InitialLONGFetchSize {get; set;}

Specifies the size that the OracleDataReader initially fetches for LONG and LONG RAW columns. The default value is the OracleCommand InitialLONGFetchSize property value.

Properties

OracleError and OracleErrorCollection [ODP.NET Only]

The OracleError and OracleErrorCollection classes are specific to the ODP.NET data provider. The OracleError class represents a warning or error reported by the Oracle database. The OracleErrorCollection class is an ArrayList of OracleError objects.

The following table outlines the implementation of the OracleError class.

ODP.NET Microsoft
Constructors

N/A

N/A
Methods

ToString()

Returns a string representation of the OracleError in the format: Ora-error number: Class.Method name error message stack trace information.

 
Properties

ArrayBindIndex {get;}

The row number for an error that occurred during Array Bind execution. Multiple errors result in multiple OracleError objects in the OracleErrorCollection.

DataSource {get;}

The TNS name that identifies the Oracle database.

Message {get;}

The message describing the error.

Number {get;}

The Oracle error number.

Procedure {get;}

The name of stored procedure that caused the error.

Source {get;}

The name of the data provider that generated the error.

 

OracleException

The OracleException class is common to both the ODP.NET and Microsoft data providers. In the either provider, the class represents an exception that is thrown when the provider encounters an error. In the Microsoft provider, the class also represents a warning or error returned by an Oracle database similar to the OracleError class in the ODP.NET provider. The OracleException class in each provider extends System.Exception.

The following table outlines other differences between the implementations.

ODP.NET Microsoft
Constructors

N/A

Constructors

N/A

Methods

GetObjectData(SerializationInfo si, StreamingContext sc)

Sets the SerializationInfo object with information about the exception. The information includes the DataSource, Message, Number, Procedure, Source, and StackTrace.

ToString()

Returns a string containing the fully qualified name of the exception, the error message in the Message property, the InnerException.ToString() message, and the stack trace. This method overrides the System.Exception implementation.

Methods
Properties

DataSource {get;}

The TNS name containing information for connecting to the Oracle instance.

Errors {get;}

A collection of OracleError objects containing details of the exceptions generated by Oracle database.

Number {get;}

The Oracle error number.

Procedure {get;}

The name of the stored procedure that caused the exception.

Source {get;}

The name of the data provider that generated the error.

Properties

Code {get;}

Gets the code portion of the error.

OracleFailoverEventArgs and OracleFailoverEventHandler [ODP.NET Only]

The OracleFailoverEventArgs class and the OracleFailoverEventHandler delegate are specific to the ODP.NET data provider. The OracleFailoverEventArgs class represents data for the OracleConnection.Failover event that is raised when the client connect request is successfully or unsuccessfully forwarded to another listener if a listener is not responding. The OracleFailoverEventHandler delegate represents the signature for the method that handles that event.

The following table outlines the implementation.

ODP.NET Microsoft
Constructors

N/A

N/A
Methods  
Properties

FailoverType {get;}

The type of failover that the client requested as a FailoverType value.

FailoverEvent {get;}

The state of the failover as a FailoverEvent value.

 

OracleGlobalization [ODP.NET Only]

Oracle globalization support lets you store, process, and retrieve data in native languages by ensuring that error messages, sort order, data types and conventions, and utilities automatically adapt to native language and locale.

The OracleGlobalization class is specific to the ODP.NET data provider. The class is used to get and set globalization settings of the session, thread, and local computer.

The following table outlines the implementation.

ODP.NET Microsoft
Constructors

N/A

N/A
Methods

Clone()

Creates a copy of the OracleGlobalization object.

static GetClientInfo(OracleGlobalization og)

Returns the Oracle globalization settings of the local computer as an OracleGlobalization object.

static GetThreadInfo()

static GetThreadInfo(OracleGlobalization og)

Returns or refreshes an OracleGlobalization instance that represents the Oracle globalization settings of the current thread.

static SetThreadInfo(OracleGlobalization og)

Sets the Oracle globalization settings to that of the current thread.

 
Properties

Calendar {get; set;}

Specifies the calendar system.

ClientCharacterSet {get;}

Specifies the client character set.

Comparison {get; set;}

Specifies the comparison method for WHERE clauses and comparisons in PL/SQL blocks. The default value is the NLS_COMP (National Language Support comparison) setting of the local computer.

Currency {get; set;}

Specifies the local currency symbol for the L number format element.

DateFormat {get; set;}

Specifies the date format for Oracle Date type.

DateLanguage {get; set;}

Specifies the language used for day and month names, and date abbreviations.

DualCurrency {get; set;}

Specifies the dual currency symbol for the U number format element.

ISOCurrency {get; set;}

Specifies the international currency symbol for the C number format element.

Language {get; set;}

Specifies the default language of the database.

LengthSemantics {get; set;}

Specifies the semantics for creation of CHAR and VARCHAR2 columns using either byte or character (default) length semantics—this corresponds to the NLS_LENGTH_SEMANTICS parameter.

NCharConversionException {get; set;}

Specifies whether data loss during implicit or explicit character type conversions results in an error.

NumericCharacters {get; set;}

Specifies the characters used for the decimal character and group separator character for numeric values in strings.

Sort {get; set;}

Specifies the collating sequence for the ORDER BY clause in queries. The default value is the NLS_SORT setting of the local computer.

Territory {get; set;}

Specifies the name of the territory.

TimeStampFormat {get; set;}

Specifies the string format for TimeStamp data types.

TimeStampTZFormat {get; set;}

Specifies the string format for TimeStampTZ data types.

TimeZone {get; set;}

Specifies the string format for time zone region name.

 

OracleInfoMessageEventArgs and OracleInfoMessageEventHandler

The OracleInfoMessageEventArgs and OracleInfoMessageEventHandler classes are common to both the ODP.NET and Microsoft data providers. The OracleInfoMessageEventArgs class represents data for the OracleConnection.InfoMessage event. The OracleInfoMessageEventHandler delegate represents the signature for the method that handles that event.

The following table outlines the differences between the OracleInfoMessageEventArgs implementations.

ODP.NET Microsoft
Constructors

N/A

Constructors

N/A

Methods Methods

ToString()

String representation of the InfoMesage event. The implementation overrides the method in the System.EventArgs class.

Properties

Errors {get;}

The collection or errors generated by the data source as an OracleErrorCollection object.

Properties

Code {get;}

The code portion of the error message.

OracleParameter and OracleParameterCollection

The OracleParameter and OracleParameterCollection classes are common to both the ODP.NET and Microsoft data providers. The OracleParameter class represents a parameter to an OracleCommand. The most significant difference between the implementations is the support for arrays by the ODP.NET provider. The OracleParameter class in each provider implements the System.Data.IDbDataParameter and System.Data.IDataParameter interfaces.

The following table outlines the differences between the implementations of the OracleParameter class.

ODP.NET Microsoft
Constructors

OracleParameter(string paramName, object paramValue)

OracleParameter(string paramName, OracleDbType dataType, ParameterDirection dir)

OracleParameter(string paramName, OracleDbType dataType, object paramValue, ParameterDirection dir)

OracleParameter(string paramName, OracleDbType dataType, int size, object paramValue, ParameterDirection dir)

Constructors
Methods

Clone()

Creates a copy of the OracleParameter object.

Dispose()

Releases resources allocated for the parameter object.

Methods
Properties

ArrayBindSize {get; set;}

Specifies the input or output size of elements of Value property before or after an Array Bind or PL/SQL Associative Array execution.

ArrayBindStatus {get; set;}

Specifies the input or output status of each element in the Value property before or after an Array Bind or PL/SQL Associative Array execution.

CollectionType {get; set;}

Specifies whether the parameter represents a collection. If it does, the collection type is specified as an OracleCollectionType value.

OracleDbType {get; set;}

Specifies the data type OracleDbType of the parameter. The default is OracleDbType.Varchar2. The OracleDbType and DbType are linked—setting one changes the other to a supporting type.

Status {get; set;}

Specifies the status of the execution related to the data in the Value property. Before execution, this property indicates the bind status related to the Value property. After execution, this property returns the status of the execution. This property is ignored for Array Bind and PL/SQL Associative Array Bind.

Properties

OracleType {get; set;}

Specifies the OracleType of parameter. The default is OracleType.VarChar. The OracleType and DbType are linked—setting one changes the other to a supporting type.

The OracleParameterCollection represents a collection of OracleParameter objects.

The following table outlines the differences between the implementation of the OracleParameterCollection class.

ODP.NET Microsoft
Constructors

N/A

Constructors

N/A

Methods Methods
Properties Properties

IsFixedSize {get;}

Indicates whether the collection has a fixed size.

IsReadOnly {get;}

Indicates whether the collection is read-only.

IsSynchronized {get;}

Indicates whether the collection is thread-safe.

SyncRoot {get;}

Gets the object used to synchronize access to the collection.

OracleRowUpdatedEventArgs and OracleRowUpdatedEventHandler

The OracleRowUpdatedEventArgs class and the OracleRowUpdatedEventHandler delegate are common to both the ODP.NET and Microsoft data providers. The OracleRowUpdatedEventArgs class represents data for the OracleDataAdapter.RowUpdated event. The OracleRowUpdatedEventHandler delegate represents the signature for the method that handles that event. Both extend System.Data.Common.RowUpdatedEventArgs.

There are no significant differences between the implementations by the ODP.NET and the Microsoft data providers.

OracleRowUpdatingEventArgs and OracleRowUpdatingEventHandler

The OracleRowUpdatingEventArgs class and the OracleRowUpdatingEventHandler delegate are common to both the ODP.NET and Microsoft data providers. The OracleRowUpdatingEventArgs class represents data for the OracleDataAdapter.RowUpdating event. The OracleRowUpdatingEventHandler delegate represents the signature for the method that handles that event. Both extend System.Data.Common.RowUpdatingEventArgs.

There are no significant differences between the implementations by the ODP.NET and the Microsoft data providers.

OracleTransaction

The OracleTransaction class is common to both the ODP.NET and Microsoft data providers. The OracleTransaction class represents a local transaction at the database. The main difference between the implementations is the support for savepoints by the ODP.NET provider. The OracleTransaction class in each provider implements the System.Data.IDbTransaction inteface.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Constructors

N/A

Constructors

N/A

Methods

Rollback(string savepointName)

The ODP.NET provider has this overload to support rolling back a transaction to a savepoint created using the Save() method.

Save(string savepointName)

Creates a savepoint within the current transaction.

Methods
Properties Properties

OracleXmlQueryProperties and OracleXmlSaveProperties [ODP.NET Only]

The OracleXmlQueryProperties and OracleXmlSaveProperties classes are specific to both the ODP.NET data provider and provide support for XML operations against an Oracle database.

The OracleXmlQueryProperties class represents XML properties for an OracleCommand of XmlCommandType = Query.

The following table outlines the implementations of the OracleXmlQueryProperties class.

ODP.NET Microsoft
Constructors

OracleXmlQueryProperties()

N/A
Methods

Clone()

Creates a copy of the OracleXmlQueryProperties object.

 
Properties

MaxRows {get; set;}

Specifies the maximum number of result set rows in the result XML document.

RootTag {get; set;}

Specifies the root element of the result XML document.

RowTag {get; set;}

Specifies the element name for a row of data in the result XML document.

Xslt {get; set;}

Specifies the XSL document used to transform the result XML document.

XsltParams {get; set;}

Specifies the parameters for the XSL document as a semi-colon separated string of name/value pairs.

 

The OracleXmlSaveProperties class represents XML properties for an OracleCommand of XmlCommandType = Insert, Update, or Delete.

The following table outlines the implementation of the OracleXmlSaveProperties class.

ODP.NET Microsoft
Constructors

OracleXmlSaveProperties()

N/A
Methods

Clone()

Creates a copy of the OracleXmlSaveProperties object

 
Properties

KeyColumnsList {get; set;}

Specifies the list of columns used as a key to locate rows for update or delete using an XML document.

RowTag {get; set;}

Specifies the name of the XML element that identifies a row of data in the XML document.

Table {get; set;}

Specifies the name of the table or view to which changes are saved.

UpdateColumnList {get; set;}

Specifies the list of columns to update or insert.

Xslt {get; set;}

Specifies the XSL document used to transform the XML document.

XsltParams {get;}

Specifies parameters for the XSL document as a semi-colon separated string of name/value pairs.

 

OraclePermission and OraclePermissionAttribute [Microsoft Only]

The OraclePermission and OraclePermissionAttribute classes are specific to the Microsoft data providers. The OraclePermission class helps ensure that a user has an adequate security level to access an OracleDatabase. The OraclePermissionAttribute class associates a security action with a custom security attribute.

Both classes are for future use when the Microsoft data provider supports partial trust scenarios—Microsoft Oracle data providers on .NET Framework 1.0 and 1.1 require full trust callers.

Data Type Classes and Structures

The ODP.NET and Microsoft providers both have a set of classes and structures used to work with Oracle data types. The following subsections discuss the differences in the implementation of these classes and structures by the providers.

Oracle Data Enumeration

The ODP.NET and Microsoft providers both have enumerations of the Oracle data types. The ODP.NET provider has the OracleDbType structure while the Microsoft provider has the OracleType structure.

The following table maps the enumerations for both providers to the Oracle data types they represent.

Oracle Data Type ODP.NET (OracleDbType) Microsoft (OracleType)
BFILE BFile BFile
BLOB Blob Blob
byte Byte Byte
CHAR Char Char
CLOB Clob Clob
DATE Date DateTime
8-byte FLOAT Double Double1
2-byte INTEGER Int16 Int16
4-byte INTEGER Int32 Int32
8-byte INTEGER Int64 N/A
INTERVAL DAY TO SECOND IntervalDS IntervalDayToSecond
INTERVAL YEAR TO MONTH IntervalYM IntervalYearToMonth
LONG Long LongVarChar
LONG RAW LongRaw LongRaw
NCHAR NChar NChar
NCLOB NClob NClob
NUMBER Decimal Number
NVARCHAR2 NVarchar2 NVarChar
RAW Raw Raw
REF CURSOR RefCursor Cursor
ROWID N/A RowId
4-byte FLOAT Single Float1
TIMESTAMP TimeStamp Timestamp
TIMESTAMP WITH LOCAL TIME ZONE TimeStampLTZ TimestampLocal
TIMESTAMP WITH TIME ZONE TimeStampTZ TimestampWithTZ
VARCHAR2 Varchar2 VarChar
    SByte1
    UInt161
    UInt321

1Not a native Oracle data type—used to improve performance when binding input parameters.

Oracle BFILE

The OracleBFile class is common to both the ODP.NET and Microsoft data provider. It represents the Oracle BFILE data type and provides methods for performing operations on the data.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Constructors

OracleBFile(OracleConnection conn)

OracleBFile(OracleConnection conn, string bfilePath, string bfileName)

Constructors
Methods

Close()

Closes the OracleBFile object and releases associated resources.

CloseFile()

Closes the file referenced by the OracleBFile object.

Compare(Int64 srcOffset, OracleBFile bfile, Int64 destOffset, Int64 numCompareBytes)

Returns the relative value of two OracleBFile objects.

CopyTo (OracleBlob blob)

CopyTo (OracleBlob blob, Int64 destOffsetBytes)

CopyTo (Int64 srcOffsetBytes, OracleBlob blob, Int64 destOffsetBytes, Int64, Int64 numCopyBytes)

CopyTo (OracleClob clob)

CopyTo (OracleClob clob, Int64 destOffsetBytes)

CopyTo (Int64 srcOffsetBytes, OracleClob clob, Int64 destOffsetBytes, Int64, Int64 numCopyBytes)

Copies data from the OracleBFile object to another object.

IsEqual(OracleBFile bfile)

Compares the OracleBFile object to another OracleBFile object and returns true if both objects refer to the same external file.

static MaxSize()

Returns the maximum number of bytes a BFILE can contain.

OpenFile()

Opens the BFILE specified by the directory and filename of the OracleBFile object.

Search(byte[] searchVal, Int64 offset, Int64 occurrence)

Searches for a binary pattern in the OracleBFile object.

Methods

CopyTo (OracleLob lob)

CopyTo (OracleLob lob, long destOffsetBytes)

CopyTo (Int64 srcOffsetBytes, OracleLob lob, long destOffsetBytes, long, long numCopyBytes)

Copies data from the OracleBFile object to another object.

SetFileName(string directory, string filename)

Associates the OracleBFile object to a different BFILE. To update the database, call the Update() method of the OracleDataAdapter.

Properties

IsEmpty {get;}

Indicates whether the BFILE is empty.

IsOpen {get;}

Indicates whether the BFILE has been opened by the OracleBFile object.

DirectoryName {get; set;}

Specifies the directory of the BFILE.

FileName {get; set;}

Specifies the filename of the BFILE.

Position {get; set;}

Specifies the current read position in the BFILE stream.

Properties

IsNull {get;}

Indicates whether the OracleBFile is a null stream.

DirectoryName {get;}

Returns the directory of the BFILE.

FileName {get;}

Returns the filename of the BFILE.

Position {get;}

Returns the current read position in the BFILE stream.

Oracle Binary

The OracleBinary structure is common to both the ODP.NET and Microsoft data providers. It represents a variable-length stream of binary data stored in an Oracle database.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Fields Fields
Methods

ToString()

Returns a hash code for the OracleBinary object. This method overrides the System.Object implementation.

Methods
Properties Properties

Oracle Boolean [Microsoft Only]

The OracleBoolean structure is specific to the Microsoft data provider. It represents the value returned from a database comparison of Oracle data types and provides methods to work with and manipulate the result.

The following table outlines the implementation.

ODP.NET Microsoft
N/A Fields

static False

A false value that can be assigned to the value of an OracleBoolean structure.

static Null

A null value that can be assigned to the value of an OracleBoolean structure.

static One

The value of one that can be assigned to the value of an OracleBoolean structure.

static True

A true value that can be assigned to the value of an OracleBoolean structure.

static Zero

The value of 0 that can be assigned to the value of an OracleBoolean structure.

  Methods

static And(OracleBoolean val1, OracleBoolean val2)

Returns the bitwise AND of two OracleBoolean structures.

CompareTo(object obj)

Returns the relative value of an OracleBoolean structure and a specified object.

Equals(object)

Returns a value indicating whether an OracleBoolean structure is equal to an object.

Equals(OracleBoolean val1, OracleBoolean val2)

Returns a value indicating whether two OracleBoolean structures are equal.

GetHashCode()

Returns the hash code for the OracleBoolean structure.

static NotEquals(OracleBoolean val1, OracleBoolean val2)

Returns a value indicating whether two OracleBoolean structures are not equal.

static OnesComplement(OracleBoolean val)

Returns the one's complement of the specified OracleBoolean structure.

static Or(OracleBoolean val1, OracleBoolean val2)

Returns bitwise OR of two OracleBoolean structures.

static Parse(string val)

Converts a string representation of a logical value to an OracleBoolean structure.

ToString()

Returns the value of the OracleBoolean structure as a string.

static XOr(OracleBoolean val1, OracleBoolean val2)

Returns the bitwise exclusive-OR of two OracleBoolean structures.

  Properties

IsFalse {get;}

Indicates whether the structure value is false.

IsNull {get;}

Indicates whether the structure value is null.

IsTrue {get;}

Indicates whether the structure value is true.

Value {get;}

Gets the value of the structure

  Operators

&

Returns the bitwise AND of two OracleBoolean structures.

|

Returns the bitwise OR of two OracleBoolean structures.

==

Returns a value indicating whether two OracleBoolean structures are equal.

^

Returns the bitwise exclusive-OR of two OracleBoolean structures.

false

Used to test whether an OracleBoolean structure is false.

!=

Returns a value indicating whether two OracleBoolean structures are not equal.

!

Returns the NOT of an OracleBoolean structure.

~

Returns the one's complement of an OracleBoolean structure.

true

Used to test whether an OracleBoolean structure is false.

bool

Converts an OracleBoolean structure to a bool.

OracleBoolean

Converts an OracleNumber, string, or bool value to an OracleBoolean structure.

Oracle DATE

The ODP.NET and Microsoft providers both have structures that support the Oracle DATE data types. The ODP.NET provider has the OracleDate structure while the Microsoft provider has the OracleDateTime structure.

The following table outlines the differences between the implementations.

ODP.NET (OracleDate) Microsoft (OracleDateTime)
Fields Fields
Methods

GetDaysBetween(OracleDate val)

Returns the number of days between the OracleDate structure and a specified OracleDate structure.

ToOracleTimeStamp()

Returns the OracleDate structure as its equivalent OracleTimeStamp structure.

static GetSysDate()

Returns an OracleDate structure for the current date and time.

Methods
Properties

BinData() {get;}

A byte array representing the structure in Oracle internal format.

Properties

Millisecond

Returns the millisecond part of the OracleDateTime structure.

Oracle LOB

The ODP.NET and Microsoft providers both have classes to support the Oracle LOB data types.

The ODP.NET provider has the OracleBlob class that represents the Oracle BLOB data type and the OracleClob class that represents the Oracle CLOB or NCLOB data type. The Microsoft data provider has the OracleLob class that represents Oracle LOB data—BLOB, CLOB, or NCLOB.

The following table outlines the differences between the implementations.

ODP.NET (OracleBlob and OracleClob) Microsoft (OracleLob)
Fields

static MaxSize()

Returns the maximum number of bytes the LOB can hold.

Fields

Null

Represents a null OracleLob object.

Methods

BeginChunkWrite()

Opens the LOB for writing. Server-side processes do not execute until EndChunkWrite is called. This method is used with the EndChunkWrite method to improve performance.

Compare(Int64 srcOffset, lob, Int64 destOffset, Int64 numCompareBytes

Returns a value indicating the relative value of the LOB to a specified object.

CopyTo (lob)

CopyTo (lob, Int64 destOffsetBytes)

CopyTo (Int64 srcOffsetBytes, lob, Int64 destOffsetBytes, Int64 numCopyBytes)

Copies data from the OracleBFile object to another object.

EndChunkWrite()

Closes the LOB for writing and allows server-side processes to execute. This method is used with the BeginChunkWrite method to improve performance.

IsEqual(lob)

Returns a value indicating whether the LOB is equal to the specified LOB.

Search(byte[] pattern, Int64 offsetBytes, Int64 occurrence)

Returns the absolute offset of a binary pattern within the LOB.

Methods

BeginBatch

Opens the LOB for writing. Server-side processes do not execute until EndBatch is called. This method is used with the EndBatch method to improve performance.

EndBatch

Closes the LOB for writing and allows server-side processes to execute. This method is used with the BeginBatch method to improve performance.

Properties

IsEmpty {get;}

Indicates whether the LOB is empty.

IsInChunkWriteMode {get;}

Indicates whether the LOB as been opened using BeginChunkWrite.

IsNCLOB {get;} [OracleClob only]

Indicates whether the LOB is a NCLOB.

OptimumChunkSize {get;}

Indicates the minimum number of bytes to retrieve or send from the server during a read or write operation.

Properties

ChunkSize {get;}

Indicates the minimum number of bytes to retrieve or send from the server during a read or write operation.

IsBatched {get;}

Indicates whether the LOB as been opened using BeginBatch.

IsNull {get;}

Indicates whether the LOB is a null stream

LobType {get;}

Returns the LOB type as an OracleType value.

Oracle NUMBER

The ODP.NET and Microsoft providers both have classes to support the Oracle NUMBER data type. The ODP.NET provider has an OracleDecimal structure while the Microsoft provider has an OracleNumber structure.

The following table outlines the differences between the implementations.

ODP.NET (OracleDecimal) Microsoft (OracleNumber)
Field

static NegativeOne

Returns the value -1.

Field

static E

Returns the value e-2.718.

staic MinusOne

Returns the value -1.

Methods

static AdjustScale(OracleDecimal val, int digits, bool round)

Returns a new OracleDecimal structure with the specified number of digits.

static ConvertToPrecScale(OracleDecimal val, int precision, int scale)

Returns a new OracleDecimal structure with the specified precision and scale.

static Mod(OracleDecimal val, OracleDecimal divider)

Returns the modulus resulting from dividing the first OracleDecimal by the second OracleDecimal.

Methods

static Modulo(OracleNumber val, OracleNumber divider)

Returns the modulus resulting from dividing the first OracleNumber by the second OracleNumber.

Properties

BinData {get;}

A byte array representing the structure in Oracle internal format.

Format {get; set;}

Specifies the format to be used by the ToString() method.

IsInt {get;}

Indicates whether the structure represents an integer.

IsPositive {get;}

Indicates whether the structure value is greater than 0.

IsZero {get;}

Indicates whether the structure value is 0.

Properties

Oracle INTERVAL

The ODP.NET and Microsoft providers both have classes to support the Oracle INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH data types.

The ODP.NET provider has the OracleIntervalDS class that represents the Oracle INTERVAL DAY TO SECOND data type and the OracleIntervalYM class that represents the Oracle INTERVAL YEAR TO MONTH data type.

The Microsoft provider has the OracleTimeSpan class that represents the Oracle INTERVAL DAY TO SECOND data type and the OracleMonthSpan class that represents the Oracle INTERVAL YEAR TO MONTH data type.

The following table outlines the differences between the implementations of the OracleIntervalDS structure and the OracleTimeSpan structure.

ODP.NET (OracleIntervalDS) Microsoft (OracleTimeSpan)
Fields

static Zero

Represents a 0 value for the OracleIntervalDS structure.

Fields
Methods Methods
Properties

BinData {get;}

A byte array representing the structure in Oracle internal format.

Nanoseconds {get;}

The nanosecond part of the structure.

TotalDays {get;}

The number of days part of the structure.

Properties
Operators

+

Adds one OracleIntervalDS structure to another.

-

Subtracts one OracleIntervalDS structure from another.

-

Negates the structure

*

Multiplies the structure by a number.

/

Divides the structure by a number.

Operators

The following table outlines the differences between the implementations of the OracleIntervalYM structure and the OracleMonthSpan structure.

ODP.NET (OracleIntervalYM) Microsoft (OracleMonthSpan)
Fields

static Zero

Represents a 0 value for the OracleIntervalYM structure.

Fields
Methods Methods
Properties

BinData {get;}

A byte array representing the structure in Oracle internal format.

Months {get;}

The month part of the structure.

TotalYears {get;}

The number of years for the time period in the structure.

Years {get;}

The year part of the structure.

Properties
Operators

+

Adds one OracleIntervalYM structure to another.

-

Subtracts one OracleIntervalYM structure from another.

-

Negates the structure.

*

Multiplies the structure by a number.

/

Divides the structure by a number.

Operators

Oracle REF CURSOR [ODP.NET Only]

The OracleRefCursor class is specific to the ODP.NET data provider. It represents the Oracle REF CURSOR data type.

The following table outlines the implementation.

ODP.NET Microsoft
Constructors N/A
Methods

Dispose()

Releases resources allocated to the OracleRefCursor object.

GetDataReader()

Returns an OracleDataReader for the REF CURSOR.

 
Properties

Connection {get;}

Indicates the OracleConnection used to fetch the REF CURSOR data.

 

Oracle String

The OracleString structure is common to both the ODP.NET and Microsoft data providers. It represents a variable-length stream of characters stored in an Oracle database.

The following table outlines the differences between the implementations.

ODP.NET Microsoft
Fields Fields

static Empty

An empty string that can be assigned to the structure.

Methods

Clone()

Returns a copy of the OracleString instance.

GetNonUnicodeBytes()

Returns a byte array for the OracleString instance using the client character set format.

GetUnicodeBytes()

Returns a byte array for the OracleString instance in Unicode format.

Methods
Properties

IsCaseIgnored {get; set;}

Specifies whether case should be ignored for string comparisons.

Properties

Oracle TimeStamp [ODP.NET Only]

The OracleTimeStamp, OracleTimeStampLTZ and the OracleTimeStampTZ structures are specific to the ODP.NET data provider and provider support for Oracle TIMESTAMP data types.

The OracleTimeStamp structure represents the Oracle TIMESTAMP data type. The OracleTimeStampLTZ structure represents the Oracle TIMESTAMP WITH LOCAL TIME ZONE data type. The OracleTimeStampTZ structure represents the Oracle TIMESTAMP WITH TIME ZONE data type.

The following table outlines the implementation of the three structures.

ODP.NET (OracleTimeStamp, OracleTimeStampLTZ, and OracleTimeStampTZ) Microsoft
Constructors

OracleTimeStamp(DateTime dateTimeVal)

OracleTimeStamp(DateTime dateTimeVal, string timeZone) [OracleTimeStampTZ only]

OracleTimeStamp(string stringVal)

OracleTimeStamp(int year, int month, int day)

OracleTimeStamp(int year, int month, int day, string timeZone) [OracleTimeStampTZ only]

OracleTimeStamp(int year, int month, int day, int hour, int minute, int second)

OracleTimeStamp(int year, int month, int day, int hour, int minute, int second, string timeZone) [OracleTimeStampTZ only]

OracleTimeStamp(int year, int month, int day, int hour, int minute, int second, double millisecond)

OracleTimeStamp(int year, int month, int day, int hour, int minute, int second, double millisecond, string timeZone) [OracleTimeStampTZ only]

OracleTimeStamp(int year, int month, int day, int hour, int minute, int second, int nanosecond)

OracleTimeStamp(int year, int month, int day, int hour, int minute, int second, int nanosecond, string timeZone) [OracleTimeStampTZ only]

OracleTimeStamp(byte[] oracleInternalStructure)

N/A
Fields

static MaxValue

The maximum value for the structure.

static MinValue

The minimum value for the structure.

static Null

A null value that can be assigned to the structure.

 
Static Methods

Equals(val1, val2)

Returns a value indicating whether the values of the specified structures are equal.

GetLocalTimeZoneName() [OracleTimeStampLTZ only]

Returns the local time zone name for the client.

GetLocalTimeZoneOffset() [OracleTimeStampLTZ only]

Returns the local time zone offset for the client.

GetSysDate()

Returns a structure that represents the current date and time.

GreaterThan(val1, val2)

Determines if the first structure value is greater than the second.

GreaterThanOrEqual(val1, val2)

Determines if the first structure value is greater than or equal to the second.

LessThan(val1, val2)

Determines if the first structure value is less than the second.

LessThanOrEqual(val1, val2)

Determines if the first structure value is less than or equal to the second.

NotEquals(val1, val2)

Determines if two structure values are not equal to each other.

Parse(string val)

Creates a structure from a specified string.

SetPrecision(val1, int fracSecPrec)

Returns a new structure with the specified fractional second precision.

 
Methods

AddDays(double days)

Adds the specified number of days to the structure.

AddHours((double hours)

Adds the specified number of hours to the structure.

AddMilliseconds((double milliseconds)

Adds the specified number of milliseconds to the structure.

AddMinutes(double minutes)

Adds the specified number of minutes to the structure.

AddMonths(long months)

Adds the specified number of months to the structure.

AddNanoseconds(long nanoseconds)

Adds the specified number of nanoseconds to the structure.

AddSeconds(double seconds)

Adds the specified number of seconds to the structure.

AddYears(int years)

Adds the specified number of years to the structure.

CompareTo(object obj)

Returns a value indicating the relative value of the structure to a specified object.

Equals(object obj)

Returns a value indicating whether the structure has the same date and time as a specified object.

GetHashCode()

Returns a hash code for the structure.

GetDaysBetween(val)

Subtracts a value from the structure and returns the result as an OracleIntervalDS structure.

GetTimeZoneOffset() [OracleTimeStampTZ only]

Returns the time zone information in hours and minutes of the current structure.

GetYearsBetween(val)

Subtracts a value from the structure and returns the result as an OracleIntervalYM structure.

ToLocalTime() [OracleTimeStampTZ only]

Converts the structure to local time.

ToOracleDate()

Converts the structure to an OracleDate structure.

ToOracleTimeStamp() [OracleTimeStampLTZ and OracleTimeStampTZ only]

Converts the structure to an OracleTimeStamp structure.

ToOracleTimeStampLTZ() [OracleTimeStamp and OracleTimeStampTZ only]

Converts the structure to an OracleTimeStampLTZ structure.

ToOracleTimeStampTZ() [OracleTimeStamp and OracleTimeStampLTZ only]

Converts the structure to an OracleTimeStampTZ structure.

ToString()

Converts the structure to a string. This method overrides the implementation in System.Object.

ToUniversalTime() [OracleTimeStampLTZ and OracleTimeStampTZ only]

Converts the local time to Coordinated Universal Time (UTC) as an OracleTimeStampTZ structure.

 
Properties

BinData {get;}

A byte array representing the structure in Oracle internal format.

Day {get;}

The day part of the structure.

IsNull {get;}

Indicates whether the structure has a null value.

Hour {get;}

The hour part of the structure.

Millisecond {get;}

The millisecond part of the structure.

Minute {get;}

The minute part of the structure.

Month {get;}

The month part of the structure.

Nanosecond {get;}

The nanosecond part of the structure.

Second {get;}

The second part of the structure.

TimeZone {get;} [OracleTimeStampTZ only]

The time zone of the structure.

Value {get;}

The value of the structure as a DateTime data type.

Year {get;}

The year part of the structure.

 
Operators

+

Adds the specified value to the structure.

==

Determines if two structure values are equal.

>

Determines if the first of two structure values is greater than the second.

>=

Determines if the first of two structure values is greater than or equal to the second.

!=

Determines if two structure values are not equal.

<

Determines if the first of two structure values is less than the second.

<=

Determines if the first of two structure values is less than or equal to the second.

-

Subtracts the specified value from the structure.

 

Oracle XMLType [ODP.NET Only]

The ODP.NET provider has two classes to support the Oracle XMLType data type—OracleXmlType and OracleXmlStream.

The OracleXmlType class represents the Oracle XMLType data type to support both XML documents without XML schemas as well as XML fragments. The following table outlines the implementation.

ODP.NET Microsoft
Constructors

OracleXmlType(OracleClob clob)

OracleXmlType(OracleConnection conn, string xmlData)

OracleXmlType(OracleConnection conn, XmlReader xr)

OracleXmlType(OracleConnection conn, XmlDocument xd)

N/A
Methods

Clone()

Creates a copy of the OracleXmlType object.

Dispose()

Releases resources allocated to the OracleXmlType object.

Extract(string xpathExpr, string namespaceMap)

Extract(string xpathExpr, XmlNameSpaceManager nsMgr)

Extracts a subset of the data stored in the OracleXmlType object based on an XPath expression.

GetStream()

Returns an OracleXmlStream for the data stored in the OracleXmlType object.

GetXmlDocument()

Returns an XmlDocument of the data stored in the OracleXmlType object.

GetXmlReader()

Returns an XmlTextReader for the data stored in the OracleXmlType object.

IsExists(string xpathExpr, string namespaceMap)

IsExists(string xpathExpr, XmlNameSpaceManager nsMgr)

Returns whether nodes identified by an XPath expression exists in the OracleXmlType object.

Transform(OracleXmlType xslDoc, string paramMap)

Transform(string xslDoc, string paramMap)

Transforms the OracleXmlType object into another OracleXmlType object using an XSL document. The paramMap parameter is ignored in the current release.

Update(string xpathExpr, string nsMap, string val)

Update(string xpathExpr, XmlNameSpaceMgr xnsMgr, string val)

Update(string xpathExpr, string nsMap, OracleXmlType val)

Update(string xpathExpr, XmlNameSpaceMgr xnsMgr, OracleXmlType val)

Updates the node or fragment in the OracleXmlType object as specified by an XPath expression.

 
Properties

Connection {get;}

The OracleConnection used to retrieve XML data into the OracleXmlType.

IsEmpty {get;}

Indicates whether the OracleXmlType is empty.

IsFragment {get;}

Indicates whether the OracleXmlType is a collection of XML elements rather than a well-formed XML document.

IsSchemaBased {get;}

Indicates whether the OracleXmlType is based on an XML schema.

Value {get;}

Returns a string containing the XML data in the OracleXmlType.

 

The OracleXmlStream class is specific to the ODP.NET data provider. It represents a read-only stream of data stored in an OracleXmlType object. The class extends System.IO.Stream.

The following table outlines the implementation.

ODP.NET Microsoft
Constructor

OracleXmlStream(OracleXmlType xmlType)

N/A
Methods

Clone()

Creates a copy of the OracleXmlStream object.

Close()

Closes the OracleXmlStream and releases resources allocated to the object.

Dispose()

Releases resources allocated to the OracleXmlStream object.

 
Properties

CanRead {get;}

Indicates whether the XML stream can be read.

CanSeek {get;}

Indicates whether the XML stream supports backward and forward seek operations.

Connection {get;}

The OracleConnection used to retrieve the XML data.

Length {get;}

The number of bytes in the XML stream.

Position {get; set;}

The position in the stream in bytes.

Value {get;}

The XML data as a string, starting with the first character in the stream.

 
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.