Using DataTables with Web API Part 3: Paging, Sorting and Searching

The internet is awash with data. So much so, it's hard to make sense of it all. Data about customers. Financial data. Data about stuff. Data that we need to keep track of and change. Most of the time, we store it in databases. Sometimes we get hold of it through APIs. On occasion, we present to people for analysis. One thing's for certain though. Howsoever we store it, we need to avoid information overload if we're viewing it on a website.

How do we avoid information overload? By viewing our data in bite-sized chunks. By allowing us to control how much we see and in which order we see it. DataTables gives us a powerful window onto our data. We can view it a few items at a time. We can search and filter to zero in on what's relevant. We can change the order, allowing for easy comparison.

First Up: Paging and Ordering

We're building atop the previous article on hooking up DataTables and Web API via POST. If you want to follow along, head over there first and grab the Visual Studio solution. I'll wait for you to get back...

Got it? Let's crack on. We need to make a small change to our JavaScript plug in code to enable all of this juicy stuff. Let's switch on paging, ordering and searching in our script:

$("#CustomerTable")
    .dataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "/api/customerSearch",
            "method": "POST"
        },
        "columns": [
            { "data": "companyName" },
            { "data": "address" },
            { "data": "postcode" },
            { "data": "telephone" }
        ],
        "language": {
            "emptyTable": "There are no customers at present.",
            "zeroRecords": "There were no matching customers found."
        },
        "searching": true, // <-- this should be set to true
        "ordering": true, // <-- this should be set to true
        "paging": true // <-- this should be set to true
    });

That's it for the front end. Head on over to the API code. We're going to add a base Controller with paging and ordering methods. We'll use this just before we send the data back to the client. Add a SearchController to the Controllers/api folder that looks like this:

public abstract class SearchController : ApiController
{
    protected static IList<TDetail> PageResults<TDetail> 
     (IEnumerable<TDetail> results, SearchRequest request) where TDetail : SearchDetail
    {
        var skip = request.Start;
        var pageSize = request.Length;
        var orderedResults = OrderResults(results, request);
        return pageSize > 0 ? orderedResults.Skip(skip).Take(pageSize).ToList() : 
              orderedResults.ToList();
    }

    private static IEnumerable<TDetail> OrderResults<TDetail> 
      (IEnumerable<TDetail> results, SearchRequest request) where TDetail : SearchDetail
    {
        if (request.Order == null) return results;
        var columnIndex = request.Order[0].Column;
        var sortDirection = request.Order[0].Dir;
        var columnName = request.Columns[columnIndex].Data.AsPropertyName();
        var prop = typeof(TDetail).GetProperty(columnName);
        return sortDirection == "asc" ? results.OrderBy(prop.GetValue) : 
                                        results.OrderByDescending(prop.GetValue);
    }
}

So what have we got here? We're using LINQ to perform our paging and ordering. Our request contains the Start and Length values that we need for paging. We're using Skip and Take to give us a page of results. For example, Start 0 and Length 10 gives us the first 10 results. Start 20 and Length 10 means skip over the first 20 results and return the next 10. We need to call the paging code after we've done our ordering. If we don't, we'll get some odd results if we change the default order and then page through.

If you want to see the paging in action, you'll need to do one of 2 things. The default page length is 10. You could add more customers to the data source, so you've got more than 10. If you don't fancy doing that, you can change the default page length. Would you believe it, but the property is called pageLength. Let's add it to the end of our plug in options code:

"paging": true,
"pageLength": 3

One other small thing to note is the AsPropertyName method. That's a small extension method that capitalises the first letter of our Columns Data property. It changes companyName to CompanyName, for example. The JavaScript will send across companyName. The GetProperty method expects to receive CompanyName or it won't work. Let's add an Extensions class:

public static class Extensions
{
    public static string AsPropertyName(this string source)
    {
        return char.ToUpper(source[0]) + source.Substring(1);
    }
}

View the original article.

Searching and Filtering

We'll use these new methods in our controller in a moment. Before that, let's take a look at the code to handle the searching side of things. We'll do that with a static helper class. Add an ApiHelper class to the project:

public static class ApiHelper
{
    public static IEnumerable<CustomerSearchDetail> FilterCustomers
                  (IEnumerable<CustomerSearchDetail> details, string searchText)
    {
        if (searchText.IsEmpty())
        {
            return details;
        }

        var results = details.Where(x => x.CompanyName.ContainsIgnoringCase(searchText)
            || x.Address.ContainsIgnoringCase(searchText)
            || x.Postcode.ContainsIgnoringCase(searchText)
            || x.Telephone.ContainsIgnoringCase(searchText)
            );

        return results;
    }
}

I've added another extension method, ContainsIgnoringCase, which makes our code read a little better. The search will be case sensitive by default. We need to make sure we still return results if they differ by case alone. Let's add it to our Extensions class:

public static bool ContainsIgnoringCase(this string source, string substring)
{
    return source.ToLower().Contains(substring.ToLower());
}

Now, we need to change our CustomerSearchController to use the new paging and ordering methods. Here's how it now looks:

public class CustomerSearchController : SearchController
{
    public IHttpActionResult Post(SearchRequest request)
    {
        var allCustomers = JsonConvert.DeserializeObject<CustomerData> (CustomerData.DataSource);
        var response = WrapSearch(allCustomers.Data, request);
        return Ok(response);
    }

    private static CustomerSearchResponse WrapSearch
           (ICollection<CustomerSearchDetail> details, SearchRequest request)
    {
        var results = ApiHelper.FilterCustomers(details, request.Search.Value).ToList();
        var response = new CustomerSearchResponse
        {
            Data = PageResults(results, request),
            Draw = request.Draw,
            RecordsFiltered = results.Count,
            RecordsTotal = details.Count
        };
        return response;
    }
}

The key takeaway here is how we're using RecordsFiltered and RecordsTotal. This allows us to tell DataTables that we're bringing back partial results. DataTables then reflects that in the UI, as the screenshot below demonstrates:

That's it for paging, ordering and searching. Next up, we'll examine how to package this up so we can re-use it. Our requirements are evolving. We need to show 4 tables of data now. Let's see if we can avoid copying and pasting that JavaScript code all over the place!

View the original article.