VB.NET DataTable序列化为JSON。

Question

我有这样一张表: 我需要获取此JSON（当然，顺序可以是任何的，结构/树最为重要）: 数据表可能会变化，因此序列化应该是动态的。 我正在使用vb.net并使用了以下代码：

 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

但是这段代码返回的json格式不正确：[{"NAME":"city","PARENT":"address","VALUE":"has child"},{"NAME":"coordinates","PARENT":"address","VALUE":"has child"},{"NAME":"street","PARENT":"address","VALUE":"has child"}.......

有人能帮我解决这个问题吗？

- innspiron

Answer 1

“你别这样做”版本：

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

- Matthew Hudson

---

你会将其反序列化为字典，还是可以处理数据表？不过这行代码真棒。 - Feign

Answer 2

这是我的解决方案：

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

- innspiron