MVC HTML Paging Helper with search and sort

MVC Search Sort and Paging is common feature we need to use, so let's write the HTML Paging Helper for our project, which will work faster than anyother build-in costum grid system because they contain many other features we don't need for our application and don't support many features we want. In this article we will try to create custom HTML paging helper which will work with paging, sorting and searching by using all the server side coding only.

Customer table which we will use for our example, here is the table structure:

CREATE TABLE [Customer] (
    [CustomerId]   INT           IDENTITY (1000, 1) NOT NULL,
    [CustomerName] VARCHAR (30)  NOT NULL,
    [Address]      VARCHAR (200) NOT NULL,
    [City]         VARCHAR (50) NOT NULL,
    [StateId]      INT           NOT NULL,
    [CountryId]    INT           NOT NULL,
    [ZipCode]      VARCHAR (10)  NULL,
    PRIMARY KEY CLUSTERED ([CustomerId] ASC)
);

We have two other look-up tables to keep the country and state records, they are very simple, let me add it otherwise it might be confusing

CREATE TABLE [Country] (
    [CountryId]   INT          IDENTITY (1, 1) NOT NULL,
    [CountryName] VARCHAR (30) NOT NULL,
    PRIMARY KEY CLUSTERED ([CountryId] ASC)
);

CREATE TABLE [State] (
    [StateId]   INT          IDENTITY (1, 1) NOT NULL,
    [StateName] VARCHAR (30) NOT NULL,
    [CountryId] INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([StateId] ASC)
);

We need these two tables because we want to search customers by name, country and state, where country and state will be dropdown, see the image.

Procedure to get page records only with search and sort

We need a procedure to search the customers by Customer name, country Id and State Id, it can be able to return the sorted records by provided sort criteria, and should be able to return only paged data, means, if page size is 10 then only 10 records should be return from this procedure so accepting the page number and page size. Create a procedure in SQL Server

CREATE PROCEDURE [SearchCustomer]
    @search varchar(50),
    @countryId int,
    @stateId int,
    @orderBy varchar(50) ='NAME ASC',   
    @page int, 
    @pageSize int
AS

WITH Temp As(
    SELECT 
     CASE
       WHEN @OrderBy = 'ID ASC' THEN ROW_NUMBER() OVER (ORDER BY C.[CustomerId])
       WHEN @OrderBy = 'ID DESC' THEN ROW_NUMBER() OVER (ORDER BY C.[CustomerId] DESC)   

       WHEN @OrderBy = 'NAME ASC' THEN ROW_NUMBER() OVER (ORDER BY C.[CustomerName])
       WHEN @OrderBy = 'NAME DESC' THEN ROW_NUMBER() OVER (ORDER BY C.[CustomerName] DESC)

       WHEN @OrderBy = 'ADDRESS ASC' THEN ROW_NUMBER() OVER (ORDER BY C.[Address])
       WHEN @OrderBy = 'ADDRESS DESC' THEN ROW_NUMBER() OVER (ORDER BY C.[Address] DESC)

       WHEN @OrderBy = 'CITY ASC' THEN ROW_NUMBER() OVER (ORDER BY C.[City])
       WHEN @OrderBy = 'CITY DESC' THEN ROW_NUMBER() OVER (ORDER BY C.[City] DESC)

       WHEN @OrderBy = 'STATE ASC' THEN ROW_NUMBER() OVER (ORDER BY C.[StateName])
       WHEN @OrderBy = 'STATE DESC' THEN ROW_NUMBER() OVER (ORDER BY C.[StateName] DESC)

       WHEN @OrderBy = 'COUNTRY ASC' THEN ROW_NUMBER() OVER (ORDER BY C.[CountryName])
       WHEN @OrderBy = 'COUNTRY DESC' THEN ROW_NUMBER() OVER (ORDER BY C.[CountryName] DESC)

       WHEN @OrderBy = 'ZIP ASC' THEN ROW_NUMBER() OVER (ORDER BY C.[ZipCode])
       WHEN @OrderBy = 'ZIP DESC' THEN ROW_NUMBER() OVER (ORDER BY C.[ZipCode] DESC)
    END AS RowNum
    ,CustomerId 
    From CustomerDetail C
    WHERE CustomerName like '%' + @search + '%'
        ANd (CountryId  = @countryId OR @CountryId  = 0) 
        ANd (StateId  = @stateId OR @stateId  = 0)
)

SELECT CD.* 
FROM Temp T
    INNER JOIN CustomerDetail CD ON CD.CustomerId = T.CustomerId
WHERE T.RowNum BETWEEN (((@page - 1) * @pageSize) + 1) AND (@page * @pageSize)
ORDER BY RowNum

In this procedure most of the code have case when then in CTE (common table expression). We try to get all the customers Id in a temporary table in required sort order of records used by the user. Finally we join this temp table with original customer table and get only the page data.

Add this procedure to Entity Framework and create a service class, say, CustomerService.cs, and add a method to search customer by using our created procedure, so need to pass parameters name, countryId, stateId, orderBy, current page and page size, see this:

public class CustomerService
{
    public static List<SearchCustomer_Result> GetCustomers(string name, int countryId, int stateId, string orderBy, int page, int pageSize)
    {
        using (var db = new SSEntities())
        {
            var customers = db.SearchCustomer(name, countryId, stateId, orderBy, page, pageSize).ToList();
            return customers;
        }
    }
}

SearchCustomer_Result: is the object created by Entity Framework for our procedure SearchCustomer, SSEntities is the DbContext.

Now we need a method to get the count of record for the current search to show the number of pages in our paging, so create a new method GetCustomerCount which will accept all the search parameters only

public static int GetCustomersCount(int countryId, int stateId, string name)
{
    using (var db = new SSEntities())
    {
        var count = db.Customers.Count(x => (x.CustomerName.ToLower().Contains(name.ToLower()) || string.IsNullOrEmpty(name))
            && (x.CountryId == countryId || countryId == 0)
            && (x.StateId == stateId || stateId == 0));
        return count;
    }
}

Note: We use here Zero (0) for all records in case of country Id and state Id, same we use in our procedure.

To move the data to the view, we need a model to keep all the paging, searching and sorting information with records, so first we will create a model say PagingInfo

public class  PagingInfo
{
    public  string Name { get; set; }
    public  int? CountryId { get; set; }
    public  int? StateId { get; set; }
    public string OrderBy { get; set; }
    public int Direction { get; set; }

    public  int CurrentPage { get; set; }
    public  int PageSize { get; set; }
    public  int TotalRecords { get; set; }
    public  int TotalPages
    {
        get
        {
            return ((TotalRecords - 1) / PageSize) + 1;
        }
    }

    public  PagingInfo()
    {
        this.CurrentPage = 1;
        this.PageSize = 10;
    }
}

It looks big but nothing complicated, first three are search properties, next two for sorting and rest five are for paging detail, by using paging info class will be easy to pass the data to our HTML Paging Helper, which we will look latter in this article.

Customer Controller

Create a controller, say CustomerController, we will use it to create customer search page, since it is a listing page so we will use the index action method so default customer page will be the listing.

Add index action method to this controller, it will have many thinks which we [HttpGet] public async Task Index(string name = "", int countryid = 0, int stateid = 0, string orderBy = "id", int direction = 0 ,int page = 1) {
ViewBag.country = await Task.Run(()=> CustomerService.GetCountry()); ViewBag.states = await Task.Run(() => CustomerService.GetStates(countryid));

    var model = new CustomerSearchViewModel();
    model.paging = new PagingInfo();
    model.paging.CountryId = countryid;
    model.paging.StateId = stateid;
    model.paging.Name = name;

    model.paging.CurrentPage = page;
    model.paging.OrderBy = orderBy.ToLower();
    model.paging.Direction = direction;

    var orderDir = String.Format("{0} {1}", orderBy, direction == 1 ? "desc" : "asc");

    model.Records = await Task.Run(() => CustomerService.GetCustomers(countryid, stateid, name, orderDir, page, model.paging.PageSize));
    model.paging.TotalRecords = await Task.Run(()=> 
        CustomerService.GetCustomersCount(countryid, stateid, name));

    return View(model);
}

Let's see what we are doing here

• All the parameters have default values
• First two lines are used to get the countries and states for selected country
• model: create object of our view model
• model.paging: create object of PagingInfo model
• Then assign all the values passed by the user to model and paging
• model.records: get records for the page for provided criteria
• model.paging.TotalRecords: Get the count of records for the criteria
• Finally return the model to render the page

Now we can add the HTML for the page with search and sort (on clicking the header of table)

@model SearchSort.Models.CustomerSearchViewModel
@using SearchSort.Models
@{
    ViewBag.Title = "Customer List";
    Layout = "~/Views/Shared/_Layout.cshtml";
    var col = Model.paging.OrderBy;
    var dir = Model.paging.Direction;
} 

To make the name small copy the value or order by and direction into two variables col and dir.

Let's add the form tag by using the BeginForm:

@using (Html.BeginForm("Index", "Customer", FormMethod.Get, new { @id = "frmSearch" }))
{
    @Html.HiddenFor(m => m.paging.OrderBy, new { @Name = "OrderBy" })
    @Html.HiddenFor(m => m.paging.Direction, new { @Name = "Direction" })
    .......
}

Two hidden fields to keep the value of order by and sort direction, so when submit the page, these values should be passed to the method parameters. Note the Name attribute, starts with Caps. It must be the same as our method parameter.

Inside the above form add the following search fields:

// To search by name
@Html.TextBoxFor(m => m.paging.Name, new { @Name = "name" })

// Country Drop down
@Html.DropDownListFor(m => m.paging.CountryId,
    new SelectList(ViewBag.country, "CountryId", "CountryName"),
    "Select Country",
    new { @Name = "countryid",
          @onchange = "javascript:document.getElementById('paging_StateId').value = null;
          document.getElementById('frmSearch').submit()"
    })
// State Drop Down  
@Html.DropDownListFor(m => m.paging.StateId,
    new SelectList(ViewBag.states, "StateId", "StateName"),
    "Select Country",
    new { @Name = "stateid",
          @onchange = "javascript:document.getElementById('frmSearch').submit()"
    })  

Here I removed the class name and placeholder from controls for clarity.

• Name Textbox: enter the value and press enter, form will be submitted
• Country Drop Dwon: We fill it from ViewBag, and on change use JavaScript to clear the state dropdown and submit the page
• Similar to country dropdown state dropdown will only submit the page on change by using the JavaScript

Create grid by using HTML Table tag:

@if (Model != null && Model.Records.Count() > 0)
{
  <table class="table table-bordered table-hover">
     <thead>
        <tr>
            <th class="pointer" onclick="sort('id')">
                <i class="fa @(col == "id" && dir == 0 ? "fa-sort-down" : col == "id" && dir == 1 ? "fa-sort-up" : "fa-sort")"></i> ID
            </th>
            <th>Name</th>
            <th>Address</th>
            <th>City</th>
            <th>State</th>
            <th>Country</th>
            <th>Zip Code</th>
        </tr>
     </thead>
     <tbody>
        @foreach (var item in Model.Records)
        {
            <tr>
                <td>@item.CustomerId</td>
                <td>@item.CustomerName</td>
                <td>@item.Address</td>
                <td>@item.City</td>
                <td>@item.StateName</td>
                <td>@item.CountryName</td>
                <td>@item.ZipCode</td>
            </tr>
        }
     </tbody>
  </table>    
} 

What is this?

• First check if there is any record or not, you can add else part to show "No Record Found"
• Header part first column have extra things than other columns, from other columns I removed the sorting Icon code and click events for clarity, you need to add in your code to give the sorting feature on all those columns where ever you want.
• onclick: call a sort function with one parameter, name of the column to sort
• I put i tag to show the sorting direction and sortable columns indicator
• if sorted column is Id and dir is 0 (ascending) use class fa-sort-down
• if sorted column is Id and dir is 1 (descending) use class fa-sort-up
• otherwise use fa-sort class to show the up and down both arrow to indicate sortable.
• table body: simply a foreach loop to show all the records

Till this point search and sort is completed, now we have to write the HTML Paging Helper:

Before starting to write the paging helper, let's discuss what we are going to write

1. if less than or equal to 5 page show all pages for example 1 2 3 4 5
2. if more than 5 pages show first 5 then ... and last page e.g. 1 2 3 4 5 ... 20
3. if selected page is less than 5 then use the 2 condition e.g 1 2 3 4 5 ... 20
4. if selected page is 5 or greater then use this format e.g. 1... 3 4 5 6 7 ...20
5. if selected page is one of last three show the last 5 pages e.g. 1 ... 16 17 18 19 20

See the image for more understand what we are going to achieve for our custom paging, let create our paging hleper. Create a folder HtmlHelper and create a class file say 'AppHelper.cs' and add following code in it:

namespace SearchSort.HtmlHelpers
{
  public static class AppHelper
  {
    public static MvcHtmlString SearchPaging(this HtmlHelper helper, string url, PagingInfo page)
    {
        if (page.TotalPages <= 1) return null;

        // if more than 1 page
        var html = new StringBuilder();

        var search = url + "?page={0}";
        if(!String.IsNullOrEmpty(page.Name))
            search += "&name=" + page.Name.Trim();
        if(page.CountryId > 0)
            search +="&countryid=" + page.CountryId;
        if(page.StateId > 0)
            search +="&stateid=" + page.StateId;

        search += string.Format("&orderby={0}&direction={1}", page.OrderBy, page.Direction);
        search = search.ToLower();

        html.Append("<ul class='pagination pagination-sm mb-2 mt-2'>");
        #region[Show all the pages]
        if (page.TotalPages <= 5)
        {
            html.Append(String.Format("<li class='page-item " + (page.CurrentPage == 1 ? "disabled active" : "") + "'><a class='page-link' href='"
                + search + "'>1</a></li>", 1));
            html.Append(String.Format("<li class='page-item " + (page.CurrentPage == 2 ? "disabled active" : "") + "'><a class='page-link' href='"
                + search + "'>2</a></li>", 2));

            if (page.TotalPages >= 3)
                html.Append(String.Format("<li class='page-item " + (page.CurrentPage == 3 ? "disabled active" : "") + "'><a class='page-link' href='"
                    + search + "'>3</a></li>", 3));

            if (page.TotalPages >= 4)
                html.Append(String.Format("<li class='page-item " + (page.CurrentPage == 4 ? "disabled active" : "") + "'><a class='page-link' href='"
                    + search + "'>4</a></li>", 4));

            if (page.TotalPages >= 5)
                html.Append(String.Format("<li class='page-item " + (page.CurrentPage == 5 ? "disabled active" : "") + "'><a class='page-link' href='"
                    + search + "'>5</a></li>", 5));
        }
        #endregion

        else if (page.TotalPages > 5)
        {
            if (page.CurrentPage < 5)
            {
                for (var i = 1; i <= page.TotalPages; i++)
                {
                    html.Append(String.Format("<li class='page-item " + (page.CurrentPage == i ? "disabled active" : "")
                        + "'><a class='page-link' href='" + search + "'>{0}</a></li>", i));
                    if (i == 5) break;
                }

                // Last Page with  ..
                html.Append("<li class='page-item disabled'><a class='page-link' href='#'>...</a></li>");
                html.Append(String.Format("<li class='page-item'><a class='page-link' href='" + search + "'>{0}</a></li>", page.TotalPages));
            }

            else
            {
                html.Append(String.Format("<li class='page-item'><a class='page-link' href='" + search + "'>{0}</a></li>", 1));
                html.Append("<li class='page-item disabled'><a class='page-link' href='#'>...</a></li>");
                if (page.CurrentPage == page.TotalPages)
                {
                    html.Append(String.Format("<li class='page-item'><a class='page-link' href='" + search + "'>{0}</a></li>", page.CurrentPage - 4));
                    html.Append(String.Format("<li class='page-item'><a class='page-link' href='" + search + "'>{0}</a></li>", page.CurrentPage - 3));
                }
                else if (page.CurrentPage + 1 == page.TotalPages)
                {
                    html.Append(String.Format("<li class='page-item'><a class='page-link' href='" + search + "'>{0}</a></li>", page.CurrentPage - 3));
                }

                html.Append(String.Format("<li class='page-item'><a class='page-link' href='" + search + "'>{0}</a></li>", page.CurrentPage - 2));
                html.Append(String.Format("<li class='page-item'><a class='page-link' href='" + search + "'>{0}</a></li>", page.CurrentPage - 1));
                html.Append(String.Format("<li class='page-item disabled active'><a class='page-link' href='" + search + "'>{0}</a></li>", page.CurrentPage));

                if(page.CurrentPage + 1 <= page.TotalPages)
                    html.Append(String.Format("<li class='page-item'><a class='page-link' href='" + search + "'>{0}</a></li>", page.CurrentPage + 1));

                if (page.CurrentPage + 2 <= page.TotalPages)
                    html.Append(String.Format("<li class='page-item'><a class='page-link active' href='" + search + "'>{0}</a></li>", page.CurrentPage + 2));

                // still more pages
                if (page.CurrentPage + 2 < page.TotalPages)
                {
                    html.Append("<li class='page-item disabled'><a class='page-link' href='#'>...</a></li>");
                    html.Append(String.Format("<li class='page-item'><a class='page-link active' href='" + search + "'>{0}</a></li>", page.TotalPages));
                }
            }
        }

        html.Append("</ul>");
        return MvcHtmlString.Create(html.ToString());
    }
  }
}

Let's explore this huge lines of code:

• Note both the class and method are static.
• Return type is MvcHtmlString
• first parameter will always be this HtmlHelper helper for any customer html helper
• Then two other parameters, Url and our PagingInfo Model which contains all the search, sort and page info
• Tf total pages are one or 0, we don't need to show any paging so return null.
• Create a StringBuilder object to store the paging HTML string, say html
• Some check to see if any search value is there to add to the Url or not
• Them some conditions to check how many pages are there and create the html accordingly
• If there are only 5 or less pages then a different condition
• If more page then check the current page and create the paging html accordingly
• Finally return html by using MvcHtmlString.Create method

How to use this html helper in our page? Before doing so we need to configure it. Open the views folder there is a web.config file, it is different than the main config file on the root of application.

Open this config file in views folder and add the our created helper namespace it in

<system.web.webPages.razor>
  <host ... />
  <pages pageBaseType="System.Web.Mvc.WebViewPage">
    <namespaces>
      <add namespace="System.Web.Mvc" />
      <add namespace="System.Web.Mvc.Ajax" />
      <add namespace="System.Web.Mvc.Html" />
      <add namespace="System.Web.Optimization"/>
      <add namespace="System.Web.Routing" />
      <add namespace="SearchSort.HtmlHelpers" />
    </namespaces>
  </pages>
</system.web.webPages.razor>

See the last line where I added namesapce SearchSort.HtmlHelpers because my helper class exists in that namespace see the above code.

Now we are ready to use this help class, so open the index.cshtml page and at the bottom add

<div class="pull-right">
    @Html.SearchPaging(Request.Url.AbsolutePath, Model.paging)
</div>

Now run the application and try to search, sort and play with paging, every thing will work smooth.

If you have any other page with different search criteria then create a new PagingInfo model with those search criteria and copy the above helper method and just change the name and adjust the search parameters and done, no need to register your helper to config because we already registered the namesapce.

If forgot to give the sort JavaScript method:

@section scripts{    
  <script>
    var sort = function (col) {
        $('#paging_OrderBy').val(col);
        var dir = $('#paging_Direction').val();
        $('#paging_Direction').val(dir == '1' ? "0" : "1");
        $('#frmSearch').submit();
    }
  </script>
}

This function is accepting one parameter, column name to sort, and try to put the value in hidden field and check the current sort direction and reverse it and put that into the direction hidden field and then submit the form.

Other blogs you may like

MVC multiple field search with webgrid - Step 5

First of all we will try for multiple field search with paging and sorting for products by using WebGrid. We will create relationship between products and category table so we can show the category name in grid. To search we need a separate model for search having all the paging, sorting, total... By Ali Adravi   On 22 Mar 2015  Viewed: 26,640

Angularjs powerful paging on table and searching with delay and demo

Paging on table is the basic requirement and most of us look for some grid so it can provide the complete feature of paging and sorting but in Angularjs it is so easy that anyone can achieve it by writing some code. I checked and found most of the available code give the paging feature in for 1 to... By Ali Adravi   On 14 Aug 2015  Viewed: 20,946

Angularjs search and sort on table data

Sorting and searching on table data in angularjs is more than easy, just we need to do some html adjustment and everything works quickly. No need to write any separate procedure with these extra features, in this article we will see how we can achieve it without calling the data from database... By Ali Adravi   On 18 Jul 2015  Viewed: 4,791

mvc search page example with code

MVC Searh page with pagination: It’s very easy to create a search page in asp.net but when I try to create the same in MVC I faced many problems, how to create model, how to keep searched values in search controls, pagination, I found myself nowhere, so start searching for some good examples but... By Ali Adravi   On 25 Aug 2013  Viewed: 40,268