How to Convert DataTable to JSON in ASP.NET Web API

Introduction

In this article, we will discuss how to return a DataTable as JSON in an ASP.NET Web API project. JSON (JavaScript Object Notation) is a lightweight data format widely used for data exchange between a server and a client. By converting the DataTable into JSON format, we can easily consume the data in various client-side frameworks or applications.

Prerequisites

• You should have a basic understanding of ASP.NET Web API.
• You should have a DataTable that you want to convert to JSON.

To return a DataTable to JSON in an ASP.NET Web API, you can use one of the following methods:

To return a DataTable to JSON in an ASP.NET Web API, you can use one of the following methods.

• Use the System.Text.Json library. This library provides a method called JsonSerializer.Serialize() that can be used to serialize any object to JSON.
• Use the JSON.NET library. This library is a popular third-party library that provides a wide range of features for working with JSON. It also includes a method called SerializeObject() that can be used to serialize a DataTable to JSON.

1. Creating a DataTable and populating it with data.

private DataTable GetDataTable()
{
    DataTable dataTable = new DataTable();

    // Add columns to DataTable
    dataTable.Columns.Add("ID", typeof(int));
    dataTable.Columns.Add("Name", typeof(string));
    dataTable.Columns.Add("Age", typeof(int));

    // Add rows to DataTable
    dataTable.Rows.Add(1, "John Doe", 25);
    dataTable.Rows.Add(2, "Jane Smith", 30);
    dataTable.Rows.Add(3, "Bob Johnson", 40);

    return dataTable;
}

2. Converting DataTable to JSON using Newtonsoft.Json library.

private string ConvertDataTableToJson(DataTable dataTable)
{
    string jsonString = string.Empty;

    if (dataTable != null && dataTable.Rows.Count > 0)
    {
        jsonString = JsonConvert.SerializeObject(dataTable, Formatting.Indented);
    }

    return jsonString;
}

3. Creating a Web API controller to return DataTable as JSON.

public class DataTableController : ApiController
{
    [HttpGet]
    public IHttpActionResult GetDataTableAsJson()
    {
        DataTable dataTable = GetDataTable();

        if (dataTable == null)
        {
            return NotFound();
        }

        string json = ConvertDataTableToJson(dataTable);

        return Ok(json);
    }
}

Conclusion

In this article, we have demonstrated how to return a DataTable as JSON in an ASP.NET Web API project. By using the Newtonsoft.Json library, we can easily convert the DataTable into a JSON string representation. This allows for easier consumption of the data in client-side frameworks or applications that support JSON.