Using & Understanding Datatables    Datatable

When it comes to displaying a large amount of data then the Datatable class is the right one. It allows us to render data from the database in tabular manner and offers functions like sorting, paging, fulltext search, etc. All that with a simple SQL query. Thats the first and most important fact here to remember: The Datatable can only display data from a SQL query (SELECT * FROM ...).

Okay, this article will explain the most important things you should know when working with the Datatable. All the examples here will connect to a sample database whose connectionstring is stored in a constant called DATA_CONNSTRING. So you will see a connection call to a different database than the default on. It should not confuse you because in your environment you can use your default configured connection. Furthermore we will with only one table called person which contains the columns id, firstname and lastname.

First Simple Datatable

Voila, we've got our first naked Datatable!

You can see that we've included our Datatable class and created an instance of it called table. The instance has been defined globally because its necessary to access it during different steps of our page lifecycle. All your datatable settings must be done within the init() and your table must be drawn within the main() (to actually render the HTML at a given position) and within the callback() procedure (to process all requests its callback requests).
Last but not least you see that we have set the sql property which is being used to populate the Datatable. Thats all the stuff you require to hook up your first one. They are a must!

Defining columns

You may have recognized that the columns in our first example have been detected automatically and rendered within your browser (column names are made human readable as well check str.humanize()). Thats cool, but not always what we want. We might want to change order, hide some columns, etc. For that reason its possible to add the columns manually and as long as there is at least one column added manually the automatic detection is deactivated. newColumn() method is our friend in that case. Check that:



We've added 3 columns manually using newColumn() within our init() procedure. The first parameter is the name of the column as it appears in the SQL Query and the second parameter is caption which should be displayed as the column headline.

Pro TIP: The second parameter can also be an array which would result that the first field is the caption and the second a tooltip. E.g.
.newColumn "firstname", array("First", "Users Firstname")

Yeah, we also selected a column named name (which concatenates firstname and lastname) within the SQL query. Thus we were also able to add it as a column to the Datatable.

Primary key columns: The first column is always being considered as the primary key column (which uniquely identifies each record). If you are missing that then you get an exception which tells you to set pkColumn property manually to the column which contains the primary key.

By the way you can see that the sort property has been used to set the initial sorting of the data. You cannot use an ORDER BY clause within your SQL Query. Thats being added by the component itself. The sort property can contain any valid ORDER BY expressions though. Examples:



Setting column properties:
If you check the API docs you will see that the newColumn methods returns an instance of a DatatableColumn. Thats great, because we can grab the column and customize it even more. Check the following example where we assign a css class to the id column to make it look gray in order to not attract too much attention. In addition we also styled the lastname column.

The power is within the SQL

The power of our Datatable is "hidden" within the SQL query. As you may have already noted its only possible to use columns which are defined within the SQL query. That shouldn't be seen as a negative constraint at all. Its good news for us instead. SQL is really powerful and thats why you can solve most of the problems directly within the SQL query already. Remember the custom column creation? It could be solved without adding columns manully as well. Just use an SQL query like this to achieve a renaming of columns (this should be familiar to most):



You can even use SQL dialect for simple if conditions (CASE). Use your imagination and you will be able to solve every problem. E.g. you could even chuck HTML code into your SQL like (though I would recommend to avoid it as much as possible - just because it lacks readability):

 
table.sql = "SELECT id, '<strong>' + firstname + '</strong>' AS first FROM person"

When using HTML within your data be sure to set encodeHTML to false on the respective column. Otherwise all HTML will be safely encoded by default.

Runtime modifications

What if you need to change values during runtime according to its values or according to some business logic you have written? Thats not a big deal and can be solved using events. There are 2 main events. You can hook up either a whole row and modify the properties of a row (DatatableRow) during runtime or you can do the same for a column. First should be used when you want to apply customizations on a per record basis and the latter is perfect when you want to deal with a column across all records. Lets take a deeper look at both of them.

onRowCreated Event
The onRowCreated event is fired when the current row instance has been created but just before its been rendered. It has to be set on the datatable instance. At this stage you can modify any properties of the current row instance which is being held in the row property of your Datatable instance (check the API docs of DatatableRow for all its members). The following example will assign a css class called inactive to all persons whose ID is less than 10. Makes not much "real world" sense but should do it :)

<!--#include virtual="/page.asp"-->
<!--#include virtual="/ajaxed/class_datatable/datatable.asp"-->
<%
set table = new Datatable
set page = new AjaxedPage
page.draw()

sub init()
  db.open(DATA_CONNSTRING)
  table.sql = "SELECT * FROM person"
  table.onRowCreated = "onRowHandler"
end sub

sub onRowHandler(dt)
  if clng(dt.data("id")) < 10 then
    dt.row.cssClass = "inactive"
  end if 
end sub

sub callback(action)
  table.draw()
end sub

sub main() %>

  <style>
    .inactive td {
      color:#ccc;
    }
  </style>

  <% table.draw() %> 

<% end sub %>

Run this code — onRow.asp

Right, onRowCreated is nothing more as a normal property. Just that the string it holds is the name of the procedure which will be called everytime a row has been created. The procedure has to accept one argument which is the Datatable instance itself. That makes it convenient to access all datatable properties during runtime and makes it even possible to reuse event handling procedures across more datatables.

You see we use that argument (dt in that case) and its row property to get access to our current row instance. Also very interesting is the usage of the data property which always gives us access to underlying recordset. That allows us to read all the records data during runtime. Powerful!

onCellCreated Event
The other way to control execution is by using the onCellCreated event of a Datatable column. Its fired when a given cell of that column has been created but not rendered yet. Thus you can change its properties according to any conditions you like. Lets check the following example which writes "less 10" if the ID is less than 10 and "greater 10" if the ID is greater instead of the ID itself. Additionally it should highlight all persons whose lastname starts with the letter "A".

<!--#include virtual="/page.asp"-->
<!--#include virtual="/ajaxed/class_datatable/datatable.asp"-->
<%
set table = new Datatable
set page = new AjaxedPage
page.draw()

sub init()
  db.open(DATA_CONNSTRING)
  table.sql = "SELECT * FROM person"
  
  'quick way of attaching an event handling procedure
  table.newColumn("id", "ID").onCellCreated = "onID"
  
  'other way...
  set c = table.newColumn("lastname", "Last")
  c.onCellCreated = "onLastname"
end sub

'Important: its a function! onRowCreated was a sub!
function onID(dt)
  onID = "less 10"
  dt.col.cssClass = "inactive"
  if dt.col > 10 then
    onID = "greater 10"
    dt.col.cssClass = ""
  end if
end function

function onLastname(dt)
  'important to pass through the original value
  'as we dont change it
  onLastname = dt.col.value
  'note; we can use dt.col instead of dt.col.value
  'as its the default property
  if str.startsWith(dt.col, "A") then
    dt.col.cssClass = "ePerson"
  else
    'important to clear it as it would remember
    'it for the whole column
    dt.col.cssClass = ""
  end if
end function

sub callback(action)
  table.draw()
end sub

sub main() %>

  <style>
    .inactive {
      color:#ccc;
    }
    .ePerson {
      color:#00f;
    }
  </style>

  <% table.draw() %> 

<% end sub %>

Run this code — onCell.asp

The process is the same as with onRowCreated apart from the fact that this time we need to define a function instead of a sub. Its because our cell event handlers need to return the actual value which will be rendered. The simplest handler would be just passing through the value:

<%
'same as if you would not use it. just pass through
function onFirstname(dt)
  onFirstname = dt.col.value
end function
%>

Action columns
For sure you want to add a column one day which allows you to perform some action on a record or you want to open a form for the modification of a record. There are many ways to solve that but most of them require the onCellCreated event.

You could e.g create link for a column:

<%
function onLastname(dt)
  onLastname = "<a href=""person.asp?id=" & dt.data("id")  & """>" & dt.col & "</a>"
end function
%>

or you could also create an empty column, hook up a cell created event and add action links to it. This example shows one which redirects to a page and another one which would call a javascript function (you can even use ajaxed.callback) to perform an action:

<%
function onAction(dt)
  onAction = "<a href=""person.asp?id=" & dt.data("id")  & """>edit</a>" & _
    "<a href="javascript:void(0)" onclick=""doDelete(" & dt.data("id") & ")"">delete</a>"
end function
%>

Styling your Datatable. Lets Pimp!

The whole Datatable control can be styled using pure CSS. So that everthing you need to know ;) By default a standard ajaxed theme is used if you do not specify a stylesheet explicitly using the css property (which takes a virtual path to a stylehseet file). If you specify it then yours is loaded instead of the standard one. Another option would be to just add your own stylesheet to the page which will override the standard settings. Thats not recommended though, as the standard appearance might change over time.

So what you have to figure out then is what css class names are being used and define them within you stylesheet. To figure out that just create a new Datatable and have a look at the source code (firebug recommended!). The class names won't change as they are part of the style contract. So lets create our first theme and apply it a Datatable.

td, th {
  padding: 0.2em 1em;
  font: 10pt arial;
  width: 200px;
}

.axdDTControlsRow {
  background:#bbb;
}

th, th * {
  background:#000;
  color:#fff;
}

.axdDTRowOdd td {
  background:#eee;
}

.axdDTHighlight {
  font-weight:bold;
  color:#f00;
}

.sortedASC, .sortedDESC {
  font-weight:bold;
}

tfoot tr {
  background:#bbb;
  color:#fff;
}

tbody tr:hover td {
  background:#B6E2F3;
}

.pagingBar span {
  padding:0.1em 0.3em;
}

.pagingBar .pCurrent {
  font-weight:bold;
  background:#000;
  color:#fff;
}

Run this code — style.css

I am not a Designer so its just for demonstration purpose ;) Last but not least we need the Datatable which uses our just created theme. The new part here is the reference to the theme using the css property.

<!--#include virtual="/page.asp"-->
<!--#include virtual="/ajaxed/class_datatable/datatable.asp"-->
<%
set table = new Datatable
set page = new AjaxedPage
page.draw()

sub init()
  db.open(DATA_CONNSTRING)
  table.sql = "SELECT * FROM person"
  table.css = "style.css"
  table.recsPerPage = 10
end sub

sub callback(action)
  table.draw()
end sub

sub main() %>

  <% table.draw() %> 

<% end sub %>

Run this code — pimped.asp

Share your Theme: Please share your Datatable themes with others by posting to our Discussion Group. Its great to have some nice and good looking datatables around.

Now you should be able to create your data tables from the scratch and style them. You will see that after some days you get used to it and its great fun. Its a powerful component with great flexibility. More functions are coming...

Debugging: Check your log files if you want to debug the Datatable. You will find the whole generated SQL which is being sent to the database when populating the table.