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."
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; }
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow