I am looking to properly describe column metadata in JSON which 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 or no columns, but where I need the columns to be created with a label and hopefully with a data type, even when I pass an empty table.
An 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"
}
]
}
An example of input with an empty table:
{
"BrokerID" : "998",
"AccountID" : "1313",
"Packages":[]
}
When I parse this 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.
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)
{
if (rowDataObj[col.ColumnName].Type != JTokenType.Null)//Skip if the Value is Null/Missing, especially for a non-nullable type.
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.
// Test with a null Value for a Non-Nullable DataType.
dt.Rows.Add(new object[] { 226, null, 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": null,
"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, , True, BLUE
500, 15000, True, PEACH
Fiddle: https://dotnetfiddle.net/GGrn9z
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With