From DataTable in C# .NET to JSON

Question

I am pretty new at C# and .NET, but I've made this code to call a stored procedure, and I then want to take the returned DataTable and convert it to JSON.

    SqlConnection con = new SqlConnection("connection string here");
    SqlDataAdapter da = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("getDates", con);
    SqlParameter par = new SqlParameter("@PlaceID", SqlDbType.Int);
    par.Value = 42;
    da.SelectCommand = cmd;
    cmd.Parameters.Add(par);
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();

    con.Open();

    try{
        cmd.CommandType = CommandType.StoredProcedure;
        da.Fill(ds);
    }

My question then is what is the best/simplest way to do that? An example would be great as I'm still very new to this.

Answer 1

Although the JavaScriptSerializer (System.Web.Script.Serialization.JavaScriptSerializer) cannot convert a DataTable directly into JSON, it is possible to unpack a DataTable into a List that may then be serialized.

The following function converts an arbitrary DataTable into a JSON string (without prior knowledge about field names or data types):

public static string 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] = row[col];
        }
        list.Add(dict);
    }
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    return serializer.Serialize(list);
}