如何在 JSON 中为空 DataTable 包含列元数据 [英] How to include column metadata in JSON for an empty DataTable
问题描述
我希望在 JSON 中正确描述列元数据，稍后 Newtonsoft 会对其进行解析以构建 C# DataTable.通过这种方式，我希望解决获得没有行或没有列的 DataTable 的问题，但是我需要使用标签创建列并希望使用数据类型，甚至当我经过一张空桌子时.
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.
标准输入示例:
{
"BrokerID": "998",
"AccountID": "1313",
"Packages": [
{
"PackageID": 226,
"Amount": 15000,
"Auto_sync": true,
"Color": "BLUE"
},
{
"PackageID": 500,
"Amount": 15000,
"Auto_sync": true,
"Color": "PEACH"
}
]
}
空表输入示例:
{
"BrokerID" : "998",
"AccountID" : "1313",
"Packages":[]
}
当我使用 JsonConvert.DeserializeObject<DataTable>(params["Packages"]); 解析它时，我没有得到任何行，显然也没有列.我正在寻找一种方法来描述 JSON 正文中的列元数据.
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.
推荐答案
Json.Net自带的DataTableConverter不输出列元数据，即使你设置了TypeNameHandling全部.但是，没有什么可以阻止您制作自己的自定义转换器来执行此操作，并改为使用它.这是我整理的一个可能适合您的需求:
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;
}
}
这是一个演示.请注意，当表被序列化时，列类型将作为 JSON 中数组的第一行写出.在反序列化时，此元数据用于使用正确的列类型和名称重建表，即使没有其他行也是如此.(您可以通过注释掉将行数据添加到表中的顶部的两行来验证这一点.)
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; }
}
输出:
{
"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
小提琴:https://dotnetfiddle.net/GGrn9z
这篇关于如何在 JSON 中为空 DataTable 包含列元数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
