Grid View With Server Side Filtering, Sorting And Paging In ASP.NET MVC 5

Background

In the previous post (Beginners guide to Create GridView in asp.net mvc), we talked how we can achieve a GridView type functionality in ASP.NET MVC, similar to that in ASP.NET Webforms. We saw how easy it is to implement a grid, using jQuery datatables plugin which provides vital features, such as searching, sorting, and pagination.

One thing to notice in the previous post is that all the features provided by the plugin are client-side which means that all the data is loaded in the page first and then the plugin handles the data on client side for searching, pagination, and sorting. That is fine if the result sets coming are not very big, but it can cause problems if the table is too big or the data is not that much but grows gradually as applications are used. So, in that case, this way of creating the grid would fail in long run.

Introduction

In this post, we will be seeing how we can implement the server side pagination, searching, and sorting which is, of course, a better approach in the long run and for the applications where data sets are too big.

We will be modifying the source code from the previous post for this. So, let’s get started.

First of all, we need to install the datatables.mvc5 from NuGet Package Manager. It is a datatables model binder to controller implemented by Stefan Nuxoll. You might be thinking why we do need this package. It is because the binder will provide strongly typed model posted at Controller which will help us avoid reading the request parameter and save us from type-casting the parameters from Request, because all the parameters posted in Request object are not type safe. So, we have to convert them manually to their destination type. This will help the developers to focus on business logic instead of playing around with Http parameters, checking them, and casting to the right type.

The good thing about this binder is that you can add custom parameters sent in the request, if your business requires that.

You can add your own custom parameters, if needed, by providing your own implementation of IDataTablesRequest. You will also need to override the BindModel and MapAdditionalColumns method of it.

 

Now, we will install datatables.mvc5 .

1. Go to Tools >> NuGet Package Manager >> Manage Nuget Packages for Solution and click it.

 

 

2. The package manager will get opened. By default, it will be displaying the installed nuGet packages in your solution. Click the Browse button.

 

3. Search for datatables.mvc5 package and select it. 

 

4. Check the projects of the Solution in which you want to install this package. In our case, we are installing it in GridExampleMVC web project only.

 

5. Now, press the Install button.

 

 

6. Select the correct package (see the above screenshot) from the top one returned in the search results and install it.

 

If the installation of package goes successful, you will be able to see it in the References of the project:



7. Go to the Index.cshtml file and update the html of the View while removing the thead and the tbody elements of the table. Your updated html would be:

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-primary list-panel" id="list-panel">
            <div class="panel-heading list-panel-heading">
                <h1 class="panel-title list-panel-title">Assets</h1>
            </div>
            <div class="panel-body">
                <table id="assets-data-table" class="table table-striped table-bordered" style="width:100%;">
                </table>
            </div>
        </div>
    </div>
</div>

We removed the head and body of the table because it would get generated by the datatables plugin itself. Now, we will have to update the jQuery datatables initialization, so that it loads data from server side via AJAXing.

8. For that, add the following code in the Index.cshtml View:

@section Scripts
{

<script type="text/javascript">
        var assetListVM;
        $(function () {
            assetListVM = {
                dt: null,

                init: function () {
                    dt = $('#assets-data-table').DataTable({
                        "serverSide": true,
                        "processing": true,
                        "ajax": {
                            "url": "@Url.Action("Get","Asset")"
                        },
                        "columns": [
                            { "title": "Bar Code", "data": "BarCode", "searchable": true },
                            { "title": "Manufacturer", "data": "Manufacturer", "searchable": true },
                            { "title": "Model", "data": "ModelNumber", "searchable": true },
                            { "title": "Building", "data": "Building", "searchable": true },
                            { "title": "Room No", "data": "RoomNo" },
                            { "title": "Quantity", "data": "Quantity" }
                        ],
                        "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
                    });
                }
            }

            // initialize the datatables
            assetListVM.init();

        });

</script>

 }

9. After this, we will write the Get action code in the AssetController. First, we need to reference the System.Linq.Dynamic namespace as we will be using the methods provided for dynamic linq in our action. For that, go to NuGet Package Manager once again and search for System.Linq.Dynamic package and install it in your project.



10. After installing the package, go to Asset Controller and write the Get action implementation, as shown below:

public ActionResult Get([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
        {
            IQueryable<Asset> query = DbContext.Assets;
            var totalCount = query.Count();

            #region Filtering
            // Apply filters for searching
            if (requestModel.Search.Value != string.Empty)
            {
                var value = requestModel.Search.Value.Trim();
                query = query.Where(p => p.Barcode.Contains(value) ||
                                         p.Manufacturer.Contains(value) ||
                                         p.ModelNumber.Contains(value) ||
                                         p.Building.Contains(value)
                                   );
            }

            var filteredCount = query.Count();

            #endregion Filtering

            #region Sorting
            // Sorting
            var sortedColumns = requestModel.Columns.GetSortedColumns();
            var orderByString = String.Empty;

            foreach (var column in sortedColumns)
            {
                orderByString += orderByString != String.Empty ? "," : "";
                orderByString += (column.Data) + (column.SortDirection == Column.OrderDirection.Ascendant ? " asc" : " desc");
            }

            query = query.OrderBy(orderByString == string.Empty ? "BarCode asc" : orderByString);

            #endregion Sorting

            // Paging
            query = query.Skip(requestModel.Start).Take(requestModel.Length);


            var data = query.Select(asset => new
            {
                AssetID = asset.AssetID,
                BarCode = asset.Barcode,
                Manufacturer = asset.Manufacturer,
                ModelNumber = asset.ModelNumber,
                Building = asset.Building,
                RoomNo = asset.RoomNo,
                Quantity = asset.Quantity
            }).ToList();

            return Json(new DataTablesResponse(requestModel.Draw, data, filteredCount, totalCount), JsonRequestBehavior.AllowGet);
        }

11. Now, build the project and run it in the browser to see the working GridView with server side filtering, paging, and sorting in action.