Deserialize Json to DataTable, DataSet C#

Deserialize Json to DataTable, DataSet C#

DataTable is a powerful in memory object offered by Microsoft. To read more about DataTable please refer DataTable C#. Modern data flow from web is transported in various format and JSON is one of the popular. In this article we will see how to deserialize a Json into DataTable.

Prerequisites:

  1. You must have a valid Json (string,file, web response)
  2. DataTable structure must meet the Data Contract with JSON

Json Example:

var myObj = { "name":"John", "age":31, "city":"New York" };

Note: Above JSON holds following fields which must be included as part of your DataTable Data contract:

  • name
  • age
  • city

Now you need to include Newtonsoft.Json Library offered by Newtonsoft as using in your code and call JsonConvert method which offers DeserializeObject method as shown below:

using Newtonsoft.Json;
DataTable dTable = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

JObject and JProperty: in this example we will see how to manipulate json with JObject and JProperty:

Json format:


{
  "results":
  [
    {
      "Enabled": true,
      "Id": 106,
      "Name": "item 1",
    },
    {
      "Enabled": false,
      "Id": 107,
      "Name": "item 2",
      "__metadata": { "Id": 4013 }
    }
  ]
}

Code example:

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Linq;
public static DataTable Tabulate(string json)
{
    var jsonLinq = JObject.Parse(json);

    // Find the first array using Linq
    var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
    var trgArray = new JArray();
    foreach (JObject row in srcArray.Children())
    {
        var cleanRow = new JObject();
        foreach (JProperty column in row.Properties())
        {
            // Only include JValue types
            if (column.Value is JValue)
            {
                cleanRow.Add(column.Name, column.Value);
            }
        }

        trgArray.Add(cleanRow);
    }

    return JsonConvert.DeserializeObject(trgArray.ToString());
}

Deserialize Json into DataSet:

using Json.Net;
DataSet data = JsonConvert.DeserializeObject(json);

Next>>Convert DataTable into Json String C#

2 thoughts on “Deserialize Json to DataTable, DataSet C#

  1. One doesn’t always know the type into which to deserialize. So it would be handy to be able to take any JSON (that contains some array) and dynamically produce a table from that.

Leave a Reply

Your email address will not be published. Required fields are marked *