CODEWITHSUNDEEP
c#jsonjson.net
Asim
Asim

Reputation: 47

Serializing SQL row of key value pair to JSON object

I am building a web form using asp.net c# to generate a JSON string. I have a SQL Server table with columns ID, Columnname, Parent ID, Value.

For a particular ID, if the Id = parentId then it is a parent column, whereas if an id != parentid then it is a child and has a parent. I am pulling these into a datatable from which i need to print a JSON string in a label (as in the actual output)

I tried using Newtonsoft.Json as below 

foreach (DataRow row in JSONInfoTable.Rows)
{
string result = Newtonsoft.Json.JsonConvert.serializeObject(row);
testlabel.Text = result;
}

But I am getting below result from above code

{"Columnname":"Parent1","Value:100"}...

Actual Output JSON (Printed in a Webform label after btnclick event)

{
    "Parent1" : "100",
    "Parent2" : {
                 "child1" : "300", 
                 "child2" : "40" 
    }
}

SQL Server: GetJSON table 

Id   Columnname  ParentId   Value 
----------------------------------
 1   Parent1         1       100
 2   Parent2         2       NULL
 3   Child1          2       300
 4   Child2          2       40

C# Datatable (Pulling data from GetJSON table)

SqlCommand Fieldextract = new SqlCommand(Select Columnname, Value from GetJSON, sqlcon);
Dataset ds1 = new DataSet();
SqlDataAdapter = JSONInfoAdapter ;
JSONInfoAdapter = new SQLDataAdapter(FieldExtract);
JSONInfoAdapter.fill(ds1);
FieldExtract.Dispose();
JSONInfoAdapter = null;
DataTable JSONInfoTable = ds1.Table[0];

Upvotes: 1

Views: 1181

Answers (1)

Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

You need to group by Columnname and then serialize it: 

JsonConvert.serializeObject(JSONInfoTable.AsEnumerable().GroupBy(row => row.Columnname));

Upvotes: 1

Related Questions