Trouble while converting datatable to nested json c#

Question

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

i am getting error : "Error converting value \"\u0004 Primary\" to type \u0027System.Collections.Generic.List`1[WebService+SubGroup]\u0027. Path \u0027[0].Sub_group\u0027, line 1, position 48." — Yogesh Sharma
– Yogesh Sharma, Commented Aug 17, 2016 at 7:42

Hari Prasad · Accepted Answer · 2016-08-17 11:13:27Z

1

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

edited Aug 17, 2016 at 11:13

answered Aug 17, 2016 at 9:15

Hari Prasad

17k4 gold badges23 silver badges35 bronze badges

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

6 Comments

Yogesh Sharma Over a year ago

thanks for your reply. but i need to display JSON Response as above

Hari Prasad Over a year ago

That's easy just modify Linq query to create an anonymous object with the name and value.

Yogesh Sharma Over a year ago

sorry for this comments but i am new in JSON as well as in C# langauage

Yogesh Sharma Over a year ago

the above answer is correctly match with my requirements i am really thanking you since two days i am posting new questions as i don't know what really needs to do. But, as your suggestion it is really help full for me and just suggest me where i needs to correction. so, i can correct it and mark his post as solved by Mr. Hari Prasad

Hari Prasad Over a year ago

@YogeshSharma try my updated code. It should generate what you want.

|

MaticDiba · Accepted Answer · 2016-08-17 08:10:36Z

0

The line:

row.Add(col.ColumnName, rs[col]);

Here you are trying to add different type of objects to this collection of yours. If all values can be stored as strings (I guess this is OK, because you are creating JSon object afterwards, try changing that line to:

 row.Add(col.ColumnName, rs[col].ToString());

answered Aug 17, 2016 at 8:10

MaticDiba

9051 gold badge11 silver badges19 bronze badges

1 Comment

Yogesh Sharma Over a year ago

showing error : "Error converting value \"\u0004 Primary\" to type \u0027System.Collections.Generic.List`1[WebService+SubGroup]\u0027. Path \u0027[0].Sub_group\u0027, line 1, position 48."

Collectives™ on Stack Overflow

Trouble while converting datatable to nested json c#

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related