What will you need?
Please review my original post for the basics of what you will need. This includes a familiarity with ASP.NET MVC, jQuery, and jQuery UI. You should also have the DataTables plugin. You can refer to the Google Code CDN to load your jQuery and jQuery UI code and jQuery UI theme, http://code.google.com/apis/libraries/, or reference them locally. If you’re using a custom theme, you’ll want to download that and use it locally. From the DataTables plugin, you need jquery.dataTables.js (or jquery.dataTables.min.js or both) and the jquery.dataTables_themeroller.css files. Put the JS files in scripts\ and the CSS files in content\. Make sure you also add the following lines to Site.css or whatever you use for your global CSS styles.
.right, .css_right {
float: right;
}
.left, .css_left {
float: left;
}Layout
We will use the same _Layout.cshtml file as we used in the previous example.
<!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <title>@ViewBag.Title</title> <link href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/redmond/jquery-ui.css" rel="stylesheet" type="text/css" /> <link href="@Url.Content( "~/content/jquery.dataTables_themeroller.css" )" type="text/css" rel="stylesheet" media="screen" /> <link href="@Url.Content( "~/content/site.css" )" rel="stylesheet" type="text/css" /> </head> <body> <div class="page"> <header> @Html.Partial( "_Header" ) </header> <section id="main"> @RenderBody() </section> <footer> @Html.Partial( "_Footer" ); </footer> </div> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7/jquery.min.js" type="text/javascript"></script> <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js" type="text/javascript"></script> <script src="@Url.Content( "~/scripts/modernizr-2.5.2.js" )" type="text/javascript"></script> <script type="text/javascript"> $(function () { $('button,.button').button(); }); </script> @RenderSection("scripts",false) </body> </html>
Notes on the Layout (repeated from earlier post)
Note that this example uses jQuery 1.7 and jQuery UI 1.8. These, as well as the jQuery UI theme (Redmond), are being downloaded from the Google Code CDN. To choose a different theme, simply substitute the theme name for redmond in the theme’s CSS link. All of the themes I’ve tried from the ThemeRollery gallery seem to be hosted there, but I can’t guarantee it. If you are using a custom theme, which you’ve downloaded locally, replace the theme link with a reference to your custom theme, typically located in Content\themes\<theme-name>. Note also, I’ve got an optional section named, scripts, that is being included AFTER the jQuery and jQuery UI scripts. This will allow me to add a scripts section to a view so that I can add the DataTables script to just the pages that need it as well as any custom JavaScript that applies to just that view, while at the same time ensuring that those scripts aren’t executed until jQuery is loaded.The AJAX Solution
The AJAX solution uses a single view and two actions. The view will contain the basic table layout, including the header but no data rows. In our simple example, we don’t have any data to pass to this view so it isn’t strongly-typed. In general, you will likely have other elements on the page that need data and will use a view model for this view to deliver both that data any, optionally, any configuration information for the DataTable plugin. To keep things simple, we’ll be configuring the plugin statically in the view. The view will, however, be specific to the JSON data that our other action will be delivering. The DataTable plugin is particular about the format of this data. I found jQuery DataTables and ASP.NET MVC Integration - Part I to be a valuable resource in getting the data right.@{
ViewBag.Title = "Members";
}
<h2>
Members</h2>
<table class="grid">
<thead>
<tr>
<th></th> @* place holder for link to details *@
<th>Name</th>
<th>Job Title</th>
<th>Institution</th>
<th>Interests</th>
<th>Registered</th>
</tr>
</thead>
</table>
@section scripts
{
<script type="text/javascript" src="@Url.Content( "~/scripts/jquery.dataTables.min.js" )"></script>
<script type="text/javascript">
$(function () {
$('table.grid').dataTable({
"bJQueryUI": true,
"bServerSide": true,
"sAjaxSource": '@Url.Action( "MemberList" )',
"bProcessing": true,
"bDeferRender": true,
"aaSorting": [[1, "asc"]],
"sPaginationType": "full_numbers",
"aoColumns": [
{
"sName": "ID",
"bSearchable": false,
"bVisible" : false,
"bSortable" : false
},
{
"sName": "Name",
"sWidth": "30em",
"bSearchable": true,
"fnRender": function(obj) {
var text = obj.aData[obj.iDataColumn].split(/>\s*</);
return text[0] + '> <a href="@Url.Action("details","home")/' + obj.aData[0] + '" title="Details"><' + text[1] + '</a>';
}
},
{
"sName": "Job Title",
"bSearchable": true,
"sWidth": "25em"
},
{
"sName": "Institution",
"bSearchable": true,
"sWidth": "25em"
},
{
"sName": "Interests",
"sWidth": "30em",
"bSearchable": true,
"bSortable": false,
"fnRender": function (obj) {
var tags = obj.aData[obj.iDataColumn].split(/\s*,\s*/),
html = '';
for (var i = 0, len = tags.length; i < len; ++i) {
var tag = tags[i];
if (tag) {
html += '<a href="@Url.Action( "tag", "home" )/' + encodeURIComponent(tag) + '" class="tag-link">' + tag + '</a>';
}
}
return html;
}
},
{
"sName" : "Registered",
"bSearchable": false
}
]
});
});
</script>
}One thing you’ll notice is that the DataTables set up is much more complex than its non-AJAX counter part. In addition to setting ”bJQueryUI” : true, we’ve added some AJAX-specific options. See http://www.datatables.net/ref for a list of all the DataTables options.
"bServerSide": true,
"sAjaxSource": '@Url.Action( "MemberList" )',
"bProcessing": true,
These tell the DataTables plugin to process the data on the server (sorting, filtering, etc.), to use the specified action as the source for the JSON data, and to show a “processing” message while the data is being retrieved. Notice that we use the UrlHelper to set up the action that will will use instead of hard-coding a path. This will keep our URLs independent of the current action and web application setup (top-level or sub-site). It’s good to get into the habit of using the helpers (Url and Html) to generate the URLs and links you need in your views.
Additionally, we’ve added an option, bDeferRender, to defer the creation of the table elements for each row until that row is rendered. This will help speed things up.
The largest amount of complexity comes in the column set up. Some of this could also have been added (searchability, sortability, sizing) for the previous solution, too. Sizing, though, seems to be more critical in an AJAX setting as the table will need to adjust to the varying data otherwise. When the data is delivered all at once, the plugin does a good job of dynamically assigning width. In my experience, when the data is delivered in pieces it does less well. In addition to affecting the UI, the search/sort parameters will also affect the data that is sent back to the MemberList action.
The most interesting bits are the additional column, which is hidden, and the render functions for the Name and Interests columns. In the previous example, I created the link using the id column directly. In this example, I don’t have access to the HtmlHelper when the JSON is delivered so I’m constructing the link using JavaScript. Note that the code which does this make use of the UrlHelper when constructing the code, but it is invoked when the view is rendered, not when the data is delivered. I’m doing it slightly differently here, as I made a change in how my table is being displayed. In this case, I’m delivering not just the Name but an image (Gravatar) and Name that will display in the Name column. I’m wrapping the member’s name to form the link to the details rather than have a column with a Details link. The code takes the data, extracts both the current column’s data and the id from the first column, then builds the link from the result of separating the image from the text and wrapping the text in an anchor tag.
Similarly, with the Interests column, I’ve decided to wrap each interest (really a tag) as a link. They’re delivered as a comma-separated list of tags as a string. This string is split and the resulting tags formed into links using the tag as both the “id” for the link and the link text.
The Model
The model that we are using will be significantly different than the previous model. We will re-use the simple MemberModel from our previous example, but only as an intermediate model. The DataTable plugin requires a JSON model in a specific format. There is some variability allowed, but it’s simplest to leave things as they are expected and adapt to that I think. We will need two separate models, one to accept the parameters that the DataTable plugin will send and one to deliver. The former needs to be a class that can be bound to. The latter will be an anonymous type constructed in our action. The parameter model (adapted and renamed from DataTables and ASP.NET MVC Integration - Part I) is below. It corresponds to the parameter data sent by the DataTables plugin in its AJAX request. By default this will be a GET request, so you’ll need to make sure that your action will allow delivery of JSON data for a GET request.
/// <summary> /// Class that encapsulates most common parameters sent by DataTables plugin /// </summary> public class DataTableParameterModel { public DataTableParameterModel() { iDisplayStart = 0; iDisplayLength = 20; sColumns = ""; sSearch = ""; } /// <summary> /// Request sequence number sent by DataTable, /// same value must be returned in response /// </summary> public string sEcho { get; set; } /// <summary> /// Text used for filtering /// </summary> public string sSearch { get; set; } /// <summary> /// An array of searchabilty flags, one per column /// </summary> public bool[] bSearchable { get; set; } /// <summary> /// Number of records that should be shown in table /// </summary> public int iDisplayLength { get; set; } /// <summary> /// First record that should be shown(used for paging) /// </summary> public int iDisplayStart { get; set; } /// <summary> /// Number of columns in table /// </summary> public int iColumns { get; set; } /// <summary> /// Number of columns that are used in sorting /// </summary> public int iSortingCols { get; set; } /// <summary> /// Array of sortability flags, one per column /// </summary> public bool[] bSortable { get; set; } /// <summary> /// Array of column ids to sort, one per sort column. The size of this array is iSortingCols /// </summary> public int[] iSortCol { get; set; } /// <summary> /// The direction of each sort, one per sort column. The size of this array is iSortingCols /// </summary> public string[] sSortDir { get; set; } /// <summary> /// Comma separated list of column names /// </summary> public string sColumns { get; set; } }
In investigating this, with the help of the aforementioned article, I found that some of the parameters are passed in non-standard ways. For example, iSortCol is passed as iSortCol_
public class DataTableParameterModelBinder : IModelBinder { public object BindModel( ControllerContext controllerContext, ModelBindingContext bindingContext ) { try { if (bindingContext.ModelType.IsAssignableFrom( typeof( DataTableParameterModel ) )) { var model = new DataTableParameterModel(); model.iColumns = (int)bindingContext.ValueProvider.GetValue( "iColumns" ).ConvertTo( typeof( int ) ); model.iDisplayLength = (int)bindingContext.ValueProvider.GetValue( "iDisplayLength" ).ConvertTo( typeof( int ) ); model.iDisplayStart = (int)bindingContext.ValueProvider.GetValue( "iDisplayStart" ).ConvertTo( typeof( int ) ); model.iSortingCols = (int)bindingContext.ValueProvider.GetValue( "iSortingCols" ).ConvertTo( typeof( int ) ); model.sEcho = bindingContext.ValueProvider.GetValue( "sEcho" ).AttemptedValue; model.sColumns = bindingContext.ValueProvider.GetValue( "sColumns" ).AttemptedValue; model.sSearch = bindingContext.ValueProvider.GetValue( "sSearch" ).AttemptedValue; model.bSearchable = new bool[model.iColumns]; model.bSortable = new bool[model.iColumns]; model.iSortCol = new int[model.iSortingCols]; model.sSortDir = new string[model.iSortingCols]; for (int i = 0; i < model.iColumns; ++i) { model.bSearchable[i] = (bool)bindingContext.ValueProvider.GetValue( "bSearchable_" + i ).ConvertTo( typeof( bool ) ); model.bSortable[i] = (bool)bindingContext.ValueProvider.GetValue( "bSortable_" + i ).ConvertTo( typeof( bool ) ); } for (int i = 0; i < model.iSortingCols; ++i) { model.iSortCol[i] = (int)bindingContext.ValueProvider.GetValue( "iSortCol_" + i ).ConvertTo( typeof( int ) ); model.sSortDir[i] = bindingContext.ValueProvider.GetValue( "sSortDir_" + i ).AttemptedValue; } return model; } } catch { } return null; } }
Register this model binder in the Application_Start() method global.asax.cs.
ModelBinders.Binders.Add( typeof( DataTableParameterModel ), new DataTableParameterModelBinder() );
The Actions
In my case the first action is very simple. I’m just going to deliver the view, which doesn’t need any extra data and, thus, no model.
[Authorize] [HttpGet] public ActionResult Members() { return View(); }
The view that delivers the data is more complicated. In this case, I’ll show an actual query so that you can see how the searching, sorting, and paging is handled based on the input model. This example uses a RavenDB query, which I’m playing with at the moment, but it could be any strongly typed IQueryable. The basic process is filter –> sort –> select results.
The first thing we want do to is implement any search filters. I’ve implemented a simple "StartsWith” search for a few of the text fields and a similar search for any tag that “StartsWith” the search value. We only perform the search if a search value is supplied. By constructing the query in pieces we keep the query as simple as possible to do the job. Note that RavenDB allows for more complex searches than I’ve shown. You’d probably want to implement some sort of full text (Lucene, in this case) search if the simple search isn’t sufficient.
[Authorize] public ActionResult MemberList( DataTableParameterModel parameterModel ) { parameterModel = parameterModel ?? new DataTableParameterModel(); RavenQueryStatistics stats; var baseQuery = DBSession.Query<Member>().Statistics( out stats ); if (!string.IsNullOrWhiteSpace( parameterModel.sSearch )) { baseQuery = baseQuery.Where( m => m.Profile.Name.StartsWith( parameterModel.sSearch ) || m.Profile.Institution.StartsWith( parameterModel.sSearch ) || m.Profile.JobTitle.StartsWith( parameterModel.sSearch ) || m.Profile.Interests.Any( i => i.StartsWith( parameterModel.sSearch ) ) ); } var query = CreateSortedQuery( parameterModel, baseQuery ); var members = GetPagedResults( query, parameterModel.iDisplayStart, parameterModel.iDisplayLength ); var model = new { sEcho = parameterModel.sEcho, iTotalRecords = stats.TotalResults, iTotalDisplayRecords = members.Count(), aaData = new List<string[]>() }; foreach (var member in members) { model.aaData.Add( new string[] { member.Id.ToString(), member.Identity, member.JobTitle, member.Institution, member.Interests, member.MemberSince } ); } return Json( model, JsonRequestBehavior.AllowGet ); }
Next, we handle sorting. We could sort before filtering, but that isn’t the most efficient from a conceptual standpoint. Note that we’re actually just extending the query. Most LINQ providers will defer this to the the data store, constructing an expression tree instead of actually performing the query. Typically you’d want to first limit the amount of data that you want to sort to reduce the sorting time. Using LINQ as we are, most providers will make a translation to their internal selection logic that optimizes the actual process, but it’s better not to make that assumption and construct the query as if it were going to be executed exactly as we’ve defined it. We delegate the construction of the sort query to a separate method to promote readability. Note the special handling when we have multiple sort columns. The first column is sorted using OrderBy, but subsequent columns are sorted using ThenBy. The actual sort expression is constructed using our (simple) logic based on a fixed mapping between our columns and the column number reported. A more robust solution might look up the column name using the index and translate that into property selection expression using reflection, but we’ll defer that until we actually need it.
/// <summary> /// Create a (possibly) compound sorting expression based on the sort columns and directions contained in /// the parameter model. /// </summary> /// <param name="parameterModel">The parameters from the DataTable plugin</param> /// <param name="baseQuery">An existing query for the model, potentially including search parameters.</param> /// <returns>An ordered query based on the model</returns> private IOrderedQueryable<Member> CreateSortedQuery( DataTableParameterModel parameterModel, IQueryable<Member> baseQuery ) { var orderedQuery = (IOrderedQueryable<Member>)baseQuery; for (int i = 0; i < parameterModel.iSortingCols; ++i) { var ascending = string.Equals( "asc", parameterModel.sSortDir[i], StringComparison.OrdinalIgnoreCase ); int sortCol = parameterModel.iSortCol[i]; Expression<Func<Member, string>> orderByExpression = GetOrderByExpression( sortCol ); if (ascending) { orderedQuery = (i == 0) ? orderedQuery.OrderBy( orderByExpression ) : orderedQuery.ThenBy( orderByExpression ); } else { orderedQuery = (i == 0) ? orderedQuery.OrderByDescending( orderByExpression ) : orderedQuery.ThenByDescending( orderByExpression ); } } return orderedQuery; } /// <summary> /// Provides a fixed translation between column number and orderby expression. /// A better solution would be to use the column list with reflection /// to find the given property on the model based on the sort column index. /// </summary> /// <param name="column">The column index to use for the sort expression.</param> /// <returns></returns> private Expression<Func<Member, string>> GetOrderByExpression( int column ) { Expression<Func<Member, string>> orderBy = m => m.Profile.Name; switch (column) { case 5: orderBy = m => m.MemberSince.ToString( "yyyyMMddHHmmss" ); break; case 3: orderBy = m => m.Profile.Institution; break; case 2: orderBy = m => m.Profile.JobTitle; break; case 1: default: break; } return orderBy; }
Once our collection is filtered and sorted, we can then get the page of results that we need. Here is one area where the DataTable plugin actually helps us because it tells us which record in the set to start with rather than the page. We can directly use this value to skip that number of records in our collection and take a page’s worth of records from the collection after that point. We do this, obviously, with the Skip and Take extension methods. Once we have this list, we realize the query using ToList() and then transform the entities into our intermediate model. Later we’ll turn the results in the intermediate model into an array of arrays of strings that is actually used by the DataTable plugin.
Again, we’ll use a separate method to convert the results into a set of paged, intermediate results that we can transform into our output model. This method will use the GravatarHelper (note I’ve done a bit of refactoring on the code that it inserts to provide a method that returns a string not an MvcHtmlString, so that the ampersands aren’t encoded in the JSON). It also does some transformations from the entity to the format that we want to consume in our model by transforming the tags into a comma-separated list and doing the DateTime to string conversion (you could use a helper here to enforce business rules about date display if you wanted). It also flattens the properties in the entity model to make it easier to convert into our output format.
/// <summary> /// Delivers one page of results as a collection of MemberModel objects. The identity is constructed as a /// a gravatar image and a span containing the user's display name so that the UI can wrap the name as /// a link to the user's details. Interests are constructed as a comma-separated string of tags. /// </summary> /// <param name="query">The filtered and sorted query.</param> /// <param name="skipRecords">The number of records to skip.</param> /// <param name="pageSize">The maximum number of records to return</param> /// <returns>A collection of MemberModel objects.</returns> private IEnumerable<MemberModel> GetPagedResults( IOrderedQueryable<Member> query, int skipRecords, int pageSize ) { var members = query.Skip( skipRecords ) .Take( pageSize ) .ToList() .Select( m => new MemberModel { Id = m.Id, Email = m.Profile.Contacts.FirstOrDefault( c => c.Type == "Email" ).Info, Identity = GravatarHelper.GravatarImageString( m.Profile.Contacts.FirstOrDefault( c => c.Type == "Email" ).Info, 48, cssClass: "gravatar" ) + " <span>" + m.Profile.Name + "</span>", Institution = m.Profile.Institution, Interests = string.Join( ", ", m.Profile.Interests ), JobTitle = m.Profile.JobTitle, MemberSince = m.MemberSince.ToShortDateString() } ); return members; }
The Output Model
Now that we have our results filtered, sorted, paged, and flattened, we now need to transform it into a model that can be turned into the JSON that DataTables needs. The DataTable plugin has a specific format required for its JSON data, included particular named fields. We’ll use an anonymous object to hold this and return this object as a JSON result to take advantage of the framework’s ability to generate JSON. The basic model format for DataTables looks like the following:
{
"sEcho"span>:"1",
"iTotalRecords":97,
"iTotalDisplayRecords":3,
"aaData":[
["1","Joe Smith","University of Iowa","MVC, .NET", "Developer", "2/12/2012" ],
["1","Jane Doe","University of Iowa","Agile Methods", "Manager", "2/22/2012" ],
["1","Rick Astley","Iowa State University","Never Gonna Give You Up", "Singer", "11/6/1987" ],
]
}Here is how we tranform our collection of results into the required model that will be transformed into this JSON format. Note the use of the RavenDB statistics to get the total number of records available. If your DB doesn't have this capability, you'll want to calculate this from your partial query prior to doing the selection step, say using Count(). The sEcho value is a sequence number that needs to be the same as what is provided in the input model. The other values are calculated from the query results. Notice that our intermediate model is flattened into a array of generic strings with the specific name, aadata.
var model = new { sEcho = parameterModel.sEcho, iTotalRecords = stats.TotalResults, iTotalDisplayRecords = members.Count(), aaData = new List<string[]>() }; foreach (var member in members) { model.aaData.Add( new string[] { member.Id.ToString(), member.Identity, member.JobTitle, member.Institution, member.Interests, member.MemberSince } ); }
Finally, we return a JSONResult of the model, remembering to allow JSON returns for GET requests.
return Json( model, JsonRequestBehavior.AllowGet );
Very Good article. Exactly what I needed. Thank you so much..
ReplyDeleteSo you have the solution / source code as well?
Unfortunately it's not possible to release the code in its entirety.
DeleteThanks so much Tim! I've been trying to solve this very issue with LINQ and DataTables.net.
ReplyDeleteThis has been a learning curve issue for me for the past three days. From Arrrggh to Yeah!
Thanks for being of service. My project can now be wrapped up.