Roy Osherove has a well read article explaining how to use Enterprise Services to do database testing. This is a pretty nice solution but if you are using .Net 2 then there is an even simpler way: Using System.Transactions.TransactionScope.
Some background: When you unit test code that is making changes to a database you want a way to ensure that the database remains consistent between tests. One way to do that is to wrap the work in a transaction and then roll it back after the test is done.
If you are using .Net 2 then you declare a TransactionScope variable then instantiate it in SetUp method:
private TransactionScope scope;
[SetUp]
public void SetUp()
{
scope = new TransactionScope(TransactionScopeOption.RequiresNew);
}
Now simple dispose of it in the TearDown method:
[TearDown]
public void TearDown()
{
scope.Dispose();
}
Any test will now run in a transaction. If your database code is using multiple connections then you may need to have the MSDTC running (because the transaction will be promoted from a lightweight one to a DTC one), but apart from that you are done.
HTH
Ian