Re: Try/Finally to prevent connection pooling leak

spinout · June 13th, 2007, 02:42 AM

Howdy!

I'm having an issue with connection pooling leaks somewhere in my application and after having done some reading on the issue I've concluded that this can be solved by using Try/Finally.

Problem is, when I place that inside my SelectDB function, the application falls over. Can anyone spot what I am doing wrong??

Code:

Shared Function SelectDB(ByVal strSqlStatement As String) As SqlDataReader

Dim strConnString As String = System.Configuration.ConfigurationManager.AppSettings("SqlConnString")
        Dim objSqlConn As New SqlConnection(strConnString)

    If objSqlConn.State = ConnectionState.Open Then
            objSqlConn.Close()
        End If

    Dim objSqlReader As SqlDataReader
        Dim strSqlCmd As New SqlCommand(strSqlStatement, objSqlConn)
      Try
        objSqlConn.Open()
        objSqlReader = strSqlCmd.ExecuteReader()
        Return objSqlReader
      Finally
        objSqlReader.Close()
        objSqlConn.Close()
      End Try

End Function

It returns the error "Invalid attempt to Read when reader is closed". Fair enough, I thought, perhaps I need to move the "return objSqlReader" down into the "finally" part of the code. No such luck though I'm afraid, that produced another error "Branching out of a 'Finally' is not valid". D'oh!

planoie · June 13th, 2007, 08:39 AM

Simply put, you cannot use a reader that was created on a connection that is now closed. Thus, you either need to return the reader WITHOUT closing the connection or return some other object.

I have used both methods.

You can create the reader with the CommandBehavior.CloseConnection option. This will close the connection when the consumer closes the reader. HOWEVER, this requires the consumer to reliably close the reader in order to free up the connection. I won't recommend NOT doing this as it results in more possibly unstable code.

Instead, go with the latter solution. If you are writing a method to return data, return a data structure such as a DataSet or DataTable instead of a data reading mechanism (the DataReader). That way you can free up the connection resource very quickly.

-Peter

Quick209 · June 13th, 2007, 04:39 PM

Also, just wanted to add thaty when you use a Try, you wanna include a catch. So in actuality it is called a Try/catch. Finally excutes no matter what but the catch is to catch the exception that is thrown in the try loop. No catch means unhandled exception if it happens

spinout · June 15th, 2007, 11:15 AM

Quote:

quote:Originally posted by planoie
You can create the reader with the CommandBehavior.CloseConnection option. This will close the connection when the consumer closes the reader. HOWEVER, this requires the consumer to reliably close the reader in order to free up the connection. I won't recommend NOT doing this as it results in more possibly unstable code.

Thanks for the reply Peter.

I have read a bit about the CommandBehaviour.CloseConnection in my various research attempts at solving my connection leak problem although I'm still a bit confused as to whether that will totally solve my problem though. I'm guessing from your response that I'm correct to assume that it probably won't?

What do you mean by returning some other object? Do you mean the other data structures like DataSet or DataTable? Problem is, I really don't understand enough about those to know how to fit them into my function or how they help to close my DB connections easier than the DataReader in order to solve my connection leak problem.

planoie · June 19th, 2007, 08:48 AM

Yes, I mean the other ADO.NET data structures. Technically speaking, the DataReader is not a data structure but a data access class. It does not really "contain" data like the DataTable. Also, the DataSet is nothing but a collection of DataTables.

The difference between using a DataReader and other methods comes down to the way that the DataReader works with the database connection. I don't know specifically how it works internally, but the theory is fairly simple. Consider this psuedo code:

Code:

Open connection
Execute SQL query for DataReader (DB engine executes query, prepares result set)
DataReader.Read() (fetch 1 record from database engine result set)
   Do something with data
Loop
Close connection

This results in code processing WHILE you are fetching records from the database which occupies 1 database connection from the connection pool. Now what happens if the "Do something with data" code takes some time? You are sitting on an open connection while doing processing.

Now consider this psuedo code:

Code:

Open connection
Execute SQL query to a DataSet/DataTable (DB engine executes query, dumps to DataTable)
Close connection

For each record in result set
   Do something with data
Loop

The utilization of the database connection (the most precious resource) is optimized to take the least amount of time. Now your "do something with data" code can take all the time it needs without utilizing an open connection.

One golden rule you will see often with database interactions is "open late, close early", meaning open the connection as late as possible and close it as soon as you can.

-Peter