How to tell if a SqlConnection has an attached SqlDataReader?

Question

This is something now more of curiosity than actual purpose. If you have a SqlConnection opened and attach a SqlDataReader to it, and then try to run another query using the same SqlConnection then it will throw an error. My question is how does the SqlConnection know that a reader is attached to it. There is not a public property or anything for HasDataReader, so how does the SqlConnection class know?

Original Question: (which is no longer relevant)

Hi, I'm setting up a little thing for connection pooling and on of the more common bugs that we have occur(its always an easy fix, but we just can't remember reader.Close()!) it is when we have a connection that is used by a lot of classes/methods and one method opens a data reader and forgets to close it. This isn't really bad cause a lot of times all you have to do is go into the debugger and go up one level and see the function before it was and check if it had an unclosed data reader.

Now, here is the bigger problem. In this connection pool, if a datareader is open, then it's not known until a thread gets a connection and tries to use it and the thing that originally opened the data reader may no longer even be alive.

So quite simply, how can you detect if a data reader is open on a connection and is there a way to close the reader without closing the connection?

Answer 1

how does the SqlConnection know that a reader is attached to it

As far as I can see, the SQLConnection knows that it has a reader attached to it because it maintains a reference to it internally.

A judicious use of Reflector shows that the SQLConnection object has a private field of type DBConnectionInternal, which is filled with one of a number of concrete implementations of this abstract class. When you try to add a second live reader to the connection the method 'ValidateConnectionForExecute' is called on the internal connection, and this traces through to an examination of an internal 'ReferenceCollection'. When this reveals an existing live reader, an exception is thrown.

I guess, if you wanted, you could dig all this out yourself at runtime with reflection.

Answer 2

The way to make sure you close your datareaders (and database connections) is to always open them in a using block, like so:

using (SqlDataReader rdr = MySqlCommandObject.ExecuteReader())
{
    while (rdr.Read())
    {
        //...
    }
} // The SqlDataReader is guaranteed to be closed here, even if an exception was thrown.

Answer 3

Nobody really answered earlz's question. ("Why are you doing it that way?" isn't an answer.) I think the answer is that you can't tell whether a connection has an open data reader associated with it just by looking at the connection itself. The connection doesn't expose any property that will tell you that. Opening a connection sets its State property to ConnectionState.Open. Opening a data reader on it doesn't change the connection state. State values like ConnectionState.Fetching are used only while data operations such as SqlDataReader.Read() are in progress. When the connection is just sitting there between Reads the connection state is just Open. So to determine when an open reader is using the connection you have to check the states of the readers that might be using it.