Call stored procedures with Entity Framework 6 code first approach in Asp.Net MVC 5

asp.net-mvc c# ef-code-first entity-framework-6 stored-procedures

Question

In ASP.NET MVC, we are utilising Ado.Net to connect to the database from our C# code. I must utilise stored procedures with Entity Framework in order to deal with it, calling stored procedures from EF.

There aren't many examples of how to utilise stored procedures with Entity Framework that are specifically geared at ASP.NET MVC and code-first approaches, at least none that I could discover.

They are a good beginning, but I need more, greater details, and better illustrations!

I have the following saved process:

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 the input variables, and@EmployeeId is a parameter for the output, returning theID of the brand-new employee.

Could someone please explain how to invoke this stored procedure with the arguments using Entity Framework (code first)?

1
3
12/27/2016 9:21:33 AM

Popular Answer

A stored procedure may be used in yourDbContext as follows: class.

this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);

Update:

This is an illustration of how to summon your SP in action.

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);     
    }
}

Next Update:

You may simply proceed as follows for several parameters:

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();
4
12/27/2016 9:04:16 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow