I am running integration tests. Before starting the test, I deleted the database and create it again. The first test is successful. But at the start of the second test I get an exception:
SetUp : System.Data.SqlClient.SqlException : Cannot drop database "Test" because it is currently in use.
Сode:
[TestFixture]
class Class1
{
public SqlConnection Repository;
[SetUp]
public void LocInit()
{
Repository = new SqlConnection(@"Data Source=.\SQLEXPRESS; Integrated Security=true;");
Repository.Open();
Repository.Execute("USE master;");
Repository.Execute("DROP DATABASE Test;");
Repository.Execute("USE master; CREATE DATABASE Test;");
Repository.Execute("USE Test;");
}
[Test]
public void Test1()
{
using (var repository = new SqlConnection(@"Data Source=.\SQLEXPRESS; Integrated Security=true;"))
repository.Execute("USE Test; SELECT 10");
}
[Test]
public void Test2()
{
using (var repository = new SqlConnection(@"Data Source=.\SQLEXPRESS; Integrated Security=true;"))
repository.Execute("USE Test; SELECT 10");
}
[TearDown]
public void LocalTearDown()
{
Repository.Dispose();
}
}
Why do I get this exception?
Havent tried it myself but maybe you can try this:
Set Pooling=false in your connection string
or
Clear the pool before you delete the database: SqlConnection.ClearAllPools()
"Cannot drop database because it is currently in use". How to fix?
The reason why you encounter such issue is that there are still some pending connections that might have not yet been killed. One workaround would be to kill all connections as described here, before actually dropping DB:
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Another would be to track down why there is such connection left. This thread shows how you can list all connections to your DB and get rid of it in proper way.