Every enterprise application features some sort of the grid. Grid being an interactive table that presents some list of data with rows and columns. Most used features of the grid are paging, sorting and filtering. There are a lot of open source and proprietary grid components available for the web build on top of jQuery and/or AngularJS. Some of them are quite heavy of code and have steep learning curve. Moreover, none of them makes any assumptions about your server-side code, so they either work only on client requiring you to load all data to client beforehand or require you to write some sort of adapter functions that translate client-side messages to server-side messages.
In the article I will show you how easy it is to build a simple table featuring paging, sorting and filtering with AngularJS on client-side and ASP.NET Web API with OData V4 on server-side without the use of any complex grid component. All data querying is happening on database server to ensure maximum performance. The techniques I use to convert the HTML table with Angular’s ng-repeat to an interactive grid take only around 125 easily readable lines of TypeScript code and some CSS. I assume that reader is familiar with following technologies and techniques:
- ASP.NET MVC and knows how to use script bundles and configure routes;
- Entity Framework and knows to configure connection strings;
- TypeScript and understands how to compile a source code file and reference external type definitions;
- AngularJS and understands how to build custom directives.
Create an empty ASP.NET Web API project with Visual Studio. Update existing packages by running update-package command in NuGet package manager console and install following NuGet packages:
- Microsoft.AspNet.OData – Microsoft ASP.NET Web API 2.2 for OData v4.0
- EntityFramework
- AngularJS
- angularjs.TypeScript.DefinitelyTyped
- Bootstrap
- TypeLite
The last one will be used to create strongly type TypeScript model. More on that later.
Create a data model class:
public class Customer
{
[Key,StringLength(5)]
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Region { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
}
Create a data context:
public class SimpleDbContext : DbContext
{
public IDbSet<Customer> Customers { get; set; }
}
Now we will create a Web API Controller named CustomerController that will look like:
…
using System.Web.OData;
using System.Web.OData.Query;
public class CustomerController : ApiController
{
private SimpleDbContext _context = new SimpleDbContext();
[EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
public IQueryable<Customer> Get()
{
return _context.Customers;
}
protected override void Dispose(bool disposing){…}
}
Notice the use of EnableQueryAttribute on the Get method. It makes any IQueryable you return from the controller to be queryable from client side using OData query option conventions. Attribute invokes the method which parses query string received from client that turns them into LINQ expressions that are then applied to your the IQueryable you have provided as a result of this method.
For example, if you want to request first 20 entities using OData conventions you will append $top=20 to the query string, which will be translated to LINQ query
context.Customers.Take(20) and later on to SQL Query SELECT TOP 20 … FROM Customer.
Now we have a fully functional end-point that we can use to query data about customers including paging, sorting and filtering. Let us play a little bit with it before we start building a user interface on client side. I have just filled the table with data from demo Northwind database to have some data to work with.
I use Postman Chrome extension to test REST queries. Fiddler can also be used for this purpose.
If we run following query http://localhost/api/Customer?$top=10 we will receive first 10 customers in JSON format:
[
{
"CustomerID": "ALFKI",
"CompanyName": "Alfreds Futterkiste",
"ContactName": "Maria Anders",
"ContactTitle": "Sales Representative",
"Address": "Obere Str. 57",
"City": "Berlin",
"Region": null,
"PostalCode": "12209",
"Country": "Germany",
"Phone": "030-0074321",
"Fax": "030-0076545"
},
…
{
"CustomerID": "BOTTM",
"CompanyName": "Bottom-Dollar Markets",
"ContactName": "Elizabeth Lincoln",
"ContactTitle": "Accounting Manager",
"Address": "23 Tsawassen Blvd.",
"City": "Tsawassen",
"Region": "BC",
"PostalCode": "T2F 8M4",
"Country": "Canada",
"Phone": "(604) 555-4729",
"Fax": "(604) 555-3745"
}
]
Now let us try more complex query http://localhost/api/Customer?$top=10& $skip=10& $orderby=ContactName& $select=CustomerID,ContactName,ContactTitle,Region,City& $filter=contains(ContactTitle,'Manager'). In addition to top option, it also have following query options:
The above query will return records from 10 to 19 of customer objects with only five fields (CustomerID, ContactName, ContactTitle, Region, City) that have Manager in their contact title and sorted by their Contact Name.
- Skip – skip first N items. Used for paging in conjunction with Top
- Order by – sorts the records by specified field(s).
- Select – returns only specified fields of the entity
- Filter – provides the expression predicate to filter data.
The above query will return records from 10 to 19 of customer objects with only five fields (CustomerID, ContactName, ContactTitle, Region, City) that have Manager in their contact title and sorted by their Contact Name.
[
{
"City": "Portland",
"Region": "OR",
"ContactTitle": "Sales Manager",
"ContactName": "Fran Wilson",
"CustomerID": "LONEP"
},
…
{
"City": "Walla Walla",
"Region": "WA",
"ContactTitle": "Marketing Manager",
"ContactName": "John Steel",
"CustomerID": "LAZYK"
}
]
If you add, logging to your entity framework data context to see actual SQL query you can confirm that all the query parameters are translated to the SQL query. I will not include the SQL here for brevity.
Now we will employ these OData mechanisms to build our simple grid. Let us start with paging first. In fact, it will not be paging per se but an infinite scroll implementation. A few words of why infinite scroll is in a fashion nowadays. The answer is simple: paging is not a convenient way to search for the data in the advent of searching capabilities of modern information systems. Try to remember how often you go the second page of the google search results. If you do not find what you are looking for on the first page you will simply add more specific search keywords to the query. The idea here is to provide the same level of convenience in your system by allowing user to use filters and sorting tools to find the records they were looking for faster. The infinite scroll signals user that there is more data available and allows user to get to scroll to other pages but does not encourage him/her to do so.
Let us create some mark-up for the table:
<div class="container-fluid" ng-app="app" ng-controller="CustomerCtrl">
<table class="table table-bordered table-condensed table-striped">
<thead>
<tr>
<th>ID</th>
<th>Company Name</th>
<th>Contact Name</th>
<th>Country</th>
<th>City</th>
</tr>
</thead>
<tbody>
<tr ng-repeat="customer in customers">
<td>{{customer.CustomerID}}</td>
<td>{{customer.CompanyName}}</td>
<td>{{customer.ContactName}}</td>
<td>{{customer.Country}}</td>
<td>{{customer.City}}</td>
</tr>
</tbody>
</table>
</div>
I will use TypeScript instead of JavaScript to demonstrate client-side code and recommend you to do the same since it has very powerful intellisense capabilities and compile time type checking, not mentioning the fact that AngularJS 2.0 is being developed using TypeScript. I have also used a TypeLite NuGet package with TT template to generate my data transfer object (just Customer for our simple case) definitions in TypeScript from corresponding class in C#. Do not forget to download current TypeScript compiler from http://www.typescriptlang.org/. I am using version 1.4.
When you install TypeLite package two files will be added to your Scripts folder: TypeLite.tt and Manager.ttinclude. Copy both files to typing subfolder, so that Visual Studio’s TypeScript editor automatically recognizes generated models. It TT file modify replace ForLoadedAssemblies() method call with For<Customer>() method call. Do not forget to import appropriate namespace in the files header: <#@ import namespace="adlordy.SimpleTable.Models" #>.
Now we need to create AngularJS controller that will bind data for us.
module app {
"use strict";
type Customer = adlordy.SimpleTable.Models.Customer;
export interface IQuery {
$top?: number;
$skip: number;
$orderby?: string;
$filter?: string;
}
export class CustomerSvc {
public static $inject = ['$http'];
constructor(private $http: ng.IHttpService) {
}
getAll(query: IQuery = null) {
return this.$http.get<Customer[]>("api/Customer", { params: query }).then((response)=>{
return response.data;
});
}
}
export interface CustomerScope extends ng.IScope {
customers: Customer[];
}
export class CustomerCtrl {
public static $inject = ['$scope','CustomerSvc'];
constructor(private $scope: CustomerScope,
private customerService: CustomerSvc
) {
customerService.getAll({$top:20,$skip:0}).then((data) => {
$scope.customers = data;
});
}
}
}
Please note that I include all the code in one place just for simplicity. The service code and IQuery interface should be located in the separate files. Do not forget to create application module:
module app {
"use strict";
angular.module('app', [])
.service('CustomerSvc',CustomerSvc)
.controller('CustomerCtrl', CustomerCtrl);
}
At this point, our application is able to render static table with first 20 rows:
.table-overflow{
width:auto;
}
.table-overflow > thead > tr,
.table-overflow > tfoot > tr{
display: block;
position: relative;
}
.table-overflow > tbody {
display: block;
height: 500px;
overflow-y: auto;
}
.table-overflow td, .table-overflow th {
width: 140px;
}
.table-overflow td{
-ms-word-wrap: break-word;
word-wrap: break-word;
-ms-word-break: break-word;
word-break: break-word;
}
.table-overflow th:last-child {
width: 17px;
}
I would agree that it is nasty hack but it works. Sort of. Your table is not a real table anymore, so your body and headers cells does not belong to the same column and you might get header cells position mismatch. Notice that cell width is fixed now. To make some columns wider than the default width I use custom classes like table-col-xs, table-col-sm, table-col-md, table-col-lg, table-col-xl on both header cell and body cell:
.table .table-col-xs {
width: 60px;
}
…
.table .table-col-xl {
width: 180px;
}
Set those widths to whatever values that make more sense to your application. If this approach does not work for you just use a simple paging with next and previous buttons instead of the infinite scroll.
Create a directive simple table with a link function:
export function SimpleTableDirective($parse: ng.IParseService) {
return {
restrict: 'A',
controller: TableController,
link: function ($scope: TableScope, element: ng.IAugmentedJQuery, attrs: ng.IAttributes, controller: TableController) {
element.addClass("table-overflow");
// parse the loader function from attribute
var loader = $parse(attrs["simpleTable"])($scope);
controller.setLoader(loader);
controller.bindScroll(element);
controller.setTop(attrs["defaultTop"] || 20);
controller.load();
}
}
}
Add directive declaration to your module:
angular.module('app', [])
…
.directive('simpleTable', ['$parse', SimpleTableDirective])
…
.directive('simpleTable', ['$parse', SimpleTableDirective])
Here we simply parse out the attribute values and pass those values to the table controller that will contain the logic. Let us apply the attributes to the table in HTML.
<table class="table table-bordered table-condensed table-striped"
simple-table="load" default-top="20">
…
<tbody>
<tr ng-repeat="customer in $data">
…
</tr>
</tbody>
</table>
The directive will be applied with simple table attribute. I have also modified customers variable to the $data variable. The use of magic global variables is discouraged but I will use them for simplicity sake. The variable name can be configured by using some additional attribute and $parse service but I will leave this implementation details out of scope. Simple table attribute expects expression that specifies a function that will be used to request data from the server. It should take query as the parameter return promise with array of any data:
type LoaderFunc = (query: any) => ng.IPromise<any[]>;
We already have this kind of function in our CustomerService. All we need to do is set to the scope and bind it to the service instance in the controller. Bind is essential in order for a service method getAll to identify this reference correctly. Change your scope and controller code to:
export interface CustomerScope extends ng.IScope {
load(query:IQuery):ng.IPromise<Customer[]>;
}
export class CustomerCtrl {
public static $inject = ['$scope','CustomerSvc'];
constructor(private $scope: CustomerScope,
private customerService: CustomerSvc
) {
$scope.load = customerService.getAll.bind(customerService);
}
}
Now here is the implementation of the Table Controller:
export interface TableScope extends ng.IScope {
$data: any[];
}
export class TableController{
public static $inject = ['$scope'];
constructor(private $scope: TableScope) {
$scope.$data = null;
this.$query = { $skip: 0 };
}
private loader: LoaderFunc;
private $query: IQuery;
setLoader(loader:LoaderFunc) {
this.loader = loader;
}
setTop(top: number) {
this.$query.$top = top;
}
load(append = false) {
if (!append)
this.$query.$skip = 0;
this.loader(this.$query).then((data)=> {
if (!append)
this.$scope.$data = [];
this.$scope.$data = this.$scope.$data.concat(data);
this.$query.$skip += data.length;
});
}
bindScroll(element: ng.IAugmentedJQuery) {
var tbody = element.find("tbody");
var raw = tbody[0];
var loadMore = this.load.bind(this, true);
tbody.bind("scroll", ()=> {
if (raw.scrollTop + raw.offsetHeight >= raw.scrollHeight) {
this.$scope.$apply(loadMore);
}
});
}
}
The implementation for the most part is self-explanatory. A load function accepts the parameter that either append results to existing variable or first cleanses previous data. The second option will be later used to implement sorting. For now, bind scroll method listens to the scroll event of the tbody tag and only user scrolls to the bottom it invokes load function with append=true.
Now we have a functional infinite scroll that sends request for new page every time we reach the bottom of the scroll bar:
Next step is to add sorting to our already not so simple table. An additional directive Order By is used for this purpose. We apply it to any header cell that requires sorting like this:
<tr>
<th order-by="CustomerID">ID</th>
<th class="table-col-lg" order-by="CompanyName">Company Name</th>
<th class="table-col-lg" order-by="ContactName">Contact Name</th>
<th order-by="Country">Country</th>
<th order-by="City">City</th>
<th></th>
</tr>
The directive itself looks like this:
export interface OrderByScope extends ng.IScope {
orderBy();
fieldName: string;
dir: string;
isSorted(): boolean;
}
export function orderByDirective() {
return {
restrict: 'A',
require: "^simpleTable",
transclude: true,
scope: {
fieldName: "@orderBy",
dir: "@dir"
},
template: "<a ng-click='orderBy()' ng-transclude ng-class='{\"sort-asc\":dir==\"asc\",\"sort-desc\":dir==\"desc\",sort:isSorted()}'></a>",
link: function ($scope: OrderByScope, element: ng.IAugmentedJQuery, attrs: ng.IAttributes, controller: TableController) {
$scope.dir = $scope.dir || "desc";
$scope.orderBy = function () {
$scope.dir = $scope.dir == 'asc' ? 'desc' : 'asc';
controller.setOrderBy($scope.fieldName, $scope.dir);
}
$scope.isSorted = function () {
return controller.getOrderBy() == $scope.fieldName + " " + $scope.dir;
}
}
}
}
We require this directive to be used only inside simple table directive, so we can utilize Table Controller. The directive uses transclusion to wrap existing header cell content with anchor tag serving as a button. When the header is clicked we call orderBy function that sets appropriate direction (ascending or descending) and then call setOrderBy function on table controller. The isSorted function on the scope is used to set sort class on the header if the data is sorted by this field. Add following methods to Table Controller:
getOrderBy() {
return this.$query.$orderby;
}
setOrderBy(fieldName: string, dir: string) {
this.$query.$orderby = fieldName + " " + dir;
this.load(false);
}
Some more CSS to make it all work:
.sort.sort-asc::after {
content: "▲";
}
.sort.sort-desc::after {
content: "▼";
}
.sort::after {
position: absolute;
}
a.sort, a.sort-desc, a.sort-asc, a.sort:link, a.sort:visited, a.sort:active {
color:rgb(51, 51, 51);
cursor:pointer;
}
Do not forget to add the directive to the module:
angular.module('app', [])
.directive('orderBy', orderByDirective)
That is all that is required for sort to work:
Now we came to the most interesting part of how to implement filtering. As you may have figured out we will use build-in OData filtering capabilities to achieve that. It means that all we need to do is construct correct $filter expression and send it to server. The type of expression itself depends on the field type and filter control type. The table itself does not need to know anything about that, it simply will pass this filter expression to the server. All we add these lines to the directive link function:
var originalFilter = attrs["filter"];
attrs.$observe("filter", function (newValue) {
if (newValue != originalFilter) {
controller.setFilter(newValue);
} else {
controller.setFilter(undefined);
}
});
And replace last line with:
if (!originalFilter)
controller.load();
The load function will need to be invoked only if no filter attribute is set. The load will be invoked from the setFilter method otherwise. Method itself is easy enough:
setFilter(filter: string) {
if (filter)
this.$query.$filter = filter;
else
delete this.$query.$filter;
}
Say for the example, we would like to filter our list by occurrence of certain search string in the company name. We will create control to read the search string from the user and set its value to the filter expression. Add this markup before the table tag:
<div class="row">
<div class="form-horizontal">
<div class="col-sm-3">
<div class="input-group input-group-sm">
<input type="search" ng-model="companyName" placeholder="Company Name" class="form-control" ng-model-options="{debounce:500}" />
<label class="input-group-addon btn btn-default" ng-click="companyName = null">
<span class="glyphicon glyphicon-remove"></span>
</label>
</div>
</div>
</div>
</div>
<br/>
Important details are ng-model and ng-model-options. First one will used to set a scope variable later referenced in filter expression and former one is used to throttle request rate to the server to 500ms. Now all we need to do is specify filter attribute with the expression:
<table class="table table-bordered table-condensed table-striped"
simple-table="load" default-top="20" filter="contains(CompanyName,'{{companyName}}')">
With that done, we are now able to filter the list of customers by their name:
Building a complex filter is also easy enough. Instead of the expression in the filter attribute just specify the function with a logic to build a string. I will give an example:
$scope.filter = function () {
var filter = [];
if ($scope.companyName)
filter.push("contains(CompanyName,'" + $scope.companyName + "'");
if ($scope.city)
filter.push("City eq '" + $scope.city + "'");
if ($scope.country)
filter.push("Country eq '" + $scope.country + "'");
return filter.join(" and ");
}
<table class="table table-bordered table-condensed table-striped"
simple-table="load" default-top="20" filter="{{filter()}}')">
Here we get city and country fields from respective dropdowns.
<select ng-model="city" class="form-control" ng-options="city for city in cities">
<option value="">Select City:</option>
</select>
Now we a have an interactive table supporting paging, sorting and filtering that is lightweight and built from the scratch. You can start applying it to your application and extending it with application specific functionality without the need of reading multipage documents and understating complex API.
