"ID": 1, "SHORT_NAME": "B", "CARRIER_NAME": "Carrier A"
Another one with multiple values comes from another datatable:
{ "YMDH": "2009-03-07 00:00:00.000", "SELL_DURATION": 222.999995 }, { "YMDH": "2009-03-07 01:00:00.000", "SELL_DURATION": 75.816664 }, { "YMDH": "2009-03-07 02:00:00.000", "SELL_DURATION": 39.349995 }, { "YMDH": "2009-03-07 03:00:00.000", "SELL_DURATION": 75.816664 }, { "YMDH": "2009-03-07 04:00:00.000", "SELL_DURATION": 39.349995 }, { "YMDH": "2009-03-07 05:00:00.000", "SELL_DURATION": 275.91666 }
Since first table will have only one records and another table will be having multiple records for that one row. how can i combine them both in my controller class to build proper JSON like this:
Desired Output:
var nwCustomers = [{ "ID": 1, "SHORT_NAME": "A", "CARRIER_NAME": "Carrier A", "SellDuration": [{ "YMDH": "2009-03-07 00:00:00.000", "SELL_DURATION": 222.999995 }, { "YMDH": "2009-03-07 01:00:00.000", "SELL_DURATION": 75.816664 }, { "YMDH": "2009-03-07 02:00:00.000", "SELL_DURATION": 39.349995 }, { "YMDH": "2009-03-07 03:00:00.000", "SELL_DURATION": 75.816664 }, { "YMDH": "2009-03-07 04:00:00.000", "SELL_DURATION": 39.349995 }, { "YMDH": "2009-03-07 05:00:00.000", "SELL_DURATION": 275.91666 }] }
];
I'm tring to do something like this but not able to get the desired output:
Expand|Select|Wrap|Line Numbers
- public class GridModel
- {
- public DateTime YMDH { get; set; }
- public double ID { get; set; }
- public string SHORT_NAME { get; set; }
- public string CARRIER_NAME { get; set; }
- public double SELL_DURATION { get; set; }
- public GridSparklineModel SellDuration { get; set; }
- }
- public class GridSparklineModel
- {
- public DateTime YMDH { get; set; }
- public double SELL_DURATION { get; set; }
- }
Expand|Select|Wrap|Line Numbers
- public ActionResult FetchGraphDataJSON()
- {
- Grid grid = new Grid();
- DataSet ds = grid.GetHistoryData();
- DataTable dt = ds.Tables[0];
- List<GridModel> data = new List<GridModel>();
- if (dt.Rows.Count != 0)
- {
- StringBuilder sb = new StringBuilder();
- foreach (DataRow row in dt.Rows)
- {
- sb.Append(new GridModel { ID = Convert.ToDouble(@row["ID"].ToString()), SHORT_NAME = @row["SHORT_NAME"].ToString() });
- double carrierId = Convert.ToDouble(@row["ID"].ToString());
- DataRow[] rowsInOtherTable = ds.Tables[1].Select("ID = " + carrierId);
- for(int i=0; i < rowsInOtherTable.Count(); i++)
- {
- // add with existing
- }
- data.Add(new GridModel { ID = Convert.ToDouble(@row["ID"].ToString()), SHORT_NAME = @row["SHORT_NAME"].ToString(), CARRIER_NAME = @row["CARRIER_NAME"].ToString(), SellDuration = new GridSparklineModel { YMDH = DateTime.Parse(@rowsInOtherTable[0]["YMDH"].ToString()), SELL_DURATION = Convert.ToDouble(@rowsInOtherTable[0]["SELL_DURATION"].ToString()) } });
- }
- }
- return Json(data, JsonRequestBehavior.AllowGet);
- }
First datatable has id values
ID SHORT_NAME CARRIER_NAME
1 A Carrier A
2 B Carrier B
3 C Carrier C
4 D Carrier D
5 E Carrier E
6 F Carrier F
7 G Carrier G
Based on above id values rows are filtered from this datatable 2 and added to make row of JSON
YMDH ID SELL_DURATION
2009-03-07 00:00:00.000 1 222.999995
2009-03-07 01:00:00.000 1 75.816664
2009-03-07 02:00:00.000 1 39.349995
2009-03-07 03:00:00.000 1 275.91666
2009-03-07 04:00:00.000 1 352.666641
2009-03-07 00:00:00.000 2 80.783324
2009-03-07 01:00:00.000 2 162.049985
2009-03-07 02:00:00.000 2 107.199989
2009-03-07 03:00:00.000 2 44.849994
2009-03-07 04:00:00.000 2 156.516658
2009-03-07 05:00:00.000 2 467.583312
2009-03-07 06:00:00.000 2 455.199977
and so on..