从具有父/子关系的 C# 数据集动态创建 Json

Question

我想编写一个简单的应用程序，从数据库中获取数据并将其格式化为Json文件。问题是，我从中获取数据的视图应该是可变的。这意味着 Json 不能从根类序列化。另外，我需要确保表是否具有父/子连接，这也被描绘出来了。

在下面的代码中，我创建了一个数据集，让您了解我的意思。

        static void Main(string[] args)
    {
        DataSet dsSet = new DataSet("OrderManagement");
        DataTable tCustumer = new DataTable("Custumer");
        DataTable tOrder = new DataTable("Order");
        tCustumer.Columns.Add("CustumerId");
        tCustumer.Columns.Add("Name");
        tOrder.Columns.Add("OrderId");
        tOrder.Columns.Add("CustumerId");
        tOrder.Columns.Add("Article");
        tCustumer.Rows.Add("1", "Chris");
        tCustumer.Rows.Add("2", "Ronja");
        tCustumer.Rows.Add("3", "Thomas");
        tOrder.Rows.Add("1", "1", "chocolate");
        tOrder.Rows.Add("2", "1", "apples");
        tOrder.Rows.Add("3", "2", "dogfood");
        tOrder.Rows.Add("4", "3", "keyboard");
        tOrder.Rows.Add("4", "3", "tomatos");
        tOrder.Rows.Add("4", "3", "green tea");
        dsSet.Tables.Add(tCustumer);
        dsSet.Tables.Add(tOrder);
        dsSet.Relations.Add(
            "RelationCustumerOrder",
            dsSet.Tables["Custumer"].Columns["CustumerId"],
            dsSet.Tables["Order"].Columns["CustumerId"], false
            );
        dsSet.AcceptChanges();
        JsonSerializerSettings settings = new JsonSerializerSettings();
        settings.TypeNameHandling = TypeNameHandling.All;
        string text = JsonConvert.SerializeObject(dsSet, Formatting.Indented, settings);
    }

这是我希望它输出的 Json：

{"Custumer": [
{
  "CustumerId": "1",
  "Name": "Chris"
    "Order": [
    {
      "OrderId": "1",
      "CustumerId": "1",
      "Article": "chocolate"
    },
    {
      "OrderId": "2",
      "CustumerId": "1",
      "Article": "apples"
    },
    ]
},
{
  "CustumerId": "2",
  "Name": "Ronja"
    "Order": [
    {
      "OrderId": "3",
      "CustumerId": "2",
      "Article": "dogfood"
    }
    ]
},
{
  "CustumerId": "3",
  "Name": "Thomas"
    "Order": [
    {
      "OrderId": "4",
      "CustumerId": "3",
      "Article": "keyboard"
    },
    {
      "OrderId": "4",
      "CustumerId": "3",
      "Article": "tomatos"
    },
    {
      "OrderId": "4",
      "CustumerId": "3",
      "Article": "green tea"
    }
  ]
}],}

这就是我得到的：

{"Custumer": [
{
  "CustumerId": "1",
  "Name": "Chris"
},
{
  "CustumerId": "2",
  "Name": "Ronja"
},
{
  "CustumerId": "3",
  "Name": "Thomas"
}],
"Order": [
{
  "OrderId": "1",
  "CustumerId": "1",
  "Article": "chocolate"
},
{
  "OrderId": "2",
  "CustumerId": "1",
  "Article": "apples"
},
{
  "OrderId": "3",
  "CustumerId": "2",
  "Article": "dogfood"
},
{
  "OrderId": "4",
  "CustumerId": "3",
  "Article": "keyboard"
},
{
  "OrderId": "4",
  "CustumerId": "3",
  "Article": "tomatos"
},
{
  "OrderId": "4",
  "CustumerId": "3",
  "Article": "green tea"
}]}

Answer 1

您可以通过几个步骤执行此操作。

步骤 1：将 Relation.Nested 属性设置为 true。

dsSet.Relations.Add(
    "RelationCustumerOrder",
    dsSet.Tables["Custumer"].Columns["CustumerId"],
    dsSet.Tables["Order"].Columns["CustumerId"]
    );
dsSet.Relations[0].Nested = true;  

第 2 步：转换为 XML。

StringWriter sw = new StringWriter();
dsSet.WriteXml(sw);
string xmlString = sw.ToString();

步骤 3：序列化为 Json 以获得最终结果

XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
JsonSerializerSettings settings = new JsonSerializerSettings();
settings.TypeNameHandling = TypeNameHandling.All;
string jsonResult = JsonConvert.SerializeXmlNode(doc, Newtonsoft.Json.Formatting.Indented);

示例的最终输出为

{
  "OrderManagement": {
    "Custumer": [
      {
        "CustumerId": "1",
        "Name": "Chris",
        "Order": [
          {
            "OrderId": "1",
            "CustumerId": "1",
            "Article": "chocolate"
          },
          {
            "OrderId": "2",
            "CustumerId": "1",
            "Article": "apples"
          }
        ]
      },
      {
        "CustumerId": "2",
        "Name": "Ronja",
        "Order": {
          "OrderId": "3",
          "CustumerId": "2",
          "Article": "dogfood"
        }
      },
      {
        "CustumerId": "3",
        "Name": "Thomas",
        "Order": [
          {
            "OrderId": "4",
            "CustumerId": "3",
            "Article": "keyboard"
          },
          {
            "OrderId": "4",
            "CustumerId": "3",
            "Article": "tomatos"
          },
          {
            "OrderId": "4",
            "CustumerId": "3",
            "Article": "green tea"
          }
        ]
      }
    ]
  }
}