Why do I get a "'The reader has been disposed' error" error when using Dapper multiple query?
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'."
Popular Answer
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; }
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow