How to find out the data-reader referencing an ADO.NET connection object to fix the error: "There is already an open DataReader associated with this Command which must be closed first"

Article
02/25/2009

Sometimes while executing a DB command in ADO.NET we come across below error.

There is already an open DataReader associated with this Command which must be closed first

This error is generated when we try to execute some query using a connection (with MARS disabled) which was earlier being used by some other component to execute some other query and that component forgot to close itself. In most of the cases this component is a DataReader. A simple piece of code to generate such an error would be

SqlConnection con;

private void Form1_Load(object sender, EventArgs e)

{

con = new SqlConnection("data source=server;Integrated Security=SSPI;Initial Catalog=DBname");

con.Open();

}

private void button1_Click(object sender, EventArgs e)

{

string sql = "select * from table1";

SqlCommand cmd = new SqlCommand(sql, con);

SqlDataReader rdr = cmd.ExecuteReader();

}

private void button2_Click(object sender, EventArgs e)

{

string sql = "select * from table2";

SqlCommand cmd = new SqlCommand(sql, con);

cmd.ExecuteReader();

}

If we click button2 after button 1 we will get above error. In this code we can easily see which object was using that connection earlier but in complex real world code it is difficult and time consuming to do that as it is generally spread over multiple files/tiers.

If we really intend to get multiple resultsets simultaneously using the same connection then we would have to enable MARS (Multiple Active Result Sets) if it is SQL 2005 or above.

But if that is not the case then we have to figure out the object which was not closed properly and is still referencing the connection. So to pinpoint that object we can just put the function written below anywhere in our code and call it just before the line where the above error is generated or more preferably in the catch block used for above error. The function accepts the connection and will return the property values for all the reader/command objects still attached to that connection e.g. CommandTimeout, CommandText (sql query) etc. By searching for this query in the project we can figure out the location where the reader was left unclosed. It might be slightly difficult to do the search if the query has been built dynamically instead of a simple string literal.

Here is the function (Add “using System.Reflection” at the top)

private string GetReferencedObjects(SqlConnection con)

{

System.Text.StringBuilder result = new StringBuilder();

Type t = con.GetType();

object innerConnection = t.GetField("_innerConnection", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(con);

Type tin = innerConnection.GetType();

object rc = tin.BaseType.BaseType.GetField("_referenceCollection", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(innerConnection);

if (rc == null)

return "";

int count = Convert.ToInt32(rc.GetType().GetField("_dataReaderCount", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(rc));

object items = rc.GetType().BaseType.GetField("_items", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(rc);

MethodInfo miGetValue = items.GetType().GetMethod("GetValue", new Type[] { typeof(int) });

result.AppendFormat("<Items timestamp=\"{0}\">" + Environment.NewLine, DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString());

for (int i = 0; i < count; i++)

{

miGetValue.Invoke(items, new object[] { i });

object referencedObj = miGetValue.Invoke(items, new object[] { i });

SqlDataReader rdr = referencedObj.GetType().GetProperty("Target").GetValue(referencedObj, null) as SqlDataReader;

SqlCommand cmd = rdr.GetType().GetField("_command", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(rdr) as SqlCommand;

PropertyInfo[] properties = cmd.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

result.AppendFormat("\t<Command id=\"{0}\">" + Environment.NewLine, i);

foreach (PropertyInfo pi in properties)

{

if (pi.PropertyType.IsPrimitive || pi.PropertyType == typeof(string))

result.AppendFormat("\t\t<{0}>{1}</{0}>" + Environment.NewLine, pi.Name, pi.GetValue(cmd, null).ToString());

if (pi.PropertyType == typeof(SqlConnection) && pi.Name == "Connection")

{

result.Append("\t\t<Connection>" + Environment.NewLine);

SqlConnection con1 = pi.GetValue(cmd, null) as SqlConnection;

PropertyInfo[] propertiesCon = con1.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

result.AppendFormat("\t\t\t<State>{0}</State>" + Environment.NewLine, con1.State.ToString());

foreach (PropertyInfo picon in propertiesCon)

{

if (picon.PropertyType.IsPrimitive || picon.PropertyType == typeof(string))

result.AppendFormat("\t\t\t<{0}>{1}</{0}>" + Environment.NewLine, picon.Name, picon.GetValue(con1, null).ToString());

}

result.Append("\t\t</Connection>" + Environment.NewLine);

}

result.AppendFormat("\t</Command>" + Environment.NewLine);

}

result.AppendFormat("</Items>" + Environment.NewLine);

return result.ToString();

}

It can be called something like this

try

{

cmd.ExecuteReader();

}

catch (InvalidOperationException ex)

{

if (ex.Message == "There is already an open DataReader associated with this Command which must be closed first")

{

string s = GetReferencedObjects(con);

if (s != string.Empty)

MessageBox.Show(s); //Or log the message somewhere

}

=A sample output would look like

<StatisticsEnabled>False</StatisticsEnabled>

<ConnectionString>data source=naresh-pc2003;Integrated Security=SSPI;Initial Catalog=Test</ConnectionString>

<DataSource>naresh-pc2003</DataSource>

<WorkstationId>NARESH-PC2008</WorkstationId>

<FireInfoMessageEventOnUserErrors>False</FireInfoMessageEventOnUserErrors>

</Connection>

<CommandText>select * from table1</CommandText>

</Command>

</Items>

You can tweak the function as per need to get more info. It uses reflection as access to private members is required to get this information.

Written by: Naresh Joshi (MSFT)

How to find out the data-reader referencing an ADO.NET connection object to fix the error: "There is already an open DataReader associated with this Command which must be closed first"

Additional resources