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.

Setup DTC from your desktop to your development server through a firewall.

Setup DTC from your desktop to your development server through a firewall.

Here is my use case:

We have integration tests that call code that already have transactions in them. We wrap the whole integration test in a “Scope Transaction” so that we can then roll back the inner transaction as they do not actual commit unless the scope transaction commits. I’ve blogged about that here. But for now, know that if you use a Scope Transaction you need to use Distributed Transaction Coordinator (DTC) even though the transactions are not “Distributed”.

For my use case to work – we have to be able to write and test the integration tests from our desktops in a corporate environment. Following these steps were we able to get distributed transactions to work from our desktops to the SQL Server 2014 servers.

Step 1 (Turn on DTC service):

Make sure Distributed Transaction Coordinator (DTC) is running on both the database server and the local desktop.

DTC Service

Step 2 (Configure DTC):

Navigate to Component Services, you should be able to just type in Component Services in Run on Windows. Right click on LOCAL DTC in Component Services and setup the Security tab as shown:

Config DTC

Step 3 (Limit Port Ranges):

By default DTC uses any set of port numbers above 1024. We have to limit that range to something the security team will allow to be open. I have not found any information from Microsoft giving a formula as to how many port numbers should be open – but I’ve seen them recommend anything from 20 to 1000 port numbers.

I ended up using 5501-5700 and the 5500 port for RPC.

In order to limit the port range go to Component Services, right click on My Computer and pick “Properties”:

Port Range Image 1

Then select Default Protocols and Properties while “Connection-oriented TCP/IP” is selected:

port_range2

Click Add and create a range from 5501 to 5700 (or the range you decide on):

port_range3

Step 4 (Configure MSDTC Port):

This port needs to be set in the registry:

1) Start Regedt32.exe
2) Locate HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
3) Right click the MSDTC key, select New and DWord (32-bit) Value
4) Type ServerTcpPort for the key name
5) Right click ServerTcpPort key and select Modify
6) Change radio button to Decimal and type 5000 in the value data, click OK. This is how the registry key should look:

dtc_reg_edit

Note: That you will need to do steps 1-4 on each developer’s desktop AND the server. You will also need to restart your desktop machine and the server.

Step 5 (Configure Firewall):

You will need to contact your security team to configure the firewall. For our example you will need to open Port 5500-5700. You will also need port 135 open for RPC.

Note that they will need to open the ports bidirectionally. This is because once you call the server, the server will store off the name of the caller, close the connection and call back when it’s ready – potentially on a different port even.

You can test the connection from your desktop to the server with DTCPing. I’ll write more about how to use the tool in a future blog post.