title: Read a sql hierarchy into a c# object
body: <p>How would you read the SQL data to get a hierarchical list of units?</p>
  
  <p>WITHOUT depending on a SQL Server only solution?</p>
  
  <pre><code>public class Unit {
      public Unit Parent { get; set; }
      public int Id { get; set; }
      public String Name { get; set; }
  }
  
  List&lt;Unit&gt; list = new List&lt;Unit&gt;();
  
  while(reader.Read())
  {
      // read sql data into clr object UNIT
  }
  </code></pre>
  
  <p>The table has 3 columns:</p>
  
  <pre><code>Id| ParentId | Name
  1 | Null     | bla
  2 |   1      | x
  3 |   1      | y
  4 |   2      | z
  5 |   2      | test
  </code></pre>
  
  <p><strong>UPDATE</strong>:</p>
  
  <pre><code>That is the code which is taken from user marc_s:
  
   List&lt;Unit&gt; units = new List&lt;Unit&gt;();
  
              String commandText =
              @";WITH Hierarchy AS
                (
                   SELECT
                      ID,  ParentID = CAST(NULL AS INT),
                      Name, HierLevel = 1
                   FROM
                      dbo.Unit
                   WHERE
                      ParentID IS NULL
  
                   UNION ALL
  
                   SELECT
                      ht.ID, ht.ParentID, ht.Name, h1.HierLevel + 1
                   FROM
                      dbo.Unit ht
                   INNER JOIN 
                      Hierarchy h1 ON ht.ParentID = h1.ID
                )
                SELECT Id, ParentId, Name
                FROM Hierarchy
                ORDER BY HierLevel, Id";
  
              using(SqlConnection con = new SqlConnection(_connectionString))
              using (SqlCommand cmd = new SqlCommand(commandText, con))
              {
                  con.Open();
  
                  // use SqlDataReader to iterate over results
                  using (SqlDataReader rdr = cmd.ExecuteReader())
                  {
                      while (rdr.Read())
                      {
                          // get the info from the reader into the "Unit" object
                          Unit thisUnit = new Unit();
  
                          thisUnit.Id = Convert.ToInt32(rdr["Id"]);
                          thisUnit.UnitName = rdr["Name"].ToString();                     
  
                          // check if we have a parent
                          if (rdr["ParentId"] != DBNull.Value)
                          {
                              // get ParentId
                              int parentId = Convert.ToInt32(rdr["ParentId"]);
  
                              // find parent in list of units already loaded
                              // NOTE =&gt; not needed anymore =&gt; Unit parent = units.FirstOrDefault(u =&gt; u.Id == parentId);
  
                              // Instead use this method to find the parent:
  
  
                              Unit parent = FindParentUnit(units, parentId);
  
                              // if parent found - set this unit's parent to that object
                              if (parent != null)
                              {
                                  thisUnit.Parent = parent;
                                  parent.Children.Add(thisUnit);
                              }
                          }
                         else
                         {
                             units.Add(thisUnit);
                         }
                      }
                  }
              }
  
              return units;
  </code></pre>
  
  <p>Thats a screenshot of the populated List</p>
  
  <p><a href="http://oi41.tinypic.com/rmpe8n.jpg" rel="nofollow">http://oi41.tinypic.com/rmpe8n.jpg</a></p>
  
  <p>Thats the sql data from the Unit table:</p>
  
  <p><a href="http://oi40.tinypic.com/mt12sh.jpg" rel="nofollow">http://oi40.tinypic.com/mt12sh.jpg</a></p>
  
  <p><strong>QUESTION</strong>:</p>
  
  <p>Actually the  populated List should have only ONE Unit object not 11 (Index 0 - 10). Yes the first Unit in the List is populated correctly, but the units Index 1 - 10 should not be in the list.</p>
  
  <p>This is how it should look like actually:</p>
  
  <pre><code>0
  |--1
  |   |--3
  |   |   |--9
  |   |   |--10  
  |   |--4
  |--2
  |   |--5
  |   |--6
  |--7
  |--8
  </code></pre>
  
  <p>UPDATE and SOLUTiON</p>
  
  <pre><code>private static Unit FindParentUnit(List&lt;Unit&gt; units, int parentId)
          {
              Unit parent;
              foreach (Unit u in units)
              {
                  if (u.Id == parentId){
                      return u;                                    
                  }
                  parent = FindParentUnit(u.Children, parentId);
                  if (parent != null)
                      return parent;
              }
              return null;
          } 
  </code></pre>