转换一个平面数据库结果集到C＃分层对象集合 [英] convert a flat database resultset into hierarchical object collection in C#

问题描述

我有一个数据库查询返回的平板格式的分层数据。例如客户，订单和订单项目（只是举个例子，我的数据是不同的）。我怎样才能将其转换为分层对象集合，即客户对象，其中每个客户对象有秩序对象的集合，每个订单对象的集合有订单项目对象的集合。难道仅仅是通过每个项目循环和手动构建对象层次还是有更好的方式来实现这一目标的情况？我不使用LINQ。我从一个SQL Server数据库的存储过程中的数据。
编辑：我不使用LINQ检索数据库中的数据，但我可以用它来操纵检索一次把它转换成所需格式的数据，如果能解决问题

。编辑：样本数据看起来像这样（通过连接客户，订单和订单项目表中检索）（抱歉格式不好，我不知道我怎么可以在编辑器格式化这个）

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. 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.
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

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

推荐答案

第一件事是，结果集必须处于正确的顺序。
这意味着，客户有先来的订单，他们必须在结果之前的订单项目设置

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.

元代码：

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

这篇关于转换一个平面数据库结果集到C＃分层对象集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！