How to Convert DataTable to JSON in C#

Converting DataTable to JSON is one of those tasks that we come across from time to time and it seems like there should be a built-in method to do it. Unfortunately, that is not the case and we need to figure out a way to do it on our own.

In this article, we will examine different ways to accomplish this and compare the performances of each proposed method.

Let’s dive in.

Support Code Maze on Patreon to get rid of ads and get the best discounts on our products!

Serialize a DataTable Using Newtonsoft.Json Package

We will start with the method that is easiest to implement.

JSON.NET library supports serialization and deserialization of DataTable out-of-the-box. 

So, we can implement a method that takes advantage of this:

public static string DataTableNewtonsoftJsonNet(DataTable dataTable)
{
    if (dataTable == null)
    {
        return string.Empty;
    }

    return Newtonsoft.Json.JsonConvert.SerializeObject(dataTable);
}

We can see it is pretty straightforward to convert DataTable to JSON using the JsonConvert class which is defined in the Newtonsoft.Json namespace.

Serialize a DataTable Using System.Text.Json

Unlike JSON.NET, the native System.Text.Json library does not support DataTable conversion out-of-the-box. 

We can still make use of it with a small workaround:

public static string DataTableSystemTextJson(DataTable dataTable)
{
    if (dataTable == null)
    {
        return string.Empty;
    }

    var data = dataTable.Rows.OfType<DataRow>()
                .Select(row => dataTable.Columns.OfType<DataColumn>()
                    .ToDictionary(col => col.ColumnName, c => row[c]));

    return System.Text.Json.JsonSerializer.Serialize(data);
}

Although DataTable serialization is not supported, with a bit of the LINQ magic, we can convert DataTable to a list of KeyValue objects. 

Now we can use JsonSerializer on this new structure to get the resulting JSON string.

Construct JSON String

Let’s examine a more “manual” approach to this problem that revolves around the idea of composing a JSON string on our own.

For this, we will define two methods. The first one uses StringBuilder:

public static string DataTableStringBuilder(DataTable dataTable)
{
    if (dataTable == null)
    {
        return string.Empty;
    }

    var jsonStringBuilder = new StringBuilder();
    if (dataTable.Rows.Count > 0)
    {
        jsonStringBuilder.Append("[");
        for (int i = 0; i < dataTable.Rows.Count; i++)
        {
            jsonStringBuilder.Append("{");
            for (int j = 0; j < dataTable.Columns.Count; j++)
                jsonStringBuilder.AppendFormat("\"{0}\":\"{1}\"{2}",
                        dataTable.Columns[j].ColumnName.ToString(),
                        dataTable.Rows[i][j].ToString(),
                        j < dataTable.Columns.Count - 1 ? "," : string.Empty);

            jsonStringBuilder.Append(i == dataTable.Rows.Count - 1 ? "}" : "},");
        }
        jsonStringBuilder.Append("]");
    }

    return jsonStringBuilder.ToString();
}

The second method uses LINQ to construct the result we need:

public static string DataTableLinq(DataTable dataTable)
{
    if (dataTable == null)
    {
        return string.Empty;
    }

    return "[" 
            + string.Join(",", dataTable.Rows.OfType<DataRow>()
            .Select(row =>
                "{" 
                + string.Join(",", dataTable.Columns.OfType<DataColumn>()
                    .Select(col => string.Format("\"{0}\":\"{1}\"", 
                                        col.ColumnName, 
                                        row[col].ToString())))
                + "}"))
            + "]";
}

These two methods can be useful when we have a DataTable with a simpler, well-defined structure. If the structure is complicated, it is not revised to use this kind of “manual” approach.

Performance Tests

Let’s now compare how these methods perform by running benchmark tests.

First, we will 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; }
}

We will generate a test DataTable using the Bogus library which will be covered in one of our future articles.

To perform tests we will define a class Benchmark that contains GenerateDummyDataTable which generates our test data:

private readonly Faker<Student> _faker = new Faker<Student>();

...

public DataTable GenerateDummyDataTable(int count = 1000)
{
    var dt = new DataTable();
    dt.Columns.Add("FirstName");
    dt.Columns.Add("LastName");
    dt.Columns.Add("BirthYear");

    foreach (var item in _faker
        .RuleFor(m => m.FirstName, faker => faker.Person.FirstName)
        .RuleFor(m => m.LastName, faker => faker.Person.LastName)
        .RuleFor(m => m.BirthYear, faker => faker.Person.DateOfBirth.Year)
        .Generate(count))
        dt.Rows.Add(new object[] { item.FirstName, item.LastName, item.BirthYear });
    
    return dt;
}

Now we can create the methods we will use to perform benchmarking:

[Benchmark]
public void NewtonsoftJsonNet() => Methods.DataTableNewtonsoftJsonNet(dataTable);

[Benchmark]
public void SystemTextJson() => Methods.DataTableSystemTextJson(dataTable);

[Benchmark]
public void Linq() => Methods.DataTableLinq(dataTable);

[Benchmark]
public void StringBuilder() => Methods.DataTableStringBuilder(dataTable);

Since we want to test our methods against a DataTable of different sizes, we will define additional classes that inherit the main Benchmark class but generate test data of different sizes:

public class Benchmark_10000 : Benchmark
{
    public Benchmark_10000()
    {
        dataTable = GenerateDummyDataTable(10000);
    }
}

public class Benchmark_100000 : Benchmark
{
    public Benchmark_100000()
    {
        dataTable = GenerateDummyDataTable(100000);
    }
}

public class Benchmark_1000000 : Benchmark
{
    public Benchmark_1000000()
    {
        dataTable = GenerateDummyDataTable(1000000);
    }
}

Finally, we are ready to run the tests.

First, let’s see the performance on a DataTable with 1,000 records:

|            Method |     Mean |    Error |   StdDev |   Median | Rank | Allocated |
|------------------ |---------:|---------:|---------:|---------:|-----:|----------:|
| NewtonsoftJsonNet | 504.1 us |  3.71 us |  3.28 us | 503.6 us |    1 | 295.92 KB |
|     StringBuilder | 550.2 us |  2.75 us |  2.58 us | 550.2 us |    2 | 248.55 KB |
|    SystemTextJson | 852.1 us | 16.39 us | 20.73 us | 849.8 us |    3 |  521.1 KB |
|              Linq | 965.4 us | 18.55 us | 15.49 us | 960.5 us |    4 | 958.85 KB |

Let’s now see how the methods perform on a DataTable with 10,000 records:

|            Method |      Mean |     Error |    StdDev |    Median | Rank | Allocated |
|------------------ |----------:|----------:|----------:|----------:|-----:|----------:|
| NewtonsoftJsonNet |  5.342 ms | 0.0802 ms | 0.1014 ms |  5.332 ms |    1 |   2.84 MB |
|     StringBuilder |  6.499 ms | 0.1107 ms | 0.1036 ms |  6.488 ms |    2 |   2.39 MB |
|    SystemTextJson |  9.996 ms | 0.1406 ms | 0.1315 ms |  9.979 ms |    3 |   5.08 MB |
|              Linq | 10.072 ms | 0.0806 ms | 0.0715 ms | 10.046 ms |    3 |   9.36 MB |

What about 100,000 records:

|            Method |     Mean |    Error |   StdDev |   Median | Rank | Allocated |
|------------------ |---------:|---------:|---------:|---------:|-----:|----------:|
| NewtonsoftJsonNet | 65.73 ms | 1.279 ms | 1.522 ms | 65.75 ms |    1 |   28.4 MB |
|              Linq | 69.30 ms | 1.181 ms | 1.105 ms | 69.59 ms |    2 |  93.57 MB |
|     StringBuilder | 79.16 ms | 1.457 ms | 1.363 ms | 78.85 ms |    3 |  23.82 MB |
|    SystemTextJson | 82.51 ms | 1.227 ms | 1.088 ms | 82.92 ms |    4 |  50.81 MB |

And finally, let’s see the performance on 1,000,000 records:

|            Method |     Mean |    Error |   StdDev |   Median | Rank | Allocated |
|------------------ |---------:|---------:|---------:|---------:|-----:|----------:|
| NewtonsoftJsonNet | 616.1 ms |  5.41 ms |  4.79 ms | 614.1 ms |    1 | 283.89 MB |
|              Linq | 670.7 ms | 12.74 ms | 12.51 ms | 668.1 ms |    2 | 935.59 MB |
|    SystemTextJson | 798.0 ms | 11.91 ms | 10.56 ms | 793.3 ms |    3 | 508.04 MB |
|     StringBuilder | 915.7 ms |  4.27 ms |  3.57 ms | 914.9 ms |    4 | 238.14 MB |

As we can see, besides simplicity, JSON.NET also has the best performance, both in terms of speed and memory usage, in all examples. Based on this, we can conclude it is the best choice for this task.

A bit surprisingly, the performance we get from the StringBuilder method is only slightly worse. The speed drops as the data size increases, but the memory usage is even better than we get with JSON.NET. 

LINQ method shows better timings as the size of the data increases but it uses the most memory in all examples. 

And in the end, a bit of a mediocre performance of the System.Text.Json method puts it in last place.

Conclusion

In this article, we learned about different methods to convert DataTable to JSON and investigated how these methods perform on example data of different sizes.

Liked it? Take a second to support Code Maze on Patreon and get the ad free reading experience!