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
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
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.
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?
Id
and dir
is 0 (ascending) use class fa-sort-down
Id
and dir
is 1 (descending) use class fa-sort-up
fa-sort
class to show the up and down both arrow to indicate sortable.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
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:
MvcHtmlString
this HtmlHelper helper
for any customer html helperMvcHtmlString.Create
methodHow 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.
![]() |
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|
By Ali Adravi On 07 May, 18 Viewed: 6,392 |
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
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
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 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