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

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

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