title: Is there a better way to populate hierarchical data from SQL into a class structure, C# or VB.NET?
body: <p>I'm trying to populate hierarchical data in a .NET 2.0 (yes, 2.0) application and upgrading right now is off the table (so no LINQ, LINQ Bridge, or other things).  </p>
  
  <p>I was wondering if there is a better way to populate hierarchical data into this class structure?  I'm pretty sure there is a far better way for this to be accomplished.</p>
  
  <p>It would be really nice to see a good way to do this.  If anyone has the time to show a .NET 2.0 way and if there is a different way they would do it in .NET 4.0+ that would be great.</p>
  
  <p>Here is an example of the node type structure:</p>
  
  <pre><code>using System.Collections.Generic;
  
  public class ExampleNode
  {
  
  private int _id;
  
  private Nullable&lt;int&gt; _parentId;
  
  
  private int _depth;
  
  private List&lt;ExampleNode&gt; _children = new List&lt;ExampleNode&gt;();
  
  public ExampleNode()
  {
  }
  
  public virtual int ApplicationNumber {
      get { return _id; }
      set { _id = value; }
  }
  
  public virtual Nullable&lt;int&gt; ParentId {
      get { return _parentId; }
      set { _parentId = value; }
  }
  
  
  public virtual int Depth {
      get { return _depth; }
      set { _depth = value; }
  }
  
  
  public virtual List&lt;ExampleNode&gt; Children {
      get { return _children; }
      set { _children = value; }
  }
  }
  </code></pre>
  
  <p>Here is an example function that is being utilized to populate the node structure.  It seems like it is not the best way to do this and it has the potential not to populate grandchildren type data.  Depth comes back from the stored proc as the level in the hierarchy (items with a level of 0 are top level, if a node is the child of a top level node it is at level 1, a grandchild of a top level node is level 2, etc.)</p>
  
  <pre><code>public List&lt;ExampleNode&gt; GetNodes()
  {
  // This may not be optimal.
  
  List&lt;ExampleNode&gt; nodeList = new List&lt;ExampleNode&gt;();
  Dictionary&lt;int, ExampleNode&gt; nodeDictionary = new Dictionary&lt;int, ExampleNode&gt;();
  
  using (SqlDataReader reader = SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings("SqlServer").ConnectionString, CommandType.StoredProcedure, "proc_GetNodeStructure", new SqlParameter("@UserId", userId), new SqlParameter("@NodeTypeId", nodeType))) {
      while (reader.Read) {
          ExampleNode nodeInstance = new ExampleNode();
  
          nodeInstance.Id = Convert.ToInt32(reader("Id"));
          nodeInstance.Depth = Convert.ToInt32(reader("Depth"));
  
  
          if (!Information.IsDBNull(reader("ParentId"))) {
              nodeInstance.ParentId = Convert.ToInt64(reader("ParentId"));
          }
  
          // Add to list
          nodeList.Add(nodeInstance);
  
          // Add to dictionary
          nodeDictionary.Add(nodeInstance.Id, nodeInstance);
  
      }
  }
  
  foreach (ExampleNode item in nodeList) {
      if (item.ParentId.HasValue) {
          nodeDictionary(item.ParentId).Children.Add(item);
      }
  
  }
  
  for (int i = nodeList.Count - 1; i &gt;= 0; i += -1) {
      if (nodeList(i).Depth &gt; 0) {
          nodeList.RemoveAt(i);
      }
  }
  
  return nodeList;
  }
  </code></pre>