Unit Testing of database applications is a large topic, so I'll concentrate on a specific scenario.

If you have the following setup:

TestFixture

• Init - populate database with default data
• Teardown - run delete script to restore the database to it's previous state.

This will be sufficient if none of our tests modify the data in between.  But we'll need to to test Create, Update and Delete methods of our business objects.

So, if one Test updates the database, but another Test is run after it which is expecting the database to be in it's original state (i.e. the state it's in straight after the TestFixture.Init() method has been called)  then we have a problem.  But we can't determine the order that the tests will run in, so what are our options?

We could partition our tests into different TestFixtures, so avoiding the problem altogether.  Or we could run the Populate and Delete scripts after EVERY test - though this is likely to be slow and will probably become impractical as our test library grows.

A good solution would be to have each test run in isolation, where changes to the database are rolled back after each test is run - this way the database can be updated in one test but returned to it's original state before the next test runs.

We can do this by using the TransactionScope object, like so:

[Test]
private void TestSomething()
{
  using(new TransactionScope())
  {
    //Update the database
    //Perform Assertions
  }
}

If we need to do some common initialisation of the TransactionScope object, for example setting IsolationLevel or Timeout properties, then we can wrap this up in a class as follows:

    public class RollbackTransaction: IDisposable
    {
        private TransactionScope mScope;

        public RollbackTransaction()
        {
            TransactionOptions tOptions = new TransactionOptions();
            tOptions.IsolationLevel = IsolationLevel.RepeatableRead;
            tOptions.Timeout = new TimeSpan(2, 0, 0);
            mScope = new TransactionScope(TransactionScopeOption.RequiresNew,
                                                          tOptions);
        }

        ///<summary>
        ///Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        ///</summary>
        ///<filterpriority>2</filterpriority>
        public void Dispose()
        {
            mScope.Dispose();
        }
    }

and instead of using(new TransactionScope()), we would instead have using(new RollbackTransaction()).