jump to navigation

Dynamically Select Columns with Server-Side Paging and Datatables.Net January 14, 2011

Posted by ActiveEngine Sensei in .Net, ActiveEngine, Ajax, ASP.Net, DataTables.Net, jQuery, JSON.Net, New Techniques, Problem Solving.
Tags: , , , , , ,
trackback

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.

Advertisements

Comments»

1. Bob S. - January 22, 2011

This is fantastic, exactly what I needed. Thank you for sharing your code. Have you tested this with multisort? I downloaded your example and it doesn’t appear to be sorting correctly for Agent + Deal Amount. Perhaps I misread the post… I couldn’t find any gotchas about limiting the sort functionality. Thanks again!

ActiveEngine Sensei - January 23, 2011

I’m glad you find this helpful. I had been using DataTables.net for about year and somehow avoided needing to provide server side paging. Once I hit the limit of streaming down a huge html table it took some time to research this and put something together.

I should have been clearer in my post that I could only get single sort to work. When I was testing sorting I recall that DataTables.net was sending me just one column at a time in the aodata structure. I’ll have to look into it further. I believe that the multi-click on a single column is behaving properly, but I have’t had the time delve in further on the mult-sort. Looks like I have the next topic for post 🙂

ActiveEngine Sensei - January 24, 2011

Bob,

Well after some work yesterday I think I implemented multi-sort correctly. Why don’t you give it a try and let me know if this fits your needs. I’ll give you the link directly before I create a new post.

Multi-sort

Bob S. - January 24, 2011

Great. I’ll give it try over the next couple of hours.

Bob S. - January 24, 2011

My testing shows it works as expected. Many thanks for the quick response! This has saved me quite a bit of time.

2. Jeff - January 25, 2011

Dave –

Finally got a chance to read this and it rocks! In one of the projects I lead we have like 20 or so views that use datatables…this will simplify things so much 🙂

– Jeff

ActiveEngine Sensei - January 26, 2011

Be sure you get the latest. Bob Sherwood pointed out some issues with the multi-column sort which I fixed. The link to latest is in the comment above. Glad this is useful for your team.

3. Lukas - April 13, 2011

Hi, nice job on that article.

But wouldn’t it be easier to load data into DateTable, then filter and sort the bable using TableView?

ActiveEngine Sensei - April 13, 2011

I suppose you could but you would have to parse incoming value pairs and then create your filter statement. It really depends on how you handle getting data to your domain objects. The intent here is to be a le to get data to any of your existing domain objects without recoding your data extraction routines to handle where clauses.

It certainly is possible to do what you’re suggesting. You would have to read your schema from your DataTable to build your statement. Give it a shot and let me know. I’m curious to see what you can come up with.

Lukas - April 14, 2011

Hi,

It worked really nice. Paging, sorting on server side, client was getting only minimal amount of data. It worked very well also with ColimnVisibility, ColumnReorder and InlineEdit. But As you noticed the biggest issue was filtering the DataView, but I haven’t chance to work around this unfortunatelly because we dropped the conception of DataTables in favour of ListView + some jQuery plugins.

ActiveEngine Sensei - April 15, 2011

The principle should be very similar. Iterate over the columns and see if the are listed in the aodata. You have to determine the column type. Have fun.

4. Jason Robertson - April 21, 2011

Sensei – what I need is to connect this to WCF 3.5 SP1 this post http://rantdriven.com/post/Using-Datatablesnet-JQuery-Plug-in-with-WCF-Services.aspx
does not work. A comment about WCF services I would make is without seeing the web.config file and the server and client side code good luck I can make it work with asmx 3.5 I need it to work with WCF 3.5 SP1 though

ActiveEngine Sensei - April 25, 2011

To be honest I have no experience with WCF. Jeff Morris, author of the post you referred to, should be able to help you out. He has experimented with my solution and WCF. Sorry I can’t be much help, but if Jeff helps you out I’d to see te solution. Good luck and let me know what happens.

Jeff - April 25, 2011

Once you get the service working it should just be a matter of debugging with firebug to get it integrated with datatables.

I’ll do another post with the source sometime this week.

ActiveEngine Sensei - April 25, 2011

Awesome – thanks for replying so quickly.

5. Jeff - April 29, 2011

I added a new post with a link to a working example using Datatables/WCF and VS2008: http://www.rantdriven.com/post/Using-Datatablesnet-JQuery-Plug-in-with-WCF-Services-part-Duex.aspx

6. Farshid Zaker - June 26, 2011

Great post. Thanks a lot for sharing your code and experience.
Is there any plan for implementing individual column filtering?

ActiveEngine Sensei - June 28, 2011

Thanks for reading – I hope it was helpful for you. With respects to individual filtering I had planned on tasking a swing at a solution but have been too busy with my day jobs and the ApprovaFlow project. I think the principle would be the same as the original filter method, with the difference being how you would handle filtering for two or more individual columns. I can imagine a scenario where you would want to filter for a value in column A OR a value in Column B.

Why not take a shot at it and share with the rest of us? Thanks again for reading, and if you are interested in an alternative to Windows Workflow try out my series ApprovaFlow. I’m eager to get some feedback on whether my approach is useful.

7. Farshid Zaker - June 29, 2011

Thank you for response. I am going to do it and share the code. There is also another problem with generic filter. While using Entity Framework classes as IQueryable, the search does not work. It seems that Entity Framework isn’t able to translate ToString method to SQL.

About ApprovaFlow, I’ll gladly take a look.
Thanks.

ActiveEngine Sensei - June 29, 2011

Interesting. I have to admit that I haven’t used EF. One of the weakness of my solution is that you are querying against a repository, and in this case the repository already has all of the “rewords” from the dats source. For the tests the repository size was c. 13000 objects and the performance seemed acceptable. I’m not sure how EF works with iQueryable – I would think that iQueryabfle is the end result and there fore EF won’t parse and create the SQL for us.

You always could create the sal statement yourself. As kong as you had your joins pre fabricated you would be simply creating where clause based on the properties of the collection. The DataTables sites had a .net example that create the SQL. I’m proposing that the filter just create that clause. Some view this as a no-no, but you could validate the filter criteria from aodata against the properties of the object and prevent and SQL injection monkey business.

I recall reading a post about predicates, expression trees and EF and that could be a place to start. Thanks for the feedback. EF could be challenge that I might accept later on this summer.

alex - February 7, 2012

Hello and thank you for the series of articles about Datatables. In one of your comments you mentioned that IQueryable is working slow with very big number of records and recommended to use SQL server queries as in datatable’s examples of server-side processing. Did I understand you correctly that everything that you discussed the series of 3 articles will not work for me if I have more than 200 000 records? Thanks

ActiveEngine Sensei - February 8, 2012

Alex,

Sorry to take so long to reply. The upper range of my testing has been c. 15000 records. This is a straight read from the SQL server. If your record set is static you could cache the data and query from the cache. I encourage you to try and see what the results are.

Another alternative is to use pass through SQL and build out a statement to send to your SQL server. The DataTaebles site has an example of this. Let me know how you fare. Good luck.

8. vrkm - July 6, 2012

Hi, the source code download link isn’t working. It’s asking me to login into box.net. Can you please re-upload and publish another download link that doesnt ask for login? thanks.

ActiveEngine Sensei - July 6, 2012

Yikes – thanks for alerting me. Give me few and I’ll have it fixed.

ActiveEngine Sensei - July 6, 2012

Can you tell me what link you chose? Then link @ the end of the post as well as the two on then update box open the datatable.zip file from box.net.

ActiveEngine Sensei - July 7, 2012

I checked all the links and the pull up the zip file. Are you prompted for a password after to click to download?

9. muzit deng - August 4, 2012

Hi,

I am a novice developer on .NET; I am having “Value cannot be null. Parameter name: property:” at exactly ” expr = Expression.Property(expr, pi);” statement when I tried to list the DB file.

I can run the sample Tenant.Json file with succes.

I’ll appreciate if you can help me or at least suggest how to ignore sorting data since getting error from here.

Below is my asmx:
InnoBus busdata = new InnoBus();
var list = busdata.GetInnobusList(25);
var xxxx = list.AsQueryable();
var dataTablePager = new DataTablePager(jsonAOData, xxxx);
var formattedList = dataTablePager.Filter();
return JsonConvert.SerializeObject(formattedList);

ActiveEngine Sensei - August 4, 2012

muzit, the issue is that my code does not take into account for null values. Unfortunately I haven’t been able to fix that, but another reader was kind enough to offer a solution. Here is the link to code. I have had any time to review it, so please let me know the results.

muzit deng - August 4, 2012

Thx for reply. It didn’t work but I’ve solved my problem by using the suggestions in http://www.awilco.net/doku/datatables..

ActiveEngine Sensei - August 5, 2012

Thanks for the info. I hope to get this updated. And thanks for reading the blog.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: