Dapper GridReader already Disposed Error

.net dapper sql sql-server

Question

I'm using Dapper dot net to execute a stored procedure that returns 4 result sets. Here's how I'm doing it:

    public Results Search(Query query)
    {
        if (query == null) throw new ArgumentNullException("query");

        Results results;
        var q = _sqlConnection.QueryMultiple("MySchema.MySproc", query,
                                             commandType: CommandType.StoredProcedure);
        {
            results = q.Read<Results>().First();
            results.CheckAlertResults = q.Read<Results.CheckAlertResult>().ToArray(); // Cannot access a disposed object.Object name: 'GridReader'.
            results.PersonAlertResultRows = q.Read<Results.PersonAlertResultRow>().ToArray();
            results.RoutingAlertResults = q.Read<Results.RoutingAlertResult>().ToArray();
        }

        return results;
    }

The first result set will only contain 1 row. It corresponds to a couple of primitive properties on my Results class.

The other 3 result sets will have many rows and will populate 3 complex array properties on the Results class.

For some reason, I'm getting

Cannot access a disposed object.Object name: 'GridReader'.

Check my code to see where.

I have verified that the procedure is working correctly when called from LinqPad, which uses Linq2Sql.

What am I doing wrong?

Accepted Answer

The reader will dispose itself when it fails to find any more results grids; basically, your C# code looks fine, but the suggestion is that your proc is only returning 1 grid. The following works fine, for example:

using (var reader = connection.QueryMultiple(
    "select 1; select 2 where 1 = 0; select 3 where 1 = 0; select 4;"))
{
    var one = reader.Read<int>().ToArray();
    var two = reader.Read<int>().ToArray();
    var three = reader.Read<int>().ToArray();
    var four = reader.Read<int>().ToArray();
    try { // only returned four grids; expect a fifth read to fail
        reader.Read<int>();
        throw new InvalidOperationException("this should not have worked!");
    }
    catch (ObjectDisposedException) {/* expected; success */}

    one.Length.IsEqualTo(1);
    one[0].IsEqualTo(1);
    two.Length.IsEqualTo(0);
    three.Length.IsEqualTo(0);
    four.Length.IsEqualTo(1);
    four[0].IsEqualTo(4);
}

I might try to improve the error message, but I suspect the error is in your SP.



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