Click here to Skip to main content
14,835,707 members
Articles / Web Development / ASP.NET
Technical Blog
Posted 1 Dec 2016

Stats

18K views
4 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
3.80/5 (4 votes)
1 Dec 2016CPOL4 min read
This is part 3 of a 4-part series on using DataTables with WebAPI.

This is part 3 of a 4-part series on using DataTables with WebAPI. Check out the other parts here:

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:

JavaScript
$("#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:

C#
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:

JavaScript
"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:

C#
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:

C#
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:

C#
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:

C#
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:

DataTables final browser display window

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

levelnis
Technical Lead Levelnis Ltd
United Kingdom United Kingdom
Follow along my journey as I create a newsletter about launching websites. Just message me with "I'm in" and I'll add you

Comments and Discussions

 
QuestionThe Best Tutorial on Datatables and Web Api & Need Help Pin
Mahbub - A - Rob1313-Oct-17 16:11
MemberMahbub - A - Rob1313-Oct-17 16:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.