ADO.NET 2.0 Features

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.

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.