Paging In MVC 5 with Entity Framework
5.00/5 (7 votes)
One more example of how to do Paging in MVC
How to do Paging in MVC? You can find a lot of examples for paging in MVC with Entity Framework on the 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 Employees Following is ViewModel class, data that you need to pass to the view. Class constructor has IQueryable<t> Datasource. It holds the data and returns 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 have 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 returns 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. It's because 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 is only meant to display paging controls. The advantage of this control and model are generic, so we can pass any Datasource to do paging.
<ul class="pagination">
@{
double _pageCount = ((double)((Model.TotalCount-1) / Model.PageSize));
int pageCount = _pageCount - (Math.Round(_pageCount)) != 0 ?
(int)(_pageCount + 1) : (int)_pageCount;
string classNameForNext = Model.CurrentPage == _pageCount ? "disabled" : string.Empty;
string classNameForPrevious = Model.CurrentPage == 1 ? "disabled" : string.Empty;
}
<li class="@classNameForPrevious">
@if (classNameForPrevious == string.Empty)
{
@Html.ActionLink("« Prev", "Index", new { page = (Model.CurrentPage - 1) })
}
else
{
<a href="#">« Prev</a>
}
</li>
@for (int pages = 1; pages <= pageCount; pages++)
{
//
//adding active class to current page
string active = "";
if (pages == Model.CurrentPage)
{
active = "active";
}
<li class="@active">@Html.ActionLink(pages.ToString(), "Index", new { page = pages })</li>
}
<li class="@classNameForNext">
@if (classNameForNext == string.Empty)
{
@Html.ActionLink("Next »", "Index", new { page = (Model.CurrentPage + 1) })
}
else
{
<a href="#">Next »</a>
}
</li>
</ul>
So we can see PagingController to see how controller works:
public class PagingController : Controller
{
private List<DimEmployee> AllEmpoyees { get; set; }
//rp
// 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);
}
}
Depending on page number, Controller creates pageno, pageSize and calls 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();