如何根据两个表过滤 TableAdapter 的 FillBy？
我在 Windows 窗体应用程序上使用 VS2008 C# Express 和 Northwind 数据库。
我使用拖放操作为两个 datagridview 设置主详细信息绑定（我使用了订单和订单详细信息）。 至此，一切都按预期进行。 为了不返回表中的每一行，我想根据订单表的过滤器以及订单详细信息表中的字段来过滤订单表。 在 TableAdapter 配置向导中，我使用查询生成器添加了一个新的 FillByMyFilter，它创建了以下查询：
SELECT Orders.[Order ID]、Orders.[Customer ID]、Orders.[Employee ID]、Orders.[Ship Name] , 订单.[发货地址], 订单.[发货城市], 订单.[发货地区], 订单.[发货邮政编码]、订单.[发货国家/地区]、订单.[发货方式]、订单.[订单日期]、订单.[必填日期]、订单.[发货日期]、 订单.运费 FROM 订单 INNER JOIN [订单详细信息] ON 订单。[订单 ID] = [订单详细信息].[订单 ID] WHERE (Orders.[Ship Name] LIKE N'A%') AND ([Order Details].Quantity < 20)
我通过添加两个表得到了这个，但没有检查“订单详细信息”表中的任何字段框，以便它只会返回原始填充查询中使用的列。 此时我只是尝试过滤主表中的数据集，而不返回不同数量的列。 订单详细信息的子行仍应像默认的未过滤结果集一样工作。
现在的问题是：当我单击“执行查询”按钮时，它工作正常。 我从上面的查询中获得了 53 行，而不是使用设计者创建的默认填充的 1078 行。 它返回与原始填充查询相同的列。 但是，当我尝试运行该应用程序时，出现以下约束错误：
“无法启用约束。一行或多行包含违反非空、唯一或外键约束的值。”
我究竟做错了什么？
更新：我认为由于向导创建的 INNER JOIN 而出现约束错误。 如果我编辑查询以使用 LEFT JOIN，则向导会将其更改回 INNER JOIN。
我的问题仍然是如何根据父表和子表中的条件过滤父表（订单）中的记录。 我的下一个测试是尝试使用存储过程，但想知道仅使用 TableAdapter 自定义 FillBy 方法。
问候，
调试
I'm using VS2008 C# Express and the Northwind database on a Windows Form application.
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:
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)
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.
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:
"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.
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.
Regards,
DeBug
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
感谢所有发布答案的人。 以下是我使用 TableAdapter 向导和 Northwind 类型数据集完成此操作的方法。
1) 右键单击xsd设计器中的父表以添加或配置查询。
2) 单击向导中的“下一步”按钮，直到看到“查询生成器”按钮。 单击“查询生成器”按钮即可进入查询生成器模式。
3) 右键单击并在设计窗格中添加子表。 您应该拥有两个表以及连接它们的默认约束。
4) 单击子表上要过滤的列（此复选标记稍后将被删除），以便将其添加到条件窗格中，以便您可以对其进行过滤。
5) 添加父列和子列的过滤器。 在此示例中，我过滤了船名 LIKE 'A%' 和订单数量 < 20.
请注意，此时您可以通过单击“执行查询”按钮来测试您的查询。 使用 Northwind DB for SQL 2008 精简版，我返回了 53 行。 如果此时保存它，它将在运行时失败，因为结果集中存在重复的主键。 所以接下来的几个步骤将摆脱它们。
6) 在条件窗格中，取消选中您之前添加的子表列。 过滤器将保留，并且同一列现在也将在设计窗格中取消选中。 如果运行查询，您仍将有 53 行，但没有子表列。
7) 右键单击设计窗格并添加“分组依据”。 此时，当您执行此查询时，订单 ID 中不应有重复项。 我正好返回了 29 行。
8) 单击“确定”，然后单击“下一步”按钮，直到保存新的 FillBy 查询。
9) 更改源代码以使用新的 FillBy。
当我运行该应用程序时，我得到了过滤后的父表，其中包含与“执行查询”按钮返回的相同的 29 行。 子表按预期工作，并且至少包含一个子行，其中子行的数量 << 20.
对于现实世界的应用程序，我认为使用存储过程或 LINQ 会更好。 但这个问题让我摸不着头脑，所以我“让它适合”只是因为它是一个挑战（至少对我来说）。
Thanks to all that posted an answer. Here is how I did it using the TableAdapter Wizard and the Northwind typed dataset.
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.
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.
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.
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.
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).
希望您能得到这个答案，但如果没有，请考虑一下。
在您的数据集中，如果 Order 和 OrderDetail 数据表之间存在关系，那么它的工作方式有点像 FK 约束。 因此，子表 (OrderDetail) 中不可能有没有对应父 (Order) 记录的记录。 因此，可能发生的情况是，当您刷新 Order DataTable 时，使用上面提到的查询，OrderDetail 表中仍然有子行，这些子行将引用刷新后将不再存在的父（订单）记录。 也就是说，如果刷新 Order DataTable，则还需要刷新 OrderDetail 数据表，或者删除两个 DataTable 之间的关系。
希望这可以帮助...
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.
Hope this helps...
本文包含一些故障排除建议，可查明导致问题的确切行：
数据集地狱 - “无法启用约束。一个或多个行包含值......”
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...."
如果您查看 Orders.Designer.cs（猜测，因为我正在使用 VB 工作），您可能会看到在 Orders 上定义的唯一约束（对于主键）。
我怀疑问题是，当您运行查询时，您会得到一个或多个具有 > 的单个订单。 1 订单详情.数量> 20 ....所以，该订单将在结果集中返回两次，违反了主键。
尝试：
SELECT * from orders where [Ship Name] LIKE '%whatever% AND OrderID in (select OrderID from OrderDetails where Quantity < 20)
这可能是一种非常低效的方法，在 Oracle 中，您将使用 EXISTS() 而不是 IN () 但我不知道 sql server 的等价物。
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).
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.
Try:
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.