我有一个包含 15 列的数据表，其中 2 列包含内部数据表，每列包含 5 列。是否可以打破层次结构并将内部数据表列添加到主数据表列？或者我可以将这 2 列从数据表中分离出来并将它们加载到不同的 sql server 表中吗？内部数据表是通过读取具有多个值的 JSONArray 对象形成的。
我使用以下代码部分将 JSON 反序列化为数据表
var dtset = JsonConvert.DeserializeObject<DataTable>(JSON);
我的 JSON 如下所示，
[{
"cpty": "SG, LP",
"internallegalentity": "ABC, LN",
"createdate": null,
"paymentcurrency": "USD",
"id": 123,
"version": 1,
"revisiondate": "2020-01-31 12:33:39.714",
"amount": -4.25,
"duedate": "2020-01-29",
"asofdate": "2020-02-25",
"isdeleted": false,
"source": "XYZ",
"isprovisional": null,
"isproforma": null,
"items": [{
"id": 123,
"amount": -4.25,
"currency": "USD",
"itemid": 15505,
"quantity": 1600.0,
"unit": "MWh",
"enddate": "2020-02-29",
"startdate": "2020-02-01",
"transactiondate": "2020-01-29",
"description": "Cash",
"price": 1.0
}],
"payments": [{
"createdate": "2020-02-12T00:00:00Z",
"transactiondate": "2020-02-11T12:00:00Z",
"paymentmethod": "Check",
"amount": 100.0,
"paymentcurrency": "USD",
"id": 123
},
{
"createdate": "2020-02-12T00:00:00Z",
"transactiondate": "2020-02-12T11:30:00Z",
"paymentmethod": "Check",
"amount": 100.0,
"paymentcurrency": "USD",
"id": 123
}
]
},
{
"counterparty": "AU",
"internallegalentity": "xyc, LP",
"createdate": null,
"paymentcurrency": "USD",
"id": 125,
"version": 1,
"revisiondate": "2020-01-31 12:33:39.922",
"amount": -5120.0,
"duedate": "2020-01-30",
"asofdate": "2020-02-25",
"isdeleted": false,
"source": "XYZ",
"isprovisional": null,
"isproforma": null,
"items": [{
"id": 125,
"amount": -1280.0,
"currency": "USD",
"itemid": 15508,
"quantity": 64000.0,
"unit": "MWh",
"enddate": "2021-02-28",
"startdate": "2021-01-01",
"transactiondate": "2020-01-30",
"description": "Cash",
"price": 1.0
},
{
"id": 125,
"amount": -1280.0,
"currency": "USD",
"itemid": 15507,
"quantity": 64000.0,
"unit": "MWh",
"enddate": "2021-02-28",
"startdate": "2021-01-01",
"transactiondate": "2020-01-30",
"description": "Cash",
"price": 1.0
}
]
}
]
有没有办法以更合适的方式阅读上面的 JSON？
我假设您遇到的真正困难不是关于批量复制方面本身，而是关于扁平化数据表。所以除了最后的一些简单代码之外，我将真正关注这个问题。
顺便说一句，这就是你在 c# 中表示 json 的方式（当然填写 elipsies）：
static string json = @"
[
{
""cpty"": ""SG, LP"",
""internallegalentity"": ""ABC, LN"",
""createdate"": null,
""paymentcurrency"": ""USD"",
""id"": 123,
""version"": 1,
...
},
...
]";
首先，创建三个 DataTable 变量。第一个是您解析的 json 数据，另外两个是空的，用于接收您的商品和付款数据：
DataTable jsonTable = JsonConvert.DeserializeObject<DataTable>(json);
DataTable items = null;
DataTable payments = null;
接下来，遍历解析的 json 数据并将嵌套的数据表提取到它们的变量对应项中。在第一遍时，您需要将表复制到它们各自的变量中。在后续传递中，您需要将表合并到变量中。并注意付款字段中的 DBNull 结果。
foreach (DataRow row in jsonTable.Rows) {
// get the inner "items"
var jsonItems = (DataTable)row["items"];
if (items == null)
items = jsonItems.Copy(); // first pass
else
items.Merge(jsonItems); // subsequent passes
// there is a null payments record, ignore it
if (row["payments"] == DBNull.Value)
continue;
// get the inner payments
var jsonPayments = (DataTable)(row["payments"]);
if (payments == null)
payments = jsonPayments.Copy(); // first not null pass
else
payments.Merge(jsonPayments); // subsequent passes
}
现在您有了项目和付款表，从最初解析的表中删除这些字段：
jsonTable.Columns.Remove("items");
jsonTable.Columns.Remove("payments");
您现在可以根据需要进行批量复制了。以下未经测试的代码假设您已经有现有表，其中列的顺序与您的数据表表示形式相同。
using (var bulkcopy = new SqlBulkCopy("connection string")) {
bulkcopy.DestinationTableName = "main";
bulkcopy.WriteToServer(jsonTable);
bulkcopy.DestinationTableName = "items";
bulkcopy.WriteToServer(items);
bulkcopy.DestinationTableName = "payments";
bulkcopy.WriteToServer(payments);
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句