convert a list of datatable to json

Question

I have this function in ASP.NET to convert a DataTable to JSON string:

Public Function GetJson(ByVal dt As DataTable) As String
    Dim serializer As System.Web.Script.Serialization.JavaScriptSerializer = New System.Web.Script.Serialization.JavaScriptSerializer()
    Dim rows As New List(Of Dictionary(Of String, Object))
    Dim row As Dictionary(Of String, Object)
    Try
        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)
    Catch ex As Exception
        logFile("SP GetJson ----" + ex.Message)
        Return "-1"
    End Try
End Function

my json would be like that:

 {
"category": [
    {
        "id": "1",
        "desc": "default",
    },
    {
        "id": "2",
        "desc": "fun",
    }
],
"images": [
    {
        "image ID": "1",
        "link": "images/logo.jpg"
        "category": "1"
    },
    {
        "image ID": "2",
        "link": "images/logo2.jpg"
        "category": "2"
    }
]
}

but now I have a list of 2 DataTables that I need to convert it to one JSON string that has 2 arrays, any ideas?

Answer 1

The structure you are looking for is a Dictionary of Lists that are created now. Try to deserialize your structure (the json string is corrected)

Dim serializer As New Web.Script.Serialization.JavaScriptSerializer()
Dim target As String = "{'category':[{'id':'1','desc':'default'},{'id':'2','desc':'fun'}],'images':[{'imageID':'1','link':'images/logo.jpg','category':'1'},{'imageID':'2','link':'images/logo2.jpg','category':'2'}]}"
Dim Desired = serializer.Deserialize(Of Object)(target)

Desired is now the structure I need to create to get required JSON string. Now I create tables (They are in a DataSet and have names present in JSON)

Dim Ds As New DataSet
For Each kvp As KeyValuePair(Of String, Object) In Desired
    Dim tbl As New DataTable(kvp.Key)
    Ds.Tables.Add(tbl)
    For Each drow As Dictionary(Of String, Object) In kvp.Value
        If tbl.Rows.Count = 0 Then
            For Each Name As String In drow.Keys
                tbl.Columns.Add(Name, GetType(Object))
            Next
        End If
        Dim tblRow As DataRow = tbl.NewRow
        For Each fld As KeyValuePair(Of String, Object) In drow
            tblRow(fld.Key) = fld.Value
        Next
        tbl.Rows.Add(tblRow)
    Next
Next

So far it I was just preparing data. Following is the desired answer.

Now I create the structure and serialize it.

Dim Result As New Dictionary(Of String, Object)
For Each tbl As DataTable In Ds.Tables
    Result.Add(tbl.TableName, GetRows(tbl))
Next

Dim serialized As String = serializer.Serialize(Result)

GetRows is modified from GetJson

Public Function GetRows(ByVal dt As DataTable) As List(Of Dictionary(Of String, Object))
    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 rows
End Function

Answer 2

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim serializer As System.Web.Script.Serialization.JavaScriptSerializer = New System.Web.Script.Serialization.JavaScriptSerializer()
    Dim rows As New List(Of Dictionary(Of String, Object))
    GetJson(dt1, rows)
    GetJson(dt2, rows)
    Dim str As String = serializer.Serialize(rows)

End Sub

Public Sub GetJson(ByVal dt As DataTable, ByRef rows As List(Of Dictionary(Of String, Object)))

    Try
        Dim row As New Dictionary(Of String, Object)
        For Each dr As DataRow In dt.Rows
            For Each col As DataColumn In dt.Columns
                row.Add(col.ColumnName, dr(col))
            Next
            rows.Add(row)
        Next
    Catch ex As Exception
        logFile("SP GetJson ----" + ex.Message)
    End Try
End Sub