Manage transactions

Transactions are data operations that need to succeed or fail as a group. The usual example is the money transfer: the given amount must be subtracted from an account, and added to another. If an exception occurs during the transfer, everything should be rolled back. No one would tolerate money which evaporates on the internet…

If you’d like to write a transaction manually in C#, using the .NET Framework, there is nothing in your way. You should use the DbTransaction base class, or one of its provider-specific derivatives to implement your custom transaction logic. There are three steps to follow here:

  1. Create the transaction
  2. Execute the commands which are parts of the transaction
  3. Commit or roll back the transaction, depending the outcome

A quick example:

SqlConnection theConnection = new SqlConnection(theConnectionString);
SqlCommand theCommand = new SqlCommand(theCommandText, theConnection);
SqlCommand theCommand2 = new SqlCommand(theCommandText2, theConnection);
using (theConnection)
SqlTransaction theTransaction = theConnection.BeginTranscation();
theCommand.Transaction = theTransaction;
theCommand2.Transaction = theTransaction;
catch(Exception ex)

This wasn’t the most complicated transaction you’ve ever seen, but it’s good for demonstrative purposes. The BeginTransaction method of DbConnection has some overloads, and two of these accepts a member of the IsolationLevel enumeration. Here are the values of it:

IsolationLevel enumeration
Chaos You cannot overwrite changes from more highly isolated transactions.
ReadCommited Locks the data while being read, but data can be changed before the end of the transaction, thus allowing phantom data and nonrepeatable reads.
ReadUncommited No locks are placed nor honored, dirty reads possible.
RepeatableRead Locks are placed on the data, so it cannot be updated while working with it.
Serializable Data locked, insertion and modification prohibited.
Snapshot While data being modified, others are served from a copy of it.
Unspecified An unknown isolation level.


The code sample before the table is a good approach when you need to deal with a transaction. But there are times when multiple transactions have to run simultaneously. In this case, you should use the TransactionScope class. It lives in System.Transactions, and you’ll need to set a reference on System.Transactions.dll to be able to use it.

TransactionScope is a handy class which is used to abstract away the complex operations of the Lightweight Transaction Manager (LTM) and the Distributed Transaction Coordinator (DTC).  TransactionScope also makes your life easier, because any connection and command placed in its scope automatically enlists itself for the given transaction. When you place it in a using block, it gives you unlimited freedom, particularly, when you define and encapsulate given database operations in their own methods. In this case, you just need to call the methods, like this:

using(TransactionScope theScope = new TransactionScope())

This results in very neat and object-oriented code, so befriend with it, and use it. One last thing about TransactionScope: it can accept a member of the TransactionScopeOption enumeration, which are as follows:

  • Required: default, a transaction is required by the scope. If one is exists, it will be used, else, it will be created.
  • RequiresNew: a new transaction will be created.
  • Suppress: the ambient transaction is suppressed when creating the scope.

And the very last thing in this post: you can access the currently running Transaction by the static Current property of the System.Transactions.Transaction class.


Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: