Click here to Skip to main content
13,865,831 members
Click here to Skip to main content
Add your own
alternative version

Stats

108.2K views
6K downloads
123 bookmarked
Posted 13 Aug 2016
Licenced CPOL

GridView with Server Side Filtering, Sorting and Paging in ASP.NET MVC 5

, 1 Feb 2018
Rate this:
Please Sign up or sign in to vote.
Beginner's guide to implement server side pagination, sorting and filtering of gridview using jquery datatables

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
    {
        // GET: Asset
        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]],
                    });
                }
            }

            // 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 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

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ehsan Sajjad
Software Developer
Pakistan Pakistan
Ehsan Sajjad is a Microsoft Certified Professional, Microsoft Certified C# Specialist and he is also among the top users on StackOverflow from Pakistan with 50k+ reputation at time of writing this and counting.

He is a passionate software developer with around 5 years of professional experience in Microsoft Technologies both web and desktop applications and always open to learn new things and platforms especially in mobile application development and game development.

Some Achievements :

  • 5th Top Contributor from Pakistan on Stackoverflow.com
  • Top User in ASP.NET MVC from Pakistan on Stackoverflow.com
  • 21st June 2017 - Article of the Day - ASP.NET Community (Beginners Guide on AJAX CRUD Operations in Grid using JQuery DataTables in ASP.NET MVC 5)
  • 19th April 2017 - Article of the Day - ASP.NET Community (ASP.NET MVC Async File Uploading using JQuery)
  • March 2017 - Visual C# Technical Guru Silver Medal on Microsoft Tech Net Wiki Article Competition
  • 20 January 2017 - Article of the Day - ASP.NET Community (Async File Uploading in ASP.NET MVC)
  • 22nd September 2016 - Article of the Day - ASP.NET Community (GridView with Server Side Filtering, Sorting and Paging in ASP.NET MVC 5)
  • 22nd August 2016 - Article of the Day - ASP.NET Community (Beginners Guide for Creating GridView in ASP.NET MVC 5)
  • December 2015 - C-SharpCorner Monthly Winner

You may also be interested in...

Comments and Discussions

 
QuestionI cant download the file and error while creating Controller Pin
Member 1364325420-Feb-18 1:09
memberMember 1364325420-Feb-18 1:09 
GeneralThank you. Pin
vokhanhthuy5-Feb-18 23:44
membervokhanhthuy5-Feb-18 23:44 
QuestionWeb api controller version Pin
developer1419-Jan-18 5:49
memberdeveloper1419-Jan-18 5:49 
AnswerRe: Web api controller version Pin
Ehsan Sajjad19-Jan-18 9:44
mvpEhsan Sajjad19-Jan-18 9:44 
QuestionGreat Tutorial Pin
C_D_M13-Nov-17 0:52
memberC_D_M13-Nov-17 0:52 
AnswerRe: Great Tutorial Pin
Ehsan Sajjad20-Nov-17 9:40
mvpEhsan Sajjad20-Nov-17 9:40 
QuestionGreat contribution to the community thanks Pin
John Perryn21-Jun-17 22:12
memberJohn Perryn21-Jun-17 22:12 
AnswerRe: Great contribution to the community thanks Pin
Ehsan Sajjad22-Jun-17 7:07
mvpEhsan Sajjad22-Jun-17 7:07 
Questionfinally it is working i updated the jquery to 3 it was 1 and i installed the bootsrapcss Pin
Member 28550471-Jun-17 2:18
memberMember 28550471-Jun-17 2:18 
AnswerRe: finally it is working i updated the jquery to 3 it was 1 and i installed the bootsrapcss Pin
Ehsan Sajjad1-Jun-17 3:39
mvpEhsan Sajjad1-Jun-17 3:39 
Questionis it necessary to have a unique identifier in the project Pin
Member 285504725-May-17 11:03
memberMember 285504725-May-17 11:03 
AnswerRe: is it necessary to have a unique identifier in the project Pin
Ehsan Sajjad25-May-17 23:39
mvpEhsan Sajjad25-May-17 23:39 
QuestionDataTables warning: table id=Employes-data-table - Ajax error. Pin
Member 285504725-May-17 1:27
memberMember 285504725-May-17 1:27 
AnswerRe: DataTables warning: table id=Employes-data-table - Ajax error. Pin
Ehsan Sajjad25-May-17 2:11
mvpEhsan Sajjad25-May-17 2:11 
QuestionHow to set time out on search textbox ? Pin
Member 127588786-Apr-17 2:04
memberMember 127588786-Apr-17 2:04 
AnswerRe: How to set time out on search textbox ? Pin
Ehsan Sajjad6-Apr-17 2:30
mvpEhsan Sajjad6-Apr-17 2:30 
GeneralRe: How to set time out on search textbox ? Pin
Member 127588786-Apr-17 3:02
memberMember 127588786-Apr-17 3:02 
GeneralRe: How to set time out on search textbox ? Pin
Ehsan Sajjad6-Apr-17 4:08
mvpEhsan Sajjad6-Apr-17 4:08 
QuestionHow to add action buttons Pin
Iman Memarpour30-Mar-17 9:08
professionalIman Memarpour30-Mar-17 9:08 
AnswerRe: How to add action buttons Pin
Ehsan Sajjad30-Mar-17 9:45
mvpEhsan Sajjad30-Mar-17 9:45 
AnswerRe: How to add action buttons Pin
Ehsan Sajjad14-Jun-17 5:02
mvpEhsan Sajjad14-Jun-17 5:02 
PraiseMy Vote is 5 Star Pin
Member 1031393923-Mar-17 22:25
memberMember 1031393923-Mar-17 22:25 
GeneralRe: My Vote is 5 Star Pin
Ehsan Sajjad23-Mar-17 23:25
mvpEhsan Sajjad23-Mar-17 23:25 
Questionsomething i was looking for..... Pin
Member 1047382923-Mar-17 19:52
memberMember 1047382923-Mar-17 19:52 
AnswerRe: something i was looking for..... Pin
Ehsan Sajjad23-Mar-17 23:25
mvpEhsan Sajjad23-Mar-17 23:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.190214.1 | Last Updated 1 Feb 2018
Article Copyright 2016 by Ehsan Sajjad
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid