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
  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';
  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';
  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" %>
  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. });
  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.