Writing unit tests for any application that connects to a database is always challenging, because deviation from the expected alterations of the database may influence the work of the business objects. The same tests have to run over and over again – after each change of the code, in order to ensure that the previous functionality has not been broken by the newly introduced changes. Altering the data so many times may have unpredictable consequences.

One of the most popular solutions of that problem in the Java and .NET worlds is the usage of mocking framework, for example Rhino Mocks. And despite the enormous advantages of this approach it has also some noticeable drawbacks. For example it is not hard to imagine a situation where your tests will pass well with the mocked data but will fail on the real database. And what if you have a lot of business logic in stored procedures that also need to be tested?

The tool I am presenting here can be quite useful in case you want to run your tests against the real data of your database, or at least a fresh copy of that data. The basic idea of this tool is that it will perform the following steps:

• Backup the model database.
• If the test database does not exist create it.
• Restore the test database from the backup of the model.
• Run your tests against the test database.
• Create assertions in order to verify the consistency of your data.

So here is the code of SqlTestManager.cs. You start with instantiating the object and passing a connection string with admin access to master database. We need that because we have to be able to backup the model database, create and restore test database. In this example the model database is called SalesDatabase:

var  manager = new SqlTestManager(
     ConfigurationManager.ConnectionStrings["masterConnection"].ConnectionString
);
manager.UseAsModel("SalesDatabase");

After that the backup is done, a new database with name SalesDatabase_TestDb is created and restored from the backup of SalesDatabase.

Here you can run your tests against SalesDatabase_TestDb. After that you use some of the data access methods of the SqlTestManager to verify the consistency of the data. For example you may check if the sales record have been creates:

var dt = manager.GetDataTable(
     "SELECT * FROM [Sales] WHERE DataCreated > {0}", 
     DateTime.Now.AddMinutes(-1)
);
Assert.Greater(dt.Rows.Count, 0, "Records were not created");

Or verify that each record in the result set has a balance higher than 100

manager.ForEachRow(
     row => Assert.IsTrue((double)row["Balance"] > 100),
     "SELECT * FROM [Invoive] WHERE CategoryID = {0}", 
     15
);

You can also get the top single row, scalar value, a random row or execute non query. Use this tool as a way to test your database.