Serge's Technology View

I was asked today to help with code which would populate the TreeView control from database table in ASP.Net page.

Since it is not a first time around and appear to be very common situation with dynamic pages, I thought it may be useful to write about it once and then just refer to this post later.

1. We have a table with the following structure:
   

   ID

   ParentID

   Name

2. We also have a stored procedure usp_GetGroupList which would return data from table above.
3. And on our ASP page we have a TreeView control called myTreeView

Few things we are trying to achieve:

1. Small code
2. Minimum of database access

Well, code would not be too complex after all:

private void PopulateTree()
{
    // Populate dataset with data for later use
    DataSet dsList = new DataSet();
    SqlConnection conn = new SqlConnection(myConnectionString);
    conn.Open();
    try
    {
        SqlCommand cmd = new SqlCommand("usp_GetGroupList", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adapt = new SqlDataAdapter(cmd);
        adapt.Fill(dsList);
    }
    finally
    {
        conn.Close();
    }

    // Clear the tree
    myTreeView.Nodes.Clear();
    PopulateTreeNode(dsList, null, "0");
}

private void PopulateTreeNode(DataSet dsList, TreeNode parent, string parentID)
{
    TreeNodeCollection baseNodes;
    string rowID;
    TreeNode node;

    if (parent == null)
    {
        baseNodes = myTreeView.Nodes;
    }
    else
    {
        baseNodes = parent.ChildNodes;
    }

    foreach (DataRow dsRow in dsList.Tables[0].Select("ParentID = " + parentID))
    {
        node = new TreeNode();
        node.Text = dsRow["Name"].ToString();
        rowID = dsRow["ID"].ToString();
        node.Value = rowID;
        node.SelectAction = TreeNodeSelectAction.Select;

        // Add link back to itself so selected group could be changed
        node.NavigateUrl = "~/default.aspx?id=" + rowID;
        baseNodes.Add(node);

        // Use recusion to populate child nodes
        PopulateTreeNode(dsList, node, rowID);

        // Preselect the node for current group
        if (node.Value == Request.QueryString["id"])
        {
            node.Selected = true;
        }
    }
}