Server side paging with KoGrid

Source code for article – https://github.com/RossWhitehead/ServerSidePagingKoGrid

In this post I’m going to demonstrate how to implement server side paging with KoGrid. I’m going to be doing this within the context of a ASP.NET MVC project, and will utilize both MVC and WebAPI controllers.

When the number of items is relatively small it is advisable to retrieve all items from the server when initializing a page, and then use client-side binding with KoGrid – or a similar grid framework. This offers the best experience for the user as they are able page through the data without the need for the browser to contact the server. However, when the number of items gets particularly large then we may encounter performance issues, both client-side and server-side. In this case it is worth considering the implementation of server-side paging whereby the client requests data from the server one page at a time.

This is what I’m going to be building –

BasicKoGrid

KoGrid is built on top of Knockout, a javascript library that takes a lot of the leg work out of separating UI mark-up (View) and presentation data/logic (ViewModel) when creating dynamic web pages.

As I will be presenting KoGrid and Knockout in the context of an MVC application, I will essentially be implementing the MCVVM (Model-Controller-View-ViewModel) pattern. There’s a good debate here on MVC and MVVM, and a number of acronyms are proposed for MVC combined with MVVM. My preference is MCVVM as in terms of application flow we have Controllers which act as intermediaries between the domain Model and the UI, responding to requests by generating markup (View) and data/logic (ViewModel). The terminology isn’t important. I’m only mentioning it as it defines the order in which I will be presenting the solution  –

  1. Model
  2. Controller
  3. View
  4. ViewModel

1. Model

ASP.NET Web Application Project

Using VS 2013, the first step is to create a ASP.NET Web Application project and select either the MVC or WebApi template, ensuring that both MVC and WebApi core references are included. This will allow requests to be routed to both MVC and WebApi controllers. I will be using an MVC controller to serve up the web page, as I can then benefit from the Razor view engine, and the layout page to generate the HTML for the header, footer, and navigation elements. I will then use Ajax to get data from a WebApi controller. I could have used the MVC controller to service the Ajax requests, but I wanted to demonstrate MVC and WebApi controllers working in the same project.

Domain Model

The next step is to create the domain model. I’m using Code-First Entity Framework 6.0 to do this.

I have 2 entities – ProductCategory and Product, with a one-to-many relationship between them. And I have a DbContext called WebApiDbContext, which is used to generate the database.

ProductCategory.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WebAPIKo.Models
{
    public class ProductCategory
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ProductCategoryId { get; set; }

        [Required]
        [StringLength(100)]
        public string Name { get; set; }

        // Navigational Properties
        public virtual ICollection<Product> Products { get; set; }
    }
}

Product.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WebAPIKo.Models
{
    public class Product
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ProductId { get; set; }

        [Required]
        [StringLength(100)]
        public string Name { get; set; }

        [Required]
        [StringLength(1000)]
        public string Description { get; set; }

        [Required]
        public int ProductCategoryId { get; set; }

        // Navigation properties
        public virtual ProductCategory ProductCategory { get; set; }
    }
}

WebApiDbContext.cs

using System.Data.Entity;

namespace WebAPIKo.Models
{
    public class WebAPIKoDbContext : DbContext
    {
        public virtual DbSet<Product> Products { get; set; }
        public virtual DbSet<ProductCategory> ProductCategories { get; set; }
    }
}

Data Transfer Objects (DTOs)

The WebApi controller, that I will detail in the next section, will service Ajax Get requests for product lists. It will interrogate the domain model to get the data that it requires.

The data requirements of the UI generally differ from the domain. To avoid a mismatch and to create a clear separation of concerns, it is good practice to create Data Transfer Objects (DTOs) to structure the data passed to the UI. The controller will transpose the data from the domain model into DTOs, which will then be serialized to Json and passed to the UI.

ProductListDTO.cs

The ProductListDTO class will be instantiated and serialized into Json by the WebApi controller in response to a Get request.

It contains a list of ProductDTO objects, representing a page of products. And an integer, ProductCount, which will hold the count of all products that match the supplied filter. If there are 30 products matching the supplied filter then ProductCount will be set equal to 30, but if the page size is set to 5 then there will only be 5 ProductDTO objects in the list.

using System.Collections.Generic;

namespace WebAPIKo.Models
{
    public class ProductListDTO
    {
        public int ProductCount { get; set; }
        public List<ProductDTO> PageOfProducts { get; set; }
    }
}

ProductDTO.cs

In the domain model, Product has a ProductCategoryId field and a navigation property to ProductCategory. In our simple example, ProductCategoryId is not required by the UI. So we flatten the Product-ProductCategory relationship and populate ProductDTO with ProductCategoryName.

namespace WebAPIKo.Models
{
    public class ProductDTO
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string ProductCategoryName { get; set; }
    }
}

2. Controller

There are 2 controllers.

HomeController.cs

The first is an MVC controller which services the initial request by responding with the BasicKoGrid.cshtml page that contains the View/ViewModel. In this case we are not parsing any view data to the view, although this is possible.

using System.Web.Mvc;

namespace WebAPIKo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult BasicKoGrid()
        {
            return View();
        }
    }
}

ProductController.cs

The second controller is a WebApi controller which contains a GetProducts action. When the grid is initialized, and when the user interacts with it – by navigating to a different page, or changing the filter and then clicking on refresh – the ViewModel will send an Ajax Get request in order to get a new page of products. The following information will be included in the Get request and will be bound to the appropriate GetProducts argument –

  • page
    • requested page number
  • pageSize
    • page size
  • filter
    • in one of 2 formats –
      • [ColumnName:filter] e.g. “name:trek” – filters on a specific column
      • [filter] e.g. “trek” – filters on all relevant columns (both name OR description)
  • sort
    • [ColumnName asc||desc] e.g. “description desc”

The GetProducts action constructs a Linq query, IQueryable<Product>. It checks to see if a filter has been supplied, and if so applies a Where clause to the query. I am using System.Linq.Dynamic extension methods to simplify the building of the query. You will need to install this library from NuGet.

The action then executes the Count() method on the query to determine the total number of products that match the filter. It then applies a SortBy clause, defaulting the sort to “name asc”, and skips to and takes the required page.

It finally populates an instance of ProductListDTO with the product count and with the page of products transposed to ProductDTO objects. The instance of ProductListDTO is serialized and returned with an OK (200) HTTP status code.

using System.Linq;
using System.Linq.Dynamic;
using System.Web.Http;
using WebAPIKo.Models;

namespace WebAPIKo.Controllers
{
    public class ProductsController : ApiController
    {
        private WebAPIKoDbContext db = new WebAPIKoDbContext();

        // GET: api/Product
        public IHttpActionResult GetProducts([FromUri]int page, [FromUri]int pageSize, [FromUri]string filter, [FromUri]string sort)
        {
            ProductListDTO dto = new ProductListDTO();

            IQueryable<Product> query = db.Products;

            if (filter != null)
            {
                if (filter.Contains(':'))
                {
                    string[] filterArray = filter.Split(':');
                    switch (filterArray[0].ToLower())
                    {
                        case "name":
                            query = query.Where(p => p.Name.Contains(filterArray[1].Trim()));
                            break;
                        case "description":
                            query = query.Where(p => p.Description.Contains(filterArray[1].Trim()));
                            break;
                    }
                }
                else
                {
                    query = query.Where(p => p.Name.Contains(filter.Trim()) || p.Description.Contains(filter.Trim()));
                }
            }

            dto.ProductCount = query.Count();

            query = query.OrderBy(sort == null ? "name asc" : sort).Skip((page - 1) * pageSize).Take(pageSize);

            dto.PageOfProducts = query.Select(p => new ProductDTO()
                {
                    ProductId = p.ProductId,
                    Name = p.Name,
                    Description = p.Description,
                    ProductCategoryName = p.ProductCategory.Name
                }
                ).ToList(); 

            return Ok(dto);
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

3. View

There are 2 aspects to the view. The CSS styles and the HTML markup.

CSS styles

.gridStyle is applied to the <div> element that acts as the container for the grid. As it’s name suggests it styles the grid.

There seems to be styling issues with the KoGrid filter. This is what I get straight out-of-the-box in both Chrome and IE –

filterbefore

I have fixed these issues with the .kg* styles  –

filterafter

I then have a set of styles for the list panel and refresh button.

And finally a style to position a loading indicator in the center of it’s parent container.

<style type="text/css">
    .gridStyle {
        border: 1px solid rgb(212,212,212);
        width: 100%;
        height: 300px;
        margin: auto;
    }

    .kgColMenu {
        width: 200px;
    }

        .kgColMenu input[type=text] {
            width: 100%;
        }

    .kgColList {
        padding-left: 25px;
    }

    .kgColListItem label {
        width: 100%;
    }

    .list-panel {
        margin-top: 20px;
    }

        .list-panel .panel-heading {
            overflow: auto;
            padding: 5px 20px;
        }

        .list-panel .panel-title {
            float: left;
            margin-top: 10px;
        }

        .list-panel .panel-body {
            padding: 20px 20px 10px 20px;
        }

        .list-panel .refresh-button {
            float: right;
        }

    .loading-indicator {
        position: absolute;
        left: 50%;
        top: 50%;
        margin-left: -26px;
        margin-top: -26px;
        display: none;
        z-index: 9999;
    }
</style>

 HTML

All that’s required to render the grid is a <div> element containing a Knockout declarative binding – data-bind=”koGrid: gridOptions” – attribute. gridOptions contains the configuration options for the grid and is a property of the ViewModel that we will be binding to the View. I will discuss this further in the ViewModel section.

<div class="gridStyle" data-bind="koGrid: gridOptions"></div>

I have placed this <div> in a Bootstrap panel, along with a refresh button and a loading indicator.

The refresh button’s click event is bound to the ViewModel’s “get” method – “data-bind= click: get”. I will discuss the “get” method in the ViewModel section.

The loading indicator will be positioned to the center of the panel using the styles mentioned above. Note. A variety of loading indicators can be downloaded from this site – http://www.ajaxload.info/

<div class="row">
<div class="col-md-12">
<div class="panel panel-primary list-panel" id="list-panel">
<div class="panel-heading list-panel-heading">
<h3 class="panel-title list-panel-title">Products</h3>
<button type="button" class="btn btn-default btn-md refresh-button" data-bind="click: get">
                    <span class="glyphicon glyphicon-refresh" aria-hidden="true"></span> Refresh
                </button></div>
<div class="panel-body">
<div class="gridStyle" data-bind="koGrid: gridOptions"></div>
</div>
<img src="~/Content/Images/ajax-loader.gif" id="loading-indicator" class="loading-indicator" /></div>
</div>
</div>

 4. ViewModel

We need to add references to the KoGrid and Knockout libraries, and also to JQuery if not already included in the app. As I am building a ASP.NET Web application, I have added references to the libraries in BundleConfig.cs –

bundles.Add(new ScriptBundle("~/bundles/knockout").Include(
            "~/Scripts/knockout-3.0.0.js",
            "~/Scripts/knockout.validation.js",
            "~/Scripts/koGrid-2.1.1.js"));

The ViewModel comprises of the following –

Product

A definition for a Product type with a constructor with productId, name, description, and productCategoryName parameters. Product contains properties defined as ko.observable objects, which are Knockout specific JavaScript objects that can notify subscribers about changes, and can automatically detect dependencies. The Product object parameters are passed to the ko.observable object constructors.

ProductListVM

We will be instantiating an instance of this type and binding it to the KoGrid.

The ProductListVM type contains the following properties –

  • self.products – an array which contains the data to be bound to the grid.
  • self.columnDefs – an array of objects defining the columns.
  • self.filterOptions – I have the useExternalFilter option set to false, so when a user enters a filter it will be applied to the current page of data in the grid. But I will also pass this filter with the Ajax get request when the user chooses to refresh the grid or navigate to a new page of data.
  • self.pagingOptions – with the following options –
    • currentPage – current page
    • pageSizes – an array of page sizes that the user can choose from
    • pageSize – default page size. Must be equal to one of the sizes specified in the pageSizes array.
    • totalServerItems – used by KoGrid to determine the number of pages to show in the pagination control. We will assign this value each time we get a new page of data from the server.
  • self.gridOptions – contains properties for configuring the grid. A full set of configuration options can be found here. All of the aforementioned ViewModel properties are assigned to configuration options. e.g data: self.products.

The ProductListVM type contains a single method – self.get(). The first thing the get() method does is show the loading indicator. It then performs an Ajax Get call to /api/products, passing in the required page, pageSize, sort and filter. It gets all these values from observable properties, hence reflecting the users interaction with the view.

On success the Ajax Get call executes a callback function that sets the self.pagingOptions.totalServerItems property to data.ProductCount, and for each ProductDTO in data.PageOfProducts pushes a new instance of the Product type to the self.products array. As the self.pagingOptions.totalServerItems and self.products properties are observable and bound to the grid, the grid is updated.

The final section of ProductListVM defines 3 event subscriptions.

The 1st is called when the user selects a new page size. It sets the current page to 1 and then calls the self.Get() method in order to refresh the data.

The 2nd is called when a user selects a new page from the pager control. If calls the self.Get() method to get the new page.

And the 3rd is called when the user clicks on a column heading in order to sort the grid. It sets the current page to 1 and then calls the self.Get() method in order to refresh the data.

Initialization code

After the DOM has loaded I create and instance of ProductListVM called vM. I then bind vM to the list panel, and then call the Get() method in order to populate the grid with the default page of data from the server.

@section scripts{

    <script type="text/javascript">
       $(function () {

            var $loadingIndicator = $('#loading-indicator');

            var productsUri = '/api/products';

            ///////////////////////////////////////////////////////////////////
            // Product

            var Product = function (productId, name, description, productCategoryName) {
                var self = this;

                // Properties

                self.productId = ko.observable(productId);
                self.name = ko.observable(name);
                self.description = ko.observable(description);
                self.productCategoryName = ko.observable(productCategoryName);
            }

            ///////////////////////////////////////////////////////////////////
            // Product List

            var ProductListVM = function () {
                var self = this;

                // Properties
                self.products = ko.observableArray([]);

                self.columnDefs = [
                   { field: 'productId', displayName: 'Id', width: 100 },
                   { field: 'name', displayName: 'Name', width: 200 },
                   { field: 'description', displayName: 'Description' },
                   { field: 'productCategoryName', displayName: 'Product Category', width: 200 }
                ];

                self.filterOptions = {
                    filterText: ko.observable(""),
                    useExternalFilter: false
                };

                self.pagingOptions = {
                    currentPage: ko.observable(1),
                    pageSizes: ko.observableArray([2, 5, 10, 20, 50]),
                    pageSize: ko.observable(5),
                    totalServerItems: ko.observable(0)
                };

                self.sortInfo = ko.observable({ column: { 'field': 'name'}, direction: 'asc' });

                self.gridOptions = {
                    data: self.products,
                    columnDefs: self.columnDefs,
                    autogenerateColumns: false,
                    showGroupPanel: true,
                    canSelectRows: false,
                    showFilter: true,
                    filterOptions: self.filterOptions,
                    enablePaging: true,
                    pagingOptions: self.pagingOptions,
                    sortInfo: self.sortInfo,
                    rowHeight: 35
                };

                // Methods

                self.get = function () {
                    $loadingIndicator.show();

                    $.ajax({
                        url: productsUri,
                        type: 'get',
                        data: {
                            'page': self.pagingOptions.currentPage(),
                            'pageSize': self.pagingOptions.pageSize(),
                            'filter': self.filterOptions.filterText == undefined ? '' : self.filterOptions.filterText(),
                            'sort': self.sortInfo().column.field + ' ' + self.sortInfo().direction
                        },
                        contentType: 'application/json; charset=utf-8',
                        success: function (data) {
                            self.pagingOptions.totalServerItems(data.ProductCount);

                            var productsArray = [];
                            $.each(data.PageOfProducts, function (key, value) {
                                productsArray.push(
                                    new Product(value.ProductId, value.Name, value.Description, value.ProductCategoryId, value.ProductCategoryName));
                            });
                            self.products(productsArray);

                            $loadingIndicator.hide();
                        }
                    });
                };

                // Subscriptions

                self.pagingOptions.pageSize.subscribe(function (data) {
                    self.pagingOptions.currentPage(1);
                    self.get();
                });

                self.pagingOptions.currentPage.subscribe(function (data) {
                    self.get();
                });

                self.sortInfo.subscribe(function (data) {
                    self.pagingOptions.currentPage(1);
                    self.get();
                });
            }

            /////////////////////////////////////////////////////////////////
            // Let's kick it all off

            var vM = new ProductListVM();
            ko.applyBindings(vM, $("#list-panel")[0]);
            vM.get();
        });

    </script>
}

Caveat Emptor

KoGrid works well with server-side binding, and is relatively simple to implement. However, the default filter functionality does not fit particularly well with server-side binding.

The filter input control updates the view model on the afterkeydown event. When a user types a filter string then the filter is applied to the bound ko.observableArray and the view with each key-stroke. This works very well with client-side binding as the ko.observableArray is populated with the all items. For example, if there are 5 pages of items, then the filter is applied to all pages. The users sees the results immediately.

But with server-side binding where the ko.observableArray is populated with a single page of items, then the filter is only applied to the visible page. This is the primary reason I added the Refresh button to my implementation of the grid. The user can type a filter string and then click on Refresh in order to update the product count and first page of products that match the filter.

If your users do not like the way the filter works then it is possible to modify the HTML template used by KoGrid to implement the filter. Possibly to add a filter button along with the filter and have the view model and view updated when the user clicks on the button rather than whilst they are typing a filter string. However, I have avoided doing this so far as KoGrid defines the filter HTML as part of the default grid template (window.kg.defaultGridTemplate). The only way to change the filter HTML is to overwrite this template. At which point we are essentially modifying the KoGrid library and so may hurt backwards compatibility.

What’s Next?

In my next post I’m going to expand on this solution by adding Add, Edit, and Delete functionality to the grid.

 

 

 

Advertisements

2 thoughts on “Server side paging with KoGrid

  1. Ross, Many thanks for sharing this post. I have found it very helpful but I am puzzled by one line in your Api controller: “query = query.OrderBy(sort == null ? “name asc” : sort).Skip((page – 1) * pageSize).Take(pageSize);” The “OrderBy”method of IQueryable takes a lambda so how did you manage to compile when passing a string?
    Thanks, Rob.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s