如何在JSON中包含用于空DataTable的列元数据 [英] How to include column metadata in JSON for an empty DataTable
问题描述
如何在稍后由Newtonsoft解析以构建ac#DataTable的JSON中正确描述列元数据，通过这种方式，我希望解决一个问题，即获取一个既不需要行又需要没有列的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.
标准输入示例:
{
"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"， 包装":[] }
example for input with empty table: { "BrokerID" : "998", "AccountID" : "1313", "Packages":[] }
当我使用JsonConvert.DeserializeObject<DataTable>(params["Packages"]);进行解析时，我没有行，而且显然没有列，我正在寻找一种描述json正文中列元数据的方法.
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.
推荐答案
即使将TypeNameHandling设置为All，Json.Net附带的DataTableConverter也不会输出列元数据.但是，没有什么可以阻止您制作自己的自定义转换器来执行此操作，而是使用该自定义转换器.这是我根据您的需求拼凑而成的:
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;
}
}
这是一个演示.请注意，序列化表时，列类型被写为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
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; }
}
输出:
{
"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
提琴: https://dotnetfiddle.net/2TwpLf
这篇关于如何在JSON中包含用于空DataTable的列元数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
