Unit testing databases - with NHibernate!

One of the pesky problems with databases is unit testing the database portion of your application. For instance, it's enough of a pain to tear down and restore data to it's original state, but it's even harder if your application code requires you to commit changes. A while ago I saw this stack overflow question that said you could wrap all your code in a TransactionScope like:

using (new TransactionScope())
{
    // Database access code here
}

When .Dispose() is called at the end of the using block, the code is supposed to roll back all transactions, even if they were committed. After reading the documentation I realized that any new transactions will use this transaction scope, and hence be rolled back when the transaction scope rolls back at the end of the using block.

This all seems like a great idea for ADO.NET code, but I was skeptical of using this with NHibernate because I know NHibernate does funny things with the session and how it creates transactions. Even though I've known about this trick for some time, I never trusted it or even took the time to actually test it...until now.

I tested this idea out inside the scope of our application code which I'm basically just pasting here. So bear with some of the abstraction code we have built up in IGenericDAO and Container.

[Test]
public void CheckNHibernateMappings()
{
    using (new TransactionScope())
    {
        // IGenericDAO is our abstraction layer for accessing NHibernate
        var dao = Container.Resolve<IGenericDAO<WorkflowTransition>>();
        var obj = new WorkflowTransition() { FromFk = 1, ToFk = 2, IsAllowed = true, WorkflowFk = 1, RightFk = 1 };
        dao.Save(obj);
        dao.CommitChanges();

        var selected = dao.SelectById(obj.WorkflowTransitionId);
        Assert.That(selected.WorkflowTransitionId, Is.GreaterThan(0));
        Assert.That(selected.To, Is.EqualTo(2));
    }
}

I placed a breakpoint at line 12, after CommitChanges(). I debugged the unit test and when it stopped at the breakpoint I ran this query in SSMS:

select * from WorkflowTransitions with (nolock)

The query returned the row I just inserted. The nolock table hint means to ignore any locks that might be on the table and read everything, even uncommitted data. This means we can see the results of NHibernate's  insert statement without having to mess with the SQL profiler. If you run the query without the nolock option it hangs until timeout. I then let the test finish and ran the query again. This time the row was gone!

Apparently, this TransactionScope is fully capable of rolling back all transactions, even if they were created automagically by NHibernate. I presume this means it will work with any ORM framework, not just NHibernate.