In this article I am going to show how we can use
stored procedure in Entity Framework with MVC Application.
Open Visual Studio -> Add -> New Project
Image 1.
Image 2.
Below is my Data Table from which I will show data
using Stored Procedure.
Image 3.
Data in my Table.
Image 4.
Below is my Stored Procedure:
Image 5.
USE [CompanyDB]
GO
/****** Object:
StoredProcedure
[dbo].[SearchEmployee]
Script Date: 03/25/2016 18:48:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchEmployee]
AS
SELECT * FROM
Emp_Information
Now
Time to Add ADO.NET Entity Data Model. So Right click on Model Folder-> Add
New Item.
Image 6.
Image 7.
Image 8.
Image 9.
Image 10.
Select your table and Stored Procedure here.
Image 11.
Image 12.
Right Click -> And Click on Model Browser:
Image 13.
Right Click on your Stored Procedure -> Click on Add
Function Import
Image 14.
Here give name to your function and select your
entities.
Image 15.
Now Add a Class.
Image 16.
Image 17.
And write below code here in this class:
Image 18.
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
namespace
SP_EntityFramework_MVC.Models
{
public class Emp_Model
{
CompanyDBEntities empdb = new CompanyDBEntities();
public List<Emp_Information> GetEmployees()
{
return empdb.FN_SearchEmployee().ToList();
}
}
}
Now time to
add a controller:
Image 19.
Image 20.
Image 21.
And do below code in your controller:
Image 22.
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 = new Emp_Model();
public ActionResult Index()
{
List<Emp_Information> result =
mod.GetEmployees();
return View(result);
}
}
}
Now Add View
to your controller Index Action Method:
Image 23.
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:solid 4px red; padding:20px; color:white; width:100%;">
<tr><td align="center" colspan="5" style="border:solid 4px red; 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:
Image 24.