Ben Guri Ben Guri - 5 months ago 67
JSON Question

How to include column metadata in JSON for an empty DataTable

How to properly describe column metadata in a JSON that is later parsed by Newtonsoft to build a c# DataTable, in this way I'm hoping to solve a problem of getting a DataTable with no rows but also with no columns where I need the columns to be created with a label and hopefully with a datatype, when I pass an empty table.

example for standard input:

{

"BrokerID" : "998",
"AccountID" : "1313",
"Packages":[
{

"PackageID": 226,
"Amount": 15000,
"Auto_sync": true,
"Color": "BLUE"

},
{

"PackageID": 500,
"Amount": 15000,
"Auto_sync": true,
"Color": "PEACH"

}

]
}


example for input with empty table:
{
"BrokerID" : "998",
"AccountID" : "1313",
"Packages":[]
}

when i parse using
JsonConvert.DeserializeObject<DataTable>(params["Packages"]);
I get no rows and obviously no columns, I'm looking for a way to describe the columns metadata in the json's body.

Answer

The DataTableConverter that ships with Json.Net does not output column metadata, even if you set TypeNameHandling to All. However, there's nothing to prevent you from making your own custom converter that does this, and using that instead. Here's one I threw together that might suit your needs:

class CustomDataTableConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return (objectType == typeof(DataTable));
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        DataTable dt = (DataTable)value;
        JObject metaDataObj = new JObject();
        foreach (DataColumn col in dt.Columns)
        {
            metaDataObj.Add(col.ColumnName, col.DataType.AssemblyQualifiedName);
        }
        JArray rowsArray = new JArray();
        rowsArray.Add(metaDataObj);
        foreach (DataRow row in dt.Rows)
        {
            JObject rowDataObj = new JObject();
            foreach (DataColumn col in dt.Columns)
            {
                rowDataObj.Add(col.ColumnName, JToken.FromObject(row[col]));
            }
            rowsArray.Add(rowDataObj);
        }
        rowsArray.WriteTo(writer);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        JArray rowsArray = JArray.Load(reader);
        JObject metaDataObj = (JObject)rowsArray.First();
        DataTable dt = new DataTable();
        foreach (JProperty prop in metaDataObj.Properties())
        {
            dt.Columns.Add(prop.Name, Type.GetType((string)prop.Value, throwOnError: true));
        }
        foreach (JObject rowDataObj in rowsArray.Skip(1))
        {
            DataRow row = dt.NewRow();
            foreach (DataColumn col in dt.Columns)
            {
                row[col] = rowDataObj[col.ColumnName].ToObject(col.DataType);
            }
            dt.Rows.Add(row);
        }
        return dt;
    }
}

Here is a demo. Notice that the column types are written out as the first row of the array in the JSON when the table is serialized. On deserialization, this metadata is used to reconstruct the table with the correct column types and names, even if there are no other rows. (You can verify this by commenting out the two lines at the top where the row data is added to the table.)

class Program
{
    static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("PackageID", typeof(int));
        dt.Columns.Add("Amount", typeof(int));
        dt.Columns.Add("Auto_sync", typeof(bool));
        dt.Columns.Add("Color", typeof(string));
        // Comment out these two lines to see the table with no data
        dt.Rows.Add(new object[] { 226, 15000, true, "BLUE" });
        dt.Rows.Add(new object[] { 500, 15000, true, "PEACH" });

        Foo foo = new Foo
        {
            BrokerID = "998",
            AccountID = "1313",
            Packages = dt
        };

        JsonSerializerSettings settings = new JsonSerializerSettings();
        settings.Converters.Add(new CustomDataTableConverter());
        settings.Formatting = Formatting.Indented;

        string json = JsonConvert.SerializeObject(foo, settings);
        Console.WriteLine(json);
        Console.WriteLine();

        Foo foo2 = JsonConvert.DeserializeObject<Foo>(json, settings);
        Console.WriteLine("BrokerID: " + foo2.BrokerID);
        Console.WriteLine("AccountID: " + foo2.AccountID);
        Console.WriteLine("Packages table:");
        Console.WriteLine("  " + string.Join(", ", 
            foo2.Packages.Columns
                .Cast<DataColumn>()
                .Select(c => c.ColumnName + " (" + c.DataType.Name + ")")));

        foreach (DataRow row in foo2.Packages.Rows)
        {
            Console.WriteLine("  " + string.Join(", ", row.ItemArray
                .Select(v => v != null ? v.ToString() : "(null)")));
        }
    }
}

class Foo
{
    public string BrokerID { get; set; }
    public string AccountID { get; set; }
    public DataTable Packages { get; set; }
}

Output:

{
  "BrokerID": "998",
  "AccountID": "1313",
  "Packages": [
    {
      "PackageID": "System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
      "Amount": "System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
      "Auto_sync": "System.Boolean, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
      "Color": "System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    },
    {
      "PackageID": 226,
      "Amount": 15000,
      "Auto_sync": true,
      "Color": "BLUE"
    },
    {
      "PackageID": 500,
      "Amount": 15000,
      "Auto_sync": true,
      "Color": "PEACH"
    }
  ]
}

BrokerID: 998
AccountID: 1313
Packages table:
  PackageID (Int32), Amount (Int32), Auto_sync (Boolean), Color (String)
  226, 15000, True, BLUE
  500, 15000, True, PEACH

Fiddle: https://dotnetfiddle.net/2TwpLf

Comments