Cannot delete database because it is in use (with Membership)

Question

I'm trying the following in my EF codefirst Seed method:

protected override void Seed(TestDbContext context)
        {
            SqlConnection.ClearAllPools();
            context.Database.Delete();
            context.Database.CreateIfNotExists();

            if (!WebMatrix.WebData.WebSecurity.Initialized)
            {
                  WebSecurity.InitializeDatabaseConnection("DefaultConnection",  "UserProfile", "UserId", "UserName", autoCreateTables: true);
            }
        }

This fails with the error 'cannot delete because the database is currently in use'. It seems to only happen after I run my Api project, which initializes its own connection to the membership tables:

private class SimpleMembershipInitializer
        {
            public SimpleMembershipInitializer()
            {
                Database.SetInitializer<UsersContext>(null);

                try
                {
                    using (var context = new UsersContext())
                    {
                        if (!context.Database.Exists())
                        {
                            // Create the SimpleMembership database without Entity Framework migration schema
                            ((IObjectContextAdapter)context).ObjectContext.CreateDatabase();
                        }
                    }
                    if (!WebMatrix.WebData.WebSecurity.Initialized)
                    {
                        WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId",
                                                                 "UserName", autoCreateTables: true);
                    }
                }
                catch (Exception ex)
                {
                    throw new InvalidOperationException("The ASP.NET Simple Membership database could not be initialized. For more information, please see http://go.microsoft.com/fwlink/?LinkId=256588", ex);
                }
            }
        }

Any idea how I can get this working from code (I'm trying to get testing automated)?

Quick and dirty way would just be to delete the db prior to running the create/seed script. In SSMS you can choose to close existing connections when dropping a DB. — dougajmcdonald
– dougajmcdonald, Commented May 5, 2013 at 21:51
That's what I'm doing now... This is part of a larger effort to automate testing so I'd like to move everything to code if I can. This has to be a common scenario... right? — SB2055
– SB2055, Commented May 5, 2013 at 21:54
You could certainly run a SQL script to kill active connections prior to running your create/seed script. But it's not particularly automated in terms of having a nicely wrapped up way to run it from C# code :( — dougajmcdonald
– dougajmcdonald, Commented May 5, 2013 at 21:55
You have a timing problem, the Membership thing probably fires (Filter?) before the Db initializer. — Henk Holterman
– Henk Holterman, Commented May 5, 2013 at 22:50

Lotok · Accepted Answer · 2013-05-05 21:54:03Z

1

On the server explorer, right click your data connection to that db and choose close connection.

answered May 5, 2013 at 21:54

Lotok

4,6151 gold badge37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Lotok Over a year ago

You might want to consider SSDT then. You keep your DB on a seperate project in the solution. It also allows versioning etc of your Db plus you can import all your changes to SQL server with 1 update when in production. Just uses a DACSAC file

Lotok Over a year ago

You can set code-first to only drop/create on model changes, or only if db doesnt exist. If you dont want it to drop at all there is a table created containing a hash value. That is what is used to determine if anything changed. Remove that table and it makes any changes to db thereafter manual

Lotok Over a year ago

why downvote? Question wasn't clear when I answered that you were trying to close the connection using code.

SB2055 Over a year ago

I do want it to drop. I just don't want to drop it manually.

Lotok Over a year ago

There is a good video tutorial by Scott Allen on this if you have time to watch. It describes adding a DropDatabaseIfModelChanges to global.asax and also seed methods. It's part of his MVC3 video but the EF bit is only bit you need. Will look out link in a sec

|

Collectives™ on Stack Overflow

Cannot delete database because it is in use (with Membership)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related