Hi sourabh,
There is no need to use StringBuilder.
Use Newtonsoft.Json.
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Newtonsoft.Json;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports Newtonsoft.Json
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
List<Customer> customers = new List<Customer>();
DataTable dt = GetData("SELECT Top 3 CustomerID,ContactName,Address From Customers");
for (int i = 0; i < dt.Rows.Count; i++)
{
Customer customer = new Customer
{
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);
}
string json = JsonConvert.SerializeObject(customers, Formatting.Indented);
File.WriteAllText(Server.MapPath("~/Json.json"), json);
}
public List<Order> GetOrders(string customerId)
{
List<Order> orders = new List<Order>();
DataTable dt = GetData(string.Format("SELECT Top 2 OrderID,CustomerID,ShipCity,Freight,OrderDate FROM Orders Where CustomerID ='{0}'", customerId));
for (int i = 0; i < dt.Rows.Count; i++)
{
orders.Add(new Order
{
OrderID = Convert.ToInt32(dt.Rows[i]["OrderID"]),
ShipCity = Convert.ToString(dt.Rows[i]["ShipCity"]),
Freight = Convert.ToDecimal(dt.Rows[i]["Freight"]),
OrderDate = Convert.ToDateTime(dt.Rows[i]["OrderDate"]).ToShortDateString()
});
}
return orders;
}
private DataTable GetData(string query)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
public class Customer
{
public string CustomerID { get; set; }
public string ContactName { get; set; }
public string Address { get; set; }
public List<Order> Orders { get; set; }
}
public class Order
{
public int OrderID { get; set; }
public string ShipCity { get; set; }
public decimal Freight { get; set; }
public string OrderDate { get; set; }
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim customers As List(Of Customer) = New List(Of Customer)()
Dim dt As DataTable = GetData("SELECT Top 3 CustomerID,ContactName,Address From Customers")
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 As String = JsonConvert.SerializeObject(customers, Formatting.Indented)
File.WriteAllText(Server.MapPath("~/Json.json"), json)
End Sub
Public Function GetOrders(ByVal customerId As String) As List(Of Order)
Dim orders As List(Of Order) = New List(Of Order)()
Dim dt As DataTable = GetData(String.Format("SELECT Top 2 OrderID,CustomerID,ShipCity,Freight,OrderDate FROM Orders Where CustomerID ='{0}'", customerId))
For i As Integer = 0 To dt.Rows.Count - 1
orders.Add(New Order With {
.OrderID = Convert.ToInt32(dt.Rows(i)("OrderID")),
.ShipCity = Convert.ToString(dt.Rows(i)("ShipCity")),
.Freight = Convert.ToDecimal(dt.Rows(i)("Freight")),
.OrderDate = Convert.ToDateTime(dt.Rows(i)("OrderDate")).ToShortDateString()})
Next
Return orders
End Function
Private Function GetData(ByVal query As String) As DataTable
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").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 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
Public Class Order
Public Property OrderID As Integer
Public Property ShipCity As String
Public Property Freight As Decimal
Public Property OrderDate As String
End Class
Generated Json file
[
{
"CustomerID": "ALFKI",
"ContactName": "Maria Anders",
"Address": "Obere Str. 57",
"Orders": [
{
"OrderID": 10643,
"ShipCity": "Berlin",
"Freight": 29.4600,
"OrderDate": "10/13/1997"
},
{
"OrderID": 10692,
"ShipCity": "Berlin",
"Freight": 61.0200,
"OrderDate": "10/13/1997"
}
]
},
{
"CustomerID": "ANATR",
"ContactName": "Ana Trujillo ",
"Address": "Avda. de la Constitución 2222",
"Orders": [
{
"OrderID": 10308,
"ShipCity": "México D.F.",
"Freight": 1.6100,
"OrderDate": "11/28/1997"
},
{
"OrderID": 10625,
"ShipCity": "México D.F.",
"Freight": 43.9000,
"OrderDate": "11/28/1997"
}
]
},
{
"CustomerID": "ANTON",
"ContactName": "Antonio Moreno ",
"Address": "Mataderos 2312",
"Orders": [
{
"OrderID": 10365,
"ShipCity": "México D.F.",
"Freight": 22.0000,
"OrderDate": "11/28/1997"
},
{
"OrderID": 10507,
"ShipCity": "México D.F.",
"Freight": 47.4500,
"OrderDate": "11/28/1997"
}
]
}
]
