Thursday, February 16, 2006

C# - Fill TreeView with SQL data

Here is some c# code to fill a treeview with hierarchal data from an sql table. The SQL table looks like this:-



Each item in the database has a pointer to it's parent (which can be null for top level items). The DisplayOrder field is used to alter the display order of items at the same level. NodeType should be "Node" for a nodes, anything else is assumed to be a "document".

The tree is displayed like this:-


Here is the code

private void LoadTreeview ()

{

// This code fills a DataTable with an SQL Query

DataTable table = DatabaseUtility.ExecuteDataTable (

new SqlConnectionSettings.Default.SQL_DSN), (Properties.

"select ID, ParentID, DisplayOrder, NodeType, NodeText from TableName"

);

// Fill the TreeView with database data. Use null

// as parentid for top level

AddKids (null, "ParentID is null", "DisplayOrder", table);

}

private void AddKids (string parentid, string filter, string sort, DataTable table)

{

DataRow[] foundRows = table.Select (filter, sort);

if (foundRows.Length == 0)

return;

// Get TreeNode of parent using Find which looks in the name

// property of each node. true itterates all children

TreeNode[] parentNode = treeView1.Nodes.Find (parentid, true);

if (parentid != null)

if (parentNode.Length == 0)

return;

// Add each row to tree

for (int i = 0; i <= foundRows.GetUpperBound (0); i++)

{

string nodetype = foundRows[i]["NodeType"].ToString ();

string nodetext = foundRows[i]["NodeText"].ToString ();

string nodeid = foundRows[i]["ID"].ToString ();

TreeNode node = new TreeNode ();

node.Text = nodetext;

node.Name = nodeid; // This is critical as the Find method searches the Name property

if (parentid == null)

treeView1.Nodes.Add (node); // Top Level

else

parentNode[0].Nodes.Add (node); // Add children under parent

// Itterate into any nodes

if (nodetype.ToLower () == "node")

AddKids (nodeid, "ParentID=" + nodeid, sort, table);

}

}


The trick here is to use the nodetype.Name to hold a unique ID of each item. The treeview.Nodes.Find("xxx") command is the olny way you can search the entire tree (including children) and it searches the .Name property only.

15 comments:

Babak said...

Hi, I just want to say thank you for this post. I was so helpful for me.

Andrew Jones said...

You are welcome. I'm glad it helped you.

Jason said...

Andrew,

Thanks for this article it is very infomrative. I have request. Can you please post the code that yo uuse to store the info the the database? I am having problems with storing and loading a treeview to a DB. If I can see how its done, that will have solved my problem totally.

Thanks.

Andrew Jones said...

The code is shown above. The database was created manually in SQL server.

Jason said...

Oh man thats not cool. My problem is trying to figure out how to get the values that you have for each node, and then store the information to the db.

Nic said...

Thanks a lot for your post. I just want to know which treeview you are using? I have VS2005? My treeview does not have the find method.

Regards

Nic

William said...

I think I should add this as I just spent the past few days trying to find a bug in this code that would cause it not to work after 10+ rows of nodes...I had to add quotes! The ParentID check needs to enclosed in single quotes. Hope this solves problems!


AddKids(nodeid, "ParentID= '" + nodeid + "'", sort, table);

prizmax said...

thank you very much. it was very helpful for me..

Roshdi said...

Tnanx alot man :)

Dan Sørensen said...

Thanks for your post. but i got the same as Nic cant find the find method on the treeview

Andrew Jones said...

The Find method is on treeView1.Nodes not on treeview itself. treeView1.Nodes is a TreeNodeCollection which contains the Find method. I hope that helps.

Andrew

Dan Sørensen said...

Can you make your code download able?

Andrew Jones said...

It's all in the post. Just copy and paste it into your code.

Janko.M said...

Thanks for your post, but code lines
string nodetype = foundRows[i]["NodeType"].ToString();
if (nodetype.ToLower().Trim() == "node")
and column 'NodeType' in table are superfluous.

Geoff Coope said...

Thanks for this, really helped me too.