Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#
Article

DataGridView with hierarchical data binding

,
Rate me:
Please Sign up or sign in to vote.
4.77/5 (22 votes)
31 Jul 2008Ms-PL2 min read 233.5K   15.9K   87   41
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:

C#
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:

C#
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.

C#
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:

C#
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)


Written By
Engineer
Brazil Brazil
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Written By
Brazil Brazil
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionAdd Images Pin
Member 1299863116-Aug-17 19:07
Member 1299863116-Aug-17 19:07 
QuestionNo working as expected Pin
kichi_blr3-Dec-15 22:30
kichi_blr3-Dec-15 22:30 
Questionupdates? Pin
kiquenet.com13-Oct-14 0:53
professionalkiquenet.com13-Oct-14 0:53 
QuestionWhere is code for HierarchicalGridView Pin
sacp.net24-Jul-14 8:26
sacp.net24-Jul-14 8:26 
QuestionVery good! Pin
JLDevC#20-Oct-13 3:26
JLDevC#20-Oct-13 3:26 
QuestionHow to Set Right To Left Property when the Hierarchical Gridview Control Converts from English to Arabic Pin
Anil Honey 20630-Dec-12 22:18
Anil Honey 20630-Dec-12 22:18 
Dear Friend,

As per Your Information I developed the Sample Application Using Hierarchical Gridview.Its Working fine,When i convert the Language From English to arabic.The TreeGridview Coloumn is not Converting Left to Right.So if any Information please share with me.


Regards,

AnilKumar.D
Questiondatagridview with Hierachical Pin
tung@vt27-Dec-12 18:15
tung@vt27-Dec-12 18:15 
AnswerRe: datagridview with Hierachical Pin
Claudia Bolis2-Apr-13 5:28
Claudia Bolis2-Apr-13 5:28 
QuestionVery good Pin
DadajiIn15-Oct-12 15:01
DadajiIn15-Oct-12 15:01 
GeneralMy vote of 5 Pin
Member 382405323-Jul-12 20:44
Member 382405323-Jul-12 20:44 
QuestionUnable to cast type Pin
duphrx7-Aug-11 8:41
duphrx7-Aug-11 8:41 
Answerupdate Pin
joel_gil22-Sep-11 4:39
joel_gil22-Sep-11 4:39 
AnswerRe: Unable to cast type Pin
Zeke Susman9-Apr-13 12:11
Zeke Susman9-Apr-13 12:11 
QuestionBindingNavigator Pin
practicalDeveloper27-Apr-11 3:15
practicalDeveloper27-Apr-11 3:15 
GeneralJust what i needed Pin
Vincent Meijer27-Jan-11 4:34
Vincent Meijer27-Jan-11 4:34 
GeneralMy vote of 5 Pin
Vincent Meijer27-Jan-11 4:32
Vincent Meijer27-Jan-11 4:32 
GeneralNeed help for Drag & Drop Pin
Srinivas N Bavirisetty21-Jan-11 6:05
Srinivas N Bavirisetty21-Jan-11 6:05 
QuestionAny possibility to format a column as checkbox? Pin
andibaden9-Jan-11 3:39
andibaden9-Jan-11 3:39 
QuestionHow to Adjust row's height? Pin
Member 198466919-Sep-10 23:01
Member 198466919-Sep-10 23:01 
GeneralMy vote of 5 Pin
blaknar9-Aug-10 2:46
blaknar9-Aug-10 2:46 
QuestionThat just works with Windows XP Style?? Pin
bruno_bert27-Apr-10 3:58
bruno_bert27-Apr-10 3:58 
AnswerRe: That just works with Windows XP Style?? Pin
BassamKhalid6-Aug-11 19:55
BassamKhalid6-Aug-11 19:55 
GeneralIt doesn't work Pin
FabianSilva18-Mar-10 12:13
FabianSilva18-Mar-10 12:13 
GeneralRe: It doesn't work Pin
P0110X24-Jun-10 4:31
professionalP0110X24-Jun-10 4:31 
GeneralRe: It doesn't work Pin
Houssam Alshami24-Sep-11 5:03
Houssam Alshami24-Sep-11 5:03 

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

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