jump to navigation

Sensei is Guest Hosting on Another Show!! October 9, 2012

Posted by ActiveEngine Sensei in DataTables.Net, RavenDB, Tutorial.
Tags: , , ,
add a comment

Antonin Januska was kind enough to let Sensei guest post for him on his awesome blog.  There you’ll find great posts on CSS, UI design, Bootstrap Framework among many other goods related to Web development.  As you Sensei loves him some RavenDB and DataTables, and has created a tutorial on how to create paging solution with RavenDB that takes advantage of Lucene and DataTables inherent filtering capability.  Teaming up these two pieces of technology is like one of those rare events when Marvel & DC teamed up Spidey and Supes.

Kind of feeling like Stan Lee so lets end with Excelsior!!

Advertisements

Some Pitfalls To Avoid With KnockoutJS “options” Binding September 8, 2012

Posted by ActiveEngine Sensei in ActiveEngine, Ajax, KnockoutJS, Mythology, New Techniques, Open Source, Tutorial.
Tags: , , , , , , , , , ,
add a comment

As Sensei has written earlier, KnockOutJS is a great framework for creating rich client side solutions for you web applications.  Simply said it cuts your development down considerably by performing CSS binding for you, while also bringing better structure to your Javascript through the use of the MVVM pattern.  But even the greatest of all wizardry, magery, grammary, magik has its stumbling blocks.  Each tool you use constrains you in some way.  This week Sensei uncovered another puzzle that has left him wondering still if he found the best solution.  Maybe you will have some insight you can share with the “options” binding from Knockout.

Here is the scenario:  You have a <select> ( or a drop down list as us old school Windows devs are found of saying) that you wish to populate with values from an array.  There are two ways that this select list will be used.  The first is when you create a record, and naturally you would like the list to display “New …”.  The second goal is set the value of the <select> to match the value of a current record.  Here is the JS-Fiddle with the first attempt. Selecting from the list sets the value, and you’ll the update at the bottom. Clicking “Simulate Editing Amys Record” will set the value of the list to “Amy” as though you were performing an edit operation.  Here is the view model code:


var ViewModel = function() {
var self = this;

// Simulated seed data from server
this.seedData = ko.observableArray([
{
ID: 1,
firstName: 'John',
value: '333'},
{
ID: 2,
firstName: 'Bob',
value: '333'},
{
ID: 3,
firstName: 'Amy',
value: '333'}]),

// Simulated data from server
self.data = {
title: ko.observable('This is a sample'),
selectedValue: ko.observable("")
}

self.prepForNew = function() {
self.data.selectedValue("");
}

self.changeIt = function() {
self.data.selectedValue("Amy");
}

};

var vm = new ViewModel();
ko.applyBindings(vm);

Now bear with Sensei as he describes the behavior that was so confounding.  We are initializing the <select> by setting data.selectedValue(“”).  The first time the page is displayed we get the behavior that we want.  Click the “Set List for New Record List”.  Nothing happens.  If you trace with Firebug you’ll see that the value is indeed being set, but once it leaves the method it reverts to the current value in the <select>.

Speak, friend, and enter …

Oookkay. Scratch your head. Walk away. Come back, fiddle so more. Rinse, then repeat for about 5 hours.  This shouldn’t be.  In his frenzy Sensei did not consult StackOverflow.  At last an idea came to mind.  Why not add “New …” as the first entry in <select>, give a value of -1 and an ID of -1.  This way at least it is identifiable.  Seems silly but when you have things to accomplish sometimes you just have to eat the sausage instead of thinking about how its made.  Check out the new JS Fiddle before Sensei explains.

Three simple changes have occured.  First we cheate by adding a new object to the array that supports our <select>.  We gave it the “New …” as the first element.

this.seedData = ko.observableArray([
{
//  Here is the default caption object
ID: -1,
firstName: 'New ...',
value: ''},
{
ID: 1,
firstName: 'John',
value: '333'},
{
ID: 2,
firstName: 'Bob',
value: '333'},
{
ID: 3,
firstName: 'Amy',
value: '333'}]),

We yanked out the “optionsCaption: ‘New …” entry in the HTML mark for the view.  Finally the altered the method self.prepForNew to set the value of selectedValue to “New …” with the statement selectedValue(“New …”);  This forces KnockOut to sync to what we want.  Remember that we are working with methods when setting values with Knockout, hence the use of (“New …”);

Sensei is happy to have things working.  Being perplexed over finding out the cause instead of simply creating something simple did fret away the hours.  Like with any new tool, there are nuances that won’t become apparent until you are hit over the head with their pitfalls.

How to Create Server-Side Paging for DataTables.Net with ASP.Net December 19, 2010

Posted by ActiveEngine Sensei in .Net, ActiveEngine, C#, DataTables.Net, Fluent, jQuery, New Techniques, Open Source, Problem Solving, Tutorial.
Tags: , , , , , , ,
22 comments

Source code has been updated!! Read about the changes in Dynamically Select Columns with Server-Side Paging and 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.

A central theme for 2010 has been fluency, or the continual practice of certain methods to such a degree that your performance improves and you produce increasingly polished, effective solutions.  For software development this has meant tools to save time and increase quality.  It also means keeping an eye toward making the users of your solutions more efficient as well.  In the spirit of “fluent solutions”, Sensei will end the year with a post that examines how to create a data paging solution for the jQuery data grid plug-in DataTables.Net.

DataTables can turn a HTML table into a fully functional data grid like the one offered by Telerik.  This plug-in offers client side sorting, filtering/ search,  as well as support for server-side processing processing of data.  It is an extremely feature rich tool created by Allan Jardine, and is itself worthy of a series of posts.  For this post on data paging Sensei recommends that you read through these examples to get an idea of what the data paging service needs to achieve.

Let’s get started with the goals we need to achieve when providing server-side data paging support:

  • Send data to client in the multiples or “chunks” that the client requests, and respond when the size of the sets requested is changed by the user.
  • Re-order the data set if the user clicks on a column heading.  Honor the data set size when returning the data.
  • Filter across all columns of data based on user input.  Implement this as partial matches, and again, honor the data set size.

Remember this is about flexibility, so we have the additional goals of:

  • Create a solution that can be reused.
  • Provide a mechanism to accommodate any type of .Net class using generics.

Essentially we want to be able to write code like so:

var tenants = tenantRepository.GetAll();
var dataTablePager = new DataTablePager();
var returnDataSet = dataTablePager.Filter(requestParms, tenants);

Before we proceed, Sensei wants to acknowledge those really smart people whose ideas contributed to this solution:

Zack Owens – jQuery DataTables Plugin Meets C#

Jeff Morris – Using Datatables.net JQuery Plug-in with WCF Services

Dave Ward – ASMX ScriptService mistake – Invalid JSON primitive

You may want to download the source before reading the rest of this post.

Communicating with DataTables

DataTables uses the following parameters when processing server-side data:

Sent to the server:

Type Name Info
int iDisplayStart Display start point
int iDisplayLength Number of records to display
int iColumns Number of columns being displayed (useful for getting individual column search info)
string sSearch Global search field
boolean bEscapeRegex Global search is regex or not
boolean bSortable_(int) Indicator for if a column is flagged as sortable or not on the client-side
boolean bSearchable_(int) Indicator for if a column is flagged as searchable or not on the client-side
string sSearch_(int) Individual column filter
boolean bEscapeRegex_(int) Individual column filter is regex or not
int iSortingCols Number of columns to sort on
int iSortCol_(int) Column being sorted on (you will need to decode this number for your database)
string sSortDir_(int) Direction to be sorted – “desc” or “asc”. Note that the prefix for this variable is wrong in 1.5.x where iSortDir_(int) was used)
string sEcho Information for DataTables to use for rendering

Reply from the server

In reply to each request for information that DataTables makes to the server, it expects to get a well formed JSON object with the following parameters.

Type Name Info
int iTotalRecords Total records, before filtering (i.e. the total number of records in the database)
int iTotalDisplayRecords Total records, after filtering (i.e. the total number of records after filtering has been applied – not just the number of records being returned in this result set)
string sEcho An unaltered copy of sEcho sent from the client side. This parameter will change with each draw (it is basically a draw count) – so it is important that this is implemented. Note that it strongly recommended for security reasons that you ‘cast’ this parameter to an integer in order to prevent Cross Site Scripting (XSS) attacks.
string sColumns Optional – this is a string of column names, comma separated (used in combination with sName) which will allow DataTables to reorder data on the client-side if required for display
array array mixed aaData The data in a 2D array

The data sent back is in the following form depicted below. Note that aaData is merely an array of strings – there is no column information. This will present a challenge in that you will not be able to simply serialize a collection and pass back the results.

{
    "sEcho": 3,
    "iTotalRecords": 57,
    "iTotalDisplayRecords": 57,
    "aaData": [
        [
            "Gecko",
            "Firefox 1.0",
            "Win 98+ / OSX.2+",
            "1.7",
            "A"
        ],
        [
            "Gecko",
            "Firefox 1.5",
            "Win 98+ / OSX.2+",
            "1.8",
            "A"
        ],
        ...
    ]
}

As you may be aware, if you wish to use ASP.Net web services to serialize JSON you must POST to the service and instruct it to interpret your parameters as JSON. DataTables will POST variables as value pairs and this won’t work for us when POSTing to a web service. We’ll have to translate the variables to a usable format. Luckily DataTables allows us to intervene with the following code, where we create a JSON string by serializing a structure called aoData:

"fnServerData": function ( sSource, aoData, fnCallback ) {

		        	var jsonAOData = JSON.stringify(aoData);

			        $.ajax( {
                                        contentType: "application/json; charset=utf-8",
				        type: "POST",
				        url: sSource,
				        data: "{jsonAOData : '" + jsonAOData + "'}",
				        success: function(msg){
				            fnCallback(JSON.parse(msg.d));
				        },
				        error: function(XMLHttpRequest, textStatus, errorThrown) {
                            alert(XMLHttpRequest.status);
                            alert(XMLHttpRequest.responseText);

                        }
			        });

Our web service can now de-serialize aoData and parse the appropriate parameters. This gives us important items such as how many records to display, what columns to sort on, and what search terms should be applied in a filter.

DataTablePager Class

DataTablePager.cs is the work horse of our solution.  It will sort, filter and order our data, and as an extra, serialize the results in format required by aaData.  Here’s the constructor:

public DataTablePager(string jsonAOData, IQueryable queryable)
        {
            this.queryable = queryable;
            this.type = typeof(T);
            this.properties = this.type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            this.aoDataList = new List>();
            this.sortKeyPrefix = new List();

            PrepAOData(jsonAOData);
        }

The parameter jsonAOData is the JSON string that contains the variables iDisplayStart, iDisplayLength, etc.  These will be parsed by the method PrepAOData.  The parameter queryable is the collection of records that will be filtered and parsed into JSON format required by DataTables.

The method Filter() coordinates all of the work.  It’s pretty simple what we want to do:  filter our data based on each column containing the search term, sort the result, then pull out the number of records we need to include in the page, and finally convert the collection into the format DataTables understands.

public FormattedList Filter()
        {
            var formattedList = new FormattedList();

            //  What are the columns in the data set
            formattedList.Import(this.properties.Select(x => x.Name + ",")
                                                 .ToArray());

            //  Return same sEcho that was posted.  Prevents XSS attacks.
            formattedList.sEcho = this.echo;

            //  Return count of all records
            formattedList.iTotalRecords = this.queryable.Count();

            //  Filtered Data
            var records = this.queryable.Where(GenericSearchFilter());
            records = ApplySort(records);

            //  What is filtered data set count now.  This is NOT the
            //  count of what is returned to client
            formattedList.iTotalDisplayRecords = (records.FirstOrDefault() == null) ? 0 : records.Count();

            //  Take a page
            var pagedRecords = records.Skip(this.displayStart)
                     .Take(this.displayLength);

            //  Convert to List of List
            var aaData = new List>();
            var thisRec = new List();

            pagedRecords.ToList()
                    .ForEach(rec => aaData.Add(rec.PropertiesToList()));

            formattedList.aaData = aaData;

            return formattedList;
        }

That said, there is some trickery that goes on in order to make this happen because we are creating a solution to is going to work with any IQueryable to we supply. This means that the filtering and the sorting will need to be dynamic.

To make the filtering dynamic we will build expression trees that will convert each property to a string, convert the string to lower case, then execute a Contains method against the value of that property.  The method GenericSearchFilter() called on line 16 accomplishes this with the following lines of code:

//  Except from GenericSearchFilter
MethodInfo convertToString = typeof(Convert).GetMethod("ToString", Type.EmptyTypes);

 var propertyQuery = (from property in this.properties
        let toStringMethod = Expression.Call(                                          Expression.Call(Expression.Property(paramExpression, property), convertToString, null),                                                            typeof(string).GetMethod("ToLower", new Type[0]))
         select Expression.Call(toStringMethod, typeof(string).GetMethod("Contains"), searchExpression)).ToArray();

We get an array of Expressions that when executed will tell us if the value matches our search term. What we want is to include the item if ANY of the properties is a match, so this means we have to use and OR for all of the properties. That can be accomplished with:

for (int j = 0; j < propertyQuery.Length; j++)
{
  //  Nothing to "or" to yet
  if (j == 0)
  {
    compoundOrExpression = propertyQuery[0];
  }

  compoundOrExpression = Expression.Or(compoundOrExpression,
                                              propertyQuery[j]);
}

So with what is listed above we would be able to match all properties with against a single search term. Pretty cool. But DataTables raises the bar even higher. If you were to go to the samples page and filter using multiple partial words you would find that you could perform some very effective searches with phrases like “new chic”. This would select all records that had properties containing “new” OR “chic”. Imagine the scenario where your user wants to finds all cities “New York” or “Chicago”. We’ve all been there where we have a grid and can only search for one term, or worse, where we have to add a row to a search filter grid and constantly push a “query” button to perform our searches. DataTables does all of the with one search box – just type and the filtering begins.

GenericSearchFilter() handles that scenario. First the search term is parsed into individual terms if there is a ” ”  supplied in the string. This means we will have to perform the propertyQuery for each term that we have. To return all of the records that correspond to each term we still need to perform the OR in groups, but then we need to AND these predicates together so we can get all of the groups per individual term. Here’s the source edited slightly for readability:

//  Split search expression to handle multiple words
var searchTerms = this.genericSearch.Split(' ');

for (int i = 0; i < searchTerms.Length; i++) {    var searchExpression = Expression.Constant( searchTerms[i].ToLower());                   //  For each property, create a contains expression   //  column => column.ToLower().Contains(searchTerm)
  //  Edited for clarity - create the array propertyQuery logic is here ...
  var propertyQuery = ...

  //  Inner loop for grouping all OR's for this search term
  for (int j = 0; j < propertyQuery.Length; j++)   {     //  Nothing to "or" to yet     if (j == 0)     {       compoundOrExpression = propertyQuery[0];     }     compoundOrExpression = Expression.Or(compoundOrExpression, propertyQuery[j]);   }   //  First time around there is no And, only first set of or's   if (i == 0)   {     compoundAndExpression = compoundOrExpression;   }   else   {     compoundAndExpression = Expression.And(compoundAndExpression, compoundOrExpression);   } } 

So GenericSearchFilter will build a humongous expression tree for all the properties in your class. To make this usable for the Where we convert it using Expression.Lambda and our Where clause just goes about its merry way. Because we have used generics, you can supply any class from your assemblies. One caveat, and Sensei is trying to find a resolution. If you have a string property to that is set to null, the expression tree fails. You’ll note that in the classes supplied in the sample, the properties that are of type string in the Tenant class are defaulted to empty in the constructor.  A small price to pay for some great functionality. To sort our data we use the method ApplySort():

 private IQueryable ApplySort(IQueryable records)         {             string firstSortColumn = this.sortKeyPrefix.First();             int firstColumn = int.Parse(firstSortColumn);             string sortDirection = "asc";             sortDirection = this.aoDataList.Where(x => x.Name == INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX +
                                                                      "0")
                                                .Single()
                                                .Value
                                                .ToLower();

            if (string.IsNullOrEmpty(sortDirection))
            {
                sortDirection = "asc";
            }

            return records.OrderBy(this.properties[firstColumn].Name, sortDirection, true);
        }

An extension method OrderBy will accept the name of column, the sort direction as parameters. The parameter initial will indicate if we are sorting mulitple times, so we can accomplish multi-property sort with syntax like

var sortedRecords = records.OrderBy("State", "desc", true)
                                      .OrderBy("City", "asc", false);

public static IOrderedQueryable OrderBy(this IQueryable source, string property, string sortDirection, bool initial)
        {
            string[] props = property.Split('.');
            Type type = typeof(T);
            ParameterExpression arg = Expression.Parameter(type, "x");
            Expression expr = arg;
            foreach (string prop in props)
            {
                // use reflection (not ComponentModel) to mirror LINQ
                PropertyInfo pi = type.GetProperty(prop);
                expr = Expression.Property(expr, pi);
                type = pi.PropertyType;
            }
            Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
            LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);

            string methodName = string.Empty;

            //  Asc or Desc
            if (sortDirection.ToLower() == "asc")
            {
                //  First clause?
                if (initial && source is IOrderedQueryable)
                {
                    methodName = "OrderBy";
                }
                else
                {
                    methodName = "ThenBy";
                }
            }
            else
            {
                if (initial && source is IOrderedQueryable)
                {
                    methodName = "OrderByDescending";
                }
                else
                {
                    methodName = "ThenByDescending";
                }
            }

            object result = typeof(Queryable).GetMethods().Single(
                    method => method.Name == methodName
                            && method.IsGenericMethodDefinition
                            && method.GetGenericArguments().Length == 2
                            && method.GetParameters().Length == 2)
                    .MakeGenericMethod(typeof(T), type)
                    .Invoke(null, new object[] { source, lambda });
            return (IOrderedQueryable)result;
        }

All good things …
It’s been a long ride, this post. A lot of code discussed, a lot of ground covered. The solution is here.  As always, play around and see how this can help you. If anything breaks, tell Sensei. If you have improvements, tell Sensei. DataTables is a great tool for your arsenal, hopefully the DataTablePager can help you integrate paging with large datasets as part of your solution offering.

Right now Sensei wants to sign off by toasting to you for wading through all of this, and for having the desire to build up your skills.  Obtaining fluency in what you do is a hard road to travel, but it’s worth it because you get things done quicker and better with each session.

Be sure to read about the latest version in “Dynamically Select Columns with Server-Side Paging and Datatables.Net
%d bloggers like this: