Server-Side Pagination in Datatable.js ~ IT Tutorials with Example

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 the project which was basically an ASP.NET WebForm application. So, I decided to share it with you so that it can help you.

If you are looking for Server-Side Pagination in ASP.NET MVC, I recommend you to go through the below link:

Server-Side Pagination in ASP.NET MVC

Let’s Begin:

1. Let’s start with a fresh empty Web Form Project.

2. Download Datatable.js files from NuGet Package Manager. Click on Browser tab and search for data tables as shown in below screen:

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.

4. Add jQuery, DataTable.js, and Datatable.css file in the Web Form page created in the above 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.

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.

USE [sample]

/****** Object: StoredProcedure [dbo].[spDataInDataTable] Script Date: 30-05-2020 01:00:43 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[spDataInDataTable] (

@sortColumn VARCHAR(50)

,@sortOrder VARCHAR(50)

,@OffsetValue INT

,@PagingSize INT

,@SearchText VARCHAR(50)

)

BEGIN

SELECT ID

,FullName

,PhoneNumber

,FaxNumber

,EmailAddress

,count(ID) OVER () AS FilterTotalCount

FROM Employee

WHERE (

(

@SearchText <> ''

AND (

FullName LIKE '%' + @SearchText + '%'

OR PhoneNumber LIKE '%' + @SearchText + '%'

)

OR (@SearchText = '')

)

ORDER BY CASE

WHEN @sortOrder <> 'ASC'

THEN ''

WHEN @sortColumn = 'FullName'

THEN FullName

END ASC

,CASE

WHEN @sortOrder <> 'Desc'

THEN ''

WHEN @sortColumn = 'FullName'

THEN FullName

END DESC

,CASE

WHEN @sortOrder <> 'ASC'

THEN 0

WHEN @sortColumn = 'ID'

THEN ID

END ASC

,CASE

WHEN @sortOrder <> 'DESC'

THEN 0

WHEN @sortColumn = 'ID'

THEN ID

END DESC

,CASE

WHEN @sortOrder <> 'ASC'

THEN ''

WHEN @sortColumn = 'PhoneNumber'

THEN PhoneNumber

END ASC

,CASE

WHEN @sortOrder <> 'DESC'

THEN ''

WHEN @sortColumn = 'PhoneNumber'

THEN PhoneNumber

END DESC

,CASE

WHEN @sortOrder <> 'ASC'

THEN ''

WHEN @sortColumn = 'FaxNumber'

THEN FaxNumber

END ASC

,CASE

WHEN @sortOrder <> 'DESC'

THEN ''

WHEN @sortColumn = 'FaxNumber'

THEN FaxNumber

END DESC

,CASE

WHEN @sortOrder <> 'ASC'

THEN ''

WHEN @sortColumn = 'EmailAddress'

THEN EmailAddress

END ASC

,CASE

WHEN @sortOrder <> 'DESC'

THEN ''

WHEN @sortColumn = 'EmailAddress'

THEN EmailAddress

END DESC OFFSET @OffsetValue ROWS

FETCH NEXT @PagingSize ROWS ONLY

END

Or we can write it in a short form with the use of a dynamic SQL query as shown below.

----Dynamic Query

DECLARE @sqlQuery VARCHAR(max) = 'SELECT ID,FullName,PhoneNumber,FaxNumber,EmailAddress,count(ID) Over() as FilterTotalCount FROM Employee';

set @sqlQuery=@sqlQuery+' WHERE (('''+@SearchText+''' <> '''' AND (FullName LIKE ''%' + @SearchText + '%'' OR PhoneNumber LIKE ''%' + @SearchText + '%'')) OR ('''+@SearchText+''' = ''''))';

set @sqlQuery=@sqlQuery+' order by '+@sortColumn+' '+@sortOrder;

set @sqlQuery=@sqlQuery+' OFFSET '+cast(@OffsetValue as varchar(100))+' ROWS FETCH NEXT '+cast(@PagingSize as varchar(100))+' ROWS ONLY';

Exec (@sqlQuery);

You can also write the query in the best and optimize way as per your need or by checking the execution plan in SQL Server.

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.

public class DBLayer

{

public DataTable GetData(string sortColumn,string sortDirection, int OffsetValue, int PagingSize, string searchby) {

DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString)) {

conn.Open();

SqlCommand com = new SqlCommand("spDataInDataTable", conn);

com.CommandType = CommandType.StoredProcedure;

com.Parameters.AddWithValue("@sortColumn", sortColumn);

com.Parameters.AddWithValue("@sortOrder", sortDirection);

com.Parameters.AddWithValue("@OffsetValue", OffsetValue);

com.Parameters.AddWithValue("@PagingSize", PagingSize);

com.Parameters.AddWithValue("@SearchText", searchby);

SqlDataAdapter da = new SqlDataAdapter(com);

da.Fill(dt);

da.Dispose();

conn.Close();

}

return dt;

}

8. In order to manage the server-side paging, we need a Web Service. Add a Web Service file in the project.

9. Make sure to uncomment the highlighted lines shown in the below image in order to call Web Service from the Script.

10. Now add a People class that is required to bind the data from the database.

11. Add the below line of code in the Web Service as shown below.

public class WebServiceDataTable : System.Web.Services.WebService

{

[WebMethod]

public void GetDataForDataTable()

{

HttpContext context = HttpContext.Current;

context.Response.ContentType = "text/plain";

//List of Column shown in the Table (user for finding the name of column on Sorting)

List<string> columns = new List<string>();

columns.Add("FullName");

columns.Add("PhoneNumber");

columns.Add("FaxNumber");

columns.Add("EmailAddress");

//This is used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables

Int32 ajaxDraw = Convert.ToInt32(context.Request.Form["draw"]);

//OffsetValue

Int32 OffsetValue = Convert.ToInt32(context.Request.Form["start"]);

//No of Records shown per page

Int32 PagingSize = Convert.ToInt32(context.Request.Form["length"]);

//Getting value from the seatch TextBox

string searchby = context.Request.Form["search[value]"];

//Index of the Column on which Sorting needs to perform

string sortColumn = context.Request.Form["order[0][column]"];

//Finding the column name from the list based upon the column Index

sortColumn = columns[Convert.ToInt32(sortColumn)];

//Sorting Direction

string sortDirection = context.Request.Form["order[0][dir]"];

//Get the Data from the Database

DBLayer objDBLayer = new DBLayer();

DataTable dt = objDBLayer.GetData(sortColumn,sortDirection, OffsetValue, PagingSize, searchby);

Int32 recordTotal = 0;

List<People> peoples = new List<People>();

//Binding the Data from datatable to the List

if (dt != null)

{

for (int i = 0; i < dt.Rows.Count; i++)

{

People people = new People();

people.ID = Convert.IsDBNull(dt.Rows[i]["ID"]) ? default(int) : Convert.ToInt32(dt.Rows[i]["ID"]);

people.FullName = Convert.IsDBNull(dt.Rows[i]["FullName"]) ? default(string) : Convert.ToString(dt.Rows[i]["FullName"]);

people.PhoneNumber = Convert.IsDBNull(dt.Rows[i]["PhoneNumber"]) ? default(string) : Convert.ToString(dt.Rows[i]["PhoneNumber"]);

people.FaxNumber = Convert.IsDBNull(dt.Rows[i]["FaxNumber"]) ? default(string) : Convert.ToString(dt.Rows[i]["FaxNumber"]);

people.EmailAddress = Convert.IsDBNull(dt.Rows[i]["EmailAddress"]) ? default(string) : Convert.ToString(dt.Rows[i]["EmailAddress"]);

peoples.Add(people);

}

recordTotal = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["FilterTotalCount"]) : 0;

}

Int32 recordFiltered = recordTotal;

DataTableResponse objDataTableResponse = new DataTableResponse()

{

draw = ajaxDraw,

recordsFiltered = recordTotal,

recordsTotal = recordTotal,

data = peoples

};

//writing the response

context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(objDataTableResponse));

}

12. In the above steps, we created a Webservice, Now let’s consume in on DataTableExample.aspx.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataTableExample.aspx.cs" Inherits="DatatableWebForm.DataTableExample" %>

<!DOCTYPE html>

<title>Datatable Example</title>

$(document).ready(function () {

//Once the document is ready call Bind DataTable

BindDataTable()

});

function BindDataTable() {

$('#tblDataTable').DataTable({

"processing": true,

"serverSide": true,

"ajax": {

url: "/WebServiceDataTable.asmx/GetDataForDataTable", type: "post" },

"columns": [

{ "data": "FullName" },

{ "data": "PhoneNumber" },

{ "data": "FaxNumber" },

{ "data": "EmailAddress" }

]

});

}

</script>

</head>

<body>

<div>

<thead>

<tr>

<th>Phone Number</th>

<th>Fax Number</th>

<th>Email Address</th>

</tr>

</thead>

</table>

</div>

</form>

</body>

</html>

13. Now Build and run the application.

Preview:

Hope this will help you.

Thanks

Note: 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.