Cannot Drop Database after Unit Test

Question

ANSWER: I wasn't clearing my ConnectionPools.

The solved scenario is:

SetUp:

1. Check to see if the 'TEMP_NUnit' database (SQL Server 2005) exists on the local instance
2. If it exists, drop it.
3. Create a new blank database named 'TEMP_NUnit'.

Test:

1. Instantiate the 'Upgrade Database' component.
2. Check that essential properties of component are defaulting correctly.
3. Point the component at the blank database and call .Upgrade()
4. Check that the upgrade actually worked

TearDown:

1. SqlConnection.ClearAllPools();
2. Drop the 'TEMP_NUnit' database.

Original question is below the fold.

---

Hi All

The test scenario is:

SetUp:

1. Check to see if the 'TEMP_NUnit' database (SQL Server 2005) exists on the local instance
2. If it exists, drop it.
3. Create a new blank database named 'TEMP_NUnit'.

Test:

1. Instantiate the 'Upgrade Database' component.
2. Check that essential properties of component are defaulting correctly.
3. Point the component at the blank database and call .Upgrade()
4. Check that the upgrade actually worked

TearDown:

1. Drop the 'TEMP_NUnit' database.

Everything's going fine until I get to the TearDown phase. I always get the following error:

Cannot drop database "TEMP_NUnit" because it is currently in use.

This is confusing me, because I'm closing (explicitly calling conn.Close in finally statements) and disposing (via using statement) all my DbConnection objects correctly. There shouldn't be anything running on the "TEMP_NUnit" database by the time the TearDown comes around.

If I close NUnit and then re-open it, step 2 of the SetUp always works first go.

I'm not sure what I'm doing wrong here. Any pointers in the right direction would be appreciated.

Answer 1

Ah, but you forgot to do this little thing before dropping the database:

SqlConnection.ClearAllPools();

By default the connection pool will maintain a connection to the database, even if you closed it before. Doing SqlConnection.ClearAllPools() will actually force all connections to be closed. You can then connect to master and drop the temp database.

It has caused me quite an amount of grief before :)