Dynamically Select Columns with Server-Side Paging and Datatables.Net

Source code has been yet again updated!! Read about the changes in DataTablePager Now Has Multi-Column Sort Capability For DataTables.Net If you are new to DataTables.Net and Sensei’s paging solution and want to detailed study of how it works, work through this post first, then get the latest edition.  Note, code links in this post are to the first version.

The last episode of server-side paging with DataTablerPager for DataTables.Net we reviewed the basics of a server-side solution that paged records and returned results in the multiples as specified by DataTables.Net.  You will want to have read that post before preceding here.  The older version of the source is included in that post as well as this will help get you acclimated.  The following capabilities were reviewed:

• The solution used generics and could work with any collection of IQueryable.  In short any of your classes from you domain solution  could be used.
• Filtering capability across all properties was provided.  This included partial word matching, regardless of case.
• Ordering of result set was in response to the column clicked on the client’s DataTables grid.

DataTablePager Enhancements

This past month Sensei has added new capabilities to the DataTablePager class that makes it an even better fit for use with DataTables.Net.  The new features are:

• Dynamically select the columns from the properties of your class based on the column definitions supplied by DataTables.Net!!!
  
• Exclude columns from sort or search based on configuration by DataTables.Net
  
• Mix columns from your class properties with client-side only column definitions; e.g. create a column with <a href>’s that do not interfere with filtering, sorting, or other processing.

Before we jump into the nitty-gritty details let’s review how DataTables.Net allows you to control a column’s interaction with a data grid.  Grab the new source code to best follow along.

DataTables.Net Column Definition

You would think that there would be quite a few steps to keep your server-side data paging solution in concert with a client side implementation, and that would mean customization for each page.   DataTables.Net provides you with fine control over what your columns will do once displayed in a data grid.  Great, but does that mean a lot of configuration on the server side of the equation?  As well soon see, no, it doesn’t.  What is done on the client for configuration will be that you need to do.

The structure aoColumnDefs is the convention we use for column configuration.  From the DataTables.Net site:

aoColumnDefs: This array allows you to target a specific column, multiple columns, or all columns, using the aTargets property of each object in the array (please note that aoColumnDefs was introduced in DataTables 1.7). This allows great flexibility when creating tables, as the aoColumnDefs arrays can be of any length, targeting the columns you specifically want. The aTargets property is an array to target one of many columns and each element in it can be:

• a string – class name will be matched on the TH for the column
• 0 or a positive integer – column index counting from the left
• a negative integer – column index counting from the right
• the string “_all” – all columns (i.e. assign a default)

So in order for you to include columns in a sort you configure in this manner:

/* Using aoColumnDefs */
$(document).ready(function() {
	$('#example').dataTable( {
		"aoColumnDefs": [
			{ "bSortable": false, "aTargets": [ 0 ] }
		] } );
} );

} );

In other words we are defining that the first column – column 0 – will not be included in the sorting operations.  When you review the columns options you’ll see you have options for applying css classes to multiple columns, can include a column in filtering, can supply custom rendering of a column, and much more.

In the example that we’ll use for the rest of the post we are going to provide the following capability for a data grid:

1. The first column – column 0 – will be an action column with a hyperlink, and we will want to exclude it form sort and filtering functions.
2. Only display a subset of the properties from a class.  Each of these columns should be sortable and filterable.
3. Maintain the ability to chunk the result set in the multiples specified by DataTables.Net; that is, multiples of 10, 50, and 100.

Here is the configuration from the aspx page SpecifyColumns.aspx:

"aoColumnDefs" : [
   {"fnRender" : function(oObj){
      return "<a href="&quot;center.aspx?centerid=&quot;">Edit</a>";
   },
     "bSortable" : false,
     "aTargets" : [0]},
   {"sName" : "Name",
     "bSearchable" : true,
     "aTargets": [1]},
   {"sName" : "Agent",
    "bSearchable" : true,
    "bSortable" : true,
    "aTargets" : [2]
   },
   {"sName" : "Center", "aTargets": [3]},
   {"fnRender" : function(oObj){
            return "2nd Action List";
         },
     "bSortable" : false,
     "aTargets" : [4]},
   {"sName" : "CenterId", "bVisible" : false, "aTargets" : [5]},
   {"sName" : "DealAmount", "aTargets" : [6]}
]

1. Column 0 is our custom column – do not sort or search on this content.  Look at oObj.aData[4] – this is a column that we’ll return but not display.  It’s referred so by the position in the data array that DataTables.Net expects back from the server.
2. Columns 1 – 3 are data and can be sorted.  Note the use of “sName”.  This will be included in a named column list that corresponds to the source property from our class.  This will be very important later on for us, as it allows us to query our data and return it in any order to DataTables.Net.  DataTables will figure out what to do with it before it renders.
3. Threw in another custom column.  Again, no sort or search, but we’ll see how this affects the server side implementation later on.  Hint – there’s no sName used here.
4. Another data column.

To recap, we want to be able to define what data we need to display and how we want to interact with that data by only instructing DataTables.Net what to do.  We’re going to be lazy, and not do anything else – the class DataTablePager will respond to the instructions that DataTables.Net supplies, and that’s it.  We’ll review how to do this next.  Sensei thinks you’ll really dig it.

DataTablePager Class Handles your Client Side Requests

If you recall, DataTables.Net communicates to the server via the structure aoData.  Here is the summary of the parameters.  One additional parameter that we’ll need to parse is the sColumns parameter, and it will contain the names and order of the columns that DataTables.Net is rendering.  For our example, we’ll get the following list of columns if we were to debug on the server:

,Name,Agent,Center,,CenterId,DealAmount

These are all the columns we named with sName, plus a place holder for those custom columns that not found in our class.  This has several implications.  For one, it will mean that we will no longer be able to simply use reflection to get at our properties, filter them and send them back down to the client.  The client is now expecting an array where each row will have 7 things, 5 of which are named and two place holders for items that the client wants to reserve for itself.  Hence the convention of passing an empty item in the delimited string as shown above.

It will also mean that we’ll have to separate the columns that we can filter or sort.  Again this is the reason for leaving the custom column names blank.  In other words, we’ll have to keep track of the items that we can search and sort.  We’ll do this with a class called SearchAndSortable:

public class SearchAndSortable
    {
        public string Name { get; set; }
        public int ColumnIndex { get; set; }
        public bool IsSearchable { get; set; }
        public bool IsSortable { get; set; }
        public PropertyInfo Property{ get; set; }

        public SearchAndSortable(string name, int columnIndex, bool isSearchable, bool isSortable)
        {
            this.Name = name;
            this.ColumnIndex = columnIndex;
            this.IsSearchable = isSearchable;
            this.IsSortable = IsSortable;
        }

        public SearchAndSortable() : this(string.Empty, 0, true, true) { }
    }

This will summarize what we’re doing with our properties.   The property ColumnIndex will record the position in sColumn where our column occurs.  Since we’ll need access to the actual properties themselves we’ll store these in the SearchAndSortable as well so that we can reduce the number of calls that use reflection. DataTablePager uses a List of SortAndSearchables to track what’s going on.  We fill this list in the method PrepAOData()

//  What column is searchable and / or sortable
            //  What properties from T is identified by the columns
            var properties = typeof(T).GetProperties();
            int i = 0;

            //  Search and store all properties from T
            this.columns.ForEach(col =>
            {
                if (string.IsNullOrEmpty(col) == false)
                {
                    var searchable = new SearchAndSortable(col, i, false, false);
                    var searchItem = aoDataList.Where(x => x.Name == BSEARCHABLE + i.ToString())
                                     .ToList();
                    searchable.IsSearchable = (searchItem[0].Value == "False") ? false : true;
                    searchable.Property = properties.Where(x => x.Name == col)
                                                    .SingleOrDefault();

                    searchAndSortables.Add(searchable);
                }

                i++;
            });

            //  Sort
            searchAndSortables.ForEach(sortable => {
                var sort = aoDataList.Where(x => x.Name == BSORTABLE + sortable.ColumnIndex.ToString())
                                            .ToList();
                sortable.IsSortable = (sort[0].Value == "False") ? false : true;
            });

We’ll get the properties from our class. Next we’ll traverse the columns and match the property names with the names of the columns. When there is a match, we need to query aoData and get the column search and sort definitions based on the ordinal position of the column in the sColumns variable. DataTables.Net convention for communicating this is the form of:

bSortable_ + column index => “bSortable_1” or “bSearchable_2”

We take care of that with this line of code:

var searchItem = aoDataList.Where(x => x.Name == BSEARCHABLE +
                                     i.ToString())
                                     .ToList();
searchable.IsSearchable = (searchItem[0].Value == "False") ? false : true;

Now we go through the list of properties again but this time determine if we should sort any of the columns. That happens in the section //Sort. In the end we have a list of properties that corresponds with the columns DataTables.Net has requested, and we have defined if the property can be search (filtered) or sorted.

For filtering DataTablePager recall that we use the method GenericSearchFilter().  The only alteration here is that we only will add the properties to our query that are defined as searcable:

//  Create a list of searchable properties
            var filterProperties = this.searchAndSortables.Where(x =>
                                        x.IsSearchable)
                                          .Select(x => x.Property)
                                          .ToList();

The rest of the method is unaltered from the prior version. Pretty cool!! Again, we’ll only get the properties that we declared as legal for filtering. We’ve also eliminated any chance of mixing a custom column in with our properties because we did not supply an sName in our configuration.

The method ApplySort() required one change. On the initial load of DataTable.Net, the client will pass up the request to sort on column 0 even though you may have excluded it. When that is the case, we’ll just look for the first column that is sortable and order by that column.

//  Initial display will set order to first column - column 0
//  When column 0 is not sortable, find first column that is
var sortable = this.searchAndSortables.Where(x => x.ColumnIndex ==
                                         firstColumn)
                              .SingleOrDefault();
if(sortable == null)
{
   sortable = this.searchAndSortables.First(x => x.IsSortable);
}

return records.OrderBy(sortable.Name, sortDirection, true);

After we have filtered and sorted the data set we can finally select the only those properties that we want to send to the client.  Recall that we have parsed a variable sColumns that tells what columns are expected.  We’ll pass these names onto extension method PropertiesToList().  This method will only serialize the property if the column is include, and since we have already paired down our data set as a result of our query and paging, there is very little performance impact.  Here is the new PropertiesToList method:

public static ListPropertiesToList(this T obj, List propertyNames)
{
   var propertyList = new List();
   var properties = typeof(T).GetProperties();
   var props = new List();

   //  Find all "" in propertyNames and insert empty value into list at
   //  corresponding position
   var blankIndexes = new List();
   int i = 0;

   //  Select and order filterProperties.  Record index position where there is
   //  no property
   propertyNames.ForEach(name =>
   {
      var property = properties.Where(prop => prop.Name == name.Trim())
         .SingleOrDefault();

      if(property == null)
      {
         blankIndexes.Add(new NameValuePair(name, i));
      }
      else
      {
         props.Add(properties.Where(prop => prop.Name == name.Trim())
                                    .SingleOrDefault());
      }
      i++;
   });

   propertyList = props.Select(prop => (prop.GetValue(obj, new object[0]) ?? string.Empty).ToString())
                                        .ToList();

   //  Add "" to List as client expects blank value in array
   blankIndexes.ForEach(index =>; {
      propertyList.Insert(index.Value, string.Empty);
   });

   return propertyList;
}

You might ask why not just pass in the list of SearchAndSortTable and avoid using reflection again. You could, but remember at this point we have reduced the number of items to the page size of 10, 50 or 100 rows, so your reflection calls will not have that great an impact. Also you should consider whether you want to simply have a function that will select only those properties that you need. Using SearchAndSortable would narrow the scope of utility, as you can use this method in other areas other than prepping data for DataTables.Net.

Now It’s Your Turn

That’s it.  Play with the page named SpecifyColumns.aspx.  You should be able to add and remove columns in the DataTable.Net configuration and they will just work.  This will mean, however, that you’ll have to always define your columns in your aspx page.  But since we worked really hard the first time around, DataTablePager will still be able to create paged data sets for any class in your domain.

Source code is here.  Enjoy.