Server-side Data Filtering, Sorting, and Paging with AngularJS, trNgGrid, ngTable, and Web API

• Download source - 808.7 KB

Introduction

Server-side data filtering, sorting, and paging are not a new topic, even in the AngularJS world. Many AngularJS grid tools are available nowadays but very few decent samples can be found using a grid tool for the server-side paginated data set like using the jqGrid in JQuery. This article presents a sample of a pure HTML 5 and AngularJS SPA website implemented with a search module for data filtering and two AngularJS grids, trNgGrid and ngTable, for requesting and displaying the data. A complete ASP.NET Web API application is attached for demonstrating how the filtered, sorted and paged data items are provided from the server.

Building and Running Sample Application

Before running the downloaded sample application, make sure that you have these prerequisites on your local machine.

• Visual Studio 2012 or 2013 with the IIS Express installed by default.
• SQL Server 2012 or 2014 LocalDB.
• Internet connection for automatically downloading all necessary libraries required by the Web API from the NuGet.

The projects in the Visual Studio solution are structured as shown below. Normally, the Web API project groups and client UI website should be in separate solutions. For easy setup and running, all projects and websites are placed in the same solution.

After opening the solution in the Visual Studio, you need to take these steps with the Solution Explorer.

• Right click the SM.Store.Api.Web project, select Properties, and then go to the Web section on the Properties page. The IIS Express is configured under the Servers section. Only thing you need to do is to click once the Create Virtual Directory button here. This will link the local project location to the IIS Express site.

• Right click on the index.html under the SM.Store.Api.Web project and then select the View in Browser menu command. This will start the IIS Express and the Web API host site, automatically create the database in your LocalDB instance, and populate tables with all needed sample data. Closing the index.html test page will not affect the running of Web API site.

• If the Web API site is stopped and you need to re-start it any time after the database has been initialized, you can simply run the following command prompt:

  "C:\Program Files (x86)\IIS Express\iisexpress.exe" /site:SM.Store.Api.Web

• When the Web API site is running, you can start the client website by opening the index.html under the SM.Store.Client.Web project. The sample application uses the proprietary search module for the data filtering, rather than the built-in filtering options in most AngularJS grid tools for each field. Enter any search criteria values and click the Go button, the data grid with the pager and field sorting headers will be loaded as shown below:

  

Placeholder of AngularJS Dropdown List

In JQuery, the placeholder "Please select..." can be added into the source data list and the display style be set without any timing issue. In AngularJS, there is also no timing issue when using the local data source to feed a dropdown list. An example of data items obtained from a local data service provider is like this in the sample application.

angular.module('smApp.AppServices').service('LocalData', [function () {
    //Local data for product search types.
    this.getProductSearchTypes = function () {
        return [
          { id: "0", name: "Please select..." },
          { id: "CategoryId", name: "Category ID" },
          { id: "CategoryName", name: "Category Name" },
          { id: "ProductId", name: "Product ID" },
          { id: "ProductName", name: "Product Name" }
        ];
    }   
}]);

The code for binding the data to directives and toggling CSS classes can then be written as the ng-options and the custom options-class directives.

<select id="ddlSearchType" class="form-control placeholder-color"
    ng-model="model.pSearchType.selected"
    ng-options="item.id as item.name for item in model.productSearchTypes"
    options-class="{'placeholder-color':'placeholder', 'control-color':'data'}"
    ng-change="changeDdlClass('ddlSearchType')"
</select>

For any list data source retrieved from databases, the custom directive options-class only works if watching cycles are added to wait for the data returned from the AJAX call. Using watching cycles to resolve the timing issue is the nature of the AngularJS directives but too many watching cycles impact the application performance. To avoid watching cycles in this custom directive, the dropdown list is initially loaded using the option tags with ng-repeat directive. In this case, the placeholder item needs to be added as the first option element with the default CSS class applied.

<select id="ddlProductStatusType" class="form-control placeholder-color"
    ng-model="model.pStatusType.selected"
    ng-change="changeDdlClass('ddlProductStatusType')"
    <option value="0" class="placeholder-color">Please select...</option>
    <option ng-selected="{{item.StatusCode == model.pStatusType}}"
        ng-repeat="item in model.productStatusTypes"
        value="{{item.StatusCode}}"
        class="control-color">
        {{item.Description}}
    </option>
</select>

UI Bootstrap Datepicker

The UI Bootstrap Datepiker is a pure AngularJS component without JQuery references. The sample application uses this component for the Date From and Date To field input on the Search Products panel. Most configurations can directly be done by setting the directive values in the HTML input elements.

<input type="text" ng-model="search.pAvailableFrom" 
    class="form-control" 
    id="txtFirstAvailFrom" 
    placeholder="Date from"
    datepicker-popup="{{format}}" 
    is-open="openedFrom" 
    min-date="'2000-01-01'" 
    max-date="'2020-12-31'" 
    datepicker-options="dateOptions"
    show-button-bar="false" 
    ng-required="true" >

To mutual-exclusively open a date picker, a pair of flags is set from the functions in the controller, which is called by clicking the Datepicker buttons.

$scope.openFrom = function ($event) {
    $event.preventDefault();
    $event.stopPropagation();
    $scope.openedFrom = true;
    $scope.openedTo = false;
};
$scope.openTo = function ($event) {
    $event.preventDefault();
    $event.stopPropagation();
    $scope.openedTo = true;
    $scope.openedFrom = false;
};

Requesting Data with Filtering, Sorting, and Paging Parameters

The request for filtered, sorted, and paged data is sent to the Web API from the AngularJS controller via the ngResource data service.

var webApiBaseUrl = "http://localhost:10611/api/";

angular.module('smApp.AppServices', ['ngResource'])

//Web API call for product list.
.factory('ProductList', function ($resource) {
    return $resource(webApiBaseUrl + 'getproductlist_p', {}, {
        post: {
            method: 'POST', isArray: false,
            headers: { 'Content-Type': 'application/json' }
        }
    });
})

The post method is called in the controller by directly passing the filterJson.json, a JSON formatted string containing input parameters.

ProductList.post(filterJson.json, function (data) {...}, function (error) {...});

The parameters basically contain two parts:

• Data search criteria. These are parameters on which the Web API methods or database stored procedures are based to retrieve the data result sets. These parameters can be more or less depending on the data filtering requirements.
• Paging and sorting parameters. These items are quite standard for a paginated data result set, mainly the starting record index, number of records per page, sorting field name, and sorting direction (ascending or descending).

Below is the object structure of the filter parameter tree used in the sample application.

The function, getFilterJson, generates the formatted JSON string based on the above object. Constructing the JSON string is also easy for validating the multiple mutually-dependent data inputs, such as Date From and Date To, or Price Low and Price High. Readers can see the code in the controller.js for details.

Processing Data Request in Web API

The JSON string will be attached to the HTTP form body and sent to the server. It will automatically be parsed and converted back to the object by the method Post_GetProductList in the Web API controller. An instance of the GetProductsBySearchRequest object holds all parameters as its properties which are then passed to the business logic and data layers for retrieving the matching results.

[Route("~/api/getproductlist_p")]
public ProductListResponse Post_GetProductList([FromBody] GetProductsBySearchRequest request)
{
    //Parse the request object and call for data...
}

Further processes in the Web API project use the Entity Framework with the LINQ to SQL, instead of calling a stored procedure, to retrieve the filtered, sorted, and paged data result set. Constructing the LINQ query with the filtering criteria is simplified by using the PredicateBuilder class whereas the sorting and paging processing logic is mainly done by using the GenericSorterPager class. Both class files are in the SM.Store.Api.Common project.

AngularJS Grids for Server-side Pagination Data

Many high quality AngularJS gird tools are available today. Among them, the ngGrid, ngTable, SmartTable, and trNgGrid are the most popular ones with server-side pagination features. Based on results from my studies, the ngGrid has a theme dependency on the JQuery library although it's maintained by the Angular UI team. The SmartTable needs more custom coding efforts than other grid tools to make the server-side pagination work. It turns out that the trNgGrid and ngTable are the better candidates for the data sets with server-side sorting and paging functionality since we need grid tools with rich functionality and easy to use but without any external JQuery dependency.

The sample application provides code examples with both trNgGrid and ngTable tools. There are advantages and disadvantages for both tools. Basically, the trNgGrid is easier to use but ngTable is more flexible. Some details regarding incorporation of these two grid tools are described in the following sections.

Using trNgGrid

The trNgGrid provides the full set of directives for server-side sorting and paging features. We just need to add the tr-ng-grid directive with an empty value into a table element and then configure the other directives with appropriate values. The pagination-related directives are:

• current-page: the zero-based paging number. The value can be set from a scope variable.
• page-items: the paging size, a.k.a., the number of records in a page.
• total-items: the total record count. The value will be post-assigned after the data is obtained from the server.
• on-data-required: the event handler used to send the request to server by calling a function in the controller. It can be triggered by any change in the current-page, page-items, total-items, or clicking any sorting button from column headers.

The sample application sets these directive values in the table element. Note that the built-in column filtering option is disabled.

<table id="tblProductList" 
    tr-ng-grid="" 
    items="model.productList"    
    selected-items="mySelectedItems" 
    selection-mode="SingleRow"
    enable-filtering="false" 
    on-data-required-delay="1000" 
    current-page="setCurrentPage"
    page-items="model.pPageSizeObj.selected" 
    total-items="model.totalProductCount"
    on-data-required="onServerSideItemsRequested
       (currentPage, pageItems, filterBy, filterByFields, orderBy, orderByReverse)">

The onServerSideItemsRequested function in the controller receives all needed parameters and then sends the request to the Web API.

//Called from on-data-required directive.
$scope.onServerSideItemsRequested = 
     function (currentPage, pageItems, filterBy, filterByFields, orderBy, orderByReverse) {
    loadProductList(currentPage, pageItems, orderBy, orderByReverse);
}
//Ajax call for list data.
var loadProductList = function (currentPage, pageItems, orderBy, orderByReverse) {
    //Get JSON string for parameters.           
    var filterJson = getFilterJson();
    //Call data service.
    ProductList.post(filterJson.json, 
	function (data) {
            $scope.model.productList = data.Products;
            $scope.model.totalProductCount = data.TotalCount;
        }, 
	function (error) {
            alert("Error getting product list data.");
        }
    );
    $scope.showProductList = true;
}

When current paging number is not 1, re-selecting the paging size or sorting parameters should reset the paging number back to 1 for any new data list loading. This effect can be achieved by resetting the current-page directive to 0 using the scope variable setCurrentPage. Since resetting the paging number will call the onServerSideItemsRequested function again, any process already related to the server call needs to be bypassed.

//Called from search Go button.
$scope.clickGo = function () {
    if ($scope.setCurrentPage != 0) {
        //Directive current-page value change will auto call onServerSideItemsRequested().
        $scope.setCurrentPage = 0;
    }
    else {
        loadProductList(pCurrentPage, pPageItems, pOrderBy, pOrderByReverse);
    }
}
//When page size is changed from dropdown in Pager.
$scope.changePageSize = function () {
    //If page size changed from ddl, set back to first page. 
    //This will auto call onServerSideItemsRequested().
    if (!resetSearchFlag) {
        $scope.setCurrentPage = 0;
    }
    else {
        resetSearchFlag = false;
    }
}

The downside of using the trNgGrid is that the table element iterates the model records inside the library file and no regular <tr> and <td> tags are available in the HTML markup. Thus we cannot use the normal ng-repeat structures for data programming. For example, it's not possible to make the text as a hyperlink in the HTML markup if we don't want to add hyperlink text into the resulted data from the controller. To execute a command related to a data row, we can have the watch cycles detect the newly selected item without distinguishing which column in the row is selected.

//Action of clicking grid row.
$scope.$watch("mySelectedItems[0]", function (newVal, oldVal) {
    var val;
    if (newVal != oldVal) {
        if (newVal == undefined && oldVal) val = oldVal;
        else val = newVal;
        alert("You selected product ID: " + val.ProductID);
    }
});

Using ngTable

The ngTable doesn't expose all paging parameters and any reachable event handler for a server data request as directives. Instead, it creates a JavaScript object, tableParams, including all parameters for the sorting and paging options, plus the settings object for other needed data items and operations. The tableParams object is then passed to the top-level directive ng-table in the table element. Developers can manipulate these parameters in JavaScript and write the code in the getData function to request for sorted and paged data. The HTML markup for the ngTable is pretty simple:

<table ng-table="tableParams" template-pagination="/Templates/ngTablePager.html" >

The code in the controller, however, looks much more complex. The loadProductList function is called by clicking the search Go button for initial data loading. Any subsequent change in properties of the tableParamter object will re-trigger the getData function and call the Web API for refreshing the data in the grid. Since the definition of the getData function is defined inside the library file and the function is automatically called by any change in the members of the tableParams object, any data request sent by non-parameter changes, for example, clicking the search Go button again, needs a simulate change in one of the parameter values. It's also necessary to set flag variable for bypassing the call triggered by the simulate parameter change. See the comment lines in the code for detailed explanations.

//Called from search Go button.
$scope.clickGo = function () {
    searchFlag = true;
    loadProductList();
}	
//Called from clicking search Go button. The getData will be called from any change of params.
var loadProductList = function () {
    //Set default values.
    pageIndex = 0;
    pageSize = pageSizeSelectedDef;

    //Subsequent clicking search Go button.
    if ($scope.tableParams != undefined) {
        //Leave same pageSize when called after changing search filter items.
        pageSize = $scope.tableParams.count();

        //Set param count differently from the current to trigger getData but bypass it.
        //The actual process still use pageSize value not this changed count.
        reSearchFlag = true;
        $scope.tableParams.count($scope.tableParams.count() + 1);
    }
        
    //Set ng-table parameters initially.
    $scope.tableParams = new ngTableParams({
        page: pageIndex + 1, // Page number
        count: pageSize,     // Count per page
        sorting: {}
    }, {
        defaultSort: 'asc',
        total: 0,                       
        countOptions: pageSizeList,
        countSelected: pageSize,
        //getData will also be called from ng-table.js whenever params changed
        getData: function ($defer, params) {
            if (!reSearchFlag) {
                if (!searchFlag) {
                    //Retrieve changed params from pager and sorter for AJAX call input            
                    pageIndex = params.page() - 1;

                    //Go to page #1 if change page size. 
                    if (pageSize != params.count()) {
                        pageSize = params.count();
                        params.page(1);
                    }
                    sortBy = Object.getOwnPropertyNames(params.sorting())[0]
                    //Go to page #1 if change sorting on any column.
                    if (sortBy != undefined && sortBy != "") {
                        if (sorting !== params.sorting()) {
                            sorting = params.sorting();
                            sortDirection = sorting[sortBy] == "asc" ? 0 : 1;
                            params.page(1);
                        }
                    }
                    else {
                        sortBy = "";
                        sortDirection = 0;
                    }
                }
                else {
                    searchFlag = false;
                }
                var filterJson = getFilterJson();                
                ProductList.post(filterJson.json, function (data) {
                    //$scope.model.productList = data.Products;
                    $timeout(function () {
                        //Update table params.
                        params.total(data.TotalCount);
                        //Set start and end page numbers.
                        if (pageIndex == 0) {
                            params.settings().startItemNumber = 1;
                        }
                        else {
                            params.settings().startItemNumber = 
                                       pageIndex * params.settings().countSelected + 1;
                        }
                        params.settings().endItemNumber = 
                                       params.settings().startItemNumber + data.Products.length - 1;
                        //Set new data.
                        $defer.resolve(data.Products);
                        //Show grid.
                        $scope.showProductList = true;
                    }, 500);
                }, function (error) {
                       alert("Error getting product list data.");
                });               
            }
            else
            {
                //Reset re-search flag.
                reSearchFlag = false;                   
            }
        }
    });       
}

Unlike the trNgGrid in which a pager can be set in the HTML tfoot element separated from the main tr-ng-grid directive, the pager for the ngTable uses the template-pagination directive that is coupled with the ng-table directive and the settings object. Scope variables defined outside of the ngTable library code cannot easily act on the pager. Thus the ngTable.js library file needs to be modified with some properties of the settings object for a paging number selection UI (such as a dropdown list), and the interactive total count display in the pager. In the sample application, these members shown in bold below are added into the settings object variable in the original ngTable.js file:

var settings = {
    $scope: null, // set by ngTable controller
    $loading: false,
    data: null, //allows data to be set when table is initialized
    total: 0,
    defaultSort: 'desc',
    filterDelay: 750,
    counts: [10, 25, 50, 100],
    countOptions: {},
    countSelected: 0,
    startItemNumber: 0,
    endItemNumber: 0,
    getGroups: this.getGroups,
    getData: this.getData
};

The pager template can then use the object data for the normal functionality.

<div class="ng-cloak ng-table-pager">
    <!--Paging size dropdown list-->
    <div class="pull-left">
        <select id="ddlPageSize" class="form-control form-ddl-adj"
                ng-model="params.settings().countSelected"
                ng-options="item.value as item.text for item in params.settings().countOptions"
                ng-change="params.count(params.settings().countSelected)"></select>
        <span><span class="pager-label 
        page-label-down">&#160;&#160;items per page</span></span>
    </div>
    <ul class="pagination ng-table-pagination pull-right">
        <!--Page number buttons-->
        <li ng-class="{'disabled': !page.active && 
        !page.current, 'active': page.current}"
            ng-repeat="page in pages" ng-switch="page.type">
            <a ng-switch-when="prev" ng-click="params.page(page.number)" 
            href="">&laquo;</a>
            <a ng-switch-when="first" ng-click="params.page(page.number)" 
            href=""><span ng-bind="page.number"></span></a>
            <a ng-switch-when="page" ng-click="params.page(page.number)" 
            href=""><span ng-bind="page.number"></span></a>
            <a ng-switch-when="more" ng-click="params.page(page.number)" 
            href="">&#8230;</a>
            <a ng-switch-when="last" ng-click="params.page(page.number)" 
            href=""><span ng-bind="page.number"></span></a>
            <a ng-switch-when="next" ng-click="params.page(page.number)" 
            href="">&raquo;</a>
        </li>
        <!--Page status messages-->
        <li>
            <span ng-class="{show: params.total() < 1, 
            hidden: params.total() > 0}" >No items to display</span>
            <span ng-class="{show: params.total() > 0, 
            hidden: params.total() < 1}" class="pager-label" 
            ng-attr-title="{{'Display Info'}}">
                {{params.settings().startItemNumber}} - 
                  {{params.settings().endItemNumber}} {{'displayed'}}
                <span>, {{params.total()}} {{'in total'}}</span>
            </span>
        </li>
    </ul>
</div>

The ngTable uses the native table-related HTML tags for the grid structure which provides the flexible approaches for column styles, text format, and hyperlinks. For example, the product names in the grid can be set as hyperlinks for initiating a subsequent request, such as opening a product detail page or dialog. Note that the scope object paging is defined here for the function openProductForm accessible from the child scope through the prototype inheritance.

In the HTML markup:

<tr ng-repeat="item in $data">
    <td data-title="'Product Name'" sortable="'ProductName'" width="120px">                    
        <a ng-click="paging.openProductForm(item.ProductID)" 
                     class="cursor-pointer">{{item.ProductName}}</a>
    </td>
    - - -                 
</tr>

In the controller:

//For communicating with ng-table scope through prototype inheritance.  
$scope.paging = {};

//Action of clicking product name link.
$scope.paging.openProductForm = function (id) {
    alert("You selected product ID: " + id);
}

Summary

Due to the directive oriented nature, implementing server-side filtering, sorting, and paging for a data list in AngularJS is quite different from achieving the same results using the JQuery. This article provides the full samples of an AngularJS client with a search module, popular grid tools, and the Web API as the filtered, sorted, and paged data provider. The article also describes the pros and cons between the trNgGrid and ngTable when using the AngularJS data grid tools.