Best way to load a TreeView?

[David Veeneman]

What's the best way to load a TreeView control from a database?

Here is my scenario: I have a TreeView that represents a Table of Contents.
The TOC has an arbitrary number of branches, and each branch has an
arbitrary depth. I am reading the TOC from a SQL Server database.

I describe below my approach to this problem. Here is what I am asking: Is
this a good way to load an arbitrary TOC from a database into an object
model? Is there a better way to approach the problem?

The TreeView can be bound to a hierarchical object model. So the problem
becomes how to load the data into a hierarchical object model. So, we start
with the database, which has a table called TOC. Each node in the TOC object
model has a corresponding record in the table. The table has four columns:
ID, Caption, Parent, and Level.

-- The ID column contains the ID for the node.

-- The Caption column contains the caption displayed for the node.

-- The Parent column contains the ID of the node's parent node.

-- The Level column contains the level at which the node can be found.
Top-level nodes have a Level of 0, nodes 1 level down have a Level of 1, and
so on. We sort the table on this column to ensure that higher-level nodes
get loaded first.

The table is initialized with a root-level node record, to which we assign a
Level of -1. All nodes with a Level of 0 have a Parent value of -1, which
means the root node is their parent.

A TocNode object has properties for ID, Caption, Parent, and Level. It also
has a Nodes collection for its child nodes. The root-level record (Level -1)
is loaded into a root-level TocNode object, then the Level 0 nodes are
loaded into its Nodes collection. The level 1 nodes are loaded into the
Nodes collections of their level 0 parents, and so on.

To make it easier to get to parent nodes while loading, each node is also
loaded into a temporary Dictionary that is keyed to the node's ID. When a
later node is loaded, the app reads that node's Parent property and
retrieves the parent node from the Dictionary. That way, the app doesn't
have to search the tree for a parent as each node is loaded. The Dictionary
is discarded when the load is complete, and the root node is bound to the
TreeView control.

That's it in a nutshell. To keep things simple, I've left out quite a bit,
such as article ID references and so on. So, is this a good way to go, or is
there a simpler way to get the job done? Thanks in advance for your help.

David Veeneman
Foresight Systems

 

[Charles Calvert]

What's the best way to load a TreeView control from a database?

Here is my scenario: I have a TreeView that represents a Table of Contents.
The TOC has an arbitrary number of branches, and each branch has an
arbitrary depth. I am reading the TOC from a SQL Server database.

[snip explanation about loading records from database, sorted by
level, adding to a dictionary and then populating a tree]

Assuming that you expect the number of nodes in the tree to stay
fairly low (a few hundred, say), this seems like a sufficient method.
If the number of nodes continued to grow, then you might consider a
virtual tree control in which you only populate children when the
parent node is expanded.

Are you concerned about performance or elegance? If the former, have
you profiled it? If the latter, there may be some class out there
that can map arbitrary tabular data to a hierarchy, but it would
likely just use a similar method internally.

 

[David Veeneman]

Thanks, Charles. The virtual control makes a lot of sense. I think it is
actually simpler than loading the entire tree at once. Here is what I am
thinking:

Primary key for the TOC table is an ID column, an integer that increments
automatically. Record also contains Caption and ParentID columns.

When a new database is created, create a record for the root node

As the user adds nodes to the TOC, create a database record for each new
node, and record the parent ID.

Now assume the user has added a lot of nodes, which are nested five or six
levels deep, and then exits the app. The next time the app is launched, the
containment hierarchy is loaded only to Level 0, as follows:

Load the first record in the table, which will be the root node. Query the
database for all nodes that have the root node as their parent (the Level 0
nodes), and load them into the parent's ChildNodes collection.

When the user expands one of the Level 0 nodes, get the expanded node's ID
and query the database for all records with that ID, and loads them into the
Level 0 node's ChildNodes collection. The same process is used to load any
other TreeView node that is clicked.

I can see how that approach would be more efficient for a larger tree--load
only what you need. It would actually be simpler for a smaller tree as well,
since it eliminates the need for the Level column in the TOC database table.
Perrformance on launch would improve, since only the top-level tree nodes
need to be loaded. The trade-off would be slightly degraded performance when
a node is expanded, since a trip to the database would be involved.