Using stored procedure

This sample demonstrate how to use stored procedure to fetch only needed data to display for the current page

Order IDOrder DateCustomer IDShip Address
109203/3/1998 12:00:00 AMAROUTBrook Farm Stratford St. Mary
109213/3/1998 12:00:00 AMVAFFESmagsloget 45
109223/3/1998 12:00:00 AMHANARRua do Paço, 67
109233/3/1998 12:00:00 AMLAMAI1 rue Alsace-Lorraine
109243/4/1998 12:00:00 AMBERGSBerguvsvägen 8
109253/4/1998 12:00:00 AMHANARRua do Paço, 67
109263/4/1998 12:00:00 AMANATRAvda. de la Constitución 2222
109273/5/1998 12:00:00 AMLACOR67, avenue de l'Europe
109283/5/1998 12:00:00 AMGALEDRambla de Cataluña, 23
109293/5/1998 12:00:00 AMFRANKBerliner Platz 43
109303/6/1998 12:00:00 AMSUPRDBoulevard Tirou, 255
109313/6/1998 12:00:00 AMRICSUStarenweg 5
109323/6/1998 12:00:00 AMBONAP12, rue des Bouchers
109333/6/1998 12:00:00 AMISLATGarden House Crowther Way
109343/9/1998 12:00:00 AMLEHMSMagazinweg 7
109353/9/1998 12:00:00 AMWELLIRua do Mercado, 12
109363/9/1998 12:00:00 AMGREAL2732 Baker Blvd.
109373/10/1998 12:00:00 AMCACTUCerrito 333
109383/10/1998 12:00:00 AMQUICKTaucherstraße 10
109393/10/1998 12:00:00 AMMAGAAVia Ludovico il Moro 22
First  Prev  ...  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  Next  Last
<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" 
Inherits="System.Web.Mvc.ViewPage<PagedList<Order>>" %>
<%@ Import Namespace="Webdiyer.Mvc2Demo.Models" %>
<%@ Import Namespace="Webdiyer.WebControls.Mvc" %>
 
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Using stored procedure
</asp:Content>
 
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
 
    <h2>Using stored procedure</h2>
    <div>This sample demonstrate how to use stored procedure to fetch 
    only needed data to display for the current page</div><br />
 
<table width="98%">
<tr><th>Order ID</th><th>Order Date</th><th>Customer ID</th><th>Ship Address</th></tr>
<%foreach(Order od in Model)
 {
     %>
     <tr><td><%=od.OrderID %></td><td><%=od.OrderDate.ToString() %></td>
     <td><%=od.CustomerID %></td><td><%=od.ShipAddress %></td></tr>
     <%
 } %>
</table>
 <%=Html.Pager(Model, new PagerOptions { PageIndexParameterName = "id",
    NumericPagerItemCount=15})%>
</asp:Content>
CREATE PROCEDURE P_GetPagedOrders2005
(@startIndex INT, 
 @endindex INT
 )
AS
begin
WITH orderList AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY O.orderid DESC)AS Row, O.* 
from orders O)
 
SELECT *
FROM orderlist
WHERE Row between @startIndex and @endIndex
 
 
end
        public ActionResult StoredProcedure(int? id)
        {
            using (var db = new MvcPagerDemoDataContext())
            {
                //start record index
                int startIndex = id.HasValue && id.Value > 1 ? (id.Value - 1) * 20 + 1 : 0;
                //get data for display in current page using stored procedure
                List<Order> orders = db.GetPagedOrders(startIndex, startIndex + 20).ToList();
                //total record count to page through
                int totalOrders = db.Orders.Count();
                //Create PagedList<T> using constructor
                PagedList<Order> pagedOrders = new PagedList<Order>(orders, id ?? 1, 20, totalOrders);
                return View(pagedOrders);
            }
        }