Dot Net Thoughts

November 10, 2007

TransactionScope and Unit Tests

Filed under: Uncategorized — dotnetthoughts @ 6:29 am
Tags: , , , , ,

Writing unit tests to validate a data access layer (DAL) can be a time consuming (but life saving) task. One of the biggest challanges of DAL unit tests is assuring that you have consistent data to pull from the database. Dedicating a database with static data for unit tests doesn’t always work. As unit tests are added to the project, data may need to be added to the database. This can cause previously created tests to fail, and a lot of time can be lost trying to resync everything.

One technique for getting around the unit test database consistency issue is to write data to the database you expect to find. The steps for doing this would be:

  • Begin a transaction in your unit test.
  • Write the data to the database that your data access layer will need.
  • Test the data access layer functionality against the inserted data.
  • Roll back the transaction.

Using a transaction has a couple of distinct advantages. Since you are running in the scope of an uncommitted transaction, your fellow developers running unit tests will not see your added data (isolation). Also, rolling back the transaction places the database back to the original state in which you found it.

Managing transactions by explicitly attaching them to the connection object doesn’t always work well when testing a data access layer. Since the DAL often contains code to retrieve its own connection, the following sequence often occurs:

  • Begin a transaction in your unit test.
  • Write the data to the database that your data access layer will need.
  • Call the Data Access Layer. The Data Access layer creates its own connection.
  • The DAL attempts to read the new data from the database. It is blocked, though, because it is in a different transaction than the unit test transaction, and will not be able to complete until the DAL commits.

The TransactionScope object helps alleviate this problem. It sits on top of the Distributed Transaction Coordinator, and will assign any code running within its context to the same transaction. In other words, a TransactionScope context within your unit tests will force your data access layer code to run within the same context. Isolation is maintained from other developers, but your DAL can access and manipulate the data as needed. (This does require that you have the Distributed Transaction Coordinator Service running on the box that handles the transaction.)

To demonstrate, let’s assume that I have a DAL method that I want to test that returns all users from a database. This method gets its own connection, retrieves the users, and returns them as a DataSet.

public DataSet GetUsers() 
    DataSet dataSet = new DataSet(); 
    string connectionString = GetConnectionString();      

    using (SqlConnection connection = new SqlConnection(connectionString)) 
        string sql = "SELECT * FROM [User]"; 
        SqlCommand command = new SqlCommand(sql, connection); 
        SqlDataAdapter adapter = new SqlDataAdapter(command); 

    return dataSet; 

Here is the unit test to test this code. It doesn’t test nearly all of the functionality you would want to check in a real unit test, but it does demonstrate the TransactionScope. Note that the TransactionScope object doesn’t have an explicit RollBack() method. The Rollback occurs if the TransactionScope object is disposed without Complete() being called on it. This occurs at the end of the using block.

public void GetUsersTest() 
    string connectionString = GetConnectionString();     

    using (TransactionScope ts = new TransactionScope()) 
        using (SqlConnection connection = 
            new SqlConnection(connectionString)) 
            DataLayer dataAccessLayer = new DataLayer();     

            DataSet dataSet = dataAccessLayer.GetUsers(); 
            AddNewUser("Fred", connection);     

            dataSet = dataAccessLayer.GetUsers(); 
            DataRow[] dr = dataSet.Tables[0].Select("[UserName] = 'Fred'"); 
            Assert.AreEqual(1, dr.Length); 

Hope this is helpful. Good luck and code safe!



  1. How about the DB configuration, do we need to enable or configure any DB features to allow using the transaction scope within the unit tests?

    Comment by Sally — May 31, 2008 @ 9:54 pm | Reply

  2. Good pointer there MW. But, what if the DAL method that you want to test also has a TransactionScope defined in it?

    Comment by ssr — July 18, 2008 @ 5:01 am | Reply

  3. It doesn’t work…..Transaction scope only allows one connection in the transaction scope….

    Sybase.Data.AseClient.AseException was unhandled by user code
    Message=Only One Local connection allowed in the TransactionScope
    at Sybase.Data.AseClient.AseConnection.Open()
    at SCAModel.AutomationCalendarModel.GetAutomationCalendarList(String FundCd, String EntityCd, String EntityTypeCd, String Script, DateTime RunDateFrom, DateTime RunDateTo, String Period) in C:\workspace_dotnet\SCARRCalendarUI\SCAModel\AutomationCalendarModel.cs:line 106
    at TestSCARRCalendarUI.AutomationCalendarModelTest.GetAutomationCalendarList_NoParams_Test() in C:\workspace_dotnet\SCARRCalendarUI\TestSCARRCalendarUI\AutomationCalendarModelTest.cs:line 78

    Comment by Anonymous — October 23, 2011 @ 9:05 pm | Reply

RSS feed for comments on this post. TrackBack URI

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

Blog at

%d bloggers like this: