CODEWITHSUNDEEP
c#jsonjson.net
Julia
Julia

Reputation: 23

Dynamic creation of Json from C# Dataset with Parent/Child relation

I want to write a simple application that takes data from a database and formats it to a Json file. The catch is, that the views from which I'm getting the data are supposed to be changeable. That means the Json can't be serialized from a rootclass. Also, I need to make sure if the tables have a parent/child connection this is depicted as well.

In the code below I have created a dataset to give you an idea of what I mean.

        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);

    }

This is the Json I want it to output:

{"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"
    }
  ]
}],}

This is what I do get:

{"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"
}]}

Upvotes: 0

Views: 1499

Answers (1)

Anu Viswan
Anu Viswan

Reputation: 18155

You can do this in couple of steps.

Step 1 : Set Relation.Nested Property as True. 

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

Step 2 : Convert to Xml.

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

Step 3: Serialize as Json for final result

XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
JsonSerializerSettings settings = new JsonSerializerSettings();
settings.TypeNameHandling = TypeNameHandling.All;

string jsonResult = JsonConvert.SerializeXmlNode(doc, Newtonsoft.Json.Formatting.Indented);

Final Output for the sample would be

{
  "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"
          }
        ]
      }
    ]
  }
}

Upvotes: 2

Related Questions