unit testing - "Cannot drop database because it is currently in use". How to fix?

Question

Having this simple code I get "Cannot drop database "test_db" because it is currently in use" (CleanUp method) as I run it.

[TestFixture]
public class ClientRepositoryTest
{
    private const string CONNECTION_STRING = "Data Source=.;Initial Catalog=test_db;Trusted_Connection=True";
    private DataContext _dataCntx;

    [SetUp]
    public void Init()
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<DataContext>());
        _dataCntx = new DataContext(CONNECTION_STRING);
        _dataCntx.Database.Initialize(true);
    }

    [TearDown]
    public void CleanUp()
    {
        _dataCntx.Dispose();
        Database.Delete(CONNECTION_STRING);
    }
}

DataContext has one property like this

 public DbSet<Client> Clients { get; set; }

How can force my code to remove database? Thanks

Answer 1

The problem is that your application probably still holds some connection to the database (or another application holds connection as well). Database cannot be deleted where there is any other opened connection. The first problem can be probably solved by turning connection pooling off (add Pooling=false to your connection string) or clear the pool before you delete the database (by calling SqlConnection.ClearAllPools()).

Both problems can be solved by forcing database to delete but for that you need custom database initializer where you switch the database to single user mode and after that delete it. Here is some example how to achieve that.