CODEWITHSUNDEEP
c#entity-frameworkef-code-firstentity-framework-6
chandler
chandler

Reputation: 1150

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

I am trying to create a DbContextInitializer and I was inheriting from DropCreateDatabaseAlways<> however it wasn't working as desired.

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 issue is when it gets to the ExecuteSqlCommand it tries to drop the database before running the command (debugging with SQL Profiler)

Could someone let me know how to execute

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

before it tries to drop the database?

By the way, the dop command is sent when it hits the ExecuteSqlCommand line.

Upvotes: 1

Views: 1611

Answers (1)

Bassam Alugili
Bassam Alugili

Reputation: 16983

You can just 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(); 
}

Update: This script will kill all running processes and then drop the database I have just test it and it working:

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 database name is UsersDatabase and my SQL Server is .\ you have to change them! just hard coded your database/server name for testing and after the test you can read the connection string from the config file as the description in the previous section.

Upvotes: 2

Related Questions