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.