how to close active SQL Server connections?

Question

I am using following code in dispose() method

 Public Sub Dispose() Implements System.IDisposable.Dispose
            If Not IsDBNull(Command) And Not Command Is Nothing Then
                Dim tmpsqlcon As SqlConnection
                tmpsqlcon = Command.Connection
                Debug.Assert(Not IsDBNull(tmpsqlcon))
                SqlConnection.ClearPool(tmpsqlcon)
                tmpsqlcon.Close()
                Command.Dispose()
                tmpsqlcon.Dispose()
            End If
        End Sub

and calling this method everytime in the database operation. But it still show me too many active connection.

how do you use command and connection in code. – fofik Feb 22 '13 at 14:37 — fofik, Feb 22 '13 at 14:37

score 0 · Answer 1 · edited May 23 '17 at 12:27

You don't need to use dispose if you connect to your db with using statement:

Example:

Dim queryString As String = _
    "SELECT OrderID, CustomerID FROM dbo.Orders;" 

Using connection As New SqlConnection(connectionString)
    Dim command As New SqlCommand(queryString, connection)
    connection.Open()

    Dim reader As SqlDataReader = command.ExecuteReader()

    ' Call Read before accessing data. 
    While reader.Read()
        Console.WriteLine(String.Format("{0}, {1}", _
            reader(0), reader(1)))
    End While 

    ' Call Close when done reading.
    reader.Close()
End Using

this is the way you should connect to your db.

some more info:

how to close a sqlconnection in asp.net

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.close.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

how to close active SQL Server connections?

1 Answers1