maqskywalker
asked on
calling a Stored Procedure instead of a table in MVC Entity Framework
I'm using Visual Studio 2013.
I have a asp.net mvc application and using sql server 2008 R2.
I'm using the Employees table of the Northwind database for my example.
In my example application I bind the Employee table to a FlexiGrid in my View.
So right now my application project directory looks like this:
Model
I created a model using the entity model. My model is called northwindEF.edmx
My model diagram looks like this. Although it shows 4 tables I'm only really using the Employee table.
In my Model Browser this is what it looks like:
Right now in the Models folder of my application I have a file called All.cs which looks like this:
Controller
So right now in my Controllers folder I have a controller called HomeController.cs
This is my code:
If you noticed the code for my controller, I have a JSON function which calls the EntContext from my model.
View
So right now my view Index.csthml code looks like this:
When I run my application it works fine and I get this:
So right now in my Model I'm just calling the Employee table from my Entity Model.
Now I'm trying to do the same example but just trying to learn the syntax to call a Stored Procedure in my model instead of just calling the table like in my example above.
So I created a simple Stored Procedure called FETCHEMPLOYEES that just returns the Employees table.
This is my code for my stored procedure:
So in my entity model I showed above when I created that model, I also added this stored procedure to it. If you look at the image of my model browser above, if you look at the Function Imports folder, it's the one called FETCHEMPLOYEES.
This is what FETCHEMPLOYEES looks like:
Essentially I just need to revise my Model and Controller to call my stored procedure instead of calling the employees table.
So then in my Models folder I created EmpModel.cs this is my code
So this is my question:
How would I revise my model and controller to call my Stored Procedure that returns the Employees table instead of calling the Employees table which it is currently doing?
I have a asp.net mvc application and using sql server 2008 R2.
I'm using the Employees table of the Northwind database for my example.
In my example application I bind the Employee table to a FlexiGrid in my View.
So right now my application project directory looks like this:
Model
I created a model using the entity model. My model is called northwindEF.edmx
My model diagram looks like this. Although it shows 4 tables I'm only really using the Employee table.
In my Model Browser this is what it looks like:
Right now in the Models folder of my application I have a file called All.cs which looks like this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using FlexigridUsingMvc2.Models;
namespace FlexigridUsingMvc2.Models
{
public class EntContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
}
}Controller
So right now in my Controllers folder I have a controller called HomeController.cs
This is my code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using FlexigridUsingMvc2.Models;
using FlexigridUsingMvc2.Extensions;
namespace FlexigridUsingMvc2.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/
[HttpPost]
public JsonResult List(int page, int rp, string qtype, string letter_pressed, string query)
{
using (var db = new EntContext())
{
if (letter_pressed == "ALL") letter_pressed = "";
Func<IQueryable<Employee>> EmployeeFiltered = () =>
db.Employees
.Where(x =>
(
(
!string.IsNullOrEmpty(letter_pressed)
&& x.LastName.StartsWith(letter_pressed)
)
||
string.IsNullOrEmpty(letter_pressed)
)
&&
(
(
!string.IsNullOrEmpty(query)
&&
(
(qtype == "LastName" && x.LastName.Contains(query))
||
(qtype == "FirstName" && x.FirstName.Contains(query))
||
(qtype == "Title" && x.Title.Contains(query))
)
)
||
string.IsNullOrEmpty(query)
)
);
return Json(
new
{
page = page,
total = EmployeeFiltered().Count(),
rows =
EmployeeFiltered()
.OrderBy(x => x.LastName)
.LimitAndOffset(pageSize: rp, pageOffset: page)
.ToList()
.Select(x =>
new
{
id = x.LastName,
cell = new string[] { x.LastName, x.FirstName, x.Title }
})
});
}//using
}//List
public ActionResult Index()
{
return View();
}
}
}If you noticed the code for my controller, I have a JSON function which calls the EntContext from my model.
View
So right now my view Index.csthml code looks like this:
@{
ViewBag.Title = "Index";
}
<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/Scripts/jquery-1.8.2.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/flexigrid/flexigrid.pack.js")" type="text/javascript"></script>
<link href="@Url.Content("~/Scripts/flexigrid/css/flexigrid.css")" rel="stylesheet" type="text/css" />
<script type="text/javascript">
$(document).ready(function () {
$("#fff").flexigrid
(
{
url: '/Home/List/',
dataType: 'json',
colModel: [
{ display: 'LastName', name: 'LastName', width: 200, sortable: false, align: 'left' },
{ display: 'FirstName', name: 'FirstName', width: 280, sortable: false, align: 'left' },
{ display: 'Title', name: 'Title', width: 220, sortable: false, align: 'left' }
],
buttons: [
{ name: 'Add', bclass: 'add', onpress: add },
{ name: 'Edit', bclass: 'edit', onpress: edit },
{ name: 'Delete', bclass: 'delete', onpress: del },
{ separator: true },
{ name: 'A', onpress: sortAlpha },
{ name: 'B', onpress: sortAlpha },
{ name: 'C', onpress: sortAlpha },
{ name: 'D', onpress: sortAlpha },
{ name: 'E', onpress: sortAlpha },
{ name: 'F', onpress: sortAlpha },
{ name: 'G', onpress: sortAlpha },
{ name: 'H', onpress: sortAlpha },
{ name: 'I', onpress: sortAlpha },
{ name: 'J', onpress: sortAlpha },
{ name: 'K', onpress: sortAlpha },
{ name: 'L', onpress: sortAlpha },
{ name: 'M', onpress: sortAlpha },
{ name: 'N', onpress: sortAlpha },
{ name: 'O', onpress: sortAlpha },
{ name: 'P', onpress: sortAlpha },
{ name: 'Q', onpress: sortAlpha },
{ name: 'R', onpress: sortAlpha },
{ name: 'S', onpress: sortAlpha },
{ name: 'T', onpress: sortAlpha },
{ name: 'U', onpress: sortAlpha },
{ name: 'V', onpress: sortAlpha },
{ name: 'W', onpress: sortAlpha },
{ name: 'X', onpress: sortAlpha },
{ name: 'Y', onpress: sortAlpha },
{ name: 'Z', onpress: sortAlpha },
{ name: 'ALL', onpress: sortAlpha }
],
searchitems: [
{ display: 'LastName', name: 'LastName', isdefault: true },
{ display: 'FirstName', name: 'FirstName' },
{ display: 'Title', name: 'Title' }
],
singleSelect: true,
sortname: "LastName",
sortorder: "asc",
usepager: true,
title: 'Employees',
useRp: true,
rp: 10,
showTableToggleBtn: false,
width: 800,
height: 255
}
);
});
function sortAlpha(com) {
jQuery('#fff').flexOptions({ newp: 1, params: [{ name: 'letter_pressed', value: com }, { name: 'qtype', value: $('select[name=qtype]').val()}] });
jQuery("#fff").flexReload();
}
function edit(com, grid) {
// alert(com); // Edit
if ( $('.trSelected',grid).length == 1 )
{
var items = $('.trSelected',grid);
alert(com + ' ' + items[0].id.substr(3));
}
}
function add(com, grid) {
alert(com);
/*if ($('.trSelected', grid).length == 1) {
var items = $('.trSelected', grid);
alert(items[0].id.substr(3));
}*/
}
function del(com, grid) {
// alert(com); // Delete
if ($('.trSelected', grid).length == 1) {
var items = $('.trSelected', grid);
alert(com + ' ' + items[0].id.substr(3));
}
}
</script>
<title>Index</title>
</head>
<body>
<div>
<table id="fff" style="display:none"></table>
</div>
</body>
</html>When I run my application it works fine and I get this:
So right now in my Model I'm just calling the Employee table from my Entity Model.
Now I'm trying to do the same example but just trying to learn the syntax to call a Stored Procedure in my model instead of just calling the table like in my example above.
So I created a simple Stored Procedure called FETCHEMPLOYEES that just returns the Employees table.
This is my code for my stored procedure:
CREATE PROCEDURE [dbo].[FETCHEMPLOYEES]
AS
BEGIN
SELECT * FROM [Northwind].[dbo].[Employees]
ENDSo in my entity model I showed above when I created that model, I also added this stored procedure to it. If you look at the image of my model browser above, if you look at the Function Imports folder, it's the one called FETCHEMPLOYEES.
This is what FETCHEMPLOYEES looks like:
Essentially I just need to revise my Model and Controller to call my stored procedure instead of calling the employees table.
So then in my Models folder I created EmpModel.cs this is my code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
// mq
using System.Data.Objects;
namespace FlexigridUsingMvc2.Models
{
public class EmpModel
{
NorthwindEntities empdb = new NorthwindEntities();
public List<Employee> GetEmployees()
{
return empdb.FETCHEMPLOYEES().ToList();
}
}
}So this is my question:
How would I revise my model and controller to call my Stored Procedure that returns the Employees table instead of calling the Employees table which it is currently doing?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.