How to manipulate hierarchical information in flat relational database tables (optionally industrial strength)

• Download demo project - 25.1 KB

Introduction

A simple table in a relational database is the Employees table. This has an employee ID, and a Reports to ID which is an employee ID. The normal way to fill your tree with this hierarchical information is to query for the root nodes, i.e., nodes with no parents; in this case, null in Reports to. Then, subsequently query the table for all children of the node, i.e., Reports to ID equals the root employee ID. Keep doing this until you come to leaf nodes with empty results. I will describe a better technique that avoids repetitive queries and has only one query to the table.

How to Represent the Data in the Relational Database Side

One part of the technique is to create a new key column in your relational table which has the hierarchical information in it. An example is as below: Table name: TreeTable.

As you can see from this example, there are random inserts going on, so the data is out of order. However, with a basic query that orders by the key column, this data will be in hierarchical sequence. An example follows: Select Node, Parent, [My Key] from TreeTable order by [My Key]. This produces the following result set:

The symbology is not pertinent here. The data in My Key column just requires that you use symbols for the node that are unique at the level of the tree that it exists at, and these symbols should sort in order according to the sort order of the database. This means that when you insert into a level, you should use the next available symbol, which in this case is the next highest number. The delimiter again could be any symbol; I have chosen '-' for convenience. The key at any level combines the key of the parent before it, as can readily be seen. Now the advantage is this puts almost no load on any existing relational database as it exists today, as sorting is very basic and all have implemented the Order by clause with great efficiency. The next step is how the client processes this information.

Client Processing

The client merely recurses over each row, checking to see if the My Key symbol in the current row starts with the My Key value from the previous row. If it does, it recurses to fill the tree at the next level. If it does not, it falls back down to the level below and checks for a match until it lands up at the root node level and paints it as a root node. A very simple algorithm on the client. And, in fractions of a second, you can fill a treeview or any other type of hierarchical visual control, or also any data structure that you might want to fill. Let's talk about inserting data into this tree or updating it. This is, of course, very trivial. All you need to do on an insert is check what the My Key value of the parent node is and append it to the beginning of the My Key value of the node you are inserting into the table, and append a unique identifier that has not been used at this level of the tree. In my example case, it would be the next highest number. It is insignificant really, and could just be the identity value of the current record you are inserting. As simple as that, which is always unique and implemented by a database like SQL Server. Or a sequence in Oracle. An update is merely doing the same thing over; you will change the My Key value if the update changes the parent of the node you are updating, in exactly the same fashion as an insert.

Summary

This technique allows you to efficiently present hierarchical data from a flat relational table with just one query to the relational database instead of multiple queries. It is much faster than existing techniques, and you should employ it wherever you have a relational table with hierarchical information in it.

Where do I Use it?

When I invented this technique in 2002, I was working with a huge tree that had unlimited growth potential for which the existing techniques that I knew of did not stand up to. An example of where this would be useful is for the parts explosion of an aircraft, the employees table that holds organizational chart information for a Fortune 500 company or one with many employees, or a scientific work which involves huge trees. It is useful with small trees when you wish to display the tree in its whole in one shot very quickly. I have shown how to insert, update, delete, and move branches so that you can use the system out of the box. But these are merely there for support, and not the reason to use the technique. This should answer the question, "Where do I use it?"

Should I use it as is?

Now for the question of "Should I use it as is?" When I wrote this code for the net, I used a DataSet, as it is the simplest tool for the job. However, the DataSet pulls the entire table across in one shot. If you are dealing with an extremely large table, this is not desirable. When working with a really large tree, you should replace the DataSet with a DataReader, cursor, or some other custom database access method that reduces the number of records fetched at one go. This is fairly simple to achieve, as one would just add a new counter variable to a class that the existing counter is compared to, and when it does not compare, do a fetch. I say this because the technique just says MovePrevious, and the GetNextRecord in that case fetches the same record again. So all you have to do is write a slightly smarter GetNextRecord that does not do a fetch unless it is required. So use it as it is if you do not have a huge tree but just want to be very fast, or change the dataset part of it only if you want to be industrial strength and deal with very large trees. I have now included the code for industrial strength version that uses a DataReader. I call this industrial strength because according to Micorosft, in the article "Contrasting the ADO.NET DataReader and DataSet", the DataReader will only fetch one row at a time over the network and not the entire table as does DataSet. The sorting part of it is not industrial strength as it would collapse under heavy load. When I come up with a better technique to handle sorting, I will update the article. It's left as an exercise to the reader now to implement a better sorting technique.

How to Modify the Code to Work with Querying a Branch of the Tree

This is fairly easy to do. You have the identity ID which is unique for the node whose branch you wish to display. You modify the query for the DataSet or DataReader generation to work with this. I will provide the query, and leave modifying the class that encapsulates the DataSet and DataReader to you as an exercise.

select * from datatable where id=3279
union
select * from datatable where
   treekey like (select treekey from datatable where id=3279) + '.%'
   order by treekey

The first query just gets the node itself, which should be at the top of the resultset. The next query just gets all its children, and orders them so that the technique can work to print them out serially. So the resulting change in the class is the following code modification:

public class DataRows
{
    .......
    public DataRows(int id)
    {
        SqlConnection conn = new SqlConnection(
              @"Server=Gandalf\SQL2K;Integrated Security=true;Database=treeview");
        SqlCommand cmd;
        if(id == 0)
        {
             cmd = new SqlCommand(
                   @"select * from datatable order by treekey", conn);
        }
        else
        {
             cmd = new SqlCommand(
                 @"select * from datatable where id="
                 + id.ToString() + 
                 " union select * from datatable where treekey like "
                 + "(select treekey from datatable where id="
                 + id.ToString() + ") + '.%' order by treekey", conn);
        }
        conn.Open();
        sdr = cmd.ExecuteReader();
    }
    .......
}

Now when instantiating the class, you just call it with an ID equal to zero if you want the whole tree, or the ID of the node if you want just the branch. The rest of the code, other than how to create a new instance of the class, remains the same.

Code for Form1 (DataSet Version)

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Treeview
{
    public partial class Form1 : Form
    {
        public static DataRows drows = new DataRows();
        public static DataTable dt = new DataTable();

        public Form1()
        {
            InitializeComponent();
            TreeNode tn = new TreeNode("Root");
            treeView1.Nodes.Add(tn);
            FillTreeView("", tn);
            SortNodes(null);
            DataColumn dc = new DataColumn("ID");
            dt.Columns.Add(dc);
            dc = new DataColumn("TreeKey");
            dt.Columns.Add(dc);
            dc = new DataColumn("ParentID");
            dt.Columns.Add(dc);
            dc = new DataColumn("Data");
            dt.Columns.Add(dc);
        }

        public class DataRows
        {
            private int counter = 0;
            private DataSet ds;

            public DataRows()
            {
                SqlDataAdapter sda = new SqlDataAdapter(
                    @"select * from datatable order by treekey",
                    @"Server=Gandalf\SQL2K;Integrated Security=true;" +
                     "Database=treeview");
                ds = new DataSet();
                sda.Fill(ds);
            }

            public DataRow GetNextRow()
            {
                if (counter >= ds.Tables[0].Rows.Count)
                    return null;
                else
                    return ds.Tables[0].Rows[counter++];
            }

            public void MovePrev() { --counter; }
        }

        private void FillTreeView(string currKeyRoot, TreeNode tn)
        {
            DataRow row = drows.GetNextRow();
            while(row != null)
            {
                if(NodeStartsWith(row["TreeKey"].ToString(), currKeyRoot))
                {
                    TreeNode tnChild = new TreeNode(row["Data"].ToString()
                        + "[" + row["TreeKey"].ToString() + "]");
                    tnChild.Tag = row;
                    tn.Nodes.Add(tnChild);
                    FillTreeView(row["TreeKey"].ToString(), tnChild);
                }
                else
                {
                    drows.MovePrev();
                    return;
                }
                row = drows.GetNextRow();
            }
        }

        private bool NodeStartsWith(string treekey, string currKeyRoot)
        {
            if (currKeyRoot == null || currKeyRoot.Length == 0 || 
               treekey == currKeyRoot || treekey.StartsWith(currKeyRoot + "."))
               return true;
            return false;
        }

        private void addNodeToolStripMenuItem_Click(object sender, 
                                                    EventArgs e)
        {
            TreeNode tn = treeView1.SelectedNode;
            if (tn != null)
            {
                DataRow r = tn.Tag as DataRow;
                AddNameForm frm = new AddNameForm();
                if (r != null)
                {
                    frm.r = r;
                }
                frm.tn = tn;
                frm.ShowDialog();
            }
        }

        private void deleteNodeToolStripMenuItem_Click(object sender, 
                                                       EventArgs e)
        {
            TreeNode tn = treeView1.SelectedNode;
            if (tn != null)
            {
                DataRow r = tn.Tag as DataRow;
                if (r != null)
                {
                   SqlConnection conn = new SqlConnection(
                                @"Server=Gandalf\SQL2K;" +
                                " Integrated Security=true;Database=treeview");
                   conn.Open();
                   if (MessageBox.Show("Do you want to delete all child nodes?",
                                       "Delete Node", 
                                MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        SqlCommand cmd = new SqlCommand(
                                      "delete datatable where treekey = '" + 
                                      r["TreeKey"].ToString() +
                                      "' or treekey like '" + 
                                      r["TreeKey"].ToString() + ".%'", conn);
                        cmd.ExecuteNonQuery();
                        tn.Parent.Nodes.Remove(tn);
                    }
                    else
                    {
                        SqlCommand cmd = new SqlCommand(
                                       "delete datatable where treekey = '" + 
                                       r["TreeKey"].ToString() + "'", conn);
                        cmd.ExecuteNonQuery();
                        tn.Parent.Nodes.Remove(tn);
                    }
                    conn.Close();
                }
            }
        }

        private void editNodeToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void treeView1_DragDrop(object sender, DragEventArgs e)
        {
            TreeNode tn = e.Data.GetData("System.Windows.Forms.TreeNode") 
                                                                 as TreeNode;
            TreeViewHitTestInfo hi = treeView1.HitTest(
                                 treeView1.PointToClient(new Point(e.X, e.Y)));
            if (hi.Node != null && tn != null)
            {
                DataRow rSrc = tn.Tag as DataRow;
                DataRow rDest = hi.Node.Tag as DataRow;
                SqlConnection conn = new SqlConnection(
                               @"Server=Gandalf\SQL2K;" +
                                "Integrated Security=true;Database=treeview");
                conn.Open();
                int dashpos = rSrc["TreeKey"].ToString().LastIndexOf('.');
                string subSrc = (dashpos > 0 ?
                      rSrc["TreeKey"].ToString().Substring(0, dashpos) : "");
                SqlCommand cmd = new SqlCommand(
                     "update datatable set treekey = '"
                     + rDest["TreeKey"].ToString()
                     + "' + right(treekey, len(treekey) - len('"
                     + subSrc + "')) where left(treekey, len('"
                     + rSrc["TreeKey"].ToString() + "')) + '.' = '"
                     + rSrc["TreeKey"].ToString() + "." + "'", conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                drows = new DataRows();
                treeView1.Nodes.Clear();
                TreeNode tnRoot = new TreeNode("Root");
                treeView1.Nodes.Add(tnRoot);
                FillTreeView("", tnRoot);
                SortNodes(null);
            }
            else
            {
                MessageBox.Show("Not found");
            }
        }

        private void treeView1_ItemDrag(object sender, ItemDragEventArgs e)
        {
            DoDragDrop(e.Item, DragDropEffects.Move);
        }

        private void treeView1_DragEnter(object sender, DragEventArgs e)
        {
            e.Effect = DragDropEffects.Move;
        }

        private class SymTreeNode:IComparable
        {
            public TreeNode tn;

            public int CompareTo(object obj)
            {
                return tn.Text.CompareTo(((SymTreeNode) obj).tn.Text);
            }
        }

        private void SortNodes(TreeNode tn)
        {
            if (tn == null)
            {
                ArrayList arlTreeNodes = new ArrayList();
                foreach (TreeNode tn1 in treeView1.Nodes)
                {
                    SortNodes(tn1);
                    SymTreeNode sn = new SymTreeNode();
                    sn.tn = tn1;
                    arlTreeNodes.Add(sn);
                }
                arlTreeNodes.Sort();
                treeView1.Nodes.Clear();
                foreach (SymTreeNode sn in arlTreeNodes)
                {
                    treeView1.Nodes.Add(sn.tn);
                }
            }
            else
            {
                ArrayList arlTreeNodes = new ArrayList();
                foreach (TreeNode tn1 in tn.Nodes)
                {
                    SortNodes(tn1);
                    SymTreeNode sn = new SymTreeNode();
                    sn.tn = tn1;
                    arlTreeNodes.Add(sn);
                }
                arlTreeNodes.Sort();
                tn.Nodes.Clear();
                foreach (SymTreeNode sn in arlTreeNodes)
                {
                    tn.Nodes.Add(sn.tn);
                }
            }
        }
    }
}

Code for AddNameForm (DataSet version)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Treeview
{
    public partial class AddNameForm : Form
    {
        public DataRow r = null;
        public TreeNode tn = null;

        public AddNameForm()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length > 0)
            {
                SqlConnection conn = new SqlConnection(
                                @"Server=Gandalf\SQL2K;" +
                                "Integrated Security=true;Database=treeview");
                conn.Open();
                SqlCommand cmd = new SqlCommand(
                    "insert into datatable (treekey, parentid, data) values("
                    + (r != null ? "'" + r["TreeKey"].ToString() + ".'
                    + " : "")
                    + "cast(ident_current('datatable') as varchar(10)), "
                    + (r != null ? r["ID"].ToString() : "0")
                    + ", '" + textBox1.Text
                    + "'); select id, treekey from datatable "
                    + "where id = ident_current('datatable')", conn);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    DataRow r1 = Form1.dt.NewRow();
                    r1["ID"] = dr["id"].ToString();
                    r1["ParentID"] = (r != null ? r["ID"].ToString() : "0");
                    r1["TreeKey"] = dr["treekey"].ToString();
                    r1["Data"] = textBox1.Text;
                    TreeNode tn2 = new TreeNode(r1["Data"].ToString() + 
                                        " [" + r1["TreeKey"].ToString() + "]");
                    tn2.Tag = r1;
                    tn.Nodes.Add(tn2);
                    this.Close();
                }
                dr.Close();
                conn.Close();
            }
            else
            {
                MessageBox.Show("Please enter some data.");
            }
        }
    }
}

Code for Form1 (Industrial Strength with DataReader Version)

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Treeview
{
    public partial class Form1 : Form
    {
        public static DataRows drows = new DataRows();

        public Form1()
        {
            InitializeComponent();
            TreeNode tn = new TreeNode("Root");
            treeView1.Nodes.Add(tn);
            FillTreeView("", tn);
            SortNodes(null);
        }

        public class DataRows
        {
            public class MyRow
            {
                public string ID;
                public string TreeKey;
                public string ParentID;
                public string Data;
            }

            private int counter = 0;
            private int lastfetched = -1;
            private SqlDataReader sdr;
            private MyRow currentrow = new MyRow();

            public DataRows()
            {
                SqlConnection conn = new SqlConnection(
                            @"Server=Gandalf\SQL2K;" + 
                            "Integrated Security=true;Database=treeview");
                SqlCommand cmd = new SqlCommand(
                            @"select * from datatable order by treekey", conn);
                conn.Open();
                sdr = cmd.ExecuteReader();
            }

            public MyRow GetNextRow()
            {
                if (counter > lastfetched)
                {
                    if (sdr.Read())
                    {
                        lastfetched = counter;
                        counter++;
                        currentrow = new MyRow();
                        currentrow.ID = sdr["ID"].ToString();
                        currentrow.ParentID = sdr["ParentID"].ToString();
                        currentrow.TreeKey = sdr["TreeKey"].ToString();
                        currentrow.Data = sdr["Data"].ToString();
                        return currentrow;
                    }
                    else
                    {
                        return null;
                    }
                }
                else if (counter == lastfetched)
                {
                    counter++;
                }

                return currentrow;
            }

            public void MovePrev() { --counter; }
        }

        private void FillTreeView(string currKeyRoot, TreeNode tn)
        {
            DataRows.MyRow row = drows.GetNextRow();
            while(row != null)
            {
                if(NodeStartsWith(row.TreeKey, currKeyRoot))
                {
                    TreeNode tnChild = new TreeNode(row.Data + "["
                                                   + row.TreeKey + "]");
                    tnChild.Tag = row;
                    tn.Nodes.Add(tnChild);
                    FillTreeView(row.TreeKey, tnChild);
                }
                else
                {
                    drows.MovePrev();
                    return;
                }
                row = drows.GetNextRow();
            }
        }

        private bool NodeStartsWith(string treekey, string currKeyRoot)
        {
            if (currKeyRoot == null || currKeyRoot.Length == 0 || 
                treekey.StartsWith(currKeyRoot + "."))
                return true;
            return false;
        }

        private void addNodeToolStripMenuItem_Click(object sender, 
                                                    EventArgs e)
        {
            TreeNode tn = treeView1.SelectedNode;
            if (tn != null)
            {
                DataRows.MyRow r = tn.Tag as DataRows.MyRow;
                AddNameForm frm = new AddNameForm();
                if (r != null)
                {
                    frm.r = r;
                }
                frm.tn = tn;
                frm.ShowDialog();
            }
        }

        private void deleteNodeToolStripMenuItem_Click(object sender, 
                                                       EventArgs e)
        {
            TreeNode tn = treeView1.SelectedNode;
            if (tn != null)
            {
                DataRows.MyRow r = tn.Tag as DataRows.MyRow;
                if (r != null)
                {
                    SqlConnection conn = new SqlConnection(
                                @"Server=Gandalf\SQL2K;" + 
                                "Integrated Security=true;Database=treeview");
                    conn.Open();
                    if (MessageBox.Show(
                         "Do you want to delete all child nodes?",
                         "Delete Node", MessageBoxButtons.YesNo)
                                                         == DialogResult.Yes)
                    {
                        SqlCommand cmd = new SqlCommand(
                            "delete datatable where treekey = '" + r.TreeKey
                            + "' or treekey like '" + r.TreeKey + ".%'", 
                            conn);
                        cmd.ExecuteNonQuery();
                        tn.Parent.Nodes.Remove(tn);
                    }
                    else
                    {
                        SqlCommand cmd = new SqlCommand(
                                      "delete datatable where treekey = '" + 
                                      r.TreeKey + "'", conn);
                        cmd.ExecuteNonQuery();
                        tn.Parent.Nodes.Remove(tn);
                    }
                    conn.Close();
                }
            }
        }

        private void treeView1_DragDrop(object sender, DragEventArgs e)
        {
            TreeNode tn = e.Data.GetData("System.Windows.Forms.TreeNode")
                                                          as TreeNode;
            TreeViewHitTestInfo hi = treeView1.HitTest(
                            treeView1.PointToClient(new Point(e.X, e.Y)));

            if (hi.Node != null && tn != null)
            {
                DataRows.MyRow rSrc = tn.Tag as DataRows.MyRow;
                DataRows.MyRow rDest = hi.Node.Tag as DataRows.MyRow;
                SqlConnection conn = new SqlConnection(
                                 @"Server=Gandalf\SQL2K;" +
                                 "Integrated Security=true;Database=treeview");
                conn.Open();
                int dashpos = rSrc.TreeKey.LastIndexOf('.');
                string subSrc = (dashpos > 0 ? rSrc.TreeKey.Substring(0,
                                 dashpos) : "");
                SqlCommand cmd = new SqlCommand(
                      "update datatable set treekey = '" + rDest.TreeKey
                      + "' + right(treekey, len(treekey) - len('"
                      + subSrc + "')) where left(treekey, len('"
                      + rSrc.TreeKey + "')) + '.' = '"
                      + rSrc.TreeKey + "." + "'", conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                drows = new DataRows();
                treeView1.Nodes.Clear();
                TreeNode tnRoot = new TreeNode("Root");
                treeView1.Nodes.Add(tnRoot);
                FillTreeView("", tnRoot);
                SortNodes(null);
            }
            else
            {
                MessageBox.Show("Not found");
            }
        }

        private void treeView1_ItemDrag(object sender, ItemDragEventArgs e)
        {
            DoDragDrop(e.Item, DragDropEffects.Move);
        }

        private void treeView1_DragEnter(object sender, DragEventArgs e)
        {
            e.Effect = DragDropEffects.Move;
        }

        private class SymTreeNode:IComparable
        {
            public TreeNode tn;

            public int CompareTo(object obj)
            {
                return tn.Text.CompareTo(((SymTreeNode) obj).tn.Text);
            }
        }

        private void SortNodes(TreeNode tn)
        {
            if (tn == null)
            {
                ArrayList arlTreeNodes = new ArrayList();
                foreach (TreeNode tn1 in treeView1.Nodes)
                {
                    SortNodes(tn1);
                    SymTreeNode sn = new SymTreeNode();
                    sn.tn = tn1;
                    arlTreeNodes.Add(sn);
                }
                arlTreeNodes.Sort();
                treeView1.Nodes.Clear();
                foreach (SymTreeNode sn in arlTreeNodes)
                {
                    treeView1.Nodes.Add(sn.tn);
                }
            }
            else
            {
                ArrayList arlTreeNodes = new ArrayList();
                foreach (TreeNode tn1 in tn.Nodes)
                {
                    SortNodes(tn1);
                    SymTreeNode sn = new SymTreeNode();
                    sn.tn = tn1;
                    arlTreeNodes.Add(sn);
                }
                arlTreeNodes.Sort();
                tn.Nodes.Clear();
                foreach (SymTreeNode sn in arlTreeNodes)
                {
                    tn.Nodes.Add(sn.tn);
                }
            }
        }
    }
}

Code for AddNameForm (Industrial Strength with DataReader Version)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Treeview
{
    public partial class AddNameForm : Form
    {
        public Form1.DataRows.MyRow r = null;
        public TreeNode tn = null;

        public AddNameForm()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length > 0)
            {
                SqlConnection conn = new SqlConnection(
                                @"Server=Gandalf\SQL2K;" +
                                Integrated Security=true;Database=treeview");
                conn.Open();
                SqlCommand cmd = new SqlCommand(
                    "insert into datatable (treekey, parentid, data) values("
                    + (r != null ? "'" + r.TreeKey + ".' + " : "")
                    + "cast(ident_current('datatable') as varchar(10)), " 
                    + (r != null ? r.ID : "0") + ", '" + textBox1.Text 
                    + "'); select id, treekey from datatable "
                    + "where id = ident_current('datatable')", conn);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    Form1.DataRows.MyRow r1 = new Form1.DataRows.MyRow();
                    r1.ID = dr["id"].ToString();
                    r1.ParentID = (r != null ? r.ID : "0");
                    r1.TreeKey = dr["treekey"].ToString();
                    r1.Data = textBox1.Text;
                    TreeNode tn2 = new TreeNode(r1.Data + " [" + r1.TreeKey
                                                  + "]");
                    tn2.Tag = r1;
                    tn.Nodes.Add(tn2);
                    this.Close();
                }
                dr.Close();
                conn.Close();
            }
            else
            {
                MessageBox.Show("Please enter some data.");
            }
        }
    }
}

SQL Script

--IF EXISTS (SELECT name FROM master.dbo.sysdatabases 
--WHERE name = N'treeview')
--    DROP DATABASE [treeview]
--GO

CREATE DATABASE [treeview]  ON (NAME = N'treeview_Data', 
       FILENAME = N'd:\MSSQL2K\MSSQL$SQL2K\data\treeview_Data.MDF' , 
       SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'treeview_Log', 
       FILENAME = N'd:\MSSQL2K\MSSQL$SQL2K\data\treeview_Log.LDF' , 
       SIZE = 1, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

use [treeview]
GO

if exists (select * from dbo.sysobjects where 
           id = object_id(N'[dbo].[DataTable]') 
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DataTable]
GO

if exists (select * from dbo.sysobjects where 
           id = object_id(N'[dbo].[LoadTableLaptopDiskSize]') 
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTableLaptopDiskSize]
GO

if exists (select * from dbo.sysobjects where 
           id = object_id(N'[dbo].[LoadTableLaptopMemory]') 
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTableLaptopMemory]
GO

if exists (select * from dbo.sysobjects where 
           id = object_id(N'[dbo].[LoadTableLaptopType]') 
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTableLaptopType]
GO

if exists (select * from dbo.sysobjects where 
           id = object_id(N'[dbo].[LoadTablePeopleNames]') 
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoadTablePeopleNames]
GO

CREATE TABLE [dbo].[DataTable] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [treekey] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [parentid] [int] NOT NULL ,
    [data] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LoadTableLaptopDiskSize] (
    [DiskSize] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LoadTableLaptopMemory] (
    [Memory] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LoadTableLaptopType] (
    [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LoadTablePeopleNames] (
    [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Modifications

• One of the comments was why copy the data, so I have eliminated this and you work directly with the resulset.
• There was a bug in which wrong nodes were matching, thanks to using the basic StartsWith of the String class. This has been eliminated, and a special function now handles checking if the node is a subnode of the current node.
• There was a problem with the way nodes were being dragged and dropped, and this was being done incorrectly and has been fixed now.
• Additional code for using the DataReader has been added for the industrial strength solution.
• Added sorting of the nodes as the method does not show any sorting as is.
• Showed how to display a branch of the tree with the same code.
• There was a problem with using -, as 1-1 was being ordered by SQL Server 2005 after 11. So I have switched to using a dot as the delimiter as 1.1 orders before 11. Please update your code if your current database has the same problem. Please remember, if dot does not work for you, search for another character that will order 1<some char>1 before 11.

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)