Server-Side Processing With Ajax In Jquery

<link href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>

<table id="gvObjects" class="display" style="width: 100%;">
   <thead>
      <tr>
        <th>Table Name</th>
      </tr>
   </thead>
   <tbody></tbody>
</table>

public class DataTableRequest
{
   public int draw { get; set; }
   public int start { get; set; }
   public int length { get; set; }
   public Search search { get; set; }

   public class Search
   {
       public string value { get; set; }
   }
}

// DataTable response class
public class DataTableResponse<T>
{
   public int draw { get; set; }
   public int recordsTotal { get; set; }
   public int recordsFiltered { get; set; }
   public List<T> data { get; set; }
}

// Table object class
public class TableObject
{
   public string name { get; set; }
}

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static object GetObjects(int draw, int start, int length, string search)
{
    List<TableObject> allTables = new List<TableObject>();
    int totalRecords = 0;
    int filteredRecords = 0;
    string searchTerm = search;

    string connStr = @"your_connection_string";

    using (SqlConnection conn = new SqlConnection(connStr))
    {
        conn.Open();

        // Count total records
        using (SqlCommand countCmd = new SqlCommand("SELECT COUNT(*) FROM sys.objects WHERE type = 'U'", conn))
        {
            totalRecords = (int)countCmd.ExecuteScalar();
        }

        // Build dynamic WHERE clause for search
        string whereClause = "WHERE type = 'U'";
        if (!string.IsNullOrEmpty(searchTerm))
        {
            whereClause += " AND name LIKE @search";
        }

        // Count filtered records
        string countFilteredQuery = $"SELECT COUNT(*) FROM sys.objects {whereClause}";
        using (SqlCommand countFilteredCmd = new SqlCommand(countFilteredQuery, conn))
        {
            if (!string.IsNullOrEmpty(searchTerm))
                countFilteredCmd.Parameters.AddWithValue("@search", $"%{searchTerm}%");

            filteredRecords = (int)countFilteredCmd.ExecuteScalar();
        }

        // Paged and filtered data
        string dataQuery = $@"
                    SELECT name FROM (
                        SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS RowNum
                        FROM sys.objects
                        {whereClause}
                    ) AS Temp
                    WHERE Temp.RowNum BETWEEN @start + 1 AND @start + @length";

        using (SqlCommand cmd = new SqlCommand(dataQuery, conn))
        {
            if (!string.IsNullOrEmpty(searchTerm))
                cmd.Parameters.AddWithValue("@search", $"%{searchTerm}%");

            cmd.Parameters.AddWithValue("@start", start);
            cmd.Parameters.AddWithValue("@length", length);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    allTables.Add(new TableObject
                    {
                        name = reader["name"].ToString()
                    });
                }
            }
        }
    }

    // Return the data in a structure that DataTables expects
    return new
    {
        d = new DataTableResponse<TableObject>
        {
            draw = draw,
            recordsTotal = totalRecords,
            recordsFiltered = filteredRecords,
            data = allTables
        }
    };
}

<script type="text/javascript">
    $(document).ready(function () {
        $('#gvObjects').DataTable({
            "processing": true,
            "serverSide": true,
            "pageLength": 5,
            "ajax": {
                "url": "Default.aspx/GetObjects",
                "type": "POST",
                "contentType": "application/json; charset=utf-8",
                "dataType": "json",
                "data": function (d) {
                    console.log(d.search.value);
                    // Sending the DataTable request parameters as an object
                    var requestData = {
                        draw: d.draw,
                        start: d.start,
                        length: d.length,
                        search: d.search.value
                           
                    };
                    return JSON.stringify(requestData);
                },
                "dataSrc": function (json) {                      
                    json.draw = json.d.d.draw;
                    json.recordsTotal = json.d.d.recordsTotal;
                    json.recordsFiltered = json.d.d.recordsFiltered;
                    return json.d.d.data;
                }
            },
            "columns": [
                { "data": "name" }
            ]
        });
    });
</script>

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

<!DOCTYPE html>
<html>
<head>
    <title>DataTables POC</title>
    <link href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css" rel="stylesheet" />
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
</head>
<body>

    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true" />

        <table id="gvObjects" class="display" style="width: 100%;">
            <thead>
                <tr>
                    <th>Table Name</th>
                </tr>
            </thead>
            <tbody></tbody>
        </table>
    </form>

<script type="text/javascript">
    $(document).ready(function () {
        $('#gvObjects').DataTable({
            "processing": true,
            "serverSide": true,
            "pageLength": 5,
            "ajax": {
                "url": "Default.aspx/GetObjects",
                "type": "POST",
                "contentType": "application/json; charset=utf-8",
                "dataType": "json",
                "data": function (d) {
                    console.log(d.search.value);
                    // Sending the DataTable request parameters as an object
                    var requestData = {
                        draw: d.draw,
                        start: d.start,
                        length: d.length,
                        search: d.search.value
                           
                    };
                    return JSON.stringify(requestData);
                },
                "dataSrc": function (json) {                      
                    json.draw = json.d.d.draw;
                    json.recordsTotal = json.d.d.recordsTotal;
                    json.recordsFiltered = json.d.d.recordsFiltered;
                    return json.d.d.data;
                }
            },
            "columns": [
                { "data": "name" }
            ]
        });
    });
</script>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Services;
using System.Web.Script.Services;

namespace YourNamespace
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        // WebMethod to handle DataTable requests
        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        //public static object GetObjects(DataTableRequest request)
        public static object GetObjects(int draw, int start, int length, string search)            
        {
            List<TableObject> allTables = new List<TableObject>();
            int totalRecords = 0;
            int filteredRecords = 0;
            string searchTerm = search;

            string connStr = @"Data Source=your_connection_string";

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                // Count total records
                using (SqlCommand countCmd = new SqlCommand("SELECT COUNT(*) FROM sys.objects WHERE type = 'U'", conn))
                {
                    totalRecords = (int)countCmd.ExecuteScalar();
                }

                // Build dynamic WHERE clause for search
                string whereClause = "WHERE type = 'U'";
                if (!string.IsNullOrEmpty(searchTerm))
                {
                    whereClause += " AND name LIKE @search";
                }

                // Count filtered records
                string countFilteredQuery = $"SELECT COUNT(*) FROM sys.objects {whereClause}";
                using (SqlCommand countFilteredCmd = new SqlCommand(countFilteredQuery, conn))
                {
                    if (!string.IsNullOrEmpty(searchTerm))
                        countFilteredCmd.Parameters.AddWithValue("@search", $"%{searchTerm}%");

                    filteredRecords = (int)countFilteredCmd.ExecuteScalar();
                }

                // Paged and filtered data
                string dataQuery = $@"
                    SELECT name FROM (
                        SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS RowNum
                        FROM sys.objects
                        {whereClause}
                    ) AS Temp
                    WHERE Temp.RowNum BETWEEN @start + 1 AND @start + @length";

                using (SqlCommand cmd = new SqlCommand(dataQuery, conn))
                {
                    if (!string.IsNullOrEmpty(searchTerm))
                        cmd.Parameters.AddWithValue("@search", $"%{searchTerm}%");

                    cmd.Parameters.AddWithValue("@start", start);
                    cmd.Parameters.AddWithValue("@length", length);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            allTables.Add(new TableObject
                            {
                                name = reader["name"].ToString()
                            });
                        }
                    }
                }
            }

            // Return the data in a structure that DataTables expects
            return new
            {
                d = new DataTableResponse<TableObject>
                {
                    draw = draw,
                    recordsTotal = totalRecords,
                    recordsFiltered = filteredRecords,
                    data = allTables
                }
            };
        }
    }

    // DataTable request class for parameters
    public class DataTableRequest
    {
        public int draw { get; set; }
        public int start { get; set; }
        public int length { get; set; }
        public Search search { get; set; }

        public class Search
        {
            public string value { get; set; }
            //public bool regex { get; set; }
        }
    }

    // DataTable response class
    public class DataTableResponse<T>
    {
        public int draw { get; set; }
        public int recordsTotal { get; set; }
        public int recordsFiltered { get; set; }
        public List<T> data { get; set; }
    }

    // Table object class
    public class TableObject
    {
        public string name { get; set; }
    }
}

Query/Feedback