Generate nested JSON string from Database using C# and VB.Net in ASP.Net

Refer the below sample code for your reference and implement it as per your code logic. Here i used Northwind Database for a sample.

C#

Customer.cs

using System;
using System.Collections.Generic;
using System.Text;

public class Customer
{
    public string CustomerID { get; set; }
    public string ContactName { get; set; }
    public string Address { get; set; }
    public List<Order> Orders { get; set; }
}

Orders.cs

using System;
using System.Collections.Generic;
using System.Web;

public class Order
{
    public int OrderID { get; set; }
    public string ShipCity { get; set; }
    public decimal Freight { get; set; }
    public string OrderDate { get; set; }
}

Default.aspx.cs

Namespace

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;

Code

protected void Page_Load(object sender, EventArgs e)
{
    List<Customer> customers = new List<Customer>();

    DataTable dt = GetData("SELECT Top 3 CustomerID,ContactName,Address From Customers");
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        Customer customer = new Customer
        {
            CustomerID = Convert.ToString(dt.Rows[i]["CustomerId"])
            ,
            ContactName = Convert.ToString(dt.Rows[i]["ContactName"])
            ,
            Address = Convert.ToString(dt.Rows[i]["Address"])
            ,
            Orders = GetOrders(Convert.ToString(dt.Rows[i]["CustomerId"]))
        };
        customers.Add(customer);
    }
    var json = new JavaScriptSerializer().Serialize(customers);
}

public List<Order> GetOrders(string customerId)
{
    List<Order> orders = new List<Order>();
    DataTable dt = GetData(string.Format("SELECT Top 2 OrderID,CustomerID,ShipCity,Freight,OrderDate FROM Orders Where CustomerID ='{0}'", customerId));
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        orders.Add(new Order
        {
            OrderID = Convert.ToInt32(dt.Rows[i]["OrderID"])
            ,ShipCity = Convert.ToString(dt.Rows[i]["ShipCity"])
            ,Freight = Convert.ToDecimal(dt.Rows[i]["Freight"])
            ,OrderDate = Convert.ToDateTime(dt.Rows[i]["OrderDate"]).ToShortDateString()
        });
    }
    return orders;
}

private DataTable GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                return dt;

            }
        }
    }
}

VB.Net

Customer.vb

Imports System
Imports System.Collections.Generic
Imports System.Text

Public Class Customer

    Public Property CustomerID As String

    Public Property ContactName As String

    Public Property Address As String

    Public Property Orders As List(Of Order)
End Class

 Orders.vb

Imports System
Imports System.Collections.Generic
Imports System.Web

Public Class Order

    Public Property OrderID As Integer

    Public Property ShipCity As String

    Public Property Freight As Decimal

    Public Property OrderDate As String
End Class

Default.aspx.vb

Namespace

Imports System.Data
Imports System.Web.Script.Serialization
Imports System.Data.SqlClient

Code

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim customers As List(Of Customer) = New List(Of Customer)()
    Dim dt As DataTable = GetData("SELECT Top 3 CustomerID,ContactName,Address From Customers")
    For i As Integer = 0 To dt.Rows.Count - 1
        Dim customer As Customer = New Customer With {.CustomerID = Convert.ToString(dt.Rows(i)("CustomerId")), .ContactName = Convert.ToString(dt.Rows(i)("ContactName")), .Address = Convert.ToString(dt.Rows(i)("Address")), .Orders = GetOrders(Convert.ToString(dt.Rows(i)("CustomerId")))}
        customers.Add(customer)
    Next

    Dim json = New JavaScriptSerializer().Serialize(customers)
End Sub

Public Function GetOrders(ByVal customerId As String) As List(Of Order)
    Dim orders As List(Of Order) = New List(Of Order)()
    Dim dt As DataTable = GetData(String.Format("SELECT Top 2 OrderID,CustomerID,ShipCity,Freight,OrderDate FROM Orders Where CustomerID ='{0}'", customerId))
    For i As Integer = 0 To dt.Rows.Count - 1
        orders.Add(New Order With {.OrderID = Convert.ToInt32(dt.Rows(i)("OrderID")), .ShipCity = Convert.ToString(dt.Rows(i)("ShipCity")), .Freight = Convert.ToDecimal(dt.Rows(i)("Freight")), .OrderDate = Convert.ToDateTime(dt.Rows(i)("OrderDate")).ToShortDateString()})
    Next

    Return orders
End Function

Private Function GetData(ByVal query As String) As DataTable
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

Ouput

[
 {
  "CustomerID":"AAFKM"
  ,"ContactName":"Mudassar Khan"
  ,"Address":"ul. Filtrowa 68"
  ,"Orders":[]
 }
 ,
 {
  "CustomerID":"ALFKI"
  ,"ContactName":"Maria "
  ,"Address":"Obere Str. 57"
  ,"Orders":[
              {
               "OrderID":10643
               ,"ShipCity":"Berlin"
               ,"Freight":29.4600
               ,"OrderDate":"8/25/1997"
              }
             ,{
               "OrderID":10692
               ,"ShipCity":"Berlin"
               ,"Freight":61.0200
               ,"OrderDate":"10/3/1997"
               }
            ]
 },
 {
  "CustomerID":"ANATR"
  ,"ContactName":"Ana Trujillo "
  ,"Address":"Avda. de la Constitución 2222"
  ,"Orders":[
			  {
			   "OrderID":10308
			   ,"ShipCity":"México D.F."
			   ,"Freight":1.6100
			   ,"OrderDate":"9/18/1996"
			  }
			  ,{
			   "OrderID":10625
			   ,"ShipCity":"México D.F."
			   ,"Freight":43.9000
			   ,"OrderDate":"8/8/1997"
			   }
			]
 }
]