Using ColdFusion CFCs to return data to DataTables 1.8.2

Just a quick post, more as a reminder for myself really, about a quick an easy way to return a query via ajax and easily load into a DataTable.

Simple CFC function:

<cffunction name="GetUsers" access="remote" output="false" returntype="any" returnformat="json">

  <cfquery name="local.qryUsers" datasource="dsn">
  SELECT id, name, surname, email FROM tblUsers
  </cfquery>

  <cfscript>
  local.strData = StructNew();
  local.strData['aaData'] = QueryToArray(local.qryCandidates);
  return local.strData;
  </cfscript>

</cffunction>

This example uses Ben Nadels QueryToArray function to convert our query object to a format that DataTables can now use (as of V1.8)

Then all you have to do is use the ‘aoColumns’ option in datatables to map your data to your columns, like so:

var userTable = $('#users).dataTable({
  "sAjaxSource": "path/to/handler.cfc?method=GetUsers",
  "aoColumns": [
    { "mDataProp": "id" , "sTitle": "ID"},
    { "mDataProp": "name" , "sTitle": "Name"},
    { "mDataProp": "surname" , "sTitle": "Surname"},
    { "mDataProp": "email" , "sTitle": "Email Address"}
  ]
});
  • http://www.cutterscrossing.com Cutter

    You can do something like this too http://www.cutterscrossing.com/index.cfm/2011/2/7/Using-The-DataTables-JQuery-Plugin

  • http://twitter.com/josegaldamez Jose Galdamez

    When I saw the QueryToArray function in your code I was thinking to myself, “How could I have overlooked this totally useful function?” Then you mentioned how it was a Ben Nadel concoction and I said, “Oh. Makes sense.” :)

    Seeing as how I’ve used dataTables before this post will definitely come in handy.

  • Sauravpandit21

    Thnaks for sharing..

  • Anna Harris

    I tried to use datatables with aspx page (C#) but the same
    error occured each time. I tried to call my page which create my json
    outpout. Can you help me?

  • Chad Baloga

    Is there any way to pull a range of data row instead of getting it all at once?

  • http://twitter.com/thedivineteam Divine Designs.ca

    This is good stuff

  • Michael White

    i’m brand new to datatables. any hope for a more complete example of using this method?