Click here to Skip to main content
15,118,573 members
Articles / Web Development / HTML
Tip/Trick
Posted 21 Jul 2015

Stats

237.6K views
10.7K downloads
27 bookmarked

Using jQuery DataTables with Server-Side Processing with ASP.NET MVC

Rate me:
Please Sign up or sign in to vote.
4.84/5 (21 votes)
21 Jul 2015MIT3 min read
This article shows how to use jQuery DataTables (version v1.10.7) with server-side processing.

Introduction

DataTables (https://www.datatables.net/) is a jQuery grid plug-in that supports pagination, instant search and sorting. DataTables is easier to use than some of the other grids such as jqGrid. As an example, suppose you have the following DOM elements (borrowed from the DataTables zero configuration example https://www.datatables.net/examples/basic_init/zero_configuration.html)

    <table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
           <th>Salary</th>
         </tr>
    </thead>

    <tfoot>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
        </tr>
    </tfoot>

    <tbody>
        <tr>
            <td>Tiger Nixon</td>
            <td>System Architect</td>
            <td>Edinburgh</td>
            <td>61</td>
            <td>2011/04/25</td>
            <td>$320,800</td>
        </tr>
        <tr>
            <td>Garrett Winters</td>
            <td>Accountant</td>
            <td>Tokyo</td>
            <td>63</td>
            <td>2011/07/25</td>
            <td>$170,750</td>
        </tr></tbody>
</table>

To turn the table above into a grid shown below, all it takes is the following one line of function call

$(document).ready(function(){
    $('#example').DataTable();
});

Image 1

Using Server-Side Processing

The example in the introduction section uses DOM elements as DataTables data source. DataTables can also use Javascript and Ajax data sources. These data sources are processed (paginated, sorted and searched) on the client side. However, if you are working with large databases, you might consider to use DataTables server-side options for performance reasons. With server-side processing, pagination, searching and sorting are done on server-side (using SQL engine or similar technologies). As a result, each draw (e.g.: pagination) of the table will need to make a new Ajax request to get the required data.

You can use NuGet to install DataTables library into your project.

Image 2

Before using DataTables, you need to include jquery-datatables.js and jquery-datatables.css files. Since DataTables needs jQuery library, make sure jquery-*.js file is included also. For example:

<script src="~/Scripts/jquery-1.7.2.js" ></script>
<script src="~/Scripts/DataTables/jquery.dataTables.js" ></script>
<link rel="stylesheet" type="text/css" href="~/Content/DataTables/css/jquery.dataTables.css">

As an example, we will work on a simple grid that displays a list of persons. Each person has a name, age and date of birthday. The grid will support searching and pagination. It will also allow you to sort by Name and DoB but not Age.

Image 3

The HTML shown below is the raw HTML table element, before it is enhanced by DataTables:

<div style="margin:30px;">
    <table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr style="text-align:left;">
                <th>Name</th>
                <th>Age</th>
                <th>DoB</th>
            </tr>
        </thead>

        <tfoot>
            <tr style="text-align:left;">
                <th>Name</th>
                <th>Age</th>
                <th>DoB</th>
            </tr>
        </tfoot>
    </table>
</div>

The Javascript shown below is used to initialise the table:

$(document).ready(function () {
        $('#example').dataTable({
            "processing": true,
            "serverSide": true,
            "info": true,
            "stateSave": true,
            "lengthMenu": [[10, 20, 50, -1], [10, 20, 50, "All"]],
            "ajax":{
                "url": "@string.Format("{0}://{1}{2}", Request.Url.Scheme, Request.Url.Authority, Url.Content("~"))/Home/AjaxGetJsonData",
                "type": "GET"
            },
            "columns": [
                { "data": "Name", "orderable" : true },
                { "data": "Age", "orderable": false },
                { "data": "DoB", "orderable": true }
            ],
            "order": [[0, "asc"]]
        });
    });

The following explains each of the options used:

  • "processing": control the processing indicator while data is being loaded.
  • "serverSide": process data on server-side for performance reasons.
  • "info": control table information display field.
  • "stateSave": restore table state on page reload.
  • "lengthMenu": specify the entries in the length drop down select list that DataTables shows when pagination is enabled. Use the first inner array as the page length values and the second inner array as the displayed options.
  • "ajax": load data for the table content from an Ajax source.
  • "columns": configure each column data source, sortablility and searchability etc.
  • "order": configure column(s) to sort and sort direction initially.

DataTables will use the options to generate a request like the following:

http://localhost:50465//Home/AjaxGetJsonData?draw=1&columns[0][data]=Name&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=true&columns[0][search][value]=&columns[0][search][regex]=false&columns[1][data]=Age&columns[1][name]=&columns[1][searchable]=true&columns[1][orderable]=false&columns[1][search][value]=&columns[1][search][regex]=false&columns[2][data]=DoB&columns[2][name]=&columns[2][searchable]=true&columns[2][orderable]=true&columns[2][search][value]=&columns[2][search][regex]=false&order[0][column]=0&order[0][dir]=asc&start=0&length=10&search[value]=&search[regex]=false&_=1437225574923

The Ajax (JSON) data returned from the server-side would look like this:

{
    "draw":1,
    "recordsTotal":995,
    "recordsFiltered":995,
    "data":[
        {"Name":"Name_00001","Age":"107","DoB":"06/14/1908"},
        {"Name":"Name_00002","Age":"41","DoB":"05/09/1974"},
        …
        {"Name":"Name_00010","Age":"45","DoB":"06/11/1970"}
    ]
}

The following MVC controller method AjaxGetJsonData() will be called for each DataTables draw (i.e. when paging, ordering, searching, etc.) and returns the json data above.

public ActionResult AjaxGetJsonData(int draw, int start, int length)
 {
     string search = Request.QueryString["search[value]"];
     int sortColumn = -1;
     string sortDirection = "asc";
     if (length == -1)
     {
         length = TOTAL_ROWS;
     }

     // note: we only sort one column at a time
     if (Request.QueryString["order[0][column]"] != null)
     {
         sortColumn = int.Parse(Request.QueryString["order[0][column]"]);
     }
     if (Request.QueryString["order[0][dir]"] != null)
     {
         sortDirection = Request.QueryString["order[0][dir]"];
     }

     DataTableData dataTableData = new DataTableData();
     dataTableData.draw = draw;
     dataTableData.recordsTotal = TOTAL_ROWS;
     int recordsFiltered = 0;
     dataTableData.data = FilterData(ref recordsFiltered, start, length, search, sortColumn, sortDirection);
     dataTableData.recordsFiltered = recordsFiltered;

     return Json(dataTableData, JsonRequestBehavior.AllowGet);
}

The function FilterData() is where you would go to databases and fetch data. Since this is just a demo, we will fake the operations as in the following

private const int TOTAL_ROWS = 995;
private static readonly List<DataItem> _data = CreateData();

public class DataItem
{
    public string Name { get; set; }
    public string Age { get; set; }
    public string DoB { get; set; }
}

public class DataTableData
{
    public int draw { get; set; }
    public int recordsTotal { get; set; }
    public int recordsFiltered { get; set; }
    public List<DataItem> data { get; set; }
}

// here we simulate data from a database table.
// !!!!DO NOT DO THIS IN REAL APPLICATION !!!!
private static List<DataItem> CreateData()
{
    Random rnd = new Random();
    List<DataItem> list = new List<DataItem>();
    for (int i = 1; i <= TOTAL_ROWS; i++)
    {
        DataItem item = new DataItem();
        item.Name = "Name_" + i.ToString().PadLeft(5, '0');
        DateTime dob = new DateTime(1900 + rnd.Next(1, 100), rnd.Next(1, 13), rnd.Next(1, 28));
        item.Age = ((DateTime.Now - dob).Days / 365).ToString();
        item.DoB = dob.ToShortDateString();
        list.Add(item);
    }
    return list;
}

private int SortString(string s1, string s2, string sortDirection)
{
    return sortDirection == "asc" ? s1.CompareTo(s2) : s2.CompareTo(s1);
}

private int SortInteger(string s1, string s2, string sortDirection)
{
    int i1 = int.Parse(s1);
    int i2 = int.Parse(s2);
    return sortDirection == "asc" ? i1.CompareTo(i2) : i2.CompareTo(i1);
}

private int SortDateTime(string s1, string s2, string sortDirection)
{
    DateTime d1 = DateTime.Parse(s1);
    DateTime d2 = DateTime.Parse(s2);
    return sortDirection == "asc" ? d1.CompareTo(d2) : d2.CompareTo(d1);
}

// here we simulate SQL search, sorting and paging operations
// !!!! DO NOT DO THIS IN REAL APPLICATION !!!!
private List<DataItem> FilterData(ref int recordFiltered, int start, int length, string search, int sortColumn, string sortDirection)
{
    List<DataItem> list = new List<DataItem>();
    if (search == null)
    {
        list = _data;
    }
    else
    {
        // simulate search
        foreach (DataItem dataItem in _data)
        {
            if (dataItem.Name.ToUpper().Contains(search.ToUpper()) ||
                dataItem.Age.ToString().Contains(search.ToUpper()) ||
                dataItem.DoB.ToString().Contains(search.ToUpper()))
            {
                list.Add(dataItem);
            }
        }
    }

    // simulate sort
    if (sortColumn == 0)
    {// sort Name
        list.Sort((x, y) => SortString(x.Name, y.Name, sortDirection));
    }
    else if (sortColumn == 1)
    {// sort Age
        list.Sort((x, y) => SortInteger(x.Age, y.Age, sortDirection));
    }
    else if (sortColumn == 2)
    {   // sort DoB
        list.Sort((x, y) => SortDateTime(x.DoB, y.DoB, sortDirection));
    }

    recordFiltered = list.Count;

    // get just one page of data
    list = list.GetRange(start, Math.Min(length, list.Count - start));

    return list;
}

Points of Interest

Using DataTables with server-side data processing requires a little more efforts than with client-side processing. However, server-side processing is generally needed for any seriously large data set (say more than 10,000 rows). I hope you find this article useful.

Thanks for reading and happy coding!

History

Last Updated 2015-07-18

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

Junlin Xu
President Computations & Graphics, Inc.
United States United States
Junlin Xu is the founder of Computations & Graphics, Inc. (http://www.cg-inc.com). He is the author of Real3D-Analysis (finite element package), SolverBlaze (finite element SDK), OpenGraph Library (OpenGL-based visualization and charting SDK for native and .NET environment), double128 SDK (quad precision floating point math for C++ and .NET).

Junlin has 19+ years software development experiences in various industries. He has skills in Windows desktop and web application development using C++, C++/CLI, C#, Objective C, Win32 and Win64, MFC, STL, OpenGL,GLSL,COM/COM+, WinForm, MS SQL, MySLQ, ASP.NET, MVC4, CSS, jQuery and jQuery UI, WCF, WPF, HL7, Mirth Connect, Autodesk Revit API, FEM, Inno Setup. He is also an expert in mathematical, CAD and algorithmic software development.

Comments and Discussions

 
Questioni cannot score with 5 because something important is missing Pin
Jaime Stuardo - Chile28-Oct-18 14:05
MemberJaime Stuardo - Chile28-Oct-18 14:05 
You don't tell how to manage variable columns definitions in the parameter. For example, when Ajax call is made, this is actually received by the controller in my case:

Request.Form.AllKeys
{string[49]}
    [0]: "draw"
    [1]: "columns[0][data]"
    [2]: "columns[0][name]"
    [3]: "columns[0][searchable]"
    [4]: "columns[0][orderable]"
    [5]: "columns[0][search][value]"
    [6]: "columns[0][search][regex]"
    [7]: "columns[1][data]"
    [8]: "columns[1][name]"
    [9]: "columns[1][searchable]"
    [10]: "columns[1][orderable]"
    [11]: "columns[1][search][value]"
    [12]: "columns[1][search][regex]"
    [13]: "columns[2][data]"
    [14]: "columns[2][name]"
    [15]: "columns[2][searchable]"
    [16]: "columns[2][orderable]"
    [17]: "columns[2][search][value]"
    [18]: "columns[2][search][regex]"
    [19]: "columns[3][data]"
    [20]: "columns[3][name]"
    [21]: "columns[3][searchable]"
    [22]: "columns[3][orderable]"
    [23]: "columns[3][search][value]"
    [24]: "columns[3][search][regex]"
    [25]: "columns[4][data]"
    [26]: "columns[4][name]"
    [27]: "columns[4][searchable]"
    [28]: "columns[4][orderable]"
    [29]: "columns[4][search][value]"
    [30]: "columns[4][search][regex]"
    [31]: "columns[5][data]"
    [32]: "columns[5][name]"
    [33]: "columns[5][searchable]"
    [34]: "columns[5][orderable]"
    [35]: "columns[5][search][value]"
    [36]: "columns[5][search][regex]"
    [37]: "columns[6][data]"
    [38]: "columns[6][name]"
    [39]: "columns[6][searchable]"
    [40]: "columns[6][orderable]"
    [41]: "columns[6][search][value]"
    [42]: "columns[6][search][regex]"
    [43]: "order[0][column]"
    [44]: "order[0][dir]"
    [45]: "start"
    [46]: "length"
    [47]: "search[value]"
    [48]: "search[regex]"


So, how to retrieve the "columns" array. The same when sorting for multiple columns. "order[0]" is just for the first column, but what to do for multiple columns? obviously, one cannot do what you hard coded, because we cannot know how many "order" items we will have.

Regards
Jaime
Questionmanage columns in datatable Pin
Member 1384006127-Jun-18 19:51
MemberMember 1384006127-Jun-18 19:51 
QuestionSorting is not working if more than 8 columns Pin
Member 121704444-May-17 1:56
MemberMember 121704444-May-17 1:56 
AnswerRe: Sorting is not working if more than 8 columns Pin
yagzstillfree25-May-17 5:26
Memberyagzstillfree25-May-17 5:26 
Questionsorting issue Pin
Member 121704443-May-17 20:33
MemberMember 121704443-May-17 20:33 
QuestionSorting and Search is not working Pin
Member 121704442-May-17 3:49
MemberMember 121704442-May-17 3:49 
QuestionDatatable service side processing Pin
Member 366671419-Jul-16 21:42
MemberMember 366671419-Jul-16 21:42 
QuestionLarge Data - SQL Server Pin
Rajeev Raina6-Jul-16 3:16
MemberRajeev Raina6-Jul-16 3:16 
AnswerRe: Large Data - SQL Server Pin
Junlin Xu6-Jul-16 17:33
MemberJunlin Xu6-Jul-16 17:33 
GeneralRe: Large Data - SQL Server Pin
Rajeev Raina6-Jul-16 20:20
MemberRajeev Raina6-Jul-16 20:20 
QuestionNot Generic Pin
lmcgs17-Jun-16 3:57
Memberlmcgs17-Jun-16 3:57 
AnswerRe: Not Generic Pin
Junlin Xu6-Jul-16 17:34
MemberJunlin Xu6-Jul-16 17:34 
GeneralMy vote of 5 Pin
Member 1097428914-May-16 17:42
MemberMember 1097428914-May-16 17:42 
QuestionDynamic Column headers with server side processing Pin
Member 1239654316-Mar-16 6:37
MemberMember 1239654316-Mar-16 6:37 
GeneralMy vote of 5 Pin
Vishal Avhad1-Jan-16 7:33
MemberVishal Avhad1-Jan-16 7:33 
GeneralRe: My vote of 5 Pin
Junlin Xu29-Jan-16 12:17
MemberJunlin Xu29-Jan-16 12:17 
Questionprevious and next button issue Pin
sreeramajagadeesh21-Aug-15 0:55
Membersreeramajagadeesh21-Aug-15 0:55 
Questionsearch & sort doesn't work Pin
Driss Lahjouji5-Aug-15 9:16
MemberDriss Lahjouji5-Aug-15 9:16 
AnswerRe: search & sort doesn't work Pin
Junlin Xu4-Jan-16 3:20
MemberJunlin Xu4-Jan-16 3:20 
GeneralMy vote of 5 Pin
Sebastiaan Lubbers22-Jul-15 0:16
professionalSebastiaan Lubbers22-Jul-15 0:16 
GeneralRe: My vote of 5 Pin
Junlin Xu22-Jul-15 6:24
MemberJunlin Xu22-Jul-15 6:24 

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.