ado.net managing connections

Question

Hi,

I'm populating a listview with a list of databases on a selected SQL instance, then retrieving a value from each database (It's internal product version, column doesn't always exist) I'm calling the below function to populate the second column of the listview:

item.SubItems.Add(DBVersionCheck(serverName, database.Name))

Function DBVersionCheck(ByVal SelectedInstance As String, ByVal SelectedDatabase As String)
    Dim m_Connection As New SqlConnection("Server=" + SelectedInstance + ";User Id=sa;Password=password;Database=" + SelectedDatabase)
    Dim db_command As New SqlCommand("select Setting from SystemSettings where [Setting] = 'version'", m_Connection)
    Try
        m_Connection.Open()
        Return db_command.ExecuteScalar().trim
        m_Connection.Dispose()
    Catch ex As Exception
        'MessageBox.Show(ex.Message)
        Return "NA"
    Finally
        m_Connection.Dispose()
    End Try
End Function

This works fine except it's creating a connection to each database and leaving it open. My understanding is the close()\dispose() releases only the connection from the pool in ado rather than the actual connection to sql.

How would I close the actual connections after I've retrieved the value? Leaving these open will create hundreds of connections to databases that will probably not be used for that session.

Answer 1

Add Pooling=false to your connection string. That should take care of it.

Answer 2

Two approaches you can use:

1 - Call the ClearAllPools or ClearPool method. You may prefer this so that you can make use of pooling with your application, but then clear the pools when you are done.
2 - Adjust your connection string to not pool the connection. Go here and search for "connection pooling values within the ConnectionString" for more info.