Implement Search, Paging and Sort in MVC 5

For the purpose of this demo we will be using the following SQL Server database table with the following records.

Query used

1. CREATE DATABASE db_Students USE db_Students CREATE TABLE Students(  
2. Id INT IDENTITY PRIMARY KEY,  
3. Name NVARCHAR(max),  
4. Gender NVARCHAR(10),  
5. Subjects NVARCHAR(max)  
6. );  
7. INSERT INTO Students  
8. VALUES  
9. (  
10. 'James Cameron', 'Male', 'Objctive C'  
11. ),  
12. ('Max Payne', 'Male', 'C-Sharp'),  
13. ('Lara Croft', 'Female', 'Java'),  
14. ('Aiden Pearce', 'Male', 'C++'),  
15. (  
16. 'Sam Fisher', 'Male', 'Ruby on Rails'  
17. ),  
18. (  
19. 'Iron Man', 'Male', 'Objective C'  
20. ),  
21. ('Black Widow', 'Female', 'C++'),  
22. (  
23. 'Michael Nolan', 'Male', 'C-Sharp'  
24. ),  
25. (  
26. 'Trevor DSouza', 'Male', 'Asp.Net'  
27. )

Download the source code from here.

To implement the preceding three functionalities please use the following procedure.

Step 1

The first step is to create a new MVC 5 web application.

Click "File" -> "New" -> "Project...".

Select Web from the installed template and choose the type of new project as ASP.NET Web Application.

Provide a meaningful name and click OK.

Step 2

Select an empty template and choose the type as MVC for the ASP.Net project.



Once you click OK, a blank ASP.Net MVC application will be created.

So we have created the required MVC 5 application. The next step is to add a model that we will be working with.

Step 3

Adding a model is very simple. All we need to do is go to the Solution Explorer, right-click on the models folder then click Add -> New Item then choose the model type as ADO.NET Entity Data Model from the data template and click Add.



Choose EF Designer from the database as the model contents and click Next.



Click on the New Connection button.



Specify the server name, select the authentication mode and select the database.



Click OK.

Specify the connection string name and click Next.



Select the table and click Finish.



Step 4

The next step is to add a Controller.

Right-click on the controller folder then select Add -> controller.

Select MVC 5 Controller with read/write actions and click Add.

Provide the controller a name.

Click Add.

So, we have added our controller successfully.

Step 5

Now, if you look at the HomeController.cs file, this HomeController is present in the namespace “SearchSortPaging.Controller” whereas our Model is present in SearchSortPaging.Models namespace. So, in order to use the model class in our home controller, we need to import the model namespace as in the following:

using SearchSortPaging.Models;



Step 6

Before adding any of the functionality let's first check if we are able to retrieve the records from the database.

1. public class HomeController: Controller {  
2.   
3.     //create a StudentsDbContext object  
4.     StudentsDbContext db = new StudentsDbContext();  
5.     public ActionResult Index() {  
6.         //the StudentsDbContext object has got a property Students which will give us all the student records back. Convert it into a list  
7.   
8.         List < Student > StudentList = db.Students.ToList();  
9.         //pass the StudentList list object to the view.  
10.         return View(StudentList);  
11.     }  
12. }

Step 7

The next step is to add an Index view and adding a view is pretty straight forward. All we need to do is right-click on the Index action method and select add view.



Click Add and run the application.



So, our application is working as expected.

Now let's add searching, sorting and paging functionality one-by-one.

Search functionality

Step 1

To implement this functionality, there are a few modifications that we need to make in our Index view.

1. @model IEnumerable < SearchSortPaging.Models.Student >  
2.   
3. @ {  
4.     ViewBag.Title = "Index";  
5. }  
6.   
7. @ * Html.BeginForm is used to add a form in an Html document * @@ * Pass the action name in the first parameter, controller name in the second parameter and specify the form request type as get * @  
8.   
9. @using(Html.BeginForm("Index", "Home", FormMethod.Get)) {  
10.     //the following are search options  
11.     <  
12.     b > Search Option: < /b>@Html.RadioButton("option", "Name") <text>Name</text > @Html.RadioButton("option", "Gender") < text > Gender < /text>  
13. @Html.RadioButton("option", "Subjects")<text>Subject</text > @Html.TextBox("search") < input type = "submit"  
14.     name = "submit"  
15.     value = "Search" / >  
16. }

Note: The preceding code should be placed just before the table.

The rest the code in the Index view remains the same.

Step 2

Write the following in the Index action method.

1. //the first parameter is the option that we choose and the second parameter will use the textbox value  
2. public ActionResult Index(string option, string search) {  
3.   
4.     //if a user choose the radio button option as Subject  
5.     if (option == "Subjects") {  
6.         //Index action method will return a view with a student records based on what a user specify the value in textbox  
7.         return View(db.Students.Where(x = > x.Subjects == search || search == null).ToList());  
8.     } else if (option == "Gender") {  
9.         return View(db.Students.Where(x = > x.Gender == search || search == null).ToList());  
10.     } else {  
11.         return View(db.Students.Where(x = > x.Name.StartsWith(search) || search == null).ToList());  
12.     }  
13. }  

Save and run the application.

Look at the URL. It just states the local host port number.

Now choose any of the options and specify the search keyword.

Look at the URL: http://localhost:53119/?option=Name&search=M&submit=Search

Option = Name

Search = M

So, all the name that starts with letter M is displayed.

Choose another search option.

Let's say we choose Gender this time and we want only the female student details.

http://localhost:53119/?option=Gender&search=Female&submit=Search

This time the option is Gender and the search value is Female.

We got only the Female records.

Choose the search option as the subject.

http://localhost:53119/?option=Subjects&search=C%2B%2B&submit=Search


We got only those records whose Subjects is C++.

So, we have successfully implemented search functionality.

Now what will happen if we specify an invalid search term?

The option is Gender but the search term is ff and there are no records in our table whose gender is ff. So, we got a blank record.

Step 3

Let's say we don't want this blank record to be displayed but let's say we want to display a meaningful message stating “Records not found”.

To get the preceding functionality we need to modify our Index view.

1. @ * Here our model is IEnumerable of Students.So, we can check  
2. if the Model.Count() method value is 0.  
3. If the value is 0 then we know  
4. for sure that it will  
5. return an empty record * @  
6.   
7. @  
8. if (Model.Count() == 0) { < tr > < td colspan = "3" > Records not found < /td>  
9. </tr >  
10. } else {  
11.   
12.     foreach(var item in Model) { < tr > < td > @Html.DisplayFor(modelItem = > item.Name) < /td>  
13. <td>  
14. @Html.DisplayFor(modelItem => item.Gender)  
15. </td > < td > @Html.DisplayFor(modelItem = > item.Subjects) < /td>  
16. </tr >  
17.     }  
18. }

Note: The preceding code should be placed just after the first closing </tr>.

Run the application and add some garbage.



Paging functionality
Step 1

To implement Paging functionality the first thing we need to do is install PagedList.Mvc using the NuGet package manager.



Search for PagedList.Mvc and click Install.

 

Step 2

Add a "using" statement for the PagedList namespace into HomeController as in the following:

using PagedList;

Step 3

Add a third parameter to the Index action method that will hold the page number and convert the returning view to a PagedList.

1. public ActionResult Index(string option, string search, int? pageNumber) {  
2. if(option == "Subjects") {  
3. return View(db.Students.Where(x => x.Subjects == search || search == null).ToList().ToPagedList(pageNumber ?? 1, 3));  
4. }  
5. else if(option == "Gender") {  
6. return View(db.Students.Where(x => x.Gender == search || search == null).ToList().ToPagedList(pageNumber ?? 1, 3));  
7. }  
8. else {  
9. return View(db.Students.Where(x => x.Name.StartsWith(search) || search == null).ToList().ToPagedList(pageNumber ?? 1, 3));  
10. }  
11. }

In the first parameter of the ToPagedList we specified the page number from where we want the paging to start and in the second parameter we specified the number of records we want to display.

Step 4

We need to modify the Index view. As we know, this view is returning an IPagedList. But currently our view is returning an IEnumerable back. So, replace the Model type from IEnumerable to IPagedList.

1. @using PagedList;  
2.   
3. @using PagedList.Mvc;  
4.   
5. @model IPagedList<SearchSortPaging.Models.Student>  
6.   
7.   
8. <table class="table" border="1">  
9. <tr>  
10. <th>  
11. @Html.DisplayNameFor(model => model.First().Name)  
12. </th>  
13. <th>  
14. @Html.DisplayNameFor(model => model.First().Gender)  
15. </th>  
16. <th>  
17. @Html.DisplayNameFor(model => model.First().Subjects)  
18. </th>  
19. </tr>

First() is an extension method that returns the first element from a sequence.

Run the application.

At the moment our Index view is returning only three records because, while passing the records to the view, we specified that only 3 records should be displayed in each page. Now we need to add page numbers so that we can view the rest of the records.

Step 5

To add paging we can use PagedListPager html helper.

1. @Html.PagedListPager(Model,pageNumber => Url.Action("Index",new{  
2. pageNumber  
3. }))

The first parameter is the IPagedList instance that is our Model.

The second parameter expects a predicate where we need to specify the page URL and that will hold the pageNumber.

Run the application.
Page Number 1

Page Number 2

Now let's choose any of the search options and specify a valid search keyword. Here I choose the search option as Gender and the search term as Male.

Now once I click on page 2 or 3 look what happens.

We are getting a Female record too. Look at the URL now as in the following:

http://localhost:53119/?pageNumber=2

But before it was:

http://localhost:53119/?option=Gender&search=Male&submit=Search

And as we know there are two more parameters in the Index action method and to retain the state of those parameter values we need to specify it in the PagedListPager object route values.

1. @Html.PagedListPager(Model,pageNumber => Url.Action("Index",new{  
2. pageNumber,  
3. option = Request.QueryString["option"],  
4. search = Request.QueryString["search"]  
5. }))

Run the application. 

Click on Page 2.

Page 3



The following is the entire Index.cshtml view:

1. @using PagedList;  
2.   
3. @using PagedList.Mvc;  
4.   
5. @model IPagedList < SearchSortPaging.Models.Student > < link href = "~/Content/PagedList.css"  
6. rel = "stylesheet" / > @ {  
7.     ViewBag.Title = "Index";  
8. }  
9. @using(Html.BeginForm("Index", "Home", FormMethod.Get)) { < b > Search Option: < /b>@Html.RadioButton("option", "Name") <text>Name</text > @Html.RadioButton("option", "Gender") < text > Gender < /text>  
10. @Html.RadioButton("option", "Subjects")<text>Subject</text > @Html.TextBox("search") < input type = "submit"  
11.     name = "submit"  
12.     value = "Search" / >  
13. } < table class = "table"  
14. border = "1" > < tr > < th > @Html.DisplayNameFor(model = > model.First().Name) < /th>  
15. <th>  
16. @Html.DisplayNameFor(model => model.First().Gender)  
17. </th > < th > @Html.DisplayNameFor(model = > model.First().Subjects) < /th>  
18. </tr >  
19.   
20. @  
21. if (Model.Count() == 0) { < tr > < td colspan = "3" > Records not found < /td>  
22. </tr >  
23. } else {  
24.   
25.     foreach(var item in Model) { < tr > < td > @Html.DisplayFor(modelItem = > item.Name) < /td>  
26. <td>  
27. @Html.DisplayFor(modelItem => item.Gender)  
28. </td > < td > @Html.DisplayFor(modelItem = > item.Subjects) < /td>  
29. </tr >  
30.     }  
31. } < /table>  
32.   
33. @Html.PagedListPager(Model, pageNumber => Url.Action("Index", new {  
34. pageNumber,  
35. option = Request.QueryString["option"],  
36. search = Request.QueryString["search"]  
37. }))

Now let's see how to implement sort functionality.

Sorting functionality
In our database table there are 9 records. But in an actual scenario there could be thousands of records and each record user name may start with any letter character and if there is a requirement to display all the records in an alphabetical order and that could be difficult. To solve this problem we can use sorting.

Let's say we want to sort the records by Name and we also want to sort the records by Gender too.

When the page loads, we want to sort the records by Name in ascending order and once the Name column is clicked it should sort in descending order and so for the Gender column too.

Step 1

Write the following in the Index action method.

1. using PagedList;  
2. using SearchSortPaging.Models;  
3. using System.Linq;  
4. using System.Web.Mvc;  
5.   
6. namespace SearchSortPaging.Controllers {  
7.     public class HomeController: Controller {  
8.         StudentsDbContext db = new StudentsDbContext();  
9.   
10.         //add a fourth parameter  
11.         public ActionResult Index(string option, string search, int ? pageNumber, string sort) {  
12.   
13.             //if the sort parameter is null or empty then we are initializing the value as descending name  
14.             ViewBag.SortByName = string.IsNullOrEmpty(sort) ? "descending name" : "";  
15.             //if the sort value is gender then we are initializing the value as descending gender  
16.             ViewBag.SortByGender = sort == "Gender" ? "descending gender" : "Gender";  
17.   
18.             //here we are converting the db.Students to AsQueryable so that we can invoke all the extension methods on variable records.  
19.             var records = db.Students.AsQueryable();  
20.   
21.             if (option == "Subjects") {  
22.                 records = records.Where(x = > x.Subjects == search || search == null);  
23.             } else if (option == "Gender") {  
24.                 records = records.Where(x = > x.Gender == search || search == null);  
25.             } else {  
26.                 records = records.Where(x = > x.Name.StartsWith(search) || search == null);  
27.             }  
28.   
29.             switch (sort) {  
30.   
31.                 case "descending name":  
32.                     records = records.OrderByDescending(x = > x.Name);  
33.                     break;  
34.   
35.                 case "descending gender":  
36.                     records = records.OrderByDescending(x = > x.Gender);  
37.                     break;  
38.   
39.                 case "Gender":  
40.                     records = records.OrderBy(x = > x.Gender);  
41.                     break;  
42.   
43.                 default:  
44.                     records = records.OrderBy(x = > x.Name);  
45.                     break;  
46.   
47.             }  
48.             return View(records.ToPagedList(pageNumber ? ? 1, 3));  
49.         }  
50.     }  
51. }

Step 2

Write the following in Index.cshtml.

1. @using PagedList;  
2.   
3. @using PagedList.Mvc;  
4.   
5. @model IPagedList < SearchSortPaging.Models.Student > < link href = "~/Content/PagedList.css"  
6. rel = "stylesheet" / > @ {  
7.     ViewBag.Title = "Index";  
8. }  
9.   
10.   
11. @using(Html.BeginForm("Index", "Home", FormMethod.Get)) {  
12.   
13.     < b > Search Option: < /b>@Html.RadioButton("option", "Name") <text>Name</text > @Html.RadioButton("option", "Gender") < text > Gender < /text>  
14. @Html.RadioButton("option", "Subjects")<text>Subject</text > @Html.TextBox("search") < input type = "submit"  
15.     name = "submit"  
16.     value = "Search" / >  
17. } < table class = "table"  
18. border = "1" > < tr > < th > @Html.ActionLink("Name", "Index", new {  
19.     sort = ViewBag.SortByName, option = Request.QueryString["option"], search = Request.QueryString["search"]  
20. }) < /th>  
21. <th>  
22. @Html.ActionLink("Gender", "Index", new { sort = ViewBag.SortByGender, option = Request.QueryString["option"], search = Request.QueryString["search"] })  
23. </th > < th > @Html.DisplayNameFor(model = > model.First().Subjects) < /th>  
24. </tr >  
25.   
26. @  
27. if (Model.Count() == 0) { < tr > < td colspan = "3" > Records not found < /td>  
28. </tr >  
29. } else {  
30.   
31.     foreach(var item in Model) { < tr > < td > @Html.DisplayFor(modelItem = > item.Name) < /td>  
32. <td>  
33. @Html.DisplayFor(modelItem => item.Gender)  
34. </td > < td > @Html.DisplayFor(modelItem = > item.Subjects) < /td>  
35. </tr >  
36.     }  
37. } < /table>  
38.   
39. @Html.PagedListPager(Model, pageNumber => Url.Action("Index", new {  
40. pageNumber,  
41. option = Request.QueryString["option"],  
42. search = Request.QueryString["search"],  
43. sort = Request.QueryString["sort"]  
44. }))

NOTE: Once the page loads, the Name link will be empty that will sort the Names in ascending order and once we will click the Name link it will sort the records in descending order.

1. @Html.ActionLink("Name", "Index", new {  
2.     sort = ViewBag.SortByName,  
3. })  
4.   
5. If a user specify the search option and term and in order to retain the state of those search criteria we need to pass them in Name action link.  
6.   
7. @Html.ActionLink("Name", "Index", new {  
8.     sort = ViewBag.SortByName, option = Request.QueryString["option"], search = Request.QueryString["search"]  
9. })

Run the application.

Click on the Name link.



So now the records are sorted in descending order.

Click on Page 2.

So, now we have the records for page 2 and this page too is sorted because we specified a sort parameter in PagedListPager html helper.

1. @Html.PagedListPager(Model, pageNumber => Url.Action("Index", new {  
2. pageNumber,  
3. option = Request.QueryString["option"],  
4. search = Request.QueryString["search"],  
5. sort = Request.QueryString["sort"]  
6. }))

Sort the records by Gender.

Page 2



Specify the search option.

And if we click the Name link then that will sort the records in descending order. The Name action link will still retain the search criteria because we have specified the option and search route values.

1. <th>  
2. @Html.ActionLink("Name", "Index", new { sort = ViewBag.SortByName, option = Request.QueryString["option"], search = Request.QueryString["search"] })  
3. </th>