c# - 如何处理从 LEFT JOIN 查询中选择的数据?
问题描述
我想执行一个查询,选择所有客户,以及每个客户的相应地址。每个客户可以有零个、一个或多个地址。
这是我尝试过的:
sql = "SELECT customers.*, addresses.* FROM customers LEFT JOIN addresses ON addresses.CustomerId = customers.Id ORDER BY customers.Id OFFSET @start ROWS FETCH NEXT @end ROWS ONLY";
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@start", limitStart));
parameters.Add(new SqlParameter("@end", limitEnd));
DataSet set = Db.ExecuteSelectQuery(sql, parameters);
DataTable table = set.Tables["customers"];
Console.WriteLine("Tables count: " + set.Tables.Count); // says there's 1 table.
List<Customer> customers = new List<Customer>();
foreach(DataRow row in table.Rows)
{
Customer cust = new Customer();
cust.Id = row.Field<int>("Id");
cust.ClientNumber = row.Field<string>("clientnumber");
cust.Date = DateTime.Parse(row.Field<string>("date"));
cust.Firstname = row.Field<string>("firstname");
cust.Insertion = row.Field<string>("insertion");
cust.Lastname = row.Field<string>("lastname");
foreach(DataRow addrRow in /* ......?...... */) // Want to loop through addresses of the current customer row.
{
Address address = new Address();
address.Street = addrRow.Field<string>("Street");
address.HouseNumber = addrRow.Field<string>("Number");
address.PostalCode = addrRow.Field<string>("PostalCode");
address.City = addrRow.Field<string>("City");
address.Country = 0;// row.Field<int>("CountryCode");
cust.Addresses.Add(address);
}
customers.Add(cust);
}
return customers;
问题是我不知道如何使用DataTable
和的DataSet
类来处理查询结果SqlClient
。
我在互联网上找不到任何解释您可以访问每个客户的选定地址的方式。
有人可以解释一下,或者给我看一个代码示例吗?
解决方案
您应该过滤属于循环中当前客户的客户的地址
你甚至可以使用一个if
子句来cust.Id
比较row.Field<int>("CustomerId")
推荐阅读
- amazon-web-services - 无法从服务器获得任何响应
- python - 当我点击 Heroku Postgres 时出现此错误消息 {"error":{"id":"unauthorized","message":"Invalid credentials provided."}}
- javascript - angularjs - 组合来自 for 循环 api 调用的数据
- javascript - React 应用程序和用户角色
- visual-studio-code - Microsoft Visual Studio Code 无法启动
- angular - How to make ng build watching multiple apps?
- python - how to update column in a oracle table and commit after each record update by using python and cx_Oracle
- angular - PrimeNG 控件未触发的反应式表单
- c# - 无法从自定义 COM 库创建对象
- json - Flutter doesn't want to import json