How to drop a database even if it is currently in use [Entity Framework]

c# ef-code-first entity-framework entity-framework-6

Question

Despite attempting to inherit from DropCreateDatabaseAlways> in my attempt to build a DbContextInitializer, it wasn't functioning as intended.

public void InitializeDatabase(DbContext context)
{
    if (context.Database.Exists())
    {
        context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction,
            $"ALTER DATABASE [{context.Database.Connection.Database}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
        context.Database.Delete();
        context.Database.Create();
    }
    else
    {
        context.Database.Create();
    }
}

The problem is that it attempts to drop the database before executing the command when it reaches the ExecuteSqlCommand step (debugging with SQL Profiler)

Could someone maybe explain how to do this?

ALTER DATABASE [{context.Database.Connection.Database}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

before to attempting to drop the database?

By the way, when it reaches the ExecuteSqlCommand line, the dop command is issued.

1
1
6/30/2016 4:19:55 PM

Accepted Answer

Simply use the C# SQL Command:

System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);

string database = builder.InitialCatalog;

using(SqlConnection con = new SqlConnection(Connectionstring)) 
{
    con.Open();
    String sqlCommandText = @"
        ALTER DATABASE " + database + @" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE [" + database  + "]";
    SqlCommand sqlCommand = new SqlCommand(sqlCommandText, con);
    sqlCommand.ExecuteNonQuery(); 
}

This script will remove the database after killing all currently operating processes. I recently tested it, and it works:

using (var con = new SqlConnection(@"Server =.\; Database = UsersDatabase; Integrated Security = True; "))
{
  con.Open();
  var sqlCommandText = @"USE master 
                         DECLARE @kill varchar(8000) = ''; 
                         SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';' 
                         FROM master..sysprocesses  
                         WHERE dbid = db_id('UsersDatabase') 
                         EXEC(@kill); 
                         ALTER DATABASE UsersDatabase  SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
                         drop database UsersDatabase";
   var sqlCommand = new SqlCommand(sqlCommandText, con);
   sqlCommand.ExecuteNonQuery();
}

My SQL Server is, and the name of my database is UsersDatabase. You must replace them! For testing, you just need to hard code the name of your database or server; after the test is complete, you may get the connection string from the configuration file as described in the previous section.

2
7/1/2016 9:19:35 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow