Best practice to fill an object hierarchy from a flat database resultset?

Answer 1

How about something like this? (Pseudo-code)

class HierarchicalObject
{
    int ID;
    List<HierarchicalObject> Children;
    // Other fields
}

...

var roots = new List<HierarchicalObject>();
var index = new Dictionary<int, HierarchicalObject>();

while (reader.Read())
{
    HierarchicalObject current = new HierarchicalObject();

    // fill in data from record

    int parentID = (int)reader["ParentID"];

    if (parentID != 0)
    {
        index[parentID].Children.Add(current);
    }
    else
    {
        roots.Add(current);
    }

    index.Add(current.ID, current);   
}

// roots now contains list of root objects; throw out index

This assumes all of your objects are homogeneous, rather than having separate parent/child classes, and also that the ordering is such that you won't read in a child before you read in its parent... but it should work for multiple levels, and you can adapt it as required.

(If the second assumption is not true, you can read all records into the index first, then go back through the index and fix up roots and children.)