Thursday, February 23, 2012

MVC, jQuery UI, and the DataTable plugin–AJAX edition

In my previous post I walked you through a simple example of using the DataTable plugin with an MVC web application in a non-AJAX setting. One of the main difficulties with that approach arises when the amount of data is large. This is the case more often than not and when it is, delivering the data via AJAX is often the most elegant solution to the problem. I find that the AJAX approach works best with two actions to support it, rather than the single action used in the non-AJAX setting. The first action will deliver the view, including the JavaScript code to retrieve the data. The second action, which will be called by the DataTable plugin code, will deliver the data via JSON.

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_ where num corresponds to the index of the item (not its value). The standard MVC model binder doesn't know how to bind these types of properties. In order to address this, I created a custom model binder for the parameter model type.  There’s a fair amount of string “magic” in the binder code that should probably be replaced with string constants or a configuration-driven mechanism.  This is one area where some refactoring might be in order.

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

Conclusion

This completes the AJAX version of the MVC/jQueryUI/DataTable integration. You've seen how to define your actions/views to deliver one view which contains the basic page layout and invokes the other action which delivers the data in a format that DataTables can consume. The backend code understands the filtering/sorting/paging data that DataTables. It transforms it into a valid model class using a custom model binder. The action uses this model, constructs and performs the query, then formats into the JSON object that DataTables requires. Now when the page loads, DataTables will fire off a request for the first page of data. As you click on the interface elements DataTables will perform the appropriate requests and display the data requested within the plugin's structure.

Tuesday, February 21, 2012

MVC, jQuery UI, and the DataTable plugin

I hate starting with disclaimers, but let’s just get this out of the way. There aren’t any grid plugins, or free ones anyway, that I’m really enamored of. Maybe, it’s just not possible, but all of the ones I’ve tried have WAY too many options, are hard to configure, and even hard to style properly. That seems especially true if you want to use jQuery UI themes for styling. I’ve tried jqGrid, Flexigrid, and several others, but the one that I’ve had the most overall luck with is DataTables. Probably the biggest reason is that I think it best works with jQuery themes. That probably reflects my bent as a developer instead of a designer. It’s simply easier to bend the code to my will than the CSS. Because DataTables requires the least lifting with respect to adding in jQuery UI themes, I’ve settled on it.
Before I go any further, I want to say that I hate (or, maybe HATE) the use of Hungarian notation in the DataTables configuration. I understand it, I just dislike it with a vengeance. It causes enough readability problems that it almost disqualifies it in my mind. Worse, I feel compelled to adapt to it in my models rather than take on the somewhat daunting task of modifying it to my preferences since I don’t feel like maintaining those changes across versions and I don’t want to introduce more confusion by changing the names of the bound properties.


What will you need?

Before we get started, I’m going to assume that you know how to create or have an existing ASP.NET MVC project. This tutorial won’t make much sense if you don’t. If you’re not somewhat familiar with ASP.NET MVC, I suggest you start with "The Book" and its example, the Nerd Dinner. You should also be familiar with jQuery and jQuery UI. We’re actually going to be interested in using jQuery UI themes (http://jqueryui.com/themeroller/), but it really only makes sense to do so if you’re already using jQuery UI for other widgets (buttons, tabs, accordions, dialogs, etc.).  Some familiarity with writing pure JavaScript will eventually come in handy, too. Not everything is a plugin! Lastly, you’ll need to download a copy of the DataTables code, http://www.datatables.net/download/. Extract the ZIP file into a folder. I will be using version 1.9.0 in this tutorial.

Let’s Start

Before we go any further, copy jquery.dataTables.js and jquery.dataTables.min.js from the folder you created when you extracted the ZIP file to the Scripts folder in your MVC project. You can find these in the media\js folder. Copy the jquery.dataTables_themeroller.css from media\css to your Content folder. You will also need to add the following two CSS definitions to your Site.css file (or whatever you use for global styles). The css_right and css_left classes are used by the DataTables plugin to position the sorting icons, but the classes aren’t included in the CSS file.   There are several other CSS and JS files.  These were the only ones that I found I needed and should be all that are required for this tutorial. These instructions don’t address any DataTable plugins that you may need; I’m not using any.
.right, .css_right {
    float: right;
}

.left, .css_left {
    float: left;
}

Now, let’s create your _Layout.cshtml file.  The important parts are including the relevant scripts and CSS.  Generally , you want your CSS in the header and your scripts right before the end of the body tag to make sure that your page loads as fast as it can.  Most page elements that require additional requests can load in parallel, but scripts generally don’t (some browsers support extensions that allow this, but you shouldn’t count on that yet).  Here’s a sample layout for a simple project using DataTables.  Note that the header and footer elements are provided via partial views, which I haven’t included.

<!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>
    @RenderSection("scripts",false)
</body>
</html>


A Couple of Notes


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.

A Non-AJAX Solution


You have a choice: AJAX or non-AJAX.  Which you choose will determine how you structure your view and your controller actions.  If you choose a non-AJAX solution, you can live with a single view and a single controller action to render that view.  Your model will contain the data to render a table and you will simply apply the DataTables plugin to the table.  For small tables that’s fine; I’ve used it that way many times and it works pretty easily.  My next article will expand on this and create an AJAX solution.

Here’s a simple view that uses the non-AJAX approach.  We’re displaying a list of web site members.  The things we want to display are  a link to the member’s details and the member’s display name, job title, the institution they work for, a list of their interests, and the date when they registered for our site. I like to start with the view since it tells me what data I’ll need in my model:  View –> Model –> Controller works pretty well for this particular application.

@{
    ViewBag.Title = "Members";
}
@model IEnumerable<MemberModel>
<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>
        <tbody>
            @foreach (var member in Model)
            {
                <tr>
                    <td>@Html.ActionLink( "Details", "detail", "member", new { id = member.Id }, null ) </td>
                    <td>@member.Identity</td>
                    <td>@member.JobTitle</td>
                    <td>@member.Institution</td>
                    <td>@member.Interests</td>
                    <td>@member.MemberSince</td>
                </tr>
            }
        </tbody>
    </table>

@section scripts
{
    <script type="text/javascript" src="@Url.Content( "~/scripts/jquery.dataTables.min.js" )"></script>
    <script type="text/javascript">
        $('table.grid').dataTable({
            "bJQueryUI": true, // render using jQuery UI theme
            "aaSorting": [[1,"asc"]], // default sort on column 1 (second column)
            "sPagination": "full_numbers"
        });
    </script>
}

Note how we're including the DataTables javascript and our table setup in the scripts section in this view. The framework will place these in the scripts placeholder in our layout when the view is rendered. This way the code need only be present on this page where it is used, not everywhere. We could do something similar with the CSS, but I wanted to always have an exact ordering for the CSS and placed it all in the layout directly.


The key setting in our DataTable options is "bJQueryUI": true. This tells the DataTable plugin to add in jQuery UI classes when it is rendering and use the theme we've included for styling. Since we've included a link in our first column, we also tell the plugin to order the table by the second column, the user's display name.

The Model


I’ll be using a view-specific model for this view. Typically, you’ll want to provide isolation between your entity models and your views. A view model accomplishes this, providing you with a way to deliver just the data needed in the way that the view requires. My model is relatively simple.If you needed other properties on the page, you might have a model class that contains the collection of this model along with the additional properties. In our case, the view is strongly-typed to the collection itself.


public class MemberModel
{
    public int Id { get; set; }
    public string Identity { get; set; }
    public string JobTitle { get; set; }
    public string Institution { get; set; }
    public string Interests { get; set; }
    public string MemberSince { get; set; }
}

The Action


And, now, we need a way to get the data to the view.  For this example, we’ll simply construct a fixed collection of model elements, though normally you would perform a query against your data store to get a collection of entities (and related objects) that would be flattened into our view model.

[HttpGet]
[Authorize]
public ActionResult Members()
{
    var memberList = new List<MemberModel>
    {
        new MemberModel
        {
            Id = 201, Identity = "Joe Developer", Institution = "University of Iowa",
            Interests = "MVC, jQuery, DataTables", JobTitle = "Developer"
        },
        new MemberModel
        {
            Id = 287, Identity = "Jane Manager", Institution = "Kirkwood Community College",
            Interests = "Agile Methods", JobTitle = "Manager"
        },
        new MemberModel
        {
            Id = 562, Identity = "Daisy Designer", Institution = "University of Iowa",
            Interests = "CSS, HTML5", JobTitle = "Designer"
        }
        // ...
    };
    return View( memberList );
}

Conclusion


With the exception of the Hungarian notation, using DataTables with jQuery UI in a non-AJAX MVC setting is pretty simple as you’ve seen. The drawback to this approach becomes evident when you have lots of data that needs to be rendered in your table. In a non-AJAX mode, the entire table is sent to the client and the DataTable plugin handles paging and sorting entirely on the client-side. For large datasets this can be very slow both in the client and it can bog down the server as well while the request is being prepared.  A better approach is to hook the DataTables plugin up to the server using AJAX.  We’ll cover that in my next article.