How to Convert JSON to DataTable in C#

In this article, we are going to discuss various methods that we can use to convert JSON to DataTable in C#. A DataTable is a representation of a database table that we can use to store data as a collection of rows and columns in a grid format.

All the methods that we will implement are for converting the sample JSON string in the article to a DataTable.

At the end of the article, we will compare the performance of all the methods using benchmark tests.

Let’s dive in.

Prepare the Environment

To start, let’s define a sample JSON array:

private const string _sampleJson
            = @"[{""FirstName"":""Conrad"",
                  ""LastName"":""Ushie"",
                  ""BirthYear"":1995,
                  ""Subject"":""Physics""}]";

We are going to use this string as the sample JSON for all the methods that we will discuss.

Now, let’s look at the various methods we can use to serialize JSON to a DataTable in C#.

Convert JSON to DataTable Using Newtonsoft.Json

The simplest way we can convert JSON to a DataTable object is by using the Newtonsoft.Json library:

public static DataTable? UseNewtonsoftJson(string sampleJson = _sampleJson)
{
    DataTable? dataTable = new();
    if (string.IsNullOrWhiteSpace(sampleJson))
    {
        return dataTable;
    }

    dataTable = JsonConvert.DeserializeObject<DataTable>(sampleJson);

    return dataTable;
}

In this method, we create a new DataTable and then handle two scenarios. First, we check if the JSON string is null or consists of only whitespaces, if it is, we return an empty DataTable.

Then, if the string is not empty, we use the JsonConvert.DeserializeObject<DataTable>() method from the Newtonsoft.Json library to deserialize it to a DataTable. This method does all the table, column, and row creation activities behind the scenes.

Finally, we insert the values from our JSON into the DataTable and return the DataTable.

Convert JSON to DataTable Using System.Text.Json

Another way we can convert JSON to a DataTable in C#, is by using the System.Text.Json built-in library:

public static DataTable? UseSystemTextJson(string sampleJson = _sampleJson)
{
    DataTable? dataTable = new();
    if (string.IsNullOrWhiteSpace(sampleJson))
    {
        return dataTable;
    }

    JsonElement data = JsonSerializer.Deserialize<JsonElement>(sampleJson);
    if (data.ValueKind != JsonValueKind.Array)
    {
        return dataTable;
    }

    var dataArray = data.EnumerateArray();
    JsonElement firstObject = dataArray.First();

    var firstObjectProperties = firstObject.EnumerateObject();
    foreach (var element in firstObjectProperties)
    {
        dataTable.Columns.Add(element.Name);
    }

    foreach (var obj in dataArray)
    {
        var objProperties = obj.EnumerateObject();
        DataRow newRow = dataTable.NewRow();
        foreach (var item in objProperties)
        {
            newRow[item.Name] = item.Value;
        }
        dataTable.Rows.Add(newRow);
    }

    return dataTable;
}

Unlike the Newtonsoft.Json package, the System.Text.Json library does not provide a method to convert JSON to a DataTable. However, we will implement a method that uses this library.

Here, we invoke the JsonSerializer.Deserialize<JsonElement>() method to deserialize the JSON string into a JsonElement.

Then, we check if the JSON is an array. If it is not, we return an empty DataTable. If it is, we iterate through the first object’s properties and add them as columns to our DataTable using the dataTable.Columns.Add() method.

Next, we iterate through the objects in our array. For each object, we get its properties and invoke the dataTable.NewRow()  method to create a new row. We then iterate through these properties and add their values to the row. After adding all the values to a particular row, we call the dataTable.Rows.Add() method to add the row to our table.

Finally, we return the filled DataTable.

Convert JSON to DataTable by Manual Creation of a DataTable

Lastly, we can define a method that manually creates a DataTable and fills it with data from our input JSON.

Now, let’s see how this works by implementing a ManuallyConvertJsonToDataTable() method:

public static DataTable? ManuallyConvertJsonToDataTable(string sampleJson = _sampleJson)
{
    DataTable? dataTable = new();
    if (string.IsNullOrWhiteSpace(sampleJson))
    {
        return dataTable;
    }

    var cleanedJson = Regex.Replace(sampleJson, "\\\\| |\n|\r|\t|\\[|\\]|\"", "");
    var items = Regex.Split(cleanedJson, "},{").AsSpan();

    for (int i = 0; i < items.Length; i++)
    {
        items[i] = items[i].Replace("{", "").Replace("}", "");
    }

    var columns = Regex.Split(items[0], ",").AsSpan();

    foreach (string column in columns)
    {
        var parts = Regex.Split(column, ":").AsSpan();
        dataTable.Columns.Add(parts[0].Trim());
    }

    for (int i = 0; i < items.Length; i++)
    {
        var row = dataTable.NewRow();
        var values = Regex.Split(items[i], ",").AsSpan();
        for (int j = 0; j < values.Length; j++)
        {
            var parts = Regex.Split(values[j], ":").AsSpan();
            if (int.TryParse(parts[1].Trim(), out int temp))
                row[j] = temp;
            else
                row[j] = parts[1].Trim();
        }
        dataTable.Rows.Add(row);
    }

    return dataTable;
}

Here, we use Regex to clean the input JSON by removing certain characters like whitespaces and newlines.

Then, we invoke the Regex.Split() method to split the JSON into an array of strings at every point where the pattern },{  is found and the array is stored in the items variable.

In this manual method, we invoke the AsSpan() method to create a Span over a target array to enhance performance and reduce memory usage. Visit How to Use Span in C# to see how to utilize the Span<T> struct to achieve this.

We then use method chaining by calling the Replace()  method twice to remove the curly brackets that were missed during the Regex cleanup.

Next, we define a variable columns and set it to an array returned from the Regex.Split() method that splits the first item in the string array by a comma symbol.

After that, we iterate over columns. At each iteration, we first define a variable named parts that will store the result we get when we split each item in columns at every point where the colon symbol appears. Then, we call dataTable.Columns.Add(parts[0].Trim()) to add the first item in parts as a column to our DataTable.

After creating our columns, we loop through the items array again to add the data as rows to our DataTable. At each iteration, we call the dataTable.NewRow() method to create a new row. Then, we use Regex.Split(items[i], ",") to split each item into an array of values.

Once that is done, we iterate through this array of values and populate our new row with data. Because of the integer value in our sample JSON, for each value in the array, we check if the value can be converted to an integer, if it can, we assign the value as an int. After that, we invoke the Rows.Add() method to add each row to the DataTable.

Finally, we return the resulting DataTable object, which contains the data from our sample JSON string in a tabular format.

Benchmark All Methods

Now, let’s use the BenchmarkDotNet library to compare the performances of these three methods that we can use to convert JSON to DataTable in C#.

First, let’s define our test entity Student:

public class Student
{
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public int BirthYear { get; set; }
    public string Subject { get; set; } = string.Empty;
}

Next, let’s create a GenerateBenchmarkStudentJson class:

public static string? Generate10000StudentsJsonData()
{
    var students = new List<Student>();
    for (int i = 0; i < 10000; i++)
    {
        students.Add(new Student
        {
            FirstName = "Student " + i,
            LastName = "Male",
            BirthYear = 2000 + i,
            Subject = "Physics"
        });
    }

    var json = JsonConvert.SerializeObject(students);

    return json;
}

In this class, we define a Generate10000StudentsJsonData() method that will generate the sample data for our benchmark tests. First, we create a list of 10000 fake students’ data. Then, we invoke the JsonConvert.SerializeObject() to convert the list to a JSON string.

Now, let’s create our benchmark class:

[MemoryDiagnoser(false)]
[Orderer(SummaryOrderPolicy.FastestToSlowest)]
public class ConvertMethodsBenchMark
{
    private static readonly string _sampleJson
        = GenerateBenchmarkStudentJson.Generate10000StudentsJsonData();

    [Benchmark]
    public void UseNewtonsoftJson() => ConvertMethods.UseNewtonsoftJson(_sampleJson);

    [Benchmark]
    public void UseSystemTextJson() => ConvertMethods.UseSystemTextJson(_sampleJson);

    [Benchmark]
    public void ManuallyConvertJsonToDataTable()
        => ConvertMethods.ManuallyConvertJsonToDataTable(_sampleJson);
}

Visit Introduction to Benchmarking C# Projects to see how to use the BenchmarkDotNet library.

Once we run the benchmark, we can inspect the results on the console:

|                         Method |      Mean |    Error |    StdDev |    Median | Allocated |
|------------------------------- |----------:|---------:|----------:|----------:|----------:|
|              UseNewtonsoftJson |  58.55 ms | 1.901 ms |  5.604 ms |  59.84 ms |   3.95 MB |
|              UseSystemTextJson |  75.75 ms | 1.823 ms |  5.376 ms |  77.61 ms |  11.01 MB |
| ManuallyConvertJsonToDataTable | 165.52 ms | 4.348 ms | 12.821 ms | 170.21 ms |  25.63 MB |

As we can see, using the NewtonsoftJson library is the fastest way, with an average time of 58.55 ms. Also, this method utilizes the least amount of memory. Therefore, if we consider the fact that it was the easiest method to implement and it produced results faster than the others, we can conclude that it is the best way to convert JSON to a DataTable in C#.

Unsurprisingly, the System.Text.Json library is slightly slower, with an average time of 75.75 ms. It also consumes more memory than the UseNewtonsoftJson() method.

Finally, although we implemented the manual method, particularly for our sample JSON, it is the slowest, with an average time of 165.52 ms. The method also uses the most memory.

Conclusion

In this article, we explored different methods that we can use to convert JSON to DataTable in C#. Then, we compared their performances using some benchmark tests.

To learn how to perform the reverse process, that is, converting a DataTable to JSON, you can visit How to Convert DataTable to JSON in C#.