Convert DataTable to JSON with key per row

by

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

More Related Contents:

  1. JSON.NET Error Self referencing loop detected for type
  2. Does Json.NET cache types’ serialization information?
  3. Cannot deserialize the JSON array (e.g. [1,2,3]) into type ‘ ‘ because type requires JSON object (e.g. {“name”:”value”}) to deserialize correctly
  4. Detect if deserialized object is missing a field with the JsonConvert class in Json.NET
  5. Json.net serialize/deserialize derived types?
  6. Using JsonConvert.DeserializeObject to deserialize Json to a C# POCO class
  7. How to ignore JsonProperty(PropertyName = “someName”) when serializing json?
  8. Json.Net: Serialize/Deserialize property as a value, not as an object
  9. “Self Referencing Loop Detected” exception with JSON.Net
  10. How to serialize/deserialize a custom collection with additional properties using Json.Net
  11. Deserializing JSON to abstract class
  12. Deserialize json array stream one item at a time
  13. Not ableTo Serialize Dictionary with Complex key using Json.net
  14. How to deserialize a JSON property that can be two different data types using Json.NET
  15. How to convert datatable to json string using json.net?
  16. Custom Deserialization using Json.NET
  17. Force JSON.NET to include milliseconds when serializing DateTime (even if ms component is zero)
  18. How to make JSON.Net serializer to call ToString() when serializing a particular type?
  19. Json.NET custom serialization with JsonConverter – how to get the “default” behavior
  20. Cannot deserialize the current JSON array (e.g. [1,2,3])
  21. Ignoring null fields in Json.net
  22. Repeated serialization and deserialization creates duplicate items
  23. Deserialize specific enum into system.enum in Json.Net
  24. Serializing an interface/abstract object using NewtonSoft.JSON
  25. Json.NET: Deserializing nested dictionaries
  26. JSonNet boolean serialization
  27. Deserializing JSON into an object with Json.NET
  28. Ignore Base Class Properties in Json.NET Serialization
  29. NewtonSoft.Json Serialize and Deserialize class with property of type IEnumerable
  30. Json.net serialization of custom collection implementing IEnumerable

Leave a Comment