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
110604/30/1998 12:00:00 AMFRANSVia Monte Bianco 34
110614/30/1998 12:00:00 AMGREAL2732 Baker Blvd.
110624/30/1998 12:00:00 AMREGGCStrada Provinciale 124
110634/30/1998 12:00:00 AMHUNGO8 Johnstown Road
110645/1/1998 12:00:00 AMSAVEA187 Suffolk Ln.
110655/1/1998 12:00:00 AMLILASCarrera 52 con Ave. Bolívar #65-98 Llano Largo
110665/1/1998 12:00:00 AMWHITC1029 - 12th Ave. S.
110675/4/1998 12:00:00 AMDRACDWalserweg 21
110685/4/1998 12:00:00 AMQUEENAlameda dos Canàrios, 891
110695/4/1998 12:00:00 AMTORTUAvda. Azteca 123
110705/5/1998 12:00:00 AMLEHMSMagazinweg 7
110715/5/1998 12:00:00 AMLILASCarrera 52 con Ave. Bolívar #65-98 Llano Largo
110725/5/1998 12:00:00 AMERNSHKirchgasse 6
110735/5/1998 12:00:00 AMPERICCalle Dr. Jorge Cash 321
110745/6/1998 12:00:00 AMSIMOBVinbæltet 34
110755/6/1998 12:00:00 AMRICSUStarenweg 5
110765/6/1998 12:00:00 AMBONAP12, rue des Bouchers
110775/6/1998 12:00:00 AMRATTC2817 Milton Dr.
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);
            }
        }