Populating the TreeView Control from a Database 4

Database Populating

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

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

Continue to Next Page

Page 1, Page 2, Page 3, Page 4, Page 5

Download / Print Complete PDF