What is the best way to load a TreeView Control? - ClientAPI Component [Lead: Jon Henning]

Forum DotNetNuke® Pro... ClientAPI Compo... What is the best way to load a TreeView Control?

8/19/2007 1:58 PM

	Dan
58 posts

What is the best way to load a TreeView Control?

Modified By Dan on 8/20/2007 11:44:33 AM)

Hello,

I am working to load a treeview control in the repository, if any of you have seen the code for this (AddCategoryToTreeObject function in the RepositoryBL.vb). Out of the box it does something like this find root node -> do a database hit to see if it has any children, then for each child do a database hit to see if they have children, etc... Actually if you look closer in the code you will see that is also does a database hit for each category to see if the item is selected. My issue is that I have my repository and my category list (which is the treeview control) on my main page. I also have a total of 140 categorys/subcategories. Which if you do the math at 2 database hits per category is 280 database hits just to load the treeview. This seems rather excessive to me, there has to be a better way to do this.

Can any recommend a better solutions, recursive sql statements, a single pull to get all categories and spin through to load, just looking for options.

Thanks,

Dan

P.S. I tried posting this in the repository group and no one had any answers so I'm hoping that someone will have ideas here.

P.P.S. Here is the table structure so you don't have to dig into the source code to give help:

The parentID of -1 is the base level.

ID CategoryName ParentID

1 Car -1

2 Ford 1

3 Truck -1

4 Toyota 3

5 Tundra 4

6 Mustang 2

8/21/2007 7:11 AM

	Jon Henning
1605 posts
www.codeendeavors.com

Re: What is the best way to load a TreeView Control?

Seems like the easiest solution would be to enable populate on demand for the tree, and keep the procs pulling one at a time.

However, you can easily get all levels of the tree with a single sql call. A proc something like

DECLARE @ret TABLE
(
ID int,
CategoryName varchar(50),
ParentID int,
HierarchyLevel int
)

SELECT @lvl = 0

INSERT INTO @ret
SELECT
   ID,
   CategoryName,
   ParentID,
   @lvl as HierarchyLevel
FROM
   MyTable
WHERE
   ParentID = -1

SET @rowCount = @@rowcount

WHILE @rowCount > 0
BEGIN
SELECT @lvl = @lvl + 1

INSERT INTO @ret
   SELECT
    m.ID,
    m.CategoryName,
    m.ParentID,
    @lvl as HierarchyLevel
   FROM
    MyTable m
    INNER JOIN @ret r ON m.ParentID = r.ParentID AND r.HierarchyLevel = @lvl-1
   WHERE
    m.ID not IN (SELECT r2.ID FROM @ret r2) --prevent infinite loops (not necessary for most scenerios)

SET @rowcount = @@rowcount
END

SELECT * FROM @ret

NOTE: I Have not tried this query out, from top of my head, but it should get you started.

8/22/2007 9:50 AM

	Dan
58 posts

Re: What is the best way to load a TreeView Control?

Jon,

Thanks for the sample sql. I'm hosting on SQL2005 so I used a standard CTE recursive sql statement to do pretty much the same thing you have outlined above. I do have a rather simple question for you. When I'm traversing the dataset I get back and inserting the new nodes into the treeview what are the correct statements to use to set a node as a child of the node before it, how do I then go to the next parent and load its children etc...

Basically I'm just looking for the load routine to get all this new data in the proper level of the treeview.

Thanks again,

Dan

8/29/2007 12:11 PM

	Jon Henning
1605 posts
www.codeendeavors.com

Re: What is the best way to load a TreeView Control?

Assuming your results come back in order (i.e. no child node is present before its parent) you typically populate it in a recursive loop by adding the new nodes to the current parent.

Here is some pseudo code to get you started.

populatenodes(TreeNode parent, DataTable dt)
{
  DataRows[] drs = dt.Find("id = " + parent.id);
  foreach (DataRow dr in drs)
  {
    TreeNode newNode = new TreeNode(dr("text"), dr("id");
    parent.nodes.add(newNode);
    populatenodes(newNode, dt);
  }
}

Page 1 of 1

Forum

DotNetNuke® Pro...

ClientAPI Compo...

What is the best way to load a TreeView Control?