利用外键关系实现多表关联更新

窗体如下图：

上面DataGridView1数据为Northwind中的Orders表

下面DataGridView2数据为Northwind中的Order Details表

两表的外键为：Orders.OrdersID和Order Details.OrderID

目标：实现点击Button1按钮，关联更新两个表。

窗体代码：

Public Class Form1    Dim bs_orders As New BindingSource    Dim da_orders As New SqlClient.SqlDataAdapter    Dim bs_ordersdetails As New BindingSource    Dim da_ordersdetails As New SqlClient.SqlDataAdapter    Dim ds As New DataSet    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load        da_orders = New SqlClient.SqlDataAdapter("Select * from Orders", cn)        da_orders.Fill(ds, "[orders]")        bs_orders.DataSource = ds        Dim scb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(da_orders)        '用SqlCommandBuilder为SqlDataAdapter生成用于添加、删除、更新的Command        da_orders.InsertCommand = scb.GetInsertCommand()        da_orders.UpdateCommand = scb.GetUpdateCommand()        da_orders.DeleteCommand = scb.GetDeleteCommand()        '绑定DS中的[Orders]表        bs_orders.DataMember = "[orders]"        da_ordersdetails = New SqlClient.SqlDataAdapter("select * from [Order Details]", cn)        da_ordersdetails.Fill(ds, "[Order Details]")        Dim scb2 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(da_ordersdetails)        '用SqlCommandBuilder为SqlDataAdapter生成用于添加、删除、更新的Command        da_ordersdetails.InsertCommand = scb2.GetInsertCommand()        da_ordersdetails.UpdateCommand = scb2.GetUpdateCommand()        da_ordersdetails.DeleteCommand = scb2.GetDeleteCommand()        '创建关系，利用[Orders]中的[OrdersID]键与[Order Details]中的[OrderID]键相关联，创建外键关系        Dim relation As DataRelation = New DataRelation("FK_ORDERS_ORDER_DETAILS", ds.Tables("[Orders]").Columns("OrderID"), ds.Tables("[Order Details]").Columns("OrderID"))        ds.Relations.Add(relation)        bs_ordersdetails.DataSource = bs_orders        '根据关系进行筛选关联        bs_ordersdetails.DataMember = "FK_ORDERS_ORDER_DETAILS"        DataGridView1.DataSource = bs_orders        DataGridView2.DataSource = bs_ordersdetails    End Sub    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        If ds.HasChanges Then            da_ordersdetails.Update(ds, "[Order Details]")            da_orders.Update(ds, "[Orders]")            ds.AcceptChanges()        End If    End SubEnd Class

对数据修改后如图：

点击保存按钮，进入数据库查看两张表更新后数据，效果如下：

由此实现了两张关系表的关联更新！