Support us on YouTube by Subscribing our YouTube Channel. Click here to Subscribe our YouTube Channel

Sunday 24 March 2019

Server Side Pagination in ASP.NET MVC

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
)
as
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.
Offset and Fetch Next in SQL
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>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
    <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>
    <div class="container">
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Title</th>
                    <th>First Name</th>
                    <th>Middle Name</th>
                    <th>Last Name</th>
                </tr>
            </thead>
            <tbody>
                <!--Binding the data in the List-->
                @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>

        <!-- Paging -->
        @if (Model.pager.EndPage > 1)
        {
            <ul class="pagination">
                <!--If Current page is not the First Page, we will show Button/Link to go First or on Previous page-->
                @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>
                }
                <!--Paging from StartPage to the end Page-->
                @for (var page = Model.pager.StartPage; page <= Model.pager.EndPage; page++)
                {
                    <li class="@(page == Model.pager.CurrentPage ? "active" : "")">
                        <a href="~/Home/Index?page=@page">@page</a>
                    </li>
                }
                <!--If Current Page is not the Last Page then show Next and Last Button/link on Paging-->
                @if (Model.pager.CurrentPage < Model.pager.TotalPages)
                {
                    <li>
                        <a href="~/Home/Index?page=@(Model.pager.CurrentPage + 1)">Next</a>
                    </li>
                    <li>
                        <a href="~/Home/Index?page=@(Model.pager.TotalPages)">Last</a>
                    </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:
Server Side Pagination in ASP.NET MVC
Hope this will help you.
Thanks

Reference:
http://jasonwatmore.com/post/2015/10/30/aspnet-mvc-pagination-example-with-logic-like-google

Related Articles:


4 comments:

  1. Working fine and really saved my time,
    Thanks for sharing.

    ReplyDelete
  2. its throwing null reference exception! I have copied pasted the same code! any clue?

    ReplyDelete

Subscribe us on YouTube

Subscribe Now

Popular Posts

Contact us

Name

Email *

Message *

Like us on Facebook