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 string
var json = obj.ToString();