Using the TransactionScope Class

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The latest Enterprise Library information can be found at the Enterprise Library site.

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.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
  int dRows = db.ExecuteNonQuery(CommandType.Text, insertString);
  dRows = db.ExecuteNonQuery(CommandType.Text, insertString2);
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.

If you use the ExecuteXmlReader method, you should test to see if a TransactionScope instance is active. This method returns an XmlReader object that holds the connection that the reader uses. When you have finished using the reader, the best practice is to close this connection. However, if you are using an instance of TransactionScope, you must not do this because closing this connection and creating a new connection changes the lightweight transaction to a distributed transaction.


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