Treeview from sql table

Question

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
               -yes

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

The purpose of a tree view is to show the child nodes only when the user expands the parent node. In other words, you should simply load the immediate children when parent is expanded. Trying to load all tree at startup is a bad design idea. This table is small, so not a big problem here, but imagine it is huge. Why would you force your users to wait for a full tree to load? — Jose Rui Santos
– Jose Rui Santos, Commented Jul 19, 2011 at 9:01
@Jose Rui Santos sir,i am a beginner in asp.net.we can go for node expansion when it is selected ....but i don't know how to do... — dhanya
– dhanya, Commented Jul 19, 2011 at 15:59
@Dhany; please change this line with this one ds.Relations.Add("Children", dtbl1.Columns["Id"], dtbl1.Columns["ParentId"]); — Muhammad Akhtar
– Muhammad Akhtar, Commented Jul 20, 2011 at 5:26
my answer from a while ago might help: stackoverflow.com/questions/948592/… — CRice
– CRice, Commented Jul 20, 2011 at 5:45

PraveenVenu · Accepted Answer · 2011-08-24 14:12:27Z

2

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

answered Aug 24, 2011 at 14:12

PraveenVenu

8,3674 gold badges32 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Will P. · Accepted Answer · 2011-12-01 17:09:43Z

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.

Collectives™ on Stack Overflow

Treeview from sql table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related