Dapper .net Multiple query 'The reader has been disposed' error

Question

Dapper .net Multiple query 'The reader has been disposed' error

c# dapper sql

Question

I have written stored procedure which return two result sets. and I have called stord procedure from C# code by dapper API

like this

var Count= result.Read<int>().ToList().FirstOrDefault();
var Employees= result.Read<EmployeeData>().ToList();

the issue is

If in stored procedure i directly return normal query then dapper api working fine

SELECT COUNT(ID) FROM  Employee

SELECT EmpId,EmpName From Employee

but when i change stord procedure and add some dynamic queries it is giving error while reading second result

changed SQL

SET @Query ='SELECT COUNT(ID) FROM  Employee;
             SELECT EmpId,EmpName From Employee'
exec (@Query);

Result of both the approches are same in sql

but in C# with dapper API for first case its working fine but for second approch (with dynamic query) its getting break at second read statemnet

var Count= result.Read<int>().ToList().FirstOrDefault();
var Employees= result.Read<EmployeeData>().ToList();<<= for second approach breaking here

Error message : "The reader has been disposed; this can happen after all data has been consumed Object name: 'Dapper.SqlMapper+GridReader'."

Rajiv

View more on Stack Overflow

Wei · Accepted Answer · 6/4/2018 6:08:47 AM

try this "use QueryMultiple"

void Main()
{
    var sql = @"
    --create test data
    CREATE TABLE #Employee
        ([ID] int, [EmpId] int, [EmpName] varchar(3))
    ;

    INSERT INTO #Employee
        ([ID], [EmpId], [EmpName])
    VALUES
        (1, 1, 'xxx'),
        (2, 2, 'bbb')
    ;

    --sql
    declare @Query nvarchar(max);
    SET @Query ='SELECT COUNT(ID) FROM  #Employee;
             SELECT EmpId,EmpName From #Employee'
    exec (@Query); 
    ";

    var result = this.Connection.QueryMultiple(sql);
    var Count = result.Read<int>().ToList().FirstOrDefault();
    var Employees = result.Read<Employee>().ToList();
}

// Define other methods and classes here
public class Employee
{
    public int? ID { get; set; }

    public int? EmpId { get; set; }

    public string EmpName { get; set; }
}

Dapper .net Multiple query 'The reader has been disposed' error

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator