如何用一个数据集更新两个表

Public Shared Function GetRecords(ByRef CNobj As System.Data.SqlClient.SqlConnection,
                                   ByRef RSobj As System.Data.DataSet,
                                   ByVal SQLquery As String,
                                   ByVal TableName() As String,
                                   Optional ByVal PrimKeys() As String = Nothing,
                                   Optional ByRef adapter As SqlDataAdapter = Nothing) As Integer
    On Error Resume Next
    Dim DataOkay As Integer = 0                             '[Set to negative if data bad]
    Dim suppliersAdapter As SqlDataAdapter = New SqlDataAdapter()
    'Create connection object, won't create if already exists
    If ConnectToDB(CNobj, False) = False Then
        'error creating object, Session("ErrorMsg") set by called code
        Return -1
    End If
    ' Open the connection.
    If CNobj.State = ConnectionState.Closed Then
        CNobj.Open()
    End If

    ' Create a SqlCommand to retrieve Suppliers data.
    Dim suppliersCommand As SqlCommand = New SqlCommand(SQLquery, CNobj)
    suppliersCommand.CommandType = CommandType.Text
    ' Set the SqlDataAdapter's SelectCommand.
    suppliersAdapter.SelectCommand = suppliersCommand

    ' Fill the DataSet.
    RSobj = New DataSet()
    suppliersAdapter.Fill(RSobj)
    If (Err.Number <> 0) Then
        DataOkay = -1
        _contexts.Session("ErrorMsg") = "Error reading records: " +
            DelDoubleQuotesInString(Err.Description)
        Call Err.Clear()
    ElseIf (RSobj.Tables.Count = 0) Then
        DataOkay = -2
        _contexts.Session("ErrorMsg") = "No tables read reading records for sql=" + SQLquery
    Else
        ' A table mapping names the DataTables.
        Dim IDX As Integer
        For IDX = 0 To TableName.Count - 1
            RSobj.Tables(IDX).TableName = TableName(IDX)
            If PrimKeys IsNot Nothing Then
                Dim primstr = PrimKeys(IDX)
                Dim primarr = Split(primstr, ",")
                Dim primcol(primarr.Count) As DataColumn
                Dim pidx As Integer
                For pidx = 0 To primarr.Count
                    primcol(pidx) = RSobj.Tables(IDX).Columns.Item(primarr(pidx))
                Next
                RSobj.Tables(IDX).PrimaryKey = primcol
            End If
        Next
        adapter = suppliersAdapter
    End If
    Return DataOkay
End Function
  Public Shared Function UpdateDB(ByRef dset As System.Data.DataSet, ByVal adapter As SqlDataAdapter)
    If dset.HasChanges = False Then
        Return True
    End If
    ' has multiple tables, select's separated by ;
    Dim SelectQry As String = adapter.SelectCommand.CommandText
    Dim commands() As String = Split(SelectQry, ";")
    Dim idx As Integer = 0
    For Each Table As System.Data.DataTable In dset.Tables
        'Connection object is global in shared ASP.NET module
        If CNobj.State = ConnectionState.Closed Then
            CNobj.Open()
        End If
        'Change select for table being updated.
        adapter.SelectCommand.CommandText = commands(idx)

        Dim cb As SqlCommandBuilder
        cb = New SqlCommandBuilder(adapter)
        adapter.InsertCommand = cb.GetInsertCommand(True)
        adapter.UpdateCommand = cb.GetUpdateCommand(True)
        adapter.DeleteCommand = cb.GetDeleteCommand(True)
        Try
            adapter.AcceptChangesDuringUpdate = True
            'force order delete, update, insert
            adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Deleted))
            Table.AcceptChanges() 'Have to do after delete!
            adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
            adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Added))
        Catch
            HttpContext.Current.Session("ErrorMsg") = "Error saving data to DB: " + Err.Description
            adapter.SelectCommand.CommandText = SelectQry
            Return False
        End Try
        idx += 1
        cb.Dispose()
    Next
    'Put original select back for next call
    adapter.SelectCommand.CommandText = SelectQry
    Return True
End Function

我包括了我正在使用的两个例程。如果使用带分号的select调用，第一个例程将获得多个表。我传入一组表名来分配给每个表。如果数据集要更新到服务器，那么我还将主键和对适配器的引用传递给更新调用。

我知道它在VB中，而不是C#中，但我正在转换为ASP.NET的经典ASP已经在VB中了，我不想在它上面移植到C#。

更新代码为每个表的每个更新调用拆分选择字符串。

错误-以上代码不适用于所有表。对于一个表，如果我不接受更改，它就不会更新-获取并发错误。在另一个表中，它表示在调用acceptchanges之前，我已经修改了13行，但在调用之后，这一行变为零，因此没有更新任何记录。我的猜测是，为一个出现并发错误的表生成的更新SQL是垃圾，不应该调用acceptchanges。当我解决这个问题时，我会更新帖子。

也许您可以使用事务？

SqlTransaction tr = conn.BeginTransaction();
    da = new SqlDataAdapter("select * from xxxx join.....", conn);
    builderForTable1 = new SqlCommandBuilder(da);
    da.Update(dataSetEmp, "Table");
    // ...
tr.Commit();

参考：

• SqlTransaction