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

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)));

Deserialize your jsonstring to some class

List<User> UserList = JsonConvert.DeserializeObject<List<User>>(jsonString);

Write following extension method to your project

using System.ComponentModel;

public static DataTable ToDataTable<T>(this IList<T> data)
{
    PropertyDescriptorCollection props =
    TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for(int i = 0 ; i < props.Count ; i++)
    {
    	PropertyDescriptor prop = props[i];
    	table.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
    	for (int i = 0; i < values.Length; i++)
    	{
    	    values[i] = props[i].GetValue(item);
    	}
    	table.Rows.Add(values);
    }
    return table;        
}

Call extension method like

UserList.ToDataTable<User>();

It can also be achieved using below code.

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

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!

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

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);

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();
}

Sample fiddle: https://dotnetfiddle.net/y0siCi

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

I solved it by the following - Used Pravin Pawar's answer below - for converting the JSON object to dt.

https://stackoverflow.com/a/11982180/504351

1)Send the JSON from Angular to C# controller method. 2)In C# Controller method -

convert the string data (JSON object) to a datatable.

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

3)Create a table in SQL Server database by parsing through rows and columns of the datatable dt

int dt_length = dt.Columns.Count;
            Random rand = new Random();
            string tablename = "Test" + rand.Next().ToString();
            string query = "Create table " + tablename + "  ( ";
            
            for (int i = 0; i < dt_length; i ++)
            {
                if(i == dt_length -1)
                {
                    query = query + dt.Columns[i].ColumnName + " varchar(max) ) ";
                    
                }
                else
                {
                    query = query + dt.Columns[i].ColumnName + " varchar(max), ";
                }
            }               

4)Similarly insert each of the row data in the table created. 5)This helps to dynamically create a database table from JSON.