2

I am serializing a datatable like this in VB.net using JSON.NET, and it's working fine:

ResultJSONString = JsonConvert.SerializeObject(MyDataTable)

The problem is that I added another column, (named JSON_Column), to the data table which has JSON strings as values, and now when I serialize the datatable, the SerializeObject method escapes my quotes in the JSON strings in that column with backslashes, so I end up with JSON that looks like this:

"ColumnA": "Value1",
"JSON_Column": "{\"SomePropertyName\":\"SomeValue\"}"

How can I tell the JSONConvert.SerializeObject method that the string values in the JSON_Column of my datatable should NOT be escaped because they are JSON themselves?

In other words, I want this as the result:

"ColumnA": "Value1",
"JSON_Column": {"SomePropertyName": "SomeValue"}

It's somewhat similar to this question, but I am serializing from a datatable, not a custom object, so I can't use that method.

Thanks for your help!

2
  • JSON_Column contains a string, not an object which is why it is escaped, the string contains double-quotes. You need to serialize it as an object, not a string. Commented Jun 14, 2013 at 20:23
  • Yes, I agree with you that is the problem - my whole question asks: how do I do that when the JSON is in a datatable? Is there a way to let the JSON.NET serializer know that column should be treated as an object and not a string? Commented Jun 14, 2013 at 23:47

1 Answer 1

4

You'll need to write a custom converter to convert that DataTable to a JSON string. You can only tell JsonConvert how to convert types so you'll need to provide a converter for DataTable.

A DataTable is serialized as an array of objects with a property corresponding to each of the values in a row. You'll want to deserialize the JSON_Column column first so it is treated as an object and not a string. Once you created this new representation of your DataTable, you can let the serializer take care of the rest.

Here's how you could write such a converter:

Class MyDataTableConverter
    Inherits JsonConverter

    Public Overrides Function CanConvert(type As Type) As Boolean
        Return type = GetType(DataTable)
    End Function

    Public Overrides Sub WriteJson(writer As JsonWriter, value As Object, serializer As JsonSerializer)
        Dim dt = TryCast(value, DataTable)
        If dt IsNot Nothing Then
            Dim rowSerializer = New MyRowSerializer
            Dim obj = New JArray(
                From row In dt.Rows.Cast(Of DataRow)
                Select rowSerializer.Serialize(dt, row.ItemArray)
            )
            serializer.Serialize(writer, obj)
        End If
    End Sub

    Public Overrides Function ReadJson(reader As JsonReader, objectType As Type, existingValue As Object, serializer As JsonSerializer) As Object
        If existingValue Is Nothing Then
            Dim dt = CreateTable
            Dim rowSerializer = New MyRowSerializer
            Dim arr = serializer.Deserialize(Of JArray)(reader)
            For Each obj In arr.Cast(Of JObject)
                dt.Rows.Add(rowSerializer.Deserialize(dt, obj))
            Next
            Return dt
        End If
        Return existingValue
    End Function

    Private Function CreateTable() As DataTable
        Dim dt = New DataTable
        dt.Columns.Add("ColumnA", GetType(String))
        dt.Columns.Add("JSON_Column", GetType(String))
        Return dt
    End Function

    Class MyRowSerializer

        Public Function Serialize(table As DataTable, values As Object()) As JObject
            return New JObject(
                From x in table.Columns.Cast(Of DataColumn).Zip(values, Function(col, value) New With { col.ColumnName, .ColumnValue = value })
                Let value = SerializeColumn(x.ColumnName, x.ColumnValue)
                Select New JProperty(x.ColumnName, value)
            )
        End Function

        Private Function SerializeColumn(name As String, value As Object) As Object
            Select Case name
                Case "JSON_Column"
                    Return JsonConvert.DeserializeObject(DirectCast(value, String))
                Case Else
                    Return value
            End Select
        End Function

        Public Function Deserialize(table As DataTable, obj As JObject) As Object()
            Dim values = From col In table.Columns.Cast(Of DataColumn)
                         Let columnName = col.ColumnName
                         Let value = obj(columnName)
                         Select DeserializeColumn(columnName, value)
            return values.ToArray
        End Function

        Private Function DeserializeColumn(name As String, value As Object) As Object
            Select Case name
                Case "JSON_Column"
                    Return JsonConvert.SerializeObject(value)
                Case Else
                    Return value
            End Select
        End Function

    End Class

End Class

Then serialize your table passing in the converter as an argument.

Dim json = JsonConvert.SerializeObject(dataTable, New MyDataTableConverter)
Sign up to request clarification or add additional context in comments.

8 Comments

Jeff- thanks for the answer, I seems like it is what I need. I'm a VB guy, so I'll do the translation and let you know if I have any questions. Thanks again for your detailed answer!
Ah sorry, I fully intended to write a VB version but forgot. But hopefully the translation is pretty straight-forward.
I rewrote it as VB code. My VB is a bit rusty but I believe it is equivalent.
if i have 2 datatables and i need to convert them in one json string but i need each datatable in json array what shall i do?
@JocelyneElKhoury: In that case, you probably don't want an array, but a mapping (an object). Just place your datatables into a dictionary or maybe even a DataSet and serialize. You can give key the tables as you want with a dictionary. A dataset will use the names of the tables as keys.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.