[RESOLVED] Return SqlDataReader and properly dispose

[Chris H]

I want to handle the connection to my database and the return of a SqlDataReader from a single routine and be able to reuse it many times from different areas of my code. However, to avoid memory leaks I need to make sure that the reader and the connection are both properly disposed of since there will be lots of data to plow through.

How do you properly handle the disposal of the SqlDataReader and SqlConnection when you return the SqlDataReader to your calling routine? Would the SqlDataReader be returned by reference? If it is, I can dispose of it from within the calling routine, but what would I do about the SqlConnection object?

Thanks

[techgnome]

The connection needs to remain open for as long as you want your reader to be valid.
The fact that you want to pass the reader all over the place though suggests a possible design issue.
Firstly, they are forward only and read only... so they can only be used to read data. Secondly because of their dependency on the connection, and the tendency of ADO.NET to want to work in a disconnected state, you really should get your data and get out, don't keep the connection open any longer than necessary. ALSO, once you attach a reader to a connection, that connection becomes dedicated to that reader and that reader only... you can't use it (until the connection is closed and reopened, or is released by the reader -which is done by closing the reader) for anything else.
What you may want to consider is using the reader to fill a datatable, then using that where you need it.

Datatables can be worked on disconnected so you wouldn't need to worry about the connection, also, they can be moved through forward and back, so you can use it in multiple places, and they avoid multiple trips to the database (which is what will happen when you .Read the next row in the reader.

If there is that much data, you just might be better off loading it into memory and then plowing through it.

-tg

[danasegarane]

You can also use the CommandBehavior.CloseConnection method

[Chris H]

Thanks gnome. I may have worded my question wrong. I want to centralize my database connectivity. The SqlDataReader would only be passed back to one function but I want other functions to be able to use the same routine to establish a connection to the DB and get a SqlDataReader from it.

I guess you could say I'm looking to eliminate redundant code.

danasegarane: The CloseConnection may be just the thing I'm looking for. My calling function can handle closing the SqlDataReader, which would then close the SqlConnection as well. I will definitely have to look into that feature further. I never knew it existed.

[Techno]

its normal practice to use SqlDataReader per call since each method would be reading different data. in other words, every function (method) say in your BLL would be:

Read Orders
Read Customers
Read Companies

each would be using their own SqlDataReader to read and construct the objects in question. This is quite common practice and is fine, but to keep it left open is not - even if you want to ever use 1 instance throughout of the DataReader. also remember, DataReader is a forward only reader and can only read 1 resultset at a time

as soon as you close the DataReader - it will be disposed and its also good practice, as mentioned earlier, that when you are calling ExecuteReader, set the CommandBehavior.CloseConnection