The Common Problems of Data Grids

Data grids are easy to do in HTML, and if we want anything complicated in terms of scrolling, locking and data entry, we usually shrug and buy a component to do it. However, there is a lot to be said for the approach of creating tables using a basic JavaScript platform such as jQuery, perhaps using a plugin which you can then customise. Dino talks you through this approach, showing how to take the DataTables plugin and implement locked columns, on-demand paging and filtering.

There have always been data grids in software. The output of an MS-DOS dir command is a grid, after all. The results of any query in any language is displayed as a grid. Every web application has a grid tucked away somewhere in at least one of its pages. Any web framework provides facilities for building grids. Speaking personally, I once built a career on digging out the hidden capabilities of the DataGrid server control in ASP.NET Web Forms. With ASP.NET MVC and the supremacy of explicit HTML over black-box components, the task of building a data grid has become just the same as building a table of data—any data. All that is required is to be able to loop over a collection of objects with a few public and serializable properties.

Although data grids are common and relatively easy to set up, they only become really useful if they support the features that are common to many applications, such as locked columns and headers, paging, scrolling, filtering, and sorting. There are many commercial products that create grids for you with all the features that one can imagine; If one of these products meet all your requirements, it makes a lot of sense to get a commercial product before embarking in a large web project that requires a sophisticated user interface. It can save a great deal of time and effort.

If this was all to be said, this would be a short article. However, the problems start to manifest themselves if you find that you need to make some relevant changes to the “conventional” behavior. Without doubt third-party frameworks are highly customizable but they require you understand their internal architecture and language. This is easier for a large enterprise development to deal with, because there is more likely to be a local guru for the framework that you’re using, but for small teams as well as for your personal engagement it is still worth looking at the alternative strategy of a handcrafted solution based on a simple framework such as JQuery. Data grids always take a bit of thought but they aren’t intrinsically difficult. In this article, I’ll discuss how to turn a HTML table of data into a relatively advanced data grid using custom code as well as the minimum of external dependencies in the form of free jQuery plugins. To illustrate the task of adding functionality to a grid, I’ll show you, in this article, how to implement locked columns, on-demand paging and filtering.

Locked Columns and Headers

Although HTML allows you to define headers in a table as a distinct section, it doesn’t actually support an attribute that could tell browsers to lock the header and/or the footer while the user scrolls vertically. It would be much easier for the browser engine to add this feature than for developers to hack the DOM and play dirty tricks with additional DIV elements and fake tables. The state of the art is that freezing the header is definitely possible but it comes at the cost of client-side tricks to be played each and every time you render a table. Thankfully, we have jQuery plugins that come to the rescue. My favorite plugin is DataTables that you find at http://datatables.net.

Interestingly, the plugin is not limited to fixed headers and columns but provides the foundation for a rich JavaScript-driven data grid framework with support for filtering, sorting and more. Here’s the sample HTML table we’ll consider. As you can see, it has no special attributes and is just a plain standard HTML table built in Razor from a collection of Country objects. The table element is also styled using plain Bootstrap classes.

The table is designed to take all the space that is available horizontally; if you need to control the width that it takes on screen, you might want to use a container DIV and size it properly.

There are a couple of things to do in order to enable the DataTables plugin. First off, you should invoke the plugin via JavaScript.

Second, you add the following snippet of CSS to ensure appropriate results.

The scrollY attribute of the plugin sets the height of the table and determines whether a vertical scrollbar is necessary. The scrollX attribute does the same to enable horizontal scrolling if necessary. Nothing else is required and the plugin will just give you a fixed header and a vertically scrollable table. Note that, internally, the plugin does a number of things, and I strongly recommend that you set the background color of the table explicitly to avoid nasty visual effects.

The fixedColumns property in the plugin initialization code is required only if you also want locked columns that don’t scroll horizontally. The property can take two types of values. If set to a Boolean value (true) then it locks only the first column. As an alternative, you can set the property to a JavaScript object with two properties—leftColumns and rightColumns—both accepting an integer. The integer value indicates how many columns on the left and right should be frozen. To enable column locking, you also need to include the following extension for the DataTable plugin:

You’ll find the link to the download right on the http://datatables.net web site. The figure below shows the plugin in action.

If you only need to freeze the header and don’t care about locked columns during horizontal scrolling, then you can also achieve the same effect using plain CSS. In this case, though, it is required that the width of each column is indicated (or all the columns are of the same explicit size). Here’s the CSS you need.

As you can see, height of the table and width of columns must be explicit in the CSS. Those measurements are used also within the plugin but the JavaScript code just hides most of it. If different columns are expected to have different width, then you can use a more precise CSS selector to set it:

To lock the header, just style the table with the fixed CSS class.

On-demand Paging

Most social networks let you page through posts and users simply by hitting the bottom of the page. The implications of this couldn’t be clearer: The user is saying “I’ve reached the end of the list, just show me more”. To implement on-demand paging, all you need is a small piece of JavaScript that hooks up the window’s scroll event, as below:

The JavaScript snippet depends on three parameters. First, the number of data items to retrieve for each page scroll. In the sample code, I have that number set to the JavaScript variable pageSize through a server-side Razor variable. Second, the URL to call to get more data. Needless to say, the URL must refer to a HTTP endpoint that knows how to retrieve data that fit into the table. In the example, the URL points to the More method on the Country controller designed as below:

In this case, the controller method returns plain HTML ready to insert in the DOM. Clearly, you can change the implementation to return an array of JSON objects to insert as HTML from the client side.

In this sample code, the scroll event is captured from the whole browser window and so the feature mimics the behavior of the LinkedIn or Facebook feed page. However, the DIV element also exposes the same scroll event, so you can also implement on-demand scrolling on the content of an individual DIV.

When it comes to on-demand paging, any code that you use to populate the view should be very well aware of the structure of the document being extended. If you code the HTTP endpoint to return plain JSON data then the details of the layout (table rows, list) can be managed on the client side only. Otherwise, as in the code presented, the layout details must be known to both the server and the client side. This is not necessarily a problem if the same individual, or the same team, manages both parts of the application. When different teams are on it instead having to share view layouts may introduce unwanted dependencies. At the same time, managing the layout on the client side is likely to require the use of some JavaScript framework for client-side data binding such as KnockoutJS or AngularJS.

In the sample code, a HTML table is assumed. The insertion point where the freshly downloaded content will be attached should be known in advance. In the sample code, I use the data-last custom attribute on the TABLE element to keep track of the last row index currently rendered. The downloaded markup is appended after the last TR of the table using the following jQuery helper method:

Filtering Content

When a table of content is displayed to users, then it is quite likely that users would like to view only a few of them. This requires a dynamic filter that takes a typed substring and hides all rows that don’t match. As mentioned, nearly every commercial grid library offers this feature and also the aforementioned DataTables library supports dynamic filters. However, even if you don’t use any such libraries, it is no big deal to code a dynamic filter on top of a HTML table. It just needs a bit of jQuery code.

This code snippet triggers when the document is ready and it processes all the TR elements in the body of the given table. The code dynamically adds a data-filter attribute to each table row set to the name of the country being rendered. The name of the country is read from the first TD element in the row. Next, let’s add an input field so that the user can type in the filter string.

Finally, let’s hook up the input event fired by the field whenever the user types in.

To filter the content of the table, you must retrieve all TR elements whose data-filter attribute matches the content of the input field. Once you have retrieved the rows, you just hide those which don’t match. Byu sing the jQuery selectors and helper methods, it can all be reduced to the following code:

Look at the figure below for an idea of how it could work.

Note that, with jQuery, you can only filter the content of the table that is already available on the client side. In other words, if your grid is paged by applying a filter as discussed here, it only filters the current page and in no way presents a comprehensive list of archived rows that match the query string.

Summary

Data grids are a classic feature of all data-driven web applications which, in turn, are a classic type of web application. There is a classic solution as well, thanks to the Web Forms powerful server controls such as DataGrid and GridView controls. With the advent of Ajax first and ASP.NET MVC (including ASP.NET Core) later, developers had to invent different solutions for the same, common, problem of paging, filtering and scrolling. In this article, I hope I’ve illustrated the point that quick and effective solutions can be implemented in any application without committing to a heavy-weight solution to all conceivable requirements, but which obliges the team to explore the depths and intricacies of some large external framework to get the precise functionality of a grid that are appropriate for the application.

  • 6933 views

  • Rate
    [Total: 7    Average: 4.9/5]
  • Oleg

    Thank you Dino, great article, as usually

  • Daniel Seara

    Good one, Dino (as usual). However, let me say that this is not always the solution. I saw several times this kind of implementation working sooo slow… just because the developer do not process the paqging routine at server side and get thousands of records before “take” some part. :). Best regards from Spain, my friend!

  • Ed Charbeneau

    Interesting and a good read. It’s important to understand the basic I/O of a data grid.

    It’s probably beyond the scope of the read but grids quickly get out of control. You have to consider mobility, accessibility, and how the grid behaves with the rest of the app. You don’t want to end up with your own DSL or franken grid made of custom & OSS frameworks. Also, feature creep hits very quickly as well, it’s just a matter of time before someone asks “can I edit this”, and you’re off writing grid logic again.