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


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

C#

/// <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">The JSON for the table structure</param>
/// <param name="tableName">The name of the table as defined in the JSON.  For this implementation the table name must be defined.</param>
/// <code>
///  {
///  "Table1": [
///     {
///       "id": 0,
///       "item": "item 0"
///     },
///     {
///       "id": 1,
///       "item": "item 1"
///     }
///   ]
/// }
///  </code>
public static DataTable JsonToDataTableNew(string json, string tableName)
{
    var dt = new DataTable(tableName);
    var root = JObject.Parse(json);
    var items = root[tableName] as JArray;

    if (items == null || items.Count == 0)
    {
        return dt;
    }

    // We know we have at least the first item, we'll use that to create the
    // properties on the DataTable.
    JObject item = items[0] as JObject;
    JProperty jprop;

    // Create the columns
    foreach (var p in item.Properties())
    {
        dt.Columns.Add(new DataColumn(p.Name));
    }

    JToken jtoken;
    JObject obj;

    for (int i = 0; i <= items.Count - 1; i++)
    {
        // Create the new row, put the values into the columns then add the row to the DataTable
        var dr = dt.NewRow();

        // Add each of the columns into a new row then put that new row into the DataTable
        obj = items[i] as JObject;
        jtoken = obj.First;

        while (jtoken != null)
        {
            jprop = jtoken as JProperty;
            dr[jprop.Name] = jprop.Value.ToString();
            jtoken = jtoken.Next;
        }

        dt.Rows.Add(dr);
    }

    return dt;
}

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

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.