Displaying Relational Data in the TreeView Control
The TreeView control can display any type of hierarchical data, even if the data hierarchy is logical, as in a database, and not physical, as in an XML file. In this section, you will use the TreeView control to display data from related tables in the Northwind database.
To start, you will create a connection to the computer running SQL Server where you have the Northwind database.
To create a connection to SQL Server
-
In Server Explorer, right-click Data Connections, and then click Add Connection. If you are using Visual Web Developer Express, use Database Explorer.
The Add Connection dialog box appears.
-
If the Data source list does not display Microsoft SQL Server (SqlClient), click Change, and in the Change Data Source dialog box, select Microsoft SQL Server.
-
If the Choose Data Source page appears, in the Data source list, select the type of data source you will use. For this walkthrough, the data source type is Microsoft SQL Server. In the Data provider list, click .NET Framework Data Provider for SQL Server, and then click Continue.
.gif) Note |
|---|
| If the Server Explorer tab is not visible in Visual Web Developer, in the View menu, click Server Explorer. If the Database Explorer tab is not visible, in the View menu, click Database Explorer. |
-
In the Add Connection box, enter your server name in the Server Name box.
-
For the Log on to the server section, select the option that is appropriate to access the running the SQL Server database (integrated security or specific ID and password) and, if required, enter a user name and password.
-
Select the Save my Password check box.
| Note |
|---|
| In production applications, do not use Save my Password because this embeds the user name and password in the application files. |
-
Under Select or enter a database name, enter Northwind.
-
Click Test Connection, and when you are sure that it works, click OK.
Your new connection has been created under Data Connections in Server Explorer (or Database Explorer).
Configuring a TreeView Control to Display Database Data
In this section, you will dynamically populate nodes with data. The first-level nodes will represent master data — in this case, categories. When users click a node, the child nodes for the category will be created by making a query to the database that retrieves the products for that category. To retrieve the data, you can use a data source control. However, in this walkthrough, you will create and execute a query programmatically.
To begin, create a new page and a new TreeView control.
To create the new page and TreeView control
-
Add an ASP.NET Web page (Web Form page) named TreeViewDynamic.aspx to your Web site.
-
Open the TreeViewDynamic.aspx page, switch to Design view, and then in the Toolbox, from the Standard group, drag a Label control onto the page and name it labelStatus.
The labelStatus control is used for error reporting only.
-
In the Toolbox, from the Navigation group, drag a TreeView control onto the page.
-
Right-click the TreeView control, click Properties, and then set MaxDataBindDepth to 2.
-
Right-click the TreeView control, click Show Smart Tasks, and then on the TreeView Tasks menu, click Edit Nodes.
-
In the TreeView Node Editor dialog box, click the icon labeled Add a root node, and then, under Properties, set Text to Product List and PopulateOnDemand to true.
-
Click OK.
You are creating the topmost node of the tree, which contains only static text.
To configure the Web.config file
-
In the Toolbox, from the Data group, drag a SqlDataSource control onto the page.
-
Select the SqlDataSource control, and then click Show Smart Tag.
-
On the SqlDataSource Tasks menu, click Configure Data Source.
The Configure Data Source - SqlDataSource1 wizard displays a page in which you can choose a connection.
-
In the Which data connection should your application use to connect to the database? box, enter the connection that you created in "To create a connection to SQL Server," and then click Next.
The wizard displays a page in which you can choose to store the connection string in a configuration file. Storing the connection string in the configuration file has two advantages:
-
Select the Yes, save this connection as check box, and then click Next.
The wizard displays a page in which you can specify what data you want to retrieve from the database.
-
Under Specify columns from a table or view, in the Name box, click Categories.
-
Under Columns, select the CategoryID and CategoryName boxes.
-
Click Next.
-
Click Finish.
You will use the connection string created in the Web.config file later, in the RunQuery method defined later in this walkthrough. You will not use the SqlDataSource control.
Now, you will add code to populate the control's child nodes when users click the node. To add nodes dynamically, you create an event handler for the TreeNodePopulate event.
To create the event handler
-
Right-click the TreeView control, and then in Properties, click the Events icon.
-
Double-click the box for the TreeNodePopulate event.
Visual Web Developer switches to Source view.
-
Add the following highlighted code to the handler.
Protected Sub TreeView1_TreeNodePopulate(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.TreeNodeEventArgs) _
Handles TreeView1.TreeNodePopulate
If e.Node.ChildNodes.Count = 0 Then
Select Case e.Node.Depth
Case 0
PopulateCategories(e.Node)
Case 1
PopulateProducts(e.Node)
End Select
End If
End Sub
protected void TreeView1_TreeNodePopulate(
object sender, TreeNodeEventArgs e)
{
if (e.Node.ChildNodes.Count == 0)
{
switch (e.Node.Depth)
{
case 0:
PopulateCategories(e.Node);
break;
case 1:
PopulateProducts(e.Node);
break;
}
}
}
This code is called when a user clicks a node to open it. Because you want to display different data at different levels of the tree, you must determine which node depth the user has clicked, and then populate the nodes at that level appropriately. In this walkthrough, the PopulateCategories method is called if the user clicks the root node (depth 0). The PopulateProducts method is called if the user clicks a category name (depth 1). The methods are shown in the next section.
The TreeNodeEventArgs object provides programmatic access to the current node. To populate the node, you add elements to it. In the example code, the node is passed to the method that will add the child nodes.
Reading Node Data from the Database
The information to display in each node comes from the database. You must write the code that performs the database query, reads the records, and creates a node for each record. This walkthrough assumes that you are working with the SQL Server Northwind sample database, so you must use ADO.NET objects from the System.Data.SqlClient namespace.
For the first level of nodes (level 0), you will display a list of all available categories. The code you create calls a RunQuery method that you will create later in the walkthrough.
To add nodes for all categories
-
Switch to Source view.
-
If you are working with a single-file page, add the following directives to the top of the code page.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
Importing the namespaces will make it easier to write the code you need.
-
If you are working with a code-behind page, switch to the code-behind page (TreeViewDynamic.aspx.vb or TreeViewDynamic.aspx.cs) and add the following lines to the top of the code file, outside the class declaration.
Imports System.Data
Imports System.Data.SqlClient
using System.Data;
using System.Data.SqlClient;
-
Make sure the page is still in Source view.
-
Add the following method to the page code.
Sub PopulateCategories(ByVal node As TreeNode)
Dim sqlQuery As New SqlCommand( _
"Select CategoryName, CategoryID From Categories")
Dim ResultSet As DataSet
ResultSet = RunQuery(sqlQuery)
If ResultSet.Tables.Count > 0 Then
Dim row As DataRow
For Each row In ResultSet.Tables(0).Rows
Dim NewNode As TreeNode = New _
TreeNode(row("CategoryName").ToString(), _
row("CategoryID").ToString())
NewNode.PopulateOnDemand = True
NewNode.SelectAction = TreeNodeSelectAction.Expand
node.ChildNodes.Add(NewNode)
Next
End If
End Sub
void PopulateCategories(TreeNode node)
{
SqlCommand sqlQuery = new SqlCommand(
"Select CategoryName, CategoryID From Categories");
DataSet resultSet;
resultSet = RunQuery(sqlQuery);
if (resultSet.Tables.Count > 0)
{
foreach (DataRow row in resultSet.Tables[0].Rows)
{
TreeNode NewNode = new
TreeNode(row["CategoryName"].ToString(),
row["CategoryID"].ToString());
NewNode.PopulateOnDemand = true;
NewNode.SelectAction = TreeNodeSelectAction.Expand;
node.ChildNodes.Add(NewNode);
}
}
}
The code creates a SqlCommand object that encapsulates the text of the query. It passes the object to a method (which you will write) that performs the database query and returns a DataSet object. The code then loops through the records in the DataSet object and creates a new node for each record, setting the text and value of the node with database information. Then, the code sets the PopulateOnDemand property of each node to true so that the node will raise its TreeNodePopulate event when clicked. The SelectAction property is set so that the nodes are expanded by default.
The second level of nodes will display the products for each category. For this reason, populating the products nodes requires a parameterized query so that you can retrieve the products for the current category and populate the child nodes appropriately.
To add nodes for products
-
Add the following method to the page code.
Sub PopulateProducts(ByVal node As TreeNode)
Dim sqlQuery As New SqlCommand
sqlQuery.CommandText = "Select ProductName From Products " & _
" Where CategoryID = @categoryid"
sqlQuery.Parameters.Add("@categoryid", SqlDbType.Int).Value = _
node.Value
Dim ResultSet As DataSet = RunQuery(sqlQuery)
If ResultSet.Tables.Count > 0 Then
Dim row As DataRow
For Each row In ResultSet.Tables(0).Rows
Dim NewNode As TreeNode = New _
TreeNode(row("ProductName").ToString())
NewNode.PopulateOnDemand = False
NewNode.SelectAction = TreeNodeSelectAction.None
node.ChildNodes.Add(NewNode)
Next
End If
End Sub
void PopulateProducts(TreeNode node)
{
SqlCommand sqlQuery = new SqlCommand();
sqlQuery.CommandText = "Select ProductName From Products " +
" Where CategoryID = @categoryid";
sqlQuery.Parameters.Add("@categoryid", SqlDbType.Int).Value =
node.Value;
DataSet ResultSet = RunQuery(sqlQuery);
if (ResultSet.Tables.Count > 0)
{
foreach (DataRow row in ResultSet.Tables[0].Rows)
{
TreeNode NewNode = new
TreeNode(row["ProductName"].ToString());
NewNode.PopulateOnDemand = false;
NewNode.SelectAction = TreeNodeSelectAction.None;
node.ChildNodes.Add(NewNode);
}
}
}
This code is similar to the code used to populate the categories node. One difference is that the SqlCommand object is configured with a parameter that is set at run time with the value of the node that the user clicked; that is, of the selected category. Another difference is that the PopulateOnDemand property is set to false. This causes the products nodes to be displayed without an expansion button, which is necessary because there are no more nodes below products.
The final step is to create the method that performs the query and returns the dataset.
To perform the query
-
Add the following subroutine to the page.
Function RunQuery(ByVal sqlQuery As SqlCommand) As DataSet
Dim connectionString As String
connectionString = _
ConfigurationManager.ConnectionStrings _
("NorthwindConnectionString").ConnectionString
Dim dbConnection As New SqlConnection
dbConnection.ConnectionString = connectionString
Dim dbAdapter As New SqlDataAdapter
dbAdapter.SelectCommand = sqlQuery
sqlQuery.Connection = dbConnection
Dim resultsDataSet As DataSet = New DataSet
Try
dbAdapter.Fill(resultsDataSet)
Catch ex As Exception
labelStatus.Text = "Unable to connect to SQL Server."
End Try
Return resultsDataSet
End Function
private DataSet RunQuery(SqlCommand sqlQuery)
{
string connectionString =
ConfigurationManager.ConnectionStrings
["NorthwindConnectionString"].ConnectionString;
SqlConnection DBConnection =
new SqlConnection(connectionString);
SqlDataAdapter dbAdapter = new SqlDataAdapter();
dbAdapter.SelectCommand = sqlQuery;
sqlQuery.Connection = DBConnection;
DataSet resultsDataSet = new DataSet();
try
{
dbAdapter.Fill(resultsDataSet);
}
catch
{
labelStatus.Text = "Unable to connect to SQL Server.";
}
return resultsDataSet;
}
This code creates a data adapter based on the SqlCommand object passed to it. Then, it creates and populates a dataset with the adapter.
You can now test the page.
To test the page
-
Press CTRL+F5 to run the page.
The TreeView control is displayed with a list of categories and products.
-
Click a category to confirm that it collapses and expands to show a list of products for each category.