Delete and recreate database using EntityFramework

Question

We are using EF 6 and for some integration tests we want to completely remove our Database, and recreate it using the database context.

We try to get exclusive use of the database, but are getting a database in use errror. This is what we tried:

    private void CreateDatabase()
    {
        MyDbContext context = new MyDbContext();
        SqlConnection sqlConnection = context.Database.Connection as SqlConnection;

        LockDb(sqlConnection);
        context.Database.Delete();
        context.Database.CreateIfNotExists();
        UnLockDb(sqlConnection);
        _context = context;
    }

    private void LockDb(SqlConnection connection)
    {
        const string cmdText = 
            "USE[master];"
            + "IF exists(select * from sys.databases where name = 'MyDb')"
            + "BEGIN"
            + "  ALTER DATABASE[MyDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"
            + "END;";

        OpenConnection(connection);
        ExecuteCommand(connection, cmdText);
    }

What are we missing?

they may, it should not matter, even stronger, if they are this should prevent them from deleting and recreagting the database concurently — k.c., Aug 02 '16 at 06:37
Well I wasn't the only one with this problem found the answer [here (on stack ofcourse)](http://stackoverflow.com/questions/15051185/entity-framework-unable-to-delete-database-database-in-use?rq=1) — k.c., Aug 02 '16 at 08:15

score 1 · Answer 1 · edited May 23 '17 at 11:44

Well I wasn't the only one with this problem found the answer here (on stack ofcourse)

the trick is to delete the database in a single SQL statement

    private void DeleteDb(SqlConnection connection)
    {
        const string cmdText = 
            "USE[master];"
            + "IF exists(select * from sys.databases where name = 'Tor')"
            + "BEGIN"
            + "  ALTER DATABASE[Tor] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"
            + "  DROP DATABASE [Tor];"
            + "END;";

        OpenConnection(connection);
        ExecuteCommand(connection, cmdText);
    }

UPDATE

Well even the above is not bullet proof.

Now just using:

    private void CreateDatabase()
    {
        TorDbContext context = new TorDbContext();
        context.Database.Delete();
        context.Database.Create();
        _context = context;
    }

Works as long as we do not have queries open in the Management Studio. It does not bother our integration tests...

Delete and recreate database using EntityFramework

1 Answers1