Import Complex JSON file to C# dataTable

Question

I have a .json file that has the following structure.

{
  "$id": "1",
  "ACCOMODATIONs": [
    {
      "$id": "2",
      "VOUCHER": {
        "$ref": "1"
      },
      "ID": 3625,
      "VID": 872,
      "CHECKIN_DATE": "2017-02-06T00:00:00",
      "CHECKOUT_DATE": "2017-02-11T00:00:00",
      "HOTEL": "HOOT-8",
      "NIGHTS": 5,
      "ROOMTYPE": "Sharing",
      "PACKAGE": "ECONOMY",
      "HotelRemarks": null
    },
    {
      "$id": "3",
      "VOUCHER": {
        "$ref": "1"
      },
      "ID": 3626,
      "VID": 872,
      "CHECKIN_DATE": "2017-02-11T00:00:00",
      "CHECKOUT_DATE": "2017-02-21T00:00:00",
      "HOTEL": "HTWE-3",
      "NIGHTS": 10,
      "ROOMTYPE": "Sharing",
      "PACKAGE": "ECONOMY",
      "HotelRemarks": null
    },
    {
      "$id": "4",
      "VOUCHER": {
        "$ref": "1"
      },
      "ID": 3627,
      "VID": 872,
      "CHECKIN_DATE": "2017-02-21T00:00:00",
      "CHECKOUT_DATE": "2017-02-26T00:00:00",
      "HOTEL": "WEST INT - 2",
      "NIGHTS": 5,
      "ROOMTYPE": "Sharing",
      "PACKAGE": "ECONOMY",
      "HotelRemarks": null
    }
  ],
  "PAXINFOes": [
    {
      "$id": "5",
      "VOUCHER": {
        "$ref": "1"
      },
      "PID": 3529,
      "NAME": "SMITH",
      "PPNO": "SDW332233",
      "RELATION": null,
      "DOB": "1976-01-01T00:00:00",
      "GENDER": "",
      "VISANO": "633333359",
      "VISISSUEDATE": "19/01/17",
      "VISA_COMPANY": "CMP-1",
      "GroupCode": "9475",
      "Age": 41,
      "VID": 872,
      "PayblePKG": "",
      "AGE_GROUP": "adult"

    },
    {
      "$id": "6",
      "VOUCHER": {
        "$ref": "1"
      },
      "PID": 3529,
      "NAME": "JACOB",
      "PPNO": "SMT334333",
      "RELATION": null,
      "DOB": "1976-01-01T00:00:00",
      "GENDER": "",
      "VISANO": "639999359",
      "VISISSUEDATE": "19/01/17",
      "VISA_COMPANY": "CMP-1",
      "GroupCode": "9475",
      "Age": 41,
      "VID": 872,
      "PayblePKG": "",
      "AGE_GROUP": "adult"
    }
  ],
  "VID": 872,
  "VNO": "872",
  "FLIGHT_NO": "QR-1188",
  "PACKAGE_TYPE": null,
  "VOUCHER_NO": null,
  "CHARGES": null,
  "DEPART_DATE": "2017-02-06T00:00:00",
  "DEPART_TIME": "19:30 PM",
  "Approved": true,
  "ApprovedBy": "hrm",
  "ServiceNo": "",
  "Transport": null,
  "TransportRate": null,
  "DepRemarks": null

}

Now deserializing a simple JSON is easy, and i have done it, but how to deserialize JSON Data that has 1-many relationship and in the above format and convert it into a C# DataTable. I use NewtonSoft JSON.

Thanks & Regrads

Answer 1

I think you can't directly deserialize it to DataTable, because a DataTable consist only of two dimensions. Row and Column, which won't allow you to represent something very complex. If you deserialize to a DataTable you will get one row per object. So if your object consists of properties which have 1 to many relation as well this can't be packed into one column.

I think a DataSet gives you one more dimension because you can store one DataTable per Object, which also can contain one row per subobject. How you can achieve this is written here:

Example:

string json = @"{
 'Table1': [
    {
      'id': 0,
      'item': 'item 0'
    },
    {
      'id': 1,
      'item': 'item 1'
    }
 ]
}";

DataSet dataSet = JsonConvert.DeserializeObject<DataSet>(json);

DataTable dataTable = dataSet.Tables["Table1"];

Console.WriteLine(dataTable.Rows.Count);
// 2

foreach (DataRow row in dataTable.Rows)
{
    Console.WriteLine(row["id"] + " - " + row["item"]);
}
// 0 - item 0
// 1 - item 1

Further I would recommend you to use an Objectstructure to accomplish your task. It's easier to handle objects with properties and you can build a structure as complex as you like. If you have one to many relation you can use List as Property.

Example:

public class MyJsonObject
{
   public List<Table> Tables{get;set;}
}

public class Table
{
   public int Id {get;set;}

   public string Item {get;set;}
}

private void Convert()
{
   string json = @"{
    'Table1': [
      {
        'id': 0,
        'item': 'item 0'
      },
      {
        'id': 1,
        'item': 'item 1'
      }
   ]
  }";

  MyJsonObject obj = JsonConvert.DeserializeObject<MyJsonObject>(json);
  Console.WriteLine(obj.Tables[0].Item);
}