Displaying our Nodes
Now that we've got the basics out of the way, we're in a position to start thinking about what we actually need to display. The first obvious step is to write some functionality that lets us view nodes at a particular level. For example - if we're at the root of our tree, we want an ASP.NET page that can display its children.
The SQL to fetch the direct children of a particular node is very simple:
CREATE PROCEDURE dfTreeGetChildren ( @id INT ) AS
SELECT * FROM dfTree WHERE parentId=@id
This stored procedure will effectively return a list of TreeNode objects - so in our SqlServerTreeProvider class we create a ProcessList procedure that accepts a stored procedure and array of parameters - and returns an ArrayList containing the nodes we're interested in. We can then use this standard procedure for executing dfTreeGetChildren. Note we've also created a TreeNodeFromDataReader method that takes a SqlDataReader object, and returns a TreeNode by reading the appropriate fields.
public ArrayList GetChildren(int uniqueID)
{
return ProcessList("dfTreeGetChildren",
new SqlParameter("@id",uniqueID));
}
protected ArrayList ProcessList(string storedProcedure, params SqlParameter[] parameters) {
using (SqlConnection sqlConn = new SqlConnection(connectionString) )
{
// execute the appropriate sp
SqlCommand sqlComm = new SqlCommand(storedProcedure,sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;
foreach(SqlParameter param in parameters)
sqlComm.Parameters.Add(param);
ArrayList nodes = new ArrayList();
sqlConn.Open();
SqlDataReader dr = sqlComm.ExecuteReader();
while (dr.Read())
nodes.Add ( TreeNodeFromDataReader(dr) );
dr.Close();
sqlConn.Close();
return nodes;
}
}
protected virtual TreeNode TreeNodeFromDataReader(SqlDataReader dr)
{
TreeNode node = new TreeNode(
(int)dr["id"],
(string)dr["name"],
dr["parentId"] == DBNull.Value ? 0 : (int)dr["parentId"],
(int)dr["depth"]);
return node;
}
Now that's sorted, we can get started on our ASP.NET page. For the moment, this will consist of a single Repeater control, that we will bind to the ArrayList returned by the GetChildren method we defined above.
TreeDemo.aspx
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD><title>Tree Demo</title>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:Repeater Runat="server" ID="categoryList">
<ItemTemplate>
<a href='<%# "TreeDemo.aspx?id=" + DataBinder.Eval(Container.DataItem,"UniqueID") %>'>
<%# DataBinder.Eval(Container.DataItem,"Name") %></a>
</ItemTemplate>
<SeparatorTemplate> <br /> </SeparatorTemplate>
</asp:Repeater>
</form>
</body>
</HTML>
This is the code we'll use in the code-behind.
private void Page_Load(object sender, System.EventArgs e)
{
// we could easily implement alternate stored procedures that
// let us fetch a "default" root item id depending on its name
int treeID = 1; // default to some tree or other
if (Request.QueryString["id"]!=null)
treeID = Int32.Parse(Request.QueryString["id"]);
SqlServerTreeProvider treeProvider = new SqlServerTreeProvider();
categoryList.DataSource = treeProvider.GetChildren(treeID);
categoryList.DataBind();
}
Now, supposing we created some nodes as follows:
SqlServerTreeProvider treeProvider = new SqlServerTreeProvider();
int indexID = treeProvider.AddNode(new TreeNode("Index",0));
int langID = treeProvider.AddNode(new TreeNode("Languages",indexID));
treeProvider.AddNode(new TreeNode("C#",langID));
treeProvider.AddNode(new TreeNode("VB.NET",langID));
treeProvider.AddNode(new TreeNode("Technologies",indexID));
and then visited TreeDemo.aspx?id=#langID#, we would see something like this:
Not exactly impressive really, is it?! However, it's a start. It would be more useful if we could know where we actually are within the tree - so, next up is providing some "breadcrumbs".