Converting Datatable And Dataset To JSON String And Vice Versa

In this blog, we will learn how to convert a datatable and dataset into a JSON string and vice versa.

Here, in this blog, I have described four methods to convert a datatable or a dataset into a JSON string and vice versa.

 

For this, you have to use the following namespaces.

using System;
using System.Xml;
using System.Data;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Web.Script.Serialization;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

First Method - DataTable to JSON String

 

Here, I have used the JavaScriptSerializer class to covert the DataTable into a JSON string.

public static object dataTableToJSON(DataTable table)
{
    var list = new List<Dictionary<string, object>>();
    foreach (DataRow row in table.Rows)
    {
        var dict = new Dictionary<string, object>();
        foreach (DataColumn col in table.Columns)
        {
            dict[col.ColumnName] = (Convert.ToString(row[col]));
        }
        list.Add(dict);
    }
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    return serializer.Serialize(list);
}

Second Method - Dataset to JSON string

public static object dataSetToJSON(DataSet ds)
{
    ArrayList root = new ArrayList();
    List<Dictionary<string, object>> table;
    Dictionary<string, object> data;

    foreach (DataTable dt in ds.Tables)
    {
        table = new List<Dictionary<string, object>>();
        foreach (DataRow dr in dt.Rows)
        {
            data = new Dictionary<string, object>();
            foreach (DataColumn col in dt.Columns)
            {
                data.Add(col.ColumnName, dr[col]);
            }
            table.Add(data);
        }
        root.Add(table);
    }
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    return serializer.Serialize(root);
}

Third Method - JSON String to DataTable

 

Here, I have used Newtonsoft.

public static DataTable jsonToDataTable(string jsonString)
{
    var jsonLinq = JObject.Parse(jsonString);

    // Find the first array using Linq
    var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
    var trgArray = new JArray();
    foreach (JObject row in srcArray.Children<JObject>())
    {
        var cleanRow = new JObject();
        foreach (JProperty column in row.Properties())
        {
            // Only include JValue types
            if (column.Value is JValue)
            {
                cleanRow.Add(column.Name, column.Value);
            }
        }
        trgArray.Add(cleanRow);
    }

    return JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());
}

Or you can use this also to convert the JSON String into DataTable.

public static DataTable JsonStringToDataTable(string jsonString)
{
    DataTable dt = new DataTable();
    string[] jsonStringArray = Regex.Split(jsonString.Replace("[", "").Replace("]", ""), "},{");
    List<string> ColumnsName = new List<string>();
    foreach (string jSA in jsonStringArray)
    {
        string[] jsonStringData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
        foreach (string ColumnsNameData in jsonStringData)
        {
            try
            {
                int idx = ColumnsNameData.IndexOf(":");
                string ColumnsNameString = ColumnsNameData.Substring(0, idx - 1).Replace("\"", "");
                if (!ColumnsName.Contains(ColumnsNameString))
                {
                    ColumnsName.Add(ColumnsNameString);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));
            }
        }
        break;
    }
    foreach (string AddColumnName in ColumnsName)
    {
        dt.Columns.Add(AddColumnName);
    }
    foreach (string jSA in jsonStringArray)
    {
        string[] RowData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
        DataRow nr = dt.NewRow();
        foreach (string rowData in RowData)
        {
            try
            {
                int idx = rowData.IndexOf(":");
                string RowColumns = rowData.Substring(0, idx - 1).Replace("\"", "");
                string RowDataString = rowData.Substring(idx + 1).Replace("\"", "");
                nr[RowColumns] = RowDataString;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                continue;
            }
        }
        dt.Rows.Add(nr);
    }
    return dt;
}

Fourth Method - JSON String to Dataset

public static DataSet jsonToDataSet(string jsonString)
{
    try
    {
        XmlDocument xd = new XmlDocument();
        jsonString = "{ \"rootNode\": {" + jsonString.Trim().TrimStart('{').TrimEnd('}') + "} }";
        xd = (XmlDocument)JsonConvert.DeserializeXmlNode(jsonString);
        DataSet ds = new DataSet();
        ds.ReadXml(new XmlNodeReader(xd));
        return ds;
    }
    catch (Exception ex)
    {
        throw new ArgumentException(ex.Message);
    }
}

 I hope this is useful to all the readers. Happy Coding!