Getting Started on Line-of-Business HTML5 App – Part 13 Display, Edit in DataTables jQuery Plug-in

imageLine-of-business apps often present data in a table.

DataTables is a plug-in for jQuery. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table.

Features

You get variable length pagination, on-the-fly filtering, multi-column sorting with data type detection, smart handling of column widths.

You display data from almost any data source, including DOM, Javascript array, Ajax file and server-side processing (PHP, C#, Perl, Ruby, AIR, Gears etc).

You can save state, hide columns, dynamically create tables, load data using AJAX, filer on a single column. There are alternative pagination types. Sorted columns are highlighted.

Fully accessible for screenreaders / keyboard access.

It is 68K minified.

And you can use themes in CSS.

And with an Editor plug-in for DataTables which provides a full editing toolset for your tables. With Editor and DataTables you have have a highly dynamic, custom configured, editable table.

And you can use Microsoft ASP.NET CDN to get DataTables JavaScript to your Website.

Let’s see how easy it is to get started.

Get DataTables

There are several ways to get DataTables.

  • If you are using your own text editors, you can get DataTables directly from the DataTables Download page. The DataTables distribution include a wide range of examples, the source files (including compressed versions) and all first class plug-ins.

  • You can load it into Visual Studio using NuGet. Right-click your project, click Manage NuGet Packages… in the menu, type DataTables in the dialog box, click Install.

    image

    Then add JQuery. Right-click your project, click Manage NuGet Packages… , type jQuery in the dialog box, click Install. The installer will replace the older version of jQuery that was installed during the DataTables installation.

  • If you know your app will be connected to the Internet, the Webpage can upload the files from the jQuery DataTables 1.9.1 on ASP.NET CDN straight into your application. The key technologies are available including the JavaScript, the CSS files, and picture files that you will need for your data table.

Load DataTables On Your Page

Next you will want to load DataTables onto your page.

You can load DataTables from the Web or you load them locally in your <head> element.

If you use ASP.NET CDN:

 <link href="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.1/css/jquery.dataTables.css" rel="stylesheet" />
 
<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.7.2.min.js " type="text/javascript"></script>
<script src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.1/jquery.dataTables.min.js" type="text/javascript"></script>

If you load it locally, you can include the demo CSS files:

     <link href="Content/DataTables-1.9.1/media/css/jquery.dataTables.css" rel="stylesheet" />
    <link href="Content/DataTables-1.9.1/media/css/demo_page.css" rel="stylesheet" />
    <link href="Content/DataTables-1.9.1/media/css/demo_table.css" rel="stylesheet" />
    <script type="text/javascript" src="Scripts/jquery-1.7.2.min.js"></script>
    <script type="text/javascript" src="Scripts/DataTables-1.9.1/media/js/jquery.dataTables.min.js"></script>

DataTables has most features enabled by default, so all you need to do to use it with one of your own tables is to call the construction function. You will notice myDataTable that is obtained using jQuery using its identifier.

 <script type="text/javascript">
    $(document).ready(function () {
        $('#myDataTable').dataTable();
    });
</script>

myDataTable is a regular table on your page. It needs to well formed manner with <thead> and <tbody> sections.

Here’s the short version of my table:

 <table id="myDataTable">
    <thead>
        <tr>
            <th>Event Name</th>
            <th>Event Location</th>
            <th>Event Date</th>
            <th>Registration Link</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>HTML5 All Up</td>
            <td>Denver, CO</td>
            <td>Feb 3</td>
            <td>https://nolinkyet</td>
        </tr>
        <tr>
            <td>HTML5 All Up</td>
            <td>Chicago, IL</td>
            <td>Feb 9</td>
            <td>https://nolinkyet</td>
        </tr>
    </tbody>
</table>

Here is what it looks like without DataTables:

image

But with DataTables you get more cool stuff, out of the box. Show entries, search, previous and next buttons.

image

Awesome.

Turning Features On and Off

Now I don’t want all those features on. I only have two entries (or enough to fit on a single page), so I want to turn off paginate and the length change drop down input.

 <script type="text/javascript">
    $(document).ready(function () {
        $('#myDataTable').dataTable({
            "bPaginate": false,        
            "bLengthChange": false,        
            "bFilter": true,        
            "bSort": false,        
            "bInfo": false,        
            "bAutoWidth": false    
        } );
    });
</script>

Here are the features you can set:

  • Multiple column sorting
  • Hidden columns
  • Complex headers with colspan
  • Scrolling (horizontally, vertically, themed, infinite)

Grouping

DataTables doesn't have row grouping built-in. But you can implement. But you can implement grouping yourself. An example is shown on DataTables site. See DataTables row grouping example.

In short, you implement the fnDrawCallback method for your table.

image

Getting the Selected Row

When you click on a row, you can highlight that row for the user. See DataTables row select example in the DataTables documentation. When you select the row, you dynamically change your HTML to make that row into a class row_selected using jQuery.

 $(this).addClass('row_selected');

Then you can take this one step further, and get the data of the selected row.

 var oTable = $('#myDataTable').dataTable();
var selectedRow = oTable.$('tr.row_selected');

// returns the first selected row
var aData = oTable.fnGetData(anSelected[0]);

// returns the data in a column of the selected row
 var firstItemInSelectedRow = aData[0];
var secondItemInSelectedRow = aData[1];

Delete

You can delete the selected row by calling the DataTables fnDeleteRow function. You will need to use your Ajax method to update the server.

 var anSelected = oTable.$('tr.row_selected');
if (anSelected.length !== 0) {
   oTable.fnDeleteRow(anSelected[0]);

Add

Add a row by using the fnAddData function. Just add the data. Of course you will need to use your Ajax method to update the server.

 $('#addrow').click(function () {
    oTable.fnAddData([
        "Big Data",
        "Big Data in the Enterprise",
        "Houston",
        "January 3",
        "https://nolinkyet"]);
});

Edit

You can edit individual cells using the jEditable plugin for jQuery.

CRUD

If you are interested in full CRUD (create, replace, update, delete) implementation for DataTables, check out the Editor plug-in for DataTables, which is offered free for 15 days.

Loading Data From the Server

There are two ways to load data dynamically from the server. You can either using the aData initialization parameter which takes an array of data, or using the sAjaxSource initialization parameter which will have DataTables go to that source with an XHR call and load data from there.

For example:

 $(document).ready(function() {
    $('#example').dataTable( {
        "bProcessing": true,
        "sAjaxSource": '../examples_support/json_source.txt'
    } );
} );

DataTables expects an object with an array called aaData with the data source. The Response that comes from the server must contain information about the total number of records, the number of currently filtered records, and the cells that should be displayed. Your response using JSON would look like this:

 {  "sEcho":"1",
    "iTotalRecords":97,
    "iTotalDisplayRecords":3,
    "aaData":[    ["1","a1","a2","a3"],
                  ["2","b1","b2","b3"],
                  ["3","c1","c2","c3"]
        ]
}

For more information, see Server-side data rendering functions on the DataTables site.

Loading Data Dynamically

DataTables is expecting that all your data comes from server. Performing the actions on the server makes for much a much snappier and scalable UI, but it adds complexity. For large amounts of data, this means larger page sizes and longer initial load times.

You can use a WCF service with DataTables, but you have to both honor the signature that DataTables expects and the data must be returned in a format that DataTables expects.

Jeff Morris provides the keys to have to implement the server side. See Using Datatables.net JQuery Plug-in with WCF Services.

He has posted a working example on GitHub: https://github.com/jeffrymorris/DataTablesWCFExample

LocalStorage

The state saving storage method that is built into DataTables makes use of cookies for compatibility with all browsers, but with a small modification you can use localstorage.

Use the fnStateSave and fnStateLoad to store the table state in localStorage and then load it back again when needed. You can use the current web-page URL should you be using multiple DataTables in your domain since localStorage is domain wide.

The following code stores the DataTable object (not the table data itself. Although you get the idea of how you could store the data locally, for offline applications such as smartphones.

 var oTable = $('#myDataTable').dataTable({
    "bStateSave": true,
    "fnStateSave": function (oSettings, oData) {
        var jsonData = JSON.stringify(oData);
        localStorage.setItem('DataTables', jsonData );
    },
    "fnStateLoad": function (oSettings) {
        return JSON.parse(localStorage.getItem('DataTables')); 
    },
    "fnStateLoaded": function () {
        alert("State Loaded");
    }
});

(And do not forget to use Modernizr to check that localstorage and JSON are available in your browser before calling the functions.)

 if( Modernizr.localStorage && window.JSON) {
 // perform localStorage actions here
}

The DataTables Editor example show how you can use localstorage to replace the default Ajax call that Editor makes so you could use the browser's localStorage abilities to save the state of the table data locally. The sample code will also give you insight into how you can roll your own localstorage for more than just the top level DataTable object. See DataTables Editor – localStorage.

More and more

I just scratched the surface of what is available in DataTables. See DataTables Examples for more details.

For More Information

Check out the excellent overview at Enhancing HTML tables using the jQuery DataTables plug-in in CodeProject.

For integrating with MVC, see Jovan Popovic’s articles on CodeProject.

For how to use OData, see Slice and Dice OData with the jQuery DataTables Plug-In in MSDN Magazine.

 

Bruce D. KyleISV Developer Evangelist | Microsoft Corporation

image