Yield with SqlDbReader Method Gives Invalid attempt to call CheckDataIsReady when reader is closed. Exception
I am calling this method with a single parameter to a call to a stored procedure that I am using to see if yield will be a benefit for my team.
This is a generic method to call stored procedures. The call to it works fine,
var parameters = new List<SqlParameter>();
parameters.Add(CreateParamter("GL45", "@prefix", SqlDbType.VarChar));
var reader = StoreProcedureReader("Yield", parameters.ToArray());
This returns what I need for the reader, however the next line
string value = reader.First()[0].ToString();
causes an excpetion, Invalid attempt to call CheckDataIsReady when reader is closed.
public static IEnumerable<IDataRecord> StoreProcedureReader(String StoredProcedureName, SqlParameter[] paramters)
{
using (var conn = new SqlConnection(MyConnectionString))
{
using (var cmd = new SqlCommand(StoredProcedureName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paramters);
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
yield return reader;
}
}
}
}
}
I have looked at a few samples here on StackOverflow and it seems that I may have missed something, one example had a close after the sqlCommand using statement, I tried that but took it back out. I can work with closing one I get something back.
I believe this problem is due to the use of the yield return. Your reader is being disposed once the enumerator is disposed which happens before the call to .First() returns.
Thus when you use the indexer [0] on the resulting IDataRecord it throws an exception because the returned IDataRecord (which is the SqlDataReader) has already been disposed.
Your best bet may be to populate and copy out the results from the reader and yield that, this will ensure the results are retrieved before the reader is disposed, i.e.
public static IEnumerable<IList<object>> StoreProcedureReader(String StoredProcedureName, SqlParameter[] paramters)
{
using (var conn = new SqlConnection(MyConnectionString))
{
using (var cmd = new SqlCommand(StoredProcedureName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paramters);
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var fieldCount = reader.FieldCount;
var results = new object[fieldCount];
for(var i = 0; i < fieldCount; ++i)
{
results[i] = reader[i];
}
yield return results;
}
}
}
}
}
Notice the return type of IEnumerable<IList<object>>, your calling code should not need to change.
This does have the disadvantage of all of the data being read into memory. Another option which would avoid this is to manually control the resulting IEnumerator<T>, so with your original code, do the following:
using(var enumerator = reader.GetEnumerator())
{
// Move to first
if(!enumerator.MoveNext())
throw new NotImplementedException();
var value = enumerator.Current[0].ToString();
}
This will ensure the resulting IEnumerator<T> is not disposed until you have read the value you are interested in.