TransactionScope causes TransactionManagerCommunicationException when used for DB unit tests

Question

I'd like to run some tests of stored procedures in my database without actually affecting the data (or, to put it more exactly, without a lasting impact after the test has run).

After some research I came up with the approach of using TransactionScope within my Visual Studio 2010 test project such as

using( new TransactionScope())
{
    using( SqlConnection connection = new SqlConnection("someConnectionString"))
    {
        connection.Open();
        using( SqlCommand command = new SqlCommand( "some sql", connection ))
        {
            // Do some database stuff...
        }
     }
}

Now this works fine as long as I put all of this within a single test method, i.e. all my changes to the database are automatically rolled back when the using block for TransactionScope is finished.

My problem is now that I'd like to do some database stuff in ClassInitialize so I only have to do it once per test class and not for every test method. When I create a public TransactionScope property and assign it an instance of TransactionScope in the ClassInitialize method, this works okay. As soon as I do any database related stuff in one of my test methods, I run into a TransactionManagerCommunicationException within that method.

I don't quite understand why that is the case, and I'd also like to know whether there is a mistake in my approach or how I can get it to work without having to set up the TransactionScope including all set up stuff for the tests within each test method again.

EDIT

Code excerpt below, I hope this gives enough information:

public TransactionScope Scope { get; set; }

[ClassInitialize]
public static void ClassInitialize( TestContext testContext )
{
    Scope = new TransactionScope();
    // Do some db set up stuff, e.g. create records used for tests etc.
}

[ClassCleanup]
public static void ClassCleanup()
{
    Scope.Dispose();
}

[TestMethod]
public void MyTestMethod()
{
    using( SqlConnection connection = new SqlConnection( "someConnectionString" ) )
    {
        DataTable result = new DataTable();
        using( SqlCommand command = new SqlCommand( "spName", connection ) )
        {
            command.CommandType = CommandType.StoredProcedure;
            using( SqlDataAdapter adapter = new SqlDataAdapter() )
            {
                adapter.SelecteCommand = command;
                // The next line causes the exception to be thrown
                adapter.Fill( result );
            }
        }

        // Assertions against DataTable result
    }
}

The exception is

---

TransactionManagerCommunicationException was unhandled by user code Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

---

I understand that I could try and change the settings, but I do not understand why I get the exception to begin with - what is different compared to having the code above in a single (test) method?

Thanks in advance and

best regards

G.

Answer 1

Your exception is saying that MSDTC isn't enabled. My guess is that when you were using TransactionScope individually, it was just creating local SQL transactions -- which don't require DTC. However, when you share a TransactionScope over multiple connections, the transaction gets "promoted" to a distributed transaction through the DTC, which you may not have enabled.

Try enabling network access on MSDTC on your local machine and the server. The steps for doing so vary a little depending on your OS. Here's how to do it in Win 2003 Server. Here's a link for Win 2008. Note that you will likely need to enable DTC through your firewalls as well (explained in the last link...)

Answer 2

You could create your setup something like this:

void Main()
{
   using(new SetupTransaction())
   {
    //Your test
   }
}

public class SetupTransaction : IDisposable
{
    private TransactionScope transaction;

    public SetupTransaction()
    {
        transaction = new TransactionScope();
        //Do your stuff here
    }

    public void Dispose()
    {
        transaction.Dispose();
    }
}

As for the error you're getting, could you post exactly how you're using your implementation?

Answer 3

One approach I have used with success is creating a base class that implements setup and teardown. Within the setup method you create a new transaction scope and store it in a private class variable. Within the teardown method you rollback the transaction scope.

I'm using NUNit, but the principle should be the same for MSTest. The clue here is that SetUp and TearDown executes once before and after each unit test to ensure isolation among unit tests.

Also, as @blech mentions, the Microsoft Distributed Transaction Coordinator (MSDTC) service must be running for this solution to work.