Is it possible to rollback committed data with TransactionScope?

Question

The goal is simple - rollback data inserted by a unit test. Here is how it goes. In a unit test, a method is called that creates a new connection and inserts some data. After that a unit test creates a new connection and tries to find what has been inserted and assert that. I was hoping to wrap these two things with TransactionScope, not call Complete and see inserted data rolled back. That's not happening. Am I doing something wrong or I am just missing the point?

using (new TransactionScope())
{
    // call a method that inserts data
    var target = new ....
    target.DoStuffAndEndupWithDataInDb();

    // Now assert what has been added.
    using (var conn = new SqlConnection(connectionString))
    using (var cmd = conn.CreateCommand())
    {
        // Just read the data from DB
        cmd.CommandText = "SELECT...";
        conn.Open();
        int count = 0;
        using (var rdr = cmd.ExecuteReader())
        {
            // Read records here
            ...
            count++;
        }

        // Expecting, say, 3 records here
        Assert.AreEqual(3, count);
    }
}

EDIT: I don't think I had DTC running and configured on my machine. So I started the service and tried to configure DTC but I am getting this error.

Answer 1

I don't think you're missing the point but just attacking the problem incorrectly.

In NUnit terms, the concepts are [SetUp] and [TearDown] methods. You've already defined the setup method in your description and your tear down method should just undo what the setup method did (assuming what you're unit testing has no residual side effects).

Answer 2

Do you have Distributed Transaction Coordinator properly configured? This is a big gotcha when trying to use TransactionScope like this... if it isn't configured, sometimes you'll get an error, but other times the transaction will just commit and not rollback.

I'd recommend looking at this article, which shows you all the various steps that need to be done in order to rollback your unit tests using MSDTC.

Answer 3

are you using MSTest ? then you can use MsTestExtensions you unit test needs to derive from MSTestExtensionsTestFixture and your test needs to have TestTransaction Attribute, it uses AOP to automatically start a transaction and roll it back.

Answer 4

Your code should work as you expect. How are you adding data in DoStuffAndEndupWithDataInDb()? I'm wondering whether the data initialization is not participating in the transaction.

For reference, the following console application correctly outputs 3 rows, and does not commit the rows to the database (checked using SSMS).

public class Program
{
    private static void Main(string[] args)
    {
        using (var trx = new TransactionScope())
        {
            InitializeData();

            using (var connection = new SqlConnection("server=localhost;database=Test;integrated security=true"))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "select count(*) from MyTable";
                connection.Open();
                Console.WriteLine("{0} rows", command.ExecuteScalar());
            }
        }
        Console.ReadLine();
    }

    private static void InitializeData()
    {
        using (var connection = new SqlConnection("server=localhost;database=Test;integrated security=true"))
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "insert into MyTable values (1),(2),(3)";
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}