Disposing of a Connection using SQLHelper

  • Thread starter Thread starter Proc
  • Start date Start date
P

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.
 
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!
************************************************
 
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?
 
Back
Top