ASP.NET Webform - Datatables JQuery plugin Server Side Integration

Datatables is a quite popular JQuery based plugin. There are a lot of flavors of Datatables plugins and it supports many major web programming technologies.

In this article, I shall be focusing more on how Datatables plugin can be integrated with classic ASP.NET webform server side. If anyone is interested to integrate this beautiful plugin with ASP.NET MVC5 server then here is the link.

output

Following are some prerequisites before you proceed any further in this tutorial,

Prerequisites:

The prerequisites include knowledge about the following technologies: 
  1. Classic ASP.NET webform.
  2. HTML.
  3. Javascript.
  4. AJAX.
  5. CSS.
  6. Bootstrap.
  7. C# programming.
  8. C# LINQ.
  9. JQuery.
  10.  WebMethod attribute

You can download the complete source code for this tutorial from here or you can follow the step by step discussion below. The sample code is developed in Microsoft Visual Studio 2015 Enterprise. I am using SalesOrderDetail table extract from Adventure Works Sample Database.

Let's begin now.

  1. Create new Webform web application project and name it "JqDatatablesWebfoms".
  2. Since the project is created with ".Net 4.5.2" framework therefore you will see its project hierarchy a bit similar to MVC5 hierarchy. If that's the case then you will see "RouteConfig.cs" file, open it and replace it with following code,
    1. public static void RegisterRoutes(RouteCollection routes)    
    2. {    
    3.   var settings = new FriendlyUrlSettings();    
    4.   settings.AutoRedirectMode = RedirectMode.Off; // RedirectMode.Permanent    
    5.   routes.EnableFriendlyUrls(settings);    
    6. }   
    In above code, I have changed "RedirectMode.Permanent" to "RedirectMode.Off". The reason is that since, I am going to use ajax call in datatables plugin, so, if redirect mode in friendly URL is not off then my ajax call will redirect to root page and ajax call to serverside will not work.

  3. Now, open "Site.Master" replace following code in it,
    1. <%@ Master Language="C#" AutoEventWireup="true" CodeBehind="Site.master.cs" Inherits="JqDatatablesWebForm.SiteMaster" %>    
    2. <!DOCTYPE html>    
    3. <html lang="en">    
    4. <head runat="server">    
    5.   <meta charset="utf-8" />    
    6.   <meta name="viewport" content="width=device-width, initial-scale=1.0" />    
    7.   <title><%: Page.Title %></title>    
    8.   <asp:PlaceHolder runat="server">    
    9.     <%: Scripts.Render("~/bundles/modernizr") %>    
    10.     <%: Scripts.Render("~/bundles/jquery") %>    
    11.   </asp:PlaceHolder>    
    12.   <webopt:bundlereference runat="server" path="~/Content/css" />    
    13.   <link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" />    
    14.   <!-- Font Awesome -->    
    15.   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.4.0/css/font-awesome.min.css" />    
    16.   <!-- Data table -->    
    17.   <link rel="stylesheet" href="https://cdn.datatables.net/1.10.10/css/dataTables.bootstrap.min.css " />    
    18.   <link rel="stylesheet" href="Content/style/custom-style.css" />    
    19.   <!-- Data Table -->    
    20.   <script src="https://cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js" type="text/javascript"></script>    
    21.   <script src="https://cdn.datatables.net/1.10.10/js/dataTables.bootstrap.min.js" type="text/javascript"></script>    
    22.   <script src="Scripts/custom-datatable.js" type="text/javascript"></script>    
    23. </head>    
    24. <body>    
    25.   <form runat="server">    
    26.     <asp:ScriptManager runat="server">    
    27.       <Scripts>    
    28.         <%--To learn more about bundling scripts in ScriptManager see http://go.microsoft.com/fwlink/?LinkID=301884 --%>    
    29.         <%--Framework Scripts--%>    
    30.         <asp:ScriptReference Name="MsAjaxBundle" />    
    31. <%--        <asp:ScriptReference Name="jquery" />--%>    
    32.         <asp:ScriptReference Name="bootstrap" />    
    33.         <asp:ScriptReference Name="respond" />    
    34.         <asp:ScriptReference Name="WebForms.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebForms.js" />    
    35.         <asp:ScriptReference Name="WebUIValidation.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebUIValidation.js" />    
    36.         <asp:ScriptReference Name="MenuStandards.js" Assembly="System.Web" Path="~/Scripts/WebForms/MenuStandards.js" />    
    37.         <asp:ScriptReference Name="GridView.js" Assembly="System.Web" Path="~/Scripts/WebForms/GridView.js" />    
    38.         <asp:ScriptReference Name="DetailsView.js" Assembly="System.Web" Path="~/Scripts/WebForms/DetailsView.js" />    
    39.         <asp:ScriptReference Name="TreeView.js" Assembly="System.Web" Path="~/Scripts/WebForms/TreeView.js" />    
    40.         <asp:ScriptReference Name="WebParts.js" Assembly="System.Web" Path="~/Scripts/WebForms/WebParts.js" />    
    41.         <asp:ScriptReference Name="Focus.js" Assembly="System.Web" Path="~/Scripts/WebForms/Focus.js" />    
    42.         <asp:ScriptReference Name="WebFormsBundle" />    
    43.         <%--Site Scripts--%>    
    44.       </Scripts>    
    45.     </asp:ScriptManager>    
    46.   <div class="navbar navbar-inverse navbar-fixed-top">    
    47.     <div class="container">    
    48.       <div class="navbar-header">    
    49.         <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">    
    50.           <span class="icon-bar"></span>    
    51.           <span class="icon-bar"></span>    
    52.           <span class="icon-bar"></span>    
    53.         </button>    
    54.       </div>    
    55.     </div>    
    56.   </div>    
    57.     <div class="container body-content">    
    58.       <asp:ContentPlaceHolder ID="MainContent" runat="server">    
    59.       </asp:ContentPlaceHolder>    
    60.       <hr />    
    61.       <footer>    
    62.         <center>    
    63.           <p><strong>Copyright © <%: DateTime.Now.Year %> - <a href="http://asmak9.blogspot.com/">Asma's Blog</a>.</strong> All rights reserved.</p>    
    64.         </center>    
    65.       </footer>    
    66.     </div>    
    67.   </form>    
    68. </body>    
    69. </html>    
    Here, I have simply altered the existing layout and incorporated links to require scripts and styles.

  4. Now, open "Default.aspx" page and replace the following code in it,
    1. <%@ Page Title="ASP.NET Webform: Datatables Jquery Plugin Integration" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="JqDatatablesWebForm._Default" %>    
    2. <asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">    
    3.   <div class="row">    
    4.     <div class="panel-heading">    
    5.       <div class="col-md-8 custom-heading3">    
    6.         <h3>    
    7.           <i class="fa fa-table"></i>    
    8.           <span>Datatables Jquery Plugin Integration with ASP.NET Webform C#</span>    
    9.         </h3>    
    10.       </div>    
    11.     </div>    
    12.   </div>    
    13.   <div class="row">    
    14.     <section class="col-md-12 col-md-push-0">    
    15.       <section>    
    16.         <div class="well bs-component">    
    17.           <br />    
    18.           <div class="row">    
    19.             <div>    
    20.               <table class="table table-striped table-bordered table-hover"    
    21.                   id="TableId"    
    22.                   cellspacing="0"    
    23.                   align="center"    
    24.                   width="100%">    
    25.                 <thead>    
    26.                   <tr>    
    27.                     <th>Sr</th>    
    28.                     <th>Order Track Number</th>    
    29.                     <th>Quantity</th>    
    30.                     <th>Product Name</th>    
    31.                     <th>Special Offer</th>    
    32.                     <th>Unit Price</th>    
    33.                     <th>Unit Price Discount</th>    
    34.                   </tr>    
    35.                 </thead>    
    36.               </table>    
    37.             </div>    
    38.           </div>    
    39.         </div>    
    40.       </section>    
    41.     </section>    
    42.   </div>    
    43. </asp:Content>   
    Here, I have created a table holder that will be integrated with Datatables plugin with data from server side. I have only provided table header information here, since the data will be integrated from server side.

  5. Now create two new models under "Model", name it "SalesOrderDetail.cs" & "DataTables.cs" and add following properties in them,
    1. using System;    
    2. using System.Collections.Generic;    
    3. using System.Linq;    
    4. using System.Text;    
    5. using System.Threading.Tasks;    
    6. namespace PluginIntegration_1.Models    
    7. {    
    8.   public class SalesOrderDetail    
    9.   {    
    10.     public int Sr { get; set; }    
    11.     public string OrderTrackNumber { get; set; }    
    12.     public int Quantity { get; set; }    
    13.     public string ProductName { get; set; }    
    14.     public string SpecialOffer { get; set; }    
    15.     public double UnitPrice { get; set; }    
    16.     public double UnitPriceDiscount { get; set; }    
    17.   }    
    18. }    
    19.   
    20. using System;    
    21. using System.Collections.Generic;    
    22. using System.Linq;    
    23. using System.Text;    
    24. using System.Threading.Tasks;    
    25. namespace JqDatatablesWebForm.Models    
    26. {    
    27.   public class DataTables    
    28.   {    
    29.     public int draw { get; set; }    
    30.     public int recordsTotal { get; set; }    
    31.     public int recordsFiltered { get; set; }    
    32.     public List<SalesOrderDetail> data { get; set; }    
    33.   }    
    34. }  
  6. Now, In "Default.aspx.cs" file add following function to load data from "SalesOrderDetail.txt" text file,
    1. #region Load Data    
    2. /// <summary>    
    3. /// Load data method.    
    4. /// </summary>    
    5. /// <returns>Returns - Data</returns>    
    6. private List<SalesOrderDetail> LoadData()    
    7. {    
    8.   // Initialization.    
    9.   List<SalesOrderDetail> lst = new List<SalesOrderDetail>();    
    10.   try    
    11.   {    
    12.     // Initialization.    
    13.     string line = string.Empty;    
    14.     string srcFilePath = "content/files/SalesOrderDetail.txt";    
    15.     var rootPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);    
    16.     var fullPath = Path.Combine(rootPath, srcFilePath);    
    17.     string filePath = new Uri(fullPath).LocalPath;    
    18.     StreamReader sr = new StreamReader(new FileStream(filePath, FileMode.Open, FileAccess.Read));    
    19.     // Read file.    
    20.     while ((line = sr.ReadLine()) != null)    
    21.     {    
    22.       // Initialization.    
    23.       SalesOrderDetail infoObj = new SalesOrderDetail();    
    24.       string[] info = line.Split(',');    
    25.       // Setting.    
    26.       infoObj.Sr = Convert.ToInt32(info[0].ToString());    
    27.       infoObj.OrderTrackNumber = info[1].ToString();    
    28.       infoObj.Quantity = Convert.ToInt32(info[2].ToString());    
    29.       infoObj.ProductName = info[3].ToString();    
    30.       infoObj.SpecialOffer = info[4].ToString();    
    31.       infoObj.UnitPrice = Convert.ToDouble(info[5].ToString());    
    32.       infoObj.UnitPriceDiscount = Convert.ToDouble(info[6].ToString());    
    33.       // Adding.    
    34.       lst.Add(infoObj);    
    35.     }    
    36.     // Closing.    
    37.     sr.Dispose();    
    38.     sr.Close();    
    39.   }    
    40.   catch (Exception ex)    
    41.   {     
    42.     // info.    
    43.     Console.Write(ex);    
    44.   }    
    45.   // info.    
    46.   return lst;    
    47. }   
    The above piece of code simply loads data from text file into list.

  7. Now, create new script file under "Scripts" folder, name it "custom-datatable.js" and place following code in it,
    1. $(document).ready(function ()    
    2. {    
    3.   $('#TableId').DataTable(    
    4.   {    
    5.     "columnDefs": [    
    6.       { "width""5%""targets": [0] },    
    7.       { "className""text-center custom-middle-align""targets": [0, 1, 2, 3, 4, 5, 6] },    
    8.     ],    
    9.     "language":    
    10.       {    
    11.         "processing""<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"    
    12.       },    
    13.     "processing"true,    
    14.     "serverSide"true,    
    15.     "ajax":    
    16.       {    
    17.         "url""Default.aspx/GetData",    
    18.         "contentType""application/json",    
    19.         "type""GET",    
    20.         "dataType""JSON",    
    21.         "data"function (d)    
    22.         {    
    23.           return d;    
    24.         },    
    25.         "dataSrc"function (json)    
    26.         {    
    27.           json.draw = json.d.draw;    
    28.           json.recordsTotal = json.d.recordsTotal;    
    29.           json.recordsFiltered = json.d.recordsFiltered;    
    30.           json.data = json.d.data;    
    31.           var return_data = json;    
    32.           return return_data.data;    
    33.         }    
    34.       },    
    35.     "columns": [    
    36.           { "data""Sr" },    
    37.           { "data""OrderTrackNumber" },    
    38.           { "data""Quantity" },    
    39.           { "data""ProductName" },    
    40.           { "data""SpecialOffer" },    
    41.           { "data""UnitPrice" },    
    42.           { "data""UnitPriceDiscount" }    
    43.     ]    
    44.   });    
    45. });   
    Now, this is the fun part which will display the server side data in the table that we have created earlier into our "Default.aspx" page. This is how Datatables plugin integrates server side data with underlying web programming language. Let's see each information here chunk by chunk,
    1. "columnDefs": [    
    2.   { "width""5%""targets": [0] },    
    3.   { "className""text-center custom-middle-align""targets": [0, 1, 2, 3, 4, 5, 6] },    
    4. ],  
    The above chunk of code provides styling, enable/disable information for sorting, searching etc, for number of columns which are being used in the table, which is why this chunk of code defines columns definition for our table.
    1. "language":    
    2.   {    
    3.     "processing""<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"    
    4.   },   
    The above chunk of code customizes the processing message that will appear when data is being loaded. I have used following custom styling here,
    1. .custom-loader-color     
    2. {    
    3.   color: #fff !important;    
    4.   font-size: 50px !important;    
    5. }    
    6. .custom-loader-background    
    7. {    
    8.   background-color: crimson !important;    
    9. }    
    10. .custom-middle-align     
    11. {    
    12.   vertical-align: middle !important;    
    13. }    
    Below is the snippet of how the processing loader will look,

    output

    Below piece of code will enable the data loading from server side,
    1. "processing"true,    
    2. "serverSide"true,    
    3. "ajax":    
    4.   {    
    5.     "url""Default.aspx/GetData",    
    6.     "contentType""application/json",    
    7.     "type""GET",    
    8.     "dataType""JSON",    
    9.     "data"function (d)    
    10.     {    
    11.       return d;    
    12.     },    
    13.     "dataSrc"function (json)    
    14.     {    
    15.       json.draw = json.d.draw;    
    16.       json.recordsTotal = json.d.recordsTotal;    
    17.       json.recordsFiltered = json.d.recordsFiltered;    
    18.       json.data = json.d.data;    
    19.       var return_data = json;    
    20.       return return_data.data;    
    21.     }    
    22.   },    
    23. "columns": [    
    24.       { "data""Sr" },    
    25.       { "data""OrderTrackNumber" },    
    26.       { "data""Quantity" },    
    27.       { "data""ProductName" },    
    28.       { "data""SpecialOffer" },    
    29.       { "data""UnitPrice" },    
    30.       { "data""UnitPriceDiscount" }    
    31. ]  
    The columns here are the exact name of the properties that we have created in "SalesOrderDetail.cs" file and the path "Default.aspx/GetData" is the function that will be returning data from the server side. "dataSrc" property will format data from server side and package it into format that is acceptable by DataTables plugin.

  8. Now, in "Default.aspx.cs" file let's create "GetData" method as follow,
    1. #region Get data method.    
    2. /// <summary>    
    3. /// GET: Default.aspx/GetData    
    4. /// </summary>    
    5. /// <returns>Return data</returns>    
    6. [WebMethod]    
    7. [ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]    
    8. public static object GetData()    
    9. {    
    10.   // Initialization.    
    11.   DataTables result = new DataTables();    
    12.   try    
    13.   {    
    14.     // Initialization.    
    15.     string search = HttpContext.Current.Request.Params["search[value]"];    
    16.     string draw = HttpContext.Current.Request.Params["draw"];    
    17.     string order = HttpContext.Current.Request.Params["order[0][column]"];    
    18.     string orderDir = HttpContext.Current.Request.Params["order[0][dir]"];    
    19.     int startRec = Convert.ToInt32(HttpContext.Current.Request.Params["start"]);    
    20.     int pageSize = Convert.ToInt32(HttpContext.Current.Request.Params["length"]);    
    21.     // Loading.    
    22.     List<SalesOrderDetail> data = _Default.LoadData();    
    23.     // Total record count.    
    24.     int totalRecords = data.Count;    
    25.     // Verification.    
    26.     if (!string.IsNullOrEmpty(search) &&    
    27.       !string.IsNullOrWhiteSpace(search))    
    28.     {    
    29.       // Apply search    
    30.       data = data.Where(p => p.Sr.ToString().ToLower().Contains(search.ToLower()) ||    
    31.                   p.OrderTrackNumber.ToLower().Contains(search.ToLower()) ||    
    32.                   p.Quantity.ToString().ToLower().Contains(search.ToLower()) ||    
    33.                   p.ProductName.ToLower().Contains(search.ToLower()) ||    
    34.                   p.SpecialOffer.ToLower().Contains(search.ToLower()) ||    
    35.                   p.UnitPrice.ToString().ToLower().Contains(search.ToLower()) ||    
    36.                   p.UnitPriceDiscount.ToString().ToLower().Contains(search.ToLower())).ToList();    
    37.     }    
    38.     // Sorting.    
    39.     data = _Default.SortByColumnWithOrder(order, orderDir, data);    
    40.     // Filter record count.    
    41.     int recFilter = data.Count;    
    42.     // Apply pagination.    
    43.     data = data.Skip(startRec).Take(pageSize).ToList();    
    44.     // Loading drop down lists.    
    45.     result.draw = Convert.ToInt32(draw);    
    46.     result.recordsTotal = totalRecords;    
    47.     result.recordsFiltered = recFilter;    
    48.     result.data = data;    
    49.   }    
    50.   catch (Exception ex)    
    51.   {    
    52.     // Info    
    53.     Console.Write(ex);    
    54.   }    
    55.   // Return info.    
    56.   return result;    
    57. }    
    58. #endregion    

In above piece of code, we have created a WebMethod which is based on searching, sorting and pagination information sent from Datatebles plugin, following have been done i.e.

  1. Data is being loaded first.
  2. Data is being churned out base on searching criteria.
  3. Data is sorted by provided column in provided order.
  4. Data is then paginated.
  5. Data is returned.

"GetData" function will be executed each time the table is being searched, sorted or new page is accessed. Here are the  following two lines which are important,

  1. // Total record count.   
  2. int totalRecords = data.Count;   
  3.   
  4. // Filter record count.   
  5. int recFilter = data.Count;  
First line determines the actual amount of records that exist in the list and second line determines the amount of records that are left after applying filtering. Below is the piece of code that will do the sorting,
  1. #region Sort by column with order method    
  2. /// <summary>    
  3. /// Sort by column with order method.    
  4. /// </summary>    
  5. /// <param name="order">Order parameter</param>    
  6. /// <param name="orderDir">Order direction parameter</param>    
  7. /// <param name="data">Data parameter</param>    
  8. /// <returns>Returns - Data</returns>    
  9. private List<SalesOrderDetail> SortByColumnWithOrder(string order, string orderDir, List<SalesOrderDetail> data)    
  10. {    
  11.   // Initialization.    
  12.   List<SalesOrderDetail> lst = new List<SalesOrderDetail>();    
  13.   try    
  14.   {    
  15.     // Sorting    
  16.     switch (order)    
  17.     {    
  18.       case "0":    
  19.         // Setting.    
  20.         lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Sr).ToList()    
  21.                                              : data.OrderBy(p => p.Sr).ToList();    
  22.         break;    
  23.       case "1":    
  24.         // Setting.    
  25.         lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.OrderTrackNumber).ToList()    
  26.                                              : data.OrderBy(p => p.OrderTrackNumber).ToList();    
  27.         break;    
  28.       case "2":    
  29.         // Setting.    
  30.         lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Quantity).ToList()    
  31.                                              : data.OrderBy(p => p.Quantity).ToList();    
  32.         break;    
  33.       case "3":    
  34.         // Setting.    
  35.         lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.ProductName).ToList()    
  36.                                              : data.OrderBy(p => p.ProductName).ToList();    
  37.         break;    
  38.       case "4":    
  39.         // Setting.    
  40.         lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.SpecialOffer).ToList()    
  41.                                               : data.OrderBy(p => p.SpecialOffer).ToList();    
  42.         break;    
  43.       case "5":    
  44.         // Setting.    
  45.         lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.UnitPrice).ToList()    
  46.                                              : data.OrderBy(p => p.UnitPrice).ToList();    
  47.         break;    
  48.       case "6":    
  49.         // Setting.    
  50.         lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.UnitPriceDiscount).ToList()    
  51.                                              : data.OrderBy(p => p.UnitPriceDiscount).ToList();    
  52.         break;    
  53.       default:    
  54.         // Setting.    
  55.         lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Sr).ToList()     
  56.                                              : data.OrderBy(p => p.Sr).ToList();    
  57.         break;    
  58.     }    
  59.   }    
  60.   catch (Exception ex)    
  61.   {    
  62.     // info.    
  63.     Console.Write(ex);    
  64.   }    
  65.   // info.    
  66.   return lst;    
  67. }    
  68. #endregion  
Here how the results will look after applying the filtering,

output

Conclusion

This article was about Datatables plugin server side integration with ASP.NET Webform. In this article you will learn to integrate server side data, searching, sorting and pagination information with Datatebles plugin using ASP.NET classic webforms. I have also created a post for integrating Datatables JQuery base plugin server side integration with ASP.NET MVC5, here is the link.