Part 162 - Binding asp.net treeview control to database table

Suggested Videos
Part 159 - TreeView control
Part 160 - Binding asp.net treeview control to an xml file
Part 161 - Binding treeview control to web.sitemap file



In this video, we will discuss binding asp.net treeview control to database table. This is continuation to Part 161, please watch Part 161 before proceeding.

1. Create the required database table using the script below.
Create table tblTreeViewItems
(
ID int identity primary key,
TreeViewText nvarchar(50),
NavigateURL nvarchar50),
ParentId int references tblTreeViewItems(ID)
)



-- Insert top level TreeView items
Insert into tblTreeViewItems values ('Home', '~/Home.aspx', NULL)
Insert into tblTreeViewItems values ('Employee', '~/Employee.aspx', NULL)
Insert into tblTreeViewItems values ('Employer', '~/Employer.aspx', NULL)
Insert into tblTreeViewItems values ('Admin', '~/Admin.aspx', NULL)
-- Insert Employee child items
Insert into tblTreeViewItems values ('Upload Resume', '~/UploadResume.aspx', 2)
Insert into tblTreeViewItems values ('Edit Resume', '~/EditResume.aspx', 2)
Insert into tblTreeViewItems values ('View Resume', '~/ViewResume.aspx', 2)
-- Insert Employer child items
Insert into tblTreeViewItems values ('Upload Job', '~/UploadJob.aspx', 3)
Insert into tblTreeViewItems values ('Edit Job', '~/EditJob.aspx', 3)
Insert into tblTreeViewItems values ('View Job', '~/ViewJob.aspx', 3)
-- Insert Admin child items
Insert into tblTreeViewItems values ('Add User', '~/AddUser.aspx', 4)
Insert into tblTreeViewItems values ('Edit User', '~/EditUser.aspx', 4)
Insert into tblTreeViewItems values ('View User', '~/ViewUser.aspx', 4)

2. Create a stored procedure that returns data from tblTreeViewItems table.
Create proc spGetTreeViewItems
as
Begin
Select ID, TreeViewText, NavigateURL, ParentId
from tblTreeViewItems
End

3. Drag and drop a treeview control on the webform
<asp:TreeView ID="Treeview1" runat="server">
</asp:TreeView>

4. Copy and paste the following ado.net code in the code-behind file.
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GetTreeViewItems();
    }
}

private void GetTreeViewItems()
{
    string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    SqlConnection con = new SqlConnection(cs);
    SqlDataAdapter da = new SqlDataAdapter("spGetTreeViewItems", con);
    DataSet ds = new DataSet();
    da.Fill(ds);

    ds.Relations.Add("ChildRows", ds.Tables[0].Columns["ID"], 
        ds.Tables[0].Columns["ParentId"]);

    foreach (DataRow level1DataRow in ds.Tables[0].Rows)
    {
        if (string.IsNullOrEmpty(level1DataRow["ParentId"].ToString()))
        {
            TreeNode treeNode = new TreeNode();
            treeNode.Text = level1DataRow["TreeViewText"].ToString();
            treeNode.NavigateUrl = level1DataRow["NavigateURL"].ToString();

            DataRow[] level2DataRows = level1DataRow.GetChildRows("ChildRows");
            foreach (DataRow level2DataRow in level2DataRows)
            {
                TreeNode childTreeNode = new TreeNode();
                childTreeNode.Text = level2DataRow["TreeViewText"].ToString();
                childTreeNode.NavigateUrl = level2DataRow["NavigateURL"].ToString();
                treeNode.ChildNodes.Add(childTreeNode);
            }
            Treeview1.Nodes.Add(treeNode);
        }
    }
}

Note: Please include the following using declarations.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

The above code works as expected only with 2 levels of TreeNode objects. If you include a third level, those TreeNodes will not be displayed in the TreeView control. For example, if you execute the following insert sql script. These rows will not be displayed in the TreeView control, we will discuss fixing this in our next video.
Insert into tblTreeViewItems values ('AAA', '~/AAA.aspx', 5)
Insert into tblTreeViewItems values ('BBB', '~/BBB.aspx', 5)