SqlExpressDatabaseInstaller

When unit testing data access components, I prefer to test against run-time components that match the target environment as closely as possible. In other words, if the data access component targets SQL Server 2008, I prefer to test against a SQL Server 2008 database. In a number of earlier posts, I've described this approach, where the database is being automatically created as an Immutable Shared Fixture.

While I prefer this approach, it has a few disadvantages that may or may not force you to look for alternatives:

• The entire test suite must at least have sysadmin privileges on the SQL Server instance, because otherwise it's not possible to create and delete databases. Since unit testing mostly focus on the functional aspects of a SUT, this may not be that important, but it's certainly more desirable if unit tests can execute under least privilege.
• Some run-time environments where the test suite will execute may not have SQL Server installed - e.g. my team's current build server doesn't have SQL Server installed, so even though my machines have, that's not going to help me if I want my tests to be running as part of the Build Verification Tests.

A reasonable alternative to SQL Server is SQL Server Express, and particularly its User Instance feature, which allows you to create a file-based database on the fly and still have full sysadmin privileges on just that database. Besides, in my particular case, our build server doesn't have SQL Server, but it does have SQL Server Express.

Since SQL Server Express uses the same relational data engine as SQL Server, it's a pretty decent alternative.

When I set up the database as part of my Immutable Shared Fixture, I want to create a one-off User Instance that can be deleted afterwards. An .mdf file in my temp folder does the trick.

As usual, I will set up and tear down the Immutable Shared Fixture using Installers. To do this, I need an Installer than can create and delete a SQL Server Express User Instance. As usual, I create a dedicated Installer for this purpose:

 public class SqlExpressDatabaseInstaller : Installer

 { 

     public override void Install(IDictionary stateSaver)

     {

         this.ExecuteNonQuery("CREATE DATABASE [{0}] ON PRIMARY (NAME='{1}', FILENAME='{2}')");

         base.Install(stateSaver);

     }

     public override void Uninstall(IDictionary savedState)

     {

         SqlConnection.ClearAllPools();

         string dropStatement =

             "IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = '{0}') BEGIN DROP DATABASE [{0}] END";

         this.ExecuteNonQuery(dropStatement);

         base.Uninstall(savedState);

     }

     private void ExecuteNonQuery(string sqlStatement)

     {

         string databaseFileName = this.Context.Parameters["DatabaseFileName"];

         string logicalFileName = Path.GetFileName(databaseFileName);

         SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();

         scsb.DataSource = @".\SQLEXPRESS";

         scsb.IntegratedSecurity = true;

         scsb.UserInstance = true;

         using (SqlConnection conn = new SqlConnection(scsb.ConnectionString))

         {

             using (SqlCommand cmd =

                 new SqlCommand(

                     string.Format(sqlStatement, databaseFileName, logicalFileName, databaseFileName),

                     conn))

             {

                 conn.Open();

                 cmd.ExecuteNonQuery();

             }

         }

     }

 }

If you compare it with SqlDatabaseInstaller, you will probably notice a lot of similarities. They both use SQL DDL statements to create and drop the database, but since SQL Express User Instances are file-based, we need to specify the file name explicitly in the CREATE statement.

Another difference from SqlDatabaseInstaller lies in the way the connection string is being built. First of all, SqlExpressDatabaseInstaller expects a file name from the Context instead of a full connection string. Subsequently, it specifies SQL Express as the DataSource and sets the UserInstance property to true.

Because of the similarities between SqlDatabaseInstaller and SqlExpressDatabaseInstaller, the obvious next step would be to merge the functionality of these to classes into a single class, but I haven't really had the need for that yet, although I may do so in the future.

Before dropping the database, I invoke SqlConnection.ClearAllPools to clear all connection pools; if I don't do that, the file will be locked and cannot be deleted. In my original post on data access component testing, I recommended disabling connection pooling for testing, but as one of my readers was so kind to point out, SqlConnection.ClearAllPools is a much better alternative, so I'm now using that exclusively. Had I posted SqlDatabaseInstaller today, I would have included a call to SqlConnection.ClearAllPools in its Uninstall method as well, so these two database Installers really are quite similar.