Populate nested HTML Table with Searching and Paging using jQuery Ajax in ASP.Net

Last Reply 2 months ago By dharmendr

Posted 3 months ago

How to apply searching and paging from sql table is nested html table

Populate nested HTML Table from database using jQuery Ajax in ASP.Net

i performed only searching and paging on primary html table but still confuse how to perofom it 

how to implement server side paging and searching can you show it in nested table 

    <script type="text/javascript"> 
        $(function () {
            GetCustomers(1);
            $("body").on("click", "[src*=plus]", function () {
                $(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>")
                $(this).attr("src", "images/minus.png");
            });
            $("body").on("click", "[src*=minus]", function () {
                $(this).attr("src", "images/plus.png");
                $(this).closest("tr").next().remove();
            });
        });
 
        $("[id*=txtSearch]").live("keyup", function () {
            GetCustomers(parseInt(1));
        });
        $(".Pager .page").live("click", function () {
            GetCustomers(parseInt($(this).attr('page')));
        });
        function SearchTerm() {
            return jQuery.trim($("[id*=txtSearch]").val());
        };
        function GetCustomers(pageIndex, name) {
            var obj = {};
            obj.pageIndex = $.trim(pageIndex);
            obj.name = $.trim(SearchTerm());
            $.ajax({
                type: "POST",
                url: "Test.aspx/GetCustomerOrders",
                data: JSON.stringify(obj),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess,
                failure: function (response) {
                    alert(response.d);
                },
                error: function (response) {
                    alert(response.d);
                }
            });
        }
      
        function OnSuccess(response) {
            $('#tblCustomers').empty();
            var customers = response.d;
            var table = '<tr><th>&nbsp;</th><th>Name</th><th>Country</th></tr>';
            $.each(customers, function (i, item) {
                table += '<tr><td><img alt="" style="cursor: pointer" src="images/plus.png" /><div id="dvOrders" style="display: none">';
                var html = '<table class="ntbl"><tr><th>Order Id</th><th>Freight</th><th>Ship City</th></tr>';
                $.each(item.Orders, function (i1, item1) {
                    html += '<tr><td>' + item1.OrderId +
                            '</td><td>' + item1.Freight +
                            '</td><td>' + item1.ShipCity +
                            '</td></tr>';
                });
                table += html + '</table></div></td>';
                table += '<td>' + item.Name + '</td>';
                table += '<td>' + item.Country + '</td></tr>';
            });
            $('#tblCustomers').append(table);
            console.log(customers[1].pgs);
 
                //var pager = xml.find("Pager");
                $(".Pager").ASPSnippets_Pager({
                    ActiveCssClass: "current",
                    PagerCssClass: "pager",
                    PageIndex: parseInt(customers[1].pi),
                    PageSize: parseInt(customers[1].psize),
                    RecordCount: parseInt(customers[1].pgs)
                });
                $(".ContactName").each(function () {
                    var searchPattern = new RegExp('(' + SearchTerm() + ')', 'ig');
                    $(this).html($(this).text());
                });            
            };
        </script>

 

Posted 2 months ago Modified on 2 months ago

Hi SajidHussa,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Procedures

-- GetCustomersPageWise 'm',1,5,100
CREATE PROCEDURE [dbo].[GetCustomersPageWise]
      @SearchTerm VARCHAR(100) = ''
      ,@PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
             ,[CustomerID]
             ,[CompanyName]
             ,[ContactName]
             ,[ContactTitle]
             ,[Address]
             ,[City]
             ,[Region]
             ,[PostalCode]
             ,[Country]
             ,[Phone]
             ,[Fax]
      INTO #Results
      FROM [Customers]
      WHERE [ContactName] LIKE @SearchTerm + '%' OR @SearchTerm = ''
    
      SELECT @RecordCount = COUNT(*)
      FROM #Results
          
      SELECT *
      FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    
      DROP TABLE #Results
END
GO
-- GetOrdersPageWiseBasedOnCustomerId 'TOMSP',1,5,100
CREATE PROCEDURE [dbo].[GetOrdersPageWiseBasedOnCustomerId]
	  @Id VARCHAR(10)
      ,@PageIndex INT = 1
      ,@PageSize INT = 5
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;

      SELECT ROW_NUMBER() OVER (ORDER BY [CustomerID] ASC) AS RowNumber,*
      INTO #Results
      FROM [Orders]
      WHERE CustomerID = @Id   

      SELECT @RecordCount = COUNT(*)
      FROM #Results         

      SELECT *
      FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1   

      DROP TABLE #Results
END

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Nested Html Table Paging</title>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
    <script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $("#txtSearch").focus();
            GetCustomers(1, '');
            $("#txtSearch").keyup(function () {
                GetCustomers(1, $(this).val());
            });
            $("body").on("click", "[src*=plus]", function () {
                $(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>")
                $(this).attr("src", "images/minus.png");
                GetOrders(1, $(this).closest("tr").find('td').eq(1).text(), $(this).closest("tr").find('#dvChild'));
            });
            $("body").on("click", "[src*=minus]", function () {
                $(this).attr("src", "images/plus.png");
                $(this).closest("tr").next().remove();
                $(this).closest("tr").find('#dvChild').empty();
            });
        });
        $(".Pager .page").live("click", function () {
            GetCustomers(parseInt($(this).attr('page')), $("#txtSearch").val());
        });

        $(".PagerChild .page").live("click", function () {
            var customerId = $(this).closest("tr").find('#hfCustomerId').val();
            GetOrders(parseInt($(this).attr('page')), customerId, $(this).closest("tr").find('#dvChild'));
        });
        var row;
        function GetCustomers(pageIndex, name) {
            var obj = {};
            obj.pageIndex = $.trim(pageIndex);
            obj.name = $.trim(name);
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetCustomers",
                data: JSON.stringify(obj),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    var xmlDoc = $.parseXML(response.d);
                    var xml = $(xmlDoc);
                    var customers = xml.find("Customers");
                    if (row == null) {
                        row = $("[id*=gvCustomers] tr:last-child").clone(true);
                    }
                    $("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
                    $.each(customers, function () {
                        var customer = $(this);
                        $("td", row).find('#hfCustomerId').val($(this).find("CustomerID").text());
                        $("td", row).find('#id').html($(this).find("CustomerID").text());
                        $("td", row).find('#name').html($(this).find("ContactName").text());
                        $("td", row).find('#country').html($(this).find("City").text());
                        $("[id*=gvCustomers]").append(row);
                        row = $("[id*=gvCustomers] tr:last-child").clone(true);
                    });
                    var pager = xml.find("Pager");
                    $(".Pager").ASPSnippets_Pager({
                        ActiveCssClass: "current",
                        PagerCssClass: "pager",
                        PageIndex: parseInt(pager.find("PageIndex").text()),
                        PageSize: parseInt(pager.find("PageSize").text()),
                        RecordCount: parseInt(pager.find("RecordCount").text())
                    });
                }
            });
        }

        function GetOrders(pageIndex, id, ele) {
            $(ele).empty();
            var obj = {};
            obj.pageIndex = $.trim(pageIndex);
            obj.id = $.trim(id);
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetOrders",
                data: JSON.stringify(obj),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    var xmlDoc = $.parseXML(response.d);
                    var xml = $(xmlDoc);
                    var orders = xml.find("Customers");
                    var html = '<table><tr><th>Order Id</th><th>Freight</th><th>Ship City</th></tr>';
                    $.each(orders, function () {
                        html += '<tr><td>' + $(this).find("OrderID").text() +
                                '</td><td>' + $(this).find("Freight").text() +
                                '</td><td>' + $(this).find("ShipCity").text() +
                                '</td></tr>';
                    });
                    html += '</table><br/><div class="PagerChild"></div>';
                    $(ele).append(html);
                    var pager = xml.find("Pager");
                    $(".PagerChild").ASPSnippets_Pager({
                        ActiveCssClass: "current",
                        PagerCssClass: "pager",
                        PageIndex: parseInt(pager.find("PageIndex").text()),
                        PageSize: parseInt(pager.find("PageSize").text()),
                        RecordCount: parseInt(pager.find("RecordCount").text())
                    });
                }
            });
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <center>
        Name : <input type="text" name="name" id="txtSearch" />
        <table id="gvCustomers">
            <tr>
                <th>&nbsp;</th>
                <th>CustomerID</th>
                <th>Contact Name</th>
                <th>City</th>
            </tr>
            <tr>
                <td valign="top">
                    <input type="hidden" id="hfCustomerId" />
                    <img alt="" style="cursor: pointer" src="images/plus.png" />
                    <div id="dvChild">
                    </div>
                </td>
                <td valign="top"><span id="id"></span></td>
                <td valign="top"><span id="name"></span></td>
                <td valign="top"><span id="country"></span></td>
            </tr>
        </table>
        <br />
        <div class="Pager"></div>
    </center>
    </form>
</body>
</html>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

private static int PageSize = 5;
[WebMethod]
public static string GetCustomers(int pageIndex, string name)
{
    // Get Customers PageWise.
    string query = "[GetCustomersPageWise]";
    SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@SearchTerm", name);
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    cmd.Parameters.AddWithValue("@PageSize", PageSize);
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
    return GetData(cmd, pageIndex).GetXml();
}

[WebMethod]
public static string GetOrders(int pageIndex, string id)
{
    // Get Orders PageWise.
    string query = "[GetOrdersPageWiseBasedOnCustomerId]";
    SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Id", id);
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    cmd.Parameters.AddWithValue("@PageSize", PageSize);
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
    return GetData(cmd, pageIndex).GetXml();
}

private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
    string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds, "Customers");
                DataTable dt = new DataTable("Pager");
                dt.Columns.Add("PageIndex");
                dt.Columns.Add("PageSize");
                dt.Columns.Add("RecordCount");
                dt.Rows.Add();
                dt.Rows[0]["PageIndex"] = pageIndex;
                dt.Rows[0]["PageSize"] = PageSize;
                dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                ds.Tables.Add(dt);
                return ds;
            }
        }
    }
}

VB.Net

Private Shared PageSize As Integer = 5
<WebMethod>
Public Shared Function GetCustomers(ByVal pageIndex As Integer, ByVal name As String) As String
    Dim query As String = "[GetCustomersPageWise]"
    Dim cmd As SqlCommand = New SqlCommand(query)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@SearchTerm", name)
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
    cmd.Parameters.AddWithValue("@PageSize", PageSize)
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
    Return GetData(cmd, pageIndex).GetXml()
End Function

<WebMethod>
Public Shared Function GetOrders(ByVal pageIndex As Integer, ByVal id As String) As String
    Dim query As String = "[GetOrdersPageWiseBasedOnCustomerId]"
    Dim cmd As SqlCommand = New SqlCommand(query)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Id", id)
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
    cmd.Parameters.AddWithValue("@PageSize", PageSize)
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
    Return GetData(cmd, pageIndex).GetXml()
End Function

Private Shared Function GetData(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Using con As SqlConnection = New SqlConnection(strConnString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using ds As DataSet = New DataSet()
                sda.Fill(ds, "Customers")
                Dim dt As DataTable = New DataTable("Pager")
                dt.Columns.Add("PageIndex")
                dt.Columns.Add("PageSize")
                dt.Columns.Add("RecordCount")
                dt.Rows.Add()
                dt.Rows(0)("PageIndex") = pageIndex
                dt.Rows(0)("PageSize") = PageSize
                dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
                ds.Tables.Add(dt)
                Return ds
            End Using
        End Using
    End Using
End Function

Screenshot