Reader.IsConsumed is false but object was disposed

dapper idisposable objectdisposedexception sqldatareader

Question

I'm using QueryMultiple which returns a GridReader.

Since I don't know how much data I'm gonna read, I'm looping over the reader with the stop condition of IsConsumed:

using (var reader = conn.QueryMultiple(mySql)) {
  while(!reader.IsConsumed) {
    reader.Read<...>
  }
}

However, I'm always getting an ObjectDisposedException on the last read. The value of IsConsumed is still false.

I've tried to pass DynamicParameters to the query with the intention of getting a callback (which seems to be useful via IParameterCallbacks), but I couldn't patch it together.

I would really rather not to have such an expected exception in the code. Thanks for any help.

I'm using SQL Server, my provider is System.Data.SqlClient in .NET 4.5, Dapper version 1.40.0.0

A failing test for example:

        [TestMethod]
        public void QueryMultipleWithCursor()
        {

            const string sql = @"
DECLARE @CurrentDate DATE
DECLARE DatesCursor CURSOR LOCAL FOR
    SELECT DISTINCT DataDate FROM Data_Table ORDER BY DataDate 
OPEN DatesCursor
FETCH NEXT FROM DatesCursor INTO @CurrentDate

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT DISTINCT
        DataDate AS Date1,
        DataDate AS Date2
        FROM Data_Table
        WHERE DataDate=@CurrentDate

    FETCH NEXT FROM DatesCursor INTO @CurrentDate
END
CLOSE DatesCursor
DEALLOCATE DatesCursor";

            using (var conn = _database.GetConnection())
            {
                    var reader = conn.QueryMultiple(sql);
                    while (!reader.IsConsumed)
                    {
                            reader.Read<DateTime, DateTime, DateTime>(
                                (date1, date2) => date1,
                                splitOn: "Date2").ToList();
                    }
            }
        }

I'm getting a NullReferenceException with the following stack:

at Dapper.SqlMapper.GridReader.NextResult() in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 4440
   at Dapper.SqlMapper.GridReader.<MultiReadInternal>d__9`8.System.IDisposable.Dispose()
   at Dapper.SqlMapper.GridReader.<MultiReadInternal>d__9`8.MoveNext() in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 4309
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.GridReader.Read[TFirst,TSecond,TReturn](Func`3 func, String splitOn, Boolean buffered) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 4330
   at Project.MyTests.QueryMultipleWithCursor() in C:\Project\MyTests.cs:line 171
Result Message: 
Test method Project.MyTests.QueryMultipleWithCursor threw exception: 
System.NullReferenceException: Object reference not set to an instance of an object.

Accepted Answer

Well it seems to be an issue with Dapper implementation, for the mean time I'm using both Dapper and SqlDataReader, which is more reliable:

public static SqlMapper.GridReader QueryMultipleStoredProcedure(this IDbConnection dbConnection, string spName, object parameters, out SqlDataReader sqlDataReader)
        {
            var gridReader = dbConnection.QueryMultiple(spName, new DynamicParameters(parameters), commandType: CommandType.StoredProcedure);
            sqlDataReader = typeof (SqlMapper.GridReader).GetInstanceField<SqlDataReader>(gridReader, "reader");
            return gridReader;
        }

        private static T GetInstanceField<T>(this Type type, object instance, string fieldName)
        {
            var bindFlags = BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic;
            var field = type.GetField(fieldName, bindFlags);
            return (T) field?.GetValue(instance);
        }

And then I can use sqlDataReader.HasRows


Expert Answer

I've pushed the following, which passes on SQL Server / SqlConnection; so it can work:

[Fact]
public void SO35554284_QueryMultipleUntilConsumed()
{
    using (var reader = connection.QueryMultiple(
        "select 1 as Id; select 2 as Id; select 3 as Id;"))
    {
        List<HazNameId> items = new List<HazNameId>();
        while (!reader.IsConsumed)
        {
            items.AddRange(reader.Read<HazNameId>());
        }
        items.Count.IsEqualTo(3);
        items[0].Id.IsEqualTo(1);
        items[1].Id.IsEqualTo(2);
        items[2].Id.IsEqualTo(3);
    }
}

I wonder if the issue here is a problem with a specific ADO.NET provider. You might want to specify exactly:

  • what backend RDBMS / etc you are using (SQL Server? Oracle? Postgresql? ...?)
  • what ADO.NET provider you are using
  • what runtime (.NET what.what? core-clr?) / OS you are using
  • what exact library version you are using (the above is against the source code, which is most similar to 1.50.0-beta8)


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why