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.