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
– RKP, Commented May 19, 2011 at 8:30
So how do you differentiate between customer, order and order item? — BitKFu
– BitKFu, Commented May 19, 2011 at 9:05

BitKFu · Accepted Answer · 2011-05-19 11:11:51Z

3

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

edited May 19, 2011 at 11:11

answered May 19, 2011 at 9:11

BitKFu

3,7073 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RKP Over a year ago

I added sample data, hope it is clear now, the above solution isn't applicable for the sample data. Thanks.

BitKFu Over a year ago

I adjusted the example. But remind: It's only Meta Code, so not compileable without a few changes.

Chris Marisic · Accepted Answer · 2011-05-18 17:31:05Z

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

answered May 18, 2011 at 17:31

Chris Marisic

33.2k30 gold badges169 silver badges265 bronze badges

Comments

Cylon Cat · Accepted Answer · 2011-05-18 17:31:30Z

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, 2011 at 17:31

Cylon Cat

7,2192 gold badges27 silver badges34 bronze badges

1 Comment

RKP Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related