Click here to Skip to main content
Click here to Skip to main content

Populating a TreeView Control from the Database

, 13 Jul 2006
Rate this:
Please Sign up or sign in to vote.
How to populate a TreeView control from the database.

Introduction

ASP.NET 2.0 came out with tons of new controls which help developers to speed up development. Among all the new controls, the TreeView control stands out as one of the most important and useful controls. The TreeView control enables you to display data in a hierarchical form. The TreeView control also allows you to embed different ASP.NET controls within it. In this article, we will see that how we can populate a TreeView control from the database.

Setting up the Scenario

The TreeView control can be populated with different types of sources, which include the SiteMapDataSource control, XML files, collections, containers, and database tables. The choice of the data source is closely tied with the scenario, and the requirements of the application. Database should be the first choice when you are certain that the data will be constantly changing. It is also the primary choice when you want to perform different operations on the data. These operations include sorting, searching, and ordering.

In this article, I will be using the Northwind database, which is the default database of SQL Server 7 and SQL Server 2000. I will be dealing with the Categories and the Products table in the Northwind database. Since, TreeView is primarily used to display hierarchical data, I will display the items in the Category table and all the products belonging to each category. I will be using entity classes and generic collections to create relationships between the Category class and the Product class. First, let's check out the T-SQL query that will return the results from the Categories and the Products table.

T-SQL Query

The following T-SQL Query is used to get information about categories and products. You can easily change the query to a stored procedure but I wanted to keep things simple, that is why I did not implement a stored procedure.

SELECT p.ProductID, p.ProductName,c.CategoryID,c.CategoryName
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID
ORDER BY c.CategoryID

Creating Entity Classes

The next step is to create the entity classes for the Category and the Products table. I have created a small tool that can be used to create entity classes. You can check out the code for the entity classes below:

public class Product
{
    private int productID;
    private string productName;

    public int ProductID
    {
        get { return this.productID; }
        set { this.productID = value; }
    }

    public string ProductName
    {
        get { return this.productName; }
        set { this.productName = value; }
    }

    public Product(int productID, string productName)
    {
        this.productID = productID;
        this.productName = productName;
    }

    public Product()
    {
    }
}

And here is the Category entity class:

public class Category
{
    private int categoryID;
    private string categoryName;
    private List<Product> productList = 
                   new List<Product>();

    public int CategoryID
    {
        get { return this.categoryID; }
        set { this.categoryID = value; }
    }

    public string CategoryName
    {
        get { return this.categoryName; }
        set { this.categoryName = value; }
    }

    public List<Product> ProductList
    {
        get { return this.productList; }
        set { this.productList = value; }
    }

After creating the entity classes, the next task is to populate the generic category list.

Populating the Generic Category List

Now, let's see how we can create and populate the generic category list. Creating a generic category list is simple, and can be accomplished by a single line of code.

List<Category> categoryList = new List<Category>();

Now, let's see how we can populate the category list. The idea behind populating the category list is that a list can have multiple categories and each category can have multiple products. In other words, the generic list will contain the category objects, and each single category object will contain a list of product objects.

int i = -1;

while (reader.Read())
{
    Category category = new Category();
    category.CategoryID = Convert.ToInt32(reader["CategoryID"]);
    category.CategoryName = reader["CategoryName"] as String;

    if (!DoesCategoryIDAlreadyExists(category.CategoryID, categoryList))
    {
        categoryList.Add(category);
        i++;
        categoryList[i].productList.Add(new Product(
           Convert.ToInt32(reader["ProductID"]), 
           reader["ProductName"] as String));
    }
    else
    {
    categoryList[i].productList.Add(new Product(
           Convert.ToInt32(reader["ProductID"]), 
           reader["ProductName"] as String));
    }
}

The complete code is available in the download. The DoesCategoryIDAlreadyExists checks whether we are dealing with the same category. If we are, then we simply keep on adding the products to that particular category. The approach mentioned above might not be the best approach to populate the category list, and if you come up with something interesting, please let me know. Anyway, after populating the category list, the next task is to populate the TreeView control.

Populating the TreeView Control

Since the data is coming from the database, we have to build the TreeNodes and the corresponding ChildNodes dynamically. The idea is to loop through the categories and create the parent nodes, and loop through the corresponding child nodes to create the products.

// This method is used to populate the TreeView Control
private void PopulateTreeViewControl(List<Category> categoryList)
{
    TreeNode parentNode = null;

    foreach (Category category in categoryList)
    {
        parentNode = new TreeNode(category.CategoryName, 
                     category.CategoryID.ToString());

        foreach (Product product in category.ProductList)
        {
            TreeNode childNode = new TreeNode(product.ProductName, 
                                 product.ProductID.ToString());
            parentNode.ChildNodes.Add(childNode);
        }

        parentNode.Collapse();

        // Show all checkboxes
        tvCategories.ShowCheckBoxes = TreeNodeTypes.All;
        tvCategories.Nodes.Add(parentNode);
    }
}

The PopulateTreeViewControl method takes the category list as a parameter, and iterates through to populate the TreeView control. The Collapse method of the TreeNode is responsible for keeping the TreeView compact so that the leafs are not expanded. Finally, after creating the TreeNodes and ChildNodes, the nodes are added to the TreeView's Nodes collection.

Take a look at the screenshot below:

Conclusion

In this article, we learned how the TreeView control can be populated with the data from the database. The use of entity classes simplified the development, and cut down the lines of code. In later articles, we will see how we can select the nodes using checkboxes inside the TreeView control.

I hope you liked the article, happy coding!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

azamsharp
Web Developer
United States United States
I am the founder of knowledge base website, HighOnCoding, GridViewGuy, RefactorCode.com and ScreencastADay.com.
 
HighOnCoding is a website which will get you high legally with useful information. There are tons of articles, videos and podcasts hosted on HighOnCoding.
 
HighOnCoding.com www.HighOnCoding.com
 

My Blog:

Blog

 

Buy my iPhone app ABC Pop

Comments and Discussions

 
Questionvb.net PinmemberMichael Sichico23-Sep-14 3:04 
SuggestionExcellent Code PinmemberRajeev Mehta18-Aug-14 22:10 
QuestionDoesCategoryIDAlreadyExists PinmemberЧ. Цэрэнпүрэв20-Apr-13 9:32 
QuestionMust modify the code for it to work correctly PinmemberRealKenny21-Nov-11 4:56 
GeneralExcellent Code!!! Pinmemberbohbo14-Apr-10 9:06 
GeneralGreat code Pinmemberangela pop19-Oct-09 1:00 
QuestionDo you have the same code for Win Forms Pinmembersam128747-Jul-08 11:44 
Questionhow to give colors to nodes in treeview Pinmemberatkrishnan20-Jun-08 0:25 
GeneralGet selected item Pinmembervondon13-Feb-08 5:27 
Generalgood code ..thanks Pinmemberrama charan16-Dec-07 22:20 
GeneralWell Done PinmemberJohnAneston20-Nov-07 1:33 
QuestionA question about populating the entity objects Pinmembermikener25-May-07 8:23 
AnswerRe: A question about populating the entity objects Pinmemberjigarchaudhari23-Mar-09 4:56 
GeneralThnks Pinmemberbeolily23-Nov-06 1:06 
GeneralHi Pinmemberkllontop2-Aug-06 8:15 
GeneralRe: Hi Pinmemberbillgate_qn7-Jan-08 21:55 
GeneralRe: Hi PinmemberRachana Chaudhari31-Aug-09 18:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 13 Jul 2006
Article Copyright 2006 by azamsharp
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid