locked
Problem with TreeView bind from database RRS feed

  • Question

  • User-2108448128 posted

    Hi

    I am trying to populate TreeView from database but I am having problems with sub child nodes 

    Why "Durres" doesnt show up as child of "Tirana" and why "Shkoder" doesnt show up as child of "Durres"?

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace WebApplication1
    {
        public partial class Index : System.Web.UI.Page
        {
            Boolean m_bNodeFound;
            Boolean intNodeFound;
            string sValuepath;
            protected void Page_Load(object sender, EventArgs e)
            {
    
    
    
    
                if (!IsPostBack)
                {
    
                    using (var connection = new SqlConnection("Data Source=(localdb)\\ProjectsV13;Initial Catalog=gab;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"))
                    {
                        string com = "Select * from dbo.TopBill";
                        SqlDataAdapter adpt = new SqlDataAdapter(com, connection);
                        DataTable dt = new DataTable();
                        adpt.Fill(dt);
                        connection.Open();
                        TreeNode root = new TreeNode();
    
                        root = new TreeNode();
                        root.Text = dt.Rows[0][5].ToString();
                        root.Value = dt.Rows[0][0].ToString();
                        TreeView1.Nodes.Add(root);
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //make NodeNotFound  at each row
                            m_bNodeFound = false;
    
                            //root as it is
                            TreeNode tn = root;
    
                            //get NodeName by cName
                            string nodeName = dt.Rows[i][5].ToString();
    
                            //Check if it is not null, to skip errors
                            if (nodeName != null && nodeName != "")
                            {
                                //MainNodecollections
                                TreeNodeCollection nodes = TreeView1.Nodes;
    
                                //Check if node already exists in Treeview
                                FindNodeInHierarchy(nodeName);
    
                                //If not found then continue
                                if (!m_bNodeFound)
                                {
                                    //If node is root node
                                    if (dt.Rows[i][2].ToString() == "" || dt.Rows[i][2].ToString() == null)
                                    {
                                        TreeNode root2 = new TreeNode();
                                        root2.Text = dt.Rows[i][5].ToString();
                                        root2.Value = dt.Rows[i][0].ToString();
                                        TreeView1.Nodes.Add(root2);
                                    }
                                    //Check if node is child node
                                    else if (CheckRowsAllValues(dt.Rows[i][1].ToString(), dt))
                                    {
                                        //Find New Root of child node
                                        TreeNode NewRoot = FindRoot(dt.Rows[i][1].ToString(), dt, root);
                                        //if New root is not empty
                                        if (NewRoot != null)
                                        {
                                            TreeNode root2 = new TreeNode();
                                            root2.Text = dt.Rows[i][5].ToString();
                                            root2.Value = dt.Rows[i][5].ToString();
                                            //append child node(current value) with new root
                                            NewRoot.ChildNodes.Add(root2);
                                        }
    
                                    }
                                }
    
                            }
                        }
    
                    }
                }
            }
    
            private TreeNode FindRoot(string v, DataTable dt, TreeNode tNode)
            {
                string expression = "cId = " + v;
                DataRow[] foundRows;
                foundRows = dt.Select(expression);
    
                //Find node using Id of table row
                TreeNode tn = TreeView1.FindNode(foundRows[0][0].ToString());
    
                //if not found, search using Name
                if (tn == null && foundRows[0][5].ToString() != "")
                {
                    var value = foundRows[0][5].ToString();
                    TreeNode searchedNode = null;
    
                    //search node by Value(City Name) by looping each node
                    foreach (TreeNode node in TreeView1.Nodes)
                    {
                        if (searchedNode == null)
                        {
                            searchedNode = SearchNode(node, value);
                            if (searchedNode == null)
                            {
                                foreach (TreeNode childNode in node.ChildNodes)
                                {
                                    searchedNode = SearchNode(childNode, value);
                                    if (searchedNode != null)
                                        tn = searchedNode;
                                }
                            }
                            else
                            {
                                break;
                            }
                        }
                        else
                        {
                            break;
                        }
                    }
                    tn = searchedNode;
    
                }
    
                return tn;
            }
    
            //Search Node code
            private TreeNode SearchNode(TreeNode node, string searchText = null)
            {
                if (node.Text == searchText) return node;
    
                TreeNode tn = null;
                foreach (TreeNode childNode in node.ChildNodes)
                {
                    tn = SearchNode(childNode);
                    if (tn != null) break;
                }
    
                if (tn != null) node.Expand();
                return tn;
            }
    
            private bool CheckRowsAllValues(string v, DataTable dt)
            {
                string expression = "cId = " + v;
                DataRow[] foundRows;
                foundRows = dt.Select(expression);
                if (foundRows.Count() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
    
    
    
            private void FindNodeByValue(string strValue)
            {
                // If the TreeView control contains any root nodes, perform a
                // preorder traversal of the tree and display the text of each node.
                if (TreeView1.Nodes.Count > 0)
                {
    
                    // Iterate through the root nodes in the Nodes property.
                    for (int i = 0; i < TreeView1.Nodes.Count; i++)
                    {
    
                        // Display the nodes.
                        DisplayChildNodeText(TreeView1.Nodes[i], strValue);
    
                    }
    
                }
            }
    
    
            void DisplayChildNodeText(TreeNode node, string strValue)
            {
    
                // Display the node's text value.
                //Message.Text += node.Text + "<br />";
                if (strValue == node.Text.ToString())
                {
                    sValuepath = node.ValuePath;
                    intNodeFound = true;
                }
    
                // Iterate through the child nodes of the parent node passed into
                // this method and display their values.
                for (int i = 0; i < node.ChildNodes.Count; i++)
                {
    
                    DisplayChildNodeText(node.ChildNodes[i], strValue);
    
                }
    
                if (intNodeFound) return;
    
            }
    
    
    
            private void FindNodeInHierarchy(string strSearchValue)
            {
    
                // If the TreeView control contains any root nodes, perform a
                // preorder traversal of the tree and display the text of each node.
                if (TreeView1.Nodes.Count > 0)
                {
    
                    // Iterate through the root nodes in the Nodes property.
                    for (int i = 0; i < TreeView1.Nodes.Count; i++)
                    {
    
                        // Display the nodes.
                        CheckChildNodeText(TreeView1.Nodes[i], strSearchValue);
    
                    }
    
                }
            }
    
    
            void CheckChildNodeText(TreeNode node, string strValue)
            {
    
                // Display the node's text value.
                //Message.Text += node.Text + "<br />";
                if (strValue == node.Text.ToString())
                {
                    m_bNodeFound = true;
                }
    
                // Iterate through the child nodes of the parent node passed into
                // this method and display their values.
                for (int i = 0; i < node.ChildNodes.Count; i++)
                {
    
                    DisplayChildNodeText(node.ChildNodes[i], strValue);
    
                }
    
                if (m_bNodeFound) return;
    
            }
        }
    }



    The result 

    What code should do or what I want in this case

    Wednesday, December 11, 2019 10:09 PM

Answers

  • User-2108448128 posted

    I found another code that is doing what I want but it is visual basic.I try to convert to c# and doesnt run the same results

    database 

    CREATE TABLE [dbo].[SampleCategories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [parentid] INT NULL,
    [title] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 12, 2019 4:13 PM
  • User288213138 posted

    Hi ai3231,

    You can also try to use recursion to bind data.

    The code:

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    DataTable dt = this.GetData("SELECT * FROM dbo.TopBill where Pid =0");
                    TreeNode node = new TreeNode();
                    AddNodes(dt, 0, node);
                }
            }
            private void AddNodes(DataTable dt, int parentId, TreeNode node)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    TreeNode subnode = new TreeNode
                    {
                        Text = dr["Cname"].ToString(),
                        Value = dr["cId"].ToString()
                    };
                    DataTable subdt = this.GetData("SELECT * FROM dbo.TopBill where Pid = " + subnode.Value.ToString());
                    if (parentId == 0)
                    {
                        TreeView1.Nodes.Add(subnode);
                        AddNodes(subdt, int.Parse(subnode.Value.ToString()), subnode);
                    }
                    else
                    {
                        AddNodes(subdt, int.Parse(subnode.Value.ToString()), subnode);
                        node.ChildNodes.Add(subnode);
                    }
    
                }
    
            }
            private DataTable GetData(string query)
            {
                DataTable dt = new DataTable();
                string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            sda.Fill(dt);
                        }
                    }
                    return dt;
                }
            }

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 13, 2019 9:52 AM

All replies

  • User288213138 posted

    Hi ai3231,

    Why "Durres" doesnt show up as child of "Tirana" and why "Shkoder" doesnt show up as child of "Durres"?

    I debugged your code and found that it will end when the DataTable is traversed for the third time. and your code logic is very complicated. so I suggest you can use the concise method. 

    You can create the Tree Node Head and add to the Tree view Control firstly, then add the child nodes to the Parent Nodes to the correct position.

    More information about this method you can refer this link:

    https://social.technet.microsoft.com/wiki/contents/articles/34948.asp-net-c-bind-tree-view-without-recursive-functions.aspx#Step_1

    Thursday, December 12, 2019 11:17 AM
  • User-2108448128 posted

    And which would be the structure of the database in my case with the code you suggested?

    Thursday, December 12, 2019 11:24 AM
  • User-1780421697 posted

    I think that your table structure is missing LevelId you need to specify the level of tree where you are storing a node, like at level 0 you can have a country, at level 1 you have its province, at level 2 you can have its union councils etc ...

    you need to mention level in your table and arrange and filter data by levels.

    Thursday, December 12, 2019 11:33 AM
  • User-2108448128 posted

    I found another code that is doing what I want but it is visual basic.I try to convert to c# and doesnt run the same results

    database 

    CREATE TABLE [dbo].[SampleCategories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [parentid] INT NULL,
    [title] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
    );

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 12, 2019 4:13 PM
  • User-2108448128 posted

    found the solution nevermind

    Thursday, December 12, 2019 5:00 PM
  • User288213138 posted

    Hi ai3231,

    You can also try to use recursion to bind data.

    The code:

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    DataTable dt = this.GetData("SELECT * FROM dbo.TopBill where Pid =0");
                    TreeNode node = new TreeNode();
                    AddNodes(dt, 0, node);
                }
            }
            private void AddNodes(DataTable dt, int parentId, TreeNode node)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    TreeNode subnode = new TreeNode
                    {
                        Text = dr["Cname"].ToString(),
                        Value = dr["cId"].ToString()
                    };
                    DataTable subdt = this.GetData("SELECT * FROM dbo.TopBill where Pid = " + subnode.Value.ToString());
                    if (parentId == 0)
                    {
                        TreeView1.Nodes.Add(subnode);
                        AddNodes(subdt, int.Parse(subnode.Value.ToString()), subnode);
                    }
                    else
                    {
                        AddNodes(subdt, int.Parse(subnode.Value.ToString()), subnode);
                        node.ChildNodes.Add(subnode);
                    }
    
                }
    
            }
            private DataTable GetData(string query)
            {
                DataTable dt = new DataTable();
                string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            sda.Fill(dt);
                        }
                    }
                    return dt;
                }
            }

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 13, 2019 9:52 AM