Using Transactions

All DataDirect Connect for ADO.NET data providers support both local and distributed transactions.

System.Transactions

The System.Transactions namespace contains classes to allow you to write a transactional application using only managed code. In the infrastructure provided in System.Transactions is a grouping that allows more inclusive and efficient transactional programming. Transaction behavior can be controlled directly using the Transaction class or implicitly using the TransactionScope class.

We recommend using the System.Transactions objects for all transaction processing with the DataDirect Connect for ADO.NET data providers.

Using Local Transactions

All DataDirect Connect for ADO.NET data providers use 100% managed code in supporting local transactions. Local transactions are implemented within the .NET Framework. A local transaction uses the internal transaction manager of the underlying database.

If your application connects to only one database and you have no requirement to connect to another database, use local transactions, as shown in the following figure. Local transactions are always faster than distributed transactions, which require additional logging and network round trips. In addition, local transactions provide the added benefit of increased security because the data provider does not need to call unmanaged code.

Using Local Transactions in the .NET Environment

Using Distributed Transactions

The DataDirect Connect for ADO.NET data providers support distributed transactions only when the application is written to serviced components. For a general discussion of using serviced components, refer to the Microsoft ADO.NET Framework SDK documentation.

The DataDirect Connect for ADO.NET data providers support distributed transactions using the Microsoft Distributed Transaction Coordinator (MS DTC), which is provided through System.Transactions. You must include specific code, such as System.Transaction, in the namespace of your application.

The MS DTC service must be in a running state. On the MSDTC tab of the Component Services Administrative ToolSet, specify the Security Configuration setting:

• For the DB2, Oracle, and Sybase data providers, select the Enable XA Transactions check box and click OK.
• For the SQL Server data provider, select the Network DTC Access check box and click OK.

Note: The location of the MS DTC configuration varies by Windows version. Refer to the Microsoft Windows help for detailed information for your environment.

Following figure illustrates using a distributed transaction.

Using Distributed Transactions in the .NET Environment

Distributed transactions are significantly slower than normal transactions due to the logging and network round trips necessary to communicate between all the components involved in the distributed transaction.

Because the DataDirect Connect for ADO.NET data providers are managed data providers, you can still enjoy some of the benefits of the .NET Framework security when you use distributed transactions. The security afforded by the data provider, with the security defined in the database, provides good protection when you use distributed transactions.

See Handling Dead Connections in a Pool for information about how connections used in a distributed transaction are processed in a connection pool.