DataTable to nested json in c#

Question

I want to convert a DataTable to nested JSON. I have a table Announcement with column name Category, Headline, Details, Short_desc, Author and Display_date.

Datatabel result is like:

Category     Headline    Details     Short_desc  Author   Display_date 

Sports       H1          d1          sd1          a1        dd1
Sports       h2          d2          sd2          a2        dd2
Technology   t1          d3          sd3          a3        dd3
Technology   t2          d4          sd4          a4        dd4

Now I want JSON result something like:

{   
  "Sports" : [ [ 
        "Headline":"H1",
        "Details":"d1",
        "Short_desc":"sd1",
        "Author":"a1",
        "Display_date":"dd1"
      ],
      [ "Headline":"H2",
        "Details":"d2",
        "Short_desc":"sd2",
        "Author":"a2",
        "Display_date":"dd2"
      ]
    ],
  "Technology" : [ [ 
        "Headline":"t1",
        "Details":"d3",
        "Short_desc":"sd3",
        "Author":"a3",
        "Display_date":"dd3"
      ],
      [ "Headline":"t4",
        "Details":"d4",
        "Short_desc":"sd4",
        "Author":"a4",
        "Display_date":"dd4"
      ]
    ]
}

I used the following code:

DataTable dts = get_banner_detail_service("");

System.Web.Script.Serialization.JavaScriptSerializer serializer = 
    new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dts.Rows)
{
    row = new Dictionary<string, object>();
    foreach (DataColumn col in dts.Columns)
    {
        row.Add(col.ColumnName, dr[col]);
    }
    rows.Add(row);
}
Response.Write(serializer.Serialize(rows));
Response.Flush();
Response.End();

Result of above code is not like what I expected. It is like:

   {   
      "Sports" : [ 
            "Headline":"H1",
            "Details":"d1",
            "Short_desc":"sd1",
            "Author":"a1",
            "Display_date":"dd1"
          ],
       "Sports" :[ 
            "Headline":"H2",
            "Details":"d2",
            "Short_desc":"sd2",
            "Author":"a2",
            "Display_date":"dd2"
          ] ....
        }

Answer 1

HI Finally i solve the issue ... below is the updated code -

DataTable dts = get_banner_detail_service("");
        DataTable dtc = get_banner_detail_cat("");
        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
        Dictionary<string, object> rowss;
        Dictionary<string, object> rowsc;
        List<object> rowsin;
        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
        rowsc = new Dictionary<string, object>();
        foreach (DataRow dr in dtc.Rows)
        {
            string cat = dr["Category"].ToString();

            var filteredAndroid = (from n in dts.AsEnumerable()
                                   where n.Field<string>("Category").Contains(cat)
                                   select n).ToList();

            if (filteredAndroid.Count != 0)
            {
                DataTable t = filteredAndroid.CopyToDataTable();

                t.Columns.Remove("Category");
                rowss = new Dictionary<string, object>();

                rowsin = new List<object>();
                foreach (DataRow drr in t.Rows)
                {
                    foreach (DataColumn col in t.Columns)
                    {
                        rowss.Add(col.ColumnName, drr[col]);
                    }
                    rowsin.Add(rowss);
                    rowss = new Dictionary<string, object>();

                }
                rowsc.Add(cat, rowsin);
                t.Dispose();
                t = null;
                filteredAndroid = null;
            }
        }
        rows.Add(rowsc);

        string json = JsonConvert.SerializeObject(rows, Newtonsoft.Json.Formatting.Indented);
        if (json.Length > 2)
        {
            json = json.Substring(1, json.Length - 2);
        }
        Response.Write(json);
        Response.Flush();
        Response.End();