Every enterprise application features some sort of the grid. A grid being is an interactive table that presents a list of data with rows and columns. The most used features of the grid are paging, sorting and filtering. There are a lot of open source and proprietary grid components available for web building on top of jQuery and/or AngularJS. Some of them are quite code heavy and have steep learning curve. Moreover, none of them makes any assumptions about your server-side code, so they either work only on a client requiring you to load all data to the 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 the client-side and ASP.NET Web API with OData V4 on the server-side without the use of any complex grid component. All data querying is happening on a database server to ensure maximum performance. The techniques I use to convert the HTML table with Angular’s ng-repeat to an interactive grid only take around 125 easily readable lines of TypeScript code and some CSS. I assume that the reader is familiar with following technologies and techniques:
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:
The last one will be used to create strongly type TypeScript model. More on that later.
Create a data model class:
Create a data context:
Now we will create a Web API Controller named CustomerController that will look like this:
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 the client that turns them into LINQ expressions that are then applied to 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 the following query http://localhost/api/Customer?$top=10 we will receive the first 10 customers in JSON format:
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.
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 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 to 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 the user to use filters and sorting tools to find the records they were looking for faster. The infinite scroll signals the user that there is more data available and allows him to scroll to other pages but does not encourage him/her to do so.
Let us create some mark-up for the table:
Now we need to create an AngularJS controller that will bind data for us.
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:
At this point, our application is able to render static table with first 20 rows:
Now we add some CSS to make the table body scrollable and keep headers fixed. To do that we will add table-overflow class to the table and add an extra empty TH (<th></th>) to the end of header row.
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 header cells do 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:
Create a directive simple table with a link function:
Add directive declaration to your module:
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 an 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 require this directive to be used only inside the simple table directive, so we can utilize Table Controller. The directive uses transclusion to wrap existing header cell content with an 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:
That is all that is required for sort to work:
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. The method itself is easy enough:
Important details are ng-model and ng-model-options. The first one will be used to set a scope variable later referenced in the filter expression and the former one is used to throttle request rate to the server to 500ms. Now all we need to do is specify a filter attribute with the expression:
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:
Here we get city and country fields from respective dropdowns.
Now we a have an interactive table supporting paging, sorting and filtering that is lightweight and built from 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.