Dealing with “Cannot drop database because it is currently in use” error in ADO.NET

I’m a big fun of Entity Framework but every once a while I come across a case where EF is not adequate or the right tool for the job. In one such case, I had to implement some code to perform bulk inserts in order to improve performance. Shortly after I implemented the helper methods my unit tests started failing when attempting inside the tear down method which was responsible for clearing out the test data and deleting the test databases. The error?

"Cannot drop database "7139e838-0de2-41b7-8b99-c0eb6a5b02c8" because it is currently in use."

Below is a method extract that bulk inserts data from an ADO.NET DataTable straight into a SQL Database table using bulk insert:

public void BulkInsertPostcodeLookupData(DataTable dataTable)
{
    using (var connection = new SqlConnection(connString))
    {
        SqlTransaction transaction = null;
        connection.Open();
        try
        {
            transaction = connection.BeginTransaction();
            using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
            {
                sqlBulkCopy.DestinationTableName = "PostcodeLookup";
                sqlBulkCopy.ColumnMappings.Add("Postcode", "Postcode");
                sqlBulkCopy.WriteToServer(dataTable);
            }
            transaction.Commit();
        }
        catch (Exception)
        {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
}

The code is pretty straight forward:

Create a connection
Create a transaction
Open the connection
Perform the bulk insert
Commit the transaction
If an error occurred then rollback and
Finally, close the connection

That should do it right? So why did I suddenly started getting errors about open connections then? As it turns out, the bulk insert uses a connection from the ADO.NET Connection Pool Connection Pool. In this case, the connection doesn’t close even if you call Connection.Close(). Instead, the connection, which is managed by the Connection Pool, get closed only after a period of inactivity. Consequently, if you attempt to delete a database shortly after performing a bulk insert or make any other raw ADO.NET calls, you may get this error.

The solution is fairly simple: just make sure you call SqlConnection.ClearPool() or SqlConnection.ClearAllPools() at the end of the process. This will ensure that all connections are closed and the database is free.

Note: Be careful when calling these two methods as they may have impact on performance. Ensure that you have completed all your database operations and you wont need another connection soon.

Using the code from the previous example, we can fix the problem by amending the code inside the “finally” block:

// Code omitted for clarity
{
    connection.Close();
    SqlConnection.ClearPool(connection);
}

Happy coding....