We are using Ado.Net for working with the database from our C# code in ASP.NET MVC. I want to work with Entity Framework, so I need to use stored procedures with it, call stored procedures from EF.
There are few examples how to use stored procedures with Entity Framework, but none specific for ASP.NET MVC and code-first (at least I couldn't find any).
They are a good start but I need something more, better information and better examples!
I have this stored procedure:
Create Procedure spAddEmployee
@Name nvarchar(50),
@Gender nvarchar(20),
@Salary int,
@EmployeeId int Out
as
Begin
Insert into tblEmployees
values(@Name, @Gender, @Salary)
Select @EmployeeId = SCOPE_IDENTITY()
End
So @Name
, @Salary
, @Gender
are input parameters, and @EmployeeId
is an output parameter, it returns the ID
of the newly added employee.
Can someone tell me how to use Entity Framework (code-first) to call this stored procedure with the parameters?
You can call a stored procedure in your DbContext
class as follows.
this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);
Update:
This is an Example how to call your SP in ActionResult:
public ActionResult ExecuteProcedure()
{
using(var db = new CueEntities())
{
var parameter = 1;
var query = db.Database.SqlQuery<TestProcedure>("TestProcedure @parameter1",
new SqlParameter("@parameter1", parameter)).ToList();
return Json(query,JsonRequestBehavior.AllowGet);
}
}
Second Update:
For Multiple Params you can easily go like this:
var param1 = new SqlParameter();
param1.ParameterName = "@Value1";
param1.SqlDbType = SqlDbType.Int;
param1.SqlValue = val1;
var param2 = new SqlParameter();
param2.ParameterName = "@Value2";
param2.SqlDbType = SqlDbType.NVarChar;
param2.SqlValue = val2;
var result = db.tablename.SqlQuery("SP_Name @Value1,@Value2", param1, param2 ).ToList();