级联删除不会保留到我的类型化数据集中的数据库中
当在两个表（例如父表和子表）之间建立数据关系时，ADO.Net 中的类型化数据集应该能够级联删除和更新。但是我无法让它发挥作用。我应该澄清一下：它在内存中对我有用；但它不会持久保存到数据库中。
我在数据库中设置了一个测试项目，其中包含两个表：颜色和盒子。每个都有一个 ID 主键和一个名称（例如“BoxId、BoxName”）。 Colors 是父项，Boxes 也有一个用外键约束标记的 ColorId 字段。然后，我设置了一个 Winform，上面有两个 DataGridView，每个表一个。我将这两个表拖到一个新的数据集中，它们的关系出现在设计器中。我将关系编辑为“外键和关系”，并将所有 3 个参照完整性选项设置为“级联”（即 DeleteRule、UpdateRule、AcceptRejectChanges）。我还为 Boxes 表创建了一个删除命令，因为由于某种原因设计者拒绝这样做。最后，我在每个网格旁边放置一个用于保存的按钮，并在每个网格的事件处理程序中放置一个 Update() 调用（例如 this.boxesTableAdapter.Update(this.pOCDataSet.Boxes);
）我运行表单和 SQL Profiler，看到所有数据，并且可以毫无问题地删除子项（框）中的项目。这些删除也会在数据库中生成 DELETE。非常好。
当我在顶部网格（颜色）中进行删除时，我删除的行消失了，并且框网格中的行也消失了（假设我没有检查数据库中的 FK 约束）。这看起来很棒。然而，在探查器中，显然只运行了 Colors DELETE 语句。 Boxes 行是孤立的。除非重新启动应用程序，否则您不会在 Winform 上看到这一点，此时 Boxes 中将要删除的行的左侧会出现一个红色感叹号图标，表明它们违反了 FK 约束。
这很令人抓狂，因为看起来我唯一的选择就是自己编写引用完整性的东西，例如使用存储过程来执行颜色删除（在其中我也会删除框），或者在删除时订购SQL Server 上的 CASCADE，甚至将内联框删除放入颜色删除语句中。我不断回到这样一个事实：我的模型中的 DataRelation 对象应该为我处理这个问题。显然，BindingSource 对象得到了它，因为它们正在从子网格中删除相关行。但为什么模型不首先对 Boxes 表执行删除，然后对 Colors 表执行删除呢？
Typed datasets in ADO.Net are supposed to be able to cascade deletes and updates when a DataRelation is set up between two tables, say a parent and child. However I have not been able to get this to work. I should clarify: It works for me in-memory; but it does not persist into the database.
I set up a test project with two tables in my database: Colors and Boxes. Each has an ID primary key and a name (e.g. "BoxId, BoxName"). Colors is the parent and Boxes, which also has a ColorId field marked with a Foreign Key constraint. I then set up a Winform with two DataGridViews on it, one for each table. I dragged the two tables into a new Dataset, and their relationship appeared in the designer. I edited the relationship to be "Both Foreign Key and Relation," and I set all 3 referential integrity options to "Cascade" (i.e. DeleteRule, UpdateRule, AcceptRejectChanges). I also created a DeleteCommand for the Boxes table, because for some reason the designer refused to do so. Finally, I put a button next to each grid for saving, and put an Update() call in the event handler for each (e.g. this.boxesTableAdapter.Update(this.pOCDataSet.Boxes);)
When I run the form and SQL Profiler, I see all my data, and I can delete items in the child (Boxes) no problem. These deletes generate a DELETE in the database, as well. Excelente.
When I do a delete in the top grid (Colors), the row I deleted disappears, and the row(s) in the Boxes grid also disappear (assuming I NOCHECK the FK constraint in the database). This looks great. However, in the profiler, clearly only the Colors DELETE statement ran. The Boxes row(s) are orphaned. You don't see this on the Winform unless your restart the app, at which point the would-be deleted rows in Boxes appear with a red exclamation icon to their left, indicating they are violating the FK constraint.
This is maddening because it sure seems like my only option here is to code the referential integrity stuff myself, a la using a stored proc to perform a Colors delete (inside of which I'd also do the Boxes delete), or ordering ON DELETE CASCADE on the SQL Server, or even putting inline Boxes deletes in the Colors delete statement. I keep coming back to the fact that the DataRelation object in my model is supposed to handle this for me. Clearly the BindingSource objects get it, since they're removing the relevant rows from the child grid. But why isn't the model first performing Deletes against the Boxes table and then the Colors table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
删除数据集中的行只会将它们标记为在数据集中删除；您仍然必须为数据库中要从中删除数据的每个表使用表适配器。您可以将数据集视为内存中数据库的版本。这些更改需要以正确的顺序提交到数据库，以防止 FK 错误。在您的情况下，您需要在子表（框）的所有删除都已提交后运行父表（颜色）的更新语句，如下所示...
希望这有帮助。
Deleting rows in a dataset will only mark them for deletion in your dataset; you still have to use a table adapter for every table in the DB you want to delete data from. You can think of the dataset as your version of the database in memory. Those changes need to be committed to the database in the correct order to prevent FK errors. In your case you need to run the update statement for the parent table (colors) AFTER all the deletes for the children (boxes) have been committed like so...
Hope this helps.