innspiron innspiron - 23 days ago 16
Vb.net Question

vb.net datatable Serialize to json

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?

Answer

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
Comments