ASP.NET MVC 5 Implementing GridView Paging

Keywords: ASP.NET JQuery SQL Database Stored Procedure

This paper implements a GridView with ASP.NET MVC 5. Its functions include paging, filtering, sorting, asynchronous execution of AJAX, and also support Responsive Web Design in appearance. The execution screen is shown in Fig. 1 below.

ASP.NET MVC implements GridView and paging in many ways. This article refers to several articles of Code Project [1]. Before reading, we should establish an idea. Paging has two kinds: front-end and back-end. We should avoid confusing them as follows:
Front-end: refers to "user interface", such as: page number column, the previous page, the next page. Wait for the button.
2. Backend: refers to the way of "fishing database", such as: using the ROW_NUMBER function of SQL Server or the T-SQL grammar OFFSET-FETCH, to go to the database to fishing, and the information to be presented on the screen.

The third-party components used in this article are:
1. jQuery datatables: Used to handle the above "front-end" paging, i.e. to present the appearance of Grid tables, user interfaces, and user interaction (in addition to jQuery, through bootstrap, CSS and a Flash animation file).
2. datatables.mvc5: Used to process the aforementioned "back-end" paging, that is, to retrieve databases. It also avoids the need for Web Forms to write and process paging SQL statements or Stored Procedure, as in the past. In addition, this component also supports the strong typed model of the Controller layer.

The sample downloads of this article are as follows:
The example uses Visual Studio 2015 (as long as it can support ASP.NET MVC 5), and requires the orthwind database of SQL Server.

Fig. 1 example execution screen

First, in VS 2015, add a new Web "project" and check the MVC template.

Then, in the Models layer, add an empty C# category Order.cs (this article is based on the Orders table as an example), and manually join, want to show in the GridView, each field get/set accessor, as follows equation code:

using System.ComponentModel.DataAnnotations;

namespace NorthwindPaging.Models
    public class Order
        public int OrderID { get; set; }

        [Display(Name = "Client ID")]
        public string CustomerID { get; set; }

        [Display(Name = "Staff ID")]
        public decimal EmployeeID { get; set; }

        [Display(Name = "Transport country")]
        public string ShipCountry { get; set; }

        [Display(Name = "Transportation costs")]
        public decimal Freight { get; set; }

Then open the file Models / Identity Models. cs, which contains the relevant functions of ASP.NET identity 2.0. We add the following property program for the Order table:

public DbSet<Order> Orders { get; set; }    //Custom Category Order

Then at the Controller layer, a new blank "controller" named OrderController is added.

Note: This example uses the concept of Entity Framework, DbSet, DbContext and other objects and binding functions, but does not use. edmx entity model files.


Install jQuery datatables

Pretend to install jQuery datatables through NuGet, as shown in Figure 2 below.

Figure 2 Installation of jQuery datatables

After installation, a DataTables folder and related. css,. swf,. js files will be generated automatically under the Content and Scripts folders of the project.

Next we need to help jQuery datatables register manually. In App_Start/BundleConfig.cs, add the following two pieces of code:

// jquery datataables js files
bundles.Add(new ScriptBundle("~/bundles/datatables").Include(

// jquery datatables css file
bundles.Add(new StyleBundle("~/Content/datatables").Include(

Then in Views/Shared/_Layout.cshtml, add the following two lines of code:



Install datatables.mvc5

Pretend to install datatables.mvc5 through NuGet, as shown in Figure 3 below.

Figure 3 Installs datatables.mvc5

Once installed, there will be an additional reference to DataTables.Mvc.dll in the project.

Setting up a database connection

First, in the Default Connection of Web.config database connection string, manually add the Northwind connection settings.

Next, add the following code to Controllers/OrderController.cs to obtain database validation and connectivity for subsequent Controller Action methods.

private ApplicationDbContext _dbContext;

public ApplicationDbContext DbContext
        return _dbContext ?? HttpContext.GetOwinContext().Get<ApplicationDbContext>();
    private set
        _dbContext = value;


Initialize jQuery datatables

In the Views/Order folder, add a blank "View" page named OrderGridView (or any name). Then empty all the contents of OrderGridView.cshtml (jQuery datatables automatically generate tags such as head, body, etc.), and manually add the following HTML tag s:

<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">Orders Data sheet</h1>
            <div class="panel-body">
                <table id="orders-data-table" class="table table-striped table-bordered" style="width:100%;"></table>

Then continue to add the following program manually in OrderGridView.cshtml to retrieve the image from the database by server-side in AJAX mode. The information to be presented on this page is as follows:

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

                init: function () {
                    dt = $('#orders-data-table').DataTable({
                        "serverSide": true,
                        "processing": true,
                        "ajax": {
                            "url": "@Url.Action("Get","Order")"
                        "columns": [
                            { "title": "Order ID", "data": "OrderID", "searchable": true },
                            { "title": "Customer ID", "data": "CustomerID", "searchable": true },
                            { "title": "Employee ID", "data": "EmployeeID", "searchable": true },
                            { "title": "Ship Country", "data": "ShipCountry", "searchable": true },
                            { "title": "Freight", "data": "Freight", "searchable": true }
                        "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],

            // initialize the datatables


In the above code, serverSide": true, means that paging, filtering, sorting should be obtained from server-side according to the user's every operation, rather than pulling out all the data that meet the requirements of SELECT WHERE at one time and then being processed by client-side (if the amount of data is too large, it will bring down the efficiency).
processing": true, refers to whether to display on the screen when accessing the database, processing effects and words when loading. If not, the default is false. lengthMenu represents the number of data pens to be displayed on each page. This example allows users to choose using combo box.


Install System.Linq.Dynamic

This example sorting uses the syntax of Dynamic LINQ (not necessary, just to simplify sorting code), which requires additional installation. We install Dynamic LINQ through NuGet, as shown in Figure 4 below.

Figure 4 Installation of Dynamic LINQ


Writing sorting, filtering, paging functions in Controller

Next, write the Get Action method in OrderController, with the following code:

public ActionResult Get([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
    IQueryable<Order> query = DbContext.Orders; //LINQ to Entites
    var totalCount = query.Count();     //Order All 830 tables

    #region Filtering (User input keyword search)
    // Apply filters for searching (DataTables.Mvc.Search Category)
    if (requestModel.Search.Value != string.Empty)
        var value = requestModel.Search.Value.Trim();

        //filtering function (Columns must be string,Hurdles int,decimal Presupposition does not work, so add ToString())
        query = query.Where(o => o.OrderID.ToString().Contains(value) ||
                                 o.CustomerID.Contains(value) ||
                                 o.EmployeeID.ToString().Contains(value) || 
                                 o.ShipCountry.Contains(value) ||

    var filteredCount = query.Count();  //All 830 pens

    #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");
    //Dynamic LINQ
    query = query.OrderBy(orderByString == string.Empty ? "OrderID asc" : orderByString);    //Presupposed Sorting Column and Sorting Method

    #endregion Sorting

    #region Paging
    query = query.Skip(requestModel.Start).Take(requestModel.Length);   //Skip:Start index,Take:Number of pens to be salvaged(Users can choose from the drop-down menu)

    int i1 = query.Count(); //Already used SQL Server Of OFFSET-FETCH or ROW_NUMBER(),Filter out 10 strokes of the page on the screen

    //use Select() Reassemble the information we need
    var data = query.Select(order => new
        OrderID = order.OrderID, 
        CustomerID = order.CustomerID,
        EmployeeID = order.EmployeeID, 
        ShipCountry = order.ShipCountry, 
        Freight = order.Freight

    #endregion Paging

    int i2 = query.Count(); //10
    int i3 = data.Count();  //10
    int i4 = requestModel.Draw;     //1,2,...(Non-Page Number)
    int i5 = requestModel.Start;    //Start index,Page 1 is 0, Page 2 is 10, Page 4 is 30,...
    int i6 = requestModel.Length;   //10. tells how many records per page user wants to see which is also configurable by user using combo box
    int i7 = filteredCount; // 830 Pen
    int i8 = totalCount;    // 830 Pen

    //Parametric 1:datatables.mvc5 Custom Categories DataTablesResponse, Parametric 2:Allow HTTP GET access
    return Json(new DataTablesResponse(requestModel.Draw, data, filteredCount, totalCount), JsonRequestBehavior.AllowGet);
ActionResult Get()

This custom Get method returns Json Result. The first parameter is the custom category DataTablesResponse of the third-party component datatables.mvc5. If we write the corresponding columns correctly in the front-end OrderGridView.cshtml, the GridView will display the data normally. Because jQuery datatables support Bootstrap, the GridView also supports RWD (Responsive Web Page) in appearance to automatically zoom when viewed by mobile devices.

Culture in GridView Messages

The text message in jQuery datatables is in English. If you want to change it into Chinese, you can open the file of Scripts DataTables jquery. dataTables. min. JS by yourself and modify it manually. The result is as shown in the numbered column at the bottom left of Figure 1.

Backend Paging T-SQL Syntax

If we open the SQL Profiler and observe the T-SQL statements executed when changing pages on the screen, we will find that LINQ has dealt with the problem of "paging" when retrieving data from the database, which can avoid writing and processing paging's SQL statements or Stored Procedure [4] by ourselves in the past WebForm era.

The database version used for editors'test is SQL Server 2016, so "paging" automatically refers to the OFFSET-FETCH grammar. The following T-SQL statements, respectively, are the grammar of the first page and the fourth page of Grid View on the screen. The starting index is 0 and 30 respectively, indicating that 10 pieces of data should be retrieved from the 0th and 30th.

    [Extent1].[OrderID] AS [OrderID], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[ShipCountry] AS [ShipCountry], [Extent1].[Freight] AS [Freight]
    FROM [dbo].[Orders] AS [Extent1] ORDER BY [Extent1].[OrderID] ASC
    [Extent1].[OrderID] AS [OrderID], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[ShipCountry] AS [ShipCountry], [Extent1].[Freight] AS [Freight]
    FROM [dbo].[Orders] AS [Extent1] ORDER BY [Extent1].[OrderID] ASC



Reference Articles

[1] GridView with Server Side Filtering, Sorting and Paging in ASP.NET MVC 5 (server-side)

[2] Grid with Server Side Advanced Search using JQuery DataTables in ASP.NET MVC 5 (server-side)

[3] Beginners Guide for Creating GridView in ASP.NET MVC 5 (client-side)

Related articles

[4] ASP.NET Data Paging Part 1 - Discussing Paging Principle and ROW_NUMBER Function of SQL Server 2005

[5] ASP.NET Data Paging Part 2 - Example Download

Posted by phaseonemedia on Mon, 15 Jul 2019 12:00:53 -0700