Tree structures in ASP.NET and SQL Server

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:

A diagram displaying the structure of the table, with these fields: id (int, primary key), parentId (int), name (varchar), depth (int), lineage (varchar).

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".

You might also like...

Comments

About the author

James Crowley

James Crowley United Kingdom

James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audien...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There's no test like production” - Anon