Comparing the Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET
2010-07-28 16:51 Tracy. 阅读(956) 评论(1) 编辑 收藏 举报.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:
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" ASTYPE T_CURSOR IS REF CURSOR;PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR);END GET_EMPLOYEES;CREATE OR REPLACE PACKAGE BODY "HR"."GET_EMPLOYEES" ASPROCEDURE GetEmployees (cur_Employees OUT T_CURSOR)ISBEGIN 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 parameterOracleRefCursor refcur = (OracleRefCursor)cmd.Parameters["cur_Employees"].Value;// get the DataReader using the OracleRefCursorOracleDataReader 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:
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 parameterOracleRefCursor refcur = (OracleRefCursor)cmd.Parameters["cur_Employees"].Value;OracleDataAdapter da = new OracleDataAdapter("", conn);DataTable dt = new DataTable();// fill the DataTable using the OracleRefCursorda.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:
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:
An asterisk can be specified for the column name to map all columns that can potentially lose data:
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 columnstring[] 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 commandOracleCommand 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 arrayscmd.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 commandOracleConnection 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:
- Specify the CollectionType property of the OracleParameter as OracleCollectionType.PLSQLAssociativeArray.
- Specify the maximum number of elements in the array using the Size property of each OracleParameter.
- Specify the size of each element in the array using the ArrayBindSize property.
- Set the ArrayBindStatus for each input parameter value to OracleParameterStatus.Success.
- 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 eventconn.Failover += new OracleFailoverEventHandler(OnFailover);// handler for the Failover eventpublic 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:
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
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 OracleCommandFetchSize property value. InitialLOBFetchSize {get; set;} Specifies the size that the OracleDataReader initially fetches for LOB columns. The default value is the OracleCommandInitialLOBFetchSize property value. InitialLONGFetchSize {get; set;} Specifies the size that the OracleDataReader initially fetches for LONG and LONG RAW columns. The default value is the OracleCommandInitialLONGFetchSize 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: |
|
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. |
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2010/07/28/1787159.html