Running SSDT Database Unit Test in Transaction Mode

As I wrote in one of my previous posts (see here) SSDT leaves test data in tables after it executes INSERT command. One of the methods to delete these data is to use a special script in post-test action. The second method is to use transaction to run the unit test.

Obviously we can use explicit BEGIN TRANSACTION and ROLLBACK commands to execute test script. But it needs some effort to change the testing script. SSDT offers another graceful solution as described in MSDN. We can declare the transaction in C# code (that’s .cs file for unit test).

To execute only one test from the test class in a transaction we should do the following:

  • Right-click the unit test in Solution Explorer and select View Code.
  • Find the declaration of your particular test
transaction test - before
  • Place the code from inside the function body into the following block:

The code will look like this:
transaction test - one test
To execute all tests of the class in a transaction we should do:
  1. Declare TransactionScope variable in a test class.
  2. Create a transaction in the Init() method.
  3. Dispose of the transaction in the Cleanup() method.
The code will look like this:
transaction test - all tests

2 Replies to “Running SSDT Database Unit Test in Transaction Mode”

  1. This is a great post, its saves lots of time to write and to manage the delete statements.

    The only issue I came up with, is that the transactionScope requires MSDTC, which I don’t want to enable in our environment .
    I have modified the code a bit to use the connection.BeginTransaction, hope it can help other

    [TestInitialize()]
    public void TestInitialize()
    {
    base.InitializeTest();
    base.ExecutionContext.Transaction = base.ExecutionContext.Connection.BeginTransaction();
    }
    [TestCleanup()]
    public void TestCleanup()
    {
    base.ExecutionContext.Transaction.Rollback();
    base.CleanupTest();
    }

    BR,
    Guy

    1. Good idea to use ExecutionContext.Transaction.
      I found a limitation for this method – it works well only if your post-test block is empty. But for our purpose to run the test within transaction, there’s no need to write any command in post-test block (usually we delete testing data here).
      If you place in the post-block, for example, SELECT command that retrieve data from the table being modified in the test block, your test would fail and you’ll get the exception “Timeout expired”. That’s because we start a transaction, and does not commit or rollback the changes yet. So the SELECT command in post-test block would wait for the lock to be released.

Leave a Reply to Guy Cancel reply

Your email address will not be published. Required fields are marked *