A poor man's approach to testing with databases

Say you are working on software that accesses a database server and you need to test it. To some extent you'll probably try to not actually hit the database with your tests by using a mock. However, this is not always possible, especially when we're dealing with integration/acceptance tests which need to verify the system as a whole. In case your software only reads from a database there isn't really an issue. You setup a database server, create the required test database, the actual tests and start doing your test passes. Unfortunately, it's not that simple anymore as soon as your software changes the data in the database. While there are approaches that perform actions regardless of the state of the database and include verification steps that are able to analyze the resulting state changes this comes at a cost and might be overkill for your project.

So what to do if you want to have tests that are comparable to unit tests in terms of the level of complexity (just another way of saying, you want to keep your test code simple)? Referring to unit tests already implies that each test requires the software we are testing to be in a certain state. This requirement of course includes the state of the database. As long as we have a backup of the test database in its initial state we can ensure this by simply restoring the database form the backup during the initialization phase of every test. At this point I'd like to point out that this does not make sense for large databases (based on the time required for a full database restore) since the overall execution time will be unacceptable.

Restoring a database can be done in T-SQL allowing us to create a stored procedure for this purpose so the test code does not contain any file paths which may be specific to the test server used. The following example creates such a stored procedure in the master database (though it should be obvious: this is a bad idea in a production environment):

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_Restore_Northwind]

AS

BEGIN

    RESTORE DATABASE Northwind

            FROM DISK = 'C:\Database Backups\Northwind.bak'

            WITH FILE = 1,

                 MOVE 'Northwind' TO 'C:\Databases\Northwind.mdf',

                 MOVE 'Northwind_log' TO 'C:\Databases\Northwind_log.ldf',

             RECOVERY;

END

Then we can add a test initialization routine to our test suite that executes the stored procedure so the database is always in the same state before each test no matter whether or not the previous test has altered its content. The following example uses the .NET Framework Data Provider for SQL Server.

[TestInitialize]

public void ResetDatabase()

{

    using (SqlConnection connection = new SqlConnection(Settings.Default.MasterConnectionString))

    {

        connection.Open();

        using (SqlCommand command = new SqlCommand("EXEC sp_Restore_Northwind", connection))

            command.ExecuteNonQuery();

        connection.Close();

    }

}

And we're done! Except for a small problem. The .NET Framework Data Provider for SQL Server uses a connection pool to improve performance. This unfortunately gets in our way. The first test - including its initialization - will run just fine. The second one however will fail since, because the data provider uses a connection pool, there is still an active connection to the test database by the time we are trying to restore it again. The end result is that the second initialization will timeout. Since the problem is caused by connection pooling it can be solved by disabling it which can be done without any code changes by adding Pooling=False to the connection string (thanks to Mathew Charles for pointing this out):

Data Source=DbServerName;Initial Catalog=Northwind;Integrated Security=True;Pooling=False

This posting is provided "AS IS" with no warranties, and confers no rights.