GridView with Server Side Filtering, Sorting and Paging in ASP.NET MVC 5
Background
In the previous post (Beginners Guide for Creating GridView in ASP.NET MVC 5), we talked about how we can achieve a GridView type functionality in ASP.NET MVC similar to ASP.NET web-forms. We saw how easy it was to implement a grid using jQuery datatables plug in 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 plug in are client side, which means that all the data is loaded in the page first and then the plug in handles the data on the client side for searching, pagination and sorting. This is fine if the result sets are not very big, but it can cause problems if the table is too big, or if the data gradually grows as applications are used. If these problems do occur, this way of creating the grid would fail in the long run.
Introduction
In this post, we will see how we can implement the server side pagination, searching, and sorting. This is, of course, a better approach in the long run or for the applications where datasets are too big.
The sample solution can be downloaded from MSDN gallery from this link
We will be modifying the source code from the previous post for this, so let’s get started.
Datatables.net for MVC5
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. Why do we need this package? Because the binder will provide a strongly typed model posted at the controller, which will help us avoid reading the request parameter and will also save us from type-casting the parameters from Request. All the parameters posted in the 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 by providing your own implementation of IDataTablesRequest, and you will also need to override the BindModel and MapAdditionalColumns method of it.
Database Creation
Now let's create database and table that we will be using in this post, open SQL Management Studio and run the following script:
01.CREATE DATABASE [GridExampleMVC]
02. GO
03.
04. CREATE TABLE [dbo].[Assets] (
05. [AssetID] UNIQUEIDENTIFIER NOT NULL,
06. [Barcode] NVARCHAR (MAX) NULL,
07. [SerialNumber] NVARCHAR (MAX) NULL,
08. [FacilitySite] NVARCHAR (MAX) NULL,
09. [PMGuide] NVARCHAR (MAX) NULL,
10. [AstID] NVARCHAR (MAX) NOT NULL,
11. [ChildAsset] NVARCHAR (MAX) NULL,
12. [GeneralAssetDescription] NVARCHAR (MAX) NULL,
13. [SecondaryAssetDescription] NVARCHAR (MAX) NULL,
14. [Quantity] INT NOT NULL,
15. [Manufacturer] NVARCHAR (MAX) NULL,
16. [ModelNumber] NVARCHAR (MAX) NULL,
17. [Building] NVARCHAR (MAX) NULL,
18. [Floor] NVARCHAR (MAX) NULL,
19. [Corridor] NVARCHAR (MAX) NULL,
20. [RoomNo] NVARCHAR (MAX) NULL,
21. [MERNo] NVARCHAR (MAX) NULL,
22. [EquipSystem] NVARCHAR (MAX) NULL,
23. [Comments] NVARCHAR (MAX) NULL,
24. [Issued] BIT NOT NULL,
25. CONSTRAINT [PK_dbo.Assets] PRIMARY KEY CLUSTERED ([AssetID] ASC)
26. )
27. GO
There is a complete SQL script file attached in the source code, so you can use it to create the database and table with sample data.
Setting Up Project
Now, create a new ASP.NET MVC 5 web application. Open Visual Studio 2015. Go to File >> New >> Project.
From the dialog, navigate to Web and select ASP.NET Web Application as project and click OK.
From Templates, select MVC, check the unit tests if you will write unit tests as well for your implementations and click OK.
Our project is created with basic things in place for us. Now, we will start by creating the database context class as we will be using Entity Framework for the Data Access.
Creating Models and Data Access
First of all, we need to create model for the Asset table which we will be using for retrieving data using ORM. In Model folder, create a new class named Asset:
01.using System.ComponentModel.DataAnnotations;
02.
03.namespace GridExampleMVC.Models
04.{
05. public class Asset
06. {
07. public System.Guid AssetID { get; set; }
08.
09. [Display(Name = "Barcode")]
10. public string Barcode { get; set; }
11.
12. [Display(Name = "Serial-Number")]
13. public string SerialNumber { get; set; }
14.
15. [Display(Name = "Facility-Site")]
16. public string FacilitySite { get; set; }
17.
18. [Display(Name = "PM-Guide-ID")]
19. public string PMGuide { get; set; }
20.
21. [Required]
22. [Display(Name = "Asset-ID")]
23. public string AstID { get; set; }
24.
25. [Display(Name = "Child-Asset")]
26. public string ChildAsset { get; set; }
27.
28. [Display(Name = "General-Asset-Description")]
29. public string GeneralAssetDescription { get; set; }
30.
31. [Display(Name = "Secondary-Asset-Description")]
32. public string SecondaryAssetDescription { get; set; }
33. public int Quantity { get; set; }
34.
35. [Display(Name = "Manufacturer")]
36. public string Manufacturer { get; set; }
37.
38. [Display(Name = "Model-Number")]
39. public string ModelNumber { get; set; }
40.
41. [Display(Name = "Main-Location (Building)")]
42. public string Building { get; set; }
43.
44. [Display(Name = "Sub-Location 1 (Floor)")]
45. public string Floor { get; set; }
46.
47. [Display(Name = "Sub-Location 2 (Corridor)")]
48. public string Corridor { get; set; }
49.
50. [Display(Name = "Sub-Location 3 (Room No)")]
51. public string RoomNo { get; set; }
52.
53. [Display(Name = "Sub-Location 4 (MER#)")]
54. public string MERNo { get; set; }
55.
56. [Display(Name = "Sub-Location 5 (Equip/System)")]
57. public string EquipSystem { get; set; }
58.
59. public string Comments { get; set; }
60.
61. public bool Issued { get; set; }
62. }
63.}
Now navigate to Models folder from Solution Explorer and open IdentityModels.cs file. We will add a property for the Asset table in the database context, which will be the Entity Framework representation of Asset table which we created using the script. Add new property in the ApplicationDbContext class:
01.public class ApplicationDbContext : IdentityDbContext<applicationuser>
02.{
03. public ApplicationDbContext()
04. : base("DefaultConnection", throwIfV1Schema: false)
05. {
06. }
07.
08. public DbSet<asset> Assets { get; set; }
09.
10. public static ApplicationDbContext Create()
11. {
12. return new ApplicationDbContext();
13. }
14.}
The above is the default entity framework settings for ASP.NET identity 2.0, we are extending it with our own tables for which we have added new DbSet for Asset table.
Now, add an empty controller in Controllers folder named AssetController, which we will be using for all the Asset related work. Here is how it should look like:
1.public class AssetController : Controller
2.{
3. // GET: Asset
4. public ActionResult Index()
5. {
6. return View();
7. }
8.}
Installing JQuery Datatables
Now, we will install jQuery datatables that we will be using to build the grid, 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 NuGet packages in your solution, click the browser button and then search for JQuery datatables 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.
Visual Studio will prompt to tell that it is going to modify the solution, you will have to press ok to continue the installation of the package.
After the NuGet package is installed successfully, we need to include the necessary js and css of JQuery datatables in the view where we will use it, for that we have to register the JQuery datatables, for that open the BundleConfig.cs file located in App_Start folder and add the following code for css and js files at the end:
1.bundles.Add(new ScriptBundle("~/bundles/datatables").Include(
2. "~/Scripts/DataTables/jquery.dataTables.min.js",
3. "~/Scripts/DataTables/dataTables.bootstrap.js"));
4.
5.bundles.Add(new StyleBundle("~/Content/datatables").Include(
6. "~/Content/DataTables/css/dataTables.bootstrap.css"));
After registering the scripts and CSS for datatables, we need to add them in our master layout which is by default _Layout.cshtml located in Views >> Shared which is defined in the _ViewStart.cshtml located in the same location.
Installing Datatables.mvc5 Package
So now, we will install datatables.mvc5, Go to Tools >> NuGet Package Manager >> Manage NugGet 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 the 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 it in the GridExampleMVC web project (only as per requirement). Then press the install button.
Select the correct package (as shown in the above screenshot, it is the top one returned) in the search results and install it.
If the installation of package is successful, you will be able to see in the References of the project:
Before writing the controller code, we need to configure the connection string for entity framework that will be used to connect database when it will be doing database operations, i.e., running queries. So our connection string should be pointing to a valid data source so that our application won’t break when we run it.
For that, open web.config and provide the connection string for the database. In config file, you will find under configuration node connectionStrings, you will need to modify the connection string in that node according to your system. In my case, it looks like:
<connectionstrings>
<add connectionstring="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=GridExampleMVC;
Integrated Security=True;MultipleActiveResultSets=true" name="DefaultConnection"
providername="System.Data.SqlClient"/>
</connectionstrings>
Now in controller, add a property for database context that we will be using for querying the database.
01.private ApplicationDbContext _dbContext;
02.
03.public ApplicationDbContext DbContext
04.{
05. get
06. {
07. return _dbContext ?? HttpContext.GetOwinContext().Get<applicationdbcontext>();
08. }
09. private set
10. {
11. _dbContext = value;
12. }
13.}
We will be using this property to query the database with entity framework in all actions of the controller wherever needed.
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>
Jquery Datatables Initialization
We removed the head and body of table because it would get generated by the datatables plug in 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>
We have written datatables initialization code in a function named **"init" **in which we are setting ***"serverSide" ***property to true, which tells that the grid will be server side (paging, filtering and sorting), so now all the records will not be loaded at once, instead the records for the first page will be displayed by default, and more data will be loaded as per user action whatever it is, ***"processing" ***property is to display the loader when data is being retrieved from the action, if someone does not want to display the message while data is being loaded, it can be eliminated and by default, it will be false. Next, we define the action which will be callback for action of the datatable, after that, we specify the columns that are needed to be displayed using **"columns" **property, "lengthMenu" is for the number of records per page for paging stuff. The assetListVM.init(); will get called on page load as it is written in document.ready, yes the $(function () { }); is short form of it.
Installing System.Linq.Dynamic Package
After this, we will write the Get action code in the AssetController. To do so, first we need to reference the System.Linq.Dynamic namespace as we will be using the methods provided for dynamic linq in our action. Go to NuGet Package Manager once again and search for the System.Linq.Dynamic package and install it in your project.
Sorting, Filtering and Paging in Controller
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);
}
We are using Entity Framework for data access but it is not mandatory, you can achieve the same with ADO.NET as well, the only thing you need is to return JSON from the action with instance of DataTableResponse, and datatables will be able to display your data correctly if columns are defined correctly in the script.
We are getting reference to the Assets so that we can Linq to Entities queries for getting the data, and we are getting the total records count of Assets table using Count() as it will be needed to be pass in the constructor of DataTablesResponse which is the last line of the action method.
IQueryable<asset> query = DbContext.Assets;
var totalCount = query.Count();
After that, we have Filtering logic written which will filter data as per user defined criteria, the code is pretty self explanatory which is:
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) );
}
So what we are doing is checking if user has specified any search criteria in text box, then check for all the four columns specified above if any records are found matching, the criteria that will be returned.
After that, we have implemented sorting logic, sorting columns information is posted in the model which we have used using Custom Model binding, and using System.Linq.Dynamic we are able to avoid the ifs and switch statements, we are iterating over the columns on which sorting is applied by user and we are ordering rows according to that:
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);
And at last, we are applying the paging part and checking which page user has selected. By default, the first page gets loaded and after that, we are keeping track of every callback that on which page user is via requestModel.Start and which page user has clicked and requestModel.Length tells how many records per page user wants to see which is also configurable by user using combo box on the page.
Now build the project, and run it in browser to see the working GridView (with server side filtering, paging, and sorting) in action.
Download Source Code
The source code is available at MSDN Gallery here.