Nested JSON string Serialize using VB.Net without any DLL
First create two SQL tables Customers and Orders
CREATE TABLE Customers
(CustomerID VARCHAR(10),
ContactName VARCHAR(10),
[Address] VARCHAR(10)
)
INSERT INTO Customers VALUES ('C001','ARKA','KOLKATA')
INSERT INTO Customers VALUES ('C002','ROSHAN','MUMBAI')
INSERT INTO Customers VALUES ('C003','SAYAK','DELHI')
INSERT INTO Customers VALUES ('C004','RAJA','CHENNAI')
INSERT INTO Customers VALUES ('C005','PRASANTA','MUMBAI')
SELECT * FROM Customers
CREATE TABLE Orders
(OrderID INT,
CustomerID VARCHAR(10),
ShipCity VARCHAR(10),
Freight NUMERIC(16,2),
OrderDate DATETIME
)
INSERT INTO Orders VALUES (1001,'C001','K1',100.00,'20200210')
INSERT INTO Orders VALUES (1002,'C001','K2',110.00,'20200211')
INSERT INTO Orders VALUES (1003,'C001','K3',120.00,'20200212')
INSERT INTO Orders VALUES (2001,'C002','M1',130.00,'20200213')
INSERT INTO Orders VALUES (2002,'C002','M2',140.00,'20200214')
INSERT INTO Orders VALUES (2003,'C002','M3',150.00,'20200215')
INSERT INTO Orders VALUES (2004,'C002','M4',160.00,'20200216')
INSERT INTO Orders VALUES (4001,'C004','C1',100.00,'20200210')
INSERT INTO Orders VALUES (4002,'C004','C2',130.00,'20200213')
INSERT INTO Orders VALUES (4003,'C004','C3',170.00,'20200217')
INSERT INTO Orders VALUES (4004,'C004','C4',180.00,'20200218')
INSERT INTO Orders VALUES (5001,'C005','M1',100.00,'20200210')
INSERT INTO Orders VALUES (5002,'C005','M2',130.00,'20200213')
INSERT INTO Orders VALUES (5003,'C005','M3',180.00,'20200218')
INSERT INTO Orders VALUES (5004,'C005','M4',190.00,'20200219')
INSERT INTO Orders VALUES (5005,'C005','M5',200.00,'20200220')
SELECT * FROM Orders
SQL Output :
Now write VB.Net code to get JSON seriliaze string.
Imports System
Imports System.IO
Imports
System.Data
Imports
System.IO.File
Imports
System.Data.SqlClient
Imports
System.Data.OleDb
Imports
System.Data.SqlClient.SqlDataAdapter
Imports
System.Text
Imports
System.Runtime.CompilerServices
Imports
System.Collections.Generic
Imports
System.Web.Script.Serialization
Imports
System.Text.RegularExpressions
Imports
System.Reflection
Imports
System.Configuration
Public Class JSON_Frm
Dim JSONstrBldr = New
StringBuilder()
Private Sub btn_JSON_Click(sender As
System.Object, e As
System.EventArgs) Handles
btn_JSON.Click
JSONstrBldr = ""
Dim
customers As List(Of Customer) = New List(Of Customer)()
Try
Dim
dt As New DataTable
dt.Clear()
sql = "SELECT
* From Customers"
da = New
System.Data.SqlClient.SqlDataAdapter(sql,
con)
da.Fill(dt)
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()
With {.MaxJsonLength = 50000000}
JSONstrBldr =
json.Serialize(customers)
Try
Clipboard.SetDataObject(JSONstrBldr)
'open
notepad
System.Diagnostics.Process.Start("notepad.exe")
System.Threading.Thread.Sleep(250)
'paste
from clipboard to notepad
Clipboard.GetDataObject()
SendKeys.Send("^V")
Catch
ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "System
ERROR Message")
End
Try
Catch
ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Partial 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
Partial Public Class Order
Public Property OrderID As Long
Public Property ShipCity As String
Public Property Freight As Double
Public Property OrderDate As
String
End Class
Public Function GetOrders(ByVal
customerId As String)
As List(Of Order)
Dim
orders As List(Of Order) = New List(Of Order)()
Fetchdatatable(String.Format("SELECT * FROM Orders Where CustomerID ='{0}'",
customerId))
For i As Integer = 0 To dt_cmn.Rows.Count - 1
orders.Add(New
Order With
{.OrderID = Convert.ToInt32(dt_cmn.Rows(i)("OrderID")), .ShipCity = Convert.ToString(dt_cmn.Rows(i)("ShipCity")), .Freight = Convert.ToDecimal(dt_cmn.Rows(i)("Freight")), .OrderDate = Convert.ToDateTime(dt_cmn.Rows(i)("OrderDate")).ToShortDateString()})
Next
Return
orders
End Function
End Class
JSON String Output:
[
{
"CustomerID": "C001",
"ContactName": "ARKA",
"Address": "KOLKATA",
"Orders": [
{
"OrderID": 1001,
"ShipCity": "K1",
"Freight": 100,
"OrderDate":
"10/02/2020"
},
{
"OrderID": 1002,
"ShipCity": "K2",
"Freight": 110,
"OrderDate":
"11/02/2020"
},
{
"OrderID": 1003,
"ShipCity": "K3",
"Freight": 120,
"OrderDate":
"12/02/2020"
}
]
},
{
"CustomerID": "C002",
"ContactName":
"ROSHAN",
"Address": "MUMBAI",
"Orders": [
{
"OrderID": 2001,
"ShipCity": "M1",
"Freight": 130,
"OrderDate":
"13/02/2020"
},
{
"OrderID": 2002,
"ShipCity": "M2",
"Freight": 140,
"OrderDate":
"14/02/2020"
},
{
"OrderID": 2003,
"ShipCity": "M3",
"Freight": 150,
"OrderDate":
"15/02/2020"
},
{
"OrderID": 2004,
"ShipCity": "M4",
"Freight": 160,
"OrderDate":
"16/02/2020"
}
]
},
{
"CustomerID": "C003",
"ContactName": "SAYAK",
"Address": "DELHI",
"Orders": []
},
{
"CustomerID": "C004",
"ContactName": "RAJA",
"Address": "CHENNAI",
"Orders": [
{
"OrderID": 4001,
"ShipCity": "C1",
"Freight": 100,
"OrderDate":
"10/02/2020"
},
{
"OrderID": 4002,
"ShipCity": "C2",
"Freight": 130,
"OrderDate":
"13/02/2020"
},
{
"OrderID": 4003,
"ShipCity": "C3",
"Freight": 170,
"OrderDate":
"17/02/2020"
},
{
"OrderID": 4004,
"ShipCity": "C4",
"Freight": 180,
"OrderDate":
"18/02/2020"
}
]
},
{
"CustomerID": "C005",
"ContactName":
"PRASANTA",
"Address": "MUMBAI",
"Orders": [
{
"OrderID": 5001,
"ShipCity": "M1",
"Freight": 100,
"OrderDate":
"10/02/2020"
},
{
"OrderID": 5002,
"ShipCity": "M2",
"Freight": 130,
"OrderDate":
"13/02/2020"
},
{
"OrderID": 5003,
"ShipCity": "M3",
"Freight": 180,
"OrderDate":
"18/02/2020"
},
{
"OrderID": 5004,
"ShipCity": "M4",
"Freight": 190,
"OrderDate":
"19/02/2020"
},
{
"OrderID": 5005,
"ShipCity": "M5",
"Freight": 200,
"OrderDate":
"20/02/2020"
}
]
}
]
N.B. Here I use some function to get SQL data, you may change it as per your coding.
If there is any other better option,
Thank You,
Arka Gupta.
