I am getting one C# DataTable. Which has couple of rows and few columns. Number of columns is dynamic but usually range between 4-8 columns.
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Name");
dataTable.Columns.Add("America");
dataTable.Columns.Add("Japan");
dataTable.Columns.Add("Singapore");
dataTable.Rows.Add("A", 200, 100, 300);
dataTable.Rows.Add("B", 300, 300, 600);
dataTable.Rows.Add("C", 400, 400, 700);
dataTable.Rows.Add("D", 500, 500, 800);
dataTable.Rows.Add("E", 600, 600, 900);
dataTable.Rows.Add("F", 700, 700, 1000);
dataTable.Rows.Add("G", 800, 800, 600);
dataTable.Rows.Add("H", 900, 100, 400);
dataTable.Rows.Add("I", 100, 200, 300);
dataTable.Rows.Add("J", 200, 300, 200);
dataTable.Rows.Add("K", 300, 500, 500);
Rows have relation between them. I have another Data Table which tells us which row in DataTable is parent row and which row is child row.
DataTable mappingTable = new DataTable();
mappingTable.Columns.Add("Name");
mappingTable.Columns.Add("id");
mappingTable.Columns.Add("parentID");
mappingTable.Rows.Add("A", 1, null);
mappingTable.Rows.Add("B", 2, 1);
mappingTable.Rows.Add("C", 3, 1);
mappingTable.Rows.Add("D", 4, null);
mappingTable.Rows.Add("E", 5, 4);
mappingTable.Rows.Add("F", 6, null);
mappingTable.Rows.Add("G", 7, 6);
mappingTable.Rows.Add("H", 8, 6);
mappingTable.Rows.Add("I", 9, 1);
mappingTable.Rows.Add("J", 10 ,null);
mappingTable.Rows.Add("K", 11, 10);
I converted first DataTable into JSON using following code:
var json = Newtonsoft.Json.JsonConvert.SerializeObject(dataTable);
[
{
"name": "A",
"america": "200",
"japan": "100",
"singapore": "300"
},
{
"name": "B",
"america": "300",
"japan": "300",
"singapore": "600"
},
{
"name": "C",
"america": "400",
"japan": "400",
"singapore": "700"
},
{
"name": "D",
"america": "500",
"japan": "500",
"singapore": "800"
},
{
"name": "E",
"america": "600",
"japan": "600",
"singapore": "900"
},
{
"name": "F",
"america": "700",
"japan": "700",
"singapore": "1000"
},
{
"name": "G",
"america": "800",
"japan": "800",
"singapore": "600"
},
{
"name": "H",
"america": "900",
"japan": "100",
"singapore": "400"
},
{
"name": "I",
"america": "100",
"japan": "200",
"singapore": "300"
},
{
"name": "J",
"america": "200",
"japan": "300",
"singapore": "200"
},
{
"name": "K",
"america": "300",
"japan": "500",
"singapore": "500"
}
]
Now I am thinking of parsing this JSON and then run LINQ over it & use MappingTanle to generate hierarchical JSON.
When i use this line, I get error:
JObject rss = JObject.Parse(json);
Error: base {"Error reading JObject from JsonReader. Current JsonReader item is not an object: StartArray. Path '', line 1, position 1."} System.Exception {Newtonsoft.Json.JsonReaderException}
This is the output I am trying to generate:
[{
"name": "A",
"america": "200",
"japan": "100",
"singapore": "300",
"children": [{
"name": "B",
"america": "300",
"japan": "300",
"singapore": "600"
}, {
"name": "C",
"america": "400",
"japan": "400",
"singapore": "700"
}, {
"name": "I",
"america": "100",
"japan": "200",
"singapore": "300"
}
]
}, {
"name": "D",
"america": "500",
"japan": "500",
"singapore": "800",
"children": [{
"name": "E",
"america": "600",
"japan": "600",
"singapore": "900"
}
]
}, {
"name": "F",
"america": "700",
"japan": "700",
"singapore": "1000",
"children": [{
"name": "G",
"america": "800",
"japan": "800",
"singapore": "600"
}, {
"name": "H",
"america": "900",
"japan": "100",
"singapore": "400"
}
]
}, {
"name": "J",
"america": "200",
"japan": "300",
"singapore": "200",
"children": [{
"name": "K",
"america": "300",
"japan": "500",
"singapore": "500"
}
]
}
]