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

  • Question

  • User1073717099 posted

    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 SP with EF. There are few examples how to use Stored Procedures with Entity Framework, but none specific for Asp.Net MVC 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 SP:

    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 outpud parameter, it shows me the ID of the new added employee.

    So can someone tell me how to use Entity Framework (Code-first) to call the stored procedures and with the parameters.

    Tuesday, December 27, 2016 8:51 AM

All replies

  • User-691209617 posted

    check this link 

    Tuesday, December 27, 2016 1:25 PM
  • User1073717099 posted

    I don't know, this workaround doesn't satisfy me. It is also not for EF 6 but an older version.

    Tuesday, December 27, 2016 2:37 PM
  • User-271186128 posted

    Hi arianit,

    So can someone tell me how to use Entity Framework (Code-first) to call the stored procedures and with the parameters.

    You could try to use the following code:

    using System.Data.Entity.Core.Objects;
    
                using(MyTestDBEntities context = new MyTestDBEntities())
                {
                    //the output parameter
                    ObjectParameter Output = new ObjectParameter("EmployeeId", typeof(Int32));
                    var query = context.spAddEmployee("AAAb", Output);
    var value = Output.Value(); //Get the output value. }

    More details, see: 

    https://code.msdn.microsoft.com/How-to-retrieve-output-e85526ba

    Best regards,
    Dillion

    Wednesday, December 28, 2016 6:42 AM
  • User1073717099 posted

    thank you but this answer helps me partly because it doesn't look like the approach of entity framework, it looks more like ADO.NET. I mean I have seen some blogs who use mapping in the DBContext class and some code more like entity framework.

    like this:

    public ActionResult ExecuteProcedure()
    {
       using(var  db = new MyDbContex())
       {
    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();
    } }

    Or in the DbContext class:

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

    but these are general examples and not specific for my SP!

    Wednesday, December 28, 2016 9:49 AM
  • User-271186128 posted

    Hi arianit,

    If you want to use SqlQuery method, you could refer to the following code:

      var idParam = new SqlParameter {
           ParameterName = "id",
           Value = 1};
     var votesParam = new SqlParameter {
           ParameterName = "voteCount",
           Value = 0,
           Direction = ParameterDirection.Output };
      var results = context.Database.SqlQuery<Person>(
          "GetPersonAndVoteCount @id, @voteCount out",
           idParam,
           votesParam);
      var person = results.Single();
      var votes = (int)votesParam.Value;

    More details, see: https://blogs.msdn.microsoft.com/diego/2012/01/09/stored-procedures-with-output-parameters-using-sqlquery-in-the-dbcontext-api/

    Best regards,
    Dillion

    Thursday, December 29, 2016 9:08 AM