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.
– k.c., Commented Aug 2, 2016 at 6:37
Well I wasn't the only one with this problem found the answer here (on stack ofcourse) — k.c.
– k.c., Commented Aug 2, 2016 at 8:15

Community · Accepted Answer · 2017-05-23 11:44:19Z

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...

Collectives™ on Stack Overflow

Delete and recreate database using EntityFramework

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related