How to create integration code tests that use SQL Server Data.

How to create integration code tests that use SQL Server Data.

As our team has started adding integration tests and in some cases even unit tests – we have run into a problem I don’t see discussed very often. Most of our code relies heavily on database interaction that changes the state of the database after our tests are run.

We thought about a few different solutions. We could drop and recreate the entire database (including data) every time we run the tests. The problem is this can be very time consuming and worse – we want to maintain the state after each individual test, not just the set of all tests. While recreating the database after all tests may be plausible, recreating it after each individual test is not.

The solution is – Transactions! The problem is our code is already using transactions and out of the gate you can’t wrap a transaction around another transaction.

So the real solution is the TransactionScope object which is a part of the Distributed Transaction Coordinator . While our tests are not “distributed” against different databases – DTC is what makes the availability of the TransactionScope. TransactionScope has the ability to wrap transactions around transactions.

Our solution involves creating a base test class wrapping TransactionScope around the call and then disposing it and the end of each test. This leaves the database in the same state as before the tests ran.

Here is how the code for the base test:

    [TestClass]
    public class BaseIntegrationTest
    {
        private TransactionScope scope;
                
        [TestInitialize]
        public void TestInit()
        {
            this.scope = new TransactionScope();
            
        }

        [TestCleanup]
        public void TestCleanup()
        {
            this.scope.Dispose();
        }       
    }

Now we can test methods that already have transactions in them such as this one:

        public void AddColor(string name)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlTransaction transaction;
                connection.Open();
                transaction = connection.BeginTransaction();
                try
                {
                    using (SqlCommand command = new SqlCommand("INSERT INTO Colors (Name) VALUES (@Name)", connection))
                    {
                        command.Transaction = transaction;                      
                        command.Parameters.Add(new SqlParameter("Name", name));                     
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    Console.WriteLine("Color not insert.");
                }
            }
        }

We can then test the AddColor method like this:

    public class ColorTest : BaseIntegrationTest
    {
        [TestMethod]
        public void InsertColor()
        {
            var color = new Colors();
            color.AddColor("testcolor");
            var colors = color.GetAll();
            Assert.IsTrue(colors.Contains("testcolor"));            
        }
    }

There is one additional problem we came across. After the TransactionScope is disposed the autokeys have been increased. This may not be an issue for some since the number is not relevant as long as it’s unique. But if this is a problem you can add some SQL to the TestCleanup method to reseed all the tables.

[TestClass]
public class BaseIntegrationTest
{
	private TransactionScope scope;
	private string connectionString = @"Data Source = localhost;Initial Catalog = IntegrationTestDB; Integrated Security = true; Connection Timeout = 10;";

	[TestInitialize]
	public void TestInit()
	{
		this.scope = new TransactionScope();
		
	}

	[TestCleanup]
	public void TestCleanup()
	{
		this.scope.Dispose();            
		SqlConnection conn = new SqlConnection(connectionString);
		var cmdText = @"exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED,1)';
						exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)';";
		SqlCommand cmd = new SqlCommand(cmdText, conn);
		conn.Open();
		cmd.ExecuteNonQuery();
		cmd.Dispose();
		conn.Dispose();
	}      
}

All of this code can be download to try from my GitHub site at: https://github.com/sehuff/IntegrationTestSample

To create the sample database you just need one table called Colors with an Id and Name field.

Leave a Reply

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