如何将列表添加到<B>对象 A，其中列表<B>是类 A 的一部分

Question

我有两类客户和产品，如下所示。我正在从数据库中获取数据，并将其读取到SqlDataReader中。从那以后，我必须把它读到客户对象中。我们将为每个客户提供多个产品。在这里，我必须将产品对象添加到客户对象中(我们可能为每个客户提供多个产品。有什么建议吗？最好的方法是什么？

public class Customer
{
public int CustomerId {get;set;}
public string Name {get;set;}
public List<Products> _products {get;set;}      
}
public class Products
{
public int CustomerId {get;set;}
public int ProductId {get;set;}
public string Name {get;set;}
public int Quantity {get;set;} 
}

While(dataReader.Read())
{
var _customer = new Customer{
CustomerId = (int)rdr["CustomerId"];
Name = (string)rdr["CustomerName"];
City    = (string)rdr["City"];
};
var _product = new Products
{
CustomerId = (int)rdr["CustomerId"];
ProductId = (int)rdr["ProductId"];
Name = (string)rdr["ProductName"];
Quantity = (int)["Quantity"];
};
}

Answer 1

正如评论中所提到的，您需要通过使用CustomerId作为关键字将客户放入字典来跟踪您已经见过的客户。以下是基本方法：

对于您读取的每条记录，首先从读取器获取CustomerId，并检查该客户是否已经在字典中。如果是，那么从字典中获取客户对象；否则，从阅读器中创建一个新客户并将其添加到字典中。然后，从阅读器中获取产品数据，创建一个新产品，并将该产品添加到客户的产品列表中。

以下是它在代码中的样子：

var customersById = new Dictionary<int, Customer>();
while (reader.Read())
{
int customerId = (int)reader["CustomerId"];
Customer customer;
if (!customersById.TryGetValue(customerId, out customer))
{
customer = new Customer
{
CustomerId = customerId,
Name = (string)reader["CustomerName"],
City = (string)reader["City"],
Products = new List<Product>()
};
customersById.Add(customerId, customer);
}
Product product = new Product
{
CustomerId = customerId,
ProductId = (int)reader["ProductId"],
Name = (string)reader["ProductName"],
Quantity = (int)reader["Quantity"]
};
customer.Products.Add(product);
}

然后，你可以像这样转储数据：

Console.WriteLine("Product list by customer:n");
foreach (Customer cust in customersById.Values)
{
Console.WriteLine(string.Format("{0}) {1} of {2}", cust.CustomerId, cust.Name, cust.City));
foreach (Product prod in cust.Products)
{
Console.WriteLine(string.Format("t{0}) {1} (qty {2})", prod.ProductId, prod.Name, prod.Quantity));
}
Console.Writeline();
}

Fiddle：https://dotnetfiddle.net/iO9vdM

Answer 2

public List<Customer> GetCustomers(SqlConnection conn) {
using (conn);
conn.Open();
SqlCommand command = new SqlCommand("your_query;",conn);
// your query must return in order
// customerId, customerName, city, productId, productName, quantity
SqlDataReader reader = command.ExecuteReader();
var customers = new List<Customer>();
var products = new List<Product>();
while (reader.Read()) {  
var customerId = reader.GetInt32(0);
var customer = new Customer() {
CustomerId = customerId,
Name = reader.GetString(1),
City    = reader.GetString(2)
};
var existing = customers.Where(x => x.CustomerId == customerId).FirstOrDefault();
if (existing == null) {
customers.Add(customer);
}
var product = new Products
{
CustomerId = customerId,
ProductId = reader.GetInt32(3),
Name = reader.GetString(4),
Quantity = reader.GetInt32(5)
};
products.Add(product);
}
reader.Close();
return customers.ForEach(item => {
item.Products = products.Where(x => x.customerId == item.CustomerId).ToList();
});
}
}

如何使用DataReader