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

SILS 61 Reputation points
2022-12-31T04:09:45.007+00:00
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	  
ASP.NET API
ASP.NET API
ASP.NET: A set of technologies in the .NET Framework for building web applications and XML web services.API: A software intermediary that allows two applications to interact with each other.
147 questions
No comments
{count} votes

Accepted answer
  1. Lan Huang-MSFT 9,126 Reputation points Microsoft Employee
    2023-01-02T06:24:38.12+00:00

    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
    275324-image.png
    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.


0 additional answers

Sort by: Most helpful