DataGridView with hierarchical data binding

Download HierarchicalGrid - 100.64 KB

Introduction

The TreeGridView by Mark Rideout (here's the link) is a great control. It allows us to display related data in a hierarchical form (a tree!), but I needed it to be sortable and support data binding.

The HierarchicalGrid does that, the quick and dirty way. It displays the table relations in a hierarchical form and sorts it through the levels too.

Also, this is my first contribution and any feedback would be greatly appreciated!

Background

I would really recommend you see the TreeGridView.

Using the code

There's a new class called DataGridSource, and the DataSource of the HierarchicalGrid must be of that type.

It takes three arguments: the dataset, a list of display columns and a list of group columns. Now, what are those? The display columns are names of the table columns to be displayed, and the group columns are columns that group total values, like sum, average, product, etc.

Let's suppose that we have two tables, one with a list of people and their fruit preference, and another with where and when each person bought their favorite fruit.

For instance, the display columns would be:

List<string> displayColumns = new List<string>();
displayColumns.Add("id");
displayColumns.Add("Name");
displayColumns.Add("Fruit");
displayColumns.Add("BoughtFrom");
displayColumns.Add("Date");

Now we have a list of columns to be displayed. What about the group columns? Let's set them:

List<GroupColumn> groupColumns = new List<GroupColumn>();
groupColumns.Add(new GroupColumn("Quantity", GroupTypeEnum.Sum));

Pay attention to the GroupColumn type. It groups the name of the column and the type of operation that it should do on its child results. We added a new sum of quantities.

Okay! We have our display columns list and our group columns list. Let's get our dataset.

SqlConnection connection = new SqlConnection("Your connection string");
connection.Open();

SqlDataAdapter dataAdapter = new SqlDataAdapter(
    "SELECT id, Name, Fruit FROM FruitPrefs",
    connection);
DataTable dtResult1 = new DataTable();
dataAdapter.Fill(dtResult1);

dataAdapter = new SqlDataAdapter(
    "SELECT id, BoughtFrom, Date, Quantity FROM SalesRecords",
    connection);
DataTable dtResult2 = new DataTable();
dataAdapter.Fill(dtResult2);

DataSet dsResults = new DataSet("Results");
dsResults.Tables.Add(dtResult1);
dsResults.Tables.Add(dtResult2);
DataRelation relation1 = new DataRelation(
    "relation1",
    dtResult1.Columns["id"],
    dtResult2.Columns["id"]);
dsResults.Relations.Add(relation1);

To set the dataset, we must use one of the datatables' dataset when creating a new DataGridSource, like this:

DataGridSource newGridSource = new DataGridSource(
    dtResult1.DataSet,
    displayColumns,
    groupColumns);
hierarchicalGridView1.DataSource = newGridSource;

Hooray! That's it! It displays an hierarchical form of the dataset, with the relations.

Points of Interest

Two things here:

- When you set the dataset, it tries to find the best sequence of relations to fill the dataset. In other words, it works like matching a sequence of domino pieces. It has one solution and it tries to find it. It finds the top and the bottom relation, then it successively searches for the relation the matches the previous one.

- We can't let the DataGridView sort function work here, otherwise it would break the structure of the data. So, each time it is asked to sort, it clears the grid and adds the nodes again, in the proper order. That's clearly NOT efficient. This is where work must be done.