C # conversion value for each column to array

Question

I want to convert a C # or datatable object so that each column becomes a key and the values ​​become an array

year productOne ProductTwo ProductThree 2010 1 2 3 2011 10 20 30 2012 100 200 300 2013 1000 2000 3000 

I want to create the following JSON output. Keep in mind that none of the rows and columns is dynamic.

 [{ name: productOne , data: [1,10,100,1000] },{ name: productTwo , data: [2,20,200,2000] },{ name: productThree , data: [3,30,300,3000] }] 

Answer 1

 DataTable dt = new DataTable(); dt.Columns.Add("col1"); dt.Columns.Add("col2"); dt.Columns.Add("col3"); DataRow dr = dt.NewRow(); dr[0] = "10"; dr[1] = "20"; dr[2] = "30"; dt.Rows.Add(dr); dr = dt.NewRow(); dr[0] = "100"; dr[1] = "200"; dr[2] = "300"; dt.Rows.Add(dr); dr = dt.NewRow(); dr[0] = "1000"; dr[1] = "2000"; dr[2] = "3000"; dt.Rows.Add(dr); List<dynamic> list = new List<dynamic>(); for (int i = 0; i < dt.Columns.Count; i++) { dynamic result = new ExpandoObject(); result.name = dt.Columns[i].ColumnName; result.data = dt.Rows.Cast<DataRow>() .Select(row => row[i]) .ToArray(); list.Add(result); } var op = JsonConvert.SerializeObject(list); 

Answer 2

You can definitely do this with lists and dictionaries.

 public static IDictionary<string, List<object>> TransposeDataTable(DataTable table) { if (table == null) { return null; } var dicData = new Dictionary<string, List<object>> (); var lstRows = new List<object>[table.Columns.Count]; foreach (DataRow item in table.Rows) { for (var i = 0; i < table.Columns.Count; i++) { if (lstRows [i] == null) { lstRows [i] = new List<object> (); } lstRows [i].Add (item [i]); } } for (var i = 0; i < table.Columns.Count; i++) { dicData.Add (table.Columns [i].ColumnName, lstRows [i]); } return dicData; } 

The above code will create the keys for the dictionary from the column names of the tables, and the data for each column will be included in the corresponding list. This is a kind of motorcade.

Suppose your DataTable is a valid dt

 var dataObject = TransposeDataTable (dt); var jsonString = JsonConvert.SerializeObject (dataObject, Formatting.Indented); 

After a successful conversion, jsonString will contain our beautiful Json object. In my case, dt as dummy data will result in something like.

 { "year": [ "2010", "2011", "2012", "2013", "2014" ], "produceOne": [ "1", "11", "21", "31", "41" ], "ProductTwo": [ "2", "12", "22", "32", "42" ], "ProductThree": [ "3", "13", "23", "33", "43" ] }