Testing Tools: Temporary Sql Express Database

by Call me dave... 25. August 2008 09:30

The Temporary SQL Express database is used to create a database that exists only within the confines of a unit test or test fixture.  I have used the class to test deployment issues with database scripts or when testing complex integration scenarios that can't be placed into a database transaction such as those within SQL Server Integration Services.  

It can be used as follows.  

   1: [Test]
   2: public void TemporaryDatabaseExample()
   3: {
   4:     using (var tempDb = new TemporarySqlExpressDatabase())
   5:     {
   6:         using (var con = new SqlConnection(tempDb.ConnectionString))
   7:         {
   8:             con.Open();
   9:             using (var cmd = new SqlCommand("CREATE TABLE foo (id int)", con))
  10:             {
  11:                 cmd.CommandType = CommandType.Text;
  12:                 cmd.ExecuteNonQuery();
  13:             }
  14:  
  15:             using (var cmd = new SqlCommand("INSERT INTO foo (id) VALUES (1)", con))
  16:             {
  17:                 cmd.CommandType = CommandType.Text;
  18:                 cmd.ExecuteNonQuery();
  19:             }
  20:  
  21:             using (var cmd = new SqlCommand("SELECT id FROM foo", con))
  22:             {
  23:                 cmd.CommandType = CommandType.Text;
  24:                 var result = (int)cmd.ExecuteScalar();
  25:                 Assert.AreEqual(1, result);
  26:             }
  27:         }
  28:     }
  29: }

Under the covers the class uses a TemporaryDirectory which ensures that a data files are deleted after the object is destroyed. 

   1: using System;
   2: using System.Data.SqlClient;
   3: using System.IO;
   4:  
   5: namespace PebbleSteps.TestingTools
   6: {
   7:     public class TemporarySqlExpressDatabase : IDisposable
   8:     {
   9:         private const string SqlExpressConnectionString
  10:             = "Data Source=.\\sqlexpress;Initial Catalog=tempdb;Integrated Security=true;User Instance=True;";
  11:  
  12:         private readonly string _temporaryDatabaseFileName;
  13:         private readonly TemporaryDirectory _temporaryDirectory;
  14:         private bool _disposed;
  15:  
  16:         public TemporarySqlExpressDatabase()
  17:         {
  18:             _temporaryDirectory = new TemporaryDirectory();
  19:             DatabaseName = "temp" + Guid.NewGuid().ToString("N");
  20:             _temporaryDatabaseFileName = Path.Combine(_temporaryDirectory.FullName, DatabaseName + ".mdf");
  21:  
  22:             using (var connection = new SqlConnection(SqlExpressConnectionString))
  23:             {
  24:                 connection.Open();
  25:  
  26:                 using (SqlCommand command = connection.CreateCommand())
  27:                 {
  28:                     command.CommandText =
  29:                         string.Format("CREATE DATABASE {0} ON PRIMARY (NAME={0}, FILENAME='{1}', SIZE = 3000KB) LOG ON ( NAME = N'{0}_Log', FILENAME = N'{1}_Log.ldf', SIZE = 512KB)", DatabaseName, _temporaryDatabaseFileName);
  30:                     command.ExecuteNonQuery();
  31:  
  32:                     command.CommandText = "EXEC sp_detach_db '" + DatabaseName + "', 'true'";
  33:                     command.ExecuteNonQuery();
  34:                 }
  35:             }
  36:         }
  37:  
  38:         public string ConnectionString
  39:         {
  40:             get
  41:             {
  42:                 return
  43:                     string.Format(@"Server=.\SQLExpress;AttachDbFilename={0};Database={1}; Trusted_Connection=Yes;",
  44:                                   _temporaryDatabaseFileName, DatabaseName);
  45:             }
  46:         }
  47:  
  48:         public string DatabaseName { get; private set; }
  49:  
  50:         #region IDisposable Members
  51:  
  52:         public void Dispose()
  53:         {
  54:             Dispose(true);
  55:             GC.SuppressFinalize(this);
  56:         }
  57:  
  58:         #endregion
  59:  
  60:         private static void ClearConnectionsToTemporaryDatabase()
  61:         {
  62:             GC.Collect();
  63:             SqlConnection.ClearAllPools();
  64:         }
  65:  
  66:         private void Dispose(bool disposing)
  67:         {
  68:             if (!_disposed)
  69:             {
  70:                 // Cleanup Managed resources
  71:                 ClearConnectionsToTemporaryDatabase();
  72:                 _temporaryDirectory.Dispose();
  73:  
  74:                 if(disposing)
  75:                 {
  76:                     //Clean unmanaged resources
  77:                 }
  78:  
  79:                 _disposed = true;
  80:             }
  81:         }
  82:  
  83:         ~TemporarySqlExpressDatabase()
  84:         {
  85:             Dispose(false);
  86:         }
  87:     }
  88: }

The TemporaryDirectory class has been updated so that it sleeps for up to 3 seconds while it waits until any running processes to close open file handles on the folder.

   1: using System;
   2: using System.IO;
   3: using System.Security.AccessControl;
   4: using System.Threading;
   5:  
   6: namespace PebbleSteps.TestingTools
   7: {
   8:     /// <summary>
   9:     /// Creates a Temporary Directory that is deleted when the Dispose method is called.  The class can be used
  10:     /// in Unit tests.
  11:     /// </summary>
  12:     public class TemporaryDirectory : IDisposable
  13:     {
  14:         private readonly DirectoryInfo _directory;
  15:         private bool _disposed;
  16:  
  17:         // The class constructor.
  18:         public TemporaryDirectory()
  19:         {
  20:             // Create the temporary directory
  21:             string directoryPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString("N"));
  22:             _directory = Directory.CreateDirectory(directoryPath);
  23:  
  24:             // Change security to full control so that directory can be used by
  25:             // services under test
  26:             DirectorySecurity dirSec = _directory.GetAccessControl();
  27:             dirSec.AddAccessRule(
  28:                 new FileSystemAccessRule("Everyone", FileSystemRights.FullControl, AccessControlType.Allow));
  29:             _directory.SetAccessControl(dirSec);
  30:         }
  31:  
  32:         /// <summary>
  33:         /// FullName of the folder.  Files created here will be automatically deleted
  34:         /// </summary>
  35:         public string FullName
  36:         {
  37:             get { return _directory.FullName; }
  38:         }
  39:  
  40:         /// <summary>
  41:         /// Persists a stream to a file that is created in the temporary directory
  42:         /// </summary>
  43:         /// <param name="s"></param>
  44:         /// <param name="fileName"></param>
  45:         public void StoreToDirectory(Stream s, string fileName)
  46:         {
  47:             using (var r = new StreamReader(s))
  48:             {
  49:                 using (var w = new StreamWriter(Path.Combine(_directory.Name, Path.Combine(_directory.FullName, fileName))))
  50:                 {
  51:                     w.Write(r.ReadToEnd());
  52:                 }
  53:             }
  54:         }
  55:  
  56:         #region IDisposable
  57:  
  58:         public void Dispose()
  59:         {
  60:             Dispose(true);
  61:             GC.SuppressFinalize(this);
  62:         }
  63:  
  64:         private void Dispose(bool disposing)
  65:         {
  66:             if (!_disposed)
  67:             {
  68:                 // Cleanup Unmanaged resources, wait up to 3 seconds for the test to complete...
  69:                 int retryCount = 3;
  70:                 while (retryCount > 0)
  71:                 {
  72:                     try
  73:                     {
  74:                         Directory.Delete(_directory.FullName, true);
  75:                         retryCount = 0;
  76:                     }
  77:                     catch (IOException)
  78:                     {
  79:                         retryCount--;
  80:                         Thread.Sleep(1000);
  81:                     }
  82:                 }
  83:                 _disposed = true;
  84:             }
  85:         }
  86:  
  87:         ~TemporaryDirectory()
  88:         {
  89:             Dispose(false);
  90:         }
  91:  
  92:         #endregion
  93:     }
  94: }

Tags: ,