Products

Evoq Content Overview Content Creation Workflow Asset Management Mobile Responsive Personalization Content Analytics SEO Integrations Security Website PerformanceEvoq Engage Overview Community Management  Dashboard  Analytics  Member Profile Gamification Advocate Marketing Community Engagement  Ideas  Answers  Discussions  Groups  Wikis  Events Mobile ReadyDNN Support PackagesEvoq: CMS FeaturesEvoq OnDemandCompare DNN's CMS Products

Solutions

Content Management SystemMulti-Channel PublishingDigital MarketingOnline Community ManagementIntranet Software SolutionOur CustomersPrime

Resources

Test DrivesSchedule A DemoDocumentation CenterWebinarsWhite PapersProduct ManualsRequest PricingData SheetsCase StudiesEvoq Preferred Products Content Management Ecommerce Forms Identity Management and Authentication Site Management Social Themes

Partners

DNN Partners Partner Directory Become a DNN Partner Partner Program Overview  Hosting  Implementation  ISV  Training  Competencies

Community

Participate Ask a Question Forums Community Showcase DNN MVP  Program Overview  Lifetime MVPs  Honorary MVPs Subscribe to DNN Digest Advisory Groups  Awareness Advisory Group  Developer Advisory Group  Partner Advisory Group  Technology Advisory GroupLearn Documentation Wiki Community Blog Video Library Project History Development RoadmapDownload DNN Store Language Packs Manuals Nightly BuildsSecurity Policies Security Center

Blog

About

News Press Releases News Coverage Press KitCareersContact DNN

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen
HomeHomeDNN Open Source...DNN Open Source...Provider and Extension ForumsProvider and Extension ForumsClientAPIClientAPIWhat is the best way to load a TreeView Control?What is the best way to load a TreeView Control?
Previous
 
Next
New Post
8/19/2007 4:58 PM
 
Dan

Joined: 12/28/2006
Posts: 58

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

 
New Post
8/21/2007 10:11 AM
 
Jon Henning

www.codeendeavors.com
Joined: 3/20/2003
Posts: 1767

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. 

 

 
 
New Post
8/22/2007 12:50 PM
 
Dan

Joined: 12/28/2006
Posts: 58

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
 
New Post
8/29/2007 3:11 PM
 
Jon Henning

www.codeendeavors.com
Joined: 3/20/2003
Posts: 1767

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
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Provider and Extension ForumsProvider and Extension ForumsClientAPIClientAPIWhat is the best way to load a TreeView Control?What is the best way to load a TreeView Control?


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out