Using TransactionScope – a pattern for integration testing

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’ve seen integration tests that run a query to insert data, then test, and then run a further query to delete the 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;

public void Init()
_trans = new TransactionScope();

public void CleanUp()

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

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s