Execute Stored Procedure using Entity Framework in ASP.NET MVC

In this article we are going to see how to execute the stored procedure in entity framework ,In MVC we are going to see the how to add the EF.

Execute the following script in the database to create a stored procedure.

CREATE PROCEDURE FETCHEMPLOYEES

AS

BEGIN

      SELECT * FROM EMPTABLE

END

CREATE PROCEDURE FETCHEMPLOYEE(@ID INT)

AS

BEGIN

      SELECT * FROM EMPTABLE WHERE ID = @ID

END

Create a Empty MVC project, select Razor as Engine. Right click the Models and add new item.Select Data in the left pane and click ADO.NET entity framework model.Give a name as Employee.edmx and press ok.

Choose the model content as Generate from the database,and click next.

 Now select the New Connection and give the Entities name, then click next. 

Then select the all stored procedure which are need for the project. Give the model name as Employee Model and click Finish.

Right click the Employee.edmx and click the model browser to see the following items.

Expand the EmployeeModel.Store and select the stored procedure and the Tables need to be in project. that need to be added in entities.and click add Function Import to import the stored procedure as Function to the code.

In the Add Function Import screen please give the function name as you need and select the return type of that stored procedure based on your requirement. In this project return type is a emptable value , if your are returning a complex type by combining a multiple tables. The entities that are seen in the collection is listed by selected what are selected in the tables in the EmployeeModel.edmx.

Now add a cs file as Empmodel.cs in the Models folder to iterate values from db through stored procedure.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.Objects;

namespace EFSample.Models

{

    public class EmpModel

    {

        EmployeeEntities empdb = new EmployeeEntities();

       

        public List<EMPTABLE> GetEmployees()

        {

           return empdb.FETCHEMPLOYEES().ToList();  

        }

        public EMPTABLE GetEmployee(int? id)

        {

            return empdb.FETCHEMPLOYEE(id).ToList().Single();

        }

    }

}

  

Add the EmployeeController 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using EFSample.Models;

namespace EFSample.Controllers

{

    public class EmployeeController : Controller

    {

        Models.EmpModel mod = new Models.EmpModel();

        public ActionResult Index()

        {

            List<EMPTABLE> result = mod.GetEmployees();

            return View(result);

        }

        public ActionResult Details(int id)

        {

            EMPTABLE result = mod.GetEmployee(id);

            return View(result);

        }

    }

}

Add the Index View and Details View.

Index.cshtml

@model IEnumerable<EFSample.Models.EMPTABLE>

@{

    ViewBag.Title = "Employee Information";

}

<h2>Employees</h2>

<p>

    @Html.ActionLink("Create New", "Create")

</p>

<table style="border:2px solid Pink">

    <tr>

        <th style="color:Blue">

            @Html.DisplayNameFor(model => model.NAME)

        </th>

        <th style="color:Blue">

            @Html.DisplayNameFor(model => model.DEPTID)

        </th>

        <th style="color:Blue">

            @Html.DisplayNameFor(model => model.COUNTRY)

        </th>

        <th style="color:Blue">

            @Html.DisplayNameFor(model => model.MARRIED)

        </th>

        <th></th>

    </tr>

@foreach (var item in Model) {

    <tr>

        <td>

            @Html.DisplayFor(modelItem => item.NAME)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.DEPTID)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.COUNTRY)

        </td>

       

        <td>           

            @Html.ActionLink("Details", "Details", new { id=item.ID })        

        </td>

    </tr>

}

</table>

Details.cshtml

@model EFSample.Models.EMPTABLE

@{

    ViewBag.Title = "Details";

}

<h2>Details</h2>

<link href="../../Site.css" rel="stylesheet" type="text/css" />

<fieldset>

    <legend>EMPTABLE</legend>

   

    <table>

   

    <tr>

    <td>  @Html.DisplayNameFor(model => model.NAME) :</td><td style="font-weight:bold">@Html.DisplayFor(model => model.NAME)</td>   

    </tr>

   

    <tr>

    <td> @Html.DisplayNameFor(model => model.DEPTID) :</td><td style="font-weight:bold"> @Html.DisplayFor(model => model.DEPTID)</td>

    </tr>

    <tr>

    <td>  @Html.DisplayNameFor(model => model.COUNTRY) :</td><td style="font-weight:bold">@Html.DisplayFor(model => model.COUNTRY)</td>

    </tr>

   

    </table>                 

</fieldset>

<p>

    @Html.ActionLink("Edit", "Edit", new { id=Model.ID }) |

    @Html.ActionLink("Back to List", "Index")

</p>

Output:

Index.View

 Details.View

From this article you can learn how to add the stored procedure as function in the Model using Entity Framework in ASP.NET MVC.