Why do I get a "'The reader has been disposed' error" error when using Dapper multiple query?

c# dapper sql

Question

Two result sets from a stored process I wrote are returned. and I used the dapper API to invoke the stord method from C# code.

like as

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

the problem is

If I directly return a regular query in a stored procedure, the Dapper api will function as intended.

SELECT COUNT(ID) FROM  Employee

SELECT EmpId,EmpName From Employee

however it gives an issue while reading the second result when I update the stord process and add some dynamic queries.

altered SQL

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

The results from both approaches are same in sql.

nonetheless, the second approach (with a dynamic query) fails at the second read when using the Dapper API in C#. statemnet

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

Mistake message: "After all data has been used, the reader may have been discarded. 'Dapper.SqlMapper+GridReader' is the name of the object."

1
2
6/4/2018 5:45:12 AM

Popular Answer

use this "try"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; }
}

4
6/4/2018 6:08:47 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow