Saturday, 30 May 2020

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

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]

GO

 

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

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[spDataInDataTable] (

       @sortColumn VARCHAR(50)

       ,@sortOrder VARCHAR(50)

       ,@OffsetValue INT

       ,@PagingSize INT

       ,@SearchText VARCHAR(50)

       )

AS

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>

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

 

    <title>Datatable Example</title>

 

    <script src="Scripts/jquery-1.7.js"></script>

    <script src="Scripts/DataTables/jquery.dataTables.js"></script>

    <link href="Content/DataTables/css/jquery.dataTables.css" rel="stylesheet" />

    <script type="text/javascript">

        $(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>

    <form id="form1" runat="server">

        <div>

            <!--Structure of the table with only header-->

            <table id="tblDataTable" class="display">

                <thead>

                    <tr>

                        <th>Full Name</th>

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


[Download Source code via Google Drive]

Protected by Copyscape - Do not copy content from this page.

0 comments:

Post a comment

Subscribe Now

Popular Posts

Contact us

Name

Email *

Message *

Subscribe us on YouTube

Like us on Facebook

Follow us on Google+