jump to navigation

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: , , , , , , ,
trackback

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
About these ads

Comments»

1. jmorris - December 20, 2010

Great post, you took this much further than I did with the lambda expressions for searching/filtering across multiple properties. I had hoped to do another post that addressed this using the Dynamic Expression api or something similar, but I think you nailed it here.

-Jeff

ActiveEngine Sensei - December 20, 2010

Thanks Jeff. Zack Owen’s post got me most the way there with respects to the filtering portions, although his code supported just a single search term. Your client side code in your post helped me get started with how to use the fnServerData function. Before I ran across your blog I was really struggling. I have to admit that this took a lot longer than what I had imagined – I thought it would be an afternoon of hacking away. I won’t tell you how many days I put into this, but I finished on my last day before Christmas vacation and thought “what a great Christmas present”!

By the way I liked your post on “Simple Pipe and Filters Implementation in C# with Fluent Interface Behavior”. It’s given me food for thought for Monads and fluent interfaces, something I’m going to work on in 2011.

Thanks again for reading my blog, and have a great holiday!

2. updated tech news - March 2, 2011

thx for great post

3. Gregg - April 29, 2011

I’m really confused here. Perhaps you’re trying to cover all scenarios but this solution seems really, really complex. Mind you, your code works while my own does not, so that kinda puts my opinion in perspective. Still, why would you perform the sorting, filtering and all that stuff in your pager class rather than performing that on the original data query (i.e., when pulling from a database)? In cases where your query might pull 1000′s of rows, wouldn’t you want to do the filtering beforehand and then hand off a pre-filtered IQueryable data object to the pager class?

Thanks to your solution, I finally managed to get my service to return JSON-style output in a DataTables-friendly format…however, the grid is never getting populated. No idea why. I noticed that your code in Default.aspx has…

fnCallback(JSON.parse(msg.d));

…fired after a successful Ajax call. I’m using Firebug to step through the code and I don’t see where the “msg” object even has a “d” property. Again, though…your code sample works, my own code does not. Still, I’m curious as to where that property is defined.

Ultimately, I just cannot seem to get the DataTables plugin to work with server-side data. I know I’m making a mistake somewhere but for the life of me, I cannot figure out where. At first it just seemed like I had incorrectly-formatted data (I was passing in proper JSON rather than the expected arrays of arrays) but fixing that issue doesn’t seem to have solved the problem. The most frustrating thing is that your demo works. My own code, while it returns similar output (different objects, of course), doesn’t. I dunno…I feel like if DataTables is such a hassle to get working, it’s probably not worth the effort in most cases. That’s a shame because for smaller sets of relatively-static data, it works so beautifully.

ActiveEngine Sensei - April 29, 2011

Thanks for reading my post. I have two others in the series that discuss the sorting and other issues as well. Be sure that you have the latest version of the code too.

Yep – the filtering method is a bit scary. The expression trees implementation is used so that you can apply the solution to any type of IQueryable without knowing what the needing to know the particulars of your object. The power comes from the solution being able to be used with any IQueryable without altering the class. You are correct assessing it’s weakness – if you have thousands of rows this solution will not perform optimally. I can’t recall the actual count of the data set I used – I think I kept it at about 14K rows of data. Above that another solution is required that would either create pass through sql or use Linq-To-SQL or the like. At the DataTables site there is a sample the implements a pass through query, and this might be of interest to you.

msg.d is the mechanism that Microsoft has chosen to avoid a XSS security flaw. If you go to Dave Ward’s blog encosia.com he discusses it’s use. I believe in .Net 40 msg.d is no longer required, and Dave also has a post regarding how to handle these scenarios. Are you using VS 2010 by any chance? This might be a source of some of your issues.

Another thing to look for is that you are matching the columns with the data set that’s returned. I struggled a bit at first with this and it produced the behavior that you have described. While I can’t guarantee I can look at it today, if you send me your code I can take a look and see if I can help. I’ll contact you directly via your email account.

4. Gregg - April 30, 2011

Bingo! “msg.d” was, indeed, the issue for me. That and a distinct lack of functioning brain cells. The columns and everything else matched up as expected, although I’ve noticed that the output of the “sColumns” seems to have extra commas. For example…

{“sEcho”:2,”iTotalRecords”:5,”iTotalDisplayRecords”:5,”aaData”:[["1","foo"],["2","foo"],["3","foo"],["4","foo"],["5","foo"]],”sColumns”:”ModelId,,ModelName,”}

Anyhow, it WORKS! I’m definitely going to have to modify this heavily for my needs, though, because I’m querying with Oracle stored procedures against a large dataset (~2 million records) rather than LINQ to SQL. My stored procedures take the page number and size as parameters so that portion of the Filter method is already handled. Likewise the sorting. Your code, though, is definitely what I need to be able to start using the DataTables plugin with my MVC application so thank you very, very much! Also, I do appreciate you pointing out Dave Ward’s blog…there’s a lot of really good & relevant info there. Thanks again for the code and, most of all, for your patient & helpful suggestions. Much appreciated!

ActiveEngine Sensei - April 30, 2011

Yep – unfortunately you have to embrace the “d” ;)

ActiveEngine Sensei - May 2, 2011

If you need to reduce records that you pull back from Oracle, you may consider using pass through SQL. On the DataTables site there was an example that created the pass through statement and fired it at the database. Here’s the link: http://datatables.net/development/server-side/asp_net. It is written against the schema of the db table but looks very straight forward.

5. Gregg - May 2, 2011

Yeah, I’m looking at a similar mechanism for taking the sort parameters from DataTables and passing them as parameters to my Oracle stored procedures. Should be do-able without a major problem…I hope.
;)

Thanks!

6. Some links for 2011-05-20 at The Standard Output - May 20, 2011

[...] Code Snippets – Snipplr Social Snippet Repository An O(ND) Difference Algorithm for C# How to Create Server-Side Paging for DataTables.Net with ASP.Net « ActiveEngine How to make a transparent PNG image for IE6 using The GIMP « Matthew Capewell’s Blog jQuery [...]

7. Ralph K - September 28, 2011

What a great peace of code, just what I needed!

I’ve made some modifications because we are using custom objects with not only string parameters but also other types of parameters.
I’ve also needed to modificate because when we were filtering it gave us null reference exceptions. This was because we have string properties in our custom objects that could be NULL.

The modification was only to the GenericSearchFilter. Here’s the code:

private Expression<Func> GenericSearchFilter()
{
// Create a list of searchable properties
var filterProperties = this.searchAndSortables.Where(x => x.IsSearchable)
.Select(x => x.Property)
.ToList();

// When no filterProperties or search terms, return a true expression
if (string.IsNullOrEmpty(this.genericSearch) || filterProperties.Count == 0)
{
return x => true;
}

ParameterExpression paramExpression = Expression.Parameter(this.queryable.ElementType, “val”);
Expression compoundOrExpression = Expression.Call(Expression.Property(paramExpression, filterProperties[0]), “ToString”, null);
Expression compoundAndExpression = Expression.Call(Expression.Property(paramExpression, filterProperties[0]), “ToString”, null);

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

for (int i = 0; i column.ToLower().Contains(searchTerm)
var propertyQuery = (from property in filterProperties
let toStringMethod = Expression.Call(
MemberExpression.Call(
Expression.Coalesce(
Expression.Convert(
Expression.Property(paramExpression, property),
typeof(object)),
Expression.Convert(Expression.Constant(string.Empty), typeof(object))),
“ToString”, new Type[] { }, new Expression[] { }),
typeof(string).GetMethod(“ToLower”, new Type[0]))
select Expression.Call(toStringMethod, typeof(string).GetMethod(“Contains”), searchExpression)).ToArray();

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);
}
}

// Create Lambda
return Expression.Lambda<Func>(compoundAndExpression, paramExpression);
}

ActiveEngine Sensei - September 28, 2011

Awesome! Glad that this worked out for you. It’s been nagging at me how to resolve the null object issue. I’ll incorporate this in the next round. Thanks for sharing, and thanks for reading the blog. Kudos should also go to Allan who created DataTables.Net, Zack Owen for his original post that inspired this code, and Jeff Morris with his post on processing the DataTables.Net parameters. We wouldn’t be hear without them!

RalphK - September 29, 2011

Yes indeed, they started it, thanks to them! I’ve ran into another problem as well. We are using the fnFilter API option to switch between certain ‘views’ of the datatable.

The code doesn’t apply the fnFilter correctly, no filter was applied. I checked the code and noticed that the INDIVIDUAL_SEARCH_KEY_PREFIX (sSearch_) is not used. Only the GENERIC_SEARCH is used (sSearch).

I’m going to try to change the code so it also accepts the INDIVIDUAL_SEARCH_KEY_PREFIX and share it with you.
Can you also give it a try to change the code?

ActiveEngine Sensei - September 29, 2011

Sure thing. This is great – thanks again for sharing. Hopefully I can squeeze in some time with this today.

8. RalphK - September 29, 2011

I didn’t had much time to optimize the code but here it is.
This enables the fnFilter (sSearch_*) options. GL!

Add just below private List columns;
============================================
private List<NameValuePair> colSearches;
Just below:

Add to the bottom of the private void PrepAOData(string aoData) method
============================================
// Extra search cols where search is not null
this.colSearches = aoDataList.Where(x => x.Name.StartsWith(INDIVIDUAL_SEARCH_KEY_PREFIX))
.Where(x => !x.Value.Equals(“”))
.ToList();

Add to the GenericSearchFilter() method just before // Create Lambda
return Expression.Lambda<Func>(compoundAndExpression, paramExpression);
============================================
// Get propertyinfo corresponding to the search column
for (int i = 0; i x.ColumnIndex.Equals(Convert.ToInt32(this.colSearches[i].Name.Replace(INDIVIDUAL_SEARCH_KEY_PREFIX, “”))))
.Select(x => x.Property)
.ToList();

// The expression to find
var searchExpression = Expression.Constant(this.colSearches[i].Value.ToLower());

// For each property, create a contains expression
// column => column.ToLower().Contains(searchTerm)
var propertyQuery = (from property in colFilterProperties
let toStringMethod = Expression.Call(
MemberExpression.Call(
Expression.Coalesce(
Expression.Convert(
Expression.Property(paramExpression, property),
typeof(object)),
Expression.Convert(Expression.Constant(string.Empty), typeof(object))),
“ToString”, new Type[] { }, new Expression[] { }),
typeof(string).GetMethod(“ToLower”, new Type[0]))
select Expression.Call(toStringMethod, typeof(string).GetMethod(“Contains”), searchExpression)).ToArray();

for (int j = 0; j < propertyQuery.Length; j++)
{
compoundAndExpression = Expression.And(compoundAndExpression, propertyQuery[j]);
}
}

ActiveEngine Sensei - September 29, 2011

This will warrant a new post :) Speaking of which, have I’m assuming you’ve read through the other posts. Just want to make sure you’re using the latest version of the code. I have get off my butt and get this up on GIT so version control / change control is better. I guess GIT would make it legit. Bad joke, I know.

Dude, thanks again for all your contribs. When I first sat down to do this, I wasn’t sure that this would fly. If you note the date of this first post, it was Dec. 19. I was up at 4 am in a hotel while traveling to Christmas vacation destination. This was an early Christmas present for me and amazingly a lot other people too.

I’ll take a look at this tonight and let you know the results.

RalphK - September 29, 2011

Thats great to hear! Hopefully it will help a lot of people! It has done great for me till now…

Another small bug in private void PrepAOData(string aoData), line 190:
var ascDesc = aoDataList.Where(x => x.Name == “sSortDir_” + order)

Must be:
var ascDesc = aoDataList.Where(x => x.Name == INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX + order)

I’ve also ran into some issues with the Newtonsoft DLL (No relevant lines of code error). I’ve replaced the NewtonSoft methods for JavaScriptSerializer.Serialize and Deserialize. Unfortunatly its not possible to let in the class library. I had to add the class to my web project.

Another tip that might make the class more flexible:
The code only returns what columns you’re asking, but what if you want to use more parameters within the fnRender but without plotting it to any column.
Solution is maybe to build another webmethod to get the full datarow (in JSON) from the source given bij the row. I hope you understand. So not, please contact me.

ActiveEngine Sensei - September 29, 2011

Just want to make sure you have the latest source: http://www.box.net/shared/zc6qf4x6f0

After reading through the blog I realized that I didn’t link up the latest post. It’s here and possibly aSorDir issue is resolved. I have to review.

What were your issues with Newtonsoft? I chose that over JavaScriptSerializer because of flexibility and speed and am curious as to what you encountered.

Regarding the fnRender, you could always request the columns of data and chose to NOT display them with DataTables. That way they are still available to you for rendering and you do not have to have second page method. Here’s the sample from Datatables.Net

9. Ralph K - September 30, 2011

ActiveEngine,

I’ve got the latest version from a few posts ahead, it’s from January 24th.
I’ve read about the datatables method and that is a possibility. I’m going to try that one.

I’m going to try to reproduce the error I’ve got with the NewtonSoft.Json.dll and let you know. It was something with ‘No relevant lines of code’ and a missing certain assembly. Read something about mixing .NET versions what caused it.

10. Ralph K - September 30, 2011

I posted my error below. I think it’s because of my website already used the Newtonsoft.Json.Net20.dll instead of the Newtonsoft.Json.dll. I’m gonna download the newest version and try again.

Server Error in ‘/’ Application.
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0656: Missing compiler required member ‘System.Runtime.CompilerServices.ExtensionAttribute..ctor’

Source Error:

[No relevant source lines]

Source File: Line: 0

ActiveEngine Sensei - October 3, 2011

Thanks for all your feedback. I’m in the middle of a roll out so I won’t be able to review everything for a few days. Again, thanks.

11. jquery | Pearltrees - March 1, 2012

[...] Posted by ActiveEngine Sensei in .Net , ActiveEngine , C# , DataTables.Net , Fluent , jQuery , New Techniques , Open Source , Problem Solving . trackback 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. How to Create Server-Side Paging for DataTables.Net with ASP.Net « ActiveEngine [...]


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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: