Converting a dataset to JSON using .NET 3.5 in C#

Question

I have been searching for a simple way to convert a dataset from a PostgreSQL database to JSON for use in a project that I am building.

This is my first time using JSON, and I have found it really tricky to find a simple way of doing this. I have been using a StringBuilder at the moment to create a JSON string from the information in the dataset, but I have heard that it is possible to do this very simply with .NET 3.5 using the System.Runtime.Serialization namespace, though I have yet to find a simple article or blog on how this is done! What is the easiest way to do this?

Award

Answer 1

Use Newtonsofts Json.Net and check out DataTable JSON Serialization in JSON.NET and JavaScriptSerializer where it's used to create a DataSet-to-JSON converter.

Barnyard

Answer 2

For others looking at this topic:

Here is the simplest way to convert a dataset to a JSON array as json_encode (PHP) does with ASP.Net:

using Newtonsoft.Json;

public static string ds2json(DataSet ds) {
    return JsonConvert.SerializeObject(ds, Formatting.Indented);
}

Ronaldronalda

Answer 3

public static string GetJSONString(DataTable Dt)
{
    string[] StrDc = new string[Dt.Columns.Count];
    string HeadStr = string.Empty;

    for (int i = 0; i < Dt.Columns.Count; i++)
    {
        StrDc[i] = Dt.Columns[i].Caption;
        HeadStr += "\"" + StrDc[i] + "\" : \"" + StrDc[i] + i.ToString() + "¾" + "\",";
    }

    HeadStr = HeadStr.Substring(0, HeadStr.Length - 1);

    StringBuilder Sb = new StringBuilder();
    Sb.Append("{\"" + Dt.TableName + "\" : [");

    for (int i = 0; i < Dt.Rows.Count; i++)
    {
        string TempStr = HeadStr;
        Sb.Append("{");

        for (int j = 0; j < Dt.Columns.Count; j++)
        {
            TempStr = TempStr.Replace(Dt.Columns[j] + j.ToString() + "¾", Dt.Rows[i][j].ToString());
        }
        Sb.Append(TempStr + "},");
    }

    Sb = new StringBuilder(Sb.ToString().Substring(0, Sb.ToString().Length - 1));
    Sb.Append("]}");

    return Sb.ToString();
}

---

var JObject = eval('(' + JSONString + ');');
for(var i = 0; i < JObject .Employees.length; i++)
{

var val1 = JObject.Employees[i].EmployeeID;

var val2 = JObject.Employees[i].NationalIDNumber;

var val3 = JObject.Employees[i].Title;

var val4 = JObject.Employees[i].BirthDate;

var val5 = JObject .Employees[i].HireDate ;

}

Occult

Answer 4

public static string dataSetToJsonSt(DataSet dataDs)
{

    int rowsCountIn = dataDs.Tables[0].Rows.Count;
    int colsCountIn = dataDs.Tables[0].Columns.Count;
    string[] columnNamesAr = new string[dataDs.Tables[0].Columns.Count];

    for (int i = 0; i < colsCountIn; i++){

        columnNamesAr[i] = dataDs.Tables[0].Columns[i].ColumnName;
    }

    string jsonSt = "[";

    for (int i = 0; i < rowsCountIn; i++)
    {
        jsonSt += "{";

        for (int j = 0; j < colsCountIn; j++)
        {
            jsonSt += ((jsonSt.Substring(jsonSt.Length - 1) == "{" ? "" : ",") + "\"" + columnNamesAr[j] + "\":" + (dataDs.Tables[0].Rows[i][j] == DBNull.Value ? "null" : ("\"" + dataDs.Tables[0].Rows[i][j] + "\"")));
        }

        jsonSt += ("}" + (i < (rowsCountIn - 1) ? "," : ""));
    }

    jsonSt += "]";

    return jsonSt;
}

Welty

Answer 5

Maybe you've heard about the system.runtime.serialization.json namespace in the newly announced .NET Framework 4.0.

Tussle

I have tried using the serialization.json namespace, but to no avail... There sont seem to be many examples on how to use it and the JavaScriptSerializer and the DataContractJsonSerializer are outputting XML – Award

Answer 6

public static T WriteJson<T>(this System.Data.DataSet dataSet)
{
    try
    {
        XmlDocument doc = new XmlDocument();
        doc.LoadXml(dataSet.GetXml());
        return (T)Convert.ChangeType(JsonConvert.SerializeXmlNode(doc).Replace("null", "\"\"").Replace("'", "\'"), typeof(T));
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Gallon

Welcome to StackOverflow. While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. – Dissimilarity