How to do Paging in MVC
You can find lot of examples for paging in MVC with Entity Framework in internet. I am adding one more example. In this example you can see one partial page for paging control and ViewModel for called Pager. Here I am using AdventureWorks database Employee table to page Empoyees
Following is ViewModel class, data that you need to pass to the view. Class constructor have IQueryable
Datasource, It hold the data and return DataSource, TotalCount,PageSize currentpage and TotalPages.
public class Pager<T>:List<T>
{
public int TotalPages { get; set; }
public int CurrentPage { get; set; }
public int PageSize { get; set; }
public int TotalCount { get; set; }
public Pager(IQueryable<T> dataSource, int pageIndex, int pageSize, int totalCount)
{
TotalCount = totalCount;
CurrentPage = pageIndex;
PageSize = pageSize;
TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);
this.AddRange(dataSource);
}
}
Model and DataContext class
public partial class EmployeeContext : DbContext
{
public EmployeeContext()
: base("name=AdventureWorksConnectionString")
{
}
public virtual DbSet<DimEmployee> DimEmployees { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
}
}
In Model class I am not included all the columns, Just added only columns I need..
[Table("DimEmployee")]
public partial class DimEmployee
{
[Key]
public int EmployeeKey { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Title { get; set; }
public string EmailAddress { get; set; }
public string DepartmentName { get; set; }
}
EmployeeContext class return Data from database, I already Installed Entity Framework from Package Manager, But in this example I am calling one stored procedure to get the data using EF. Its for I am not taking the whole data from database.. Just taking what I want to display. Here we need to pass the limit of data we want.
USE [AdventureWorksDW2008R2]
GO
/****** Object: StoredProcedure [dbo].[getEmployeeByPageNumber] Script Date: 08/12/2014 14:24:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Raju Melveetilpurayil>
-- Create date: <Create Date,,>
-- Description: <Get rows by row between limit>
-- =============================================
ALTER PROCEDURE [dbo].[getEmployeeByPageNumber]
-- Add the parameters for the stored procedure here
@Start INT,
@End INT,
@TotalCount INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @TotalCount=COUNT(*) FROM dbo.DimEmployee
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[Title]
,[EmailAddress]
,[DepartmentName] FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY EmployeeKey) ROW_NUM, * FROM DimEmployee
) AS K
WHERE ROW_NUM >@Start AND ROW_NUM <=@End
END
Now we can see our MVC pages. Here is a partial page it only meant to display paging controls. The advantage of this control and model are generic, so we can pass any datasouce to do paging.
form the Index.cshtml we pass Model to the partial page. From the Model partial page read class properties TotalPage,CurrentPage..
and generate page numbers and next and previous buttons
Passing Model to partial Page
<tr>
<td colspan="5">
@Html.Partial("_PagingPartial",Model)
</td>
</tr>
Ok Now we can see the whole Index.cshtml
@model EFMVCSample.ViewModel.Pager<EFMVCSample.Models.DimEmployee>
@{
ViewBag.Title = "Employee";
}
<table class="table">
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>Title</th>
<th>EmailAddress</th>
<th>DepartmentName</th>
</tr>
@foreach (EFMVCSample.Models.DimEmployee employee in Model.AsQueryable().ToList())
{
<tr>
<td>@employee.FirstName</td>
<td>@employee.LastName</td>
<td>@employee.Title</td>
<td>@employee.EmailAddress</td>
<td>@employee.DepartmentName</td>
</tr>
}
<tr>
<td colspan="5">
@Html.Partial("_PagingPartial",Model)
</td>
</tr>
</table>
So we can see PagingController to see how controller works
public class PagingController : Controller
{
private List<DimEmployee> AllEmpoyees { get; set; }
//
// GET: /Paging/
public ActionResult Index(int? page)
{
int pageno = 0;
pageno = page == null ? 1 : int.Parse(page.ToString());
int pageSize = 15;
int totalCount = 0;
using (var db = new EmployeeContext())
{
int limitEnd = pageno * pageSize;
int limitStart = limitEnd - pageSize;
//
//calling stored procedure to get the total result count
var start = new SqlParameter("@Start", limitStart);
var end = new SqlParameter("@End", limitEnd);
//
// setting stored procedure OUTPUT value
// This return total number of rows, and avoid two database call for data and total number of rows
var spOutput = new SqlParameter
{
ParameterName = "@TotalCount",
SqlDbType = System.Data.SqlDbType.BigInt,
Direction = System.Data.ParameterDirection.Output
};
//
//calling stored procedure to get paged data.
AllEmpoyees = db.Database.SqlQuery<DimEmployee>(
"getEmployeeByPageNumber @Start,@End,@TotalCount out",
start, end, spOutput)
.ToList();
//
// setting total number of records
totalCount = int.Parse(spOutput.Value.ToString());
}
Pager<DimEmployee> pager = new Pager<DimEmployee>(AllEmpoyees.AsQueryable(), pageno, pageSize, totalCount);
return View(pager);
}
}
Depend on page number, Controller create pageno,pageSize and call stored procedure to get data between limit.
var spOutput = new SqlParameter
{
ParameterName = "@TotalCount",
SqlDbType = System.Data.SqlDbType.BigInt,
Direction = System.Data.ParameterDirection.Output
};
//
//calling stored procedure to get paged data.
AllEmpoyees = db.Database.SqlQuery<DimEmployee>("getEmployeeByPageNumber @Start,@End,@TotalCount out",start, end, spOutput).ToList();
