Question:

I'm running a multithreaded application that connects to a database, the connections are closed when I'm done with them (and I dispose of all the threads). I've tried clearing the connection pools, setting pooling=false and I've used both .dispose and .close.

I get the error "cannot drop the database X because it is currently in use" if I try to drop the database I connected to after the connections have all been closed. Below is my code:

Dim comExecuteInsert As New SqlCommand Dim comm As New SqlConnection If (Not comm Is Nothing) Then comm = Nothing End If comExecuteInsert.Connection = comm comExecuteInsert.CommandType = CommandType.StoredProcedure comExecuteInsert.CommandText = strProcedureName comExecuteInsert.CommandTimeout = 26000 comExecuteInsert.Parameters.Add("@tableName", SqlDbType.VarChar, 100).Value = strTableName comExecuteInsert.Parameters.Add("@filename", SqlDbType.VarChar, 500).Value = strFileName comExecuteInsert.ExecuteScalar() comExecuteInsert.Parameters.Clear() comExecuteInsert = Nothing comm.Close() SqlConnection.ClearPool(comm)

The stored procedure creates temporary tables which it later drops and inserts data into tables existing in the database.

You are not disposing your command object.

Instead of

comExecuteInsert = Nothing

try

comExecuteInsert.Dispose

Or try the following:

Using comm As New SqlConnection Using comExecuteInsert As New SqlCommand comExecuteInsert.Connection = comm comExecuteInsert.CommandType = CommandType.StoredProcedure comExecuteInsert.CommandText = strProcedureName comExecuteInsert.CommandTimeout = 26000 comExecuteInsert.Parameters.Add("@tableName", SqlDbType.VarChar, 100).Value = strTableName comExecuteInsert.Parameters.Add("@filename", SqlDbType.VarChar, 500).Value = strFileName comExecuteInsert.ExecuteScalar() comExecuteInsert.Parameters.Clear() comm.Close() End Using End Using

If you are trying to close any connection to a db so you can drop it, you can do execute something like the following against the master database in Management Studio:

Alter Database MyDatabaseName Set Single_User With Rollback Immediate GO Drop Database MyDatabaseName GO

To do this in code, you would need to open a separate connection to the master database and execute each of the above statements individually (and not try to execute the GO words). Keep in mind that this will kill <em>all</em> connection to the database including ones other than your own no matter what they might be doing.

I missed the bit where you said you were dropping a database. You need to use

SqlConnection.ClearAllPools()

SQL Server caches connections for re-use. The above clears the cached connections.

For example...

Sub Demo_DropDatabase(ByVal strCnn As String, ByVal strDBName As String) Using cnnSQLS As New SqlConnection(strCnn) SqlConnection.ClearAllPools() cnnSQLS.Open() Dim strSQL As String = "DROP DATABASE [" & strDBName & "]" Using cmdDrop As New SqlCommand(strSQL, cnnSQLS) cmdDrop.ExecuteNonQuery() 'N.B. may throw exception ' End Using cnnSQLS.Close() End Using End Sub

use the using keyword, see <a href="https://stackoverflow.com/questions/376068/does-end-using-close-an-open-sql-connection" rel="nofollow">Does End Using close an open SQL Connection</a>