{"id":493,"date":"2009-01-05T00:00:00","date_gmt":"2009-01-05T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/rendering-hierarchical-data-with-the-treeview\/"},"modified":"2021-05-17T18:35:04","modified_gmt":"2021-05-17T18:35:04","slug":"rendering-hierarchical-data-with-the-treeview","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/rendering-hierarchical-data-with-the-treeview\/","title":{"rendered":"Rendering Hierarchical Data with the Treeview"},"content":{"rendered":"
\n

Using Nested Sets with \u00a0the Treeview<\/h2>\n

If you’re like me, one day Joe Celko exposed you to Michael J. Kamfonas’ Nested Sets model <\/a>for trees in SQL on the Usenet, and it changed your life. So you have an easy way of expressing hierarchical data in SQL, with ordered sibling nodes, no less.<\/p>\n

A decade or so later, we find ourselves trying to integrate our database design patterns with the .NET controls. Data-binding handles a lot of the functional requirements for you, but you have to do it pretty much the way that Microsoft expects you to, which you’ll understand if you try to set the DataSource on a FormView <\/b>control. We have to give up middle layer logic and tie our user interface directly to the database objects, which is not generally a good idea, but not always worth avoiding.<\/p>\n

The .NET control for hierarchical data is the TreeView,<\/b> and the TreeView<\/b> requires an IHierarchicalDataSource<\/b> <\/b>object. However, if you try to do this through the GUI, you’ll see there is no way that is provided to add a connection to a SQL Server database.<\/p>\n

\"623-first.png\"<\/p>\n

Figure 1 TreeView control in Toolbox under ‘Navigation’<\/p>\n

\u00a0\"623-Second.png\"<\/p>\n

Figure 2: Options for a TreeView data source<\/p>\n

It seems nested sets aren’t so pervasive that .NET provides a UI component set. The point of this article, though, is to fill that gap. So, let’s back up a little.<\/p>\n

Populating a Treeview with Nested sets<\/h2>\n

The Database bit.<\/h3>\n

In the nested set model, every node has two integer values which (after Celko) I call lft<\/b> and rgt<\/b> . rgt<\/b> is always greater than lft<\/b> , but only one greater for leaf nodes. If one node’s lft<\/b> is less than another node’s lft<\/b> , and the first node’s rgt<\/b> is greater than the next node’s rgt<\/b> , the first node is an ancestor of the second node. There is a lot more to nested sets, and many wonderful reasons to use them. However, this article is about showing them in a .NET TreeView, so I’ll refer you to Celko’s “SQL for Smarties<\/a>” or “Trees and Hierarchies in SQL For Smarties<\/a>” for a discussion. Now, we’ll just make one, with data enough to populate a TreeView.<\/p>\n

USE SQLTreeNode\r\nGO\r\n-- drop the schemabound function\r\nIF OBJEcT_ID('GetTreeNodeParentPath', 'FN') IS NOT NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DROP FUNCTION GetTreeNodeParentPath\r\nGO\r\n-- create a table to store node data. \r\nIF OBJECT_ID('TreeNode', 'U') IS NOT NULL DROP TABLE TreeNode\r\nIF OBJECT_ID('Node', 'U') IS NOT NULL DROP TABLE Node\r\nGO\r\nCREATE TABLE Node(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NodeID INT IDENTITY(0,1) CONSTRAINT PK_NODE PRIMARY KEY Clustered,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TEXT NVARCHAR(255) NOT NULL CONSTRAINT NOSLASH 0 = CHARINDEX('\/', Text),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TOOLTIP NVARCHAR(MAX),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IMAGEURL NVARCHAR(4000),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Checked BIT DEFAULT 0\r\n)\r\nGO\r\n\/** create a table to place nodes in the hierarchy.\r\n\u00a0 * note that the node data are stored separately from the node position to \r\n\u00a0 * allow a node to appear in the tree multiple times\r\n\u00a0 *\/\r\nGO\r\nCREATE TABLE TreeNode(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lft INT UNIQUE, -- we would cluster on lft if we were using a single table\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rgt INT UNIQUE,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT PK_TreeNode PRIMARY KEY NONCLUSTERED(lft , rgt ),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NODEID INT CONSTRAINT TreeNodeNode FOREIGN KEY (NODEID) REFERENCES NODE(NODEID)\r\n)\r\nGO\r\n-- We're going to want that merge join\r\nCREATE CLUSTERED INDEX IX_TreeNode ON TreeNode(NodeID)\r\nGO\r\n\/** This procedure lets us check for bad nodes. It's for development and schema \r\n\u00a0 * Maintenance\r\n\u00a0 *\/\r\nIF OBJEcT_ID('CheckTreeNodeTable', 'P') IS NOT NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DROP PROCEDURE CheckTreeNodeTable\r\nGO\r\nCREATE PROCEDURE CheckTreeNodeTable AS\r\nBEGIN\r\n\u00a0\u00a0\u00a0 IF EXISTS(SELECT * FROM TreeNode tvd1 INNER JOIN TreeNode tvd2 ON \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0tvd1.lft = tvd2.rgt ) RAISERROR('lft and rgt together are not unique', 16, 1)\r\n\u00a0\u00a0\u00a0 IF 1 <> (SELECT 2 * COUNT(*) - MAX(rgt ) + MIN(lft ) FROM TreeNode) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR('Gaps exist in lft and rgt values', 16, 1)\r\n\u00a0\u00a0\u00a0 IF EXISTS(SELECT * FROM TreeNode, TreeNode tvd2 WHERE \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (TreeNode.lft < tvd2.lft AND TreeNode.lft > tvd2.rgt ) AND \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (TreeNode.rgt > tvd2.lft AND TreeNode.rgt < tvd2.rgt )) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR('Incorrectly nested nodes are defined', 16, 1)\r\nEND\r\nGO\r\n\/** Add a root node\r\n\u00a0 *\/\r\nINSERT INTO Node([TEXT], TOOLTIP, IMAGEURL, Checked) VALUES (N'Root', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 N'This is the node from which all other nodes descend', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 N'http:\/\/example.com\/myicon.png', 0 )\r\nINSERT INTO TreeNode (lft , rgt , NodeID) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES (0, 1, @@IDENTITY)\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\n\/** Return's the path to the node's parent \r\n\u00a0 * @lft the value for the node's left edge\r\n*\/\r\nCREATE FUNCTION GetTreeNodeParentPath(@lft INT) RETURNS nvarchar(max) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 with schemabinding AS BEGIN\r\n\u00a0\u00a0\u00a0 DEcLARE @RETVAL nvarchar(max), @rgt INT\r\n\u00a0\u00a0\u00a0 SelEcT @rgt = rgt FROM dbo.TreeNode WHERE lft = @lft \r\n\u00a0\u00a0\u00a0 -- return the node's parent's slash-delimited path \r\n\u00a0\u00a0\u00a0 SELEcT @RETVAL = \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- Initialize @Retval with an empty string\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COALESCE(@RETVAL + '\/', SPACE(0)) + Text FROM dbo.TreeNode \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN dbo.Node ON dbo.TreeNode.NodeID = dbo.Node.NodeID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- get all ancestor nodes\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE lft < @lft AND rgt > @rgt \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- get lower level nodes earlier\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDEr BY lft ASC\r\n\u00a0\u00a0\u00a0 RETURN @RETVAL\r\nEND\r\nGO\r\nIF OBJECT_ID('GetDescendingNodes', 'P') IS NOT NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DROP PROCEDURE GetDescendingNodes\r\nGO\r\n\/** return all the nodes descending from the node with the specified path\r\n\u00a0 *\u00a0 We leave lft and rgt in the database, and deal with path enumeration out \r\n\u00a0 *\u00a0 in the world. We undo that here.\r\n\u00a0 * @Path slash-delimited path enumeration of ancestors of the node and the node\r\n\u00a0 * itself. Only this node and its descendants will be returned. If @Path is \r\n\u00a0 * null, return the entire tree.\r\n*\/\r\nCREATE PROCEDURE GetDescendingNodes(@PATH NVARCHAR(max) = NULL) AS BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @rgt INT, @lft INT\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* For very large hierarchies, calculating the path for each node will \r\n\u00a0\u00a0\u00a0\u00a0 * become time-consuming and you may wish to rewrite the query finding a \r\n\u00a0\u00a0\u00a0\u00a0 * node by its path in the following way\r\n\u00a0\u00a0\u00a0\u00a0 * 1. Take the token before the first 'slash' and leave a remainder\r\n\u00a0\u00a0\u00a0\u00a0 * 2. Find the nth-level node with that text descending from the passed \r\n\u00a0\u00a0\u00a0\u00a0 *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 node [a node's level is \r\n\u00a0\u00a0\u00a0\u00a0 *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (count(*) where lft < node.lft and rgt > node.rgt )]\r\n\u00a0\u00a0\u00a0\u00a0 * 3. If the remainder has text, recurse with the found node and the \r\n\u00a0\u00a0\u00a0\u00a0 *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 remainder\r\n\u00a0\u00a0\u00a0\u00a0 * 4. If the remainder is empty, return the node \r\n\u00a0\u00a0\u00a0\u00a0 * 5. Return all nodes with lft and rgt between that nodes lft and rgt as \r\n\u00a0\u00a0\u00a0\u00a0 *\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 below\r\n\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* Common Table Expressions get a bad rap as only enabling people who \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0* insist on using adjacency lists with CONNECT BY. But, we can cut our\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0* calls to GetTreeNodeParentPath in half using them here.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ;WITH TreeNodes(lft , rgt , ParentPath, TEXT, ToolTip, ImageURL, Checked) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS (SELECT lft , rgt , dbo.GetTreeNodeParentPath(lft ), TEXT, ToolTip, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ImageURL, Checked FROM TreeNode INNER JOIN Node ON \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TreeNode.NodeID = Node.NodeID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- Return the node and its descendants\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT tns.ParentPath, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COALESCE(tns.ParentPath + '\/', SPACE(0)) + tns.TEXT AS PATH, tns.TEXT, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tns.Tooltip, tns.ImageURL, tns.Checked \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM TreeNodes tns INNER JOIN TreeNodes anc \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- get the specified node and all descendants\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON tns.lft >= anc.lft AND tns.rgt <= anc.rgt \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- compare the nodes path with the argument\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND COALESCE(Anc.ParentPath + '\/', SPACE(0)) + Anc.[TEXT] = COALESCE(@PATH, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- get the root node if the argument is not specified\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT COALESCE(ParentPath + '\/', SPACE(0)) + Text \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM TreeNodes WHERE lft IN \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- the root node has the lowest lft value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT MIN(lft ) FROM TreeNodes)))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- ordering by lft ensures nodes will precede their children\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY tns.lft ASC\r\nEND\r\nGO\r\n\/** Add some nodes, just so there's something to see in the display\r\n\u00a0 * using Free Icons from \r\n\u00a0 * http:\/\/www.iconarchive.com\/category\/business\/people-icons-by-aha-soft.html\r\n\u00a0 * Clearly, you'll need CreateNode, ReviewNode, UpdateNode and DeleteNode\r\n\u00a0 * procedures, but those can wait until an article on the FormView ;)\r\n\u00a0 *\/\r\nDECLARE @ROOTID INT\r\nSELECT @ROOTID = NodeID FROM TreeNode WHERE lft = 0\u00a0 \r\nUPDATE TreeNode SET rgt = 19 WHERE nodeid = @ROOTID\r\nUPDATE Node SET TEXT = 'People', ImageURL = \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/people-icon.jpg'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , ToolTip = 'We''re all people, after all'\r\nINSERT INTO node (TEXT, ImageURL, Tooltip) VALUES(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Boss', 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/boss-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Your boss is a person')\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (1, 8, @@IDENTITY) \r\nINSERT INTO node (TEXT, ImageURL, Tooltip) VALUES('Engineer', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/engineer-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Your boss could be an engineer')\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (2, 5, @@IDENTITY) \r\nINSERT INTO node (TEXT, ImageURL, Tooltip) VALUES('Footballer', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/footballer-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Engineers play a lot of soccer; I think they like the hexagons')\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (3, 4, @@IDENTITY) \r\nINSERT INTO node (TEXT, ImageURL, Tooltip) VALUES('Army Officer', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/army-officer-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Your boss could be an Army officer')\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (6, 7, @@IDENTITY) \r\nINSERT INTO node (TEXT, ImageURL, Tooltip) VALUES('Users', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/users-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Users are people')\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (9, 18, @@IDENTITY) \r\nINSERT INTO node (TEXT, ImageURL, Tooltip) VALUES('User Group', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/user-group-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Users can form groups')\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (10, 11, @@IDENTITY) \r\nINSERT INTO node (TEXT, ImageURL, Tooltip) VALUES('User', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/user-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Users can be individuated')\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (12, 17, @@IDENTITY) \r\nINSERT INTO node (TEXT, ImageURL, Tooltip) VALUES('User Info', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/user-info-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Users have information')\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (13, 14, @@IDENTITY) \r\nINSERT INTO node (TEXT, ImageURL, Tooltip, Checked) VALUES('Check User', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'http:\/\/icons.iconarchive.com\/icons\/aha-soft\/people\/check-user-icon.jpg', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Users can be checked!', 1)\r\nINSERT INTO TreeNode (lft , rgt , NODEID) VALUES (15, 16, @@IDENTITY)\r\n\u00a0\r\n<\/pre>\n
That code contains a few different sections<\/p>\n
    \n
  1. Making the data tables. We make one table to contain the data and another to locate it in the tree. This can be one table for simple implementations. Note that for a production implementation, you might want security, auditing and historical state functions, which are not included here<\/li>\n
  2. This section starts with the GetParentPath function. It provides the ‘select’ functionality for the TreeView control, for which we’ll shortly get to providing a client.<\/li>\n
  3. The third section adds some data, leaning on some free icons I found on the web. Any practical implementation is going to provide Create\/Review\/Update\/Delete features, but since the TreeView doesn’t provide a way to use them, they’re out of scope here.<\/li>\n<\/ol>\n
    The Middle Bit<\/h3>\n
    So, now, all that’s left to do is write some helper functions for the IHierarchicalDataSource<\/b> , the IHierarchicalDataSource<\/b> itself, and tie it to a TreeView. Really, we’ve defined the table, so the heavy lifting is done. I don’t know what those application developers go on about.<\/p>\n
    What’s in an IHierarchicalDataSource<\/b> ? The documentation<\/a> says ” The interface defines a single method, GetHierarchicalView, which retrieves a strongly typed HierarchicalDataSourceView<\/a> object.” Our HierarchicalDataSourceView implementation, in turn, must override HierarchicalDataSourceView.Select<\/a> and return a collection implementing IHierarchicalEnumerable<\/a>. So, let’s define our IHierarchicalEnumerable.<\/p>\n
    using System.Web.UI;\r\nusing System.Collections;\r\n\u00a0\r\nnamespace SQLTreeView\r\n{\r\n\u00a0\u00a0\u00a0 \/** A collection of IHierarchyData objects -- nothing specific to \r\n\u00a0\u00a0\u00a0\u00a0 * SQLTreeNodeHierarchyData objects happens in this class.\r\n\u00a0\u00a0\u00a0\u00a0 *\/ \r\n\u00a0\u00a0\u00a0 public class HierarchicalEnumerable : ArrayList, IHierarchicalEnumerable\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Casts as IHierarchyData\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * returns null if that was not possible\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * @enumeratedItem object to cast as IHierarchyData\r\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public IHierarchyData GetHierarchyData(object enumeratedItem)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ not clear why this is an instance method\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return enumeratedItem as IHierarchyData;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 }\r\n}\r\nWe now need a class to implement the IHierarchyData object.\r\nusing System.Web.UI;\r\nusing System.Data.SqlTypes;\r\nusing System.Text.RegularExpressions;\r\n\u00a0\r\nnamespace SQLTreeView\r\n{\r\n\u00a0\u00a0\u00a0 \/** Class to transfer nested set data to the TreeView\r\n\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0 public class SQLTreeNodeData : IHierarchyData\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected static string _select = \"GetDescendingNodes\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected bool _hasChildren = false;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected bool _checked = false;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected string _toolTip = \"\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected string _text = \"\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected string _path = \"\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected string _imageURL = \"\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected HierarchicalEnumerable _children\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = new HierarchicalEnumerable();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected SQLTreeNodeData _parent = null;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected Regex _lasttoken = new Regex(\"([^\/]*)$\");\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\/** create a tree node with various display settings set\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * @path the text for each ancestor node in order from root to this,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\u00a0 delmited by slashed\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * @text Caption for the node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * @tooltip pop-up mouseover text\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0* @imageURL locator for icon to display\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * @checkd whether the node is checked. 'checked' is a C# keyword.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public SQLTreeNodeData(string path, string text, string tooltip, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string imageURL, bool checkd)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (_lasttoken.Matches(path)[0].ToString() != text) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 throw new System.Exception(text + \" is not the terminating token in \" + path);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.Path = path;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.Text = text;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.ToolTip = tooltip;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.ImageURL = imageURL;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ checked is misspelled because it is a keyword\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.Checked = checkd;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Return the path for ToString requests\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public override string ToString()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return this.Path;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** The stored procedure to call to collect the TreeNodes\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public static string Select\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 get\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return _select;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 _select = value;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ report if a children collection with non-zero count exists\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public bool HasChildren\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 get\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (null == _children || 0 == _children.Count)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return true;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** the slash-delimited text of the node and its ancestors\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string Path{ get { return _path; } set { _path = value; }}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Text for the treeview node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string Text\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 get { return _text; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set { _text = value; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** ToolTip to contextualize the node for the user\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string ToolTip{get { return _toolTip; }set { _toolTip = value; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Uniform resource identifier for a picture to display on the TreeView\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string ImageURL {get {return _imageURL; }set {_imageURL = value;}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Whether to display the treenode as checked\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public bool Checked {get {return _checked; } set {_checked = value; } }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Accessor and Modifier for the collection of children\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public HierarchicalEnumerable Children\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 get { return _children; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set { _children = value; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** specify which node contains this node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public SQLTreeNodeData Parent\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 get { return _parent; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set { _parent = value; }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** interface method to access Children property\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public IHierarchicalEnumerable GetChildren()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return this.Children;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** interface method to access Parent property\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public IHierarchyData GetParent()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return this.Parent;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** interface property to return this very object\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public object Item { get { return ((IHierarchyData)this); } }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** interface propery to wrap this.getType().Name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * If the TreeViewBinding.DataMember property is specified and \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * specified a matching value, this element may get displayed\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string Type{ get{ return this.GetType().Name; }}\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n\u00a0\r\n<\/pre>\n
    I added the name of the select stored procedure to the object as a static property. The public fields consumed by the .NET infrastructure, including those mapped to column names, are all properties, because you otherwise risk “Could not find a property named…<\/a>” errors.<\/p>\n
    We’re now ready to code a HierarchicalDataSourceView.<\/p>\n
    using System.Configuration;\r\nusing System.Data.SqlClient;\r\nusing System.Data;\r\nusing System.Collections;\r\nusing System.Web.UI;\r\nusing System;\r\nnamespace SQLTreeView\r\n{\r\n\u00a0\u00a0\u00a0 \/\/ The SQLTreeNodeView class encapsulates the\r\n\u00a0\u00a0\u00a0 \/\/ capabilities of the SQLTreeNodeDataSource data source control.\r\n\u00a0\u00a0\u00a0 public class SQLTreeNodeView : HierarchicalDataSourceView\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public string RootNodePath;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected HierarchicalEnumerable _lastreturned = null;\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** create a view of the node with the \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * specified path and its descendants\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public SQLTreeNodeView(string rootNodePath)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Store the root node path\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RootNodePath = rootNodePath;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** create a view of the entire tree\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public SQLTreeNodeView() : this(null) { }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/**Return the list of nodes descending from the slash-delimited path\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * Changing or generalizing the delimiter is \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * left as an exercise to the reader\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public override IHierarchicalEnumerable Select()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HierarchicalEnumerable retval = new HierarchicalEnumerable();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ load some TreeNodeData objects from the database and return them\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DataSet treeNodeDataSet = new DataSet(SQLTreeNodeData.Select);\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0try\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* pull a connection string from an \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * configuration\/connectionStrings\/add element in Web.config*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlConnection connect = new SqlConnection(ConfigurationManager\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .ConnectionStrings[\"Connectionstring\"].ConnectionString);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connect.Open();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlCommand command = connect.CreateCommand();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ populate the treeview with descendants of Tag\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.CommandText = SQLTreeNodeData.Select;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.CommandType = CommandType.StoredProcedure;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlCommandBuilder.DeriveParameters(command);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* DataBinding passes an empty string instead of null to \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * HierarchicalDataSourceView.GetHierarchicalView. Correct that \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * here.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Parameters[\"@Path\"].Value = this.RootNodePath == \"\" ? \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 null : this.RootNodePath;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Hashtable parentage = new Hashtable();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlDataReader reader = \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.ExecuteReader(CommandBehavior.Default);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 while (reader.Read())\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ capture the record\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQLTreeNodeData thd = new SQLTreeNodeData(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (string)reader[\"Path\"], (string)reader[\"Text\"], \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (string)reader[\"ToolTip\"], (string)reader[\"ImageURL\"], \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (bool)reader[\"Checked\"]);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ store the record in your hashtable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string path = reader[\"Path\"].ToString();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parentage.Add(path, thd);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* the stored procedure guarantees that parents will be \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * returned before children\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string parentpath = reader[\"ParentPath\"].ToString();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ add the parent and child to each node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 thd.Parent = \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SQLTreeView.SQLTreeNodeData)parentage[parentpath];\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (thd.Parent != null) \/\/ handle the root\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ((ArrayList)thd.Parent.Children).Add(thd);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ return the root node\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (0 == retval.Count) retval.Add(thd);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 reader.Close();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 reader.Dispose();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 command.Dispose();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connect.Close();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Write SQLExceptions to standard out\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 catch (SqlException se)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Console.Write(se.StackTrace);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (SqlError ser in se.Errors)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Console.Write(ser.Message);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 catch (NullReferenceException nre)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* return an empty data set if the \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connectionstringname property isn't present\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ update the local field\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 _lastreturned = retval;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Return the HierarchicalEnumerable with 0 or 1 members \r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return retval;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Report the last data to be constructed and returned\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public HierarchicalEnumerable LastReturned { get { return _lastreturned; } }\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n
    The Select method builds the hierarchy and loads the root node into the return value. It also loads every other node into that node’s parent’s Children collection, which we defined as part of the SQLTreeNodeData implementation of IHierachyData. That Select method is the most coding that gets done; most of this implementation is just getting the objects in the right places.<\/p>\n
    Unlike with identifiers like the NodeID, there’s no philosophical objection to exposing lft<\/b> and rgt<\/b> to the data consumer. They’re meaningful numbers describing the node position in any context. We certainly could have retrieved the data as lft<\/b> , rgt<\/b> , Text, et cetera, instead of building the path enumeration in the database, but the logic for matching parents to children in C# would have been somewhat hairier. Any node can get its immediate children from the TreeNode table with<\/p>\n
     <\/p>\n
    SELECT * FROM TREENODE WHERE lft > @lft AND rgt < @rgt AND  \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @LEVEL + 1 = (SELECT COUNT(*) FROM TreeNode anc WHERE \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 anc.lft < TREENODE.lft AND anc.rgt > TREENODE.rgt )\r\n<\/pre>\n
    where @lft is the node’s lft<\/b> value and @rgt is the node’s rgt<\/b> value, and @LEVEL is the number of the node’s ancestors, or<\/p>\n
    SELECT COUNT(*) FROM TreeNode anc WHERE anc.lft < @lft AND anc.rgt > @rgt \r\n<\/pre>\n
    but you can see that that’s starting to become a large number of database round-trips. This is why we’re getting all the nodes at once.<\/p>\n
    Or, nearly. There’s a hidden feature in the code as written that lets you start your tree from any node. Wiring that in to the front-end code is left as an exercise to the reader.<\/p>\n
    With a principle of reducing impact on data resources in mind, you might prefer to load the data into a detached data set and close the connection before doing anything else at all. However, in this case, all that’s being done is the building of a number of ArrayList objects — there’s little faster. Building the hierarchy of SQLTreeNodeData objects as you navigate the open cursor is not even necessarily more demanding that the construction of an offline DataSet would be.<\/p>\n
    Our only remaining component to provide a data source for a TreeView is the DataSource itself. So…<\/p>\n
    using System.Web.UI;\r\nnamespace SQLTreeView\r\n{\r\n\u00a0\u00a0\u00a0 \/**Data Source for the SQLTreeNodeView\r\n\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0 public class SQLTreeNodeDataSource : HierarchicalDataSourceControl\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 private SQLTreeNodeView _view = null;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Returns a SQLTreeNodeView object providing access to \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * Select functionality for the SQLTreeNode objects descending from the\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * given path\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * @rootnodepath the path from which returned nodes descend; if set to \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * null, all nodes are returned\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected override HierarchicalDataSourceView \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GetHierarchicalView(string rootnodepath)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (null == _view)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 _view = new SQLTreeNodeView(rootnodepath);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else if (_view.RootNodePath != rootnodepath) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 _view.RootNodePath = rootnodepath;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return _view;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Overriding HierarchicalDataSourceControl.CreateControlCollection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * allows the TreeView to attach to the DataSouce in the aspx page, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * which simplifies data binding immensely\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected override ControlCollection CreateControlCollection()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return new ControlCollection(this);\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** Return the last data set to be constructed \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public HierarchicalEnumerable LastReturned \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 { get { return _view == null ? null : _view.LastReturned; } }\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n
    We now have a HierarchicalDataSourceControl<\/a> object that can be used declaratively, so let’s take advantage of that.<\/p>\n
    The Front End Bit<\/h3>\n
    With a registered and included SQLTreeNodeDataSource control, my aspx page now looks like<\/p>\n
    <%@ Page Language=\"C#\" AutoEventWireup=\"true\" \r\nCodeBehind=\"SQLTreeNodeDemonstration.aspx.cs\" \r\nInherits=\"SQLTreeView.SQLTreeNodeDemonstration\" %>\r\n<%@ Register Assembly=\"SQLTreeView\" Namespace=\"SQLTreeView\" TagPrefix=\"stv\" %>\r\n<!DOCTYPE html PUBLIC \"-\/\/W3C\/\/DTD XHTML 1.0 Transitional\/\/EN\" \r\n\"http:\/\/www.w3.org\/TR\/xhtml1\/DTD\/xhtml1-transitional.dtd\">\r\n<html xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\" >\r\n<head runat=\"server\">\r\n\u00a0\u00a0\u00a0 <title>SQLTreeNodeDataSource Demonstration<\/title>\r\n<\/head>\r\n<body>\r\n\u00a0\u00a0\u00a0 <form id=\"frm\" runat=\"server\">\r\n\u00a0\u00a0\u00a0 <stv:SQLTreeNodeDataSource runat=\"server\" id=\"tds\" \/>\r\n\u00a0\u00a0\u00a0 <div>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <asp:TreeView ID=\"tvw\" runat=\"server\" DataSourceID=\"tds\"\/>\r\n\u00a0\u00a0\u00a0 <\/div>\r\n\u00a0\u00a0\u00a0 <\/form>\r\n<\/body>\r\n<\/html>\r\n\r\n<\/pre>\n
    All that’s left, really, is to bind the SQLTreeNodeData<\/b> fields to TreeNode<\/b> properties, which we do through a property page on the TreeView<\/b> called DataBindings<\/b>.<\/p>\n
    \"623-Third.png\"<\/p>\n
    Figure \u00a03… Property Page for the TreeView Control<\/p>\n
    Click on the ellipses in the Property Page for the TreeView<\/b> to launch the TreeViewDataBindingsEditor.<\/b><\/p>\n
    \"623-Fourth.png\"<\/p>\n
    Figure 4…TreeViewDataBindings Editor<\/p>\n
    Click the ‘Add’ button to create a TreeViewDataBindings <\/b>object.<\/p>\n
    \"623-Fifth.png\"<\/p>\n
    Figure\u00a0 5… TreeViewBindings Editor with bindings defined<\/p>\n
    \n
    \"623-Sixth.png\"<\/p>\n
    Figure \u00a06… ASPX page showing TreeView 
    \nwith HierarchicalDataSourceControl<\/p>\n<\/div>\n
    Populate the fields of the new TreeViewDataBindings. The DataMember field should be the return value of the ‘Type’ property of the SQLTreeNodeData objects. This can be useful as a filter, but as I’ve implemented it each object just returns the class name, so this field could just as well be left blank. The ImageUrlField, TextField and ValueField have the obvious mappings, and the ValueField is set to text so as to enable the Find method for nodes. This is also why it was so important that we use a declarative data source with DataSourceID instead of programmatically setting the DataSource property of the TreeView — Find does not work otherwise, and we are going to need it to.\u00a0 Not to foreshadow.<\/p>\n
    You now have a working TreeView based on hierarchical data stored as nested sets in the database.<\/p>\n
    We should take a moment to savor this before addressing that nagging feeling that we’ve defined a column and returned it in our stored procedure without displaying it on the page.<\/p>\n
    Adding Checkboxes<\/h3>\n
    \n
    \"623-Seventh.png\"<\/p>\n
    Figure 7: ShowCheckBoxes property of 
    \nthe TreeView control<\/p>\n<\/div>\n
    There comes a time in every .NET implementation — or at least every one I have done to date — where patiently sorting out how to connect all the plumbing to meet your specified requirements suddenly blows up into a red-eyed tragicomedy. One day you’re tending sheep for Laban, then suddenly you’re taking blows to your hip socket in a tent at Peniel.<\/p>\n
    And so it is with checkboxes. You can show them easily enough by setting ‘ShowCheckBoxes’ to Leaf, All or whatever you please.<\/p>\n
    The difficulties — or ‘opportunities for excellence’ — start to come when you try to do things like handle OnCheck events, or even set them to checked. I will show you how to do the latter\u00a0 here. The former is still beyond my reach.<\/p>\n
    OnDataBound<\/h4>\n
    .NET data-bound controls provide ‘OnDataBinding’ and ‘OnDataBound’ events, to modify states before or after a control updates itself with retrieved data respectively. We can implement any event handler by clicking through the event part of the property page.<\/p>\n
    \"623-Eighth.png\"<\/p>\n
    Figure\u00a0 8… Event handlers for the TreeView control<\/p>\n
    Triple click in the method name field for the DataBound event handler, and Visual Studio 2005 will create the handler and give it focus in the code editor.<\/p>\n
    using System;\r\nusing System.Web.UI;\r\nnamespace SQLTreeView\r\n{\r\n\u00a0\u00a0\u00a0 public partial class SQLTreeNodeDemonstration : System.Web.UI.Page\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0protected void tvw_DataBound(object sender, EventArgs e)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ get the last constructed data set from the data source\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 HierarchicalEnumerable he = tds.LastReturned;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (SQLTreeNodeData tnd in he)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CheckBoxes(tnd);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/** For each passed node and its descendants, check the corresponding \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * box if the Checked property is true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 protected void CheckBoxes(SQLTreeNodeData tnd) { \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ if Checked is True, find the corresponding TreeNode and check it\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* Note that FindNode is expensive enough that \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * tvw.FindNode(tnd.Path).Checked = tnd.Checked \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 * would be rather slower\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (tnd.Checked) tvw.FindNode(tnd.Path).Checked = true;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (IHierarchyData ihd in tnd.GetChildren())\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CheckBoxes((SQLTreeNodeData)ihd);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n
     <\/p>\n
    \n
    \"623-Ninth.png\"<\/p>\n
    Figure\u00a0 9… Displayed TreeView with checked state 
    \nfrom data<\/p>\n<\/div>\n
    This justifies our LastReturned<\/b> read-only HierarchicalEnumerable-valued property procedures for the SQLTreeNodeView<\/b> and SQLTreeNodeDataSource<\/b> objects. If we hadn’t get a copy of those objects when creating them for the TreeView’s consumption, we would have to return to the database to get the data again. Our other option would be to store the ‘Checked’ value in some otherwise irrelevant property, perhaps binding it with TargetField,<\/b> but we have to realize that the days of storing obscure XML snippets in the tag property of controls are gone.<\/p>\n
    In any case, we have now succeeded in our task as set.<\/p>\n
    Thanks for reading, and I hope you enjoy your many .NET TreeView controls populated from nested sets!<\/p>\n
     <\/p>\n<\/div>\n
    \u00a0<\/div>\n","protected":false},"excerpt":{"rendered":"
    It sometimes happens that Web Server controls that visualize data
    \ndon’t quite fit with the way that the data is actually held in the application. This shouldn’t be an obstacle, as Ion Freeman
    \npoints out. To prove his point, he shows how to use Nested Sets with Treeview.…<\/p>\n","protected":false},"author":221850,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538],"tags":[4143,4157,4179],"coauthors":[11315],"class_list":["post-493","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-asp-net","tag-source-control"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221850"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=493"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/493\/revisions"}],"predecessor-version":[{"id":74322,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/493\/revisions\/74322"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=493"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}