Convert Datatable into Json Object in Asp.Net

Question

I'm Joinnes from Indonesia. I want to ask about converting my datatable into json object. I'm using asp.net with Vb. How can i convert my datatable into Json Like this:

    {
"awb": "81903054216",
"detail":
{
"shipped_date": "23-06-2016 08:24:00",
"delivered_date": "24-06-2016 10:54:00",
"services_code": "reguler",
"actual_amount": 33150,
"weight": 1000,
"final_status": 200,
"sender": {
"name": "ABCD",
"addr": "JL. ABCD",
"zipcode": 12345,
"city": "XXXX",
"geoloc": "xxx.bbb, xxx.aaa"
},
"receiver":{
"name": "BCDA",
"addr": "JL. BCDA",
"zipcode": 12345,
"city": "XXXX",
"geoloc": "xxx.bbb, xxx.aaa"
},
"driver":{
"id": "940184928",
"name": "driver A",
"phone": "12303523941",
"photo": ".com/potonya.jpg"
}
},
"history": [
{
"date_time": "20-JUN-2016 12:54:00",
"city_name": "XXXX",
"status": "manifested",
"status_code": 101,
"note": "something something",
"receiver": ""
},
{
"date_time": "21-JUN-2016 10:54:00",
"city_name": "XXXX",
"status": "Pickup Failed",
"status_code": 151,
"note": "something 1",
"receiver": ""
},
{
"date_time": "23-JUN-2016 12:54:00",
"city_name": "XXXX",
"status": "Shipping",
"status_code": 100,
"note": "something 2",
"receiver": ""
},
{
"date_time": "24-JUN-2016 10:54:00",
"city_name": "YYYY",
"status": "Delivered",
"status_code": 200,
"note": "something 3",
"receiver": "SETYAWAN"
}
]
}

Thanks in advance for your response :)

Possible duplicate of [Convert datatable to JSON in C#](https://stackoverflow.com/questions/17398019/convert-datatable-to-json-in-c-sharp) — Naveen Gogineni, May 30 '17 at 05:30
This code snippet from [Convert Datatable to JSON String in C#, VB.NET](http://www.aspdotnet-suresh.com/2013/05/c-convert-datatable-to-json-string-in-c.html)might help you. — Naveen Gogineni, May 30 '17 at 05:32

score 1 · Answer 1 · edited Mar 07 '23 at 01:50

for VB

 Public Function ConvertDataTabletoString() As String
    Dim dt As New DataTable()
    Using con As New SqlConnection("Data Source=SureshDasari;Initial Catalog=master;Integrated Security=true")
       Using cmd As New SqlCommand("select title=City,lat=latitude,lng=longitude,description from LocationDetails", con)
       con.Open()
       Dim da As New SqlDataAdapter(cmd)
       da.Fill(dt)
       Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
       Dim rows As New List(Of Dictionary(Of String, Object))()
       Dim row As Dictionary(Of String, Object)
       For Each dr As DataRow In dt.Rows
          row = New Dictionary(Of String, Object)()
          For Each col As DataColumn In dt.Columns
             row.Add(col.ColumnName, dr(col))
          Next
          rows.Add(row)
       Next
       Return serializer.Serialize(rows)
    End Using
 End Using

End Function

for C#

 public string ConvertDataTabletoString()
    {
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Initial Catalog=master;Integrated Security=true"))
        {
            using (SqlCommand cmd = new SqlCommand("select title=City,lat=latitude,lng=longitude,description from LocationDetails", con))
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
                List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
                Dictionary<string, object> row;
                foreach (DataRow dr in dt.Rows)
                {
                    row = new Dictionary<string, object>();
                    foreach (DataColumn col in dt.Columns)
                    {
                        row.Add(col.ColumnName, dr[col]);
                    }
                    rows.Add(row);
                }
                return serializer.Serialize(rows);
            }
        }
    }

OP needs solution in VB.net :) Though it's correct way to convert datatable into JSON using C#.net. Nice. — Chirag, May 30 '17 at 05:34

score 1 · Answer 2 · answered May 30 '17 at 05:32

You can convert your datatable into JSON format by doing this.

Public Function GetJson(ByVal dt As DataTable) As String

     Return New JavaScriptSerializer().Serialize(From dr As DataRow In dt.Rows Select dt.Columns.Cast(Of DataColumn)().ToDictionary(Function(col) col.ColumnName, Function(col) dr(col)))

End Function

score 1 · Answer 3 · answered May 30 '17 at 12:25

1

So simple

First add nuget package Newtonsoft.json Than include it in your project like

using Newtonsoft.Json

Than Convert datatable to json like this

return JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented);

answered May 30 '17 at 12:25

Saurabh

1,505
6
20
37

Convert Datatable into Json Object in Asp.Net

3 Answers3