Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Executing Stored Procedure using Entity Framework DataContext

0.00/5 (No votes)
26 Jun 2015 1  
Executing Stored Procedure that returns resultset using Entity Framework DataContext

Introduction

Executing Stored Procedure that accepts parameters and returns resultset using Entity Framework DataContext with code first or database first approach.

Background

Most of the times, we need to execute stored procedures than get the result set even though we decide to use entity framework. It's pretty straight forward to leverage ADO.NET and DataSet to retrieve the result returned from the Stored Procedure. It's not that common to do this using Entity Framework DataContext.

Using the Code

Below are the steps and key points:

//create parameters to pass to the stored procedure  
//First input Parameter
var param1 = new SqlParameter { ParameterName = "@paramName1", 
SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = 1 }; 

//Second input parameter
var param2 = new SqlParameter { ParameterName = "@paramName2", 
SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = "Test Input" };                 

//third out parameter
var param3 = new SqlParameter { ParameterName = "@paramName3", 
SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Output, Size = 255 }; 

//compose the SQL
var SQLString = "EXEC [dbo].[name of the Stored Proc] @paramName1, @paramName2, @paramName3"; 

//Execute the stored procedure 
var employees= DataContext.Employee.SqlQuery(SQLString, param1, param2, param3); 

//or you can execute the SP using below 
//var employees = DataContext.Database.SqlQuery<Employees>
(SQLString, param1, param2, param3)

Points of Interest

  1. In the above sample, DataContext would be the name of your DataContext Instance variable
  2. Employee would be name entity (POCO class) mapping to your database
  3. If the resultset returned by the SP would be mapped to the IEnumerable collection of Entity - The above sample would return IEnumerable<Employee>
  4. We can use <DataContext>.DataBase.SqlQuery method or <DataContext>.<EntityName>.SqlQuery
  5. Your out param declared would have value returned by SP
  6. The connect sting should have MARS = true - MultipleActiveResultSets=true

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here