Create a Grid View with paging and sorting feature using ASP.NET MVC Razor and LINQ
Recently, I came across a requirement to create a Grid View with paging and column sorting feature. Since, I was using ASP.NET MVC there was no server side control such as Repeater, GridView or DataList which are available in ASP.NET. I googled but didn’t come across a good solution except few third-party paid ASP.NET MVC controls. So finally I decided to code my own Grid View with support for paging and column sorting with help from information available on blogs and forums. Even though I am showing the code in context of an example its extensible enough to reuse it elsewhere.
Prerequisites
Visual Studio 2012
Entity Framework
LocalDb (to deploy the database or you can use SQL Server)
Key Features
1. Provide Previous/Next button at the bottom of the grid.
2. Provide drop down list to directly jump to any page.
3. Click on column name to sort data.
4. Show current page number and total pages.
In this example, I am using the list of countries to demonstrate paging and column sorting feature.
Listing 1: Screenshot of Grid View with support for paging and column sorting
Let’s start coding
Before looking at the actual implementation of paging and sorting in Grid View, let’s start by creating a ViewModel which allow us to customize the data to be viewed and control different aspects of our Grid View. I am using Entity Framework 5, code-first model.
Listing 2: CountryViewModel.cs
public class CountryTypeViewModel
{
public List<CountryViewModel> CountryList { get; set; }
public int CurrentPage;
public int pageSize;
public double TotalPages;
public int sortBy;
public bool isAsc;
public string Search;
public int isLastRecord;
public int Count;
}
public class CountryViewModel
{
public int CountryID { get; set; }
public string Name { get; set; }
public string Region { get; set; }
}
The class CountryViewModel is a ViewModel that shows only specific columns instead of displaying all the columns defined in the table. A list of CountryViewModel and other properties related to paging and column sorting used in Grid View is defined under CountryTypeViewModel class.
Let’s take a look at the action in Controller for Grid View.
Listing 3: GridViewController.cs
public CountryTypeViewModel GetCountry(int page = 1, int sortBy = 1, bool isAsc = true,bool isPaging=false)
{
CountryDBContext db = new CountryDBContext();
int pageSize = Convert.ToInt16(ConfigurationManager.AppSettings["PageSize"]);
IEnumerable countryList = null;
CountryTypeViewModel obj = new CountryTypeViewModel();
string sortColumn = string.Empty;
#region SortingColumn
switch (sortBy)
{
case 1:
if (isAsc)
sortColumn = "CountryID";
else
sortColumn = "CountryID Desc";
break;
case 2:
if (isAsc)
sortColumn = "Name";
else
sortColumn = "Name Desc";
break;
case 3:
if (isAsc)
sortColumn = "Region";
else
sortColumn = "Region Desc";
break;
}
#endregion
if (!isPaging)
{
countryList = (from p in db.CountryList
select new CountryViewModel
{
CountryID=p.CountryID,
Name = p.Name,
Region = p.Region
}).OrderBy(sortColumn).ToList();
obj.TotalPages = countryList.Count();
}
else if (isPaging)
{
countryList = (from p in db.CountryList
select new CountryViewModel
{
CountryID = p.CountryID,
Name = p.Name,
Region = p.Region
}).OrderBy(sortColumn).Skip((page - 1) * pageSize).Take(pageSize + 1).ToList();
}
obj.CurrentPage = page;
obj.pageSize = pageSize;
obj.sortBy = sortBy;
obj.isAsc = isAsc;
if (countryList.Count() <= pageSize)
obj.isLastRecord = 2;
if (obj.isLastRecord != 2)
{
if (countryList.Count() <= pageSize)
obj.isLastRecord = 1;
else
obj.isLastRecord = 0;
}
obj.CountryList = countryList.Take(pageSize).ToList();
return obj;
}
The method GetCountry is invoked from Index and CountryList actions.
An IEnumerable of type CountryViewModel is defined which ultimately hold the list of all countries. A switch-case block is used which identifies the column number using isAsc variable and sorts using with the help of extension method accordingly.
Also, depending on the current page and page size set from web.config, the LINQ query will select only specific records.
Listing 4: LINQ query to fetch specific records (GridViewController.cs)
countryList = (from p in db.CountryList
select new CountryViewModel
{
CountryID = p.CountryID,
Name = p.Name,
Region = p.Region
}).OrderBy(sortColumn).Skip((page - 1) * pageSize).Take(pageSize + 1).ToList();
Listing 5: Index action method code when page loads for the first time
public ActionResult Index()
{
CountryTypeViewModel obj = new CountryTypeViewModel();
obj = GetCountry(1, 1, true,false);
List PageCount = new List();
for (int i = 1; i <= Math.Ceiling(obj.TotalPages / obj.pageSize); i++)
PageCount.Add(i.ToString());
ViewBag.TotalCountDropDown = new SelectList(PageCount.ToList());
ViewBag.SelectPageIndex = 1;
return View(obj);
}
Listing 6: CountryList action method called when next or previous button is clicked
public ActionResult CountryList(int page = 1, int sortBy = 1, bool isAsc = true, string TotalCount = null, string SelectPageIndex = null)
{
CountryTypeViewModel obj = new CountryTypeViewModel();
bool isPaging = false;
if (page > 1)
isPaging = true;
if (SelectPageIndex != null)
{
page = Convert.ToInt32(SelectPageIndex);
isPaging = true;
}
obj = GetCountry(page, sortBy, isAsc, isPaging);
if (obj.TotalPages == 0)
obj.TotalPages = Convert.ToDouble(TotalCount);
List PageCount = new List();
for (int i = 1; i <= Math.Ceiling(obj.TotalPages / obj.pageSize); i++)
PageCount.Add(i.ToString());
ViewBag.TotalCountDropDown = new SelectList(PageCount.ToList());
ViewBag.SelectPageIndex = 1;
return View("Index", obj);
}
This completes the business logic on Controller side.
Let’s take a look at code on the View side.
The helper method SortLink will create link for table header column and the for-each loop creates table rows.
Listing 7: Helper method for SortLink
@helper sortLink(string name, int id)
{
<a href="@Url.Action("CountryList", "GridView", new { sortby = id, search = @Model.Search, isasc = (id == Model.sortBy ? (!Model.isAsc).ToString() : "true"), TotalCount = Model.TotalPages })">@name</a>
if (id == Model.sortBy)
{
<span class="arrow @(Model.isAsc ? "up" : "down")"></span>
}
}
<table class="FilterTable">
<tr>
<th>@sortLink("ID", 1)</th>
<th>@sortLink("Country Name", 2)</th>
<th>@sortLink("Region", 3)</th>
</tr>
@{int i = 1;
foreach (GridViewSample.Models.ViewModel.CountryViewModel item in Model.CountryList)
{
<tr class="@(i++ % 2 == 0 ? "highlighted" : "")">
<td>@item.CountryID</td>
<td>@item.Name</td>
<td>@item.Region</td>
</tr>
}
}
</table>
Performance Aspect
Let’s take a look at coding to see how the performance was improved. As a developer, it is very important that we focus on performance during the development cycle instead of investing time after the task gets completed or when the feature goes live. Most of the solutions were fetching all records from SQL Server and later sorting was performed on objects during runtime. This resulted in setting up two objectives which I tried to accomplish using this sample application.
1. When paging is done, fetch only number of records defined in the page size instead of fetching all records and then filtering the records based on page index. Most solutions were fetching all records from database and later paging was done in .NET application pool. Every next or previous click was hitting the server for all the records. When records are less no performance issue you would see generally but when records are in millions, impact is addressable easily and we see the performance lag.
2. When we go for sorting the records, it’s done on all records not on just grid view’s current page records using OrderBy clause. One way doing it is just retrieve all records, apply the OrderBy clause on it and show the sorted records based on pagination current page number. Here again, we are stuck to all records retrieving process which is not desirable.
Both objectives were achieved using LINQ Extension methods. Here is the reference link which I have referred for this sample application.
http://aonnull.blogspot.in/2010/08/dynamic-sql-like-linq-orderby-extension.html
Listing 8: Use of Extension method in paging and sorting query.
string sortColumn = string.Empty;
switch (sortBy)
{
case 1:
if (isAsc)
sortColumn = "CountryID";
else
sortColumn = "CountryID Desc";
break;
case 2:
if (isAsc)
sortColumn = "Name";
else
sortColumn = "Name Desc";
break;
case 3:
if (isAsc)
sortColumn = "Region";
else
sortColumn = "Region Desc";
break;
}
The code snippet given below shows the use of sorting and paging using LINQ.
.OrderBy(sortColumn).Skip((page - 1) * pageSize).Take(pageSize + 1).ToList();
Now both sorting and paging work efficiently by fetching required records using skip() and take() methods from LINQ.
Deploy Database into LocalDb using Package Manager Console
1. Download the source code file attached below.
2. Unzip and open GridViewSample.sln using Visual Studio 2012.
3. Build solution using Ctrl + Shift + B or click on Build menu -> Build Solution.
4. Now open Package Manager Console from Tools -> Library Package Manager.
5. Migration is already enabled. Hence, type the following command in Package Manager Console window.
PM> Update-Database –verbose
6. The above command will deploy the database into LocalDb and using the Seed method populate few records into Countries table.
7. Now hit Ctrl + F5 to run the application.
Areas of Improvement
This sample application can be tweaked further to include search feature. I would recommend using Lucene.Net for search.
I hope you find this post helpful. Happy Coding :)
Download Sample
Click here to download the sample application explained in this blog.