NUnit Rollback attribute seems to fail on SQL Server 2005

Question

I'm using a nice little [Rollback] attribute on my NUnit database-level tests:

public class RollbackAttribute : Attribute, ITestAction 
{
   private TransactionScope _transaction;

   public ActionTargets Targets {
      get { return ActionTargets.Test; }
   }

   public void BeforeTest(TestDetails testDetails) {
      _transaction = new TransactionScope();
   }

   public void AfterTest(TestDetails testDetails) {
      _transaction.Dispose();
   }
}

So I can then decorate my db-based tests like this:

[Test]
[Rollback]
public void TestGetAllActiveItems()
{
    // Arrange
    SetupTestData();

    // Act
    var results = GetAllActiveItems(string.Empty, string.Empty);

    // Assert
    Assert.IsNotNull(results);
}

The sample data I create and store in the SetupTestData() method is used for the test, and then discarded at the end of the test.

This works like a charm on SQL Server 2012 and 2014 on my local dev machine - but for some reason, it appears to fail miserably on our build & test machine, which is still using SQL Server 2005 (soon to be upgraded).

Any ideas why? I just see that my two items that I insert into the database in my SetupTestData() method are inserted once for each test method, and NOT rolled back at the end of each method, for some weird reason.... I don't see any errors or other indicators in the build / testrun logs - it just doesn't work and doesn't do the rollback on my build/test server.

Any pointers? Thoughts? Points to check?

@MichalHosala: no, still working on it. It seems to be related to (a) SQL connections that are open before the test starts, and (b) multiple ObjectContexts of EF that cause the "MSDTC" to kick in (and fail) on the server - pretty weird...... but I've also confirmed that it's a general issue - SQL Server 2014 (on the build server) also has the same problems - it's not version specific — marc_s, Apr 16 '15 at 14:10

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Based on this article which describes specifics of SQL2005 I would presume your problem may be that you are reusing the same connection for all the transactions, quoting:

In SQL Server 2005, using the TransactionScope starts a local, not a distributed, transaction. This is the behavior whether TransactionScope is used with client-side code or SQLCLR procedures unless there is already a transaction started when the SQLCLR procedure is invoked.

...

The transaction actually begins when Open is called on the SqlConnection, not when the TransactionScope instance is created.

...

At this point, because SQL Server 2005 doesn’t support autonomous transactions on the same connection, SqlConnection’s BeginTransaction method is the best choice for local transactions.

Please let me know whether this piece of info is valid as I currently cannot test the problem myself.

NUnit Rollback attribute seems to fail on SQL Server 2005

1 Answers1

Linked