Scalable Treeview using SQL Server

naguini · Apr 28, 2012

Hi all, I am trying to develop a robust and scalable treeView for the web using the new ASP.NET TreeView web Control. I would like to populate the treenodes from SQL Server based on a parent/child relationship stored in the table (e.g. no known number of depth in levels). Example of SQL table tblTreeNodes ---------------------------------------------------- NodeID ParentID NodeName 1 0 Root Folder 2 1 Child 1 3 1 Child 2 4 2 Child 1-1 5 4 Child 1-2 After some experimenting I have gotten this to work using the following logic: 1. First I have a SQL user function that recursively re-orders the output to Parent/Child/Child etc. In this example, the output would become: NodeID ParentID NodeName 1 0 Root Folder 2 1 Child 1 4 2 Child 1-1 5 4 Child 1-2 3 1 Child 2 2. On the c# codebehind page of the ASP.NET webform I read the output into a dataset and then drop the dataset into an XML Document which produces the following schema: <TREENODES> <TreeNode> <NodeID>1</NodeID> <ParentID>0</ParentID> <NodeName>Root</NodeName> </TreeNode> <TreeNode> <NodeID>2</NodeID> <ParentID>1</ParentID> <NodeName>Child 1</NodeName> </TreeNode> <TreeNode> <NodeID>4</NodeID> <ParentID>2</ParentID> <NodeName>Child 1-1</NodeName> </TreeNode> <TreeNode> <NodeID>5</NodeID> <ParentID>4</ParentID> <NodeName>Child 1-1-1</NodeName> </TreeNode> <TreeNode> <NodeID>3</NodeID> <ParentID>1</ParentID> <NodeName>Child 2</NodeName> </TreeNode> </TREENODES> 3. I wrote a function that, using x-path, recursively goes through this xml document, builds a nodeCollection and when finished, returns this node colletion. 4. Lastly, I have to loop through this node collection and add each node to the treeview control programatically. Here are my issues with this: Number one, I am very new to ASP.NET, XML, and C# and not sure if this is the best way of doing this. I have tried several ways, using XSL Stylesheet to transform, trying to bind the output stream to the TreeView.TreenodeSrc property, etc. . But something always was not working. The way I have it now works, however, the performance is horrible once I get past 100 nodes in the tree and this is not acceptable. I have searched the web and newsgroups for help and examples but only find little bits and pieces, like, "You should have certain nodes call SQL again and dynamically load up the next batch of nodes..." - Ok, but how? Code examples?. - Obviously, this would be ideal to have a tree where you could, load up only the first 50, and then the next 50 when needed, or the first two levels only and then the next level or two only when clicked (without refreshing the entire tree). I think there is and will be a great need to have a scalable TreeView driven by a database. But, again, I only find bits, pieces and hints. Even on Microsofts TreeView documentation in the MSDN there are only short snippets that talk about "... bind the SQL Server output to the TreeNodeSrc property to dynamically populate the control".... but nothing else. Anyway, I would love to see an article/tutorial on this or pointers in the right direction or suggestions, comments etc. on how to either re-do my current logic or improve upon it. Thanks in advance for any help, suggestions and comments P.S. Please let me know if you need any of my source code that I am currently using. Email me at [email protected]

Scalable Treeview using SQL Server

naguini

New Member