How do I generate Nested Json using MSSQL 2019 Datatable.?

Question

Hi   
I have an issue in nested Json from MSSQL datatable.  
I am using normal Json (not nested ). It works fine.  
But nested, I could not do.  
  
Pls Advice me  
thank you  
Maideen  

Format should be like below

{    "Shipment":  [  
        {  
            "ShipmentServiceType": "Standard Delivery",  
            "SenderName": "ABC",  
            "RecipientName": "XYZ",          
              
			"ShipmentAddressFrom":  {  
                "CompanyName": "ABC ENTERPRISE",  
                "UnitNumber": "6, ",  
                "Address": "Jalan TP2, "  
   
            },  
            "ShipmentAddressTo":   {  
                "CompanyName": "XYZ Enterprise",  
                "UnitNumber": "10, ",  
                "Address": "Jalan TP5, "  
            },  
  
	     "InsurancePurchase" : [  
			  {  
				  "ProductDescription":"The description of the item .",  
				  "Quantity":	1,		  
				  "UnitPrice":	 110	  
			   }],  
		 "WayBill": [  
                {  
                    "WayBillNo": ""  
                },  
            ],  
            "DONumber": ""  
        }       
]      }  

	Public Class Shipment  
        Public Property SenderName As String  
        Public Property ShipmentServiceType As String  
        Public Property RecipientName As String  
        Public Property ShipmentAddressFrom As List(Of ShipmentAddressFrom)  
        Public Property ShipmentAddressTo As List(Of ShipmentAddressTo)  
		Public Property InsurancePurchase As List(Of InsurancePurchase)  
        Public Property WayBill As List(Of WayBill)  
  
    End Class  
	  
    Public Class ShipmentAddressFrom  
        Public Property CompanyName As String  
        Public Property UnitNumber As String  
        Public Property Address As String  
    End Class	  
	  
    Public Class ShipmentAddressTo  
        Public Property CompanyName As String  
        Public Property UnitNumber As String  
        Public Property Address As String  
    End Class	  
    Public Class InsurancePurchase  
        Public Property ProductDescription As String  
        Public Property UnitPrice As String  
    End Class	  
    Public Class WayBill  
        Public Property WayBillNo As String  
    End Class	  
	  
	  
    Private Sub LoadLineClearData()  
        Dim Shipments As List(Of Shipment) = New List(Of Shipment)()  
        Dim dt As DataTable = GetData("select * from LineClear_Data")  
        For i As Integer = 0 To dt.Rows.Count - 1  
            Dim Shipment As Shipment = New Shipment With  
                {.ShipmentServiceType = Convert.ToString(dt.Rows(i)("ShipmentServiceType")),  
                 .SenderName = Convert.ToString(dt.Rows(i)("SenderName")),  
                 .RecipientName = Convert.ToString(dt.Rows(i)("RecipientName")),  
                 .ShipmentAddressFrom = ShipmentAddressFrom(Convert.ToString(dt.Rows(i)("SenderName")),  
                 .ShipmentAddressTo = ShipmentAddressTo(Convert.ToString(dt.Rows(i)("SenderName")),  
				 .InsurancePurchase = InsurancePurchase(Convert.ToString(dt.Rows(i)("SenderName")),  
                 .WayBill = WayBill(Convert.ToString(dt.Rows(i)("SenderName"))}  
            Shipments.Add(Shipment)  
        Next  
        Dim json = New JavaScriptSerializer().Serialize(Shipments)  
        Me.txtJson.Text = json  
    End Sub	  
	  
	  
	  
    Public Function ShipmentAddressFrom(ByVal customerId As String) As List(Of ShipmentAddressFrom)  
        Dim ShipmentAddressFroms As List(Of ShipmentAddressFrom) = New List(Of ShipmentAddressFrom)()  
        Dim dt As DataTable = GetData(String.Format("SELECT * FROM LineClear_Data Where SenderName ='{0}'", customerId))  
        For i As Integer = 0 To dt.Rows.Count - 1  
            ShipmentAddressFroms.Add(New ShipmentAddressFrom With  
                                    {.CompanyName = Convert.ToString(dt.Rows(i)("From_CompanyName")),  
                                     .UnitNumber = Convert.ToString(dt.Rows(i)("From_UnitNumber")),  
                                     .Address = Convert.ToString(dt.Rows(i)("From_Address"))})  
  
        Next  
        Return ShipmentAddressFroms  
    End Function  
  
	  
	  
    Public Function ShipmentAddressTo(ByVal customerId As String) As List(Of ShipmentAddressTo)  
        Dim ShipmentAddressTos As List(Of ShipmentAddressTo) = New List(Of ShipmentAddressTo)()  
  
        Dim dt As DataTable = GetData(String.Format("SELECT * FROM LineClear_Data Where SenderName ='{0}'", customerId))  
        For i As Integer = 0 To dt.Rows.Count - 1  
            ShipmentAddressTos.Add(New ShipmentAddressTo With  
                                    {.CompanyName = Convert.ToString(dt.Rows(i)("To_CompanyName")),  
                                     .UnitNumber = Convert.ToString(dt.Rows(i)("To_UnitNumber")),  
                                     .Address = Convert.ToString(dt.Rows(i)("To_Address"))})  
        Next  
        Return ShipmentAddressTos  
    End Function	  
	  
	  
	//***  WayBillNo is Final outcome.  
	  
    Public Function WayBill(ByVal customerId As String) As List(Of WayBill)  
        Dim WayBills As List(Of WayBill) = New List(Of WayBill)()  
        Dim dt As DataTable = GetData(String.Format("SELECT * FROM LineClear_Data Where SenderName ='{0}'", customerId))  
        For i As Integer = 0 To dt.Rows.Count - 1  
            WayBills.Add(New WayBill With  
                                    {.WayBillNo = Convert.ToString(dt.Rows(i)("WB1_WayBillNo"))})  
        Next  
        Return WayBills  
    End Function	  
	  
	  
	  
	  
    Public Function InsurancePurchase(ByVal customerId As String) As List(Of InsurancePurchase)  
        Dim InsurancePurchases As List(Of InsurancePurchase) = New List(Of InsurancePurchase)()  
        Dim dt As DataTable = GetData(String.Format("SELECT * FROM LineClear_Data Where SenderName ='{0}'", customerId))  
        For i As Integer = 0 To dt.Rows.Count - 1  
  
            InsurancePurchases.Add(New InsurancePurchase With  
                                    {.ProductDescription = Convert.ToString(dt.Rows(i)("IP1_ProductDescription")),  
                                     .UnitPrice = Convert.ToDecimal(dt.Rows(i)("IP1_UnitPrice"))})  
        Next  
        Return InsurancePurchases  
    End Function	  

Answer 1

Hi @SILS ,
According to the nested json format you provided, you need to add a field (ie: Shipment).
Specifically, you can refer to the following code:

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
        Dim shipments As List(Of Test) = New List(Of Test)()  
        Dim dt As DataTable = GetData("SELECT * From LineClear_Data")  
        For i As Integer = 0 To dt.Rows.Count - 1  
            Dim all As Test = New Test With {.Shipment = GetShipment(Convert.ToString(dt.Rows(i)("shipment")))}  
            shipments.Add(all)  
        Next  
  
        Dim json = New JavaScriptSerializer().Serialize(shipments)  
        Response.Write(json)  
  
    End Sub  
  
    Public Function GetShipment(ByVal shipment As String) As List(Of Shipment)  
        Dim shipments As List(Of Shipment) = New List(Of Shipment)()  
        Dim dt As DataTable = GetData("SELECT * From LineClear_Data")  
        For i As Integer = 0 To dt.Rows.Count - 1  
            Dim all As Shipment = New Shipment With {  
                .SenderName = Convert.ToString(dt.Rows(i)("SenderName")),  
                .ShipmentServiceType = Convert.ToString(dt.Rows(i)("ShipmentServiceType")),  
                .RecipientName = Convert.ToString(dt.Rows(i)("RecipientName")),  
                .ShipmentAddressFrom = GetShipmentAddressFroms(Convert.ToString(dt.Rows(i)("shipmentAddressFrom"))),  
                .ShipmentAddressTo = GetShipmentAddressTo(Convert.ToString(dt.Rows(i)("shipmentAddressTo"))),  
                .InsurancePurchase = GetInsurancePurchase(Convert.ToString(dt.Rows(i)("insurancePurchase"))),  
                .WayBill = GetWayBill(Convert.ToString(dt.Rows(i)("wayBill")))}  
            shipments.Add(all)  
        Next  
  
        Return shipments  
    End Function  
    Public Function GetShipmentAddressFroms(ByVal shipmentAddressFrom As String) As List(Of ShipmentAddressFrom)  
        Dim shipmentAddressFroms As List(Of ShipmentAddressFrom) = New List(Of ShipmentAddressFrom)()  
        Dim dt As DataTable = GetData(String.Format("SELECT * FROM ShipmentAddressFrom Where SenderName ='{0}'", shipmentAddressFrom))  
        For i As Integer = 0 To dt.Rows.Count - 1  
            shipmentAddressFroms.Add(New ShipmentAddressFrom With {  
                                     .CompanyName = Convert.ToString(dt.Rows(i)("CompanyName")),  
                                     .UnitNumber = Convert.ToString(dt.Rows(i)("UnitNumber")),  
                                     .Address = Convert.ToString(dt.Rows(i)("Address"))})  
        Next  
  
        Return shipmentAddressFroms  
    End Function  
    Public Function GetShipmentAddressTo(ByVal shipmentAddressTos As String) As List(Of ShipmentAddressTo)  
        Dim shipmentAddressToss As List(Of ShipmentAddressTo) = New List(Of ShipmentAddressTo)()  
        Dim dt As DataTable = GetData(String.Format("SELECT * FROM ShipmentAddressFrom Where SenderName ='{0}'", shipmentAddressTos))  
        For i As Integer = 0 To dt.Rows.Count - 1  
            shipmentAddressToss.Add(New ShipmentAddressTo With {  
                                     .CompanyName = Convert.ToString(dt.Rows(i)("CompanyName")),  
                                     .UnitNumber = Convert.ToString(dt.Rows(i)("UnitNumber")),  
                                     .Address = Convert.ToString(dt.Rows(i)("Address"))})  
        Next  
  
        Return shipmentAddressToss  
    End Function  
    Public Function GetInsurancePurchase(ByVal insurancePurchase As String) As List(Of InsurancePurchase)  
        Dim insurancePurchases As List(Of InsurancePurchase) = New List(Of InsurancePurchase)()  
        Dim dt As DataTable = GetData(String.Format("SELECT ProductDescription,UnitPrice FROM LineClear_Data Where SenderName ='{0}'", insurancePurchase))  
        For i As Integer = 0 To dt.Rows.Count - 1  
            insurancePurchases.Add(New InsurancePurchase With {  
                                     .ProductDescription = Convert.ToString(dt.Rows(i)("ProductDescription")),  
                                     .UnitPrice = Convert.ToString(dt.Rows(i)("UnitPrice"))})  
  
        Next  
        Return insurancePurchases  
    End Function  
    Public Function GetWayBill(ByVal wayBill As String) As List(Of WayBill)  
        Dim wayBills As List(Of WayBill) = New List(Of WayBill)()  
        Dim dt As DataTable = GetData(String.Format("SELECT WayBillNo FROM LineClear_Data Where SenderName ='{0}'", wayBill))  
        For i As Integer = 0 To dt.Rows.Count - 1  
            wayBills.Add(New WayBill With {.WayBillNo = Convert.ToString(dt.Rows(i)("WayBillNo"))})  
        Next  
        Return wayBills  
    End Function  
  
    Private Function GetData(ByVal query As String) As DataTable  
        Dim conString As String = ConfigurationManager.ConnectionStrings("DBCS").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  

---

Public Class Test  
    Public Property Shipment As List(Of Shipment)  
End Class  
  
  
Public Class Shipment  
    Public Property SenderName As String  
    Public Property ShipmentServiceType As String  
    Public Property RecipientName As String  
    Public Property ShipmentAddressFrom As List(Of ShipmentAddressFrom)  
    Public Property ShipmentAddressTo As List(Of ShipmentAddressTo)  
    Public Property InsurancePurchase As List(Of InsurancePurchase)  
    Public Property WayBill As List(Of WayBill)  
  
End Class  
  
Public Class ShipmentAddressFrom  
    Public Property CompanyName As String  
    Public Property UnitNumber As String  
    Public Property Address As String  
End Class  
  
Public Class ShipmentAddressTo  
    Public Property CompanyName As String  
    Public Property UnitNumber As String  
    Public Property Address As String  
End Class  
Public Class InsurancePurchase  
    Public Property ProductDescription As String  
    Public Property UnitPrice As String  
End Class  
Public Class WayBill  
    Public Property WayBillNo As String  
End Class  

Result

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.