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

• Download demo - 6.5 MB

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>
            &hellip;
        </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();
    });
    

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.

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.

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