One common theme when dealing with trees (especially in a web context) is to be able to provide what is known as a "breadcrumb" - that is, the path from the root of the tree, to the one we're looking at. For instance, on Developer Fusion the breadcrumb when you look at the ASP.NET section is Web Directory / Programming Languages / ASP.NET.
In order to do this, we need to fetch all rows whose lineage column is a prefix of the lineage column for the node we want to navigate to in the tree. For instance, if we're looking at a node childC whose lineage column is /root/childA/childC/, then we want to return the nodes with lineage columns /root/childA/ and /root/ - that is, the two nodes root and childA.
We'll use the LIKE operator again here, but this could equally be done using a combination of the LEFT and LEN functions.
CREATE PROCEDURE dfTreeGetPath ( @id INT
)
AS
SELECT * FROM dfTree WHERE
(SELECT lineage
FROM dfTree WHERE id = @id) LIKE lineage + '%'
ORDER BY lineage, name
Although we've represented the tree in the SQL Server database, when we execute the dfTreeGetPath stored procedure, we still get a totally linear set of data back. For the moment though, we're not going to bother trying to re-create the tree structure by populating the TreeNode's Children collection. For one thing, it's a little complicated (and explained later), and secondly - we've got no need to. By sorting on the lineage and name columns, the rows are actually going to come back in a very sensible order - the root node first, and the node we're looking at last. To create our breadcrumb on a web page, we'll actually just end up binding an ASP.NET repeater control to this list of nodes.
Here's the code for our SqlServerTreeProvider class.
public ArrayList GetPath(int uniqueID)
{
return ProcessList("dfTreeGetPath",
new SqlParameter("@id",uniqueID));
}
In order to add our breadcrumb to the page we defined earlier, we add a new repeater to the page.
<asp:Repeater Runat="server" ID="breadcrumb">
<ItemTemplate>
<a href='<%# "TreeDemo.aspx?id=" + DataBinder.Eval(Container.DataItem,"UniqueID") %>'>
<%# DataBinder.Eval(Container.DataItem,"Name") %></a>
</ItemTemplate>
<SeparatorTemplate> / </SeparatorTemplate>
</asp:Repeater>
And then simply add the following code to our Page_Load event.
breadcrumb.DataSource = treeProvider.GetPath(treeID);
breadcrumb.DataBind();
You'll also need to have populated the database with some dummy information by this point! After having clicked "Create Nodes" in your browser, you should see something like this:
Looking a bit more hopeful now, isn't it? Because of the way we've designed the database, we've got no limit on the depth of the tree or the number of children that can be displayed. We can also store more than one tree in the database - by simply adding a new root node.
Comments