There are tones of samples for PHP about server-side data processing, the only one for c# is
jQuery DataTables Plugin Meets C#.
Zack Owens wrote a DataTableParser class to process the incoming JSON request which will take an HttpRequestBase (or an HttpRequest) and an IQueriable of whatever type. It will output a new FormattedList in the parse method, which you will return via JSON (which is serialized in the Json method for MVC).
The only limit here is the Parser will output all fields to browser. I changed the DataTableParser class so I can define what fields to be output in parameters like below:
Listusers = _biz.GetUsers();
var parser = new DataTableParser(this.Request, users.AsQueryable());
return Json(parser.Parse(c => new List(new string[]{c.Name, c.Email, c.Points.ToString()})), JsonRequestBehavior.AllowGet);
In client side, we just display three fields even though the User has other fields like Birthday, etc
<% =Html.Grid(Model.Ranks)
.Attributes( ID => "userTable" )
.Columns(
col =>
{
col.For(p => p.Name)
.Named("Name");
col.For(p => p.Email)
.Named("Email");
col.For(p => p.Points)
.Named("Points");
}
)
%>
The javascript code for auto refresh:
The whole DataTableParser class:
///
/// Parses the request values from a query from the DataTables jQuery pluggin
///
///List data type
public class DataTableParser
{
/*
* int: iDisplayStart - Display start point
* int: iDisplayLength - Number of records to display
* string: string: sSearch - Global search field
* boolean: bEscapeRegex - Global search is regex or not
* int: iColumns - Number of columns being displayed (useful for getting individual column search info)
* string: sSortable_(int) - Indicator for if a column is flagged as sortable or not on the client-side
* string: sSearchable_(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, but left for backward compatibility)
* string: sEcho - Information for DataTables to use for rendering
*/
private const string INDIVIDUAL_SEARCH_KEY_PREFIX = "sSearch_";
private const string INDIVIDUAL_SORT_KEY_PREFIX = "iSortCol_";
private const string INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX = "sSortDir_";
private const string DISPLAY_START = "iDisplayStart";
private const string DISPLAY_LENGTH = "iDisplayLength";
private const string ECHO = "sEcho";
private const string ASCENDING_SORT = "asc";
private IQueryable_queriable;
private readonly HttpRequestBase _httpRequest;
private readonly Type _type;
private readonly PropertyInfo[] _properties;
public DataTableParser(HttpRequestBase httpRequest, IQueryablequeriable)
{
_queriable = queriable;
_httpRequest = httpRequest;
_type = typeof(T);
_properties = _type.GetProperties();
}
public DataTableParser(HttpRequest httpRequest, IQueryablequeriable)
: this(new HttpRequestWrapper(httpRequest), queriable)
{ }
///
/// Parses theparameter values for the accepted
/// DataTable request values
///
///Formated output for DataTables, which should be serialized to JSON
///
/// In an ASP.NET MVC from a controller, you can call the Json method and return this result.
///
/// public ActionResult List()
/// {
/// // change the following line per your data configuration
/// IQueriableusers = datastore.Linq();
///
/// if (Request["sEcho"] != null) // always test to see if the request is from DataTables
/// {
/// var parser = new DataTableParser(Request, users);
/// return Json(parser.Parse());
/// }
/// return Json(_itemController.CachedValue);
/// }
///
/// If you're not using MVC, you can create a web service and write the JSON output as such:
///
/// using System.Web.Script.Serialization;
/// public class MyWebservice : System.Web.Services.WebService
/// {
/// public string MyMethod()
/// {
/// // change the following line per your data configuration
/// IQueriableusers = datastore.Linq();
///
/// response.ContentType = "application/json";
///
/// JavaScriptSerializer serializer = new JavaScriptSerializer();
/// var parser = new DataTableParser(Request, users);
/// return new JavaScriptSerializer().Serialize(parser.Parse());
/// }
/// }
///
///
public FormatedList Parse()
{
return Parse(this.SelectProperties);
}
public FormatedList Parse(Expression>> properties)
{
var list = new FormatedList();
// import property names
list.Import(_properties.Select(x => x.Name).ToArray());
// parse the echo property (must be returned as int to prevent XSS-attack)
list.sEcho = int.Parse(_httpRequest[ECHO]);
// count the record BEFORE filtering
list.iTotalRecords = _queriable.Count();
// apply the sort, if there is one
ApplySort();
// parse the paging values
int skip = 0, take = 10;
int.TryParse(_httpRequest[DISPLAY_START], out skip);
int.TryParse(_httpRequest[DISPLAY_LENGTH], out take);
// if -1, take all
if (take == -1)
take = list.iTotalRecords;
// setup the data with individual property search, all fields search,
// paging, and property list selection
list.aaData = _queriable.Where(ApplyGenericSearch)
.Where(IndividualPropertySearch)
.Skip(skip)
.Take(take)
.Select(properties)
.ToList();
// total records that are displayed
list.iTotalDisplayRecords = list.aaData.Count;
return list;
}
private void ApplySort()
{
// enumerate the keys for any sortations
foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SORT_KEY_PREFIX)))
{
// column number to sort (same as the array)
int sortcolumn = int.Parse(_httpRequest[key]);
// ignore malformatted values
if (sortcolumn < 0 || sortcolumn >= _properties.Length)
break;
// get the direction of the sort
string sortdir = _httpRequest[INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX + key.Replace(INDIVIDUAL_SORT_KEY_PREFIX, string.Empty)];
// form the sortation per property via a property expression
var paramExpr = Expression.Parameter(typeof(T), "val");
var expression = Expression.Convert(Expression.Property(paramExpr, _properties[sortcolumn]), typeof(Object));
var propertyExpr = Expression.Lambda>(expression, paramExpr);
// apply the sort (default is ascending if not specified)
if (string.IsNullOrEmpty(sortdir) || sortdir.Equals(ASCENDING_SORT, StringComparison.OrdinalIgnoreCase))
_queriable = _queriable.OrderBy(propertyExpr);
else
_queriable = _queriable.OrderByDescending(propertyExpr);
}
}
///
/// Expression that returns a list of string values, which correspond to the values
/// of each property in the list type
///
///This implementation does not allow indexers
private Expression>> SelectProperties
{
get
{
//
return value => _properties.Select
(
// empty string is the default property value
prop => (prop.GetValue(value, new object[0]) ?? string.Empty).ToString()
)
.ToList();
}
}
///
/// Compound predicate expression with the individual search predicates that will filter the results
/// per an individual column
///
private Expression> IndividualPropertySearch
{
get
{
var paramExpr = Expression.Parameter(typeof(T), "val");
Expression whereExpr = Expression.Constant(true); // default is val => True
foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SEARCH_KEY_PREFIX)))
{
// parse the property number
int property = -1;
if (!int.TryParse(key.Replace(INDIVIDUAL_SEARCH_KEY_PREFIX, string.Empty), out property)
|| property >= _properties.Length || string.IsNullOrEmpty(_httpRequest[key]))
continue; // ignore if the option is invalid
string query = _httpRequest[key].ToLower();
// val.{PropertyName}.ToString().ToLower().Contains({query})
var toStringCall = Expression.Call(
Expression.Call(
Expression.Property(paramExpr, _properties[property]), "ToString", new Type[0]),
typeof(string).GetMethod("ToLower", new Type[0]));
// reset where expression to also require the current contraint
whereExpr = Expression.And(whereExpr,
Expression.Call(toStringCall,
typeof(string).GetMethod("Contains"),
Expression.Constant(query)));
}
return Expression.Lambda>(whereExpr, paramExpr);
}
}
///
/// Expression for an all column search, which will filter the result based on this criterion
///
private Expression> ApplyGenericSearch
{
get
{
string search = _httpRequest["sSearch"];
// default value
if (string.IsNullOrEmpty(search) || _properties.Length == 0)
return x => true;
// invariant expressions
var searchExpression = Expression.Constant(search.ToLower());
var paramExpression = Expression.Parameter(typeof(T), "val");
// query all properties and returns a Contains call expression
// from the ToString().ToLower()
var propertyQuery = (from property in _properties
let tostringcall = Expression.Call(
Expression.Call(
Expression.Property(paramExpression, property), "ToString", new Type[0]),
typeof(string).GetMethod("ToLower", new Type[0]))
select Expression.Call(tostringcall, typeof(string).GetMethod("Contains"), searchExpression)).ToArray();
// we now need to compound the expression by starting with the first
// expression and build through the iterator
Expression compoundExpression = propertyQuery[0];
// add the other expressions
for (int i = 1; i < propertyQuery.Length; i++)
compoundExpression = Expression.Or(compoundExpression, propertyQuery[i]);
// compile the expression into a lambda
return Expression.Lambda>(compoundExpression, paramExpression);
}
}
}
public class FormatedList
{
public FormatedList()
{
}
public int sEcho { get; set; }
public int iTotalRecords { get; set; }
public int iTotalDisplayRecords { get; set; }
public List
> aaData { get; set; }
public string sColumns { get; set; }
public void Import(string[] properties)
{
sColumns = string.Empty;
for (int i = 0; i < properties.Length; i++)
{
sColumns += properties[i];
if (i < properties.Length - 1)
sColumns += ",";
}
}
}
