In this article you will learn how to use Stored Procedure in Entity Framework MVC. Here are the steps:
Open Visual Studio, Add, then New Project,
![New Project]()
![mvc]()
Here is my Data Table from which I will show data using Stored Procedure.
![data]()
Data in my Table.
![Table]()
The following is my Stored Procedure:
![Stored Procedure]()
- USE [CompanyDB]
- GO
- /****** Object: StoredProcedure
- [dbo].[SearchEmployee]
- Script Date: 03/25/2016 18:48:48 ******/
- SETANSI_NULLSON
- GO
- SETQUOTED_IDENTIFIERON
- GO
-
- ALTERPROCEDURE [dbo].[SearchEmployee]
- AS
-
- SELECT*FROM Emp_Information
Now time to add ADO.NET Entity Data Model. So, right click on
Models folder, click Add, then
New Item.
![add]()
![add]()
![model]()
![server]()
![connection]()
Select your table and Stored Procedure here.
![table]()
![Stored Procedure]()
Right Click and then click on
Model Browser:
![Model Browser]()
Right Click on your Stored Procedure, then click on
Add Function Import,
![Function Import]()
Here give name to your function and select your entities.
![function]()
Now Add a Class.
![add]()
![class]()
And write below code here in this class:
![code]()
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- namespace SP_EntityFramework_MVC.Models
- {
- public class Emp_Model
- {
- CompanyDBEntitiesempdb = newCompanyDBEntities();
- public List < Emp_Information > GetEmployees()
- {
- return empdb.FN_SearchEmployee().ToList();
- }
- }
- }
Now time to add a controller:
![add]()
![controller]()
![controller]()
And write the following code in your controller:
![code]()
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Web.Mvc;
- using SP_EntityFramework_MVC.Models;
- namespace SP_EntityFramework_MVC.Controllers
- {
- public class EmployeeController: Controller
- {
- Emp_Model mod = newEmp_Model();
- public ActionResult Index()
- {
- List < Emp_Information > result = mod.GetEmployees();
- return View(result);
- }
- }
- }
Now Add View to your controller Index Action Method:
![Add View]()
View.cshtml:
- @model IEnumerable<SP_EntityFramework_MVC.Models.Emp_Information>
- @{
- Layout = null;
- }
- <!DOCTYPE html>
- <html>
- <head>
- <meta name="viewport"content="width=device-width"/>
- <title>Employee List</title>
- </head>
- <body>
- <table class="table"style="border:solid4pxred; padding:20px; color:white; width:100%;">
- <tr><td align="center"colspan="5"style="border:solid4pxred; background-color:orange; padding:10px; font-family:Verdana; font-size:20pt; color:white; width:100%;"> Using Stored Procedure In Entity Framework MVC</td></tr>
- <tr style="background-color:green;">
- <th>
- @Html.DisplayNameFor(model =>model.EMP_ID)
- </th>
- <th>
- @Html.DisplayNameFor(model =>model.Name)
- </th>
- <th>
- @Html.DisplayNameFor(model =>model.ProjectName)
- </th>
- <th>
- @Html.DisplayNameFor(model =>model.ManagerName)
- </th>
- <th>
- @Html.DisplayNameFor(model =>model.City)
- </th>
- </tr>
- @foreach (var item in Model)
- {
- <tr style="background-color:skyblue; color:red; padding:10px;">
- <td>
- @Html.DisplayFor(modelItem =>item.EMP_ID)
- </td>
- <td>
- @Html.DisplayFor(modelItem =>item.Name)
- </td>
- <td>
- @Html.DisplayFor(modelItem =>item.ProjectName)
- </td>
- <td>
- @Html.DisplayFor(modelItem =>item.ManagerName)
- </td>
- <td>
- @Html.DisplayFor(modelItem =>item.City)
- </td>
- </tr>
- }
- </table>
- </body>
- </html>
Now run your application:
![application]()
Read more articles on MVC: