ASP.Net MVC DataTables Model Binder

pwc1011

For those of you using asp.net mvc, I thought I would share some work we're doing to use DataTables. I append some additional posts as testing progresses.

First, here are classes needed for communicating with datatables:

[code]
public class jqDataTablesResult
{
//int: iTotalRecords - Total records, before filtering (i.e. the number of records in the database)
public int iTotalRecords { get; set; }
//int: iTotalDisplayRecords - Total records, after filtering (i.e. the number of records after filtering has been applied)
public int iTotalDisplayRecords { get; set; }
//string: sEcho - An unaltered copy of sEcho sent from the client side. 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
public int sEcho { get; set; }
//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
public string sColumns { get; set; }
//array array mixed: aaData - The data in a 2D array
public object aaData { get; set; }

}

///
/// Object to use for call from datatables get
///
public class jqDataTableInput
{

//int: iDisplayStart - Display start point
public int? iDisplayStart { get; set; }
//int: iDisplayLength - Number of records to display
public int? iDisplayLength { get; set; }
//string: string: sSearch - Global search field
public string sSearch { get; set; }
//boolean: bEscapeRegex - Global search is regex or not
public bool? bEscapeRegex { get; set; }
//int: iColumns - Number of columns being displayed (useful for getting individual column search info)
public int? iColumns { get; set; }
//string: sSortable_(int) - Indicator for if a column is flagged as sortable or not on the client-side
public bool?[] bSortable_ { get; set; }
//string: sSearchable_(int) - Indicator for if a column is flagged as searchable or not on the client-side
public bool?[] bSearchable_ { get; set; }
//string: sSearch_(int) - Individual column filter
public string[] sSearch_ { get; set; }
//boolean: bEscapeRegex_(int) - Individual column filter is regex or not
public bool?[] bEscapeRegex_ { get; set; }
//int: iSortingCols - Number of columns to sort on
public int? iSortingCols { get; set; }
//int: iSortCol_(int) - Column being sorted on (you will need to decode this number for your database)
public int?[] iSortCol_ { get; set; }
//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)
public string[] sSortDir_ { get; set; }
//string: sEcho - Information for DataTables to use for rendering
public int? sEcho { get; set; }


}
[/code]

A few things to note.
- nullable values were used to account for any model binding errors.
- arrays are used to stack the values, like columns for search

Patrick

pwc1011

Next, we'll add the custom model binder to dynamically handle the number of columns...

[code]
public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
{

jqDataTableInput _input = new jqDataTableInput();
ValueProviderResult vResult;

// can't be null
if (bindingContext == null)
{
throw new ArgumentNullException("bindingContext");
}
// can't be null
if (controllerContext == null)
{
throw new ArgumentNullException("controllerContext");
}

// can't be null
_input.iColumns = GetValue(bindingContext, "iColumns");
if (_input.iColumns == null) {
//throw new ArgumentNullException("iColumns");
}

// can't be null
_input.sEcho = GetValue(bindingContext, "sEcho");
if (_input.sEcho == null) {
//throw new ArgumentNullException("sEcho");
}



_input.bEscapeRegex = GetValue(bindingContext, "bEscapeRegex");

_input.iSortingCols = GetValue(bindingContext, "iSortingCols");

bindingContext.ValueProvider.TryGetValue("sString", out vResult);
if (vResult != null )
{
_input.sSearch = (string)vResult.ConvertTo(typeof(String));
}


_input.iDisplayLength = GetValue(bindingContext, "iDisplayLength");

_input.iDisplayStart = GetValue(bindingContext, "iDisplayStart");


// create array for sort values based on column count
if (_input.iColumns != null)
{

_input.iSortCol_ = new int?[(int)_input.iColumns];
_input.sSearch_ = new string[(int)_input.iColumns];
_input.bSearchable_ = new bool?[(int)_input.iColumns];
_input.bSortable_ = new bool?[(int)_input.iColumns];
_input.sSortDir_ = new string[(int)_input.iColumns];
_input.bEscapeRegex_ = new bool?[(int)_input.iColumns];

// get results based on column number in name
// zero based array list
int i = 0;
while (i < _input.iColumns)
{
bindingContext.ValueProvider.TryGetValue("sSearch_" + i.ToString(), out vResult);
if (vResult != null)
{
_input.sSearch_[i] = (String)vResult.ConvertTo(typeof(String));
}

bindingContext.ValueProvider.TryGetValue("sSortDir_" + i.ToString(), out vResult);
if (vResult != null)
{
_input.sSortDir_[i] = (String)vResult.ConvertTo(typeof(String));
}
_input.iSortCol_[i] = GetValue(bindingContext, "iSortCol_" + i.ToString());
_input.bSearchable_[i] = GetValue(bindingContext, "bSearchable_" + i.ToString());
_input.bSortable_[i] = GetValue(bindingContext, "bSortable_" + i.ToString());
_input.bEscapeRegex_[i] = GetValue(bindingContext, "bEscapeRegex_" + i.ToString());

i++;
}

}

return _input;
}

// get processing value
private Nullable GetValue(ModelBindingContext bindingContext, string key) where T : struct
{
ValueProviderResult valueResult;
bindingContext.ValueProvider.TryGetValue(key, out valueResult);
if (valueResult == null)
return null;
else
return (Nullable)valueResult.ConvertTo(typeof(T));
}
}
[/code]

register in the gobal asax if you want it to automatically bind to any input with jqDataTableInput

[code]
protected void Application_Start()
{
RegisterRoutes(RouteTable.Routes);
ModelBinders.Binders[typeof(DateTime)] = new DateAndTimeModelBinder() { Date = "Date", Time = "Time" };
ModelBinders.Binders.Add(typeof(jqDataTableInput),new jqDataTablesModelBinder());
}
[/code]

And finally, in your controller...

[code]
[HttpGet]
public ActionResult EntityListData(jqDataTableInput _input)
{

IndividualRepository _ind = new IndividualRepository();
jqDataTablesResult _output = new jqDataTablesResult();

TryUpdateModel(_input);

if (ModelState.IsValid)
{

.. .do your filtering... sorting... etc...

}

return Json(_output, JsonRequestBehavior.AllowGet);
}
[/code]

Enjoy

Patrick

pwc1011

Here is a post version with a sort example. Post is the preferred method.

[code]
[NoCache]
[HttpPost]
public ActionResult EntityListData(jqDataTableInput _input)
{
IndividualRepository _ind = new IndividualRepository();
jqDataTablesResult _output = new jqDataTablesResult();

if (ModelState.IsValid)
{
// used for security
_output.sEcho = (int)_input.sEcho;

string[] cols = new string[] { "Details", "AgeInt", "NameStr", "Id" };

// create list for query and list for string results
string colSlct = "new ( \"\" as Details, AgeInt.ToString() as AgeInt, NameStr.ToString() as NameStr, 1 as ID )";
// where statement
string whereStmt = "1=1";

string ordrStmt = "NameStr Asc";

// result sort...
int i = 0;
if (_input.iSortingCols != null)
{
ordrStmt = "";

while (i < _input.iSortingCols)
{

ordrStmt = cols[(int)_input.iSortCol_[i]] + " " + (_input.sSortDir_[i] == null ? "DESC" : _input.sSortDir_[i].Substring(0,1).ToUpper() == "A" ? "ASC" : "DESC") + ", ";
i++;
}

// remove following comma
ordrStmt = ordrStmt.Remove(ordrStmt.Length - 2);
}
else
{
ordrStmt = "1 ASC";
}

_output.aaData = _ind.Individuals
.Where(whereStmt)
.OrderBy(ordrStmt)
.Select(colSlct).ToStringArray();

}

return Json(_output, JsonRequestBehavior.AllowGet);
}
[/code]

allan

Hi Patrick,

Nice one - thank you :-). When I get myself sorted with the server-side scripting gallery I'll be in touch if that's okay :-)

Regards,
Allan

pwc1011

Of course! I look forward to it.

Patrick

justSteve

Thanks for the code & comments Patrick. I'm most interested in your comment:

register in the gobal asax if you want it to automatically bind to any input with jqDataTableInput

Could i get you to expand that and perhaps point to a demo? what context of 'input' are we talking here?

thx again

EDIT: I've come to a better understanding of my original question that i'll draft here. When the ModelBinder is defined in Global.asax any action with parameter of type jqDataTablesResult - data is filled automatically from the available request parameters ('input').

Advantage being clearer (and less repetitive) code in the controllers.

yes?

and now a different followup...

Have you implemented any nested (AKA parent/child - master/detail - subtable) tables? if so, from a back-end point of view, are you treating the subtable any differently than the master?

again...thx. over the next couple of days i hope to have some of my own code whipped into a presentable shape for comparison purposes - your ideas have certainly helped.

pwc1011

Hello justSteve,

Yes, regarding the model binding, if it is to be used each time the input has jqDataTableInput, why bother with defining it specifically each time. It also prevents errors and forgetting :-)

I have not done master/detail sub-tables, but have done master detail, in the sense that I show a detail row below the table row when they click on a button.

As far as your question, I don't see why I would treat the sub-table any differently. It would just show it in a row within the master table.

if you're interested, here's some additional sample code taking into account more options in linq...

[code]

[HttpPost]
public ActionResult EntityListData(jqDataTableInput _input)
{
webtestdbDataContext db = new webtestdbDataContext();
jqDataTablesResult _output = new jqDataTablesResult();

if (ModelState.IsValid)
{
// used for security
_output.sEcho = (int)_input.sEcho;

string[] cols = new string[] { "Details", "NameStr", "AgeInt", "Id" };

// create list for query and list for string results
string colSlct = "new ( \"\" as Details, NameStr, AgeInt, Id )";
// where statement
string whereStmt = "1=1";

string ordrStmt = "NameStr Asc";

// result sort...
int i = 0;
if (_input.iSortingCols != null)
{
ordrStmt = "";

while (i < _input.iSortingCols)
{

ordrStmt = cols[(int)_input.iSortCol_[i]] + " " + (_input.sSortDir_[i] == null ? "DESC" : _input.sSortDir_[i].Substring(0,1).ToUpper() == "A" ? "ASC" : "DESC") + ", ";
i++;
}

// remove following comma
ordrStmt = ordrStmt.Remove(ordrStmt.Length - 2);

}
else
{
ordrStmt = "1 ASC";
}


// total count
// get count after filter;

_output.iTotalRecords = db.Individuals.Where(whereStmt).Count();
_output.iTotalDisplayRecords = _output.iTotalRecords;
_output.sColumns = "Details, NameStr, AgeInt, Id";

// start the final query
var query = from a in db.Individuals
select a;

// using skip and take for paging
var q = (from a in query
.Where(whereStmt)
.OrderBy(ordrStmt)
select a).Skip((int)_input.iDisplayStart).Take((int)_input.iDisplayLength).Select(colSlct);

_output.aaData = q.ToStringArray();

}

return Json(_output, JsonRequestBehavior.AllowGet);
}

[/code]

Patrick