Nested JSON string Serialize using VB.Net without any DLL

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,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta