When unit testing on your database, you will run into a common problem.
Rolling back.
So you want to do some unit tests, and then you want to reset your database back to the nice squeaky clean version that doesn't have half failed unit tests.
So how can you do this?
There are many ways to achieve this.
The best way I found (requires Win XP SP2 or Windows Server 2003) is to use Roy's Unit Testing Rollback Attribute. Simply inherit his class, add a "DataRollback" attribute, and you are good to go. Using some complicated Interception logic and Enterprise Services (COM+) it rolls back all the database work that was done. It's super easy to implement. Here is some sample code that shows you just how easy it is. You just have to download XtUnit (an extension to NUnit) to do this. (Full source code available)
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using TeamAgile.ApplicationBlocks.Interception.UnitTestExtensions;
using NUnit.Framework;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
namespace DBTest
{
///<summary>
/// Test roll back functionality.
///</summary>
[TestFixture]
public class RollbackTest : ExtensibleFixture
{
[Test, DataRollBack]
[Category("Database")]
public void TestInsert()
{
//this method will be performed inside a COM+ transaction
//this requires windows XP SP2 or better
//Windows Server 2003 works as well.
string strCnn = "your_conn_string";
Guid random = Guid.NewGuid();
string sqlI = string.Format(@"insert into log4net (message,date,thread,level,logger) values ('{0}',getdate(),'{1}','Debug','Test')", random.ToString(), System.Threading.Thread.CurrentThread.GetHashCode());
SqlHelper.ExecuteNonQuery(strCnn, CommandType.Text, sqlI);
string sqlS = string.Format(@"select count(*) from log4net where message='{0}'", random.ToString());
int rowcount = (int)SqlHelper.ExecuteScalar(strCnn, CommandType.Text, sqlS);
Assert.That(rowcount > 0, "Cannot find {0}", random.ToString());
}
}
}
This test passes successfully. What does that mean? The insert and select worked perfectly fine. After that, I did a query and found the database to be clean. So the rollback worked too.
There are also other ways to skin this cat (i.e. to achieve this goal). One is to use Spring Framework and extend their Unit Testing class (AbstractTransactionalSpringContextTests), and they will handle rolling back everything. Here is an article on this topic, but unfortunately I was not able to make it work for me due to some odd reason. Here's hoping you have better luck. If you don't have any transactions, and your code is wired to use Spring, it's still also very easy, you just need to call TransactionManager.Rollback.
You can also try to achieve this using Nested Transactions if you have existing transactions implemented via Spring. But then you have to set up checkpoints and stuff like that.
I have some other ideas on how to achieve this that I will post later, God willing.