In many cases, you may want to populate your tree from dynamic data. Obtaining your values from a database allows menu and input trees to change on the fly. While there are ways to convert your database results into XML and then populate the tree with the converted data, we can skip this conversion step and fill the tree directly from database using straightforward ADO.NET.
For the database example, we'll generate a list of Suppliers and the Products each makes. The Northwind example database in SQL 2000 will be our source. Here's a view of the tree we'll create, collapsed and expanded:
On your Web form, drag a TreeView control over and name it TreeView1. Below is the control code. Remember to include an import for System.Data, System.Data.SqlClient, and Microsoft.Web.UI.WebControls:
Dim strConn As String
= "server=.;database=Northwind;integrated security=true;"
Dim objConn As New
SqlConnection(strConn)
Dim objDS As New
DataSet
Dim daSuppliers As New SqlDataAdapter("SELECT CompanyName,SupplierID FROM
Suppliers", objConn)
Dim daProducts As New
SqlDataAdapter("SELECT ProductName, ProductID, SupplierID FROM
Products", objConn)
daSuppliers.Fill(objDS,
"dtSuppliers")
daProducts.Fill(objDS,
"dtProducts")
objConn.Close()
objDS.Relations.Add("SuppToProd", _
objDS.Tables("dtSuppliers").Columns("SupplierID"), _
objDS.Tables("dtProducts").Columns("SupplierID"))
Dim nodeSupp, nodeProd As TreeNode
Dim rowSupp, rowProd As DataRow
For Each rowSupp In objDS.Tables("dtSuppliers").Rows
nodeSupp = New TreeNode
nodeSupp.Text = rowSupp("CompanyName")
nodeSupp.ID = rowSupp("SupplierID")
TreeView1.Nodes.Add(nodeSupp)
For Each rowProd In rowSupp.GetChildRows("SuppToProd")
nodeProd = New TreeNode
nodeProd.Text =
rowProd("ProductName")
nodeProd.ID = rowProd("ProductID")
nodeSupp.Nodes.Add(nodeProd)
Next
Next
'clean up
objDS.Dispose()
daSuppliers.Dispose()
daProducts.Dispose()
objConn.Close()
objConn.Dispose()
Place the control code in your Page Load area. Again, remember to include an import for System.Data, System.Data.SqlClient, and Microsoft.Web.UI.WebControls.
The first three lines are standard database connection objects:
Dim strConn As String = "server=.;database=Northwind;integrated
security=true;"
Dim objConn As New SqlConnection(strConn)
Dim objDS As New DataSet
The important point on the next two SQL statements is that each statement includes a common relation field. In this case, SupplierID will connect the Suppliers to their Products. We will create a separate Data Adapter for each parent child relationship. In this case, there are only two, Suppliers and Products. If there were a relation under Products, like Colors available, we would create a third Data Adapter that would include the ProductID so Colors could be tied back to Products:
Dim daSuppliers As New SqlDataAdapter("SELECT
CompanyName,SupplierID FROM Suppliers", objConn)
Dim daProducts As New SqlDataAdapter("SELECT ProductName,
ProductID, SupplierID FROM Products", objConn)
Next we fill both Data Adapters then close our connection:
daSuppliers.Fill(objDS,
"dtSuppliers")
daProducts.Fill(objDS,
"dtProducts")
objConn.Close()
Relation: The key to the TreeView is the relation. When a DataSet has more than one DataTable, a relation can be defined. There must be a common field in both to define the Parent Child relationship. In our case, it's the SupplierID.
objDS.Relations.Add("SuppToProd",
_
objDS.Tables("dtSuppliers").Columns("SupplierID"),
_
objDS.Tables("dtProducts").Columns("SupplierID"))
If we had our third relation of Color, there would be a second relation statement tying Products to Color.
Now we create our TreeView datatypes:
Dim nodeSupp, nodeProd As
TreeNode
Dim rowSupp, rowProd As
DataRow
Loop through our data sets and populate the TreeView. The ID attribute at +++ is not needed for our example to function. Including it gives a convenient method of determining your database ID values during click and check events.
For Each rowSupp In objDS.Tables("dtSuppliers").Rows
nodeSupp = New TreeNode
nodeSupp.Text = rowSupp("CompanyName")
nodeSupp.ID = rowSupp("SupplierID") +++
TreeView1.Nodes.Add(nodeSupp)
For Each
rowProd In rowSupp.GetChildRows("SuppToProd")
nodeProd = New TreeNode
nodeProd.Text = rowProd("ProductName")
nodeProd.ID = rowProd("ProductID")
nodeSupp.Nodes.Add(nodeProd)
***
Next
Next
If you had a third relation, like the Colors available mentioned previously, we would create a third For Next loop located at the *** marker.
