CODEWITHSUNDEEP
c#mysqlsqlentity-frameworkstored-procedures
Eran Buddhika
Eran Buddhika

Reputation: 15

How to get mysql stored procedure result set to C# with entity framework

I am trying to get mysql stored procedure result set to list using entity framework. I am trying this code:

using (var db = new DistributorEntities())
{
    var returnedData = db.EmployeeInfor(); 
}

but returnedData value is 0.

This is my mysql stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `EmployeeInfor`()
BEGIN

DECLARE Type1 VARCHAR(30) DEFAULT '';
DECLARE Type2 VARCHAR(30) DEFAULT '';

CREATE TEMPORARY TABLE emp
SELECT
E.`Employee Code`, E.`First Name`,
E.`Middle Name`, E.`Last Name`,
E.Address, E.`Date of Birth`,
E.`NIC No`, E.`Employee Type 1`,
E.`Employee Type 2`, Type1,
Type2, E.`E mail`
FROM employee AS E
where E.isactive = 1;

CREATE TEMPORARY TABLE empType
SELECT `Employee Type Code`, `Type Description` FROM `employee type`;

SET SQL_SAFE_UPDATES = 0;

UPDATE emp AS E
INNER JOIN empType AS ET
ON E.`Employee Type 1` = ET.`Employee Type Code`
SET E.Type1 = ET.`Type Description`;

UPDATE emp AS E
INNER JOIN empType AS ET
ON E.`Employee Type 2` = ET.`Employee Type Code`
SET E.Type2 = ET.`Type Description`;

select 
E.`Employee Code`,E.`First Name`,
E.`Middle Name`,E.`Last Name`,
E.Address,E.`Date of Birth`,
E.`NIC No`,E.Type1,
E.Type2,E.`E mail`
FROM emp AS E;

drop TABLE if exists empType;
drop TABLE if exists emp;

END

How can I retrieve the data from the table? Thank you.

Upvotes: 0

Views: 1134

Answers (1)

Henry Chettiar
Henry Chettiar

Reputation: 61

You can call a stored procedure like below also in that {class name} will be your model class which will contain all the properties returned from sp.

var obj = (IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<employee>("call EmployeeInfor").ToList();

Upvotes: 1

Related Questions