How do you completely close an SQLConnection string in vb.net?

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.

Is it that you are trying to drop the database or just ensure you close the connections (which actually returns the connection to the connection pool and only later actually closes it)? — Thomas, May 16 '11 at 18:51
@mellamokb I close the connection with comm.Close() the second to last line of my code. — RobinReborn, May 16 '11 at 18:53
@Thomas I want to close the connection and I want to drop the database. — RobinReborn, May 16 '11 at 18:53
@Thomas Yes I do, but I can successfully do that before I open the connections. — RobinReborn, May 16 '11 at 18:57

score 2 · Answer 1 · answered May 16 '11 at 18:59

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 all connection to the database including ones other than your own no matter what they might be doing.

score 2 · Accepted Answer · answered May 17 '11 at 05:54

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

SSS · Answer 3 · 2015-05-24T11:06:19.930

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

score 1 · Answer 4 · edited May 23 '17 at 11:58

1

use the using keyword, see Does End Using close an open SQL Connection

edited May 23 '17 at 11:58

Community

1
1

answered May 16 '11 at 18:43

Ivo

3,406
4
33
56

I've tried that and it doesn't work, perhaps there's something in my stored procedure that is keeping my database open. – RobinReborn May 16 '11 at 18:58

score 0 · Answer 5 · answered Oct 09 '19 at 09:30

Use below T-SQL in your VB.Net Code to remove existing connections.

TSQL

declare @vSqlTx AS varchar(max);

select @vSqlTx = concat(@vSqlTx, ' KILL ', spid, ';', CHAR(13))
from sys.sysprocesses
where DB_NAME(NULLIF(dbid, 0))  = <'yourDatabaseName'>
and spid != @@spid;

print @vSqlTx;
exec (@vSqlTx);

VB.NET Code:

Private Sub CloseAllOtherConnections(ByVal conStr As String, ByVal databaseName As String)
    Dim strSqlText As String 

    Using con As New SqlConnection(conStr)      
        SqlConnection.ClearAllPools()
        con.Open()


        strSqlText = "  declare @vSqlTx AS varchar(max);

                    select @vSqlTx = concat(@vSqlTx, ' KILL ', spid, ';', CHAR(13))
                    from sys.sysprocesses
                    where DB_NAME(NULLIF(dbid, 0))  = '" & databaseName & "'
                    and spid != @@spid;

                    print @vSqlTx;
                    exec (@vSqlTx); "

        Dim cmd As New SqlCommand(strSqlText, con)
        cmd.ExecuteNonQuery()       
        con.Close()     
  End Using

End Sub

How do you completely close an SQLConnection string in vb.net?

5 Answers5

Linked