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

窗体如下图：

上面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 Sub
End Class

对数据修改后如图：

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

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