Menu
I have a method ("GetDataReader," let's call it) that returns a SqlDataReader. It's inside a Singleton DataFactory class that maintains a persistent connection to the database.
The problem with this is that after being returned, the DataReader is still "connected" to the Connection object in my DataFactory. So, I have to make sure the code that calls GetDataReader then calls Close() on the DataReader that comes back, otherwise, it "locks" the Connection with this:
There is already an open DataReader associated with this Command which must be closed first.
How can I "detach" the DataReader before I send it back from GetDataReader? Either that, or clone it and send back the clone? I don't want to have to make the calling code always explicitly close it.
There has to be a best practice here.
Update:
Thanks everyone for your input. The bottom line is that I need to lose the habit of using DataReaders and switch to DataTables. They're much more manageable.
Also, thanks for the note on connection pooling. I knew about it, but just didn't put two and two together and realize I was re-inventing the wheel.
586
To create a SqlDataReader, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor. While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it.
Note that disposing a SqlDataReader instantiated using SqlCommand. ExecuteReader() will not close/dispose the underlying connection. and the calling code just needs to dispose the reader thus: using(SqlDataReader reader = ExecuteReader(...))
To retrieve data using a DataReader, create an instance of the Command object, and then create a DataReader by calling Command. ExecuteReader to retrieve rows from a data source.
A SqlDataReader is read-only, forward-only cursor that allows you to iterate through the result set and read one record at a time. To read and store the customer data we create a List of Customer records (C# record). It makes sense to iterate through the result set only if there are any rows in it.
DataReader's must stay connected to the db until you no longer need them - that's the nature of using a DataReader so you can't "disconnect" them as such. When you're finished with a data reader, you should close it (.Close()) but then you can't use it any more.
From .NET 2.0 on, if you're using SQL 2005 or later, you can make use of MARS (Multiple Active Result Sets) as explained here. This allows you to use a single connection for multiple data readers and just involves a change to your connection string. However, SqlDataReaders aren't ideal for passing around your code in the way it sounds like you want.
Alternatively (which is what I think you need to do), you may want to use a disconnected resultset which is where DataSet/DataTables come in. You use an SqlDataAdapter to fill a DataSet/DataTable with all the results of a query. You can then use the connection for any other purpose, or close the connection, and it doesn't affect your in-memory resultset. You can pass your resultset around your code without needing to maintain an open database connection.
73
Do not persist you database connection. There is a feature called "connection pooling". Getting a fresh connection is not expensive.
41
Generally, best practice would be to use connection pooling rather than a persistent connection, to allow simultaneous access by multiple users. The only way I can think of to do what you want to do would be to load a DataSet from the reader and return that.
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
