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

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 Code Access Security

ODP.NET implements code access security through the OraclePermission class. This ensures that application code trying to access the database has the requisite permission to do so.

When a .NET assembly tries to access Oracle Database through ODP.NET, ODP.NET demands OraclePermission. The .NET runtime security system checks to see whether the calling assembly, and all other assemblies in the call stack, have OraclePermission granted to them. If all assemblies in the call stack have OraclePermission granted to them, then the calling assembly can access the database. If any one of the assemblies in the call stack does not have OraclePermission granted to it, then a security exception is thrown.

Configuring OraclePermission

The DemandOraclePermission configuration attribute is used to enable or disable OraclePermission demand for an ODP.NET API. The DemandOraclePermission value can be specified in the Windows registry or an individual application configuration file.

The following Windows registry key is used to configure the DemandOraclePermission configuration attribute:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version\DemandOraclePermission

Here Assembly_Version is the full assembly version number of Oracle.DataAccess.dll. The DemandOraclePermission key is of type REG_SZ. It can be set to either 1 (enabled) or 0 (disabled).

You can also enable OraclePermission demand for an individual application using its application configuration file. The following example enables the DemandOraclePermission property in an application configuration file:

<configuration>
  <oracle.dataaccess.client>
    <settings>
      <add name="DemandOraclePermission" value="1"/>
    </settings>
  </oracle.dataaccess.client>
</configuration>

An application or assembly can successfully access the database if OraclePermission has been added to the permission set associated with the assembly's code group. A system administrator can modify the appropriate permission set manually or by using the Microsoft .NET configuration tool (Mscorcfg.msc).

Administrators may also use an appropriate .NET Framework Tool, such as the Code Access Security Policy Tool (Caspol.exe), to modify security policy at the machine, user, and enterprise levels for including OraclePermission.

OracleConnection makes security demands using the OraclePermission object when OraclePermission demand has been enabled using DemandOraclePermission configuration attribute. Application developers should make sure that their code has sufficient permission before using OracleConnection.

Configuring OraclePermission for Web Applications with High or Medium Trust Levels

For Web applications operating under high or medium trust, OraclePermission needs to be configured in the appropriate web_TrustLevel.config file, so that the application does not encounter any security errors.

OraclePermission can be configured using the OracProvCfg tool. OraProvCfg.exe adds appropriate entries to the web_hightrust.config and web_mediumtrust.config files associated with the specified .NET framework version.The following example illustrates using the OraProvCfg tool for configuring OraclePermission in a .NET 2.0 Web application:

OraProvCfg.exe /action:config  /product:odp /component:oraclepermission
               /frameworkversion:v2.0.50727
               /providerpath:full_path_of_Oracle.DataAccess.dll

On running the preceding command, the following entry is added to the web_hightrust.config and web_mediumtrust.config files under the ASP.NET permission set:

<IPermission class="Oracle.DataAccess.Client.OraclePermission, Oracle.DataAccess,
Version=2.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" version="1"
Unrestricted="true" />

OraProvCfg can also be used to remove these entries from the .config files when required. The following example illustrates this:

OraProvCfg.exe /action:unconfig  /product:odp  /component:oraclepermission
               /frameworkversion:v2.0.50727
               /providerpath:full_path_of_Oracle.DataAccess.dll

Configuring OraclePermission for Windows Applications Running in a Partial Trust Environment

For Windows applications operating in a partial trust environment, the OraclePermission entry should be specified under the appropriate permission set in the security.config file. The security.config file is available in the %windir%\Microsoft.NET\Framework\{version}\CONFIG folder.

The following example specifies the OraclePermission entry for a .NET 2.0 Windows application:

<IPermission class="Oracle.DataAccess.Client.OraclePermission, Oracle.DataAccess,
 Version=2.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" version="1"
 Unrestricted="true" />

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 and Promotable Transactions

ODP.NET for .NET Framework 2.0 supports System.Transactions. A local transaction is created for the first connection opened in the System.Transactions scope to Oracle Database 11g release 1 (11.1), or higher. When a second connection is opened, this transaction is automatically promoted to a distributed transaction. This functionality provides enhanced performance and scalability.

Connections created within a transaction context, such as TransactionScope or ServicedComponent, can be established to different versions of Oracle Database. However, in order to enable the local transaction to be promotable, the following must be true:

  • The first connection in the transaction context must be established to an Oracle Database 11g release 1(11.1) instance or higher.

  • All connections opened within the transaction context must have the "Promotable Transaction" setting set to "promotable". If you try to open a subsequent connection in the same transaction context with the "Promotable Transaction" setting set to "local", an exception is thrown.

  • Promoting local transactions requires Oracle Services for Microsoft Transaction Server 11.1.0.7.20, or higher. If this requirement is not met, then a second connection request in the same transaction context throws an exception.

Setting "local" as the value of "PromotableTransaction" in the registry, configuration file (machine/Web/application), or the "Promotable Transaction" connection string attribute allows only one connection to be opened in the transaction context, which is associated with a local transaction. Such local transactions cannot be promoted.

For applications connecting to a pre-Oracle Database 11g release 1 (11.1) instance, refer to "Local Transaction Support for Older Databases". This section describes how ODP.NET behavior can be controlled using the "Promotable Transaction" setting.

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 Transaction Enlistment Using TransactionScope

The TransactionScope class provides a mechanism to write transactional applications where the applications do not need to explicitly enlist in 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 local transaction that can be promoted to a distributed transaction.

Note:

If the first connection is opened to a pre-Oracle Database 11g release 1 (11.1) instance, then the connection enlists as a distributed transaction, by default.

You can optionally create the transaction as a local transaction by using the procedure described in "Local Transaction Support for Older Databases". However, these transactions cannot be promoted to distributed transactions.

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 promotable local 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 Transaction Enlistment Using CommittableTransaction

The instantiation of the CommittableTransaction object and the EnlistTransaction method provides an explicit way to create and enlist in a 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 Support for Older Databases

If the first connection in a TransactionScope is opened to a pre-Oracle Database 11g release 1 (11.1) instance, then the connection creates a distributed transaction, by default. You can optionally have the fist connection create a local transaction by using the procedure described in this section.

To create local transactions in a System.Transactions scope, either the PromotableTransaction setting in the registry, machine/Web/application configuration file, or the "Promotable Transaction" connection string attribute must be 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.

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 than conventional loading (using conventional SQL INSERT statements). Conventional loading formats Oracle data blocks and writes the data blocks directly to the data files. Bulk Copy eliminates considerable processing overhead.

The ODP.NET Bulk Copy feature can load data into older Oracle databases.

See Also:

"System Requirements" to learn which versions of the Oracle Database ODP.NET interoperates with

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.

Data Types Supported by Bulk Copy

The data types supported by Bulk Copy are:

  • ORA_SB4

  • ORA_VARNUM

  • ORA_FLOAT

  • ORA_CHARN

  • ORA_RAW

  • ORA_BFLOAT

  • ORA_BDOUBLE

  • ORA_IBDOUBLE

  • ORA_IBFLOAT

  • ORA_DATE

  • ORA_TIMESTAMP

  • ORA_TIMESTAMP_TZ

  • ORA_TIMESTAMP_LTZ

  • ORA_INTERVAL_DS

  • ORA_INTERVAL_YM

Bulk copy does not support overwrites.

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.