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

Last Reply one month ago By dharmendr

Posted one month ago

example below

Implement Nested Repeater (Repeater inside Repeater) with example in ASP.Net using C# and VB.Net

i want to perforem same functionality in html table and main point is also fetch the value ant store in json format 

i have no idea how to implement

You are viewing reply posted by: dharmendr one month ago.
Posted one month 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

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetCustomerOrders",
                data: {},
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    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><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);
                },
                failure: function (response) {
                    alert(response.d);
                },
                error: function (response) {
                    alert(response.d);
                }
            });
            $("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();
            });
        });       
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <table id="tblCustomers" width="100%">
    </table>
    </form>
</body>
</html>

Namespaces

C#

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

VB.Net

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

Code

C#

[WebMethod]
public static List<Customer> GetCustomerOrders()
{
    List<Customer> customers = new List<Customer>();
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            con.Open();
            cmd.CommandText = "SELECT TOP 5 CustomerId,ContactName,Country FROM Customers ORDER BY ContactName ASC";
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Customer customer = new Customer
                {
                    CustomerId = rdr["CustomerId"].ToString(),
                    Name = rdr["ContactName"].ToString(),
                    Country = rdr["Country"].ToString(),
                    Orders = Orders(rdr["CustomerId"].ToString())
                };

                customers.Add(customer);
            }
            con.Close();
        }
    }

    return customers;
}

public static List<Order> Orders(string customerId)
{
    List<Order> orders = new List<Order>();
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            con.Open();
            cmd.CommandText = "SELECT TOP 3 OrderId,Freight,ShipCity FROM Orders WHERE CustomerId = @CustomerId";
            cmd.Parameters.AddWithValue("@CustomerId", customerId);
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Order order = new Order
                {
                    OrderId = rdr["OrderId"].ToString(),
                    Freight = rdr["Freight"].ToString(),
                    ShipCity = rdr["ShipCity"].ToString(),
                };

                orders.Add(order);
            }
            con.Close();
        }
    }

    return orders;
}

public class Customer
{
    public string CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
    public List<Order> Orders { get; set; }
}

public class Order
{
    public string OrderId { get; set; }
    public string Freight { get; set; }
    public string ShipCity { get; set; }
}

VB.Net

<WebMethod()>
Public Shared Function GetCustomerOrders() As List(Of Customer)
    Dim customers As List(Of Customer) = New List(Of Customer)()
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.Connection = con
            con.Open()
            cmd.CommandText = "SELECT TOP 5 CustomerId,ContactName,Country FROM Customers ORDER BY ContactName ASC"
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            While rdr.Read()
                Dim customer As Customer = New Customer With {
                    .CustomerId = rdr("CustomerId").ToString(),
                    .Name = rdr("ContactName").ToString(),
                    .Country = rdr("Country").ToString(),
                    .Orders = Orders(rdr("CustomerId").ToString())
                }
                customers.Add(customer)
            End While

            con.Close()
        End Using
    End Using

    Return customers
End Function

Public Shared Function Orders(ByVal customerId As String) As List(Of Order)
    Dim ordersList As List(Of Order) = New List(Of Order)()
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.Connection = con
            con.Open()
            cmd.CommandText = "SELECT TOP 3 OrderId,Freight,ShipCity FROM Orders WHERE CustomerId = @CustomerId"
            cmd.Parameters.AddWithValue("@CustomerId", customerId)
            Dim rdr As SqlDataReader = cmd.ExecuteReader()

            While rdr.Read()
                Dim order As Order = New Order With {
                    .OrderId = rdr("OrderId").ToString(),
                    .Freight = rdr("Freight").ToString(),
                    .ShipCity = rdr("ShipCity").ToString()
                }
                ordersList.Add(order)
            End While

            con.Close()
        End Using
    End Using

    Return ordersList
End Function

Public Class Customer
    Public Property CustomerId As String
    Public Property Name As String
    Public Property Country As String
    Public Property Orders As List(Of Order)
End Class

Public Class Order
    Public Property OrderId As String
    Public Property Freight As String
    Public Property ShipCity As String
End Class

Screenshot