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
