Ralph Varjabedian
Sharing .NET Code and ideas

Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Tuesday, April 22, 2008 10:17 PM

The other day I wanted to bind a TreeView to a DataSet (or an ObjectDataSource that would return a DataSet) and I was disappointed to see that it does not support this. Googling this showed a lot of developers opting to fill the TreeView themselves programmatically. So I decided to create a simpler solution for this.

The key to this solution is that the TreeView can bind to any object that implements the interface IHierarchicalDataSource. So I created a simple class that takes a DataSet as its input along with two column names, I will explain them in a moment, and this class would implement the IHierarchicalDataSource interface.

So instead of writing:

TreeView1.DataSource = dataSet;

which is not supported, we write this:

TreeView1.DataSource = new HierarchicalDataSet(dataSet, "ID", "ParentID");

And that's it. And here is your sample application.

Check this post for the VB.NET version of the sample application.

The two extra parameters that you have to pass to the constructor of HierarchicalDataSet are two column names. The first one being the primary key of the table, and the second one being the parent key which usually would be a foreign key to the primary key of the same table; this allows modeling the tree structure in a database table. Here is a simple design of such a tableTable design for a tree structure.












Feedback

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

This is what I was looking for - our org chart is represented in an oracle table with columns employee_number, supervisor_emp_number, and I need to use something like a treeView control to display it on our Intranet. You're right, everyone seems to be building the hierarchical part on the fly, which can consume resources.

Thanks for posting this! 4/24/2008 11:30 PM | Paulette Neal-Allen

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

You're welcome. Please do not hesitate to let me know if you need anything else. 4/26/2008 11:02 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Ralph,
this is very interesting indeed, thank you for that.

For less experienced readers, I would indicate that the TreeView DataBindings needs to be setup appropriately.

This can be done in several ways:
A) by inserting bindings definition in the asp:TreeView tag:
<DataBindings>
<asp:TreeNodeBinding
DataMember="System.Data.DataRowView"
TextField="Text"
ValueField="ID" />
</DataBindings>

B) or with code like this:
//---
TreeNodeBinding tnb = new TreeNodeBinding();
tnb.DataMember = "System.Data.DataRowView";
tnb.TextField = "Text";
tnb.ValueField = "ID";
tnb.PopulateOnDemand = false;
tnb.SelectAction = TreeNodeSelectAction.Select;
TreeViewPS.DataBindings.Add(tnb);

TreeViewPS.DataSource = new BindingTools.HierarchicalDataSet(dataSet, "ID", "ParentID");
TreeViewPS.DataBind();
//---

C) or with the dialog available via the DataBindings property of the TreeView in the Designer.

Regards. 5/3/2008 1:24 AM | Alain Deby

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Well, you are right for the Binding code, this is already done in the Sample application, but it is a good idea to mention it in detail on the post itself for the less experienced. Thank you. 5/3/2008 2:21 AM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

I keep receiving the error:

"Cannot find column[ORG00379]"

when the DataBind method is executed. ORG00379 is the ID of the first record in the query. Example:

PARENTID ID TEXT
---------- ---- ------
ORG00379 ORG 1
ORG00001 ORG 2
ORG00001 ORG00003 ORG 2A
ORG00001 ORG00005 ORG 2B

I'm using ASP.NET 2.0 and I don't know what you're doing with Assembly. The only thing I'm using is the IHierarchialDataSet.cs file in my App_Code folder. I've removed the namespace. Do you have any idea what the error is referring to exactly?

Thanks! 5/6/2008 6:36 PM | Jon

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

<pre>
PARENTID ID TEXT
--------- ----- --------
ORG00379 ORG 1
ORG00001 ORG 2
ORG00001 ORG00003 ORG 2A
ORG00001 ORG00005 ORG 2B
</PRE> 5/6/2008 6:38 PM | Jon

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

(PARENTID, ID, TEXT)

( , "ORG00379", "ORG 1")
( , "ORG00001", "ORG 2")
( "ORG00001", "ORG00003", "ORG 2A" )
( "ORG00001", "ORG00005" "ORG 2B" )

Okay, it's formatted properly. 5/6/2008 6:41 PM | Jon

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

I believe I fixed it. The errors were because of the format strings created for GetParentRow(), HasChildren() and HierarchicalEnumerable.GetEnumerator(). Where the format string is "{0} = {1}", it should actually be "{0} = '{1}'". The first variable is a column name, whereas the second one is a value in the column. If there aren't any apostrophes, then it's trying to find a column name named after the value in the column.

So it works now... as far as I know. .NET craps out on me when the page attempts to load (faulting application w3wp.exe).... that's something else that's totally my problem. 5/6/2008 7:44 PM | Jon

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Ralph
I am trying to use your IHierarchicalDataSet.cs in my project and class that populates dataset
and i pass the dataset and ID and ParentID to IhierarchicalDatasource but i face problem in this line

IEnumerator i = hDataSet.dataSet.Tables[0].DefaultView.GetEnumerator();

saying check if it is going in infinite loop

i am using sample dataset which you used in dafault.aspx.cs

PLease kindly advice where it is going wrong as i need it urgent and am new to using IHierarchical datasource

Thanks 5/8/2008 2:06 PM | Paul

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Paul, I will contact you by email to help you out. 5/8/2008 3:24 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Jon,

I see your problem, this is because your primary keys are strings which is why they need the single quotes, I will update my code to detect this and correctly work it out.

If you need any further help let me know. 5/8/2008 3:27 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Jon,

I updated the code to take into account the columns of type strings.

Let me know if you need something else. 5/14/2008 11:46 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

I have the same isue as Paul, above, that being that the supplied code invariably falls into a stack overflow. Even utilising the supplied same data. Any idea how to resolve this? And, yes, before you ask, I'm using .NET 2.0. Is this an issue with this version of the .NET framework?

Many Thanks 5/15/2008 11:45 AM | Andy Twiss

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Andy,

I will follow up your case by Email to help you.
Thanks. 5/15/2008 12:32 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

I used the code and it runs without any error but the tree view doesn't appear 5/18/2008 4:06 AM | somen

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

I think that you are missing something here Jon. You stated that the expression used in the filter containing this :
"{0} = {1}"
needs to be changed to :
"{0} = '{1}'".

I am not sure about that. The apostrophe is usually used when building an expression with a string variable whereas without, the variable is expected to be numeric.

Since this index key is numeric, it seems to me that the filter is correct without the apostophes.


5/18/2008 8:44 PM | Howard

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hello Howard,

Yes you are right. The code does not always change it to the filter with the quotes. The code checks the type of the column name and if it is a string type, then the quotes are used, if it is not of string, then quotes are not used, covering both cases.
5/19/2008 12:54 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

So, I have taken your updated class (that distinguishes between strings and numbers), and have replaced the version that I had. It works fine with the same programmatic dataset, but I have not been able to modify the datasource to use my resultset.

If I may, here is the SQL, the treeview, and the call.

1)
sSQL = "select distinct EMP_SSN, TO_NUMBER(EMP_SSN) AS PARENT_KEY_NUMBER, TO_NUMBER(CHILD_KEY) AS CHILD_KEY_NUMBER, UPDATE_DATETIME, TRANSACTION_TYPE, RecCount FROM ( " & vbCrLf & _
" SELECT EMP_SSN, UPDATE_DATETIME, '1' || '000' || EMP_SSN AS CHILD_KEY, 'CUR' as TRANSACTION_TYPE, SUM(1) AS RecCount" & vbCrLf & _
" FROM(TDF_FID_SCF_CUR) " & vbCrLf & _
" GROUP BY EMP_SSN, UPDATE_DATETIME " & vbCrLf & _
"" & vbCrLf & _
"" & vbCrLf & _
" UNION ALL " & vbCrLf & _
"" & vbCrLf & _
"" & vbCrLf & _
" SELECT EMP_SSN, UPDATE_DATETIME, '2' || '000' || EMP_SSN AS CHILD_KEY, 'CUR' as TRANSACTION_TYPE, SUM(1) AS RecCount" & vbCrLf & _
" FROM(TDF_FID_SCF_CUR) " & vbCrLf & _
" GROUP BY EMP_SSN, UPDATE_DATETIME " & vbCrLf & _
"" & vbCrLf & _
"" & vbCrLf & _
" UNION ALL " & vbCrLf & _
"" & vbCrLf & _
"" & vbCrLf & _
" SELECT EMP_SSN, UPDATE_DATETIME, '3' || '000' || EMP_SSN AS CHILD_KEY, 'CUR' as TRANSACTION_TYPE, SUM(1) AS RecCount" & vbCrLf & _
" FROM(TDF_FID_SCF_CUR) " & vbCrLf & _
" GROUP BY EMP_SSN, UPDATE_DATETIME " & vbCrLf & _
"" & vbCrLf & _
") " & vbCrLf & _
"" & vbCrLf & _
"Where UPDATE_DATETIME>=trunc(sysdate-8,'DD') " & vbCrLf & _
"order by EMP_SSN, UPDATE_DATETIME, CHILD_KEY" & vbCrLf & _
"" & vbCrLf & _
""

2)
<asp:TreeView ID="MyTree" runat="server" ImageSet="WindowsHelp">

<DataBindings>
<asp:TreeNodeBinding DataMember="System.Data.DataRowView"
TextField="EMP_SSN" ValueField="PARENT_KEY_NUMBER" />
</DataBindings>

<ParentNodeStyle Font-Bold="False" />
<HoverNodeStyle Font-Underline="True" ForeColor="#6666AA" />
<SelectedNodeStyle BackColor="#B5B5B5" Font-Underline="False"
HorizontalPadding="0px" VerticalPadding="0px" />
<NodeStyle Font-Names="Tahoma" Font-Size="8pt" ForeColor="Black"
HorizontalPadding="5px" NodeSpacing="0px" VerticalPadding="1px" />
</asp:TreeView>

3)
ds = DBUtils.GetDataSet(sSQL)
Me.MyTree.DataSource = New TreeViewBinding.HierarchicalDataSet(ds, "CHILD_KEY_NUMBER", "PARENT_KEY_NUMBER")
Me.MyTree.DataBind()

Please note that I have ported the code to vb.net.

Thanks,
Howard
5/19/2008 8:51 PM | Howard

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Ralph
I have used your HierarchicalDataSet.cs in my project but my page doesnt show up anything
I have SQL server database with table
menu_item_id,Parent_id,Menu_item,Link
I have created a class that returns a dataset
then i want to populate the tree with this dataset .I have already tested my class with a windows form and it populates the form correctly .
Now i want to create a treeview with this dataset
can you please help its urgent as my deadline is close
Please help
5/20/2008 3:00 AM | sanya

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hello Sanya and Howard,

I will answer your questions via email.

Howard if you can share with us your VB.NET Code that would be great, send it to me and I will post it by itself and with reference to you.
5/20/2008 12:47 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

I'm adding another node to the sample to make an existing node a parent but it doesn't display. Here's the added row:

row = dataSet.Tables[0].NewRow();
row["ID"] = 7;
row["ParentID"] = 6;
row["Text"] = "Child of Child 4";
dataSet.Tables[0].Rows.Add(row);

Any ideas?

Ken
6/1/2008 10:23 PM | Ken Cox

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Nevermind about my added row comment above...

I was inadvertently referencing the compiled code rather than my edited .cs file. Oops.

Ken
6/1/2008 10:30 PM | Ken Cox

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Glad to hear you got it to work.
Let me know if you need further help.
Have a good day. 6/2/2008 2:56 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

This is great for a 2 level tree view..

I recently came across a problem in which I needed a 3 level Treeview...

I posted a question on Experts-Exchange. Here is the link..

http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_23488558.html

I would appreciate your advice. Many Thanks!
Josh 6/16/2008 6:55 PM | Joshua Couto

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi,

Looks to me like a straight forward solution with my class, Your key here would be: "SubOwnerID" and your parent key would be "OwnerID". Just load the whole table and feed it to the class. It should work unless there is something I am not seeing.

Just as a side note, the solution presented is not just 2 levels, it is n levels nested under each other as much as you want, all you have to have is the correct parent-child relationship.

Let me know if you need further help. 6/17/2008 11:29 AM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Greate Code
Exactly what i was looking for 6/21/2008 9:49 AM | Sushilkumar Pandey

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Ralph

I'm having the same problem as sanya where nothing shows up.

On debugging the control comes into the HierarchicalDataSet constructor and nothing happens after that. The dataset sure has got data in it.

Any ideas what's going wrong

( Note: as there is no explicit call for any other method inside the HierarchicalDataSet constructor Is it supposed to call any method in
the interface IHierarchicalDataSource)

Thanks
RainManAlex
6/24/2008 12:06 PM | RainManAlex

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hello,

Can you please show me your data quickly, or email me your project altogether so that I can debug your example. And no the constructor does not call anything, it just hands in the interface IHierarchicalDataSource, upon binding the treeView will start calling methods inside the class (via the interface methods).

Let me know. 6/24/2008 12:54 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hello, I have same problem like Paul's (5/8/2008 2:06 PM | Paul, Infinity Loop...)
Can U email a solution for this problem, thanks
6/27/2008 6:09 PM | Alex Petrov

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Ralph,

nice piece of work, congratulations! I've been searching the web for quite some time to find a good solution for this problem and evaluated various approaches to bind my organisational data. Your solution is fast and very reliable, thank you!

For others using it, some minor remarks may be helpful:
- The Treeview will display nothing, when the 'root' has an entry in the parent-column. This may be important to know when only a subtree is to be displayed
- I put the whole into a 'If Not Page.IsPostBack' condition, as the tree doesn't need to be re-read on every postback
- In the VB code (HierarchicalDataSet.vb, sub new), I made two minor changes, as my ID-Variables are always numerical:

dataSet.Tables(0).Columns(idColumnName).DataType = GetType(String)
Me.columnIsString = True

throws an exeception, as VB cannot evaluate the first line, it was changed into

If (dataSet.Tables(0).Columns(idColumnName).DataType Is GetType(String)) Then
Me.columnIsString = True
End If

The according change is to be made in the second 'sub new' as well (with dataview.table)

Thanks again

Holger

6/30/2008 4:46 PM | Holger

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Hi Holger

Thank you for the feedback. Your remarks are appreciated. It is true that the tree will not display if someone is feeding the data of a subtree, I will try to modify the code when my time permits to support this case.

As for the VB code, thank you for the changes, I will try to add them to the code.

Thanks again. 6/30/2008 5:24 PM | Ralph Varjabedian

# re: Binding ASP.NET TreeView to a DataSet or an ObjectDataSource

Thanks a lot, very nice 7/5/2008 1:25 AM | m.sayed

Post a comment





 

Please add 6 and 5 and type the answer here: