ASP.NET

Using Stored Procedure In Entity Framework MVC

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
  1. USE [CompanyDB]  
  2. GO  
  3. /****** Object: StoredProcedure  
  4. [dbo].[SearchEmployee]   
  5. Script Date: 03/25/2016 18:48:48 ******/  
  6. SETANSI_NULLSON  
  7. GO  
  8. SETQUOTED_IDENTIFIERON  
  9. GO  
  10.   
  11. ALTERPROCEDURE [dbo].[SearchEmployee]  
  12. AS  
  13.   
  14. 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
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. namespace SP_EntityFramework_MVC.Models  
  6. {  
  7.     public class Emp_Model  
  8.     {  
  9.         CompanyDBEntitiesempdb = newCompanyDBEntities();  
  10.         public List < Emp_Information > GetEmployees()  
  11.         {  
  12.             return empdb.FN_SearchEmployee().ToList();  
  13.         }  
  14.     }  
  15. }  
Now time to add a controller:

add

controller

controller

And write the following code in your controller:

code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using SP_EntityFramework_MVC.Models;  
  7. namespace SP_EntityFramework_MVC.Controllers  
  8. {  
  9.     public class EmployeeController: Controller  
  10.     {  
  11.         Emp_Model mod = newEmp_Model();  
  12.         public ActionResult Index()  
  13.         {  
  14.             List < Emp_Information > result = mod.GetEmployees();  
  15.             return View(result);  
  16.         }  
  17.     }  
  18. }  
Now Add View to your controller Index Action Method:

Add View

View.cshtml:
  1. @model IEnumerable<SP_EntityFramework_MVC.Models.Emp_Information>  
  2. @{  
  3. Layout = null;  
  4. }  
  5. <!DOCTYPE html>  
  6. <html>  
  7. <head>  
  8. <meta name="viewport"content="width=device-width"/>  
  9. <title>Employee List</title>  
  10. </head>  
  11. <body>  
  12. <table class="table"style="border:solid4pxred; padding:20px; color:white; width:100%;">  
  13. <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>  
  14. <tr style="background-color:green;">  
  15. <th>  
  16. @Html.DisplayNameFor(model =>model.EMP_ID)  
  17. </th>  
  18. <th>  
  19. @Html.DisplayNameFor(model =>model.Name)  
  20. </th>  
  21. <th>  
  22. @Html.DisplayNameFor(model =>model.ProjectName)  
  23. </th>  
  24. <th>  
  25. @Html.DisplayNameFor(model =>model.ManagerName)  
  26. </th>  
  27. <th>  
  28. @Html.DisplayNameFor(model =>model.City)  
  29. </th>  
  30. </tr>  
  31. @foreach (var item in Model)  
  32. {  
  33. <tr style="background-color:skyblue; color:red; padding:10px;">  
  34. <td>  
  35. @Html.DisplayFor(modelItem =>item.EMP_ID)  
  36. </td>  
  37. <td>  
  38. @Html.DisplayFor(modelItem =>item.Name)  
  39. </td>  
  40. <td>  
  41. @Html.DisplayFor(modelItem =>item.ProjectName)  
  42. </td>  
  43. <td>  
  44. @Html.DisplayFor(modelItem =>item.ManagerName)  
  45. </td>  
  46. <td>  
  47. @Html.DisplayFor(modelItem =>item.City)  
  48. </td>  
  49. </tr>  
  50. }  
  51. </table>  
  52. </body>  
  53. </html>  
Now run your application:

application

Read more articles on MVC:

Up Next
    Ebook Download
    View all
    Learn
    View all
    Membership not found