Memory Leak Returning Reader

Colonel Angus · March 10th, 2004, 01:48 PM

I am using the following retrieval method in one of my classes:

Code:

Public Function GetMyData() As SqlDataReader
        Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectString"))
        Dim cmd As New SqlCommand("sp_GetMyData", conn)
        Dim myReader As SqlDataReader

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Bit))
        cmd.Parameters("@MyParam").Value = 0
        conn.Open()
        myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Return myReader
    End Function

As you can see, I am not explicitly closing the connection object.
Once control returns to the calling page, does Garbage Collection recognize the connection object falling out of scope and close it? Does the connection pool hold on to it until the pool limit is reached? I'm afraid of causing a memory link in this case. Is there a way to close the connection and still pass the reader back to the calling page?

Thanks in advance

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

planoie · March 10th, 2004, 02:54 PM

A DataReader is a connected object. You cannot return it after closing it's parent connection. Once you close the connection associated with the reader, you loose the reader.

My suggest is to return a DataTable. Fill a table in a DataSet, and return that table. You can close the connection after the table is filled and not loose the data in it because the DataSet (and tables contained within it) is disconnected.

Peter
------------------------------------------------------
Work smarter, not harder.

Colonel Angus · March 11th, 2004, 04:42 PM

I see your point, and I understand the disconnected, xml-based nature of the dataset. I like your solution, and thank you.

However, I would still like to know if the code I posted would cause a memory leak, would garbage collection close my connection object?

-Colonel

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

Imar · March 11th, 2004, 06:29 PM

I don't think it would cause a memory leak, but it will certainly degrade performance of your site.

First of all, it *is* possible to have a method return a DataReader. The CommandBehavior.CloseConnection enum more or less says: when you close the Reader, close the associated Connection as well. So, as long as you have something like this:

MyReader = GetMyData()
' Do something with the reader
MyReader.Close

the connection will be closed too.

As you said it already, the objects in the page will go out of scope. Depending on when the garbage man comes by however the connection may already have timed out and is already closed. So, I think this doesn't result in a memory leak, but it will definitely result in bad performance and useless open connections if you don't explicitly close the Readers. So, explain how it works, and make sure everyone always closes their Readers when they are done with it ;)

Personally, I would use the Reader when high speed performance was very important. For all other scenario's, I would go with the method Peter suggested.

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Colonel Angus · March 12th, 2004, 09:53 AM

Thanks, guys.

My purpose for initially choosing the Reader was to use it to populate dropdowns on my pages. I didn't want the additional (and in my opinion unnecessary) overhead of a dataset.

That being said, I think it will both maintain good performance and be slightly quicker to implement the Close method for my open Readers.

Dank U, Imar
Thanks, Peter (uh, go Flyers?

)

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

planoie · March 12th, 2004, 11:56 AM

(Yeah Flyers! er... who are the Flyers?)

Imar, can you clarify a little bit on this?

If I open a connection in a method that returns a reader, but I don't close the connection or the reader in THAT method, I can return the reader with valid data? I should then make sure I close the returned reader within the code that is calling that method. I don't recall trying it, but it certainly makes sense.

However, following my personal standards, I would consider it bad form to not close the connection within the data access method so would therefore return a DataSet or DataTable. Any idea of the overhead comparison between a DataReader and DataTable? Obviously, the table is more capable with regards to row navigation so there's some overhead there.

Peter
------------------------------------------------------
Work smarter, not harder.

Imar · March 12th, 2004, 12:08 PM

Yes, you can return the reader from the method *with* an open connection. The CommandBehavior.CloseConnection will make sure that as soon as you close the Reader, the underlying connection is closed as well.

I use this method often when I want to return speedy data from my business layer.

AFAIK (almost sure, not entirely), ASP.NET databinding will close the reader for you.

So, something like this would be fast, easy and clean:

MyReader = MyBusinessLayer.GetUsefulData();
MyDataGrid.DataSource = MyReader;
MyDataGrid.DataBind();

At this point, the grid will be bound, the Reader is closed and finally the connection is closed as well.

It's hard to create a real clean, independent business layer without links to the outside world. DataSets make that possible, but they are not always the right tool for the right job (That is, I don't see the point in using an "entire" DataSet, just to bind a simple Category drop-down filter).
I agree it's bad practice to leave it up to the presentation layer to make sure your connection is closed. However, with CommandBehavior.CloseConnection I certainly find it acceptable, especially with the Framework as a backup to close the Reader (if that is the case, anyway).

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

planoie · March 12th, 2004, 12:14 PM

Good point, by using the CloseConnection enum, the business tier is implicitly responsible for closing the connection (assuming that reader's consumer closes it). Thanks for the clarification.

Imar · March 12th, 2004, 12:21 PM

Yes. The business layer does it best to close the connection. Of course, naughty presentation layer can still mess things up, but at least the business layer tried.... ;)

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Colonel Angus · March 12th, 2004, 01:38 PM

FYI, my code is now looking like this:

MyReader = MyBusinessLayer.GetUsefulData();
MyDropDownList.DataSource = MyReader;
MyDropDownList.DataBind();
MyBusinessLayer.GetUsefulData.Close()

and I am using CommandBehavior.CloseConnection where the Reader originates.

P.S. - (Peter) The Flyers = http://www.philadelphiaflyers.com/

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.