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
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs