Unit Test inconsistent pass or fail result

One of my unit tests seems to randomly pass or fail when I run it. The only thing that makes sense to me for why this is happening is if the data in the database is getting into a different state each time the test is ran, but I use transactions to rollback the database in each test - unless it's not working right. Here's my base unit test class and the unit test class that's having the problem. Can you see anything I might be missing or what else I should look for?

This happens with TestDriven.Net and the Visual Studio Unit Test Framework.

Partial Public MustInherit Class TestBase

    Private _scope As Transactions.TransactionScope

    <DebuggerStepThrough()> _
    <TestInitialize()> _
    Public Sub Setup()

            //'Start the Distribution Transaction Coordinator, if it's not already running.
            Using dtcService As New System.ServiceProcess.ServiceController("Distributed Transaction Coordinator", My.Computer.Name)
                    If dtcService.Status = ServiceProcess.ServiceControllerStatus.Stopped Then
                            dtcService.Start()
                    End If
            End Using

            _scope = New TransactionScope(TransactionScopeOption.RequiresNew, New TimeSpan(0))
    End Sub

    <DebuggerStepThrough()> _
    <TestCleanup()>
    Public Sub Teardown()
        If _scope IsNot Nothing Then
            _scope.Dispose()
        End If
    End Sub

    <System.Diagnostics.DebuggerStepThrough()> _
    Public Shared Function ExecSql(ByVal sql As String) As System.Data.DataTable

        Dim connStr = GlobalSettings.GetConnectionString()
        Using conn As New System.Data.SqlClient.SqlConnection(connStr)
            conn.Open()

            Dim cmd As New System.Data.SqlClient.SqlCommand(sql.ToString, conn)
            Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(cmd)
            Dim dt As System.Data.DataTable = New System.Data.DataTable
            adapter.Fill(dt)

            Return dt

            If conn.State <> System.Data.ConnectionState.Closed Then
                conn.Close()
            End If
            conn.Dispose()
        End Using
    End 开发者_JAVA技巧Function
End Class

And here's my unit test:

Partial Public Class CampaignEmailSendLimitServiceTests
    Inherits TestBase

    Private _service = ServiceManager.Current.MyService

    <TestMethod()> _
    Public Sub MyTest()
        //' Set the pre-test condition.
        ExecSql("update base.dbo.tblTableA set someDate = '2010-06-28' where id = 56937 ")

        //' Run the service
        _service.Process()

       //' Verify the expected result
       Dim dt = ExecSql("select deliveryDate from tblTableB ")
       Assert.AreEqual(False, dt.Rows(0).IsNull("deliveryDate"))

    End Sub
End Class

This has always worked for fine for me. The main difference I see is using TransactionScopeOption.Required.

[TestClass()]
public class MyTest: DatabaseTestClass
{

    public MyTest()
    {
        InitializeComponent();
    }

    TransactionScope ambientTransaction;

    [TestInitialize()]
    public void TestInitialize()
    {
        ambientTransaction = new TransactionScope(TransactionScopeOption.Required);
        base.InitializeTest();
    }

    [TestCleanup()]
    public void TestCleanup()
    {
        ambientTransaction.Dispose();
        base.CleanupTest();
    }
}

Why are You using DataAdapter.Fill to execute updates? It is designed to fill DataTables with select statements.

My guess is that You didn't write anything to database using ExecSql in the first place.

And second thing. That assert is as unreadable as possible. I would change

Assert.AreEqual(False, dt.Rows(0).IsNull("deliveryDate"));

to

Assert.IsFalse(dt.Rows(0).IsNull("deliveryDate"));

or

Assert.That(dt.Rows(0)("deliveryDate"), Is.Not.Null));

Unreadable tests are one of the reasons some people say Unit Testing is bad because it slows You down. You have to make Unit Tests as easy to read and understand as possible. So that they don't have arguments against unit testing ;)

There may be some typos as I don't use VB.NET. Assert examples are from NUnit.

I finally figured out what was going on. It had nothing to do with transactions. That's all working great. It was my process that was creating inconsistent behavior - by design. There was a part of the process that had a "random ranking" to determine the deliveryDate if no other rankings where found. The unit test needs to be rewritten to better reflect the business rules.