Is there a way to detect open readers on a SqlConnection?

Question

I am getting the error "New transaction is not allowed because there are other threads running" in an application I am working on. It has arisen during refactoring and specifically during creation of a test suite.

I realise from looking around that it means that I probably have a data reader still open when I am creating the transaction, however it's a complex app and it's not obvious to me where the problem is. Therefore I'd like to be able to work out what readers are connected to the SqlConnection in question.

Ideally I want to be able to add a watch in Visual Studio and then step through in debug mode to see when the number of connected readers change.

Is there any way to do this? I am working in C#.

Thanks in advance.

Martin

Answer 1

You might find this thread of interest.

Edit: Looks like they got at DbConnectionInternal with Reflector, and a free version is available. As for reflection, it's not too complicated really. There's a decent overview on MSDN.

Edit2: Just realized that you already figured it out. Great. :) I'll leave the edit in case somebody else wants more info.

Answer 2

Phew! Well I know a lot more about Reflection now!

For anyone else looking for the answer to this, here's a method that returns the number of data readers on a SqlConnection.

public static int CountConnectedReaders(SqlConnection conn)
{
    int readers = 0;
    Type t = conn.GetType();
    MemberInfo[] minf = t.GetMembers(BindingFlags.NonPublic | BindingFlags.Instance);
    for (int i = 0; i < minf.Length; i++)
    {
    if (minf[i].Name == "get_InnerConnection")
    {                    
        MethodInfo methinf = (MethodInfo)minf[i];

        object result = methinf.Invoke(conn, new object[0]);

        PropertyInfo[] pinfs = result.GetType().GetProperties(BindingFlags.NonPublic | BindingFlags.Instance);
        foreach (PropertyInfo pinf in pinfs)
        {
        if (pinf.PropertyType.Name == "DbReferenceCollection")
        {
            object dbrc = pinf.GetValue(result, new object[0]);
            if (dbrc == null) readers = 0;
            else
            {
            MemberInfo[] dbrcInfs = dbrc.GetType().GetMembers(BindingFlags.NonPublic | BindingFlags.Instance);
            foreach (MemberInfo dbrcInf in dbrcInfs)
            {
                if (dbrcInf.Name == "_dataReaderCount")
                {
                FieldInfo finf = (FieldInfo)dbrcInf;
                readers = (Int32) finf.GetValue(dbrc);
                }
            }
            }
        }
        }
    }
    }

    return readers;
}

Interestingly, using this in my problem code suggests there are no data readers open on the connection when I get the "New transaction is not allowed because there are other threads running" error so back to the drawing board (or at least another SO question) with that.