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);
}
}
