<%@ 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);

            }

        }