Here’s an Entity Framework scenario for you:
You’re using Entity Framework 6.
You’re using a custom database initializer to init your DEV and TEST databases. Specifically the DropCreateDatabaseAlways initializer.
Yup, that's me.
Your project contains 1 or more Unit Test projects that utilize the above.
Do you have a unit test project? Say yes.
You have a connection open to the database that the initializer is going to drop.
Say, via SSMS.
You run your unit tests. And it runs, and runs, and runs … and then :
System.Data.SqlClient.SqlException: Cannot drop database "Foo" because it is currently in use.. Aborting test execution. Result StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.b__0(DbCommand t, DbCommandInterceptionContext`1 c) at blahdy blah blah blah ...
Ok, so what’s happening? The initializer cannot drop the database because there is currently an open connection. The most simple way of getting rid of open connections is either to delete the DB in your SSMS (with the close all connections checkbox checked), or run some SQL in a query window that accomplishes the same task.
How annoyed are you after the 10th time of doing this? The correct answer is: very.
Give Those Connections a Dirt Nap
There is a fairly simple way to combat this problem by embedding the code necessary to kill open connections directly into the database initializer. Here’s what you do:
In your custom initializer that inherits from DropCreateDatabaseAlways, add the following method (you can rename it to something a little less Class A felony if you wish):
Now with the above method in place, the add it to the InitializeDatabase() method in your custom initializer:
Now all those connections are sleepin’ with the fishes.
Some (hopefully obvious) Caveats
- Make sure the MurderAllConnections() call runs before the base.
- Do not name your method MurderAllConnections(). That’s just bad form.
- Your connection will need to be in windows auth mode.
- Highly recommend NOT using this in a PROD or STAGING environment. Just sayin’.
Happy Entity Frameworking …
Let Axian come to the rescue and help define your custom application strategy, develop a roadmap, work with your business community to identify the next project, and provide clarity and direction to a daunting task. For more details about Axian, Inc. and the Custom Application practice click here to view our portfolio or email us directly to setup a meeting.