Easy .Net Transaction Management with Transaction Scope
// April 3rd, 2009 // No Comments » // .net, SQL Server, ado.net, software development
Transactions are a common technique to ensure consistency of the data when using database applicationsfor example with Sql Server. The System.Transactions namespace in the .Net framework simplifies Transaction Managements considerably.
This time we are going to talk about TransactionScope, which is part of the System.Transactions assembly. Using TransactionScope to manage transactions is fairly simple, yet powerful. Let’s look at an simple example using ADO:
TransactionScope tranScope = new TransactionScope(); SqlConnection connection = new SqlConnection(connectionSettigs); SqlCommand cmd = new SqlCommand(query, connection); connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); transScope.Complete();
In the example we first initialized the TransactionScope object, then we initialized the connection (very important point, we will talk about it later), execute the command, etc. And then at the end we called the method Complete for the transaction scope object.
When the transaction scope object is initialized, the transaction is effectively created and all commands after that will be protected by the transaction. After the commands are executed, then we decide to either commit the transaction or rollback. Now, for the transaction scope, the method Complete effectively commits the transaction to the database, when Dispose executes the rollback.
For some developers, is quite confusing since most developers are used to:
// Handling the transactions explicitly. SqlConnection connection = new SqlConnection(connectionSettigs); SqlTransaction trans = connection.BeginTransaction(); SqlCommand cmd = new SqlCommand(query, connection); cmd.Transaction = trans; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); connection.Commit();
Not only is the TransactionScope idiom different but also the Commit and Rollback method names are also different. The reason why the names are also different is because 1) The TransactionScope object is not a transaction per se, it is more of a transaction handler and 2) Because TransactionScope follows the Dispose pattern (just like SqlConnection). Following the Dispose pattern to implement the TransactionScope makes it easy to do this:
// Handling the transactions implicitly.
using(TransactionScope tranScope = new TransactionScope()) {
SqlConnection connection = new SqlConnection(connectionSettigs);
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
int id = cmd.ExecuteScalar();
connection.Close();
// If success commit
if(id > 0) {
transScope.Complete();
}
}
The example displayed above shows how to handle implicit transactions using the ‘using‘ keyword. When applying the ‘using‘ idiom on an object which implements the Dispose pattern, the ‘Dispose‘ method of such object will be called when the using block ends.
What the transaction scope is doing is registering the transaction in the connection contained in the transaction scope block. Making it easy to handle transactions in .Net code.
In the case of the example, if the transaction scope is not committed before the block ends, it is effectively rolled back automatically, since the using idiom calls the Dispose method of the selected object. This makes the use of Transactions extremely easy to read and maintain.
Now what happens if there is an exception inside of the TransactionScope block ? Well, the object’s Dispose method is called, which automatically rolls back the transaction.
Now it is very important, while using TransactionScope, that the connection is opened inside of the transaction scope block, otherwise the transaction won’t be registered in the connection.
SqlConnection connection = new SqlConnection(connectionSettigs);
// This will not work. The transaction will not be registered in the connection,
// and it will deadlock your application.
connection.Open();
// Handling the transactions implicitly.
using(TransactionScope tranScope = new TransactionScope()) {
SqlCommand cmd = new SqlCommand(query, connection);
int id = cmd.ExecuteScalar();
// If success commit
if(id > 0) {
transScope.Complete();
}
}
connection.Close();
The above example will not work. What happens is that when the Connection is opened inside the TransactionScope block, the transaction is registered in the connection which means that any command for that connection will be in the transaction until the connection is closed.
Be very careful to initialize the connection inside the transaction scope block, otherwise the transaction won’t register itself in the connection, and your data won’t be protected by the transaction. In some cases, any command inside of the transaction scope block, for which the connection is opened before the transaction block will result in a dead lock. The code above serves as an example of this case.
More information:
MSDN:Transaction Scope Class.
MSDN:Implementing an Implicit Transaction using Transaction Scope
