Oracle Data Provider for .NET : Part I

Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for the Oracle database.

ODP.NET uses Oracle native APIs to offer fast and reliable access to Oracle data and features from any .NET application.

For programmers using Oracle Provider for OLE DB, ADO (ActiveX Data Objects) provides an automation layer that exposes an easy programming model. ADO.NET provides a similar programming model, but without the automation layer, for better performance. More importantly, the ADO.NET model allows native providers such as ODP.NET to expose Oracle-specific features and datatypes.

ODP.NET Assembly

Oracle.DataAccess.dll assembly provides two namespaces:

• The Oracle.DataAccess.Client namespace contains ODP.NET classes and enumerations.
• The Oracle.DataAccess.Types namespace contains the Oracle Data Provider for .NET Types (ODP.NET Types).

Oracle.DataAccess.Client Classes and Enumerations

This namespace is the Oracle Data Provider for .NET (ODP.NET).

Table 1.1 lists the client classes:

Table 1-1 Oracle.DataAccess.Client Classes  

Class	Description
OracleCommand	An OracleCommand object represents a SQL command, a stored procedure, or a table name.
OracleCommandBuilder	An OracleCommandBuilder object provides automatic SQL generation for the OracleDataAdapter when updates are made to the database.
OracleConnection	An OracleConnection object represents a connection to an Oracle database.
OracleDataAdapter	An OracleDataAdapter object represents a data provider object that communicates with the DataSet.
OracleDataReader	An OracleDataReader object represents a forward-only, read-only, in-memory result set.
OracleError	The OracleError object represents an error reported by an Oracle database.
OracleErrorCollection	An OracleErrorCollection object represents a collection of OracleErrors.
OracleException	The OracleException object represents an exception that is thrown when Oracle Data Provider for .NET encounters an error.
OracleFailoverEventArgs	The OracleFailoverEventArgs object provides event data for the OracleConnection.Failover event.
OracleFailoverEventHandler delegate	The OracleFailoverEventHandler delegate represents the signature of the method that handles the OracleConnection.Failover event
OracleGlobalization	The OracleGlobalization class is used to obtain and set the Oracle globalization settings of the session, thread, and local computer (read-only).
OracleInfoMessageEventHandler delegate	The OracleInfoMessageEventHandler delegate represents the signature of the method that handles the OracleConnection.InfoMessage event.
OracleInfoMessageEventArgs	The OracleInfoMessageEventArgs object provides event data for the OracleConnection.InfoMessage event.
OracleParameter	An OracleParameter object represents a parameter for an OracleCommand.
OracleParameterCollection	An OracleParameterCollection object represents a collection of OracleParameters.
OracleRowUpdatedEventArgs	The OracleRowUpdatedEventArgs object provides event data for the OracleDataAdapter.RowUpdated event.
OracleRowUpdatedEventHandler	The OracleRowUpdatedEventHandler delegate represents the signature of the method that handles the OracleDataAdapter.RowUpdated event.
OracleRowUpdatingEventArgs	The OracleRowUpdatingEventArgs object provides event data for the OracleDataAdapter.RowUpdating event.
OracleRowUpdatingEventHandler	The OracleRowUpdatingEventHandler delegate represents the signature of the method that handles the OracleDataAdapter.RowUpdating event.
OracleTransaction	An OracleTransaction object represents a local transaction.
OracleXmlQueryProperties	An OracleXmlQueryProperties object represents the XML properties used by the OracleCommand class when the XmlCommandType property is Query.
OracleXmlSaveProperties	An OracleXmlSaveProperties object represents the XML properties used by the OracleCommand class when the XmlCommandType property is Insert, Update, or Delete.

Table 1.2 lists the client enumerations

Table 1-2 Oracle.DataAccess.Client Enumerations  

Enumeration	Description
FailoverEvent	FailoverEvent enumerated values are used to explicitly specify the state of the failover.
FailoverReturnCode	FailoverReturnCode enumerated values are passed back by the application to the ODP.NET provider to request a retry in case of a failover error or to continue in case of a successful failover.
FailoverType	FailoverType enumerated values are used to indicate the type of failover event that was raised.
OracleDbType	OracleDbType enumerated values are used to explicitly specify the OracleDbType of an OracleParameter.
OracleParameterStatus	The OracleParameterStatus enumeration type indicates whether a NULL value is fetched from a column, whether truncation has occurred during the fetch, or whether a NULL value is to be inserted into a database column.
OracleXmlCommandType	The OracleXmlCommandType enumeration specifies the values that are allowed for the OracleXmlCommandType property of the OracleCommand class.

Oracle.DataAccess.Types Classes and Structures

The Oracle.DataAccess.Types namespace provides classes and structures for Oracle native types that can be used with Oracle Data Provider for .NET.

Table 1.3 lists the types structures: 

Table 1-3 Oracle.DataAccess.Types Structures  

Structure	Description
OracleBinary	The OracleBinary structure represents a variable-length stream of binary data.
OracleDate	The OracleDate structure represents the Oracle DATE datatype.
OracleDecimal	The OracleDecimal structure represents an Oracle NUMBER in the database or any Oracle numeric value.
OracleIntervalDS	The OracleIntervalDS structure represents the Oracle INTERVAL DAY TO SECOND datatype.
OracleIntervalYM	The OracleIntervalYM structure represents the Oracle INTERVAL YEAR TO MONTH datatype.
OracleString	The OracleString structure represents a variable-length stream of characters.
OracleTimeStamp	The OracleTimeStamp structure represents the Oracle TimeStamp datatype.
OracleTimeStampLTZ	The OracleTimeStampLTZ structure represents the Oracle TIMESTAMP WITH LOCAL TIME ZONE data type.
OracleTimeStampTZ	The OracleTimeStampTZ structure represents the Oracle TIMESTAMP WITH TIME ZONE data type.

Type Exceptions are thrown only by ODP.NET type structures.

Table 1.4 lists the type exceptions:

Table 1-4 Oracle.DataAccess.Types Exceptions

Exception	Description
OracleTypeException	The OracleTypeException object is the base exception class for handling exceptions that occur in the ODP.NET Type classes.
OracleNullValueException	The OracleNullValueException represents an exception that is thrown when trying to access an ODP.NET Type structure that is null.
OracleTruncateException	The OracleTruncateException class represents an exception that is thrown when truncation in an ODP.NET Type class occurs.

Table 1.5 lists the types classes:

Table 1-5 Oracle.DataAccess.Types Classes 

Class	Description
OracleBFile	An OracleBFile is an object that has a reference to BFILE data. It provides methods for performing operations on BFiles.
OracleBlob	An OracleBlob object is an object that has a reference to BLOB data. It provides methods for performing operations on BLOBs.
OracleClob	An OracleClob is an object that has a reference to CLOB data. It provides methods for performing operations on CLOBs.
OracleRefCursor	An OracleRefCursor object represents an Oracle REF CURSOR.
OracleXmlStream	An OracleXmlStream object represents a sequential read-only stream of XML data stored in an OracleXmlType object.
OracleXmlType	An OracleXmlType object represents an Oracle XmlType instance.

Using ODP.NET in a Simple Application

The following is a very simple C# application that connects to an Oracle database and displays its version number before disconnecting.

using System;
using Oracle.DataAccess.Client;
class Example
{
OracleConnection con;
void Connect()
{
con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle";
con.Open();
Console.WriteLine("Connected to Oracle" + con.ServerVersion);
}
void Close()
{
con.Close();
con.Dispose();
}
static void Main()
{
Example example = new Example();
example.Connect();
example.Close();
}
}

Continue article...