This is the department table which contains information about department
Dept_Id
Dept_Name
Dept_Desc
This is the employees
table which contains information about employees.
Emp_Id
Emp_Name
Emp_Desc
Emp_Salary
Stored procedure that returns multiple resultsets, both resultsets can not be mapped to any existing dto:
Create procedure MyProcedure
(@DId int)
As
Begin
Select
Dept_Id, Dept_name, dept_desc,
count (e.Emp_Id) as ‘TotalEmpInDepartmentNo’
From
departments d
Join
Employees e On d.dept_id = e.dept_id
Where
d.dept_id = @DId
Select *
from products
Where dept_id = @dept_id
End
go
Complex dto which contains information regarding a category .
public int Dept_Id { get; set; }
public string Dept_Name{ get; set; }
public string Dept_Desc{ get; set; }
public int Count { get; set; }
// Count represents total no of employees in each dept
public IEmployeeDTO IList<IEmployeeDTO> EmployeesList { get; set; }
//list represents employees list corresponding to the particular department
Employees dto which contains information about a particular employee
public int Emp_Id{ get; set; }
public string Emp_Name{ get; set; }
public string Emp_Desc{ get; set; }
public string Emp_Salary{ get; set;}
First result sets contains a single row which contains information about a department
department id
name
desc
count --> total no of employees in this dept
Second resultset contains multiple rows of employees belong to that particular dept.
I want to call the procedure through Entity Framework code.
I want to do 2 things inside using :
Now I want to map the first result set to the starting four properties of complexDTO using entityConverter
I will create a separate employeeDTO for each row in second resultset , and after converting that DTO from entityconverter , I will add that EmployeeDTo to the list .