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

Solution 1:

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

Solution 2:

Try the following code in your aspx or ascx file:

<asp:TreeviewID="TreeView1"runat="server" />

And in the codebehind to populate it:

privatevoidPopulateTreeView()
{
    DataSet ds = newDataSet(); //(populate the dataset with the table)//Use LINQ to filter out items without a parentDataTable parents = ds.Tables[0].AsEnumerable()
        .Where(i => i.Field<object>("parentid") == DBNull.Value)
        .CopyToDataTable();

    //Use LINQ to filter out items with parentDataTable 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 treeviewforeach(DataRow dr in parents)
    {
        TreeNode node = newTreeNode();
        node.Text = dr["name"].ToString();
        node.Value = dr["id"].ToString();
        TreeView1.Nodes.Add(node);
    }

    //Add the children to the parentsforeach(DataRow dr in children)
    {
        TreeNode node = newTreeNode();
        node.Text = dr["name"].ToString();
        node.Value = dr["id"].ToString();
        TreeNode parentNode = FindNodeByValue(dr["parentid"].ToString());
        if(parentNode != null)
            parentNode.ChildNodes.Add(node);
    }
}

privateTreeNodeFindNodeByValue(string value)
{
    foreach(TreeNode node in TreeView1.Nodes)
    {
        if(node.Value = value) return node;
        TreeNode pnode = FindNodeRecursion(node, value);
        if(pnode != null) return pnode;
    }
    returnnull;
}

privateTreeNodeFindNodeRecursion(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;
    }
    returnnull;
}

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.