Using the TransactionScope Class

Some of the Database class methods take advantage of the .NET Framework TransactionScope class. This class automatically enlists database calls into an ambient transaction. This is useful for enlisting business objects in a transaction without passing a transaction to those business objects. Here is the basic model for using the TransactionScope class. It assumes that you have resolved the Database class you require and stored a reference in the variable named db.

For more information on instantiating objects see Creating and Referencing Enterprise Library Objects.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
  int dRows = db.ExecuteNonQuery(CommandType.Text, insertString);
  dRows = db.ExecuteNonQuery(CommandType.Text, insertString2);
}

'Usage
Using scope As New TransactionScope(TransactionScopeOption.RequiresNew)
  Dim dRows As Integer = db.ExecuteNonQuery(CommandType.Text, insertString)
  dRows = db.ExecuteNonQuery(CommandType.Text, insertString2)
End Using

The two ExecuteNonQuery methods insert the rows within the transaction that you define when you create a new TransactionScope instance.

The TransactionScope class creates a local, lightweight transaction. It assumes that you will use a single connection for all of the database calls that occur within the transaction. This means that, instead of passing the DbTransaction instance, you simply pass the connection, and the .NET Framework automatically sets the transaction for each command that you execute.

Enterprise Library, on the other hand, normally opens and closes a connection for each request. This approach is incompatible with the way the TransactionScope class works. If there are multiple connections, the TransactionScope class considers the transaction to be a distributed transaction. Distributed transactions have a significant performance and resource overhead compared with a local transaction.

To avoid this, the Database class methods, such as ExecuteDataSet, recognize when a TransactionScope instance is active and they enlist database calls in this transaction. If a transaction is currently active as a result of using a TransactionScope instance, the Database class methods use a single connection.

In particular, the GetOpenConnection method replaces the OpenConnection method within the Database methods. The GetOpenConnection method returns a connection inside a wrapper. The method disposes the wrapper if there is no transaction in progress. However, when a transaction is in progress, the method keeps the connection open.

Note

Multiple threads sharing the same transaction in a transaction scope will cause the following exception: "Transaction context in use by another session."