将sql层次结构读取到c#对象中

如何读取SQL数据以获得单元的分层列表？

不依赖于仅SQL Server的解决方案？

public class Unit {
    public Unit Parent { get; set; }
    public int Id { get; set; }
    public String Name { get; set; }
}
List<Unit> list = new List<Unit>();
while(reader.Read())
{
    // read sql data into clr object UNIT
}

该表有3列：

Id| ParentId | Name
1 | Null     | bla
2 |   1      | x
3 |   1      | y
4 |   2      | z
5 |   2      | test

更新：

That is the code which is taken from user marc_s:
 List<Unit> units = new List<Unit>();
            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 => not needed anymore => Unit parent = units.FirstOrDefault(u => 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;

这是已填充的列表的屏幕截图

http://oi41.tinypic.com/rmpe8n.jpg

这是Unit表中的sql数据：

http://oi40.tinypic.com/mt12sh.jpg

问题：

实际上，填充的List应该只有一个Unit对象，而不是11（索引0-10）。是的，列表中的第一个单元被正确填充，但索引1-10的单元不应该在列表中。

这就是实际情况：

0
|--1
|   |--3
|   |   |--9
|   |   |--10  
|   |--4
|--2
|   |--5
|   |--6
|--7
|--8

更新和SOLUTiON

private static Unit FindParentUnit(List<Unit> 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;
        } 

// set up connection string
string connectionString = "server=.;database=test;integrated Security=SSPI;";
// define a CTE (Common Table Expression) to recursively build your hierarchical
// structure into a flat list and order it according to its "sequence" (root first)
string cteStatement =
            @";WITH Hierarchy AS
              (
                 SELECT
                    ID,  ParentID = CAST(NULL AS INT),
                    Name, HierLevel = 1
                 FROM
                    dbo.HierarchyTest   -- replace with your table name!
                 WHERE
                    ParentID IS NULL
                 UNION ALL
                 SELECT
                    ht.ID, ht.ParentID, ht.Name, h1.HierLevel + 1
                 FROM
                    dbo.HierarchyTest ht   -- replace with your table name!
                 INNER JOIN 
                    Hierarchy h1 ON ht.ParentID = h1.ID
              )
              SELECT Id, ParentId, Name
              FROM Hierarchy
              ORDER BY HierLevel, Id";
// set up list of "Unit" objects
List<Unit> units = new List<Unit>();
// create connection and command to query             
using(SqlConnection conn = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand(cteStatement, conn))
{
    conn.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 = rdr.GetInt32(0);
            thisUnit.Name = rdr.GetString(2);
            thisUnit.Children = new List<Unit>();
            // check if we have a parent
            if(!rdr.IsDBNull(1))
            {
                // get ParentId
                int parentId = rdr.GetInt32(1);
                // find parent in list of units already loaded
                Unit parent = units.FirstOrDefault(u => u.Id == 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);
            }
        }
    }
    conn.Close();
}

Dictionary<Int32,Unit> dic = new Dictionary<Int32,Unit>();
while(reader.Read()) 
{ 
    //create the new Unit
    // if the parent is not null get the parent unit from dic
    // add the new Unit to dic
}