C# 如何基于两个表过滤 TableAdapter 的 FillBy？

How do you filter a TableAdapter's FillBy based on two tables?

提问by user45191

I'm using VS2008 C# Express and the Northwind database on a Windows Form application.

我在 Windows 窗体应用程序上使用 VS2008 C# Express 和 Northwind 数据库。

I used drag and drop to set up the master details binding (I used the Orders and Order Details) for the two datagridviews. At this point, everything works as expected. So as not to return every row in the table, I want to filter the Orders table based on a filter for the Orders Table AND also on a field in the Orders Details table. In the TableAdapter Configuration Wizard, I used the query builder to add a new FillByMyFilter which created the following query:

我使用拖放来为两个数据网格视图设置主详细信息绑定（我使用了订单和订单详细信息）。此时，一切都按预期进行。为了不返回表中的每一行，我想根据订单表的过滤器以及订单详细信息表中的字段来过滤订单表。在 TableAdapter 配置向导中，我使用查询构建器添加了一个新的 FillByMyFilter，它创建了以下查询：

SELECT Orders.[Order ID], Orders.[Customer ID], Orders.[Employee ID], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship Region], Orders.[Ship Postal Code], Orders.[Ship Country], Orders.[Ship Via], Orders.[Order Date], Orders.[Required Date], Orders.[Shipped Date], Orders.Freight FROM Orders INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID] WHERE (Orders.[Ship Name] LIKE N'A%') AND ([Order Details].Quantity < 20)

SELECT Orders.[Order ID], Orders.[Customer ID], Orders.[Employee ID], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship Region], Orders.[Ship Postal Code], Orders.[Ship Country], Orders.[Ship Via], Orders.[Order Date], Orders.[Required Date], Orders.[Shipped Date], Orders.Freight FROM Orders INNER JOIN [订单详情] ON Orders.[Order ID] = [Order Details].[Order ID] WHERE (Orders.[Ship Name] LIKE N'A%') AND ([Order Details].Quantity < 20)

I got this by adding both tables but did not check any of the field boxes in the Order Details table so that it would only return the columns that were used in the original Fill query. I'm only trying to filter the DataSet in the master table at this point and not return a different number of columns. Child rows of the Order Details should still work like the default unfiltered result set.

我通过添加两个表得到了这个，但没有检查 Order Details 表中的任何字段框，因此它只会返回在原始 Fill 查询中使用的列。此时我只是尝试过滤主表中的数据集，而不是返回不同数量的列。Order Details 的子行应该仍然像默认的未过滤结果集一样工作。

Now the problem: When I click the Execute Query button it works fine. I get 53 rows from the above query rather than the 1078 using the default Fill created by the designer. It return the same columns as the original fill query. However, when I try and run the application I get the following constraint error:

现在的问题是：当我单击“执行查询”按钮时，它工作正常。我从上面的查询中得到了 53 行，而不是使用设计者创建的默认 Fill 得到的 1078 行。它返回与原始填充查询相同的列。但是，当我尝试运行该应用程序时，出现以下约束错误：

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

“未能启用约束。一行或多行包含违反非空、唯一或外键约束的值。”

What am I doing wrong?

我究竟做错了什么？

UPDATE: I think I'm getting the constraint error because of the INNER JOIN created by the Wizard. If I edit the query to use LEFT JOIN then the Wizard changes it back to INNER JOIN.

更新：我认为由于向导创建了 INNER JOIN，我收到了约束错误。如果我编辑查询以使用 LEFT JOIN，则向导将其更改回 INNER JOIN。

My question still stands as how to filter records in the Parent table (Orders) based on criteria from both the Parent and Child table. My next test is to try and use a stored proc but would like to know using just the TableAdapter custom FillBy method.

我的问题仍然是如何根据父表和子表中的条件过滤父表（订单）中的记录。我的下一个测试是尝试使用存储过程，但想知道只使用 TableAdapter 自定义 FillBy 方法。

Regards,

问候，

DeBug

调试

采纳答案by user45191

Thanks to all that posted an answer. Here is how I did it using the TableAdapter Wizard and the Northwind typed dataset.

感谢所有发布答案的人。下面是我使用 TableAdapter 向导和 Northwind 类型数据集的方法。

1) Right click the Parent table in the xsd designer to add or configure the query. 2) Click the "Next" button in the Wizard until you see the "Query Builder" button. Click the Query Builder button to get yourself in the query builder mode. 3) Right click and add the child table in the design pane. You should have both tables and the default constraint that connects them. 4) Click the column on the child table that you want to filter (this check mark will be removed later) in order to add it to the criteria pane so you can filter it. 5) Add the filter for the Parent and Child columns. In this example, I filtered the Ship Name LIKE 'A%' and the Order Quantity < 20.

1) 在 xsd 设计器中右键单击父表以添加或配置查询。2) 单击向导中的“下一步”按钮，直到看到“查询生成器”按钮。单击查询构建器按钮进入查询构建器模式。3) 右键单击​​并在设计窗格中添加子表。您应该同时拥有表和连接它们的默认约束。4) 单击要过滤的子表上的列（此复选标记稍后将被删除）以将其添加到条件窗格中，以便您可以对其进行过滤。5) 为父列和子列添加过滤器。在这个例子中，我过滤了 Ship Name LIKE 'A%' 和 Order Quantity < 20。

Note that at this point you can test your query by clicking the Execute Query button. Using the Northwind DB for SQL 2008 compact edition I get 53 rows returned. If you were to save it at this point it would fail at runtime because of the duplicate Primary Keys in the result set. So the next few steps will get rid of 'em.

请注意，此时您可以通过单击“执行查询”按钮来测试您的查询。使用 Northwind DB for SQL 2008 精简版，我返回了 53 行。如果此时保存它，它会在运行时失败，因为结果集中有重复的主键。所以接下来的几个步骤将摆脱它们。

6) In the criteria pane, uncheck the child table column that you added previously. The filter will remain and the same column will now be uncheck in the design pane as well. If you run the query you will still have 53 rows but without the child table column. 7) Right click the design pane and add the "Group By". At this point, when you execute this query, you should have no duplicates in the Order ID. I got exactly 29 rows returned. 8) Click OK and then the "Next" button until you save your new FillBy query. 9) Change your source code to use your new FillBy.

6) 在条件窗格中，取消选中您之前添加的子表列。过滤器将保留，同一列现在也将在设计窗格中取消选中。如果您运行查询，您仍将有 53 行，但没有子表列。7) 右键单击​​设计窗格并添加“分组依据”。此时，当您执行此查询时，订单 ID 中应该没有重复项。我正好返回了 29 行。8) 单击确定，然后单击“下一步”按钮，直到保存新的 FillBy 查询。9) 更改源代码以使用新的 FillBy。

When I ran the application, I got the filtered Parent table with the same 29 rows that the Execute Query button returned. The child table worked as expected and contained at least one child row that contained a quantity of < 20.

当我运行该应用程序时，我得到了过滤后的父表，其中的 29 行与“执行查询”按钮返回的行相同。子表按预期工作，并包含至少一个包含 < 20 数量的子行。

For a real world application, I think it would be better to use a stored proc or LINQ. But this problem had me scratching my head and so I "made it fit" just because it was a challenge (at least for me).

对于现实世界的应用程序，我认为使用存储过程或 LINQ 会更好。但是这个问题让我摸不着头脑，所以我“让它适合”只是因为它是一个挑战（至少对我来说）。

回答by Turnkey

This article contains some troubleshooting suggestions to pinpoint the exact row causing the problem:

本文包含一些故障排除建议，以查明导致问题的确切行：

DataSet hell - "Failed to enable constraints. One or more rows contain values...."

数据集地狱-“无法启用约束。一行或多行包含值......”

回答by Turnkey

Hope you got this answered but if not, give this a thought.

希望你能得到这个答案，但如果没有，请考虑一下。

In your dataset if the Order and OrderDetail DataTables have a relationship between them then it works somewhat like an FK constraint. So, there can be no records in the child table (OrderDetail) that do not have a cooresponding parent (Order) record. So what may be happening is when you refresh the Order DataTable, with the query you mention above, there are still child rows in the OrderDetail table that will have refrernces to Parent (Order) records that will no longer be there after the refresh. That said, if you refresh the Order DataTable you need to also refresh the OrderDetail data table, or remove the relationship between the two DataTables.

在您的数据集中，如果 Order 和 OrderDetail DataTables 之间存在关系，那么它的工作方式有点像 FK 约束。因此，子表 (OrderDetail) 中不能有没有相应父 (Order) 记录的记录。因此，可能会发生的情况是，当您刷新 Order DataTable 时，使用上面提到的查询，OrderDetail 表中仍有子行，这些子行将引用刷新后将不再存在的父（订单）记录。也就是说，如果您刷新 Order DataTable，您还需要刷新 OrderDetail 数据表，或者删除两个 DataTable 之间的关系。

Hope this helps...

希望这可以帮助...

回答by tbone

If you look in the Orders.Designer.cs (guessing, as I am working in VB), you will likely see a unique constraint defined on Orders (for the primary key).

如果您查看 Orders.Designer.cs（猜测，因为我在 VB 中工作），您可能会看到在 Orders 上定义的唯一约束（对于主键）。

I suspect the problem is, when you run your query, you are getting one or more individual Order who has > 1 OrderDetails.Quanity > 20....so, that Order will be returned twice in your resultset, violating the primary key.

我怀疑问题是，当您运行查询时，您得到一个或多个具有 > 1 OrderDetails.Quanity > 20 的单个订单......因此，该订单将在您的结果集中返回两次，违反了主键。

Try: SELECT * from orders where [Ship Name] LIKE '%whatever% AND OrderID in (select OrderID from OrderDetails where Quantity < 20)

尝试：SELECT * from orders where [Ship Name] LIKE '%whatever% AND OrderID in (select OrderID from OrderDetails where Quantity < 20)

This is likely a very inefficient way to do it, in oracle you would use EXISTS() instead of IN() but I don't know the sql server equivalent.

这可能是一种非常低效的方法，在 oracle 中您将使用 EXISTS() 而不是 IN() 但我不知道 sql server 等效项。