Why is my MVC controller returning JSON wrapped in quotes and unformatted?

Question

I'm not getting a proper JSON return. Any ideas? Is there a better way to do this? Do I need the DataTable?

public JsonResult ReportData(string pdfID)
{
    string sqlConnectionString = @"ConnectThatThangStuff;";
    string queryString = @"SELECT stuffs FROM Thingy;";

    var json = string.Empty;
    using (SqlConnection connection = new SqlConnection(sqlConnectionString))
    {
        SqlCommand cmd = new SqlCommand(queryString, connection);

        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();

        DataTable dt = new DataTable();
        dt.Load(reader);

        json = JsonConvert.SerializeObject(dt);
    }

    return Json(json, JsonRequestBehavior.AllowGet);
}

And what do I get for my efforts? This crap:

"[{\"transid\":1111,\"FromEmailAddress\":\"email@email.com\",\"Name\":\"bob doe\",\"EmailAddress\":\"email@email.com\",\"CreateDt\":\"4/08/2021\",\"ExpirationDt\":\"5/08/2021\",\"Status\":\"Complete\",\"FinalDt\":\"4/09/2021\"},{\"transid\":22222,\"FromEmailAddress\":\"email@email.com\",\"Name\":\"bill doe\",\"EmailAddress\":\"email@email.com\",\"CreateDt\":\"4/08/2021\",\"ExpirationDt\":\"5/08/2021\",\"Status\":\"Ready\",\"FinalDt\":null}]"

I need the JOSN to build a jQuery DataTable in my view. If there is an easier way to go from SQL Query -> jQuery DataTable, I'll take that also.

I'm not using Entity Framework.

If you need to serialize a `DataTable` using `System.Text.Json` see [Serialize DataSet with current version of System.Text.Json.JsonSerializer](https://stackoverflow.com/q/59780446/3744182). — dbc, Apr 12 '21 at 14:38

score 3 · Accepted Answer · answered Apr 09 '21 at 19:04

3

return Json(object); handles the serialization for you, try passing dt directly in there

answered Apr 09 '21 at 19:04

Tim

2,587
13
18

Ok thanks. When I do this, I get: `A circular reference was detected while serializing an object of type 'System.Reflection.RuntimeModule'.` – flashsplat Apr 09 '21 at 19:44

Brian Rogers · Answer 2 · 2021-04-09T21:24:30.967

3

As others have said, you are serializing twice. You can fix this issue while avoiding the circular reference problem by using the Content method instead of the Json method in your controller:

// serialize using Json.Net (handles DataTable cleanly)
json = JsonConvert.SerializeObject(dt);   

// Return the already-serialized JSON string with a content type of application/json
return Content(json, "application/json");

You will also need to change your method signature to return a ContentResult (or ActionResult) instead of a JsonResult.

edited Apr 09 '21 at 21:24

answered Apr 09 '21 at 21:18

Brian Rogers

125,747
31
299
300

1

Thanks, this is actually exactly what I ended up doing. – flashsplat Apr 09 '21 at 21:42

Bruno Brant · Answer 3 · 2021-04-09T19:18:11.477

You are converting the object to JSON twice:

json = JsonConvert.SerializeObject(dt);

return Json(json, JsonRequestBehavior.AllowGet);

In fact, if you look closely, you'll see that your json variable is a string.

When you pass the string to the Json method, it converts a string to json, which is what you got.

I suggest you skip the call to JsonConvert.Serialize and just call Json(dt).

However, a small piece of advice: JsonConvert.Serialize is calling Newtonsoft Json (JSON.NET) to do the serialization. By calling Json directly, ASP.Net will handle the serialization, and if not properly configured, you might have System.Text.Json taking care of serialization for you, which changes a bit how you deal with serialization issues like changing casing, customizing property names and dealing with conversions.

Thanks for the detailed explanation. I'm now getting this error: `A circular reference was detected while serializing an object of type 'System.Reflection.RuntimeModule'.` Which I will research. — flashsplat, Apr 09 '21 at 19:45

Why is my MVC controller returning JSON wrapped in quotes and unformatted?

3 Answers3