Using datatables.js with Get()

It’s been a while since I’ve posted in the Software Development category, but here’s the solution to something that’s been a thorn in my team’s side for a few days. We’re working with datatables.js, which provides a nice interface to tabular data. We’re using it to Get() data from an API we’re building, and out of habit we were just returning data in a JSON object (btw, if you’re not using Swagger, start doing so now):

Unfortunately, this just kept showing us a frustrating “No data available in table” message.

After much digging, we realized that datatables really wanted the data in a completely different format:

Notice that each data “row” is now basically an array, and there is just one “data” element in the JSON.

We’re using ASP.NET MVC 5, and so here’s some code that may be of help to you in a similar project:

in HomeController.cs

public ActionResult IncidentDataTable()
    {
      ViewBag.Title = "simple data table";
      return View();
    }

in IncidentDataTable.cshtml (the view)

<table id="example" class="display">
  <thead>
    <tr>
      <th>IncidentId
      <th>ProductId
      <th>ProductName
      <th>ClientId
      <th>ClientName
      <th>ExternalId
      <th>DismType
      <th>DismCode
      <th>Status
      <th>CreateDate
    </tr>
  </thead>
</table>

<script>
  $(document).ready(function () {
    $('#example')
      .DataTable({
        processing: true,
        ajax: {
          url: 'http://localhost:63736/api/IncidentDataTable',
          dataType: "json",
          type: "Get",
          "columns": [
            { "data": "IncidentId" },
            { "data": "ProductId" },
            { "data": "ProductName" },
            { "data": "ClientId" },
            { "data": "ClientName" },
            { "data": "ExternalId" },
            { "data": "DismType" },
            { "data": "DismCode" },
            { "data": "Status" },
            { "data": "CreateDate" }
          ],
          error: function () {
            alert("Unable to load data.");
          }
        }
      });
  });
</script>

in IncidentDataTableController.cs (the API)

public class IncidentDataTableData
{
   // this is the "magic" format that datatables.js is expecting
   public List data { get; set; } = new List();
}

// ... 

public IncidentDataTableData Get()
{
      var connectionString = // db-specific config info, use ConfigurationManager()
      var returnValueForTable = new IncidentDataTableData();
      using (var conn = new SqlConnection(connectionString))
      {
        var sql = // the SELECT needed to get the data
        var cmd = new SqlCommand(sql, conn);
        conn.Open();
        var reader = cmd.ExecuteReader();
        while (reader.Read())
          returnValueForTable.data.Add(new string[10] // match the number of fields you're using
          // each element in the object should itself be an array of strings 
          {
            Convert.ToString(reader["SysIncidId"]),
            Convert.ToString(reader["SysProdId"]),
            Convert.ToString(reader["ProductName"]),
            Convert.ToString(reader["SysCltId"]),
            Convert.ToString(reader["ClientName"]),
            Convert.ToString(reader["ExtOptId"]),
            Convert.ToString(reader["DismType"]),
            Convert.ToString(reader["DismCode"]),
            Convert.ToString(reader["Status"]),
            Convert.ToString(reader["CreateDate"])
          }
            );
        reader.Close();
        if (!returnValueForTable.data.Any())
          return null;
      }
      return returnValueForTable;
}

I hope this helps someone.