February 2011

Volume 26 Number 02

Data Points - Slice and Dice OData with the jQuery DataTables Plug-In

By Julie Lerman | February 2011

The Open Data Protocol (OData) allows data producers to provide their data over the Web in a common format that can be consumed by anyone using an HTTP-enabled technology. Data is provided through URIs and you can use the common HTTP verbs—GET, PUT, POST, MERGE and DELETE—to interact with the data. You can handle this interaction directly through a language like JavaScript or by using a client API such as the Microsoft .NET Framework, Silverlight, PHP or others provided by Microsoft. Either way, you can interact with all OData feeds in the same way.

There are a growing number of publicly provided OData services such as commercial feeds from Netflix Inc. and eBay Inc., World Cup data, even a service that supplies 150 years of baseball statistics.

Accessing data is getting easier and easier, but what about presenting the data? When you have 150 years worth of baseball stats or thousands of movie titles, there’s still some effort on the client side to retrieve and navigate through all of that data.

At a recent Vermont .NET User Group presentation on jQuery, I was inspired by a jQuery plug-in called DataTables as a low-investment way to allow users to slice and dice large amounts of data. The power of DataTables is in its blazingly fast client-side processing, although it does allow you to be more interactive with server-side code if you desire.

JQuery is a client-side Web technology (and can be used in any type of Web application) that simplifies working with JavaScript. If you’ve talked to anyone who’s jumped on the jQuery bandwagon, you’ll find a lot of passion for the technology. DataTables is one of a huge number of jQuery plug-ins. And you can use jQuery in any type of Web application. 

As I happen to do most of my work with the .NET Framework, in this column I’ll demonstrate using some of the basic DataTables plug-in features in applications using both ASP.NET MVC and WebForms. However, the logic in the WebForms app will be driven by client-side code. I’ll be working with the Netflix OData service (https://developer.netflix.com/docs/oData_Catalog), which gives me an opportunity to show you how to deal with some common pitfalls you may encounter when using various OData services.

You can download the DataTables plug-in from datatables.net. If you’re new to consuming OData, you might want to get up to speed by visiting the WCF Data Services section of the MSDN Developer Center at msdn.microsoft.com/data/odata.

Query OData with LINQ and the Client APIs

I’ll begin with a simple MVC application where I’ve added a service reference to https://odata.netflix.com/v1/Catalog using the Visual Studio Add Service Reference wizard. This, in turn, creates proxy classes for me to consume in my application and builds an Entity Data Model based on the service, as shown in Figure 1. The wizard also adds references to the .NET Framework OData client library APIs. Both the .NET Framework and Silverlight OData client libraries make working with OData fairly simple thanks to their support of LINQ querying.

Figure 1 The MVC Project in Solution Explorer

My startup controller, HomeController.cs, uses the OData client library and the service proxy to query for all of the movie titles in a particular genre: Independent. The results of the query are returned to the View associated with this particular controller action:

public ActionResult Index() {
  var svcUri = new Uri("https://odata.netflix.com//v1//Catalog");
  var context = new NetflixOData.NetflixCatalog(svcUri);
  var query = from genre in context.Genres
              where genre.Name == "Independent"
              from title in genre.Titles
              where title.ReleaseYear>=2007
              select title ;
  var titles = query.ToList();             
  return View(titles);
}

The markup in the HomeController Index view (\Views\HomeController\index.aspx) is where all of the interesting presentation logic is being performed. To leverage jQuery and the DataTables plug-in, you’ll need to add a set of script files to your project. Alternatively, you can point to the online set of scripts (see the Microsoft AJAX Content Delivery Network at asp.net/ajaxLibrary/CDN.ashx), but I’ve chosen to host them locally. The download for the DataTables plug-in contains a \media folder (which contains the scripts) that you can drop into your project. You can see I’ve already done this in Figure 1.

Figure 2 contains the code listing of the Index.aspx file.

Figure 2 The HomeController Index.aspx

<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master"
 Inherits="System.Web.Mvc.ViewPage<IEnumerable<Title>>" %>
<%@ Import Namespace="JQueryMVC.Controllers" %>
<%@ Import Namespace="JQueryMVC.NetflixOData" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" 
  runat="server">
    Home Page
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" 
  runat="server">
  <head>
    <link href="../../media/css/demo-table.css" 
      rel="stylesheet" type="text/css" />
    <script src="../../media/js/jquery.js" 
      type="text/javascript"></script>
    <script src="../../media/js/jquery.dataTables.js" 
      type="text/javascript"></script>
    <script type="text/javascript" charset="utf-8">
        $(document).ready(function () {
           $('#Netflix').dataTable();
        });
    </script>
  </head>
  <div>
    <table id="Netflix">
      <thead><tr><th>Title</th>
                 <th>Rating</th>
                 <th>Runtime</th></tr></thead>
      <tbody>
        <% foreach (Title title in Model)
           { %>
             <tr><td><%= title.Name %> </td>
                 <td><%= title.AverageRating %></td>
                 <td><%= title.Runtime %></td></tr>
           <% } %>
      </tbody>
    </table>
  </div>
</asp:Content>

The CSS link and two script sources at the beginning of the <head> section point to CSS formatting and the critical jQuery and jQuery.datatables JavaScript files.

Next, let’s focus on the table as it’s laid out in the page. The DataTables plug-in is dependent on the table’s ID and header information stored in the <thead> section. After this, a bit of code iterates through the IEnumerable<Title> passed in to the View from the HomeController.cs file and displays the Name, AverageRating and Runtime values in the appropriate columns.

When the page initially starts up, the JavaScript method in the header tag uses jQuery to locate the Netflix table in the form and applies the dataTable function to it. DataTables is highly configurable, but with this simple form of calling the dataTable function, the referenced table, Netflix, will acquire the DataTables default configuration. Figure 3 shows the resulting page.

Figure 3 Displaying Data with the DataTables Plug-In

DataTables has done more than use CSS to make the table pretty. Notice that, at the bottom, it tells you that it retrieved 155 rows. By default it does client-side paging starting at 10 entries per page, although the user can choose 25, 50 or 100 entries per page in the drop-down. The Search box filters the results based on its findings in all of the available columns in the table. The user can also click on the header row columns to sort the data. The DataTables plug-in feature set is so rich that there are even plug-ins for the plug-in. You can learn much more about enhancing the default on the datatables.net Web site.

Querying OData on the Client Side

You don’t always have the benefit of working with a client API, so I’ll flip over to the more challenging task of querying the Netflix OData on the client  side without the benefit of one of the other available libraries (the AJAX client library). I’ll employ the DataTables plug-in while getting around some of the limitations posed by the Netflix service. You’re likely to run into these same limitations when working with other public OData services as well.

For this round, I’m using an ASP.NET WebForms app, though I could be using plain old HTML because I’m not using any .NET Framework code in this page. You’ll need the \media folder in this application as well, but you won’t be creating a proxy to the service, so there’s no need to use Add Service Reference.

The dataTable function has a method called sAjaxSource that will automatically retrieve data from the target source. But this requires your results to be formatted in a specific way. OData results don’t align with this. There’s a great blog post written by a California-based developer, Jeff Morris, that demonstrates reshaping OData results in a WCF Data Services query interceptor. You can read the post at bit.ly/bMPzTH.

Instead, I’ll use AJAX to return the OData in its native form and then manually populate the table.

The body of the page starts out with the table and its <theader> defined (again, required by DataTables), as well as an empty <tbody>:

<body>
  <form id="form1" runat="server">
    <table id="Netflix" width="100%">
      <thead>
        <tr><th width="50%">Title</th>
            <th>Rating</th>
            <th>Runtime</th></tr>
      </thead>
      <tbody id="netflixBody"/>
    </table>
  </form>
</body>

The page has a number of functions: GetData, displayResults and a helper function to deal with one of the current shortcomings of the Netflix service. Similar to the .NET Client Library for OData, there’s a client library for AJAX that’s part of the Microsoft ASP.NET AJAX APIs. Here’s an example from the AJAX documentation of what a JavaScript OData query looks like using this library:

function doQuery() {
var northwindService = new
Sys.Data.OpenDataServiceProxy("/Northwind.svc");
northwindService.query("/Customers", cbSuccess, cbFailure, userContext);

Alternatively, you can use straight AJAX and jQuery as I do in the following examples. Let’s look at the beginning of the header script, including the getData function:

<script type="text/javascript" charset="utf-8">
  var oTable;
  var query = "https://odata.netflix.com/v1/Catalog/Titles?$orderby=Name&$top=500"
  $(document).ready(function () { getData() });
  function getData() {
    var url = query + "&$callback= displayResults" 
      + "&$format=json";
    $.ajax({ dataType: "jsonp", url: url });
  }

When the page begins, the document.ready function automatically calls getData. getData constructs a URL from the pre-defined OData query and appends parameters to return the OData as JSON (an alternative to the default AtomPub format), as well as defining the method to be executed when the AJAX call is complete.

When the AJAX call is complete, the displayResults function will be called using the results from the OData query (see Figure 4).

Figure 4 Preparing OData Results for Display

function displayResults(results) {
  var entities;
  var redraw;
// Find data in results 
  if (results.d[0] == undefined) {
    queryNext = results.d.__next;
    entities = results.d.results;
  }
  else {
    queryNext = "";
    entities = results.d;
  }
  // Instantiate dataTable if necessary
  if (oTable ==null)
    oTable = $('#Netflix').dataTable();
  // Build table rows from data using dataTables.Add
  for (var post in entities) {
    if (post == queryResults.length-1)
      redraw = true; //only redraw table on last item
    else
      redraw = false;
    oTable.fnAddData([
      entities[post].Name, entities[post].Rating, 
      entities[post].Runtime],redraw);
  }
  // Continue retrieving results
  if (queryNext > "") {
    query = FixNetFlixUrl(queryNext);
    getData();
  }
}

The section of code that’s commented with “find data in results” is handling one of the Netflix limitations I’ve mentioned. Netflix is enforcing server-side paging to protect its servers and only returns 500 rows per request. Can you imagine if someone lazily queried for all movies? I’m sure that happens frequently. The server-side paging doesn’t prevent you from getting additional rows; you just need to do this explicitly.

Handling large amounts of data in the client is exactly what DataTables is brilliant at, and there’s a good chance you’ll want to take advantage of it. It may take a bit longer to load all of the data when you’re retrieving large quantities (for example, 5,000 rows), but once they’re in memory, DataTables can let the end user do all types of filtering and sorting on that data.

When I first saw DataTables demonstrated, the person showing it off said they were using it for a corporate reporting tool where they were downloading 80,000 rows. I protested loudly to this abuse of the Internet and the server. However, having seen DataTables in action, I’m no longer so opposed to this usage in a controlled scenario.

OData provides a way to easily request another batch of data and Netflix provides this hook for you to take advantage of. Here’s a query that requests 501 results:

https://odata.netflix.com/v1/Catalog/Titles?$orderby=Name&$top=501

When the query exceeds that service’s limit, Netflix uses the OData continuation token feature. In addition to the entries, the results contain one more element after the last entry. Here it is in AtomPub format:

<link rel="next"
  href="https://odata.netflix.com:20000/v1/Catalog/Titles/?$orderby=
Name&$top=1&$skiptoken='1975%20Oklahoma%20National%20Championship%20
Game','BVZUb'" /> 
</feed>

The skiptoken parameter tells the query where to start with the next set of results. In JSON, that entry is seen at the beginning of the results in a property called __next, as shown in Figure 5.

Figure 5 JSON Results of a Request for More Data than the Service Is Configured to Return

When a query doesn’t exceed the limitation, the entries are directly inside the d property, as you can see in Figure 6. That’s why GetData needs to test to see where it will find the results. If there’s a continuation token, it stores that in NextQuery and then executes the continuation query in order to build up the complete result set in memory.

Figure 6 JSON Results for a Request Within the Configured Return Amount

If you look at the __next property, you’ll notice that Netflix added a port number, 20,000, to the query. However, if you execute that query directly, it will fail. So you’ll need to remove the port number from the URI before requesting it. That’s the purpose of the FixNetFlixUrl function that I call prior to making the request.

You’ll have to watch for anomalies such as this when consuming public OData services. You’ve now seen how to deal with a service that limits the number of results it will return and one that inflicts a breaking change in its continuation token.

For each set of results that is retrieved, the method uses the DataTables fnAddData method to add each item to the table. Redrawing the table is expensive, so I’ve set the redraw parameter of fnAddData to false until reaching the last item in the results. Redrawing throughout the data retrieval makes the UI more fluid, rather than waiting until all 5,000 rows have been retrieved and added to the table.

After modifying the initial query to return 5,000 rows in my rural-Vermont-with-poky-Internet-access environment, and postponing the redraw to the bitter end, it took nearly a minute to capture all of the rows and display the table. Redrawing every row was much snappier and I was able to interact with the table even as more rows were being added. That was a nice surprise.

Once all 5,000 rows were in the table, DataTables did an amazing job of sorting and searching. Sorting took less than one second. Search was instantaneous, as it responds to each keystroke in the search box (see Figure 7).

Figure 7 Real-Time Search Results in DataTables

A Small Adjustment for Internet Explorer 8

A recent update to DataTables triggers an Internet Explorer 8 feature that’s not at all desirable when working with large result sets in DataTables. Internet Explorer displays a warning message when too many lines of script are being executed.

The Microsoft support site recommends adjusting the client machine’s registry to change this behavior. That’s not a reasonable solution for fixing this application; I don't want to mess with a client’s registry settings if it can be avoided. But there’s another option.

A post in the DataTables user forums suggested a modification to the DataTables script file. I implemented this modification and it works like a charm. You can see the details in the forum thread titled, “Sorting causes IE to throw ‘A script on this page is causing Internet Explorer to run slowly,’” at datatables.net/forums/discussion/3333/sorting-causes-ie-to-throw-a-script-on-this-page-is-causing-internet-explorer-to-run-slowly.

So Many Features to Explore

Hopefully you’ve already seen enough to understand my excitement over this extensive plug-in. There’s a lot more that you can do to configure a table’s look—as well as its behavior—in the read-only scenario I’ve demonstrated. DataTables also lets you edit in a fluid way, and if you want to keep some of that logic on the server side, you can do that while still benefiting from DataTables.

Using DataTables to let your end users slice and dice the large amounts of data available in the increasing number of publicly available OData services seems, to me, like a match made in geek heaven.

Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other Microsoft .NET Framework topics at user groups and conferences around the world. Lerman blogs at thedatafarm.com/blog and is the author of the highly acclaimed book, “Programming Entity Framework” (O’Reilly Media, 2010). Follow her on Twitter.com: @julielerman.

Thanks to the following technical experts for reviewing this article: Rey Bango and Alex James