Category Archives: .NET 4

Entity Framework 6: Taming the Open Connection Conundrum

Here’s an Entity Framework scenario for you:

You’re using Entity Framework 6.

Check.

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 : 51485cb2b3ab8.preview-620

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):

private void MurderAllConnections(HumanResourceContext context)
{

try
{

// FIRST: Build a connection using the DB Context's current connection.
SqlConnectionStringBuilder sqlConnBuilder = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);

// Set the catalog to master so that the DB can be dropped
sqlConnBuilder.InitialCatalog = "master";
using (SqlConnection sqlConnection = new SqlConnection(sqlConnBuilder.ConnectionString))
{

   sqlConnection.Open();
   string dbName = context.Database.Connection.Database;

   // Build up the SQL string necessary for dropping database connections. This statement is doing a couple of things:
   // 1) Tests to see if the DB exists in the first place.
   // 2) If it does, sets single user mode, which kills all connections.
   string sql = @"IF EXISTS(SELECT NULL FROM sys.databases WHERE name = '" + dbName + "') BEGIN ALTER DATABASE [" + dbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE END";

   using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConnection))
   {
       // Run and done.
       sqlCmd.CommandType = System.Data.CommandType.Text;
       sqlCmd.ExecuteNonQuery();
   }
}
}
catch
{
   // Something bad happened.
   throw new Exception("Hey, boss, the UnitTestInitializer failed. You want I should fix it?");
}

}

Now with the above method in place, the add it to the InitializeDatabase() method in your custom initializer:

public override void InitializeDatabase(FooContext context)
{
   this.MurderAllConnections(context);
   base.InitializeDatabase(context);
}

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 …

Cheers,
Jim

Tagged , ,

.NET 4: GAC’ing Assemblies Made Simple

Installing assemblies into the GAC is no longer straight forward. In the good ol’ days it used to be as simple as dragging a file into the Assembly folder. Or using the gacutil.exe. Neither is available any longer.

According to various posts out in the wild wild web, the updated .NET 4 version of gacutil.exe is for dev purposes only and should not be used in a production environment. It says so here, and I believe everything posted on the internet. The recommended approach of using a Setup and Deployment InstallSheild (Limited Edition) installation project also has it’s issues; the out of the box version installed with VS 2012 is not usable without downloading a new version, downloading a new version requires you to register, registration is a hassle, and the dude doesn’t abide hassles.

So … is there another option?

You betcha.

Are you going to tell me what it is?

If you ask nicely.

Okay, what’s the other option?

Check out PowerShellGac on CodePlex. It contains several useful ps scripts for getting assemblies into and out of the GAC. This was the best option I found.

The documentation on CodePlex is good enough to get you going, however when running the Install-Module command, I had to do: Install-Module .\Gac instead of Install-Module Gac. No big whoop.

Is there a catch?

Not really. If I had one wish for Christmas this year, just one wish, this set of scripts would allow for deployments of assemblies to various staging and production servers remotely. In order to use these powershells they need to be deployed to each server and run individually.

Are there other options?

Great question. Do you know of a better method or better scripts? Is there an easier way to install remotely? Comment up, people.

*EDIT*: I’ve finally had a moment to check into powershell remoting. It’s fairly straight forward. The one catch is remoting must be enabled on your various machines. Take a look at this TechNet post for more informaiton.

Cheers,
Jim