jQuery Datatables Bindings

Assembly
WebExtras.dll
Namespace
WebExtras.JQDataTables
Dependancies
  • Appropriate third party libraries
  • jquery.datatables.bootstrap.js
  • webextras.bootstrap.css
Steps involved in creating a Datatable
  • Creating the column definitions
  • Creating the table settings
  • Creating the table records
  • Creating the table object
  • And finally the output.....

Sorting support can be added by slightly modifying the column definitions and using the Sort extension method.

Updating the column definitions to enable sorting

Column definitions are created by instantiating the WebExtras.JQDataTables.AOColumn class. In order to enable sorting on a column, you MUST set the Sortable property to true. By default this will be set to false by the constructor.

  AOColumn dtColumn = new AOColumn
  {
    sTitle = "First Column",    // Only the sTitle property is compulsory. All other properties are optional
    bSortable = true,
    sClass = "",                // any extra CSS class you would like to apply to this column
    sWidth = "10%",             // specified as a CSS width
    bVisible = true
  };  
  

Creating table settings

Table settings are created by instantiating the WebExtras.JQDataTables.DatatableSettings class
  
  // We need a collection of columns to be specified in the settings, so create an array from our column
  AOColumn[] dtColumns = new AOColumn[] { dtColumn };

  DatatableSettings dtSettings = new DatatableSettings 
  (
    5,                                      // specify the number of records per page
    dtColumns,
    new AASort(0, SortType.Ascending),      // specify one initial sort, multiple initial sorts, or leave null for no sort
    "~/getsorteddata",                       // specify an AJAX source to retrieve data from
    "paged and sorted records",             // specify a table footer suffix
    "150px"                                 // specify a maximum table height, once reached you will get scroll bars
  );
  

Creating a data handler

We make use of the C# LinqToSQL feature in order to simulate the sorting behavior. WebExtras provides an extension method for the IEnumerable<IEnumerable<string>> interface which takes care of the sorting for us.
  
  public DatatableRecords GetSortedRecords(DatatableFilters filters)
  {
    // Let's create the actual data to go into the table by adding 15 records
    List<string[]> dtData = new List<string[]>();   // Note that this still implements IEnumerable<IEnumerable<string>>

    // You can retrieve data from your repository here
    for (int i = 0; i < 15; i++)
    {
      dtData.Add(new string[] { 
        string.Format("first column paged row {0}", i + 1), 
        string.Format("second column paged row {0}", i + 1) 
      });
    }

    DatatableRecords dtRecords = new DatatableRecords
    {
      sEcho = filters.sEcho,
      iTotalRecords = dtData.Length,                                        // Total records in table
      iTotalDisplayRecords = dtData.Length,                                 // Total records to be displayed in the table
      aaData = dtData.Sort(filters.iSortCol_0, filters.SortDirection)        // The sorted data to be displayed
    };

    return dtRecords;
  }
  

Make the first page of the table

We need to create the first page of the table in order to have the paging behavior kick in. This should be done when you display the table the first time.
  
  DatatableRecords dtRecords = GetSortedRecords(new DatatableFilters { iDisplayStart = 0, iDisplayLength = 5 });
  Datatable dTable = new Datatable("sorted-table", dtSettings, dtRecords);
  

A slightly modified AJAX callback handler

We will now make use of the method that we have already created before to do our grunt work and get the data. The fact that we have used DatatableFilters as one of the parameters means that we can simply forward the filtering parameters we got from the HTTP GET request from the client side.

  public JsonResult GetSortedData(DatatableFilters filters)
  {
    DatatableRecords dtRecords = GetSortedRecords(filters);

    return Json(dtRecords, JsonRequestBehavior.AllowGet);
  }
  

And our sorting enabled output is

HTML field column String Column DateTime Column Numeric Column Currency Column

Some details about the sorter extension

The sorter extension can currently handle columns with the following .NET base types and some special formats as listed below:

  • string
  • DateTime
  • int, float, double, decimal
  • currency i.e. data in the format: €5.00, $6.00, £7.00, ¥8.00. The sorter will strip the currencies and sort on the decimal number. It isn't intelligent enough to look up the current exchange rate and sort them, so beware!!
    Euro, Dollar, Pound and Yen are the currently supported currencies.
  • HTML fields i.e data in format: <a href='/somelink.html'>some text</a>. The sorter will strip the HTML tags and sort on the inner text of the tag. In this case the sorter will sort on some text and not the HTML A tag.

But my data doesn't fit any of the default formats :(

All defaults sorters have a fixed way in which they try to parse data and then apply the sorting logic on it. However, you are not limited to only the default sorters. If you have a custom data format you are using, you can provide custom parsers to use in order to perform sorts.


  // An example custom parser to sort on the second character of the column data
  Func<string, object> secondCharParser = (string str) => 
  {
    char[] arr = str.ToCharArray();
    
    if(arr.Length > 1)
      return arr[1];
    
    return null; 
  }

  // Let's say we want to apply this custom parser to the second column of the table above
  Dictionary<int, Func<string, object>> parsers = new Dictionary<int, Func<string, object>>();
  parsers.Add(1, secondCharParser);           // Note that the dictionary key must match the column index in the table
      
  // This is how your DatatableRecords object creation would look
  DatatableRecords dtRecords = new DatatableRecords
  {
    sEcho = filters.sEcho,
    iTotalRecords = dtData.Length,                                        
    iTotalDisplayRecords = dtData.Length,                                 
    aaData = dtData.Sort(filters.iSortCol_0, filters.SortDirection, parsers)        // Sorting with custom parser
  };