Skip Headers
Oracle® Data Provider for .NET Developer's Guide
11g Release 1 (11.1.0.6.20)
E10927-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
 
Next
Next
 

ADO.NET 2.0 Features

Oracle Data Provider for .NET 10.2.0.2 or later supports Microsoft ADO.NET 2.0 APIs.

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-3 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 it is specified that the transaction be created as a local transaction, as described in "Local Transaction for System.Transactions Support".

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.


Note:

Microsoft Hotfix Needed

There is a known issue in Microsoft ADO.NET 2.0 that affects the BatchUpdate functionality.

To resolve this issue, both ODP.NET release 11.1 and a specific Microsoft hotfix must be installed on the same computer. The Microsoft hotfix is available for free download from the following site: http://support.microsoft.com/?id=916002

Without this fix, the BatchUpdate feature does not provide the correct error description for the failed rows in the DataSet. All errors in a batch are either appended to the exception message, if DbDataDataAdapter.ContinueUpdateOnError is false, or appended to the RowError property of the last updated row of the DataSet.

ODP.NET has been enhanced to use this hotfix and to populate the correct error description to the RowError property of the individual failed rows in a batch.



See Also:

"UpdateBatchSize"

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:

Bulk Copy Support

ODP.NET provides a Bulk Copy feature which enables applications to efficiently load large amounts of data from a table in one database to another table in the same or a different database.

The ODP.NET Bulk Copy feature uses a direct path load approach, which is similar to, but not the same as Oracle SQL*Loader. Using direct path load is faster that the conventional loading (using conventional SQL INSERT statements) as it formats Oracle data blocks and writes the data blocks directly to the data files. This eliminates considerable processing overhead.

The ODP.NET Bulk Copy feature can load data into 9i release 2, 10g release 1, 10g release 2, and 11g release 1 databases.

The ODP.NET Bulk Copy feature is subject to the same basic restrictions and integrity constraints for direct path loads, as discussed in the next few sections.

Restrictions on Oracle Bulk Copy of a Single Partition

  • The table that contains the partition cannot have any global indexes defined on it.

  • The tables that the partition is a member of cannot have referential and check constraints enabled.

  • Enabled triggers are not allowed.

Integrity Constraints Affecting Oracle Bulk Copy

During a Oracle bulk copy, some integrity constraints are automatically enabled or disabled, as follows:

Enabled Constraints

During an Oracle bulk copy, the following constraints are automatically enabled by default:

  • NOT NULL

  • UNIQUE

  • PRIMARY KEY (unique-constraints on not-null columns)

NOT NULL constraints are checked at column array build time. Any row that violates the NOT NULL constraint is rejected.

UNIQUE constraints are verified when indexes are rebuilt at the end of the load. The index is left in an Index Unusable state if it violates a UNIQUE constraint.

Disabled Constraints

During an Oracle bulk copy, the following constraints are automatically disabled by default:

  • CHECK constraints

  • Referential constraints (FOREIGN KEY)

If the EVALUATE CHECK_CONSTRAINTS clause is specified, then CHECK constraints are not automatically disabled. The CHECK constraints are evaluated during a direct path load and any row that violates the CHECK constraint is rejected.

Database Insert Triggers

Table insert triggers are disabled when a direct path load begins. After the rows are loaded and indexes rebuilt, any triggers that were disabled are automatically reenabled. The log file lists all triggers that were disabled for the load. There should be no errors reenabling triggers.

Unlike integrity constraints, insert triggers are not reapplied to the whole table when they are enabled. As a result, insert triggers do not fire for any rows loaded on the direct path. When using the direct path, the application must ensure that any behavior associated with insert triggers is carried out for the new rows.

Field Defaults

Default column specifications defined in the database are not available with direct path loading. Fields for which default values are desired must be specified with the DEFAULTIF clause. If a DEFAULTIF clause is not specified and the field is NULL, then a null value is inserted into the database.