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"
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
Or
ERROR [HY000] [Microsoft][SQL Native Client]Connection is busy with results for another command
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 method:
using System
using System.Data.Common;
using System.Reflection;
using System.Text;
public class ConnectionUtility
{
public static string GetReferencedObjects(DbConnection 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;
FieldInfo fi;
if(con is System.Data.SqlClient.SqlConnection)
fi = tin.BaseType.BaseType.GetField("_referenceCollection", BindingFlags.Instance | BindingFlags.NonPublic);
else
fi = tin.BaseType.GetField("_referenceCollection", BindingFlags.Instance | BindingFlags.NonPublic);
if(fi == null)
return "";
rc = fi.GetValue(innerConnection);
if (rc == null)
return "";
object items = rc.GetType().BaseType.GetField("_items", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(rc);
int count = Convert.ToInt32(items.GetType().GetProperty("Length", BindingFlags.Instance | BindingFlags.Public).GetValue(items, null));
MethodInfo miGetValue = items.GetType().GetMethod("GetValue", new Type[] { typeof(int) });
result.AppendFormat("<ReferencedItems timestamp=\"{0}\">" + Environment.NewLine, DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString());
for (int i = 0; i < count; i++)
{
object referencedObj = miGetValue.Invoke(items, new object[] { i });
bool hasTarget = Convert.ToBoolean(referencedObj.GetType().GetProperty("HasTarget").GetValue(referencedObj, null));
if(hasTarget)
{
bool inUse = Convert.ToBoolean(referencedObj.GetType().GetProperty("InUse").GetValue(referencedObj, null));
object objTarget = referencedObj.GetType().GetProperty("Target").GetValue(referencedObj, null);
result.AppendFormat("\t<Item id=\"{0}\" inUse=\"{1}\" type=\"{2}\" hashCode=\"{3}\">" + Environment.NewLine, i, inUse.ToString(), objTarget.GetType().ToString(), objTarget.GetHashCode().ToString());
DbCommand cmd = null;
if(objTarget is DbDataReader)
{
DbDataReader rdr = objTarget as DbDataReader;
try
{
cmd = objTarget.GetType().GetProperty("Command", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(objTarget, null) as DbCommand;
}
catch {}
}
else if(objTarget is DbCommand)
cmd = objTarget as DbCommand;
if(cmd != null)
{
PropertyInfo[] properties = cmd.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
result.AppendFormat("\t\t<Command type=\"{0}\" hashCode=\"{1}\">" + Environment.NewLine, cmd.GetType().ToString(), cmd.GetHashCode().ToString());
foreach (PropertyInfo pi in properties)
{
if (pi.PropertyType.IsPrimitive || pi.PropertyType == typeof(string))
result.AppendFormat("\t\t\t<{0}>{1}</{0}>" + Environment.NewLine, pi.Name, pi.GetValue(cmd, null).ToString());
if (pi.PropertyType == typeof(DbConnection) && pi.Name == "Connection")
{
DbConnection con1 = pi.GetValue(cmd, null) as DbConnection;
result.AppendFormat("\t\t\t<Connection type=\"{0}\" hashCode=\"{1}\">" + Environment.NewLine, con1.GetType().ToString(), con1.GetHashCode().ToString());
PropertyInfo[] propertiesCon = con1.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
result.AppendFormat("\t\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\t<{0}>{1}</{0}>" + Environment.NewLine, picon.Name, picon.GetValue(con1, null).ToString());
}
result.Append("\t\t\t</Connection>" + Environment.NewLine);
}
}
result.AppendFormat("\t\t</Command>" + Environment.NewLine);
}
result.AppendFormat("\t</Item>" + Environment.NewLine);
}
}
result.AppendFormat("</ReferencedItems>" + Environment.NewLine);
return result.ToString();
}
}
It can be called 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" || ex.Message == "ERROR [HY000] [Microsoft][SQL Native Client]Connection is busy with results for another command")
{
string s = ConnectionUtility.GetReferencedObjects(con);
if (s != string.Empty)
MessageBox.Show(s); //Or log the message somewhere
}
}
A sample output would look like this:
<ReferencedItems timestamp="7/25/2009 4:54:13 AM">
<Item id="0" inUse="True" type="System.Data.Odbc.OdbcCommand" hashCode="36181605">
<Command type="System.Data.Odbc.OdbcCommand" hashCode="36181605">
<CommandText>select * from table1</CommandText>
<CommandTimeout>30</CommandTimeout>
<DesignTimeVisible>True</DesignTimeVisible>
<Connection type="System.Data.Odbc.OdbcConnection" hashCode="28068188">
<State>Open</State>
<ConnectionString>Driver={SQL Native Client};Server=naresh-pc2003;Database=OleDbTest; Uid=sa1;</ConnectionString>
<ConnectionTimeout>15</ConnectionTimeout>
<Database>Test</Database>
<DataSource>Server1</DataSource>
<ServerVersion>09.00.4035</ServerVersion>
<Driver>SQLNCLI.DLL</Driver>
</Connection>
</Command>
</Item>
</ReferencedItems>
The method uses reflection as access to private members is required to get this information. You can tweak the function as per need to get more info.
It has been tested for SqlConnection, OdbcConnection and OledbConnection only but should work fine with other types too as long as internal structure is same.
Author : Naresh Joshi , SQL Developer Technical Lead, Microsoft