title: SQL Data Hierarchy
body: <p>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. </p>
  
  <p>This is how the hierarchy would work:</p>
  
  <pre>
  SQL TABLE
  
  ID     | Location ID | Name
  _______| __________  |_____________
  1331   | 1331        | House
  1321   | 1331        | Room
  2141   | 1321        | Bed
  1251   | 2231        | Gym
  
  </pre>
  
  <p>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.</p>
  
  <p>For the above example:</p>
  
  <pre>
  - House
     -- Room
         --- Bed
  </pre>
  
  <p>Any help or direction to easy to follow tutorials would be greatly appreciated.  </p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Code I have so far, but it only gets the Parent and Children, no GrandChildren. I can't seem to figure out how to get it to recursively get all of the nodes.</p>
  
  <pre><code>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["AssetWhereConnectionString1"].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 &lt; 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 &lt; myRows.Length; c++)
                  {
                      if ((Guid)myRows[p]["LocationID"] == (Guid)myRows[c]["LocationID"] 
                          &amp;&amp; (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();
      }
  
  }
  }
  </code></pre>
  
  <p>Here is a snippit from the actual SQL table: Locations</p>
  
  <pre>
  <b>ID</b>                                      <b>LocationID</b>                              <b>Name</b>
  ____________________________________    ____________________________________    ______________
  DEAF3FFF-FD33-4ECF-910B-1B07DF192074    48700BC6-D422-4B26-B123-31A7CB704B97    Drop F
  48700BC6-D422-4B26-B123-31A7CB704B97    7EBDF61C-3425-46DB-A4D5-686E91FD0832    Olway
  06B49351-6D18-4595-8228-356253CF45FF    6E8C65AC-CB22-42DA-89EB-D81C5ED0BBD0    Drop E 5
  E98BC1F6-4BAE-4022-86A5-43BBEE2BA6CD    DEAF3FFF-FD33-4ECF-910B-1B07DF192074    Drop F 6
  F6A2CF99-F708-4C61-8154-4C04A38ADDC6    7EBDF61C-3425-46DB-A4D5-686E91FD0832    Pree
  0EC89A67-D74A-4A3B-8E03-4E7AAAFEBE51    6E8C65AC-CB22-42DA-89EB-D81C5ED0BBD0    Drop E 4
  35540B7A-62F9-487F-B65B-4EA5F42AD88A    48700BC6-D422-4B26-B123-31A7CB704B97    Olway Breakdown
  5000AB9D-EB95-48E3-B5C0-547F5DA06FC6    6E8C65AC-CB22-42DA-89EB-D81C5ED0BBD0    Out 1
  53CDD540-19BC-4BC2-8612-5C0663B7FDA5    6E8C65AC-CB22-42DA-89EB-D81C5ED0BBD0    Drop E 3
  7EBDF61C-3425-46DB-A4D5-686E91FD0821    B46C7305-18B1-4499-9E1C-7B6FDE786CD6    TEST 1
  7EBDF61C-3425-46DB-A4D5-686E91FD0832    7EBDF61C-3425-46DB-A4D5-686E91FD0832    HMN
  </pre>
  
  <p>Thanks.</p>