Deserialize to DataTable with Json.NET while keeping nested tokens as JSON

Question

I'm doing a simple

JsonConvert.DeserializeObject<DataTable>(result.ToString());

the source JSON might have nested objects. The converter creates nested DataTables. I'd like to convert only the root object to a DataTable, and store nested objects into a string column. Is that possible? I've looked at JsonSerializerSettings but I don't seem to see a relevant setting for that.

I could re-serialize the nested DataTable later, but that's more processing, and some nested objects are not consistent, and I end up with an ArgumentException during deserialization.

score 0 · Answer 1 · answered Jul 24 '19 at 17:04

You will need a custom DataTable converter to accomplish this. I adapted the one from this answer.

The main idea is to detect whether the property contains an array with if (rowDataObj[col.ColumnName].Type == JTokenType.Array). In that case, we simply use .ToString() in order to keep the original string.

class Program
{
    static void Main(string[] args)
    {
        var json = @"[{""Name"":""John"",""Age"":""22"",""Json"":[{""Prop"":1}]},"
                  + @"{""Name"":""Eric"",""Age"":""25"",""Json"":[{""Prop"":2}]},"
                  + @"{""Name"":""Joan"",""Age"":""38"",""Json"":[{""Prop"":3}]}]";
        var table = JsonConvert.DeserializeObject<DataTable>(json, 
            new CustomDataTableConverter());

        foreach (DataRow row in table.Rows)
        {
            Console.WriteLine($"{row["Name"]}, {row["Age"]}, {row["Json"]}");
        }
    }
}

public class CustomDataTableConverter : JsonConverter
{
    public override bool CanConvert(Type objectType) => (objectType == typeof(DataTable));
    public override bool CanWrite => false;

    public override object ReadJson(JsonReader reader, Type objectType, 
        object existingValue, JsonSerializer serializer)
    {
        var rowsArray = JArray.Load(reader);
        var metaDataObj = (JObject)rowsArray.First();
        var dt = new DataTable();

        foreach (var prop in metaDataObj.Properties())
        {
            dt.Columns.Add(prop.Name);
        }
        foreach (JObject rowDataObj in rowsArray)
        {
            var row = dt.NewRow();
            foreach (DataColumn col in dt.Columns)
            {
                if (rowDataObj[col.ColumnName].Type == JTokenType.Array)
                {
                    row[col] = rowDataObj[col.ColumnName].ToString(Formatting.None);
                }
                else
                {
                    row[col] = rowDataObj[col.ColumnName].ToObject(col.DataType);
                }
            }
            dt.Rows.Add(row);
        }
        return dt;
    }

    public override void WriteJson(JsonWriter writer, object value, 
        JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }
}

The result is:

John, 22, [{"Prop":1}]
Eric, 25, [{"Prop":2}]
Joan, 38, [{"Prop":3}]

Deserialize to DataTable with Json.NET while keeping nested tokens as JSON

1 Answers1