Sorting Paging a Grid using Knockout JS and ASP.NET Web API Data Service

Posted by: Suprotim Agarwal , on 9/18/2013, in Category DNC Magazine
Views: 44806
Abstract: This article uses ASP.NET Web API and Knockout JS to implement Templating and Data Binding and applies it to a common Line of Business application requirement – Paging and Sorting a Grid

Knockout JS as we know is a highly versatile JavaScript library that helps us implement the MVVM pattern on the client by providing us with two way data binding and templating capabilities. Today we will use Knockout JS and ASP.NET Web API to build an application that represents data in a tabular format with AJAX based sorting and paging capabilities.

Representing Tabular data in a table is as old as web itself and today end users expect a certain amount of features and functionality built in. These include fast access, sorting, paging and filtering. There are well established libraries like Datatables.net that already do these for us. However, knowing how something is implemented, helps us tweak things to our advantage, because no two requirements are the same, ever.

This article is published from the DNC .NET Magazine – A Free High Quality Digital Magazine for .NET professionals published once every two months. Subscribe to this eMagazine for Free

 

Sorting and Paging a Grid using Knockout & ASP.NET Web API – The Requirement

We have a table of user accounts with a few hundred user records. The requirement is to be able to list this user data in a grid layout and provide sorting and paging capabilities on the client side.

Prerequisites

We assume familiarity with ASP.NET Web API and concepts of MVVM. We’ll walk through the specifics of Knockout model binding.

Getting Started

We start off in Visual Studio with a new ASP.NET MVC 4 project using the Empty template. We will start from scratch and not assume any dependencies.

Installing Dependencies

We start by installing jQuery and Knockout JS using the Nuget Package Management console.

PM> install-package jQuery

PM> install-package knockoutjs

Next we update our Web API dependencies

PM> update-package Microsoft.AspNet.WebApi

We install Entity Framework for the Data Layer

PM> install-package EntityFramework

We also get BootStrap styles and scripts from Nuget using

PM> install-package Twitter.Bootstrap

Setting up the Data Layer

Step 1: In the Model folder, add a class called Contact with the following properties.

public class Contact
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public DateTime DateOfBirth { get; set; }
public string PhoneNumber { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
}

Build the solution.

Step 2: Set up a connection string to point to a LocalDb instance as follows

<add
name="DefaultConnection"
connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename='|DataDirectory|\KODatatableContext.mdf';Integrated Security=True"
providerName="System.Data.SqlClient" />

You can point to a local SQL Express or SQL Server too, simply update the connection string appropriately.

Step 3: Next we scaffold a Controller, Repository and Views using MVC Scaffolding

mvc-scaffold-controller

Step 4: In the generated KoDatatableContext class, add the following constructor.

public KoDatatableContext():base("DefaultConnection")

{

}

This forces the DB Context to use the connection string. If you don’t provide this, default setting is to look for a SQL Server at .\SQLExpress and create a DB Based on the Context’s namespace.

Step 5: Run the application and visit the /Contacts/Index page. This will bring up the empty Index page by first generating the DB behind the scenes.

Step 6: To have multiple pages of contacts we need some pre-generated data. I found http://www.generatedata.com to be a pretty neat site for this purpose. I added all the columns except Id and requested it to generate 100 rows into an Insert Script. Once the script is downloaded, connect to the DB using the Database Explorer in Visual Studio and run it on the KnockoutDataTableContext DB. Refresh the /Contacts/Index page and you should see 100 rows of data.

sample-data-barebones

Sprucing it up with BootStrap

The page Index looks rather plain vanilla, so let’s get BootStrap kicking and spruce it up a little.

Step 1: Add _ViewStart.cshtml in the Views folder.

add-view-start

Update the markup to contain only the following

@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}

Step 2: Create a folder /Views/Shared and add _Layout.cshtml to it.

add-layout

Step 3: To make use of Bundling and Minification we need to add one more Nuget package

PM> install-package Microsoft.AspNet.Web.Optimization

Once the optimization bundle is downloaded, setup the BundleConfig.cs. In App_Start folder, add a BundleConfig.cs

public class BundleConfig
{
public static void RegisterBundles(BundleCollection bundles)
{
  bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
   "~/Scripts/jquery-{version}.js"));
  bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
   "~/Scripts/bootstrap.js",
   "~/Scripts/html5shiv.js"));
  bundles.Add(new ScriptBundle("~/bundles/jqueryui").Include(
   "~/Scripts/jquery-ui-{version}.js"));

  bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
   "~/Scripts/jquery.unobtrusive*",
   "~/Scripts/jquery.validate*"));
  bundles.Add(new ScriptBundle("~/bundles/knockout").Include(
   "~/Scripts/knockout-{version}.js"));
  bundles.Add(new StyleBundle("~/Styles/bootstrap/css").Include(
   "~/Content/bootstrap-responsive.css",
   "~/Content/bootstrap.css"));
}
}

This sets up BootStrap and jQuery script & style bundles.

Step 4: Update the _Layout.cshtml to use the bootstrap CSS and Script bundles

@{
    ViewBag.Title = "Knockout DataTable";
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>@ViewBag.Title</title>
@Styles.Render("~/Styles/bootstrap/css")
</head>
<body>
<div class="navbar navbar-inverse">
  <div class="navbar-inner">
   <div class="brand">
    @ViewBag.Title
   </div>
  </div>
</div>
<div class="container-fluid">
  <div class="row-fluid">
   @RenderBody()
  </div>
   @Scripts.Render("~/bundles/jquery")
   @Scripts.Render("~/bundles/knockout")
   @Scripts.Render("~/bundles/bootstrap")
   @RenderSection("Scripts", false)
  </div>
</body>
</html>

Step 5: In the Index.cshtml add the class table to the container

<table class="table">

</table>

If we run the app now, it should look much cleaner

bootstrapped-basic

We are all set, let’s implement the rest of the features.

Paging the Grid

Switching to ASP.NET Web API

Since we want all the rendering to be done on the client side, instead of using the ASP.NET MVC Controller and rendering the View on the server, we’ll switch to ASP.NET Web API and change the markup to use Knockout JS.

Step 1: Add a new API Controller called AddressBookController

scaffold-addressbook-controller

Step 2: Update the existing Contacts controller so that it doesn’t return any data

public ViewResult Index()
{
    return View();
}

Adding Knockout ViewModel and Updating UI

We will step through the KO Script incrementally. To start off with, we add a new JavaScript file called ko-datatable.js under the Scripts folder. This will contain our view model and data-binding code.

Step 1: As a first step, we setup our viewModel to contain only the contacts collection as an observable collection. When the document is loaded, it calls our AddressBook api and gets the list or contacts. It then pushes them into the contacts viewModel property. Finally we call ko.applyBindings(…) to do the data binding.

/// <reference path="knockout-2.2.1.js" />
/// <reference path="jquery-2.0.2.js" />
var viewModel = function ()
{
$this = this;
$this.contacts = ko.observableArray();
}

$(document).ready(function ()
{
$.ajax({
  url: "/api/AddressBook",
  type: "GET"
}).done(function (data)
{
  var vm = new viewModel();
  vm.contacts(data);
  ko.applyBindings(vm);
});
});

Step 2: To utilize the ViewModel data, we update the Index.cshtml to use Knockout Databinding syntax instead of the previous server side razor implementation.

We use the foreach data-binding to bind the contacts collection to the table (body). Whereas we bind each td to the field names.

<tbody data-bind="foreach: contacts">
<tr>
  <td data-bind="text: Name"></td>
  <td data-bind="text: Email"></td>
  <td data-bind="text: DateOfBirth"></td>
  <td data-bind="text: PhoneNumber"></td>
  <td data-bind="text: City"></td>
  <td data-bind="text: State"></td>
  <td data-bind="text: Country"></td>
</tr>
</tbody>

At this point if we run the app, we’ll see the same type of table layout we had seen for the server side binding. The only difference now is it’s using Knockout’s Databinding and templating to generate the table on the client side.

Implementing Pagination in Grid

For paged data, we need to be able to track the following:

- Current Page Index: Starts at zero and increases/decreases as user selects different pages

- Page Size: A value indicating the number of items in the page

- All elements: The complete list of elements from which the current page will be sliced out.

We add the above properties in our view Model and add a Knockout Computed observable to calculate the current page. Computed Observables in Knockout are functions that are recalculated automatically when the observables used in the function change. So our computed observable returns a slice of the array elements from the complete list of Contacts. By default page size is 10 and current page index is 0, so the first 10 records are shown.

There are two functions previousPage and nextFunction. These are bound to the Previous and Next buttons’ click events. The next button circles back to the first page once it reaches the last page. Similarly the previous button circles back to the last page once it is reaches the first page.

var viewModel = function ()
{
$this = this;
$this.currentPage = ko.observable();
$this.pageSize = ko.observable(10);
$this.currentPageIndex = ko.observable(0);
$this.contacts = ko.observableArray();
$this.currentPage = ko.computed(function ()
{
  var pagesize = parseInt($this.pageSize(), 10),
  startIndex = pagesize * $this.currentPageIndex(),
  endIndex = startIndex + pagesize;
  return $this.contacts.slice(startIndex, endIndex);
});
$this.nextPage = function ()
{
  if ((($this.currentPageIndex() + 1) * $this.pageSize()) < $this.contacts().length)
  {
   $this.currentPageIndex($this.currentPageIndex() + 1);
  }
  else
  {
   $this.currentPageIndex(0);
  }
}
$this.previousPage = function ()
{
  if ($this.currentPageIndex() > 0)
  {
   $this.currentPageIndex($this.currentPageIndex() - 1);
  }
  else
  {
   $this.currentPageIndex((Math.ceil($this.contacts().length / $this.pageSize())) - 1);
  }
}
}

We add the following Table footer markup for the previous next and current page index.

<tfoot>
<tr>
  <td colspan="7">
   <button data-bind="click: previousPage" class="btn"><i class="icon-step-backward"></i></button>
    Page<label data-bind="text: currentPageIndex() + 1" class="badge"></label>
   <button data-bind="click: nextPage" class="btn"><i class="icon-step-forward"></i></button>
  </td>
</tr>
</tfoot>

If we run the app now, the previous next buttons will be functional and we can see the data change accordingly.

pagination-buttons

Sorting Grid

We can implement sorting on the client side as well, with the help of Knockout’s sort function for the observable array. KO provides a sort function with two inputs left and right, that contain the JavaScript objects form the array. We can write custom logic to sort based on any of the properties in the object. So if we wanted to sort on the Name property in our Contact object, our sorting function would be as follows:

$this.contacts.sort(function (left, right)
{
    return left.Name < right.Name ? 1 : -1;
});

However, the above is only a one-way sort, as in, after it’s sorted say in ‘ascending’ order, there is no toggling to ‘descending’ order, like is the case with most sort functionality in tabular data representations. To be able to toggle between ascending and descending, we have to store the current status in our ViewModel.

Another issue is that the Name property is hard coded above. We would like to sort based on the column header we click on. So instead of the function picking the Name property, we should determine the Property to be sorted on at run time.

Updating the Markup

We update our Index.cshtml’s table header as follows

<thead>
<tr data-bind="click: sortTable">
  <th data-column="Name">Name
  </th>
  <th data-column="Email">Email
  </th>
  <th data-column="DateOfBirth">Date of Birth
  </th>
  <th data-column="PhoneNumber">Phone Number
  </th>
  <th data-column="City">City
  </th>
  <th data-column="State">State
  </th>
  <th data-column="Country">Country
  </th>
</tr>
</thead>

We have bound the click event of the table’s header row to a function called sortTable in the ViewModel. We will see the implementation of this function shortly.

Next we have used HTML5 data- attributes to define a new attribute called data-column and set the value to the name of each property that column is bound to.

Updating our Knockout ViewModel and Implementing the Sort

1. First we add a property that saves the current Sort type (ascending or descending) and set it to ‘ascending’ by default.

$this.sortType = "ascending";

2. Next we add the function sortTable that does the actual sorting

$this.sortTable = function (viewModel, e)
{
var orderProp = $(e.target).attr("data-column")
$this.contacts.sort(function (left, right)
{
  leftVal = left[orderProp];
  rightVal = right[orderProp];
  if ($this.sortType == "ascending")
  {
   return leftVal < rightVal ? 1 : -1;
  }
  else
  {
   return leftVal > rightVal ? 1 : -1;
  }
});
$this.sortType = ($this.sortType == "ascending") ? "descending" : "ascending";
}
};

This code works as follows

a. The function first extracts the column on which the click happened. It uses the event object e and picks out the value in the ‘data-column’ attribute. As we saw in the markup, ‘data-column’ has the name of the property to which that column’s data is bound to.

b. Once we have which column to sort on, we call the ‘contacts’ observable array’s sort method with a custom delegate that return 1 or -1 based on the comparison result.

c. In the sort function, we extract the property using the column name. Since this is now coming from the ‘data-column’ attribute, we will automatically get the correct property name to compare.

d. Next, based on whether the current sortType is ascending or descending, we return the value 1 or -1.

e. Once the entire array has been sorted, we set the sortType attribute to opposite of what it was.

That’s all that needs to be done to get sorting going! If you run the application now, you can click on the columns and see sorting in action on each.

Showing a Sort-Indicator arrow

Usually when we have a sortable table, the column on which it is sorted has an arrow indicating if it is sorted in ascending or descending order. In our view model, we are already saving the sortType, we will add two more properties.

- First is iconType - this is set to the bootstrap css style or ‘icon-chevron-up’ or ‘icon-chevron-down’. This is set in the sortTable function when user click on the header column.

- Second property is for saving the name of the column that the table is currently sorted on – currentColumn. This value is also set in sortTable function.

$this.sortTable = function (viewModel, e)
{
var orderProp = $(e.target).attr("data-column")
$this.currentColumn(orderProp);
$this.contacts.sort(function (left, right)
{
  …
}

$this.iconType(($this.sortType == "ascending") ? "icon-chevron-up" : "icon-chevron-down");
}

Next we update the Table header markup, to do two things

1. Set the arrow visible based on the currentColumn and invisible for the rest of the columns.

2. Set the arrow to ascending or descending based on sortType

To do this we do the following:

a. Add a couple of CSS styles that we’ll set dynamically

<style type="text/css">
    .isVisible {
        display: inline;
    }

    .isHidden {
        display: none;
    }
</style>

b. In the table header, for every column we add a span whose visibility is set based on whether the currentColumn value is the same as for current one. For the Name column if the currentColumn() value is ‘Name’ then we set the class to ‘isVisible’ which in turn makes the span and its contents visible. For all other values of currentColumn(), the span remain hidden.

<table class="table">
<thead>
  <tr data-bind="click: sortTable">
   <th data-column="Name">Name
    <span data-bind="attr: { class: currentColumn() == 'Name' ? 'isVisible' : 'isHidden' }">
     <!-- add image here -->
    </span>
   </th>
   …
  <tr>
</thead>
</table>

c. Finally we add an icon in the placeholder above and set the image by binding the class attribute to iconType

<i data-bind="attr: { class: iconType }"></i>

If we run the application now, we can see the sorting and the sort indicator in action. For example the snapshot below shows that the table is sorted in ascending order of the Name column.

knockout-table-name-sorted

Dynamic Page Size

The final feature that we’ll add is to let users select the number of rows per page. Currently it is hard-coded to 10. We will add an html select to the footer with various other sizes and bind it to the pageSize property of our ViewModel.

Because the currentPage property is computed and one of the observables it depends on is pageSize, any change in pageSize will re-compute the currentPage property. This will result in re-calculation and rendering of the table.

As shown in the markup below we have

<tfoot>
<tr>
  <td>
   Number of items per page:
   <select id="pageSizeSelector" data-bind="value: pageSize">
    <option value="10">10</option>
    <option value="20">20</option>
    <option value="30">30</option>
    <option value="40">40</option>
    <option value="50">50</option>
    <option value="60">60</option>
    <option value="70">70</option>
    <option value="80">80</option>
    <option value="90">90</option>
    <option value="100">100</option>
   </select>
  </td>

</tr>
</tfoot>

Here is our final Table

knockout-webapi-final-table

With that have completed our requirements of having a Sortable Grid with pagination.

Conclusion

We saw how to use ASP.NET Web API and Knockout JS for templating and data-binding to get pretty rich functionality with minimal code.

We can certainly improve on this model. For example if we have more than a few hundred rows of data, we should use server side data selection and instead of bringing back the complete dataset, use only the current page’s data. This will offer the right balance between performance and richness of features.

Overall Knockout is a powerful library and can be used as a drop-in enhancement to bring in rich functionality on the client side.

Download the entire source code of this article (Github)

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Suprotim Agarwal, ASP.NET Architecture MVP, MCSD, MCAD, MCDBA, MCSE, is the CEO of A2Z Knowledge Visuals Pvt. He primarily works as an Architect Consultant and provides consultancy on how to design and develop .NET centric database solutions.

Suprotim is the founder and primary contributor to DotNetCurry, DNC .NET Magazine, SQLServerCurry and DevCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls. and is authoring another one at The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Maxim on Monday, September 23, 2013 8:27 AM
May be I miss something...Could you clarify where is the server-side paging? What if my database contains 100500+ items?
Comment posted by Maxim on Monday, September 23, 2013 8:29 AM
[nb:it is even strange it is published in magazine.]
Comment posted by Alexey Zimarev on Thursday, October 17, 2013 2:11 PM
Maxim is right, server-size paging is much more relevant, this is why we have WebAPI, otherwise we can just pump everything into the view from regular MVC controller.

However it is not very complicated to develop the code from this article to have reguests to the server WebAPI on each page change. It will require the API controller method change to get extra parameters of course.
Comment posted by Lajos Marton on Friday, November 15, 2013 1:20 AM
Hi Suprotim,

What about sorting, if you have a computed column, for example an average of two columns?

It was a good article!

Thank you!
Comment posted by jakub klekota on Saturday, November 23, 2013 10:59 AM
Nice. But in my opinion, when build a web api most logical in pagging is download on next page data. This example show: download from db any data (1000+) bind to ko.obserablearray and show. Maybe, autor show how build pagging using dynamic data download on next page.
Comment posted by Tolga on Wednesday, November 27, 2013 3:23 AM
Image links are broken..
Comment posted by samit verma on Monday, February 10, 2014 4:26 PM
nice work !!
Comment posted by tej on Tuesday, March 11, 2014 5:46 AM
Hi Agarwal, how can i eliminate EMAIL column from sorting?
Comment posted by Yaron on Saturday, March 15, 2014 4:48 AM
Sometimes client based paging is relevant since in some cases you do know that there won't be more than a few items.
I think the sorting solution is not complete.
The ascending\descending should be stored for each column individually and as mentioned before sorting should be available just for column that defined as sortable.
Comment posted by Steve5877 on Sunday, June 29, 2014 6:11 AM
I have to agree with Maxim. This is not server-side paging and NOT a good use of a Web API. The entire table is pulled into the observable array? Isn't better to take a page from the server when needed?

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel