Treeview from SQL Table

[neoseeker191]

I have looked through a few SQL hierarchy tutorials, but none of them made much sense for my application. Perhaps I am just not understanding them correctly. I'm writing a C# ASP.NET application and I would like to create a tree view hierarchy from SQL data.

This is how the hierarchy would work:

SQL TABLE

ID     | Location ID | Name
_______| __________  |_____________
1331   | 1331        | House
1321   | 1331        | Room
2141   | 1321        | Bed
1251   | 2231        | Gym

If the ID and Location ID are the same, this would determine the top Parent. Any Children of that Parent would have the same Location ID as the Parent. Any Grandchildren of that Child would have a Location ID equal to the ID of the Child, and so on.

For the above example:

- House
   -- Room
       --- Bed

Ideally I would like to pull this data and place it in a treeview using C#.

Any help or direction to easy to follow tutorials would be greatly appreciated.

Thanks.

[BitBlt]

Interesting...I googled your question and found this:
http://stackoverflow.com/questions/6037501/sql-data-hierarchy
It appears you got an answer to this yesterday at a different web site. Why are you posting the same question here?

[neoseeker191]

Interesting...I googled your question and found this:
http://stackoverflow.com/questions/6037501/sql-data-hierarchy
It appears you got an answer to this yesterday at a different web site. Why are you posting the same question here?

I received an answer for how to do it in SQL. I was wondering how I would do it in C#. It seems it would be more efficient to build the tree in C# since I can populate the tree as I pull it from the SQL.

[BitBlt]

Ah. Okay, here's a link to a good article describing how one fellow solved it:
http://www.codeproject.com/KB/tree/TreeViewWithDatabase.aspx
I've always found this guy's articles very good, and his example code easy to follow. Hope this helps!
<downvote rescinded -- I should've read more closely -- sorry>

[neoseeker191]

Ah. Okay, here's a link to a good article describing how one fellow solved it:
http://www.codeproject.com/KB/tree/TreeViewWithDatabase.aspx
I've always found this guy's articles very good, and his example code easy to follow. Hope this helps!
<downvote rescinded -- I should've read more closely -- sorry>

Not a problem. I've read that article before. I'm just having trouble understanding how to apply what he covers in the article to what I need in my situation. Using SQL I can get the path as outlined in the first link you posted. I'd like to be able to do the same thing in C#, and that's the part I'm having trouble understanding.

[vincezed]

Try this out .. .. this is the exact code what u are searching for...

public void FillTree()
        {

            SqlCeConnection Connect = new SqlCeConnection("Data Source = test.sdf");

            Connect.Open();

            DataTable dtTree = new DataTable();
            string sql = "SELECT * FROM table";
            SqlCeDataAdapter Adapt = new SqlCeDataAdapter(sql, Connect);
            Adapt.Fill(dtTree);
            Adapt.Dispose();

            treeView1.BeginUpdate();
            treeView1.Nodes.Clear();

            TreeNode rootNode = treeView1.Nodes.Add("DATA");
            rootNode.Tag = "RootDB";
           
            CreateTreeView(rootNode.Nodes, 0, dtTree);
            rootNode.Nodes[0].Expand();

            treeView1.Select();
            treeView1.EndUpdate();
            Connect.Close();

        }

        // create tree 
        public void CreateTreeView(TreeNodeCollection parentNode, int parentID, DataTable mytab)
        {
           
            foreach (DataRow dta in mytab.Rows)
            {
                if (Convert.ToInt32(dta["LocationId"]) == parentID)
                {

                    String key = dta["Id"].ToString();
                    String text = dta["Name"].ToString();
                    TreeNodeCollection newParentNode = parentNode.Add(key, text).Nodes;
                    CreateTreeView(newParentNode, Convert.ToInt32(dta["Id"]), mytab);
                }
            }
        }

Well i have used a sdf database change to ur database...

Hope it works...

Thanks
vince

[BitBlt]

Unfortunately, I don't think the standard TreeView control has SAAT-style loading. So you have to get your data into a nice format then go through it RBAR to load the control. It's the same way for a lot of the list/grid controls. I think there are some third-party controls that do this, but I haven't used any of them (costs $ and I'm a cheapskate).

-SAAT = Set-At-A-Time
-RBAR = Row-by-Agonizing-Row

[neoseeker191]

I appreciate the code, it's a lot easier to learn (especially for me) how this all works when I have example's relative to my issue. Although I believe some of the definitions such as "EndUpdate()" pertain to the System.Windows.Forms.TreeView class. My project is an ASP.NET (C#) application, so I don't think I can use those classes.

*I believe that this should have been filed in ASP.NET, now that I look at the sections.

[neoseeker191]

Here is the code I came up with, but I can't figure out how to grab more than two levels of the tree.

using System;
using System.Data;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;

namespace TreeViewProject
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            PopulateTree(SampleTreeView);

        }



        public void PopulateTree(Control ctl)
        {

            // Data Connection
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString);
            connection.Open();

            // SQL Commands
            string getLocations = "SELECT ID, LocationID, Name FROM dbo.Locations";
            SqlDataAdapter adapter = new SqlDataAdapter(getLocations, connection);
            DataTable locations = new DataTable();
            // Fill Data Table with SQL Locations Table
            adapter.Fill(locations);
            // Setup a row index
            DataRow[] myRows;
            myRows = locations.Select();

            // Create an instance of the tree
            TreeView t1 = new TreeView();
            // Assign the tree to the control
            t1 = (TreeView)ctl;
            // Clear any exisiting nodes
            t1.Nodes.Clear();

            // BUILD THE TREE!
            for (int p = 0; p < myRows.Length; p++)
            {
                // Get Parent Node
                if ((Guid)myRows[p]["ID"] == (Guid)myRows[p]["LocationID"])
                {
                    // Create Parent Node
                    TreeNode parentNode = new TreeNode();
                    parentNode.Text = (string)myRows[p]["Name"];
                    t1.Nodes.Add(parentNode);

                    // Get Child Node
                    for (int c = 0; c < myRows.Length; c++)
                    {
                        if ((Guid)myRows[p]["LocationID"] == (Guid)myRows[c]["LocationID"] 
                            && (Guid)myRows[p]["LocationID"] != (Guid)myRows[c]["ID"] /* Exclude Parent */)
                        {
                            // Create Child Node
                            TreeNode childNode = new TreeNode();
                            childNode.Text = (string)myRows[c]["Name"];
                            parentNode.ChildNodes.Add(childNode);
                        }
                    }
                }
            }
            // ALL DONE BUILDING!

            // Close the Data Connection
            connection.Close();
        }

    }
}

[hades200621]

neoseeker191 I registered just to thank you, you code helped me alot!!!

[Momerath]

He hasn't logged in in 11 months, I doubt he'll see that.