[c#] Convert JSON to DataTable

I have JSON in the following format:

[
    {"id":"10","name":"User","add":false,"edit":true,"authorize":true,"view":true},
    {"id":"11","name":"Group","add":true,"edit":false,"authorize":false,"view":true},
    {"id":"12","name":"Permission","add":true,"edit":true,"authorize":true,"view":true}
]

How can I convert that into a C# DataTable object as follows?

---------------------------------------------------------------------
ID    |  Name     |  Add    |   Edit  | View   | Authorize
---------------------------------------------------------------------
10    | User      | true    |  true   | true   |  true
11    | Group     | true    |  true   | true   |  true
12    | Permission| true    |  true   | true   |  true

json = File.ReadAllText(System.AppDomain.CurrentDomain.BaseDirectory + "App_Data\\" +download_file[0]);
DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

You can make use of JSON.Net here. Take a look at JsonConvert.DeserializeObject method.

Here is another seamless approach to convert JSON to Datatable using Cinchoo ETL - an open source library

Sample below shows how to convert

string json = @"[
{""id"":""10"",""name"":""User"",""add"":false,""edit"":true,""authorize"":true,""view"":true},
{ ""id"":""11"",""name"":""Group"",""add"":true,""edit"":false,""authorize"":false,""view"":true},
{ ""id"":""12"",""name"":""Permission"",""add"":true,""edit"":true,""authorize"":true,""view"":true}
]";

using (var r = ChoJSONReader.LoadText(json))
{
    var dt = r.AsDataTable();
}

Hope it helps.

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.

An issue can arise however, where the deserializer doesn't know where to look for the array to tabulate. When this happens, we get an error message similar to the following:

Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1.

Even if we give it come encouragement or prepare our json accordingly, then "object" types within the array can still prevent tabulation from occurring, where the deserializer doesn't know how to represent the objects in terms of rows, etc. In this case, errors similar to the following occur:

Unexpected JSON token when reading DataTable: StartObject. Path '[0].__metadata', line 3, position 19.

The below example JSON includes both of these problematic features:

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

So how can we resolve this, and still maintain the flexibility of not knowing the type into which to derialize?

Well here is a simple approach I came up with (assuming you are happy to ignore the object-type properties, such as __metadata in the above 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<JObject>())
    {
        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<DataTable>(trgArray.ToString());
}

I know this could be more "LINQy" and has absolutely zero exception handling, but hopefully the concept is conveyed.

We're starting to use more and more services at my work that spit back JSON, so freeing ourselves of strongly-typing everything, is my obvious preference because I'm lazy!

It can also be achieved using below code.

DataSet data = JsonConvert.DeserializeObject<DataSet>(json);

There is an easier method than the other answers here, which require first deserializing into a c# class, and then turning it into a datatable.

It is possible to go directly to a datatable, with JSON.NET and code like this:

DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

I recommend you to use JSON.NET. it is an open source library to serialize and deserialize your c# objects into json and Json objects into .net objects ...

Serialization Example:

Product product = new Product();
product.Name = "Apple";
product.Expiry = new DateTime(2008, 12, 28);
product.Price = 3.99M;
product.Sizes = new string[] { "Small", "Medium", "Large" };

string json = JsonConvert.SerializeObject(product);
//{
//  "Name": "Apple",
//  "Expiry": new Date(1230422400000),
//  "Price": 3.99,
//  "Sizes": [
//    "Small",
//    "Medium",
//    "Large"
//  ]
//}

Product deserializedProduct = JsonConvert.DeserializeObject<Product>(json);