It makes sense to unit test your database to see if you can do CRUD operations especially when you’re working with LINQ2SQL or Entity Framework. After reading this article by Graeme Hill, we could work with the following methods.

• Put each test in a transaction
  • Single session, possible deadlocks
• Rebuild the database after each test
  • Slow
• Use SQL Server snapshots
  • Looks nice, how to automate this?
• Distributed Transactions
  • This is the recommended approach. However, our code we want to test is already using Distributed Transactions. Because we will have a transaction A inside another transaction B, we can’t use any foreign key references in transaction C and we’re unable to test all our methods. The transaction B will be committed but as this transaction resides in another transaction A the changes are not reflected to transaction C.

So if we go for the Server snapshot, the rest of this post describes how you can implemented it.

SQL Server snapshot implementation.

There are 3 stored procedures to make this work. You need to create the procedures on a different database than the one you are taking snapshots from (preferably master database).

Creating the snapshot

Needs 4 required parameters.

• sqlDatabaseName: name of your database
• sqlSnapShotPath: full path of your snapshot location on your disk (eg. c:\myfolder\)
• sqlSnapShotName: the name of your snapshot
• sqlInternalDatabaseName: the internal name of your database

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[Test_CreateSnapShot]    Script Date: 11/21/2010 23:31:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Test_CreateSnapShot]
	-- Add the parameters for the stored procedure here
	@sqlDatabaseName                   varchar(512),
	@sqlSnapShotPath               varchar(512),
	@sqlSnapShotName	               varchar(512),
	@sqlInternalDatabaseName		varchar(512)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @sql varchar(500)
  Select @sqlSnapShotPath = @sqlSnapShotPath + @sqlSnapShotName

Select @sql = 'CREATE DATABASE ' + @sqlSnapShotName + ' ON (NAME=' + @sqlInternalDatabaseName + ', FILENAME='''
+ @sqlSnapShotPath + ''') AS SNAPSHOT OF ' + @sqlDatabaseName
print @sql
EXEC(@sql)
END

Closing open connections

The procedure will check the database for any existing connections when replacing the snapshot. If you don’t kill any open connections, you will have the following error.

System.Data.SqlClient.SqlError: RESTORE cannot process database ‘DbName’ because it is in use by this session. It is recommended that the master database be used when performing this operation.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[Test_ClearDBUsers]    Script Date: 11/21/2010 23:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  CREATE PROCEDURE [dbo].[Test_ClearDBUsers]
    @dbName SYSNAME
AS
BEGIN
    SET NOCOUNT ON 

    DECLARE @spid INT,
        @cnt INT,
        @sql VARCHAR(255) 

    SELECT @spid = MIN(spid), @cnt = COUNT(*)
        FROM master..sysprocesses
        WHERE dbid = DB_ID(@dbname)
        AND spid != @@SPID 

    PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.' 

    WHILE @spid IS NOT NULL
    BEGIN
        PRINT 'About to KILL '+RTRIM(@spid)
        SET @sql = 'KILL '+RTRIM(@spid)
        EXEC(@sql)
        SELECT @spid = MIN(spid), @cnt = COUNT(*)
            FROM master..sysprocesses
            WHERE dbid = DB_ID(@dbname)
            AND spid != @@SPID
        PRINT RTRIM(@cnt)+' processes remain.'
    END
END

Restoring the snapshot

This procedure uses the ClearDBUsers procedure.
Needs 2 required parameters.

• sqlDatabaseName: name of your database
• sqlSnapshotName: the name of your snapshot (same name you gave when creating the snapshot)

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[Test_RestoreSnapShot]    Script Date: 11/21/2010 23:47:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Test_RestoreSnapShot]
	-- Add the parameters for the stored procedure here
	@sqlDatabaseName                   varchar(512),
	@sqlSnapshotName	               varchar(512)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @sql varchar(500)

-- make sure we operate on the master and not on the database to restore
-- use master (sp will do this for us)
-- cloase any existing connections
execute Test_ClearDBUsers @sqlDatabaseName

-- restore!
RESTORE DATABASE @sqlDatabaseName
FROM DATABASE_SNAPSHOT = @sqlSnapshotName
Select @sql = 'DROP DATABASE ' + @sqlSnapshotName
exec(@sql)
END

When using Unit Testing, you can call the stored procedures on ClassInitialize (create the snapshot) and on ClassCleanup (remove the snapshot). Creating an ordered Unit Test will make sure we can create our objects first, update (with the right foreign key references) and delete them. How to: Create an ordered test.

        [ClassCleanup()]
        public static void AfterAllTests()
        {
            SqlTestSetup.DeleteSnapShot();
        }

        [ClassInitialize()]
        public static void MyClassInitialize(TestContext testContext)
        {
            SqlTestSetup.CreateSnapShot();
        }

Calling the stored procedure can be done using your preferred method. This is an example using the Microsoft Data Access Application Blocks.

public static class SqlTestSetup
	{
        private const string spCreateSnapShot = "cip_Test_CreateSnapShot";
        private const string spRestoreSnapShot = "cip_Test_RestoreSnapShot";

        private static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["CipDatabase"].ConnectionString; }
        }

        public static void CreateSnapShot()
        {
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);

            var paramDatabaseName = new SqlParameter() { ParameterName = "@sqlDatabaseName", SqlValue = SqlDbType.VarChar, Value = sqlConnection.Database.ToString() };
            var paramSnapShotPath = new SqlParameter() { ParameterName = "@sqlSnapShotPath", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["DBSnapShotPath"].ToString() };
            var paramSqlSnapShotName = new SqlParameter() { ParameterName = "@sqlSnapShotName", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["DBSnapShotName"].ToString() };
            var paramSqlInternalDatabasName = new SqlParameter() { ParameterName = "@sqlInternalDatabaseName", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["DBInternalName"].ToString() };

            var parameterValues = new SqlParameter[] { paramDatabaseName, paramSnapShotPath, paramSqlSnapShotName, paramSqlInternalDatabasName };

            // SP should be executed from master db
            string masterConnectionString = ConnectionString.Replace(sqlConnection.Database.ToString(), "master");

            SqlHelper.ExecuteDataset(new SqlConnection(masterConnectionString), CommandType.StoredProcedure,
                                                                     spCreateSnapShot, parameterValues);

        }

        public static void DeleteSnapShot()
        {

            SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CipDatabase"].ConnectionString);

            var paramDatabaseName = new SqlParameter() { ParameterName = "@sqlDatabaseName", SqlValue = SqlDbType.VarChar, Value = sqlConnection.Database.ToString() };
            var paramSnapShotPath = new SqlParameter() { ParameterName = "@sqlSnapShotName", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["DBSnapShotName"].ToString() };

            var parameterValues = new SqlParameter[] { paramDatabaseName, paramSnapShotPath };

            // SP should be executed from master db
            string masterConnectionString = ConnectionString.Replace(sqlConnection.Database.ToString(), "master");

            SqlHelper.ExecuteDataset(new SqlConnection(masterConnectionString), CommandType.StoredProcedure,
                                                                 spRestoreSnapShot, parameterValues);
        }
}