Treeview from sql table

Question

I have a sql table as shown below. I have to show it as a tree

id parentid name 1 NULL outlook 2 1 overcast 3 1 rainy 4 1 sunny 5 2 yes 6 3 wind 7 4 humidity 8 6 strong 9 6 weak 10 7 high 11 8 no 12 9 yes 13 10 no 14 15 yes 15 7 normal 

I need a conclusion like

-Outlook

  - overcast - yes - rainy - wind - strong - no - weak - yes -sunny - humidity -high -no -normal -yes 

There is only one root node outlook. Then, the child nodes and nodes of the child nodes appear.

Answer 1

 WITH q AS ( SELECT * FROM mytable WHERE ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate UNION ALL SELECT m.* FROM mytable m JOIN q ON m.parentID = q.ID ) SELECT * FROM q 

Answer 2

check this link from SO:

populate-treeview-from-database

I think he has a good answer for you.

Answer 3

Check out this link, and also uses the CTE feature introduced with SQL Server 2005

Printing a tree using SQL CTE

Answer 4

Try using the following code in an aspx or ascx file:

 <asp:Treeview ID="TreeView1" runat="server" /> 

And in the code to populate it:

 private void PopulateTreeView() { DataSet ds = new DataSet(); //(populate the dataset with the table) //Use LINQ to filter out items without a parent DataTable parents = ds.Tables[0].AsEnumerable() .Where(i => i.Field<object>("parentid") == DBNull.Value) .CopyToDataTable(); //Use LINQ to filter out items with parent DataTable children = ds.Tables[0].AsEnumerable() .Where(i => i.Field<object>("parentid") != DBNull.Value) .OrderBy(i => i.Field<int>("parentid")) .CopyToDataTable(); //Add the parents to the treeview foreach(DataRow dr in parents) { TreeNode node = new TreeNode(); node.Text = dr["name"].ToString(); node.Value = dr["id"].ToString(); TreeView1.Nodes.Add(node); } //Add the children to the parents foreach(DataRow dr in children) { TreeNode node = new TreeNode(); node.Text = dr["name"].ToString(); node.Value = dr["id"].ToString(); TreeNode parentNode = FindNodeByValue(dr["parentid"].ToString()); if(parentNode != null) parentNode.ChildNodes.Add(node); } } private TreeNode FindNodeByValue(string value) { foreach(TreeNode node in TreeView1.Nodes) { if(node.Value = value) return node; TreeNode pnode = FindNodeRecursion(node, value); if(pnode != null) return pnode; } return null; } private TreeNode FindNodeRecursion(TreeNode parentNode, string value) { foreach(TreeNode node in parentNode.ChildNodes) { if(node.Value = value) return node; TreeNode pnode = FindNodeRecursion(node, value); if(pnode != null) return pnode; } return null; } 

There may be a better way to do this, and I have not tested it, but it should work. Or you can always try Telerik or another third-party tool that simplifies data binding for these types of controls.