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

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 r开发者_运维问答eaders 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

---

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.

---

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.