Convert JSON to a DataTable with C# or VB.Net

Posted by Blake on 1/3/2015
)

This afternoon I wanted to write an example that took basic flat JSON and converted it into a DataTable that I could bind to a Windows BindableListView control I have in an personal application I was working on.  This code is basic and runs under the assumption that the JSON represents a flat table of data with consistent columns (e.g. a database table that was sent via JSON that does not contain nested data).

Using James Newton King’s JSON.Net I was able to easily read/loop over some simple JSON that represented a table and then add that into a new DataTable that I could bind to my UI control.  This isn’t the most efficient way to handle putting JSON into the grid but for my hobby project will work just fine (keep that in mind). 

I am going to provide both the C# and the VB.Net for this function.

This code will require that you reference the JSON.Net assembly or that it's in the bin directory if you're including this on an ASP.Net site (and binding to a GridView control, etc).

VB.Net

        ''' <summary>
        ''' Converts JSON that is not nested into a DataTable.  Typically this would be JSON that represents the contents of a table that
        ''' is not nested.
        ''' </summary>
        ''' <param name="json"></param>
        ''' <param name="tableName"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function JsonToDataTable(json As String, tableName As String) As DataTable
            Dim columnsCreated As Boolean = False
            Dim dt As New DataTable(tableName)

            Dim root As Newtonsoft.Json.Linq.JObject = Newtonsoft.Json.Linq.JObject.Parse(json)
            Dim items As Newtonsoft.Json.Linq.JArray = DirectCast(root(tableName), Newtonsoft.Json.Linq.JArray)

            Dim item As Newtonsoft.Json.Linq.JObject
            Dim jtoken As Newtonsoft.Json.Linq.JToken

            For i As Integer = 0 To items.Count - 1
                ' Create the columns once
                If columnsCreated = False Then
                    item = DirectCast(items(i), Newtonsoft.Json.Linq.JObject)
                    jtoken = item.First

                    While jtoken IsNot Nothing
                        dt.Columns.Add(New DataColumn(DirectCast(jtoken, Newtonsoft.Json.Linq.JProperty).Name.ToString()))
                        jtoken = jtoken.[Next]
                    End While

                    columnsCreated = True
                End If

                ' Add each of the columns into a new row then put that new row into the DataTable
                item = DirectCast(items(i), Newtonsoft.Json.Linq.JObject)
                jtoken = item.First

                ' Create the new row, put the values into the columns then add the row to the DataTable
                Dim dr As DataRow = dt.NewRow

                While jtoken IsNot Nothing
                    dr(DirectCast(jtoken, Newtonsoft.Json.Linq.JProperty).Name.ToString()) = DirectCast(jtoken, Newtonsoft.Json.Linq.JProperty).Value.ToString()
                    jtoken = jtoken.[Next]
                End While

                dt.Rows.Add(dr)
            Next

            Return dt

        End Function	

C#

/// Converts JSON that is not nested into a DataTable.  Typically this would be JSON that represents the contents of a table that
/// is not nested.
/// </summary>
/// <param name="json"></param>
/// <param name="tableName"></param>
/// <returns></returns>
/// <remarks></remarks>
public static DataTable JsonToDataTable(string json, string tableName)
{
	bool columnsCreated = false;
	DataTable dt = new DataTable(tableName);

	Newtonsoft.Json.Linq.JObject root = Newtonsoft.Json.Linq.JObject.Parse(json);
	Newtonsoft.Json.Linq.JArray items = (Newtonsoft.Json.Linq.JArray)root(tableName);

	Newtonsoft.Json.Linq.JObject item = default(Newtonsoft.Json.Linq.JObject);
	Newtonsoft.Json.Linq.JToken jtoken = default(Newtonsoft.Json.Linq.JToken);

	for (int i = 0; i <= items.Count - 1; i++) {
		// Create the columns once
		if (columnsCreated == false) {
			item = (Newtonsoft.Json.Linq.JObject)items(i);
			jtoken = item.First;

			while (jtoken != null) {
				dt.Columns.Add(new DataColumn(((Newtonsoft.Json.Linq.JProperty)jtoken).Name.ToString()));
				jtoken = jtoken.Next;
			}

			columnsCreated = true;
		}

		// Add each of the columns into a new row then put that new row into the DataTable
		item = (Newtonsoft.Json.Linq.JObject)items(i);
		jtoken = item.First;

		// Create the new row, put the values into the columns then add the row to the DataTable
		DataRow dr = dt.NewRow;

		while (jtoken != null) {
			dr(((Newtonsoft.Json.Linq.JProperty)jtoken).Name.ToString()) = ((Newtonsoft.Json.Linq.JProperty)jtoken).Value.ToString();
			jtoken = jtoken.Next;
		}

		dt.Rows.Add(dr);
	}

	return dt;

}