2011/04/05

EF integration testing, recreate database --> kill All users

Before recreate SQL server database, with no risk of "The database XXX is in use", you must close all open connections.

One option is close pooled connections, but maybe others programs are using database or some connections still are opened because of wrong dispose.

The always work approach is request database to kill all current users, and then obtain exclusive access to run schema update, and later put database in multiuser mode.

Here is the utility methods in base test class:

protected void ChangeDatabaseToMultiUserMode(IDbConnection connection)
{
   try
   {
     connection.Open();
     var cmd = connection.CreateCommand();
     cmd.CommandText = "ALTER DATABASE [" + connection.Database + "] SET MULTI_USER WITH NO_WAIT";
     cmd.ExecuteNonQuery();
   }
   finally
   {
      connection.Close();
   }
}

protected void ChangeDatabaseToSingleUserMode(IDbConnection connection)
{
   try
   {
     connection.Open();
     var cmd = connection.CreateCommand();
     cmd.CommandText = "ALTER DATABASE[" + connection.Database + "] SET SINGLE_USER WITH NO_WAIT";
     cmd.ExecuteNonQuery();
   }
   finally
   {
      connection.Close();
   }
}

protected void KillOpenDatabaseConnections(string dataSource, string databaseName)
{
   SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder();
   connStrBuilder.DataSource = dataSource;
   connStrBuilder.IntegratedSecurity = true;

   using (SqlConnection connection = new SqlConnection(connStrBuilder.ConnectionString))
   {
      connection.Open();

      foreach (int processId in GetCurrentConnectedProcessesIDs(connection, databaseName))
      {
         try
         {
            using (SqlCommand killCommand = new SqlCommand("KILL " + processId, connection))
            {
               killCommand.ExecuteNonQuery();
            }
         }
         catch (Exception ex)
         {
            Console.WriteLine("Error killing sql process " + processId 
                            + " reason: (" + ex.GetType().Name + ") " + ex.Message);
         }
      }
   }
}

private List<int> GetCurrentConnectedProcessesIDs(SqlConnection connection, string databaseName)
{
   List<int> resultado = new List<int>();
   string sql = "SELECT spid as Process_ID FROM  MASTER..SysProcesses WHERE DBId = DB_ID('" 
              + databaseName + "') AND SPId <> @@SPId";
   using (SqlDataReader dr = new SqlCommand(sql, connection).ExecuteReader())
   {
      while (dr.Read())
      {
         int processID = int.Parse(dr["Process_ID"].ToString());
         resultado.Add(processID);
      }
   }
   return resultado;
}

And example of usage:

var config = ConfigurationManager.ConnectionStrings["DATABASE_NAME"];
var connection = DbProviderFactories.GetFactory(config.ProviderName).CreateConnection();
connection.ConnectionString = config.ConnectionString;

KillOpenDatabaseConnections(connection.DataSource, connection.Database);

using (var context= new MyDbContext(connection))
{
   ChangeDatabaseToSingleUserMode(connection);
   try
   {
      new DropCreateDatabaseAlways<MyDbContext>().InitializeDatabase(context);
      new DefaultInitializer().InitializeDatabase(context);
      new TestInitializer().InitializeDatabase(context);
   }
   finally 
   {
      ChangeDatabaseToMultiUserMode(connection);
   } 
}

No hay comentarios: