[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4803
  • Last Modified:

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:

my asp.net mvc directory
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.

entity model diagram
In my Model Browser this is what it looks like:my entity model's model browser

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; }
        
    }

}

Open in new window




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();
        }


    }
}

Open in new window



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>

Open in new window


When I run my application it works fine and I get this:

my working grid view

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]
END

Open in new window


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:

my FETCHMPLOYEES stored procedure function
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();

        }

    }
}

Open in new window


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?
1
maqskywalker
Asked:
maqskywalker
1 Solution
 
Fernando SotoCommented:
Hi maqskywalker;

Modify your DbContext to look like this. You will need the default constructor as it is below and the two using statements
using System.Data.Entity.Core.Objects;  
using System.Data.Entity.Infrastructure;

public class EntContext : DbContext
{
    public EntContext()
        : base("name=NorthwindEntities")
    {
    }

    public virtual DbSet<Employee> Employees { get; set; }

    public virtual ObjectResult<Employee> FETCHEMPLOYEES()
    {
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Employee>("FETCHEMPLOYEES");
    }
}

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now