将DataTable转换为嵌套JSON输出 [英] Converting a DataTable to nested JSON output

问题描述

我有一个定义为的SQL Server源表:

I have a SQL Server source table defined as:

 sku    store  qty
 20000  100    3
 20000  132    1
 20000  320    0
 30000  243    2
 30000  210    1
 10000  410    5

我需要的输出是:

{
  "skus": {
    "20000": {
      "100": 3,
      "132": 1,
      "320": 0
    },
    "30000": {
      "243": "2",
      "410": "1"
    },
    "10000": {
      "410": "5"
    }
  }
}

我将源SQL Server表导入到DataSet中，然后打算使用JSON.NET解析结果.我当时想我应该用一个带有存储/数量键/值对列表的sku创建某种Class结构，但是我不确定这是否正确.

I have the source SQL Server table being imported to a DataSet, then was going to use JSON.NET to parse the results. I was thinking I should create some sort of Class structure with a sku having a list of store/qty key/value pairs, but I'm not totally sure if that's the right track or not.

推荐答案

您处在正确的轨道上.为了获得您在问题中概述的结构，您需要使用字典的字典，表示每个SKU的商店数量到数量的映射.该类如下所示:

You're on the right track. To get the structure you outlined in your question, you need to use a dictionary of dictionaries representing mappings of store number to quantity per SKU. The class would look like this:

class RootObject
{
    [JsonProperty("skus")]
    public Dictionary<string, Dictionary<string, int>> Skus { get; set; }
}

您将需要少量代码来将数据表行分组到嵌套字典中，如下所示.注意:此代码假定每个SKU仅会遇到一次每个商店编号.如果不是这种情况，则需要相应地进行调整.

You will need a small amount of code to group your data table rows into the nested dictionaries, as shown below. Note: this code assumes that each store number will be encountered only once per SKU. If this is not the case, you will need to adjust it accordingly.

DataTable table = new DataTable();
table.Columns.Add("sku", typeof(int));
table.Columns.Add("store", typeof(int));
table.Columns.Add("qty", typeof(int));
table.Rows.Add(20000, 100, 3);
table.Rows.Add(20000, 132, 1);
table.Rows.Add(20000, 320, 0);
table.Rows.Add(30000, 243, 2);
table.Rows.Add(30000, 210, 1);
table.Rows.Add(10000, 410, 5);

var skus = new Dictionary<string, Dictionary<string, int>>();

foreach (DataRow row in table.Rows)
{
    string sku = row["sku"].ToString();
    Dictionary<string, int> stores;
    if (!skus.TryGetValue(sku, out stores))
    {
        stores = new Dictionary<string, int>();
        skus.Add(sku, stores);
    }
    stores.Add(row["store"].ToString(), (int)row["qty"]);
}

RootObject root = new RootObject { Skus = skus };

一旦将数据收集到RootObject中，使用Json.Net将其序列化为JSON就很简单了:

Once you have the data gathered into your RootObject, it is trivial to serialize it to JSON using Json.Net:

string json = JsonConvert.SerializeObject(root, Formatting.Indented);

将JSON反序列化回您的RootObject也很容易:

Deserializing the JSON back to your RootObject is just as easy:

RootObject root = JsonConvert.DeserializeObject<RootObject>(json);

这是一个完整的往返演示: https://dotnetfiddle.net/qR3wbE

Here is a full round-trip demo: https://dotnetfiddle.net/qR3wbE

这篇关于将DataTable转换为嵌套JSON输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！