Grouping in the ASP.NET AJAX WebHierarchicalDataGrid with On-Demand Loading of grouped records

Kiril Matev / Monday, April 26, 2010

Let’s say you have the following scenario: you have a flat table containing a large number of records that you display in a grid control in a web application. Furthermore, you’d like to display the data always grouped by the same column, as all users use this column to locate the data they’re looking for. As each group contains a lot of records, it’s an absolute necessity to only retrieve the records in the group the user would like to expand, rather than records in all groups.

In order to make the scenario easily understood, let’s make it a bit more specific. We are asked to work with customer data, where a customer is defined as first and last names, address, city, state and country in the Customers table. Our users would very much like the grid to contain a list of grouping headers representing countries, which once clicked, would retrieve and display in the grid the list of customers coming from that country.

Your first guess might be – bind the dataset to the WebHierarchicalDataGrid, and add a group by clause using the code-behind to do the grouping in advance using guidance from this sample. Although this sounds good, it wouldn’t work for us in terms of performance, because the grouped records are all loaded and displayed in the WebHierarchicalDataGrid together with the group headers.

By this time you’re are probably thinking – this sounds like a hierarchy, just bind it to the WebHierarchicalDataGrid, and take advantage of its out-of-the-box Automatic Load on Demand capability. It will provide the expected performance boost, as it only loads the child records once a parent record is expanded. However, in this scenario, we have a flat dataset, and we don’t want to change the database schema so we can’t apply it readily. We’ll have to go off the beaten path in order to deliver.

In a nutshell, we will use a DataRelation to build a hierarchy on the fly based on the flat data table, and we'll produce a sample project illustrating the approach.

We’ll extract from the original flat data table (the Customers table) the distinct values of the column to be used for the grouping (the Country column) in a separate flat table, called Countries.

//create a dataview of the customers table, sorted by country name
DataView dv = countryCustomersDataSet.Tables["Customers"].DefaultView;
dv.Sort = "Country ASC";

//add a new table called "Countries" with the distinct Country column values from the Customers table
countryCustomersDataSet.Tables.Add(dv.ToTable("Countries", true, "Country"));

We’ll then construct a data relation which will link records from the two tables, using the grouping column (the Country column) as the key. Basically, we’re linking the Country column in the Countries table to the Country column in the Customers table. This allows us to retrieve the customers for each country.

//Set up a data relation for the Customers table with the Countries table
//using the country column values to match records in a parent-child relationship
DataRelation rel = new DataRelation("CountryCustomers",
countryCustomersDataSet.Tables["Countries"].Columns["Country"],
countryCustomersDataSet.Tables["Customers"].Columns["Country"]);
countryCustomersDataSet.Relations.Add(rel);

// Set the table's primary key field
countryCustomersDataSet.Tables["Countries"].PrimaryKey = new DataColumn[] {
countryCustomersDataSet.Tables["Countries"].Columns["Country"] };

Now that the dataset contains two tables which have been linked in a parent-child relationship, we can go ahead and bind the data to the WebHierarchicalDataGrid. We specify the name of the table containing the group by column (the Countries table) as the DataMember, and the name of the group by column – Country in the DataKeyFields property. This tells the WebHierarchicalDataGrid to put the records from the Countries table as parent records, and to use the DataRelation to retrieve the records from the Customers table, related to each Country value.

this.WebHierarchicalDataGrid1.DataSource = countryCustomersDataSet;
this.WebHierarchicalDataGrid1.DataMember = "Countries";
this.WebHierarchicalDataGrid1.DataKeyFields = "Country";
this.WebHierarchicalDataGrid1.DataBind();

The above code segments are slightly abbreviated in order to illustrate the main points of the implementation. One important omission is that that the code retrieving the Customers table from the database and constructing the data relation should be executed just once, with the resulting dataset bound to the WebHierarchicalDataGrid every time the page is posted back to the server.

Please download the attached project files for a complete code listing. The sample application retrieves data to bind to the WebHierarchicalDataGrid from a Northwind database, included in the solution as a Microsoft Access database file. Please note that to run the sample application, you'll need to download and install the 10.1 release of NetAdvantage for ASP.NET - a trial version is freely available here.

Using the approach described above, we fulfilled the requirements of our users, and we delivered without any degradation in performance or excessive footprint on the client. Even though we went slightly off the beaten path, we made no changes to the database schema, used no arcane tricks and came up with a simple implementation that is easy to maintain. At the end of the day, that’s the good stuff that earns you the respect of both users and colleagues.

If you have any questions or comments, please use the forum below or write me at kmatev(at)infragistics.com.