Can't get table to see row data from C# MVC JSON

rosborn@rti.org

I have now spent almost 2 days trying to get this to work. I'm calling a .NET (MVC) API to retrieve the data, but I can't quite get DataTables to see the row data.

HTML:

Column 1	Column 2	Column 3	Column 4

JAVASCRIPT:

<

script type="text/javascript">

$(document).ready(function () {

  $('#example').DataTable({
     "processing": true,
     "serverSide": false,
     "ajax": {
        url: "@Url.Action("GetReport", "Reports")",
        type: 'POST',
        data: function (d) {
           return JSON.stringify(d);
        }
     },
     "columns": [
        { "data": "Column1" },
        { "data": "Column2" },
        { "data": "Column3" },
        { "data": "Column4" }
     ]
  });

});

C# server code:

  [HttpPost]
  public JsonResult GetReport(DataTableAjaxPostModel model)
  {
     String rc = "";
     int rows = 0;

     using (var command = db.Database.Connection.CreateCommand())
     {
        command.CommandText = "SELECT * FROM DHAPP_vwTest";
        command.CommandType = System.Data.CommandType.Text;
        if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); }
        using (var result = command.ExecuteReader())
        {
           StringBuilder sb = new StringBuilder();
           StringWriter sw = new StringWriter(sb);

           using (JsonWriter jsonWriter = new JsonTextWriter(sw))
           {
              jsonWriter.WriteStartArray();

              while (result.Read())
              {
                 rows++;
                 jsonWriter.WriteStartObject();
                 //jsonWriter.WriteStartArray();

                 int fields = result.FieldCount;

                 for (int i = 0; i < fields; i++)
                 {
                    jsonWriter.WritePropertyName(result.GetName(i));
                    jsonWriter.WriteValue(result[i]);
                 }

                 jsonWriter.WriteEndObject();
                 //jsonWriter.WriteEndArray();
              }

              jsonWriter.WriteEndArray();

              rc = sw.ToString();
           }
        }
     }

     JsonResult j = Json(new
     {
        // this is what datatables wants sending back
        draw = model.draw,
        recordsTotal = rows,
        recordsFiltered = rows,
        data = rc
     });

     return j;
  }

SQL Query DHAPP_vwTest:

SELECT 'Col 1' AS Column1, 'Col 2' AS Column2, 'Col 3' AS Column3, 'Col 4' AS Column4
UNION
SELECT 'Col 12' AS Column1, 'Col 2' AS Column2, 'Col 3' AS Column3, 'Col 4' AS Column4
UNION
SELECT 'Col 13' AS Column1, 'Col 2' AS Column2, 'Col 3' AS Column3, 'Col 4' AS Column4

Running this displays the javascript exception:

DataTables warning: table id=example - Requested unknown parameter 'Column1' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

Debugger Output:

https://debug.datatables.net/icases

==========================================

I have tried all different combinations of dataSrc, but data for rows is just not "seen".

One thing I noticed is that the C# code escapes the double-quote characters - it uses \" instead of just ". So, I tried doing a Replace(@\,"") but still didn't work.

I tired trapping the retruned data by adding a dataSrc function:

$(document).ready(function () {

  $('#example').DataTable({
     "processing": true,
     "serverSide": false,
     "ajax": {
        url: "@Url.Action("GetReport", "Reports")",
        type: 'POST',
        data: function (d) {
           return JSON.stringify(d);
        },
        dataSrc: function (d2) {
           return d2;
        }
     },
     "columns": [
        { "data": "Column1" },
        { "data": "Column2" },
        { "data": "Column3" },
        { "data": "Column4" }
     ]
  });

});

I put a breakpoint in the javascript debugger and looked at d2 and it looks right. However, the DataTables debugger output seems top still have the escap characters in it, so maybe the is still the problem.

Here's an exact copy of the d2 variable in the javascripot Console:

data : "[{"Column1":"Col 1","Column2":"Col 2","Column3":"Col 3","Column4":"Col 4"},{"Column1":"Col 12","Column2":"Col 2","Column3":"Col 3","Column4":"Col 4"},{"Column1":"Col 13","Column2":"Col 2","Column3":"Col 3","Column4":"Col 4"}]"
draw : 0
recordsFiltered : 3
recordsTotal : 3

DataTables Debugger:
https://debug.datatables.net/otuwam

Sure looks like a valid JSON array of objects to me. Any ideas?

kthorngren

In both debugger results the response looks like this:

{
    "draw": 0,
    "recordsTotal": 3,
    "recordsFiltered": 3,
    "data": "[{\"Column1\":\"Col 1\",\"Column2\":\"Col 2\",\"Column3\":\"Col 3\",\"Column4\":\"Col 4\"},{\"Column1\":\"Col 12\",\"Column2\":\"Col 2\",\"Column3\":\"Col 3\",\"Column4\":\"Col 4\"},{\"Column1\":\"Col 13\",\"Column2\":\"Col 2\",\"Column3\":\"Col 3\",\"Column4\":\"Col 4\"}]"
}

If you run this through https://jsonlint.com/ it will state that it is a valid JSON string, which it is. However its not exactly what Datatables is looking for. The problem is with the data object. It is a string: "[{\"Column1\":\"Col 1\" .... \"Column4\":\"Col 4\"}]". It starts and ends with ". It looks like your code is taking your data results and encoding it as JSON then the full response is JSON encoded. Effectively you are encoding the data object twice which is escaping all the " and encapsulating it in a string.

Your JSON response should look like this:

{
    "draw": 0,
    "recordsTotal": 3,
    "recordsFiltered": 3,
    "data": [{
        "Column1": "Col 1",
        "Column2": "Col 2",
        "Column3": "Col 3",
        "Column4": "Col 4"
    }, {
        "Column1": "Col 12",
        "Column2": "Col 2",
        "Column3": "Col 3",
        "Column4": "Col 4"
    }, {
        "Column1": "Col 13",
        "Column2": "Col 2",
        "Column3": "Col 3",
        "Column4": "Col 4"
    }]
}

Kevin