DataTable to JSON string for amcharts

Question

I have a method in my model that queries my SQL Server database and stores the results in a datatable. Then, I am using the following method to convert that datatable into a JSON string.

public void ConvertDataTabletoJSONString(DataTable dt)
{
    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
    Dictionary<string, object> row;

    foreach (DataRow dr in dt.Rows)
    {
        row = new Dictionary<string, object>();
        foreach (DataColumn col in dt.Columns)
        {
            row.Add(col.ColumnName, dr[col]);
        }
        rows.Add(row);
    }

    JSONstring =  serializer.Serialize(rows);
}

My controller looks like this:

    public ActionResult getMonthlyData()
    {
        TDR_Monthly viewModel = new TDR_Monthly();
        viewModel.getList(0);
        return Json(viewModel.JSONstring, JsonRequestBehavior.AllowGet);
    }

My view's javascript AmCharts.makechart dataLoader has this:

        "dataLoader":
        {
            "url": "../TDR_MonthlyController/getMonthlyData",
            "format": "json"
        }

When I "test" the output of that string (by displaying the string's contents in my view, it shows properly in the browser, like so:

 [{"REGION":"Atlanta", "STATE_NAME":"Alabama", "STATE":"AL", "CATEGORY_ID":"0 ", "CATEGORY":"No Group", "COUNT":100, "DEFICIENCY1":0, "DEFICIENCY2":0, "RESCIND1":0, "RESCIND2":0}]

However, when I modify the browser's URL to execute the JSON call (http://localhost:49777/Monthly/getMonthlyData) and I open the JSON file, it looks like it has a crap tone of extra spacing and characters:

"[{\"REGION\":\"Atlanta             \",\"STATE_NAME\":\"Alabama                                                                                                                                                                                                                                                       \",\"STATE\":\"AL\",\"CATEGORY_ID\":\"0        \",\"CATEGORY\":\"No Group                                               \",\"COUNT\":100,\"DEFICIENCY1\":0,\"DEFICIENCY2\":0,\"RESCIND1\":0,\"RESCIND2\":0}]

The view itself informs me that it cannot parse it

Error parsing JSON file: ../TDR_MonthlyController/getMonthlyData

Any help would be appreciated!!!

Are you double serializing your string? Perhaps the answers from [this question](http://stackoverflow.com/questions/13833900/return-json-but-it-includes-backward-slashes-which-i-dont-want) might help. — xorspark, Jan 10 '17 at 12:46
xorspark it would seem that this might be the issue, but now I don't know how to convert my datatable to the proper JSON format for parsing. — blacksaibot, Jan 10 '17 at 12:56
Try `return Content(viewModel.JSONstring...` instead of `return Json(viewModel.JSONstring...` as you already have a JSON string. — Anderson Pimentel, Jan 10 '17 at 14:45

Anderson Pimentel · Accepted Answer · 2017-01-10T15:25:17.207

1

In getMontlhyData, try changing

return Json(viewModel.JSONstring);

to

return Content(viewModel.JSONstring, "application/json");

because you already have a JSON string. The Json() method is serializing it again.

edited Jan 10 '17 at 15:25

answered Jan 10 '17 at 14:48

Anderson Pimentel

5,086
2
32
54

Oh man, that was perfect! Thanks!!! The JSON file looks the way it should be, now I Just have to figure out why amcharts is still displaying that it cannot parse the file though =/ do you think its because returning Content does not create an actual JSON file?? – blacksaibot Jan 10 '17 at 15:01
Maybe it's lacking the content type. Check the updated answer. – Anderson Pimentel Jan 10 '17 at 15:25
1

yep, that makes the JSON file, but amcharts is still complaining about not being able to parse. I'm going to send a message to their support center and post the solution, should I find one. Thanks so much for your help. – blacksaibot Jan 10 '17 at 15:46

DataTable to JSON string for amcharts

1 Answers1