Treeview from sql table

I have sql table like below. I have to show it in tree view

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 want output as

-outlook

 - overcast

         - yes

- rainy
     - wind
        - strong
              - no
        - weak
              - yes
-sunny
   - humidity

         -high
               -no
         -normal
          开发者_StackOverflow社区     -yes

There is only one root node 'outlook' here.then comes child nodes and sub-child nodes like that.

---

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

---

Try the following code in your aspx or ascx file:

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

And in the codebehind 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 might be a better way to do this and I haven't tested it, but it should work. Or you could always try out Telerik or another third party tool that makes data binding for these types of controls super easy.