Using TransactionScope – a pattern for integration testing

Wednesday, August 3, 2016

Integration testing is a valuable check on methods that talk to a database, and a good way to check that stored procedures or sql queries are behaving as expected. I often see one of 2 mistakes with integration tests. The first is reliance on live data that is liable to change. This is easy to fix; insert dummy data before testing. But the second mistake is that dummy data is inserted, the test executed, and then further query cleans up the dummy data. The problem with this is that if the test fails, for whatever reason, the data never gets cleaned up.

A really simple win, if you’re using .Net, is to use the TransactionScope class. If you run your data queries in the context of a TransactionScope, but don’t commit, any changes will simply be rolled back when the method terminates.

So the flow is: introduce a new TransactionScope, insert test data into the db, run tests, dispose of TransactionScope.

I’m using NUnit, which has these useful [TestFixtureSetup] and TestFixtureTearDown] attributes – add these to methods for them to run at the beginning/end of a [TextFixture] class run, respectively. Other frameworks have analogous attributes; in MSTest they are [ClassInitialize] and [ClassCleanup].

private TransactionScope _trans;

[TestFixtureSetUp]
public void Init()
{
    _trans = new TransactionScope();
}

[TestFixtureTearDown]
public void CleanUp()
{
    _trans.Dispose();
}

And that’s all there is to it. You can insert, update or delete data in your tests and no changes will be persisted to the database.


Leave a Reply

Your email address will not be published. Required fields are marked *