How to Convert Datable and Dataset to JSON in C#

Today in this article we’ll see how to generate JSON from Table and DataSet in C# using a database (RDBMS) in a .NET or .NET Core-based application.

We often find the need to serialize Table to JSON or Deserialize JSON to Table conversion in application development.

Today in this article, we will cover below aspects,

Let’s get started

Newtonsoft.Json (JSON.NET) helps you generate a JSON from a Table is the simplest way to achieve the conversion.

Let’s Install Newtonsoft JSON from the Nuget package Manager,

Or

PM> Install-Package Newtonsoft.Json -Version 12.0.3

Here is the sample Table, we are dealing with

Datable to JSON Conversion

JSON.NET has utility methods out of the box to help the conversion.

Use the SerializeObject method to convert the Datable into JSON string conversion.

private string ConvertDataTableasJSON(DataTable dataTable)
        {
            return JsonConvert.SerializeObject(dataTable);
        }

The generated JSON content looks as below,

[
  {
    "ID": "1001",
    "Name": "",
    "City": "City1",
    "Country": "USA"
  },
  {
    "ID": "1002",
    "Name": "PQRS",
    "City": "City2",
    "Country": "INDIA"
  },
  {
    "ID": "1003",
    "Name": "XYZZ",
    "City": "City3",
    "Country": "CHINA"
  },
  {
    "ID": "1004",
    "Name": "LMNO",
    "City": "City4",
    "Country": "UK"
  }
]

DataSet to JSON Conversion

JSON.NET has utility methods out of the box to help the conversion from even DataSet to JSON easily,

Use the SerializeObject method to convert the DataSet into JSON string conversion.

private static string ConvertDataSetasJSON(DataSet dataSet)
        {
            return JsonConvert.SerializeObject(dataSet);
        }

The generated JSON content looks as below,

{
  "Table1": [
    {
      "ID": "1001",
      "Name": "",
      "City": "City1",
      "Country": "USA"
    },
    {
      "ID": "1002",
      "Name": "PQRS",
      "City": "City2",
      "Country": "INDIA"
    },
    {
      "ID": "1003",
      "Name": "XYZZ",
      "City": "City3",
      "Country": "CHINA"
    },
    {
      "ID": "1004",
      "Name": "LMNO",
      "City": "City4",
      "Country": "UK"
    }
  ],
  "Table2": [
    {
      "ID": "1001",
      "Name": "",
      "City": "City1",
      "Country": "USA"
    },
    {
      "ID": "1002",
      "Name": "PQRS",
      "City": "City2",
      "Country": "INDIA"
    },
    {
      "ID": "1003",
      "Name": "XYZZ",
      "City": "City3",
      "Country": "CHINA"
    },
    {
      "ID": "1004",
      "Name": "LMNO",
      "City": "City4",
      "Country": "UK"
    }
  ]
}

Using System.Text.Json for DataTable to JSON ??

You may want to use the recently released System.Text.Json as a serializer or deserializer for the .NET Core 3.0 and above application.

However, the default JSON serializer(i.e. System.Text.Json) used in ASP.NET Core 3.0 or Non-host application(Console or Windows Form or WPF) and the above version doesn’t support DataTable/Dataset to JSON conversion yet.

I end up getting the below error “System.Text.Json.JsonException: ‘A possible object cycle was detected which is not supported.”

ReferenceLoopHandling and datable support are not yet added in the System.Text.Json serializer. This feature will be supported most in probably .NET 5 version in the future.

Until then you should be relying on any custom workaround or using Newtonsoft(JSON.Net).

Other references:

• Adding NewtonSoft.JSON support in ASP.NET Core

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.