Adding transaction support to database unit tests

I wanted to describe how to add transaction support to your database unit tests. In this previous post I motivate why you would want to do this. It’s all about managing the database state prior to and during your test run. For managing the database state at the beginning of a database unit test run, Team Edition for Database Professionals’ data generation capabilities ensure that a repeatable set of test data is appropriately generated in the database prior to executing tests.

 

The next important place where state must be appropriately maintained is between database unit tests. As I mention in the previous post, there are 2 ways to achieve this. One is to author the test in such a way that the state is appropriately cleaned up as part of the test. You can imagine a test for a CreateAuction stored procedure ensuring that it deletes the auction that was created as part of the test script.

 

This process can be simplified if you utilize transactions in your database unit tests. The idea here is that you open a database transaction at the beginning of your test, perform your test, and then rollback the transaction at the end of the test to return your database to the state prior to running this test. This will ensure that all database unit tests execute with the state that was originally created for the test run by your data generation plan.

 

There are several ways to go about adding transaction support to a database unit test. I’ll walk you through those approaches here.

 

Let me first set up the scenario. Let’s say we start out with the Northwind database, which supports an online e-tailer’s storefront. We have recently added online auctions support to the database and thus have tables like Auctions, AuctionTypes, Bids, and their associated relationships with the existing tables. In addition, we have a couple of new stored procedures: GetAllAuctions and CreateAuction. Let’s say we want to create a database unit test for the CreateAuction stored procedure.

 

CreateAuction

CREATE PROCEDURE CreateAuction

  @AuctionTitle nvarchar(50),

  @AuctionDescription nvarchar(2000),

  @AuctionStartDate datetime,

  @AuctionLength int,

  @ProductID int,

  @ItemQuantity int,

  @EmployeeID int,

  @AuctionTypeID int,

  @MininumBidAmount money

AS

INSERT INTO Auctions

(

  AuctionTitle,

  AuctionDescription,

  AuctionStartDate,

  AuctionLength,

  ProductID,

  ItemQuantity,

  EmployeeID,

  AuctionTypeID,

  MinimumBidAmount

)

VALUES

(

  @AuctionTitle,

  @AuctionDescription,

  @AuctionStartDate,

  @AuctionLength,

  @ProductID,

  @ItemQuantity,

  @EmployeeID,

  @AuctionTypeID,

  @MininumBidAmount

)

RETURN SCOPE_IDENTITY()

So a typical database unit test for this stored procedure might look like the following:

 

dbo_CreateAuctionTest

-- database unit test for dbo.CreateAuction

DECLARE @AuctionID Int,

      @AuctionTitle NVarChar( 50 ),

      @AuctionDescription NVarChar( 2000 ),

      @AuctionStartDate DateTime,

      @AuctionLength Int,

      @ProductID Int,

      @ItemQuantity Int,

      @EmployeeID Int,

      @AuctionTypeID Int,

      @MininumBidAmount Money

SELECT @AuctionID = 0,

      @AuctionTitle = 'Chai',

      @AuctionDescription = '2 25-pack boxes of India Chai',

      @AuctionStartDate = '10/12/2006',

      @AuctionLength = 10,

      @ProductID = 1,

      @ItemQuantity = 2,

      @EmployeeID = 1,

      @AuctionTypeID = 1,

      @MininumBidAmount = 10.00

EXEC @AuctionID = [dbo].[CreateAuction] @AuctionTitle , @AuctionDescription , @AuctionStartDate , @AuctionLength , @ProductID , @ItemQuantity , @EmployeeID , @AuctionTypeID , @MininumBidAmount

--query AuctionTitle, AuctionStartDate of created auction

--to verify appropriate results

SELECT AuctionTitle, AuctionStartDate

FROM Auctions WHERE AuctionID = @AuctionID

--delete created auction to return db to previous state

DELETE FROM Auctions WHERE AuctionID = @AuctionID

 

 

In this database unit test, we are managing the database state by cleaning up the addition of the auction by executing a DELETE SQL command. Now let’s look at how we can enhance this database unit test to use transactions instead to manage the database state during the test run.

 

Approach 1 – Transactions in T-SQL

The simplest approach to adding transaction support is to merely add the appropriate begin and rollback transaction statements to the beginning and end of your test script.

 

So in the above test script the first step I did was comment out the DELETE FROM SQL statement:

 

--delete created auction to return db to previous state

--DELETE FROM Auctions WHERE AuctionID = @AuctionID

Then I added at the top of the test script the following statement to begin the transaction:

--begin database transaction

BEGIN TRANSACTION

And finally I added the following statement at the end of the test script to rollback the transaction:

--rollback database transaction

ROLLBACK TRANSACTION

That’s all you need to do to add transaction support to a specific database unit test!

 

Approach 2 – System.Transactions in C#\VB

The second approach is to modify the generated C#\VB code to use System.Transactions. This approach allows you to easily add transaction support to all database unit tests by simply adding a few lines of code to your test class.

 

The first thing we are going to want to do is jump to the code view for our database unit test class. Simply right-click on the test class in Solution Explorer, and select View Code.

 

Now we want to declare a member variable inside the test class for our transaction. You can add this near the top of the test class:

 

        //member variable for the transaction scope

        TransactionScope _ts;

Then we’ll want to modify the Test Initialize to instantiate the transaction scope. This is all we need to do to start the ambient transaction. Make sure that you add the instantiation line above the call to base.InitializeTest():

        [TestInitialize()]

        public void TestInitialize()

        {

            //instantiate the transaction scope

            _ts = new TransactionScope();

            base.InitializeTest();

        }

We want to similarly modify the Test Cleanup to dispose of the transaction:

 

        [TestCleanup()]

        public void TestCleanup()

        {

         base.CleanupTest();

           

            //dispose of transaction scope

            _ts.Dispose();

        }

 

Since the Test Initialize and Test Cleanup run prior to every database unit test in the test class, all of our database unit tests are now appropriately transacted!

 

Note: You may need to manually enable the Distributed Transaction Coordinator in order for you to take advantage of TransactionScope. To do this, you can simply start the Distributed Transaction Coordinator service by going to Start -> Settings -> Control Panel -> Administrative Tools -> Services.

 

I hope that shows you how easy it is to add transaction support to your database unit tests to manage the database state during a database unit test run.

 

Sachin Rekhi

Comments

• Anonymous
  October 16, 2006
  Sachin Rehke has put up a another video along with a great blog post that backs it up. Check it out!