How should I keep track of open DataReaders?

Question

I've been tasked to create a class that clients can use to get data from a specific data source. For example, the main routines will be

IDataReader GetDataReader(DbCommand command);
DataSet GetDataSet(DbCommand command);

I know that the Data Access Application block does this but I can't use the application block for reasons I won't explain. Anyway, I do plan to borrow some logic.

However, another part of my task is to keep track of open DataReaders. This is wanted just to verify everyone is closing their readers properly. My plan was to just have a collection of DataReaders inside this new class, that which will be added to each time the GetDataReader routine is called. At the end of the app's execution, the code will go through this collection and log warnings to a file for each reader that is still open.

So, I have 2 questions:

1. Is there anything inherently wrong with this design?
2. Is there anyway I can get the SQL command executed from the DataReader? This would greatly simplify the search for the un-closed reader. Or, do I have to store the reader/command pair to get this information?

Answer 1

Have you considered making the data access component a little higher level to enforce rules? For example, to enforce a get-in-get-your-data-and-get-out-and-close policy we only return data sets and scalers from our data access component (we also enforce other rules like only using stored procedures to interact with database). If the callers really only need the data and not so much the data reader, perhaps you can read the data from the reader and then close it, therefore not having to worrk about tracking the status of the reader.

If you do have a technical need for returning the DataReader (and there would be many), perhaps rather than returning the raw reader you could create wrapper class or a sub-class of the reader and return that instead ... e.g. a TrackedDataReader. You could then build in the book keeping into the class itself. Just a thought.