TreeView bound to database

[vsiat]

I am trying to create a treeview out of a database table
with the typical structure ID, NAME, PARENTID, TYPE,
EXTRA_INFO, where [parentid] is linked to the [id].

What I want to achieve is create a tree made of custom,
extended nodes, which include all the extra information
contained in the table and not just typical TreeNode
objects.

To do that, I first created a structure with all the extra
information about the node (taken from the table), and
then created my own treenode class, adding the structure
as an extra property.

I am now trying to populate the treeview with my
treenodes, but that doesn't seem to work.

Any ideas or links to examples would be much appreciated.

 

[Ken Tucker [MVP]]

Hi,

http://support.microsoft.com/default.aspx?scid=kb;en-us;308063

Ken

 

[Jay B. Harlow [MVP - Outlook]]

Vsiat,
In addition to the others comments, here is a Routine I put together a
couple of weeks ago:

Something like (for the SQL Server Northwind database, the Employees table).

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim data As DataSet = BuildEmployees()
Dim connection As New SqlClient.SqlConnection("packet
size=4096;integrated security=SSPI;data source=.;persist security
info=False;initial catalog=Northwind")
Dim adapter As New SqlClient.SqlDataAdapter("Select EmployeeID,
LastName, FirstName, ReportsTo from Employees", connection)
adapter.Fill(data, "Employees")

For Each row As DataRow In
data.Tables("Employees").Select("ReportsTo is Null")
AddChild(TreeView1.Nodes, row)
Next
End Sub

Private Sub AddChild(ByVal nodes As TreeNodeCollection, ByVal row As
DataRow)

Const format As String = "{1}, {0}"
Dim node As New TreeNode(String.Format(format, row("FirstName"),
row("LastName")))
nodes.Add(node)
AddChildren(node, row)
End Sub

Private Sub AddChildren(ByVal parentNode As TreeNode, ByVal parentRow As
DataRow)
For Each childRow As DataRow In
parentRow.GetChildRows("EmployeesEmployees")
AddChild(parentNode.Nodes, childRow)
Next
End Sub

Private Function BuildEmployees() As DataSet
Dim data As New DataSet
Dim table As New DataTable("Employees")
Dim columnEmployeeID As New DataColumn("EmployeeID",
GetType(Integer))
table.Columns.Add(columnEmployeeID)
Dim columnLastName As New DataColumn("LastName", GetType(String))
table.Columns.Add(columnLastName)
Dim columnFirstName As New DataColumn("FirstName", GetType(String))
table.Columns.Add(columnFirstName)
Dim columnReportsTo As New DataColumn("ReportsTo", GetType(Integer))
table.Columns.Add(columnReportsTo)

table.PrimaryKey = New DataColumn() {columnEmployeeID}
data.Tables.Add(table)
data.Relations.Add("EmployeesEmployees", columnEmployeeID,
columnReportsTo)
Return data
End Function

Instead of building the table inline as I have, you could use a typed
dataset.

The important things in this routine are:
1. The primary key on the Employees data table.
2. The relationship between the EmployeeID column & the ReportsTo column of
the Employees data table.
3. The GetChildRows on the relationship defined in item 2.

An alternative is to only bring back child nodes when that node is expanded,
which has a significantly better startup, but the initial expanding of each
node is slightly slower. Unfortunately I do not have a full sample of this
alternative handy.

Hope this helps
Jay

I am trying to create a treeview out of a database table
with the typical structure ID, NAME, PARENTID, TYPE,
EXTRA_INFO, where [parentid] is linked to the [id].

What I want to achieve is create a tree made of custom,
extended nodes, which include all the extra information
contained in the table and not just typical TreeNode
objects.

To do that, I first created a structure with all the extra
information about the node (taken from the table), and
then created my own treenode class, adding the structure
as an extra property.

I am now trying to populate the treeview with my
treenodes, but that doesn't seem to work.

Any ideas or links to examples would be much appreciated.

 

[vsiat]

by studying your example, I found a few major defects
(concernig only what I am trying to do, of course)

1. the tree is not bound directly to the database. An xml
file must be created, re-created on every refresh, and
probably saved on disk, before data can be displayed on
the tree.

2. for each tree node, only the [name] field is actually
contained in the node's properties, therefore no clue
about the database record containing the rest of the node
information (like the [id] column) can be obtained
directly from the tree.


the ideal case for me would be to have a dataview
presenting the database contents the way I want, and then
(bound on that dataview) a treeview control showing a
hierarchical representation of the data and at the same
time being the database navigation instrument for the
user, as well as other controls (textboxes mainly) showing
the rest of the record contents regarding the node
selected by the user on the treeview. That is, controls
also bound on the same dataview, with their contents
synchronized with the treeview selected node.

-----Original Message-----
Hi,

http://support.microsoft.com/default.aspx?scid=kb;en- us;308063

Ken
---------------

I am trying to create a treeview out of a database table
with the typical structure ID, NAME, PARENTID, TYPE,
EXTRA_INFO, where [parentid] is linked to the [id].

What I want to achieve is create a tree made of custom,
extended nodes, which include all the extra information
contained in the table and not just typical TreeNode
objects.

To do that, I first created a structure with all the extra
information about the node (taken from the table), and
then created my own treenode class, adding the structure
as an extra property.

I am now trying to populate the treeview with my
treenodes, but that doesn't seem to work.

Any ideas or links to examples would be much appreciated.

.

 

[vsiat]

clever approach regarding the way the tree is populated,
but still not solving my problem.

you are using traditional treenode objects with the .text
property being the only database-related information about
them and I really can't see how that could be used, for
example, to locate the selected node in the underlying
table --my major problem.
I'm stuck to believing that other info (particularly, the
table primary key) should also be loaded in the node
properties, in order to become useful for synchronizing
other controls bound on the same table with the treeview.
If the [id] field (in my example) could be part of the
node properties then whenever the user navigates to
another node the related table row could be easily
referenced by other controls on the same form and extra
info about the selection displayed.
But to do that, you have to use a custom node class,
having an .id property apart from the default .text one.
The same goes for any other extra information one wishes
the node to have.

I will try to use a custom node class with your code to
see what happens.

-----Original Message-----
Vsiat,
In addition to the others comments, here is a Routine I put together a
couple of weeks ago:

Something like (for the SQL Server Northwind database, the Employees table).

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim data As DataSet = BuildEmployees()
Dim connection As New SqlClient.SqlConnection ("packet
size=4096;integrated security=SSPI;data source=.;persist security
info=False;initial catalog=Northwind")
Dim adapter As New SqlClient.SqlDataAdapter ("Select EmployeeID,
LastName, FirstName, ReportsTo from Employees", connection)
adapter.Fill(data, "Employees")

For Each row As DataRow In
data.Tables("Employees").Select("ReportsTo is Null")
AddChild(TreeView1.Nodes, row)
Next
End Sub

Private Sub AddChild(ByVal nodes As

TreeNodeCollection, ByVal row As

DataRow)

Const format As String = "{1}, {0}"
Dim node As New TreeNode(String.Format(format, row ("FirstName"),
row("LastName")))
nodes.Add(node)
AddChildren(node, row)
End Sub

Private Sub AddChildren(ByVal parentNode As TreeNode, ByVal parentRow As
DataRow)
For Each childRow As DataRow In
parentRow.GetChildRows("EmployeesEmployees")
AddChild(parentNode.Nodes, childRow)
Next
End Sub

Private Function BuildEmployees() As DataSet
Dim data As New DataSet
Dim table As New DataTable("Employees")
Dim columnEmployeeID As New DataColumn ("EmployeeID",
GetType(Integer))
table.Columns.Add(columnEmployeeID)
Dim columnLastName As New DataColumn("LastName", GetType(String))
table.Columns.Add(columnLastName)
Dim columnFirstName As New DataColumn ("FirstName", GetType(String))
table.Columns.Add(columnFirstName)
Dim columnReportsTo As New DataColumn

("ReportsTo", GetType(Integer))

table.Columns.Add(columnReportsTo)

table.PrimaryKey = New DataColumn() {columnEmployeeID}

data.Tables.Add(table)
data.Relations.Add("EmployeesEmployees",

columnEmployeeID,
columnReportsTo)
Return data
End Function

Instead of building the table inline as I have, you could use a typed
dataset.

The important things in this routine are:
1. The primary key on the Employees data table.
2. The relationship between the EmployeeID column & the ReportsTo column of
the Employees data table.
3. The GetChildRows on the relationship defined in item 2.

An alternative is to only bring back child nodes when that node is expanded,
which has a significantly better startup, but the initial expanding of each
node is slightly slower. Unfortunately I do not have a full sample of this
alternative handy.

Hope this helps
Jay

I am trying to create a treeview out of a database table
with the typical structure ID, NAME, PARENTID, TYPE,
EXTRA_INFO, where [parentid] is linked to the [id].

What I want to achieve is create a tree made of custom,
extended nodes, which include all the extra information
contained in the table and not just typical TreeNode
objects.

To do that, I first created a structure with all the extra
information about the node (taken from the table), and
then created my own treenode class, adding the structure
as an extra property.

I am now trying to populate the treeview with my
treenodes, but that doesn't seem to work.

Any ideas or links to examples would be much appreciated.

.

 

[Jay B. Harlow [MVP - Outlook]]

vsiat,
I would use a custom node class, others might use the TreeNode.Tag property.

I would add a single "DataRow" property on my custom Node class that
contained the "DataRow" the node is "bound" to (rather then individual
properties for each field). I would consider making the "DataRow" property
type System.Object, so as I could support binding to any class and not just
DataTables.

I haven't pursued it but when I wrote the sample, I was thinking of
separating the actual binding code into a custom Tree Control from the
DataSource used.

Ideally the TreeView should drive the CurrencyManager so changing the
selected node in Tree changes the CurrencyManager, similar to how changing
the selected row in a DataGrid will change the selected row in other
controls...

Hope this helps
Jay

clever approach regarding the way the tree is populated,
but still not solving my problem.

you are using traditional treenode objects with the .text
property being the only database-related information about
them and I really can't see how that could be used, for
example, to locate the selected node in the underlying
table --my major problem.
I'm stuck to believing that other info (particularly, the
table primary key) should also be loaded in the node
properties, in order to become useful for synchronizing
other controls bound on the same table with the treeview.
If the [id] field (in my example) could be part of the
node properties then whenever the user navigates to
another node the related table row could be easily
referenced by other controls on the same form and extra
info about the selection displayed.
But to do that, you have to use a custom node class,
having an .id property apart from the default .text one.
The same goes for any other extra information one wishes
the node to have.

I will try to use a custom node class with your code to
see what happens.

-----Original Message-----
Vsiat,
In addition to the others comments, here is a Routine I put together a
couple of weeks ago:

Something like (for the SQL Server Northwind database, the Employees table).

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim data As DataSet = BuildEmployees()
Dim connection As New SqlClient.SqlConnection ("packet
size=4096;integrated security=SSPI;data source=.;persist security
info=False;initial catalog=Northwind")
Dim adapter As New SqlClient.SqlDataAdapter ("Select EmployeeID,
LastName, FirstName, ReportsTo from Employees", connection)
adapter.Fill(data, "Employees")

For Each row As DataRow In
data.Tables("Employees").Select("ReportsTo is Null")
AddChild(TreeView1.Nodes, row)
Next
End Sub

Private Sub AddChild(ByVal nodes As

TreeNodeCollection, ByVal row As

DataRow)

Const format As String = "{1}, {0}"
Dim node As New TreeNode(String.Format(format, row ("FirstName"),
row("LastName")))
nodes.Add(node)
AddChildren(node, row)
End Sub

Private Sub AddChildren(ByVal parentNode As TreeNode, ByVal parentRow As
DataRow)
For Each childRow As DataRow In
parentRow.GetChildRows("EmployeesEmployees")
AddChild(parentNode.Nodes, childRow)
Next
End Sub

Private Function BuildEmployees() As DataSet
Dim data As New DataSet
Dim table As New DataTable("Employees")
Dim columnEmployeeID As New DataColumn ("EmployeeID",
GetType(Integer))
table.Columns.Add(columnEmployeeID)
Dim columnLastName As New DataColumn("LastName", GetType(String))
table.Columns.Add(columnLastName)
Dim columnFirstName As New DataColumn ("FirstName", GetType(String))
table.Columns.Add(columnFirstName)
Dim columnReportsTo As New DataColumn

("ReportsTo", GetType(Integer))

table.Columns.Add(columnReportsTo)

table.PrimaryKey = New DataColumn() {columnEmployeeID}

data.Tables.Add(table)
data.Relations.Add("EmployeesEmployees",

columnEmployeeID,
columnReportsTo)
Return data
End Function

Instead of building the table inline as I have, you could use a typed
dataset.

The important things in this routine are:
1. The primary key on the Employees data table.
2. The relationship between the EmployeeID column & the ReportsTo column of
the Employees data table.
3. The GetChildRows on the relationship defined in item 2.

An alternative is to only bring back child nodes when that node is expanded,
which has a significantly better startup, but the initial expanding of each
node is slightly slower. Unfortunately I do not have a full sample of this
alternative handy.

Hope this helps
Jay

I am trying to create a treeview out of a database table
with the typical structure ID, NAME, PARENTID, TYPE,
EXTRA_INFO, where [parentid] is linked to the [id].

What I want to achieve is create a tree made of custom,
extended nodes, which include all the extra information
contained in the table and not just typical TreeNode
objects.

To do that, I first created a structure with all the extra
information about the node (taken from the table), and
then created my own treenode class, adding the structure
as an extra property.

I am now trying to populate the treeview with my
treenodes, but that doesn't seem to work.

Any ideas or links to examples would be much appreciated.

.

 

[Cor Ligthert]

Hi Vsiat,

Have a look at the listview for you problem, for me that fits much more than
the treeview which is build primary to hold only one dataitem per node.

Cor

 

[vsiat]

Jey,
I have tried what I was thinking would be the solution and
in fact it works fine.

I admit though, your idea of using a single "extra
properties" object (whether a DataRow or just an Object)
instead of a series of single, database-specific objects
is much more flexible.
I see that every time I have to use this treeview to
another application, I will have to go through my custom
TreeNode class again and again, changing theese extra
properties, which is not very elegant.

Although you can't completely avoid being "database-
specific" somewhere in your code, having a class that
works like a black box and requires no attention in doing
so, is definetely progress.

Thanks for the tip, I will give it a try

-----Original Message-----
vsiat,
I would use a custom node class, others might use the TreeNode.Tag property.

I would add a single "DataRow" property on my custom Node class that
contained the "DataRow" the node is "bound" to (rather then individual
properties for each field). I would consider making the "DataRow" property
type System.Object, so as I could support binding to any class and not just
DataTables.

I haven't pursued it but when I wrote the sample, I was thinking of
separating the actual binding code into a custom Tree Control from the
DataSource used.

Ideally the TreeView should drive the CurrencyManager so changing the
selected node in Tree changes the CurrencyManager, similar to how changing
the selected row in a DataGrid will change the selected row in other
controls...

Hope this helps
Jay

clever approach regarding the way the tree is populated,
but still not solving my problem.

you are using traditional treenode objects with the .text
property being the only database-related information about
them and I really can't see how that could be used, for
example, to locate the selected node in the underlying
table --my major problem.
I'm stuck to believing that other info (particularly, the
table primary key) should also be loaded in the node
properties, in order to become useful for synchronizing
other controls bound on the same table with the treeview.
If the [id] field (in my example) could be part of the
node properties then whenever the user navigates to
another node the related table row could be easily
referenced by other controls on the same form and extra
info about the selection displayed.
But to do that, you have to use a custom node class,
having an .id property apart from the default .text one.
The same goes for any other extra information one wishes
the node to have.

I will try to use a custom node class with your code to
see what happens.

-----Original Message-----
Vsiat,
In addition to the others comments, here is a Routine I put together a
couple of weeks ago:

Something like (for the SQL Server Northwind database, the Employees table).

Private Sub Form1_Load(ByVal sender As

System.Object,
ByVal e As

System.EventArgs) Handles MyBase.Load

Dim data As DataSet = BuildEmployees()
Dim connection As New SqlClient.SqlConnection ("packet
size=4096;integrated security=SSPI;data

source=.;persist
security

info=False;initial catalog=Northwind")
Dim adapter As New SqlClient.SqlDataAdapter ("Select EmployeeID,
LastName, FirstName, ReportsTo from Employees", connection)
adapter.Fill(data, "Employees")

For Each row As DataRow In
data.Tables("Employees").Select("ReportsTo is Null")
AddChild(TreeView1.Nodes, row)
Next
End Sub

Private Sub AddChild(ByVal nodes As

TreeNodeCollection, ByVal row As

DataRow)

Const format As String = "{1}, {0}"
Dim node As New TreeNode(String.Format(format,

row
("FirstName"),

row("LastName")))
nodes.Add(node)
AddChildren(node, row)
End Sub

Private Sub AddChildren(ByVal parentNode As

TreeNode,
ByVal parentRow As

DataRow)
For Each childRow As DataRow In
parentRow.GetChildRows("EmployeesEmployees")
AddChild(parentNode.Nodes, childRow)
Next
End Sub

Private Function BuildEmployees() As DataSet
Dim data As New DataSet
Dim table As New DataTable("Employees")
Dim columnEmployeeID As New DataColumn ("EmployeeID",
GetType(Integer))
table.Columns.Add(columnEmployeeID)
Dim columnLastName As New DataColumn

("LastName",
GetType(String))

table.Columns.Add(columnLastName)
Dim columnFirstName As New DataColumn ("FirstName", GetType(String))
table.Columns.Add(columnFirstName)
Dim columnReportsTo As New DataColumn

("ReportsTo", GetType(Integer))

table.Columns.Add(columnReportsTo)

table.PrimaryKey = New DataColumn() {columnEmployeeID}
columnEmployeeID,
columnReportsTo)
Return data
End Function

Instead of building the table inline as I have, you

could
use a typed

dataset.

The important things in this routine are:
1. The primary key on the Employees data table.
2. The relationship between the EmployeeID column & the ReportsTo column of
the Employees data table.
3. The GetChildRows on the relationship defined in item 2.

An alternative is to only bring back child nodes when that node is expanded,
which has a significantly better startup, but the

initial
expanding of each

node is slightly slower. Unfortunately I do not have a full sample of this
alternative handy.

Hope this helps
Jay


I am trying to create a treeview out of a database table
with the typical structure ID, NAME, PARENTID, TYPE,
EXTRA_INFO, where [parentid] is linked to the [id].

What I want to achieve is create a tree made of custom,
extended nodes, which include all the extra information
contained in the table and not just typical TreeNode
objects.

To do that, I first created a structure with all the extra
information about the node (taken from the table), and
then created my own treenode class, adding the structure
as an extra property.

I am now trying to populate the treeview with my
treenodes, but that doesn't seem to work.

Any ideas or links to examples would be much appreciated.



.

.