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

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 2

To avoid this, wrap your DataReader in a using block, this will guarantee that it disposes the connection like so:

using (IDataReader reader = command.ExecuteReader())
{
      //do stuff
}

There's a property on IDataReader called IsClosed which will tell you its state.

Answer 3

Check if it's open, and if so, close it. Heads-up, if you are using the SqlHelper class this is a bug - it doesn't close the connection in some scenarios. Solution is to use either try/catch or using blocks in your code, depending on whether you're pre-2.0 or not.

Answer 4

You can use delegates too, if for any reason you can't use the using clausule, heres an example of how to accomplish that:

public delegate void TransactionRunner(DbConnection sender, DbTransaction trans, object state);

public void RunTransaction(TransactionRunner runner, object state)
    {
        RunTransaction(runner, IsolationLevel.ReadCommitted, state);
    }

public void RunTransaction(TransactionRunner runner, IsolationLevel il, object state)
    {

        DbConnection cn = GetConnection from pool
        DbTransaction trans = null;

        try
        {  
            trans = cn.BeginTransaction(il);
            runner(cn, trans, state);
            trans.Commit();
        }
        catch (Exception err)
        {
            if (trans != null)
                trans.Rollback();
            throw err;
        }
        finally
        {
            //Here you can close anything that was left open
        }
    }

Then when you need to use this just use the function and pass the function as

public void DoStuff(){
    TransactionRunner tr = new TransactionRunner(MyFunction);
    RunTransaction(tr, <a parameter>);
}
public void DoStuffInternal(DbConnection cn, DbTransaction trans, object state){
    //Do Stuff and Im sure that the transaction will commit or rollback
}

This seems like an overkill now in .Net 3.5 but this was how we did it back then in .Net 1.0... Hope it helps...

Answer 5

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.

Answer 6

Today I also came across in the same situation but... no luck in the web.

So, I wrote the below code to find if a reader is opened in a connection or generally find if a connection is ready to be used:

private bool IsConnectionReady(SqlConnection Connection)
{
    bool nRet = true;

    try
    {
        String sql = "SELECT * FROM dummy_table";

        using (SqlCommand cmd = new SqlCommand(sql, Connection))
        {
            using (SqlDataReader rdr = cmd.ExecuteReader())
            { }
        }
    }
    catch (Exception ex)
    {
        nRet = false;
    }

    return nRet;
}

The "dummy_table" is an empty dummy table in my db to check accessibility.

This is just a workaround but I should make things work and be able to check connection availability in any case.

So, I hope it helps you.

Answer 7

According to the article, you should always close the reader after you're done, even if you use a using block. A using block will close a connection, but will not close a reader. Why the inconsistency? Beats me.

Answer 8

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 9

and then try to run another query using the same SqlConnection then it will throw an error.

of course, you could enable Multiple Active Result Sets - then it doesn't throw. There are some limitations of course (aren't there always?), but it'll work. Of course, this is only intended for nesting operations. If the problem is that you have accidentally left something open (that you should have closed already), then the answer is (as already stated) using.

Answer 10

wow.. Lots of people not answering the question! The thing that no one is mentioning is multi-threaded applications. I think everyone here gets the fact that you must close the reader, but what I don't seemed to see anyone addressing is the fact that the reader may not be finished when the next request comes in. For example.. I have a table that is filled through a separate thread so that I preserve UI interaction. It would be nice to have the second third and fourth threads wait while the connection is in use. Then when it frees up do it business. Without a clean cut way for determining the whether the connection has a reader attached to it, I have to spend several minutes creating some sort of static boolean flag system for every reader in every class that MIGHT want to use the connection. A lot more complex than need be