Populating TreeView Using Database

ASP.NET 2.0 came out with tons of new controls which helps the developer to speed development. Among all the new controls TreeView control stands out as one of the most important and useful control. TreeView control enables you to display the data in hierarchical form. 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.

Introduction:

Setting Up the Scenario:

TreeView control can be populated with different types of sources which include SiteMapDataSource control, XML File, 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 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 includes sorting, searching and ordering.

In this article I will be using the Northwind database which, is the default database of the 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 the hierarchical data that is why I will display Category and all the products belonging to that 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 the 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 implemented the 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 Categories and Products table. I have created a small tool that can be used to create the entity classes. You can download the tool from this link. Nevertheless, 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 that 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 iterate 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 TreeNode and ChildNodes the nodes are the adding the the TreeView nodes collection.

Take a look at the screen shot below:

Conclusion:

In this article we learned that 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 that how we can select the nodes using the checkboxes inside the TreeView control.

I hope you liked the article, happy coding!