Server-Side Pagination In Datatable.js

In this article, we will learn how to manage server-side pagination, sorting, and searching in DataTable.js. Recently, I used that in one of my projects, which was basically an ASP.NET WebForm application. (It might be old technology for MVC guys but still many applications are running on with this technology. In the case, if you are working on ASP.NET MVC Application, then add an Action in your controller which returns JSON as a response. The response will be of the same type as we are returning through web service.). So, I decided to share it with you so that it can help you.
 

 
Let’s begin.
 
Step 1
 
Let’s start with a fresh empty Web Form Project.
 
 
Step 2
 
Download Datatable.js files from NuGet Package Manager. Click on Browser tab and search for data tables as shown in the below screen.
 
Step 3
 
Now, right-click on Project and Click on Add a new item; i.e. Web Form. Give it a meaningful name (DataTableExample.aspx) and click on Add. 
 
 
Step 4
 
Add jQuery, DataTable.js, and Datatable.css file in the Web Form page created in the above step.
 
Step 5
 
In SQL Server Database, I have created a Sample DB that contains the Employee Table. We will use Offset and Fetch Next clause which is used in conjunction with Select and Order by Clause.
 
 
Step 6
 
Now add a procedure; i.e. spDataInDataTable, which will be called in order to bring the data from the database to the application. The same procedure can be written in multiple ways. The first one is a long way; i.e., without using the dynamic query as shown below.
  1. Create PROCEDURE [dbo].[spDataInDataTable] (  
  2.     @sortColumn VARCHAR(50)  
  3.     ,@sortOrder VARCHAR(50)  
  4.     ,@OffsetValue INT  
  5.     ,@PagingSize INT  
  6.     ,@SearchText VARCHAR(50)  
  7.     )  
  8. AS  
  9. BEGIN  
  10.     SELECT ID  
  11.         ,FullName  
  12.         ,PhoneNumber  
  13.         ,FaxNumber  
  14.         ,EmailAddress  
  15.         ,count(ID) OVER () AS FilterTotalCount  
  16.     FROM Employee  
  17.     WHERE (  
  18.             (  
  19.                 @SearchText <> ''  
  20.                 AND (  
  21.                     FullName LIKE '%' + @SearchText + '%'  
  22.                     OR PhoneNumber LIKE '%' + @SearchText + '%'  
  23.                     )  
  24.                 )  
  25.             OR (@SearchText = '')  
  26.             )  
  27.     ORDER BY CASE   
  28.             WHEN @sortOrder <> 'ASC'  
  29.                 THEN ''  
  30.             WHEN @sortColumn = 'FullName'  
  31.                 THEN FullName  
  32.             END ASC  
  33.         ,CASE   
  34.             WHEN @sortOrder <> 'Desc'  
  35.                 THEN ''  
  36.             WHEN @sortColumn = 'FullName'  
  37.                 THEN FullName  
  38.             END DESC  
  39.         ,CASE   
  40.             WHEN @sortOrder <> 'ASC'  
  41.                 THEN 0  
  42.             WHEN @sortColumn = 'ID'  
  43.                 THEN ID  
  44.             END ASC  
  45.         ,CASE   
  46.             WHEN @sortOrder <> 'DESC'  
  47.                 THEN 0  
  48.             WHEN @sortColumn = 'ID'  
  49.                 THEN ID  
  50.             END DESC  
  51.         ,CASE   
  52.             WHEN @sortOrder <> 'ASC'  
  53.                 THEN ''  
  54.             WHEN @sortColumn = 'PhoneNumber'  
  55.                 THEN PhoneNumber  
  56.             END ASC  
  57.         ,CASE   
  58.             WHEN @sortOrder <> 'DESC'  
  59.                 THEN ''  
  60.             WHEN @sortColumn = 'PhoneNumber'  
  61.                 THEN PhoneNumber  
  62.             END DESC  
  63.         ,CASE   
  64.             WHEN @sortOrder <> 'ASC'  
  65.                 THEN ''  
  66.             WHEN @sortColumn = 'FaxNumber'  
  67.                 THEN FaxNumber  
  68.             END ASC  
  69.         ,CASE   
  70.             WHEN @sortOrder <> 'DESC'  
  71.                 THEN ''  
  72.             WHEN @sortColumn = 'FaxNumber'  
  73.                 THEN FaxNumber  
  74.             END DESC  
  75.         ,CASE   
  76.             WHEN @sortOrder <> 'ASC'  
  77.                 THEN ''  
  78.             WHEN @sortColumn = 'EmailAddress'  
  79.                 THEN EmailAddress  
  80.             END ASC  
  81.         ,CASE   
  82.             WHEN @sortOrder <> 'DESC'  
  83.                 THEN ''  
  84.             WHEN @sortColumn = 'EmailAddress'  
  85.                 THEN EmailAddress  
  86.             END DESC OFFSET @OffsetValue ROWS  
  87.   
  88.     FETCH NEXT @PagingSize ROWS ONLY  
  89. END  
Or we can write it in a short form with the use of a dynamic SQL query as shown below.
  1. ----Dynamic Query  
  2. DECLARE @sqlQuery VARCHAR(max) = 'SELECT ID,FullName,PhoneNumber,FaxNumber,EmailAddress,count(ID) Over() as FilterTotalCount FROM Employee';  
  3.   
  4. SET @sqlQuery = @sqlQuery + ' WHERE ((''' + @SearchText + ''' <> '''' AND (FullName LIKE ''%' + @SearchText + '%'' OR PhoneNumber LIKE ''%' + @SearchText + '%'')) OR (''' + @SearchText + ''' = ''''))';  
  5. SET @sqlQuery = @sqlQuery + ' order by ' + @sortColumn + ' ' + @sortOrder;  
  6. SET @sqlQuery = @sqlQuery + ' OFFSET ' + cast(@OffsetValue AS VARCHAR(100)) + ' ROWS FETCH NEXT ' + cast(@PagingSize AS VARCHAR(100)) + ' ROWS ONLY';  
  7.   
  8. EXEC (@sqlQuery);  
You can also write the query in the best and optimized way as per your need or by checking the execution plan in SQL Server.
 
Step 7
 
Add a class that will act as DB Layer in our project. You can also use Entity Framework and other libraries.
 
 
 
Below is the ADO.NET code which will call the Procedure.
  1. public class DBLayer  
  2. {  
  3.         public DataTable GetData(string sortColumn,string sortDirection, int OffsetValue, int PagingSize, string searchby) {  
  4.             DataTable dt = new DataTable();  
  5.             using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString)) {  
  6.                 conn.Open();  
  7.                 SqlCommand com = new SqlCommand("spDataInDataTable", conn);  
  8.                 com.CommandType = CommandType.StoredProcedure;  
  9.                 com.Parameters.AddWithValue("@sortColumn", sortColumn);  
  10.                 com.Parameters.AddWithValue("@sortOrder", sortDirection);  
  11.                 com.Parameters.AddWithValue("@OffsetValue", OffsetValue);  
  12.                 com.Parameters.AddWithValue("@PagingSize", PagingSize);  
  13.                 com.Parameters.AddWithValue("@SearchText", searchby);  
  14.                 SqlDataAdapter da = new SqlDataAdapter(com);  
  15.                 da.Fill(dt);  
  16.                 da.Dispose();  
  17.                 conn.Close();  
  18.             }  
  19.             return dt;  
  20.         }  
  21. }  
Step 8
 
In order to manage the server-side paging, we need a Web Service. Add a Web Service file in the project.
 
 
Step 9
 
Make sure to uncomment the highlighted lines shown in the below image in order to call Web Service from the Script.
 
 
Step 10
 
Now add a People class that is required to bind the data from the database.
 
 
Step 11
 
Add the below line of code in the Web Service as shown below.
  1. public class WebServiceDataTable : System.Web.Services.WebService  
  2. {  
  3.         [WebMethod]  
  4.         public void GetDataForDataTable()  
  5.         {  
  6.             HttpContext context = HttpContext.Current;  
  7.             context.Response.ContentType = "text/plain";  
  8.             //List of Column shown in the Table (user for finding the name of column on Sorting)  
  9.             List<string> columns = new List<string>();  
  10.             columns.Add("FullName");  
  11.             columns.Add("PhoneNumber");  
  12.             columns.Add("FaxNumber");  
  13.             columns.Add("EmailAddress");  
  14.             //This is used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables  
  15.             Int32 ajaxDraw = Convert.ToInt32(context.Request.Form["draw"]);  
  16.             //OffsetValue  
  17.             Int32 OffsetValue = Convert.ToInt32(context.Request.Form["start"]);  
  18.             //No of Records shown per page  
  19.             Int32 PagingSize = Convert.ToInt32(context.Request.Form["length"]);  
  20.             //Getting value from the seatch TextBox  
  21.             string searchby = context.Request.Form["search[value]"];  
  22.             //Index of the Column on which Sorting needs to perform  
  23.             string sortColumn = context.Request.Form["order[0][column]"];  
  24.             //Finding the column name from the list based upon the column Index  
  25.             sortColumn = columns[Convert.ToInt32(sortColumn)];  
  26.             //Sorting Direction  
  27.             string sortDirection = context.Request.Form["order[0][dir]"];  
  28.             //Get the Data from the Database  
  29.             DBLayer objDBLayer = new DBLayer();  
  30.             DataTable dt = objDBLayer.GetData(sortColumn,sortDirection, OffsetValue, PagingSize, searchby);  
  31.             Int32 recordTotal = 0;  
  32.             List<People> peoples = new List<People>();  
  33.             //Binding the Data from datatable to the List  
  34.             if (dt != null)  
  35.             {  
  36.                 for (int i = 0; i < dt.Rows.Count; i++)  
  37.                 {  
  38.                     People people = new People();  
  39.                     people.ID = Convert.IsDBNull(dt.Rows[i]["ID"]) ? default(int) : Convert.ToInt32(dt.Rows[i]["ID"]);  
  40.                     people.FullName = Convert.IsDBNull(dt.Rows[i]["FullName"]) ? default(string) : Convert.ToString(dt.Rows[i]["FullName"]);  
  41.                     people.PhoneNumber = Convert.IsDBNull(dt.Rows[i]["PhoneNumber"]) ? default(string) : Convert.ToString(dt.Rows[i]["PhoneNumber"]);  
  42.                     people.FaxNumber = Convert.IsDBNull(dt.Rows[i]["FaxNumber"]) ? default(string) : Convert.ToString(dt.Rows[i]["FaxNumber"]);  
  43.                     people.EmailAddress = Convert.IsDBNull(dt.Rows[i]["EmailAddress"]) ? default(string) : Convert.ToString(dt.Rows[i]["EmailAddress"]);  
  44.                     peoples.Add(people);  
  45.                 }  
  46.                 recordTotal = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["FilterTotalCount"]) : 0;  
  47.             }  
  48.             Int32 recordFiltered = recordTotal;  
  49.             DataTableResponse objDataTableResponse = new DataTableResponse()  
  50.             {  
  51.                 draw = ajaxDraw,  
  52.                 recordsFiltered = recordTotal,  
  53.                 recordsTotal = recordTotal,  
  54.                 data = peoples  
  55.             };  
  56.             //writing the response           context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(objDataTableResponse));  
  57.         }  
  58. }  
Step 12
 
In the above steps, we created a Webservice. Now let’s consume it in DataTableExample.aspx.
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataTableExample.aspx.cs" Inherits="DatatableWebForm.DataTableExample" %>    
  2.     
  3. <!DOCTYPE html>    
  4. <html xmlns="http://www.w3.org/1999/xhtml">    
  5. <head runat="server">    
  6.     <title>Datatable Example</title>    
  7.     <script src="Scripts/jquery-1.7.js"></script>    
  8.     <script src="Scripts/DataTables/jquery.dataTables.js"></script>    
  9.     <link href="Content/DataTables/css/jquery.dataTables.css" rel="stylesheet" />    
  10.     <script type="text/javascript">    
  11.         $(document).ready(function () {    
  12.             //Once the document is ready call Bind DataTable    
  13.             BindDataTable()    
  14.         });    
  15.     
  16.         function BindDataTable() {    
  17.             $('#tblDataTable').DataTable({    
  18.                 "processing"true,    
  19.                 "serverSide"true,    
  20.                 "ajax": {    
  21.                     url: "/WebServiceDataTable.asmx/GetDataForDataTable", type: "post" },    
  22.                 "columns": [    
  23.                     { "data""FullName" },    
  24.                     { "data""PhoneNumber" },    
  25.                     { "data""FaxNumber" },    
  26.                     { "data""EmailAddress" }    
  27.                 ]    
  28.             });    
  29.         }    
  30.     </script>    
  31. </head>    
  32. <body>    
  33.     <form id="form1" runat="server">    
  34.         <div>    
  35.             <!--Structure of the table with only header-->    
  36.             <table id="tblDataTable" class="display">    
  37.                 <thead>    
  38.                     <tr>    
  39.                         <th>Full Name</th>    
  40.                         <th>Phone Number</th>    
  41.                         <th>Fax Number</th>    
  42.                         <th>Email Address</th>    
  43.                     </tr>         
  44.                 </thead>    
  45.             </table>    
  46.         </div>    
  47.     </form>    
  48. </body>    
  49. </html>     
Step 13
 
Now build and run the application.
 
Preview
 
I hope this will help you.
 
Thanks.