locked
Need advise. Problem with connection pool. Function returning SqlDataReader. RRS feed

  • Question

  • User1701475643 posted

    Halo all

     

    I have a problem with "timeout expired. Thei timeout periode elapsed prior to obtaining a connection from the pool. This may have occured because all pooled  connections were use  and max pool size was reached"

    Then i explore and found out that i did not close my SqlDataReader, SqlDataAdapter, SqlCommand or my connection.

     

    But i have a function that return a  SqlDataReader.

    Is this function will cause a connection problem?

     

    Thanks in advance

     

     

            Public Function GetDataReader(ByVal strSQL As String, ByVal DBCon As DB.DBConnection) As SqlDataReader
                Dim MyCommand As SqlCommand = New SqlCommand(strSQL, DBCon.GetConnection())

                If DBCon.GetConnection().State = ConnectionState.Closed Then
                    DBCon.GetConnection().Open()
                End If

                Dim dr As SqlDataReader = MyCommand.ExecuteReader()

                Return dr

                dr.Close()

            End Function

    Thursday, September 6, 2007 4:49 AM

Answers

  • User1109032460 posted

    OK. That's pretty close. Here's a rough layout of what I would do if I decided to write a method that returned a DataReader

    Function GetReader( command as string ) as DbDataReader
      Dim con As DbConnection = ...  ' Get the connection information from web.config
      Dim cmd As DbCommand = ... ' Prepare the command from the command string that's passed in

      Try
        con.Open()
        Dim reader As DbDataReader = cmd.ExecuteReader( CommandBehavior.CloseConnection )
        ' This tells the reader to close the connection when it is done
        Return reader
      Catch
        If con IsNot Nothing Then con.Close()
        Throw ' Rethrow the exception because there's no way to recover from it
      End Try
    End Function

    To use this method, I'd write

    Sub MethodThatUsesDatabase()

      Dim reader As DbDataReader = Nothing
      Try
        reader = GetReader( "SELECT ... " )
        ' Use the reader
       While reader.Read()
         ' ...
       End While
      Finally
        If reader IsNot Nothing Then reader.Close()
      End Try
    End Sub

    The Try Catch block in the GetReader method ensures that the connection is closed if an exception is generated in the ExecuteReader method
    The Try Finally bloc in the MethodThatUsesADatabase method ensures that the connection is closed if a valid DataReader is returned

    There is an edge case that is not handled by this process. If an exception occurs after GetReader has been called but before the assignment to the reader variable is performed (in MethodThatUsesADatabase), then potentially you'll leave the connection open. However, that probably means that you're either calling Thread.Abort() on the thread (in which case you deserve everything you get) or because the CLR is falling apart (in which case a DB connection is the least of your problems).

    But as I mentioned in my earlier post, I prefer to write methods that

    1. Open the connection
    2. Execute the command, and
    3. Close the connection

    Otherwise you have to make sure that every developer that uses the GetReader method remembers to add the Try Finally block.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 7, 2007 4:44 AM

All replies

  • User1109032460 posted

    This function is indeed not closing the connection and is liable to cause you serious problems.

    You should avoid returning a DataReader from a method, but should instead use it and Close it, along with the connection, inside one method. You can then sensibly wrap its usage in a Try ... Finally ... End Try block.

    If you absolutely must return a DataReader from a method, then

    1. When you call ExecuteReader, pass in the flag that indicates that when the reader is closed, the connection must also be closed, and

    2. Make absolutely sure that you close the DataReader in the code that calls the method that returns the DataReader

    Thursday, September 6, 2007 4:59 AM
  • User1701475643 posted

    Ok, i also think this is the one causing the problem.

    If change some code in the function into this one,

    i suppose it close the connection, and i still can use the function without have to change any code that use this function,

    Am i right or not?

     

    Thanks for the advise.

     

            Public Function GetDataReader(ByVal strSQL As String, ByVal DBCon As DB.DBConnection) As SqlDataReader
                Dim MyCommand As SqlCommand = New SqlCommand(strSQL, DBCon.GetConnection())

                If DBCon.GetConnection().State = ConnectionState.Closed Then
                    DBCon.GetConnection().Open()
                End If

                Dim dr As SqlDataReader = MyCommand.ExecuteReader()

                <STRIKE>Return dr  </STRIKE>(before i use this method)

                GetDataReader = dr
                dr.Close()
                MyCommand.Dispose()

            End Function

    Thursday, September 6, 2007 5:15 AM
  • User1701475643 posted

    Sorry DMW, i just try change the function

    but can not work

    because i close the dr inside the function.

     

    If the code that call the function, close the dr,

    is the dr inside the function also close?

     

    This the the code that call the function:

     

    Public Sub ABC()
            Dim MyConnection As New DB.DBConnection()
            Dim dr As SqlDataReader

            dr = CommonFunction.GetDataReader("Select * From table", MyConnection)

            dr.Close()
            MyConnection.CloseConnection()

     

    end sub

    Is this code safe for preventing the connection pool to reach max?

     

    Thanks for your time DMW,

    i really appriciate it.

     

    Thursday, September 6, 2007 5:30 AM
  • User1109032460 posted

    OK. That's pretty close. Here's a rough layout of what I would do if I decided to write a method that returned a DataReader

    Function GetReader( command as string ) as DbDataReader
      Dim con As DbConnection = ...  ' Get the connection information from web.config
      Dim cmd As DbCommand = ... ' Prepare the command from the command string that's passed in

      Try
        con.Open()
        Dim reader As DbDataReader = cmd.ExecuteReader( CommandBehavior.CloseConnection )
        ' This tells the reader to close the connection when it is done
        Return reader
      Catch
        If con IsNot Nothing Then con.Close()
        Throw ' Rethrow the exception because there's no way to recover from it
      End Try
    End Function

    To use this method, I'd write

    Sub MethodThatUsesDatabase()

      Dim reader As DbDataReader = Nothing
      Try
        reader = GetReader( "SELECT ... " )
        ' Use the reader
       While reader.Read()
         ' ...
       End While
      Finally
        If reader IsNot Nothing Then reader.Close()
      End Try
    End Sub

    The Try Catch block in the GetReader method ensures that the connection is closed if an exception is generated in the ExecuteReader method
    The Try Finally bloc in the MethodThatUsesADatabase method ensures that the connection is closed if a valid DataReader is returned

    There is an edge case that is not handled by this process. If an exception occurs after GetReader has been called but before the assignment to the reader variable is performed (in MethodThatUsesADatabase), then potentially you'll leave the connection open. However, that probably means that you're either calling Thread.Abort() on the thread (in which case you deserve everything you get) or because the CLR is falling apart (in which case a DB connection is the least of your problems).

    But as I mentioned in my earlier post, I prefer to write methods that

    1. Open the connection
    2. Execute the command, and
    3. Close the connection

    Otherwise you have to make sure that every developer that uses the GetReader method remembers to add the Try Finally block.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 7, 2007 4:44 AM
  • User1701475643 posted

    Halo Dave

     

    Thanks for your explaination.

    I agree with you 100%.

    FYI, as i work as a team, and i am just moving to this company (in China),

    and found the timeout problem,

    i found the function that not closing the connection.

    When i discuss with the rest of the team,

    they strongly opposed my opinion.

     

    But now, i am more confident with my opinion,

    i now i am absolutely sure that this function causing the timeout problem.

     

    I will change the code.

    I will use your given code.

    I will realy appriciate your help. [Yes]

    Friday, September 7, 2007 5:04 AM
  • User-1802637964 posted

    hello,

    iam having the exact problems and using method to return sqldataReader.. if you have solved the issue please posted.

    thanks

    Sunday, June 16, 2013 2:45 PM