Convert DataTable to JSON with key per row
This is quite simple with JSON.NET. Just convert your data table into the equivalent dictionary of dictionaries:
public Dictionary<string, Dictionary<string, object>> DatatableToDictionary(DataTable dt, string id){ var cols = dt.Columns.Cast<DataColumn>().Where(c => c.ColumnName != id); return dt.Rows.Cast<DataRow>() .ToDictionary(r => r[id].ToString(), r => cols.ToDictionary(c => c.ColumnName, c => r[c.ColumnName]));}
Then call:
JsonConvert.SerializeObject(DatatableToDictionary(dt, "ID"), Newtonsoft.Json.Formatting.Indented);
Here's the full test:
var dt = new DataTable("MyTable");dt.Columns.Add("ID");dt.Columns.Add("Name");dt.Columns.Add("Active");dt.LoadDataRow(new[] {"ID1", "John", "True"}, true);dt.LoadDataRow(new[] {"ID2", "Bill", "False"}, true);JsonConvert.SerializeObject(DatatableToDictionary(dt, "ID"));
And the result:
{ "ID1": { "Name": "John", "Active": "True" }, "ID2": { "Name": "Bill", "Active": "False" }}
Using JSON.NET (Newtonsoft.Json.Linq
)
var obj = new JObject( dataTable.Rows.Cast<DataRow>() .Select(r => new JProperty(r["ID"].ToString(), new JObject( new JProperty("Name", r["Name"].ToString()), new JProperty("Active", r["Active"].ToString()) ) )));// Convert the JObject to a JSON stringvar json = obj.ToString();