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

yes, I select data in one query, I don't want to make a separate database call for each parent to get the children — RKP, May 19 '11 at 08:30
So how do you differentiate between customer, order and order item? — BitKFu, May 19 '11 at 09:05

BitKFu · Accepted Answer · 2011-05-19T11:11:51.803

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);
}

I added sample data, hope it is clear now, the above solution isn't applicable for the sample data. Thanks. — RKP, May 19 '11 at 09:57
I adjusted the example. But remind: It's only Meta Code, so not compileable without a few changes. — BitKFu, May 19 '11 at 11:12

score 2 · Answer 2 · answered May 18 '11 at 17:31

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

score 0 · Answer 3 · answered May 18 '11 at 17:31

0

You don't say how you are using this (binding to controls that are aware of relationships, perhaps?), but if you are tied to using ADO.NET, put the results into separate tables within a DataSet, and add data relationships that define the relationships between the tables. It works pretty much the way that foreign key references would in a database, and controls that are relationship-aware will make use of these.

answered May 18 '11 at 17:31

Cylon Cat

7,111
2
25
33

I am not binding this data to any controls, this data is used by some other component which expects data as a collection of objects with parent child relationships. – RKP May 19 '11 at 08:32

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

3 Answers3

Linked