Webgrid Paging, sorting and filtering in ASP.Net MVC

Saturday, November 19, 2016 Sourav Mondal Webgrid

Introduction

In one of the previous article, I have shown you, how we can implement custom paging in webgrid in ASP.NET MVC application.
Today, In this tutorial I will show you webgrid Paging, sorting and filtering functionality in asp.net MVC application.

It is an essential approach to using paging, sorting and filtering technique in applications where a lot of data to be loaded from a database. So here in this article, I have tried to show how we can do this
just following few simple steps.

Also nowadays, web page looks is very important and as we know bootstrap design is very popular, here in this article I have implemented bootstrap design in webgrid for making its looks perfect.

You can see the below image, how the page will look like when you're done.

The column headings are links that the user can click to sort by that column and also showing sort arrow for showing current sort order. Clicking a column heading repeatedly toggles between ascending and descending sort order.

Ok let start implement webgrid paging, sorting and filtering functionality with bootstrap looks in MVC application.

Follow the following steps in order to implement "Webgrid paging, sorting and filtering in ASP.NET MVC".

Here In this article, I have used Visual Studio 2013

Step - 1: Create New Project.

Go to File > New > Project > ASP.NET Web Application (under web) > Enter enter application name > select your project location > and then click on add button > It will brings up a new dialog window for select template > here I will select Empty template > checked MVC checkbox from Add folder and core referances for: > and then click on ok button.

Step-2: Add required js & css library from NuGet.

Now I will add Jquery & Bootstrap CSS & JS library into our application.
Go to Solution Explorer > Right Click on Project name from Solution Explorer > Manage NuGet packages > Search for Jquery for adding jquery library > Select the "jQuery" library and then click on Install button.

Sameway we will add Bootstrap CSS & JS library.

Step-3: Add a Database.

Now I will create a database for our application. As this is a tutorial project, I will add a database in our applications here in the app_data folder.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-4: Create a table in our database.

In this example, I have added the table for store Employee information for showing in webgrid.

double click on the database under app_data folder for open the database in server explorer > expand the database and Right click on Tables node > click on Add New Table > here we will write schema of the table for the table we want to create > now click on Update button for create the table and then again click on Update Database button.

Step-5: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-6: Add a new class WebGridExtensions.cs

We need to just add 1 another class (WebGridExtensions.cs

) in our application.
We will get the class from this link : https://github.com/mdobie/WebGridPagerReplacement

Step-7: Add reference of System.Linq.Dynamic.

Add reference of System.Linq.Dynamic for dynamic sorting using string column name. Using
System.Linq.Dynamic library we can do sorting using string column name & sort direction. We can do sorting like this data.OrderBy("FirstName desc")

Go to solution explorer > Right click on Project Name > Manage NuGet packages > Search for System.Linq.Dynamic & install.

Step-8: Create an MVC Controller.

Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.

Here I have created a controller named "HomeController"

Step-9: Add a new method in HomeController.

Here I have added a method GetEmployees for fetch employees data from the database.

public List<Employee> GetEmployees(string search, string sort, string sortdir, int skip, int pageSize, out int totalRecord)
{
    using (MyDatabaseEntities dc = new MyDatabaseEntities())
    {
        var v = (from a in dc.Employees
                    where
                            a.FirstName.Contains(search) ||
                            a.LastName.Contains(search) ||
                            a.EmailID.Contains(search) ||
                            a.City.Contains(search) ||
                            a.Country.Contains(search)
                    select a
                        );
        totalRecord = v.Count();
        v = v.OrderBy(sort + " " + sortdir);
        if (pageSize > 0)
        {
            v = v.Skip(skip).Take(pageSize);
        }
        return v.ToList();
    }
}

Step-10: Write code in Index action of HomeController.

Now In this Index action we will write code for show employees data in webgrid.

public ActionResult Index(int page =1, string sort = "FirstName", string sortdir="asc", string search="")
{
    int pageSize = 10;
    int totalRecord = 0;
    if (page < 1) page = 1;
    int skip = (page * pageSize) - pageSize;
    var data = GetEmployees(search, sort, sortdir, skip, pageSize, out totalRecord);
    ViewBag.TotalRows = totalRecord;
    ViewBag.search = search;
    return View(data);
}

Step-11: Add view for Index action.

Right Click on Action Method (here right click on Index action) > Add View... > Enter View Name > Select "Empty" under Template dropdown > > Add.

HTML Code

@model List<WebgridPagingSortingFiltering.Employee>

@{
    Layout = null;
    var grid = new WebGrid(canPage: true, rowsPerPage: 10);
    grid.Bind(source: Model, rowCount: ViewBag.TotalRows, autoSortAndPage: false);
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
    <div class="container">
        <h2>Employees List</h2> 
        <nav class="navbar navbar-default">
            <div class="container-fluid">
                <div class="navbar-header">
                    <a href="#" class="navbar-brand">Search</a>
                </div>
                <div class="collapse navbar-collapse">
                    @using (Html.BeginForm("index","home", FormMethod.Get,new{@class="navbar-form navbar-left"}))
                    {
                        <div class="form-group">
                            <input type="text" name="search" value="@ViewBag.search" class="form-control" placeholder="search" />
                        </div>
                        <button type="submit" value="Search" class="btn btn-success">Search</button>
                    }
                </div>
            </div>
        </nav>
        <div>
            @grid.Table(
                tableStyle: "table table-responsive table-bordered",
                columns: grid.Columns(
                    grid.Column(columnName:"FirstName", header: "First Name"),
                    grid.Column(columnName:"LastName", header: "Last Name"),
                    grid.Column(columnName: "EmailID", header: "Email"),
                    grid.Column(columnName: "City", header: "City"),
                    grid.Column(columnName:"Country", header: "Country")
                )
            )

            <div class="row">
                <div class="col-sm-6">
                    @grid.PagerList(mode: WebGridPagerModes.All, paginationStyle: "pagination pagination-small pagination-right")
                </div>
            </div>

            @Html.Hidden("dir", grid.SortDirection)
            @Html.Hidden("col", grid.SortColumn)

        </div>
    </div>
    <script src="~/Scripts/jquery-3.1.1.js"></script>
    <script src="~/Scripts/bootstrap.min.js"></script>
    <script>
        $(function () {
            try {
                var dir = $('#dir').val();
                var col = $('#col').val();
                var header = $("th a[href*=" + col + "]");
                if (dir == "Ascending") {
                    header.text(header.text() + "  ▲")
                }
                if (dir == "Descending") {
                    header.text(header.text() + "  ▼");
                }

            } catch (e) {

            }
        })
    </script>
</body>
</html>

Step-12: Run Application.

We have done all the steps. Now it's time to run the application.

Live Demo & Download