At some point, all good developers make the realization that Unit Testing is not evil and is the best way to ensure your code works as you expect it to. With that said, the moment you start writing tests for methods that interact with a database you start to run into some really unique problems that can lead to a lot of unneeded code.
Generally speaking you always want to leave the system as you found it when testing started. That means that any data you add/edit/delete during the test needs to be restored when the test has concluded. For the longest time I would spend my time writing test rigs to manage all these functions for each data method being tested. It didn't take me long to realize that this was a waste of time and that there had to be a way to make this easier.
With that in mind I set out to make my unit tests transactional since transactions have rollback support. Stealing a trick from the COM world I was able to find a way to make my test transactional. Here is what you need to do. As I go though this, keep in mind that I am using TFS Unit Tests run against a Microsoft SQL Server. This same technique can be used with other technologies (Ex. NUnit) although the syntax will be different.
One last note... If you look at the line "config.TransactionTimeout = 900", this may seem like a really long time. This is set to this length to ensure you have plenty of time to step through code during tests. Adjust this as needed.
''' Abstract class defining a transactional baseline for database driven unit tests
Public MustInherit Class DatabaseTransactionTestClass
''' Hold a defined in progress transaction
Private TestTransactionScope As TransactionScope
''' Init method to setup transaction requirements
Public Shared Sub TransactionSetup(ByVal testContext As TestContext)
Dim config As ServiceConfig = New ServiceConfig()
config.Transaction = TransactionOption.RequiresNew
config.TransactionTimeout = 900
config.IsolationLevel = TransactionIsolationLevel.RepeatableRead
''' Cleanup method to roll-back all database changes
Public Shared Sub TransactionTearDown()
''' Init method to define the start of a transaction on a per-test basis
Public Sub TestSetup()
TestTransactionScope = New TransactionScope
''' Cleanup method to close and abort the transaction
Public Sub TestCleanup()
If Not TestTransactionScope Is Nothing Then
''' Rolls back and existing test level transactions as defined by the TestTransactionScope
Public Shared Sub RollbackTransaction()
If ContextUtil.IsInTransaction Then