Click here to Skip to main content
15,921,793 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

WayBillNo is Final outcome.

Pls Advice me
thank you
Maideen

Here is my code

What I have tried:

Json Should be like
JavaScript
{    "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": ""
        }     
]      }


ASP.NET
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
Posted
Comments
Sandeep Mewara 30-Dec-22 23:33pm    
What is the problem here? You have a defined data structure (I see a JSON and model object corresponding to it). Now you need to feed in the data from DB into it which would need code to:
1. retrieve all the data you want to use (from DB it wouldn't be nested - would be flat)
2. map the data retrieved into your model object (flat to hierarchical)
Maideen Abdul Kader 31-Dec-22 22:11pm    
Thank you for prompt reply Mr.Sandeep
when I run this code, I got the result like this
[{
"SenderName": "ABC",
"ShipmentServiceType": "Standard Delivery",
"RecipientName": "XYZ"
"ShipmentAddressFrom": [{
"SenderName": null,
"CompanyName": "ABC ENTERPRISE",
"UnitNumber": "6,",
"Address": "Jalan TP2,"
}],
"ShipmentAddressTo": [{
"CompanyName": "XYZ Enterprise",
"UnitNumber": "10, ",
"Address": "Jalan TP5, "
}],
"InsurancePurchase": [{
"ProductDescription": "The description of the item A for buying insurance",
"Quantity": "0.00",
"UnitPrice": "0.00"
}],
"WayBill": [{
"WayBillNo": ""
}]
}]

But actual Json needs to POST in API


{ "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": ""
}
] }

Pls advice me.
Thank you...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900