Re: recursive treeview improvement

0 views
Skip to first unread message

Jay B. Harlow [MVP - Outlook]

unread,
May 3, 2004, 6:56:02 PM5/3/04
to
Robert,
Rather then opening multiple connections to the data base & running multiple
queries, I would simply bring back all the records into a dataset that has a
relationship defined for the parent/child relationship.

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


"Robert Samuel White" <newsgroup...@enetwizard.net> wrote in message
news:49980307-8584-4FB7...@microsoft.com...
> Below is the code I am using to recursively build a treeview of a
parent-child relationship maintained in a single table. It works but takes
too long to load. I realize it could be improved if I understood how to
tell it the true relationship between these two fields, but I've been able
to accomplish that. If I could stop it from calling the Add2Node function
when there are no child records, I believe the speed would be dramatically
improved. Would someone please tell me where I am going wrong with this and
how to make it better? Thanks!
>
> Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
>
> Dim oleConnection As OleDb.OleDbConnection
> Dim oleRoot As OleDb.OleDbDataAdapter
> Dim oleDataSet As New DataSet()
>
> oleConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
GetSetting("eNetwizard", "TechOpSuite\SuiteData", "Management") &
";Mode=Share Deny None")
> oleRoot = New OleDb.OleDbDataAdapter("SELECT UserId, Name,
Key, Parent FROM tdataUsers WHERE Parent = 0 ORDER BY Name;", oleConnection)
> oleRoot.Fill(oleDataSet, "theRoot")
>
> TreeView1.Nodes.Clear()
>
> Dim ParentRow As DataRow
> Dim ParentTable As DataTable
> ParentTable = oleDataSet.Tables("theRoot")
>
> For Each ParentRow In ParentTable.Rows
>
> Dim ParentNode As TreeNode
>
> ParentNode = New TreeNode(CStr(ParentRow.Item("Name")) & " ("
& CStr(ParentRow.Item("Key")) & ")")
> TreeView1.Nodes.Add(ParentNode)
> ParentNode.Tag = ParentRow.Item(0)
> RecurseNode(CInt(ParentRow.Item(0)), ParentNode)
>
> Next ParentRow
>
> End Sub
>
>
>
> Function RecurseNode(ByVal UserId As Integer, ByVal theNode As
TreeNode) As Integer
>
> Try
>
> Dim oleConnection As OleDb.OleDbConnection
> Dim oleChild As OleDb.OleDbCommand
> Dim oleDataSet As New DataSet()
>
> oleConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
GetSetting("eNetwizard", "TechOpSuite\SuiteData", "Management") &
";Mode=Share Deny None")
> oleConnection.Open()
> oleChild = New OleDb.OleDbCommand("SELECT UserId, Name,
Key, Parent FROM tdataUsers WHERE Parent = " & UserId & " ORDER BY Name;",
oleConnection)
>
> Dim oleReader As OleDb.OleDbDataReader =
oleChild.ExecuteReader
>
> While oleReader.Read
>
> Dim NewNode As TreeNode
> NewNode = New TreeNode(CStr(oleReader.Item("Name")) & " ("
& CStr(oleReader.Item("Key")) & ")")
> Add2Node(TreeView1.Nodes, theNode, NewNode)
> RecurseNode(CInt(oleReader.Item("UserId")), NewNode)
>
> End While
>
> oleReader.Close()
> oleConnection.Close()
>
> Catch e As Exception
> MsgBox(e.Message)
>
> End Try
>
> End Function
>
>
>
> Private Function Add2Node(ByVal TestNode As TreeNodeCollection, ByVal
findnode As TreeNode, ByVal newnode As TreeNode) As Boolean
>
> Dim n As TreeNode
>
> For Each n In TestNode
>
> If n.Text = findnode.Text Then
>
> n.Nodes.Add(newnode)
>
> Add2Node = True
>
> Exit Function
>
> End If
>
> Add2Node(n.Nodes, findnode, newnode)
>
> Next
>
> End Function


Robert Samuel White

unread,
May 3, 2004, 8:12:18 PM5/3/04
to
Jay,

Thanks for your response. I made a new form with an updated code algorithm
based on the code you posted. I am experiencing a new problem, pretty much
the problem I had before in not being able to get it to acknowledge a
relationship to the two fields. The code is actually quite simplified, I
like it! Here is the code first, and next the error message:

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

Dim oleDataRow As DataRow
Dim oleDataSet As DataSet = BuildUsers()
Dim oleConnection As OleDb.OleDbConnection
Dim oleAdapter As OleDb.OleDbDataAdapter

oleConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
GetSetting("eNetwizard", "TechOpSuite\SuiteData", "Management") &
";Mode=Share Deny None")

oleAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM tdataUsers


ORDER BY Name;", oleConnection)

oleAdapter.Fill(oleDataSet, "Users")

For Each oleDataRow In oleDataSet.Tables("Users").Select("Parent Is
Null")

AddChild(treeUsers.Nodes, oleDataRow)

Next

End Sub

Private Sub AddChild(ByVal oleNodes As TreeNodeCollection, ByVal theRow
As DataRow)

Dim theNode As New TreeNode(CStr(theRow("FirstName")))
oleNodes.Add(theNode)
AddChildren(theNode, theRow)

End Sub

Private Sub AddChildren(ByVal theNode As TreeNode, ByVal theRow As
DataRow)

Dim oleDataRow As DataRow

For Each oleDataRow In theRow.GetChildRows("Relationship")

AddChild(theNode.Nodes, theRow)

Next

End Sub

Private Function BuildUsers() As DataSet

Dim oleDataSet As New DataSet
Dim oleTable As New DataTable("Users")

Dim cUserId As New DataColumn("UserID", GetType(Integer))
oleTable.Columns.Add(cUserId)

Dim cName As New DataColumn("Name", GetType(String))
oleTable.Columns.Add(cName)

Dim cKey As New DataColumn("Key", GetType(String))
oleTable.Columns.Add(cKey)

Dim cParent As New DataColumn("Parent", GetType(Integer))
oleTable.Columns.Add(cParent)

oleTable.PrimaryKey = New DataColumn() {cUserId}
oleDataSet.Tables.Add(oleTable)
oleDataSet.Relations.Add("Relationship", cUserId, cParent)

Return oleDataSet

End Function


Now, the error I am getting is an exception from system.data.dll of type
'System.Data.SyntaxErrorException' with the additional message: "Syntax
error in Lookup expression: Expecting keyword 'Parent' followed by a single
column argument with possible relation qualifier: Parent [(<Relation
Name>)].<column_name>. I have no idea what that means, but I decided to try
to figure it out."

Well, the first thing I thought of was where it says .Select("Parent Is
Null"). If they are top-level employees, then they are actually set to 0.
So I changed this to say .Select("Parent = 0") but I received a different
error message. This time the exception came from system.data.dll of type
'System.Data.ConstraintException' and said "Failed to enable constraints.
One or more rows contains values violating non-null, unique, or foreign key
constraints." Again, I tried to research the problem.

I went into the Access table design view and looked for a way to allow null
values, but did not see any property to do that. If one existed, I would
have set the 0 parents to Null and gone with the .Select("Parent Is Null")
route. I tried it both as an index (allowing duplicates) and not as an
index. I'm not sure what the problem is. I'm sure that all the parents in
the table have values that point to an existing record. What's wrong?

Again, thanks, and any help would be greatly appreciated!

-Samuel


"Jay B. Harlow [MVP - Outlook]" <Jay_Har...@msn.com> wrote in message
news:OX7bQHW...@TK2MSFTNGP09.phx.gbl...


> Robert,
> Rather then opening multiple connections to the data base & running
multiple
> queries, I would simply bring back all the records into a dataset that has
a
> relationship defined for the parent/child relationship.
>

> (...Code...)

Jay B. Harlow [MVP - Outlook]

unread,
May 3, 2004, 9:16:01 PM5/3/04
to
Robert,

> 'System.Data.SyntaxErrorException' with the additional message: "Syntax
> error in Lookup expression: Expecting keyword 'Parent' followed by a
single

"Parent" is a reserved word in ADO.NET. It is used by ADO.NET column
expressions to mean the parent relationship, "Child" is reserved to man
child relationships...

Either choose a different field name or escape the Parent field name.

Change:


.Select("Parent Is Null")

To:


.Select("[Parent] Is Null")

For details on what expressions ADO.NET support see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataColumnClassExpressionTopic.asp

For a good tutorial on ADO.NET as well as a good desk reference once you
know ADO.NET see David Sceppa's book "Microsoft ADO.NET - Core Reference"
from MS press

Hope this helps
Jay


"Robert Samuel White" <newsgroup...@enetwizard.net> wrote in message

news:109do3m...@corp.supernews.com...

Robert Samuel White

unread,
May 3, 2004, 10:22:28 PM5/3/04
to
Well, that fixed the problem. But it is only getting the top-level nodes,
nothing below them. The routine I started with was able to get them all...
I'm not familiar with the GetChildRows function, so I don't know how to tell
if it is getting them or not. I'm actually totally new to ADO. I've been
using DAO for years!


Jay B. Harlow [MVP - Outlook]

unread,
May 4, 2004, 8:56:00 AM5/4/04
to
Robert,
The routine I gave works as posted!

Not sure what your code or database is doing different then what the
Northwind sample has.

Like I stated, David Sceppa's book is both a good tutorial & desk reference
on ADO.NET (BTW ADO itself is a COM based technology like DAO, .NET uses
ADO.NET).

If you want to zip the code you have in a sample form & project along with
the database & email it to me, I will look at it to see if its something
obvious. For others I only look at sample code & messages emailed to me when
I request them!

Hope this helps
Jay

"Robert Samuel White" <newsgroup...@enetwizard.net> wrote in message

news:109dvnf...@corp.supernews.com...

Reply all
Reply to author
Forward
0 new messages