Close database connection on SQL Server using ADO .NET

Question

I'm connecting to database using ADO .NET:

        Dim conn As SqlConnection
        Dim sqlcmd As SqlCommand
        Dim da As SqlClient.SqlDataAdapter
        Dim table As DataTable

        conn = New SqlConnection(Utilities.ConnectionString)
        sqlcmd = New SqlClient.SqlCommand()
        sqlcmd.Connection = conn
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.CommandText = "mySP"
        sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param", param1))

        da = New SqlClient.SqlDataAdapter()
        da.SelectCommand = sqlcmd
        table = New DataTable()
        da.Fill(table)
        conn.Close()   
        sqlcmd.Connection.Close()

That works good. When I launch on SQL Server the command:

       EXEC SP_WHO2

For each call made from the previous code in ADO .NET, I have in the field Command the value: "AWAITING COMMAND", and in the field Status the value is "sleeping". What does this means? The connection to database is still active? What should I do in order to close db connection?

I believe that is a result of connection pooling. For performance, when a connection is 'closed' it's really just returned to the pool of available connections. — Steve Wellens, Sep 23 '12 at 11:01
Yes, in fact my problem is related to connection pool. I use the code above cyclically (one call every 20 seconds), and after a few hours of good works my application generates an error of "exhaust connection pool", and I can only clean the pool on IIS, so all work good again. — GVillani82, Sep 23 '12 at 11:04
Try using Dispose on the connection: http://stackoverflow.com/questions/61092/close-and-dispose-which-to-call. Or, you could keep the connection object around and reuse it for better performance. — Steve Wellens, Sep 23 '12 at 11:12

Steve · Accepted Answer · 2012-09-23T12:18:21.490

The fact that after few hours you receive errors about connections exausted means that somewhere in your code you don't dispose correctly of your connection.
The code above seems correct, but what happen if you have exceptions? Are you sure to handle correctly the situation when your code exits unexpectedly from a method, due to exceptions?

The correct approach to this situation is refactoring your code.
Introduce everywhere the Using pattern. So your code above become:

    Dim conn As SqlConnection 
    Dim sqlcmd As SqlCommand 
    Dim da As SqlClient.SqlDataAdapter 
    Dim table As DataTable 

    Using conn = New SqlConnection(Utilities.ConnectionString) 
        Using sqlcmd = New SqlClient.SqlCommand() 
            sqlcmd.Connection = conn 
            sqlcmd.CommandType = CommandType.StoredProcedure 
            sqlcmd.CommandText = "mySP" 
            sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param", param1)) 

            da = New SqlClient.SqlDataAdapter() 
            da.SelectCommand = sqlcmd 
            table = New DataTable() 
            da.Fill(table) 
        End Using  
    End Using

This approach will ensure that your disposable objects (connection and command) will be released correctly and you will remove the subtle problem of connection leakings.

I tried even this solution, but I'm not sure the connection with database is closed because the command EXEC SP_WHO2 still shows, for each call a row with field Command the value: "AWAITING COMMAND", and in the field Status the value is "sleeping". — GVillani82, Sep 23 '12 at 16:03
As said before, the connection pooling plays a role in this. The connection pooling keeps the connection opened also when you explicity close them. This is faster when you ask to reopen a connection using the same connection string. The pool gives you back an already opened connection. This explain also why it is a bad idea to keep a connection opened for the lifetime of your application. — Steve, Sep 23 '12 at 16:07
Ok, so when the pool will be full, if the close procedure is correct, I shouldn't have any problem! thank you! — GVillani82, Sep 23 '12 at 16:14

Close database connection on SQL Server using ADO .NET

1 Answers1