vb.net datatable Serialize to json

datatable, json, serialization, vb.net

Solution

Here is my solution:

Public Function GetJson() As String

    Dim dt As New System.Data.DataTable
    dt = CreateDataTable() 'here I retrive data from oracle DB
    Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
    Dim packet As New List(Of Dictionary(Of String, Object))()
    Dim row As Dictionary(Of String, Object) = Nothing

    Try
        Dim result() As DataRow = dt.Select("Parent_ = 'main'")
        Dim i As Integer
        For i = 0 To result.GetUpperBound(0)
            row = New Dictionary(Of String, Object)()
            If UCase(result(i)(2)) <> "HAS CHILD" Then
                row.Add(result(i)(0), result(i)(2))
            Else
                row.Add(result(i)(0), add_(dt, result(i)(0)))
            End If
            packet.Add(row)
        Next i

        Return serializer.Serialize(packet)
    Catch ex As Exception
        MsgBox(ex.ToString)
        Me.Close()
    End Try

End Function

Public Function add_(ByVal dt As System.Data.DataTable, ByVal parent_ As String) As Dictionary(Of String, Object)
    Dim row As Dictionary(Of String, Object) = Nothing
    Try
        Dim result() As DataRow = dt.Select("Parent_ = '" & parent_ & "'")
        Dim i As Integer
        row = New Dictionary(Of String, Object)()
        For i = 0 To result.GetUpperBound(0)

            If UCase(result(i)(2)) <> "HAS CHILD" Then
                row.Add(result(i)(0), result(i)(2))
            Else
                row.Add(result(i)(0), add_(dt, result(i)(0)))
            End If
        Next i
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
    Return row
End Function

Problem

I have this kind of table: I need to get this JSON (of course order could be any, structure/tree is most important): Data table can change, so serialization should be dynamic. I am working with vb.net and used this code: ``` Public Function GetJson() As String Dim dt As New System.Data.DataTable dt = CreateDataTable() 'here I retrive data from oracle DB Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer() Dim packet As New List(Of Dictionary(Of String, Object))() Dim row As Dictionary(Of String, Object) = Nothing For Each dr As DataRow In dt.Rows row = New Dictionary(Of String, Object)() For Each dc As DataColumn In dt.Columns row.Add(dc.ColumnName.Trim(), dr(dc)) Next packet.Add(row) Next Return serializer.Serialize(packet) End Function ``` But this code returns me bad json: `[{"NAME":"city","PARENT":"address","VALUE":"has child"},{"NAME":"coordinates","PARENT":"address","VALUE":"has child"},{"NAME":"street","PARENT":"address","VALUE":"has child"}.......` Can someone help me out in here?

Original source