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.
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.
“there should be a built-in method to do it”. I was surprised to see this statement. For years now, I have had all my webAPIs like this
public object GetData(MyContext c)
{
var ds=new DataSet(“R”);
//setup some parameters p
c.GetData(ds, “SELECT * FROM Table1”, “T1”, p);
c.GetData(ds, “SELECT * FROM Table2”, “T2”, p);
return ds;
}

Actually, it does not seem to matter what I return, the object automatically gets serialized to json.
Hi, Byron! Thank you for the comment. I agree it is possible to return serialized dataset or a data table in the API endpoint and it would be serialized to a selected format, including JSON. It is the consequence of the API configuration, not actually the built-in support we were looking for in this article.
Your GenerateDummyDataTable method uses a _faker object, which isn’t identified.
I assume that the “dataTable.Rows.OfType()” references should be dataTable.Rows.OfType<DataRow>(), with the generic type being swallowed by html. I prefer using “dataTable.Rows.Cast<DataRow>()” but it’s pretty much the same either way.
It’s funny that System.Text.Json did so badly here, since it’s whole reason for existing is to be faster that Newtonsoft. The delay here is probably because it has to do two conversions.
The StringBuilder version would probably get a boost, if you preallocated it to a reasonable large size. I’d suggest something like : var jsonStringBuilder = new StringBuilder(dataTable.Columns.Count * dataTable.Rows.Count * 25);
Hi James.
Regarding the _faker field, we didn’t show it in the snippet but it exists in our source code. Now, I added it to the snippet as well to avoid confusion.
About those OfType methods, yeah, that was exactly the case. Somehow the snippet swallowed those <DataRow> and <DataColumn> parts, I’ve fixed those as well.
About the last suggestion, we will see with the author of the article to test it.
Thank you as always.
Hi, James!
Thank you for the comment. I’ve tested your suggestion regarding the StringBuilder and there is a slight improvement in the performance but it is noticeable on a smaller amount of records. For example, for 1000 records StringBuilder is even faster than Newtonsoft. But the order of methods per performance is the same in all other examples.