No Need for a Grid
No Need for a Grid
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:
- ASP.NET MVC and knows how to use script bundles and configure routes;
- Entity Framework and knows how 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
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:
Now let us try a 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 the top option, it also has the following query options:
- 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.
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:
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 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:
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:
Add directive declaration to your module:
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.
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 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:
Now here is the implementation of the Table Controller:
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 the user scrolls to the bottom it invokes load function with append=true.
Now we have a functional infinite scroll that sends a request for a 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:
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:
Now we come 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 are these lines to the directive link function:
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:
Say for the example, we would like to filter our list by occurrence of a 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:
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.
Specialist in Software Development and Architecture (.NET)