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.
We will be modifying the source code from the previous post for this, so let’s get started.
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 webforms. 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.
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:
CREATE DATABASE [GridExampleMVC]
GO
CREATE TABLE [dbo].[Assets] (
[AssetID] UNIQUEIDENTIFIER NOT NULL,
[Barcode] NVARCHAR (MAX) NULL,
[SerialNumber] NVARCHAR (MAX) NULL,
[FacilitySite] NVARCHAR (MAX) NULL,
[PMGuide] NVARCHAR (MAX) NULL,
[AstID] NVARCHAR (MAX) NOT NULL,
[ChildAsset] NVARCHAR (MAX) NULL,
[GeneralAssetDescription] NVARCHAR (MAX) NULL,
[SecondaryAssetDescription] NVARCHAR (MAX) NULL,
[Quantity] INT NOT NULL,
[Manufacturer] NVARCHAR (MAX) NULL,
[ModelNumber] NVARCHAR (MAX) NULL,
[Building] NVARCHAR (MAX) NULL,
[Floor] NVARCHAR (MAX) NULL,
[Corridor] NVARCHAR (MAX) NULL,
[RoomNo] NVARCHAR (MAX) NULL,
[MERNo] NVARCHAR (MAX) NULL,
[EquipSystem] NVARCHAR (MAX) NULL,
[Comments] NVARCHAR (MAX) NULL,
[Issued] BIT NOT NULL,
CONSTRAINT [PK_dbo.Assets] PRIMARY KEY CLUSTERED ([AssetID] ASC)
)
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 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:
using System.ComponentModel.DataAnnotations;
namespace GridExampleMVC.Models
{
public class Asset
{
public System.Guid AssetID { get; set; }
[Display(Name = "Barcode")]
public string Barcode { get; set; }
[Display(Name = "Serial-Number")]
public string SerialNumber { get; set; }
[Display(Name = "Facility-Site")]
public string FacilitySite { get; set; }
[Display(Name = "PM-Guide-ID")]
public string PMGuide { get; set; }
[Required]
[Display(Name = "Asset-ID")]
public string AstID { get; set; }
[Display(Name = "Child-Asset")]
public string ChildAsset { get; set; }
[Display(Name = "General-Asset-Description")]
public string GeneralAssetDescription { get; set; }
[Display(Name = "Secondary-Asset-Description")]
public string SecondaryAssetDescription { get; set; }
public int Quantity { get; set; }
[Display(Name = "Manufacturer")]
public string Manufacturer { get; set; }
[Display(Name = "Model-Number")]
public string ModelNumber { get; set; }
[Display(Name = "Main-Location (Building)")]
public string Building { get; set; }
[Display(Name = "Sub-Location 1 (Floor)")]
public string Floor { get; set; }
[Display(Name = "Sub-Location 2 (Corridor)")]
public string Corridor { get; set; }
[Display(Name = "Sub-Location 3 (Room No)")]
public string RoomNo { get; set; }
[Display(Name = "Sub-Location 4 (MER#)")]
public string MERNo { get; set; }
[Display(Name = "Sub-Location 5 (Equip/System)")]
public string EquipSystem { get; set; }
public string Comments { get; set; }
public bool Issued { get; set; }
}
}
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:
public class ApplicationDbContext : IdentityDbContext<applicationuser>
{
public ApplicationDbContext()
: base("DefaultConnection", throwIfV1Schema: false)
{
}
public DbSet<asset> Assets { get; set; }
public static ApplicationDbContext Create()
{
return new ApplicationDbContext();
}
}
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:
public class AssetController : Controller
{
public ActionResult Index()
{
return View();
}
Installation of Jquery Datatables
Now, we will install jQuery datatables that we will be using to build the gird, 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:
bundles.Add(new ScriptBundle("~/bundles/datatables").Include(
"~/Scripts/DataTables/jquery.dataTables.min.js",
"~/Scripts/DataTables/dataTables.bootstrap.js"));
bundles.Add(new StyleBundle("~/Content/datatables").Include(
"~/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.net Package
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 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:
Configuring Connection String for Database
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.
private ApplicationDbContext _dbContext;
public ApplicationDbContext DbContext
{
get
{
return _dbContext ?? HttpContext.GetOwinContext().Get<applicationdbcontext>();
}
private set
{
_dbContext = value;
}
}
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 Initilization
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]],
});
}
}
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
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
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
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 Entites 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.
What's Next (Advanced Search)
There is another article in this series which talks about how to add Advanced Search in this server side JQuery DataTables processing, as right now, single textbox is being used which is searching through all the columns of the Grid or the columns we specified it to search in, but sometimes we need more robust search by applying different search criterias on each column which this article demonstrates how to do:
You Might Also Like to Read
