convert a flat database resultset into hierarchical object collection in C#

Question

I have a database query which returns the hierarchical data in flat format. for example customers, orders and order items (just an example, my data is different). how can I convert it into hierarchical object collection i.e. collection of customer objects where each customer object has a collection of order objects and each order object has a collection of order item objects. Is it just a case of looping through each item and building the object hierarchy manually OR is there a better way to achieve this? I am NOT using LINQ. I get the data from a SQL server database stored procedure. Edit: I am not using LINQ to retrieve the data from the database, but I can use it to manipulate the data once retrieved to convert it into required format, if that solves the problem.
Edit: sample data looks like this (retrieved by joining customer, order and order item tables) (sorry for poor formatting, I don't know how can I format this in editor)

CustId CustName OrderId OrderName OrderItemId OrderItemName
C1 Cust1 O1 Order1 OI1 OrderItem1
C1 Cust1 O1 Order1 OI2 OrderItem2
C1 Cust1 O2 Order2 OI3 OrderItem3
C1 Cust1 O2 Order2 OI4 OrderItem4
C2 Cust2 O3 Order3 OI5 OrderItem5
C2 Cust2 O3 Order3 OI6 OrderItem6
C2 Cust2 O4 Order4 OI7 OrderItem7
C2 Cust2 O4 Order4 OI8 OrderItem8

Answer 1

First thing is that the result set has to be in the correct ordering. That means, that the customer has to come prior the orders and they have to be in the result set prior the order items.

Sorting: customer -> orders -> order items

With this knowledge you only have to iterate through the collection one time.

Meta Code:

foreach (item in resultset)
{
  // Build a customer and order dictionary
  if (!CustomerDictionary.Contains(item.Customer.Id)
     CustomerDictionary.Add(item.Customer.Id, item.Customer)

  if (!OrderDictionary.Contains(item.Order.Id)
     OrderDictionary.Add(item.Order.id, item.Order)

  // Now add the association      
  var customer = CustomerDictinoary[item.Customer.Id];
  customer.AddOrder(item.Order);

  var order = OrderDictinoary[item.Order.id];
  order.AddOrderItem(item.OrderItem);
}

Answer 2

I would recommend using a mapping tool, specifically ValueInjecter. ValueInjecter supports unflattening data.

Your usage of ValueInjecter will be similar to

var unflat = new Foo();
unflat.InjectFrom<UnflatLoopValueInjection>(flat);

Full details of this unflattening example