Server Side Pagination in ASP.NET MVC ~ IT Tutorials with Example

In this Article, we will learn How to implement server-side paging in ASP.NET MVC. Pagination is used to divide the data or content into multiple pages which makes a page to load faster and the user can read the data without scrolling.

In order to demonstrate, I am using a sample table, i.e. Person. Person of AdventureWorks database which has more than 15K Records.

Let’s Begin:
Create a new Empty ASP.NET MVC Project and add the below Models in the project.

public class Person

{

public int TotalRecords { get; set; }

public string Title { get; set; }

public string FirstName { get; set; }

public string MiddleName { get; set; }

public string LastName { get; set; }

}

public class PersonViewModel

{

public List<Person> ListPerson { get; set; }

public Pager pager { get; set; }

}

public class Pager

{

public Pager(int totalItems, int? page, int pageSize = 10)

{

// Total Paging need to show

int _totalPages = (int)Math.Ceiling((decimal)totalItems / (decimal)pageSize);

//Current Page

int _currentPage = page != null ? (int)page : 1;

//Paging to be starts with

int _startPage = _currentPage - 5;

//Paging to be end with

int _endPage = _currentPage + 4;

if (_startPage <= 0)

{

_endPage -= (_startPage - 1);

_startPage = 1;

}

if (_endPage > _totalPages)

{

_endPage = _totalPages;

if (_endPage > 10)

{

_startPage = _endPage - 9;

}

//Setting up the properties

TotalItems = totalItems;

CurrentPage = _currentPage;

PageSize = pageSize;

TotalPages = _totalPages;

StartPage = _startPage;

EndPage = _endPage;

}

public int TotalItems { get; set; }

public int CurrentPage { get; set; }

public int PageSize { get; set; }

public int TotalPages { get; set; }

public int StartPage { get; set; }

public int EndPage { get; set; }

}

In above code, We have Person Class which is used for the binding the Person Table Data. Pager class for the pagination which have parameterized constructor and accepts totalitems i.e. Total Records, page for CurrentPage and pagesize as parameters. We have created PersonViewModel as we need multiple models in the view.

Add a controller in your project and add the below code in the controller. I am connecting to the database using ADO.NET directly in the controller just for the demonstration purpose (not recommends in the real-world project). You can make changes as per your need or project you are working with. In the below code, we have set the PageSize to 10 which stands for the number of rows or records to be shown on per page, then we connected to the database and call the procedure by passing two parameters (OffsetValue and PagingSize) required by the procedure.

public ActionResult Index(int page = 1)

{

//Defining the PageSize

int PageSize = 10;

//Creating the ViewModel's Object

PersonViewModel obj = new PersonViewModel();

DataSet ds = new DataSet();

//List of the Person

List<Person> lstPerson = new List<Person>();

//Connecting to the Database (Here, I am using ADO.Net in order to interact with the database)

//You can use any ORM as per your need or requirement

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString)) {

con.Open();

SqlCommand com = new SqlCommand("getPerson",con);

com.CommandType = CommandType.StoredProcedure;

//Passing the Offset value in the procedure

com.Parameters.AddWithValue("@OffsetValue", (page-1) * PageSize);

com.Parameters.AddWithValue("@PagingSize", PageSize);

SqlDataAdapter adapt = new SqlDataAdapter(com);

//Fill the Dataset and Close the connection

adapt.Fill(ds);

con.Close();

//Bind the data in List of type Person

//We are returning Dataset with two Datatable, one contains the Person Data and Other contains the total records count

if (ds!=null && ds.Tables.Count==2) {

for (int i=0;i<ds.Tables[0].Rows.Count;i++) {

Person objPerson = new Person();

objPerson.Title = Convert.IsDBNull(ds.Tables[0].Rows[i]["Title"]) ? "" : Convert.ToString(ds.Tables[0].Rows[i]["Title"]);

objPerson.FirstName = Convert.IsDBNull(ds.Tables[0].Rows[i]["FirstName"]) ? "" : Convert.ToString(ds.Tables[0].Rows[i]["FirstName"]);

objPerson.MiddleName = Convert.IsDBNull(ds.Tables[0].Rows[i]["MiddleName"]) ? "" : Convert.ToString(ds.Tables[0].Rows[i]["MiddleName"]);

objPerson.LastName = Convert.IsDBNull(ds.Tables[0].Rows[i]["LastName"]) ? "" : Convert.ToString(ds.Tables[0].Rows[i]["LastName"]);

lstPerson.Add(objPerson);

}

//Passing the TotalRecordsCount, Current Page and Page Size in the constructore of the Pager Class

var pager = new Pager((ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0) ? Convert.ToInt32(ds.Tables[1].Rows[0]["TotalRecords"]): 0, page, PageSize);

obj.ListPerson = lstPerson;

obj.pager = pager;

}

return View(obj);

}

Procedure used in the above example:

Create procedure [dbo].[getPerson]

(

@OffsetValue int,

@PagingSize int

)

Begin

Select Title,FirstName,MiddleName,LastName from Person.Person order by BusinessEntityID

OFFSET @OffsetValue ROWS FETCH NEXT @PagingSize ROWS ONLY

Select count(BusinessEntityID) as TotalRecords from Person.Person

End

In the above procedure, we are using OFFSET and FETCH NEXT clause which is used in conjunction with SELECT and ORDER BY clause. Value of Offset must be equal and greater than Zero. Offset stands for the number of rows to be skipped and fetch will pick the number of rows to be picked after offsetting the number of rows.

Run the below query on the AdventureWorks database.

Select BusinessEntityID,Title,FirstName,MiddleName,LastName

from Person.Person order by BusinessEntityID

OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY

After running the above query, you will get the below output.

Now let’s add the below code on the view. We are using bootstrap for the UI Designing purpose. You can add the bootstrap in your project from the NuGet package manager.

@model PagingSampleMVC.Models.PersonViewModel

Layout = null;

}

<!DOCTYPE html>

<html>

<head>

<title>Index</title>

<style>

.pagination > li > a, .pagination > li > span {

position: relative;

float: left;

padding: 6px 12px;

margin-left: -1px;

line-height: 1.42857143;

color: #337ab7;

text-decoration: none;

background-color: #fff;

border: 1px solid #ddd;

}

.pagination > .active > a, .pagination > .active > a:focus, .pagination > .active > a:hover, .pagination > .active > span, .pagination > .active > span:focus, .pagination > .active > span:hover {

z-index: 2;

color: #fff;

cursor: default;

background-color: #337ab7;

border-color: #337ab7;

}

</style>

</head>

<body>

<thead>

<tr>

<th>Title</th>

<th>First Name</th>

<th>Middle Name</th>

</tr>

</thead>

<tbody>

@if (Model.ListPerson != null)

{

for (int i = 0; i < Model.ListPerson.Count; i++)

{

<tr>

<td>@Model.ListPerson[i].Title</td>

<td>@Model.ListPerson[i].FirstName</td>

<td>@Model.ListPerson[i].MiddleName</td>

<td>@Model.ListPerson[i].LastName</td>

</tr>

}

</tbody>

</table>

@if (Model.pager.EndPage > 1)

{

@if (Model.pager.CurrentPage > 1)

{

<li>

<a href="~/Home/Index">First</a>

</li>

<li>

<a href="~/Home/Index?page=@(Model.pager.CurrentPage - 1)">Previous</a>

</li>

}

@for (var page = Model.pager.StartPage; page <= Model.pager.EndPage; page++)

{

</li>

}

@if (Model.pager.CurrentPage < Model.pager.TotalPages)

{

<li>

</li>

<li>

</li>

}

</ul>

}

</div>

</body>

</html>

When a user clicks on the paging, it will call the get request to the Index Action method and picks the next records to be shown accordingly. Now, Build and Run the application.
Preview: