Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

DataGridView with hierarchical data binding

, , 31 Jul 2008
Rate this:
Please Sign up or sign in to vote.
The TreeGridView by Mark Rideout with data binding and sorting

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.

History

  • July 31, 2008 - Initial release

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Share

About the Authors

Joao Eduardo Grossi
Engineer
Brazil Brazil
No Biography provided

No Biography provided

Comments and Discussions

 
QuestionWhere is code for HierarchicalGridView Pinmembersacp.net24-Jul-14 8:26 
QuestionVery good! PinmemberJLDevC#20-Oct-13 3:26 
QuestionHow to Set Right To Left Property when the Hierarchical Gridview Control Converts from English to Arabic Pinmemberarchanakumari30-Dec-12 22:18 
Questiondatagridview with Hierachical Pinmemberthanhtungtrucgiang27-Dec-12 18:15 
AnswerRe: datagridview with Hierachical PinmemberClaudia Bolis2-Apr-13 5:28 
QuestionVery good PinmemberDadajiIn15-Oct-12 15:01 
GeneralMy vote of 5 PinmemberMember 382405323-Jul-12 20:44 
QuestionUnable to cast type Pinmemberduphrx7-Aug-11 8:41 
Answerupdate [modified] Pinmemberjoel_gil22-Sep-11 4:39 
AnswerRe: Unable to cast type PinmemberZeke Susman9-Apr-13 12:11 
QuestionBindingNavigator PinmemberNetKoder27-Apr-11 3:15 
GeneralJust what i needed PinmemberVincent Meijer27-Jan-11 4:34 
GeneralMy vote of 5 PinmemberVincent Meijer27-Jan-11 4:32 
It works for me Wink | ;) just what i needed
GeneralNeed help for Drag & Drop Pinmemberbnsrinivas21-Jan-11 6:05 
QuestionAny possibility to format a column as checkbox? Pinmemberandibaden9-Jan-11 3:39 
QuestionHow to Adjust row's height? PinmemberMember 198466919-Sep-10 23:01 
GeneralMy vote of 5 Pinmemberblaknar9-Aug-10 2:46 
QuestionThat just works with Windows XP Style?? Pinmemberbruno_bert27-Apr-10 3:58 
AnswerRe: That just works with Windows XP Style?? PinmemberBassamKhalid6-Aug-11 19:55 
GeneralIt doesn't work PinmemberMetalPower18-Mar-10 12:13 
GeneralRe: It doesn't work PinmemberP0110X24-Jun-10 4:31 
GeneralRe: It doesn't work PinmemberMember 422198424-Sep-11 5:03 
QuestionVb Version? Pinmembercleomc8-Dec-09 3:28 
GeneralMy vote of 1 Pinmemberxpesdal1-Jul-09 1:21 
GeneralWhere did GroupColumn Come From PinmemberLemuel.Adane2-Jun-09 23:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 31 Jul 2008
Article Copyright 2008 by Joao Eduardo Grossi, Ricardo Macedo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid