Appendix B: Transactions in ADO.NET

ADO.NET has built-in support for transactions, and transactional support in ADO.NET can be roughly divided into two categories: transaction management and transaction participation. Transaction management application programming interfaces (APIs) give ADO.NET applications a mechanism to explicitly manage transaction demarcation boundaries. Transaction participation allows transaction-aware resources to be enlisted with a transaction managed by some other entity ”such as an external Transaction Manager.

Transaction Management

There are two main approaches to transaction management in enterprise applications:

• Manual or explicit transaction management

• Automatic or declarative transaction management

With manual transaction management, the application s code has explicit statements to start and terminate the transaction. Although transactional boundaries are fixed, an application has fine-grained control over how the transaction is managed. The application is responsible for the coordination of exception handling and transaction termination; for example, if some application code throws an exception, the enclosing transaction often needs to be manually rolled back.

Automatic transaction management relies on a Transaction Management Service to handle most of the transaction demarcation logic. A Transaction Management Service determines the rules that govern the way an application s code participates in transaction demarcation. The application code that uses automatic transaction management has to indicate when to start and terminate the transaction to the external Transaction Manager.

Manual Transaction Management

The IDbConnection and IDbTransaction interfaces are at the lowest level of the ADO.NET API we have. They can be used to manually manage database transactions. The IDbCommand interface has a Transaction property that can be set to associate the command with the specified transaction. All the commands enlisted with a transaction will be executed within that transaction s context; all the commands enlisted with a transaction will either commit or be rolled back as one unit of work. If the transaction is rolled back, all changes made by all the commands associated with the transaction will be undone in the database. Here is a sample to demonstrate the flow:

 private static void RunATransaction()    {       IDbConnection connection = GetConnection();       IDbTransaction transaction = null;       try       {          connection.Open();          transaction = connection.BeginTransaction();          IDbCommand command1 = ;          command1.Transaction = transaction;          IDbCommand command2 = ;          command2.Transaction = transaction;          command1.ExecuteNonQuery();          command2.ExecuteNonQuery();          transaction.Commit();       }       catch(Exception)       {          transaction.Rollback();       }       finally       {          connection.Close();       }    } 

This example demonstrates the manual transaction management capabilities of the ADO.NET framework. The control flow follows the same pattern:

• Begin a transaction ( connection.BeginTransaction() ).

• Associate all the transactional resources with the transaction ( command1.Transaction = transaction ).

• Perform an interaction with a transaction-capable data source using the transactional resources associated with the Transaction Manager ( command1.ExecuteNonQuery() ).

• Handle application exceptions; if necessary, manage the rollback of the transaction ( transaction.Rollback() ).

• Release the transactional resources ( connection.Close() ).

In the example shown previously, all the transaction management is done within one method: RunATransaction. (The transaction is started, executed, and committed or rolled back within the boundaries of this one method.) With manual transaction management, you are not required to confine the transaction to one method; you can write code where a transaction spans multiple method invocations.

Automatic Transaction Management

ADO.NET also supports automatic transaction management, which relies on the mechanism of associating the caller s thread with a transaction. Instances of IDbConnection can be pooled, and the pool manager can be integrated with the external Transaction Manager. When an application issues a request to open a connection, the external Transaction Manager determines whether there is an existing transaction associated with the thread of the caller; if there is one, the connection returned to the caller will also be associated with this transaction.

Not all classes that use the ADO.NET API will automatically participate in automatic transaction management. Transaction management is one of the enterprise services available to specially developed components; some other services include just-in-time activation, security, object pooling, queued components , synchronization, and so on.

Application code has to be written and packaged in a specific way to take advantage of these enterprise services. For example, the application components have to be packaged in a strongly named assembly and registered with the COM+ run time. Application classes that are authored to meet these requirements are called serviced components; these classes use attributes to describe the services that they require and service-specific configuration information. Here is a code fragment to demonstrate a serviced component that uses automatic transaction management service:

 [Transaction(TransactionOption.Required)]    public class Account : ServicedComponent    {       [AutoCommit(true)]       public void Deposit(decimal amount)       {} } 

The application class has to extend the ServicedComponent class; this is required of all serviced components. The Account class has a class-level attribute: [Transaction(TransactionOption.Required)] . This attribute indicates that the class requires the automatic transaction management service, and the value of the attribute TransactionOption.Required states that all access to the services of this class must be made within a transactional context.

When the thread of control enters this component, the Transaction Manager will determine whether there is a transaction associated with this thread. If there is one, the Account component will automatically be associated with the existing transaction; if there is no transaction in progress, the Transaction Manager will start a new one and associate it with the thread of control. Several other values for the Transaction attribute allow the application developer to choose the desired behavior:

• Disabled

• NotSupported

• RequiresNew

• Supported

The other aspect of interaction of the transaction-aware serviced component with the Transaction Manager is in the transaction termination logic. In the preceding example code, the [AutoCommit(true)] attribute is associated with the Deposit method of our Account component. This attribute tells the Transaction Manager to automatically commit the transaction started on behalf of this component if the method executes without throwing an exception. If an exception occurs, the Transaction Manager will automatically roll back the transaction. The other option is to explicitly vote for transaction rollback or commit using the SetAbort and SetCommit methods of the ContextUtil class.