Yogesh Sharma Yogesh Sharma - 3 months ago 47
JSON Question

Trouble while converting datatable to nested json c#

I have a SQL Table with values like:

Main_group Sub_group CstCmpCode
COMBO SET DD-101 AH01
COMBO SET DD-102 AH01


I need to create nested json string like:

{
"CstCmpCode": "AH01",
"Main_Group": "COMBO SET",
"sub_group": [
{
"Sub_Group": "DD-101",
},
{
"Sub_Group": "DD-102",
}
]
}


My code as below for converting datatable to nested json string :

System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TallyWeb"].ToString());
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select distinct Main_group, Sub_group, CstCmpCode from TlyStkSumm where CstCmpCode = @CstCmpCode";
cmd.Parameters.AddWithValue("@CstCmpCode", CstCmpCode);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.SelectCommand.Connection = con;
da.Fill(dt);
con.Close();

List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row = null;
foreach (DataRow rs in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, rs[col]);
}
rows.Add(row);
}


Pls. check in the above what i am going to wrong.

Thanks.

Yogesh.Sharma

Answer

Looks like you need grouping on Main_Group and CstCmpCode, I would suggest you simple Linqto group and select in desired format and pass it to JavaScriptSerializer to serialize it.

var results = dt.AsEnumerable()
    .GroupBy(x => 
        new
        {
            Main_group = x.Field<string>("Main_group"),
            CstCmpCode = x.Field<string>("CstCmpCode")
        })
    .Select(x=> 
        new
        {
            Main_group = x.Key.Main_group,
            CstCmpCode = x.Key.CstCmpCode,
            sub_group = x.Select(s => new {Sub_Group= s.Field<string>("Sub_group") })

        } );

JavaScriptSerializer serializer = new JavaScriptSerializer();
var serializedString = serializer.Serialize(results);

Output :

[{
    "Main_group": "COMBO SET",
    "CstCmpCode": "AH01",
    "Sub_group": ["DD-101 ", "DD -102 "]
}]