DataTable to nested json in c#

Question

I want to convert datatable into nested json.

 

Datatabel result is like:

 

 

CstCmpCode	Main_Group	Sub_Group	ClosBal
AH01	Neck Wraps	AFGHANI	7

 

Now I want JSON result something like:

 

{

"CstCmpCode": "AH01",

"Main_Group": "Neck Wraps",

"sub_group": [

{

"Sub_Group": "AFGHANI",

"ClosBal": 7

}

]

}

 

I used the following code:

 

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 CstCmpCode, Loaded_date, Main_Group, Sub_Group, ClosBal 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();

 

var main = new Dictionary<string, Dictionary<string, string>>();

foreach (DataRow rs in dt.Rows)

{

string Main_Group = rs["Main_Group"].ToString();

Dictionary<string, string> sub;

if (!main.TryGetValue(Main_Group, out sub))

{

sub = new Dictionary<string, string>();

main.Add(Main_Group, sub);

}

sub.Add(rs["Sub_Group"].ToString(), (string)rs["ClosBal"]);

}

RootObject root = new RootObject { main = main };

 

 

public class RootObject

{

[JsonProperty("main")]

public Dictionary<string, Dictionary<string, string>> main { get; set; }

}