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"
          ] ....
        }

What is the problem? Can you explain? Does it crash? Or is the output not like expected? — Arion
– Arion, Commented Feb 26, 2014 at 6:58
Yes output is not like that which i really wants . it returns category wise result — Amit Sharma
– Amit Sharma, Commented Feb 26, 2014 at 7:05

M.S. · Accepted Answer · 2014-02-26 07:28:50Z

1

You need to create nested dictionary as:

        Dictionary<string, Dictionary<string, object>> rows = new Dictionary<string, Dictionary<string, object>>();
        Dictionary<string, object> row;
        foreach (DataRow dr in dts.Rows)
        {
            row = new Dictionary<string, object>();
            var columns = dts.Columns;

            for (int i = 1; i < columns.Count; i++)
            {
                row.Add(columns[i].ColumnName, dr[columns[i]]);
            }

            if (rows.ContainsKey(columns[0].ColumnName))
                rows[columns[0].ColumnName] = rows[columns[0].ColumnName].Concat(row).ToDictionary(p=>p.Key,v=>v.Value);
            else
                rows[columns[0].ColumnName] = row;

        }

answered Feb 26, 2014 at 7:28

M.S.

4,4531 gold badge22 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amit Sharma Over a year ago

Thanks for reply but Argument Exception occur-An item with the same key has already been added.

Amit Sharma · Accepted Answer · 2014-02-27 07:36:44Z

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();

Siddiqui Mizanul Islam · Accepted Answer · 2023-05-03 05:00:53Z

0

Nested Json tree with linq

      var structured =  from grp in QueryResult.GroupBy(x => x.col1)
                 select new
                 {   name = grp.Key,
                     newgrp = from af in grp.GroupBy(y => y.col2)
                                  select new
                                  {   name = af.Key,
                                      case_id =sy.Max(c=>c.col_nameId)}
                                  }
                   }                
                 };

edited May 3, 2023 at 5:00

answered May 3, 2023 at 4:31

Siddiqui Mizanul Islam

11 bronze badge

1 Comment

moken Over a year ago

Answer needs supporting information Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

DataTable to nested json in c#

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related