Grid View with Server Side Filtering, Sorting and Paging in asp.net mvc 5
Download Source Code
Background
In the previous post, we talked about how we can achieve a gridview type functionality in asp.net mvc same like we have in asp.net webforms. We saw that how easy it is to implement a gird 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 which 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 is used, so if that is the 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 or for the applications where datasets 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 datatables.mvc5 from nuget package manager, it is a datatables model binder to controller implemented by Stefan Nuxoll. You may be thinking why we need this package, it is because the binder will provide strongly typed model posted at controller, which will help us to avoid reading the request parameter and will also save us from type-casting the parameters from Request as all the parameters posted in Request object are not type safe so we have to convert them manually to their destination type, which 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 requirements need that.
You can add your own custom parameters if needed by providing your own implementation of IDataTablesRequest, and you will also need to override the BindModel and MapAdditionalColumns method of it.
So now we will install datatables.mvc5 , Go to Tools >> NuGet Package Manager >> Manage Nuget Packages for Solution and click it.
The package manager will get opened and by default it will be displaying the installed nugget packages in your solution, click the Browse button and then search for datatable.mvc5 package, then select it and check the projects of the solution in which you want to install this package, in our case we are installing in it GridExampleMVC web project only as per requirement and then press the install button.
Select the correct package which is in the above screenshot the top one returned in the search results and install it.
If the installation of package goes successful, you will be able to see in the References of the project:
Go to the Index.cshtml file and update the html of the view by removing the thead and 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 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.
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>
}
After this, we will write the Get action code in the AssetController, for that 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.
After installing the package, go to AssetController and write the Get action implementation which will be :
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);
}
Now build the project, and run it in browse to see the
working grid view with server side filtering, paging and sorting in action.
Source Code
The source code is available for download from MSDN code samples gallery here
