David Ross's Blog Random thoughts of a coder

Testing Tools: Temporary Sql Express Database

25. August 2008 00:30 by David in
0

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: ,