Building a SQL Hierarchical Data Source Control

In this final section of this chapter, we build a SqlHierarchicalDataSource control. This custom control enables you to declaratively and (thus) easily bind controls such as the Menu and treeView controls to data retrieved from a database.

Note

The code samples in this section can be found in the SqlHierarchicalDataSourceVB and SqlHierarchicalDataSourceCS applications on the CD.

The page in Listing 17.32 illustrates how you can use the SqlHierarchicalDataSource control to bind a Menu control to a database table that contains nested categories.

Listing 17.32. `ShowMenu.aspx`

<%@ Page Language="VB" %>
<%@ Register TagPrefix="custom" Namespace="AspNetUnleashed" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">

    Sub Menu1_MenuItemClick(sender As Object, e As MenuEventArgs)
        lblSelected.Text = Menu1.SelectedValue
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        .menu
        {
            border:solid 1px black;
            padding:4px;
        }
    </style>
    <title>Show Menu</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>


    <asp:Menu
        id="Menu1"
        DataSourceId="srcCategories"
        OnMenuItemClick="Menu1_MenuItemClick"
        Orientation="Horizontal"
        DynamicMenuStyle-CssClass="menu"
        Runat="server">
        <DataBindings>
            <asp:MenuItemBinding TextField="Name" ValueField="Name" />
        </DataBindings>
    </asp:Menu>


    <custom:SqlHierarchicalDataSource
        id="srcCategories"
        ConnectionString='<%$ ConnectionStrings:Categories %>'
        DataKeyName="CategoryId"
        DataParentKeyName="ParentId"
        SelectCommand="SELECT CategoryId, ParentId, Name FROM Categories"
        Runat="server" />


    <hr />


    <asp:Label
        id="lblSelected"
        Runat="server" />


    </div>
    </form>
</body>
</html>

When you open the page in Listing 17.32, all the rows from the Categories table are displayed in the Menu control.

Notice that the SqlHierarchicalDataSource control includes two properties: DataKeyName and DataParentKeyName . The DataKeyName property represents the name of a database column that contains a unique value for each database table row. The DataParentKeyName column represents the name of a database column that relates each row to its parent row.

Furthermore, notice that the Menu control includes a MenuItemBinding , which associates the database Name column with the Menu item Text property, and the Name column with the Menu item Value property.

You also can use the SqlHierarchicalDataSource control when working with the treeView control. The page in Listing 17.33 displays all the rows from the Discuss database table in a treeView control.

Listing 17.33. `ShowTreeView.aspx`

<%@ Page Language="VB" %>
<%@ Register TagPrefix="custom" Namespace="AspNetUnleashed" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
 "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">


    Sub TreeView1_SelectedNodeChanged(sender As object, e As EventArgs)
        lblSelected.Text = TreeView1.SelectedValue
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show TreeView</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>


    <asp:TreeView
        id="TreeView1"
        DataSourceID="srcDiscuss"
        OnSelectedNodeChanged="TreeView1_SelectedNodeChanged"
        ImageSet="News"
        Runat="server">
        <DataBindings>
            <asp:TreeNodeBinding
                TextField="Subject"
                ValueField="MessageId" />
        </DataBindings>
    </asp:TreeView>

    <custom:SqlHierarchicalDataSource
        id="srcDiscuss"
        ConnectionString='<%$ ConnectionStrings:Discuss %>'
        DataKeyName="MessageId"
        DataParentKeyName="ParentId"
        SelectCommand="SELECT MessageId,ParentId,Subject FROM Discuss"
        Runat="server" />


    <hr />


    You selected message number:
    <asp:Label
        id="lblSelected"
        Runat="server" />


    </div>
    </form>
</body>
</html>

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

When you open the page in Listing 17.33, the contents of the Discuss database table are displayed in the treeView control.

All the code for the SqlHierarchicalDataSource control is included on the CD that accompanies this book. The control is composed out of five separate classes:

SqlHierarchicalDataSource This class represents the actual control. It inherits from the base SqlDataSource control and implements the IHierarchicalDataSource interface.
SqlHierarchicalDataSourceView This class represents the hierarchical data returned by the control. It inherits from the base HierarchicalDataSourceView class.
SqlHierarchicalEnumerable This class represents a collection of SqlNodes .
SqlNode This class represents a particular database row from the data source. It includes methods for retrieving child and parent rows.
SqlNodePropertyDescriptor This class inherits from the base PropertyDescriptor class. It converts the database columns represented by a SqlNode into class properties so that you can bind to the columns using TReeView and Menu control DataBindings.

Note

The Microsoft .NET Framework SDK Documentation includes a sample of a FileSystemDataSource control that implements the IHiearchicalDataSource interface. Look up the IHearchicalDataSource topic in the documentation index.