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, Jul 19 '11 at 09: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, Jul 19 '11 at 15:59
@Dhany; please change this line with this one ds.Relations.Add("Children", dtbl1.Columns["Id"], dtbl1.Columns["ParentId"]); — Muhammad Akhtar, Jul 20 '11 at 05:26
my answer from a while ago might help: http://stackoverflow.com/questions/948592/populating-treeview-using-linq/948790#948790 — CRice, Jul 20 '11 at 05:45

score 2 · Answer 1 · answered Aug 24 '11 at 14:12

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

score 0 · Answer 2 · answered Dec 01 '11 at 17:09

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.

Treeview from sql table

2 Answers2

Linked

Related