Skip Headers
Oracle® Data Provider for .NET Developer's Guide
11g Release 1 (11.1)

Part Number B28375-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

ADO.NET 2.0 Features

Oracle Data Provider for .NET 10.2.0.2 or higher supports Microsoft ADO.NET 2.0 APIs and interfaces, for Oracle8i Database release 3 (8.1.7) and later.

This section contains the following topics:

About ADO.NET 2.0

ADO.NET 2.0 is a Microsoft specification that provides data access features designed to work together for provider independence, increased component reuse, and application convertibility. Additional features make it easier for an application to dynamically discover information about the data source, schema, and provider.

Note:

Using ODP.NET with Microsoft ADO.NET 2.0 requires ADO.NET 2.0- compliant ODP.NET.

See Also:

ADO.NET in the MSDN Library

Backward Compatibility for ADO.NET

For writing provider-independent, generic data access code, ADO.NET 1.x uses interfaces. For the same purpose, ADO.NET 2.0 provides an inheritance-based approach, while continuing to maintain interfaces from ADO.NET 1.x for backwards compatibility.

ODP.NET for ADO.NET 2.0 supports backward compatibility so that ADO.NET 1.x APIs can be used.

This guide provides the declarations for both ADO.NET 2.0 and ADO.NET 1.x when appropriate.

Base Classes and Provider Factory Classes

With ADO.NET 2.0, data classes derive from the base classes defined in the System.Data.Common namespace. Developers can create provider-specific instances of these base classes using provider factory classes.

Provider factory classes allow generic data access code to access multiple data sources with a minimum of data source-specific code. This reduces much of the conditional logic currently used by applications accessing multiple data sources.

Using Oracle Data Provider for .NET, the OracleClientFactory class can be returned and instantiated, enabling an application to create instances of the following ODP.NET classes that inherit from the base classes:

Table 3-2 ODP.NET Classes that Inherit from ADO.NET 2.0 Base Classes

ODP.NET Classes Inherited from ADO.NET 2.0 Base Class

OracleClientFactory

DbProviderFactory

OracleCommand

DbCommand

OracleCommandBuilder

DbCommandBuilder

OracleConnection

DbConnection

OracleConnectionStringBuilder

DbConnectionStringBuilder

OracleDataAdapter

DbDataAdapter

OracleDataReader

DbDataReader

OracleDataSourceEnumerator

DbDataSourceEnumerator

OracleException

DbException

OracleParameter

DbParameter

OracleParameterCollection

DbParameterCollection

OracleTransaction

DbTransaction


In general, applications still require Oracle-specific connection strings, SQL or stored procedure calls, and declare that a factory from Oracle.DataAccess.Client is used.

Connection String Builder

The OracleConnectionStringBuilder class makes creating connection strings less error-prone and easier to manage.

Using this class, developers can employ a configuration file to provide the connection string and/or dynamically set the values though the key/value pairs. One example of a configuration file entry follows:

<configuration>
   <connectionStrings> 
<add name="Publications" providerName="Oracle.DataAccess.Client" 
           connectionString="User Id=scott;Password=tiger;Data Source=inst1" />
   </connectionStrings> 
</configuration>

Connection string information can be retrieved by specifying the connection string name, in this example, Publications. Then, based on the providerName, the appropriate factory for that provider can be obtained. This makes managing and modifying the connection string easier. In addition, this provides better security against string injection into a connection string.

Data Source Enumerator

The data source enumerator enables the application to generically obtain a collection of the Oracle data sources that the application can connect to.

Support for Schema Discovery

ADO.NET 2.0 exposes five different types of metadata collections through the OracleConnection.GetSchema API. This permits application developers to customize metadata retrieval on an individual-application basis, for any Oracle data source. Thus, developers can build a generic set of code to manage metadata from multiple data sources.

The following types of metadata are exposed:

  • MetaDataCollections

    A list of metadata collections that is available from the data source, such as tables, columns, indexes, and stored procedures.

  • Restrictions

    The restrictions that apply to each metadata collection, restricting the scope of the requested schema information.

  • DataSourceInformation

    Information about the instance of the database that is currently being used, such as product name and version.

  • DataTypes

    A set of information about each data type that the database supports.

  • ReservedWords

    Reserved words for the Oracle query language.

User Customization of Metadata

ODP.NET provides a comprehensive set of database schema information. Developers can extend or customize the metadata that is returned by the GetSchema method on an individual application basis.

To do this, developers must create a customized metadata file and provide the file name to the application as follows:

  1. Create a customized metadata file and put it in the CONFIG subdirectory where the .NET framework is installed. This is the directory that contains machine.config and the security configuration settings.

    This file must contain the entire set of schema configuration information, not just the changes. Developers provide changes that modify the behavior of the schema retrieval to user-specific requirements. For instance, a developer can filter out internal database tables and just retrieve user-specific tables

  2. Add an entry in the app.config file of the application, similar to the following, to provide the name of the metadata file, in name-value pair format.

    <oracle.dataaccess.client>
      <settings>
        <add name="MetaDataXml" value="CustomMetaData.xml" />
      </settings>
    </oracle.dataaccess.client>
    
    

When the GetSchema method is called, ODP.NET checks the app.config file for the name of the customized metadata XML file. First, the GetSchema method searches for an entry in the file with a element named after the provider, in this example, oracle.dataaccess.client. In this XML element, the value that corresponds to the name MetaDataXml is the name of the customized XML file, in this example, CustomMetaData.xml.

If the metadata file is not in the correct directory, then the application loads the default metadata XML file, which is part of ODP.NET.

See Also:

"GetSchema"

System.Transactions Support

ODP.NET for .NET Framework 2.0 supports System.Transactions. When System.Transactions is used, the transaction becomes a distributed transaction (default) immediately, unless local transaction is specified.

If applications use System.Transactions, it is required that the "enlist" connection string attribute is set to either "true" (default) or "dynamic".

ODP.NET supports the following System.Transactions programming models for applications using distributed transactions.

Implicit Distributed Transaction Enlistment Using TransactionScope

The TransactionScope class provides a mechanism to write transactional applications where the applications do not need to explicitly enlist in distributed transactions.To accomplish this, the application uses the TransactionScope object to define the transactional code. Connections created within this transactional scope will enlist in a distributed transaction.Note that the application must call the Complete method on the TransactionScope object to commit the changes. Otherwise, the transaction is aborted by default.

// C#
 
using System;
using Oracle.DataAccess.Client;
using System.Data;
using System.Data.Common;
using System.Transactions;
 
class psfTxnScope
{
  static void Main()
  {
    int retVal = 0;
    string providerName = "Oracle.DataAccess.Client";
    string constr = 
           @"User Id=scott;Password=tiger;Data Source=oracle;enlist=true";
 
    // Get the provider factory.
    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
 
    try
    {
      // Create a TransactionScope object, (It will start an ambient
      // transaction automatically).
      using (TransactionScope scope = new TransactionScope())
      {
        // Create first connection object.
        using (DbConnection conn1 = factory.CreateConnection())
        {
          // Set connection string and open the connection. this connection 
          // will be automatically enlisted in a distributed transaction.
          conn1.ConnectionString = constr;
          conn1.Open();
 
          // Create a command to execute the sql statement.
          DbCommand  cmd1 = factory.CreateCommand();
          cmd1.Connection = conn1;
          cmd1.CommandText = @"insert into emp (empno, ename, job) values 
                                                     (1234, 'emp1', 'dev1')";
 
          // Execute the SQL statement to insert one row in DB.
          retVal = cmd1.ExecuteNonQuery();
          Console.WriteLine("Rows to be affected by cmd1: {0}", retVal);
 
          // Close the connection and dispose the command object.
          conn1.Close();
          conn1.Dispose();
          cmd1.Dispose();
        }
 
        // The Complete method commits the transaction. If an exception has
        // been thrown or Complete is not called then the transaction is 
        // rolled back.
        scope.Complete();
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex.StackTrace);
    }
  }
}

Explicit Distributed Transaction Enlistment Using CommittableTransaction

The instantiation of the CommittableTransaction object and the EnlistTransaction method provides an explicit way to create and enlist in a distributed transaction. Note that the application must call Commit or Rollback on the CommittableTransaction object.

// C#
 
using System;
using Oracle.DataAccess.Client;
using System.Data;
using System.Data.Common;
using System.Transactions;
 
class psfEnlistTransaction
{
  static void Main()
  {
    int retVal = 0;
    string providerName = "Oracle.DataAccess.Client";
    string constr = 
           @"User Id=scott;Password=tiger;Data Source=oracle;enlist=dynamic";
 
    // Get the provider factory.
    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
 
    try
    {
      // Create a committable transaction object.
      CommittableTransaction cmtTx = new CommittableTransaction();
 
      // Open a connection to the DB.
      DbConnection conn1 = factory.CreateConnection();
      conn1.ConnectionString = constr;
      conn1.Open();
 
      // enlist the connection with the commitable transaction.
      conn1.EnlistTransaction(cmtTx);
 
      // Create a command to execute the sql statement.
      DbCommand cmd1 = factory.CreateCommand();
      cmd1.Connection = conn1;
      cmd1.CommandText = @"insert into emp (empno, ename, job) values 
                                                     (1234, 'emp1', 'dev1')";
 
      // Execute the SQL statement to insert one row in DB.
      retVal = cmd1.ExecuteNonQuery();
      Console.WriteLine("Rows to be affected by cmd1: {0}", retVal);
 
      // commit/rollback the transaction.
      cmtTx.Commit();   // commits the txn.
      //cmtTx.Rollback(); // rolls back the txn.
 
      // close and dispose the connection
      conn1.Close();
      conn1.Dispose();
      cmd1.Dispose();
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex.StackTrace);
    }
  }
}

Local Transaction for System.Transactions Support

Beginning with Oracle Data Provider for .NET release 10.2.0.3, applications can use local transactions with System.Transactions. Previous versions of ODP.NET supported only distributed transactions with System.Transactions.

To use local transactions, either the PromotableTransaction registry entry must be created and set to "local" or the "Promotable Transaction" connection string attribute must set to "local".

If "local" is specified, the first connection opened in the TransactionScope uses a local transaction. If any subsequent connections are opened within the same TransactionScope, an exception is thrown. If there are connections already opened in the TransactionScope, and an OracleConnection with "Promotable Transaction=local" attempts to open within the same TransactionScope, an exception is thrown.

If "promotable" is specified, the first and all subsequent connections opened in the same TransactionScope enlist in the same distributed transaction.

If both the registry and the connection string attribute are used and set to different values, the connection string attribute overrides the registry entry value. If neither are set, "promotable" is used. This is the default value and is equivalent to previous versions of ODP.NET which only supported distributed transactions.

The registry entry for a particular version of ODP.NET applies for all applications using that version of ODP.NET.

Batch Processing Support

The OracleDataAdapter UpdateBatchSize property enables batch processing when the OracleDataAdapter.Update method is called. UpdateBatchSize is a numeric property that indicates how many DataSet rows to update the Oracle database for each round-trip.

This enables the developer to reduce the number of round-trips to the database.

ADO.NET 2.0 Only Classes and Class Members

In addition to classes which are ADO.NET 2.0 only, other ODP.NET classes that inherit from the System.Data.Common namespace include methods and properties which require ADO.NET 2.0.

The following classes are ADO.NET 2.0 only:

The following class members are ADO.NET 2.0 only: