Using Stored Procedure In Entity Framework MVC

Rahul Kumar Saxena

10y151.5k32100Article

MVC Entity FrameworkEntity Framework MVCUsing Stored Procedure

32 Comments

Join the conversation! Your thoughts help the community grow.

Dave6y ago
What if you wish to output extra fields NOT in the table? Why does EF always need the stored procedure's output columns to match a table?
shanthan ruban7y ago
Thank you.. really useful
vaibhav wadekar8y ago
How to play songs within mvc
Md Raquib8y ago
This is not working code, There is error in this. db.FN_SearchEmployee().ToList(); code generating error.
Jhon Hernández8y ago
Ah... of course... I don?t have a Data base
Jhon Hernández8y ago
On public partial class CompanyDBEntities : DbContext
Jhon Hernández8y ago
Hi, I?m using VS 2013 and this error comes from your code: An exception of type 'System.Data.Entity.Core.EntityException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
Delpin Susai Raj10y ago
Good one
Rahul Kumar Saxena10y ago
Thanks Suraj Sahoo bro
Suraj Sahoo10y ago
Nice one Sir. :)
Rahul Kumar Saxena10y ago
Thanks
Rahul Kumar Saxena10y ago
Thanks Mohammed Ibrahim
Mohammed Ibrahim10y ago
nice
Rahul Kumar Saxena10y ago
Thanks Debasis Saha
Rahul Kumar Saxena10y ago
Thanks Vignesh Mani
Rahul Kumar Saxena10y ago
Thanks Shridhar Sharma
Debasis Saha10y ago
Nice One..
Vignesh Mani10y ago
Nice one
Shridhar Sharma10y ago
very well explained sir.
Rahul Kumar Saxena10y ago
Thanks 2 All
Rahul Kumar Saxena10y ago
Thanks Imtiyaz Ansari
Rahul Kumar Saxena10y ago
Thanks Karthikeyan K
Rahul Kumar Saxena10y ago
Thanks Mohammad Khalid
Rahul Kumar Saxena10y ago
Thanks Gowtham Rajamanickam
Rahul Kumar Saxena10y ago
Thanks Jaipal Reddy
Rahul Kumar Saxena10y ago
Thanks Debendra Dash
Imtiyaz Ansari10y ago
Nice...
Karthikeyan K10y ago
Good one sir..Thanks for sharing
Mohammad Khalid10y ago
Clear steps followed. Very nice
Gowtham Rajamanickam10y ago
good one..
Jaipal Reddy10y ago
Nice one sir. .
Debendra Dash10y ago
Nice one sir.....

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,

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:

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

Select your table and Stored Procedure here.

table

Right Click and then click on Model Browser:

Right Click on your Stored Procedure, then click on Add Function Import,

Here give name to your function and select your entities.

Now Add a Class.

add

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

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:

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: