Fork me on GitHub

MvcPager demo - Using stored procedure

This sample demonstrates how to use SQL Server stored procedure to retrieve data for paging.

Order ID Customer ID Company Name Employee Name
10760 MAISD Maison Dewey Margaret Peacock
10761 RATTC Rattlesnake Canyon Grocery Steven Buchanan
10762 FOLKO Folk och fä HB Janet Leverling
10763 FOLIG Folies gourmandes Janet Leverling
10764 ERNSH Ernst Handel Michael Suyama
First Prev 1 2 3 4 5 6 7 8 9 10 ... Next Last 

View:

        
@model PagedList<Order>
@Html.Partial("_DataTable")
@Html.Pager(Model,new PagerOptions{PageIndexParameterName = "id",PagerItemTemplate = "{0}&nbsp;"})
@section Scripts{@{Html.RegisterMvcPagerScriptResource();}}

_DataTable.cshtml:

@model PagedList<order>
<table class="table table-striped table-bordered">
    <tr>
    <th class="nowrap">
            @Html.DisplayNameFor(model => model.OrderId)
        </th>
    <th class="nowrap">
            @Html.DisplayNameFor(model => model.CustomerId)
        </th>
    <th class="nowrap">
            @Html.DisplayNameFor(model => model.CompanyName)
        </th>
    <th class="nowrap">
            @Html.DisplayNameFor(model => model.EmployeeName)
        </th>
    </tr>
    @foreach (var item in Model)
    {
    <tr>
    <td>
                @Html.DisplayFor(modelItem => item.OrderId)
            </td>
    <td>
                @Html.DisplayFor(modelItem => item.CustomerId)
            </td>
    <td>
                @Html.DisplayFor(modelItem => item.CompanyName)
            </td>
    <td>
                @Html.DisplayFor(modelItem => item.EmployeeName)
            </td>
        </tr>
    }
</table>

Model:

    public class Order
    {
        [Display(Name = "Order ID")]
        public int OrderId { get; set; }
        [Display(Name = "Customer ID"),StringLength(20)]
        public string CustomerId { get; set; }
        [Display(Name = "Order Date")]
        public DateTime OrderDate { get; set; }
        [Display(Name = "Company Name"), StringLength(20)]
        public string CompanyName { get; set; }
        [Display(Name = "Employee Name"), StringLength(20)]
        public string EmployeeName { get; set; }
    }

Stored Procedure:

 CREATE PROCEDURE [dbo].[USP_GetPagedOrders]
	@startIndex int,
	@endIndex int,
	@totalItems int output
AS
WITH orderList AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY A.OrderDate DESC)AS Row, A.* from Orders A)
SELECT [OrderId],[OrderDate],[CustomerId],[CompanyName],[EmployeeName] FROM orderList WHERE Row between @startIndex and @endIndex
select @totalItems= count(OrderId) from Orders

Controller:

        
		public ActionResult StoredProcedure(int id = 1)
		{
			using (var db = new DataContext())
			{
				var pageSize = 5;
				var startIndex = (id - 1)*pageSize + 1;
				SqlParameter prmStart = new SqlParameter("@startIndex", startIndex);
				SqlParameter prmEnd = new SqlParameter("@endIndex", startIndex + pageSize);
				SqlParameter prmTotal = new SqlParameter("@totalItems", SqlDbType.Int);
				prmTotal.Direction = ParameterDirection.Output;
				var orders =db.Database.SqlQuery<Order>("exec [USP_GetPagedOrders] @startIndex,@endIndex,@totalItems out", prmStart, prmEnd,prmTotal).ToList();
				var totalItems = (int) prmTotal.Value;
				var model = new PagedList<Order>(orders, id, pageSize, totalItems);
				return View(model);
			}
		}