Question about sql connections staying open using vb.net and Using clause RRS feed

  • Question

  • I'm using the following function to access data in a sql server db.

    Public Function ExecuteDataTable(ByVal ConnString As String, ByVal SqlQuery As String, ByVal CmdType As System.Data.CommandType) As DataTable

                    Using SqlConn As New SqlConnection(ConnString)
                        Using SqlCmd As New SqlCommand(SqlQuery, SqlConn)
                            SqlCmd.CommandType = CmdType
                            Using drReader As SqlDataReader = SqlCmd.ExecuteReader()
                                Using dtTable As New DataTable
                                    Return (dtTable)
                                End Using
                            End Using
                        End Using
                    End Using
                Catch ex As Exception
                    Throw New Exception("Error in SqlDataBaseOperations.ExecuteDataTable with error: " & ex.ToString)
                End Try
            End Function

    this function is in a class called SqlDataBaseOperations that implements IDisposable. There are no objects declared in this class, just this function. I don't have any code in the Dispose method since I don't have an object to dispose.

    In the main form I'm doing this:

    Using MySqlOps As New Data.SqlServer.SqlDataBaseOperations()
                        dt = MySqlOps.ExecuteDataTable(MySqlOps.SQLServer_Local, "Select * from master", System.Data.CommandType.Text)

                        For Each dr As DataRow In dt.Rows
                            org = dr.Item("f_name").ToString
                        dt = Nothing
                    Catch ex As Exception

                    End Try
                End Using

    When this code finishes I can still see a connection to sql server (using sp_who2 in the query window) for the login I'm using.

    I was under the impression that using the Using clause would automatically dispose of connections so I don't understand why I still see a connections after execution.  It stays there until I exit completely (close the form).

    I'm concerned because I eventually want to use this ExecuteDataTable code in an application that uses parallel programming. So if I have multiple tasks using this code I'll have that many connections open. However, I expect the connections to disappear when the code finishes, not when the application closes.  Since the tasks will run every 5 minutes I don't want these connections staying open.



    Mike Rutledge
    Thursday, July 28, 2011 3:06 PM


  • Hi Rut,



    Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.



    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by jmrut Friday, July 29, 2011 11:58 AM
    Friday, July 29, 2011 6:09 AM