Disposing of a Connection using SQLHelper

[Proc]

My application makes extensive use of the SQLHelper class provided
with the Microsoft DAAB. The problem I am having is when I have a
bunch of users use my application, I get the following exception
periodically:

---------------------------
System.Data.SqlClient.SqlException: Timeout expired. The timeout
period elapsed prior to completion of the operation or the server is
not responding.
at System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection
(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.SessionState.SqlStateConnection..ctor(String
sqlconnectionstring)
---------------------------

Now, I know the problem has to do with connection pooling, but I am
not sure how to resolve it. Here is a sample of my code (some details
have been removed to illustrate the important parts):

---------------------------
strSQL = "SELECT STATEMENT HERE"
objDR = SqlHelper.ExecuteReader(strConnString, CommandType.Text,
strSQL)
If (objDR.Read) Then
'Do Something
End If
objDR.Close()
---------------------------

I believe that I simply have to "dispose" of the connection opened by
SqlHelper, but I have no idea how to get to it. Am I doing something
wrong? Is there any way to fix this without explicitly creating my
own Connection object and passing it into the ExecuteReader?

Thanks for any information you can provide.

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

Here is what I would do. Switch overloads to the signature:

ExecuteReader(SqlConnection connection, _
CommandType commandType, string commandText)

Then, alter your code like so:

strSQL = "SELECT STATEMENT HERE"
Dim conn As SqlConnection = _
new SqlConnection(connString)

Try 'This might be optional for you

conn.Open()

objDR = SqlHelper.ExecuteReader(conn, _
CommandType.Text, strSQL)
If (objDR.Read) Then
'Do Something
End If

objDR.Close()
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If

conn.Dispose()
End Try


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

 

[Proc]

Thanks for the recommendation. Seems that the "ExecuteReader" with
the connection string signature should not even be available as a
"best practice", since you cannot seemingly close the Connection
object properly.

Seems like a big potential problem. Has anyone else experienced
problems with this way of doing things? Or is it just me?