Introduction:
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.
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!