In this article, we are going to implement paging, sorting, and searching of data in C# MVC application.In previous two articles, i have explained basics of MVC C# , then wrote another article on inserting and fetching data in MVC using entity framework, if you haven't read both these articles and you don't have much knowledge in MVC(or you are a beginner) please go through these articles first, it won't take much of your time.
From this point, I assume, you have knowledge of fetching list data from database using MVC C#.
So let's fetch all the data from database using Entity Framework (Ado.NET), code below
public ActionResult Index()
{
//Create a new list variable of Customer Model type
var ModelList = new List<Customer>();
//initialize context
using (var context = new NorthWindEntities())
{
//fill list model with customer list from database
var model = from s in context.Customers
select s;
ModelList=model.ToList();
}
// return List model to View
return View(ModelList);
}
In the view, we can have code
<!--accepts model of Ienumerable(list type)-->
@model IEnumerable<Test.Models.Customer>
@{
ViewBag.Title = "Customer List";
}
<!--Table with bootstrap class .table and .table-condensed-->
<table class="table table-condensed">
<thead>
<tr>
<th>Customer Name</th>
<th>Work Area</th>
<th>City</th>
</tr>
</thead>
<tbody>
<!--Loop through each customer to show it's data-->
@foreach (var Customer in Model)
{
<tr>
<td>@Customer.Customername</td>
<td>@Customer.WorkArea</td>
<td>@Customer.City</td>
</tr>
}
</tbody>
</table>
Output of this code would be:-
Sorting
Now let's add sorting into this table, using "Customer Name" column, for this we will have to add link to table head of Customer Name column, and change C# code of controller
In the view, we need to change table column header as
<tr>
<th> @Html.ActionLink("Customer Name", "Index", new { sortOrder = ViewBag.NameSortParm }) </th>
<th>Work Area</th>
<th>City</th>
</tr>
It can be This code uses the information in the ViewBag
properties to set up hyperlinks with the appropriate query string values.
Code change in Index ActionMethod would be
public ActionResult Index(string sortOrder)
{
//Add ViewBag to save SortOrder of table
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
var ModelList = new List<Customer>();
using (var context = new NorthWindEntities())
{
var model = from s in context.Customers
select s;
//Switch action according to sortOrder
switch (sortOrder)
{
case "name_desc":
ModelList = model.OrderByDescending(s => s.Customername).ToList();
break;
default:
ModelList = model.OrderBy(s => s.Customername).ToList();
break;
}
}
return View(ModelList);
}
Output :-
Filtering
Now let's add the filtering functionality in the page, in Controllers\HomeController.cs, replace the Index
method with the following code:
public ActionResult Index(string sortOrder, string searchString)
{
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
var ModelList = new List<Customer>();
using (var context = new NorthWindEntities())
{
var model = from s in context.Customers
select s;
//Added this area to, Search and match data, if search string is not null or empty
if (!String.IsNullOrEmpty(searchString))
{
model = model.Where(s => s.Customername.Contains(searchString)
|| s.City.Contains(searchString)
|| s.WorkArea.Contains(searchString));
}
switch (sortOrder)
{
case "name_desc":
ModelList = model.OrderByDescending(s => s.Customername).ToList();
break;
default:
ModelList = model.OrderBy(s => s.Customername).ToList();
break;
}
}
return View(ModelList);
}
We have added a searchString
parameter to the Index
method. The search string value is received from a text box that you'll add to the Index view. You've also added to the LINQ statement a where
clause that selects only students whose first name or last name contains the search string. The statement that adds the where clause is executed only if there's a value to search for
In view, we must have text-box to place search query so let's add this code before customer table
@using (Html.BeginForm())
{
<p>
Find by name: @Html.TextBox("SearchString")
<input type="submit" value="Search" />
</p>
}
<!--Table with bootstrap class .table and .table-condensed-->
<table class="table table-condensed">
Rebuild solution, Run the page, enter a search string, and click Search to verify that filtering is working, and output will be
Paging
Last part of this tutorial is paging, now let's add paging in the table using PagedList.Mvc. So to add paging in the view we need to install it using Nuget.
PagedList.Mvc is one of many good paging and sorting packages for ASP.NET MVC, and its use here is intended only as an example, not as a recommendation for it over other options.
From the Tools menu, select Library Package Manager and then Package Manager Console.
In the Package Manager Console window, make sure ghe Package source is nuget.org and the Default project is ContosoUniversity, and then enter the following command:
Install-Package PagedList.Mvc
Now build the project and do the following changes in Controller and View, for controller part code will be
//This code adds a page parameter, a current sort order parameter, and a current filter parameter to the method signature
public ViewResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
//A ViewBag property provides the view with the current sort order, because this must be included in
// the paging links in order to keep the sort order the same while paging
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
var ModelList = new List<Customer>();
//ViewBag.CurrentFilter, provides the view with the current filter string.
//he search string is changed when a value is entered in the text box and the submit button is pressed. In that case, the searchString parameter is not null.
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewBag.CurrentFilter = searchString;
using (var context = new NorthWindEntities())
{
var model = from s in context.Customers
select s;
//Search and match data, if search string is not null or empty
if (!String.IsNullOrEmpty(searchString))
{
model = model.Where(s => s.Customername.Contains(searchString)
|| s.City.Contains(searchString)
|| s.WorkArea.Contains(searchString));
}
switch (sortOrder)
{
case "name_desc":
ModelList = model.OrderByDescending(s => s.Customername).ToList();
break;
default:
ModelList = model.OrderBy(s => s.Customername).ToList();
break;
}
}
//indicates the size of list
int pageSize = 3;
//set page to one is there is no value, ?? is called the null-coalescing operator.
int pageNumber = (page ?? 1);
//return the Model data with paged
return View(ModelList.ToPagedList(pageNumber, pageSize));
}
I have explained code by commenting in C#, now let's do some changes in View
<!--Changed model type to that of pagedList-->
@model PagedList.IPagedList<Test.Models.Customer>
@using PagedList.Mvc;
@{
ViewBag.Title = "Home Page";
}
@using (Html.BeginForm())
{
<p>
Find by name: @Html.TextBox("SearchString")
<input type="submit" value="Search" />
</p>
}
<!--Table with bootstrap class .table and .table-condensed-->
<table class="table table-condensed">
<thead>
<tr>
<th>@Html.ActionLink("Customer Name", "Index", new { sortOrder = ViewBag.NameSortParm })</th>
<th>Work Area</th>
<th>City</th>
</tr>
</thead>
<tbody>
<!--Loop through each customer to show it's data-->
@foreach (var Customer in Model)
{
<tr>
<td>@Customer.Customername</td>
<td>@Customer.WorkArea</td>
<td>@Customer.City</td>
</tr>
}
</tbody>
</table>
<br />
<!--Code to show curent page and page number-->
Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount
@Html.PagedListPager(Model, page => Url.Action("Index",
new { page, sortOrder = ViewBag.CurrentSort, currentFilter = ViewBag.CurrentFilter }))
If you will check the view code, you will notice at top we are using PagedList model as paging data will be returned from controller now, also have added the new paging code, at the bottom which will show current page and number of pages.
Note:
I have not includede CSS of pagedlist as MVC project is referencing Bootstrap by default, if you not referring bootstrap please include css file
Final output will be
That's it, we are done, please share your reviews on this or any other article after logging in or using Facebook comment's tabs.
Recommended MVC Articles:
What is .NET framework and understanding .NET framework architecture
OutputCache in ASP.NET MVC ( Caching in MVC)
Data annotation in ASP.NET MVC (Example with creating Custom Validation)
How to Create login & registration page in MVC with database (ASP.NET)
File Upload in ASP.NET MVC (Single and Multiple files upload example)