Recursively adding a node into a .NET TreeView

Question

I need to create a menu structure taken from a database table which uses an ID and a ParentID and a Rank which is used to determine the order of the nodes.

Root(ID 1, ParentID 0, Rank 1)
  - Node(ID 2, ParentID 1, Rank 1)
    - Node(ID 3, ParentID 2, Rank 1)
      - Node(ID 4, ParentID 3, Rank 1)
      - Node(ID 5, ParentID 3, Rank 2)
    - Node(ID 6, ParentID 2, Rank 2)
    - Node(ID 7, ParentID 2, Rank 3)
  - Node(ID 8, ParentID 1, Rank 2)
    - Node(ID 9, ParentID 8, Rank 1)
    - Node(ID 10, ParentID 8, Rank 2)

I've attempted to create a function to iterate through the SQL data and create this tree structure but I'm unsure how to handle the depth of the addition. I can add the first layer of Nodes by simply checking whether they have a ParentID, I can add the second layer of nodes in the else condition, however i'm unsure how to add the following layers of the heirarchy.

Iterating through the Database:

using (var command = new SqlCommand(_Query, _Connection))
{
    _Connection.Open();                
    var _Reader = command.ExecuteReader();
    while (_Reader.Read())
    {
        CreateNode((int)_Reader["MenuID"], (int)_Reader["ParentID"], (int)_Reader["Rank"], _Reader["English"].ToString());                    
    }
    _Connection.Close();
}

Creation of the nodes:

private void CreateNode(int id, int parentID, int rank, string text)
{            
    if(parentID == -1)
    {
        TreeNode _Node = new TreeNode(text, id.ToString());
        Root.Nodes.Add(_Node);
    }

    if (parentID != -1)
    {
        foreach (TreeNode _Node in Root.Nodes)
        {                    
            if (_Node.Value == parentID.ToString())
            {
                _Node.ChildNodes.Add(new TreeNode(text, id.ToString()) { ShowCheckBox = true } );
            }
        }
    }
}

Currently this isn't sorting the nodes by rank

I'd expect the output HTML to be something similar to the following:

<ul id="1">
    <li>A</li>
    <li>
        <ul id="2">
            <li>B</li>
            <li>
                <ul id="3">
                    <li>C</li>
                    <li>
                        <ul id="4">
                            <li>D</li>
                        </ul>
                    </li>
                    <li>
                        <ul id="5">
                            <li>E</li>
                        </ul>
                    </li>
                </ul>                
            </li>
            <li>
                <ul id="6">
                    <li>F</li>
                </ul>
            </li>
            <li>
                <ul id="7">
                    <li>G</li>
                </ul>
            </li>            
        </ul>
    </li>        
    <li>
        <ul id="8">
            <li>H</li>
            <ul>
                <li>
                    <ul id="9">
                        <li>I</li>
                    </ul>
                </li>
                <li>
                    <ul id="10">
                        <li>J</li>
                    </ul>
                </li>                
            </ul>
        </ul>
    </li>
</ul>

http://jsfiddle.net/RjE7H/

Answer 1

Use a dictionary, that way you don't have to painfully scan your tree for the parent:

Dictionary<int, TreeNode> ParentCache = new Dictionary<int, TreeNode>();
private void CreateNode(int id, int parentID, int rank, string text)
{
    TreeNodeCollection parentNode = root.Nodes;
    if(parentID != 0)
    {
        TreeNode foundParentNode;
        if (!ParentCache.TryGetValue(parentID, out foundParentNode)
            throw new Exception("Given parentID has not been added to the tree yet - " + parentID.ToString());
        parentNode = foundParentNode.ChildNodes;
    }

    TreeNode newNode = new TreeNode(text, id.ToString());
    parentNode.Add(newNode);
    ParentCache.Add(id, newNode);
}

If your data is being received in the order you specified, then the output should be implicitly in rank order. Given that we are always appending to the end of TreeNodeCollections.

If you want to ignore any exception where the node parent is not found, but you still want to attach to the root, make the following changes:

    if(parentID != 0)
    {
        TreeNode foundParentNode;
        //Note: I changed the if logic from, "not TryGetValue" to "TryGetValue"
        if (ParentCache.TryGetValue(parentID, out foundParentNode)
            parentNode = foundParentNode.ChildNodes;
    }