.net - SqlDataAdapter 未更新

Question

我正在尝试更新 TableTwo使用 DataTable使用 TableOne 构建.
表之间的关系是一个名为 TableOneId 的外部列里面 TableTwo .

我使用以下代码示例来完成这项工作:Performing Batch Operations Using DataAdapters (MSDN)
DataTable在另一个 Public Shared 中填充功能。

我不知道出了什么问题。不报告错误消息。 watch 显示DataTable加载了数据。

DataTable定义为:

Public MyDataTable As New DataTable

Public Shared Sub DefineDataTable()

    Dim ErrorEmail As New ErrorEmailMessageClass
    With ErrorEmail
        Try
            Using connection As New SqlConnection(My.Settings.MyDB)
                MyDataTable.Columns.Add("ID", Type.GetType("System.Int32"))
                MyDataTable.Columns.Add("Column1", Type.GetType("System.Int32"))
                MyDataTable.Columns.Add("Column2", Type.GetType("System.Int32"))
                MyDataTable.Columns.Add("Column3", Type.GetType("System.Int32"))
                MyDataTable.Columns.Add("Column4", Type.GetType("System.Int32"))
            End Using
        Catch ex As Exception
            .WriteError("Sub DefineDataTable", ex.Message)
        End Try
    End With
End Sub

但是 SqlDataAdapter未更新:

Public Shared Sub UpdateTable()
    Dim ErrorEmail As New ErrorEmailMessageClass

    With ErrorEmail
        Try
            Using connection As New SqlConnection(My.Settings.MyDB)
                connection.Open()

                Dim adapter As New SqlDataAdapter()

                'Set the UPDATE command and parameters.
                adapter.UpdateCommand = New SqlCommand( _
                  "UPDATE Schema.TableTwo " _
                  & "SET " _
                  & "Column1=@Column1, " _
                  & "Column2=@Column2, " _
                  & "Column3=@Column3, " _
                  & "Column4=@Column4 " _
                  & "WHERE TableOneId=@ID;", connection)
                adapter.UpdateCommand.Parameters.Add("@Column1", SqlDbType.Int, 4, "Column1")
                adapter.UpdateCommand.Parameters.Add("@Column2", SqlDbType.Int, 4, "Column2")
                adapter.UpdateCommand.Parameters.Add("@Column3", SqlDbType.Int, 4, "Column3")
                adapter.UpdateCommand.Parameters.Add("@Column4", SqlDbType.Int, 4, "Column4")
                adapter.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
                adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.OutputParameters

                ' Set the batch size.
                adapter.UpdateBatchSize = 0

                ' Execute the update.
                adapter.Update(MyDataTable)

                connection.Close()
            End Using
        Catch ex As Exception
            .WriteError("Sub UpdateTable", ex.Message)
        End Try
    End With
End Sub

Answer 1

正如您在 your other question 中评论的那样，这是我的回答:

您应该设置 UpdateBatchSize SqlDataAdapter 的属性为 0(无限制)。
我看不到在不循环 table1 的情况下更新 table2 的方法。

这是一个示例代码，向您展示实现此目的的一种方法:

Public Sub BatchUpdate(ByVal table1 As DataTable)
    Dim connectionStringServer2 As String = GetConnectionString()

    Using connection As New SqlConnection(connectionStringServer2)
        Dim adapter As New SqlDataAdapter()

        'Set the UPDATE command and parameters'
        adapter.UpdateCommand = New SqlCommand( _
          "UPDATE Table2 SET " _
          & "NAME=@NAME,Date=@Date  WHERE TableOneId=@TableOneId;", _
          connection)
        adapter.UpdateCommand.Parameters.Add("@Name", _
          SqlDbType.NVarChar, 50, "Name")
        adapter.UpdateCommand.Parameters.Add("@Date", _
          SqlDbType.DateTime, 0, "Date")
        adapter.UpdateCommand.Parameters.Add("@TableOneId", _
        SqlDbType.Int, 0, "TableOneId")
        adapter.UpdateCommand.UpdatedRowSource = _
          UpdateRowSource.None

        ' Set the batch size,' 
        ' try to update all rows in a single round-trip to the server'
        adapter.UpdateBatchSize = 0
        ' You might want to increase the UpdateCommand's CommandTimeout as well'
        adapter.UpdateCommand.CommandTimeout = 600 '10 minutes'

        Dim table2 As New DataTable("table2")
        table2.Columns.Add(New DataColumn("Name", GetType(String)))
        table2.Columns.Add(New DataColumn("Date", GetType(Date)))
        table2.Columns.Add(New DataColumn("TableOneId", GetType(Int32)))

        ' copy content from table1 to table2'
        For Each row As DataRow In table1.Rows
            Dim newRow = table2.NewRow
            newRow("TableOneId") = row("ID")
            newRow("Name") = row("Name")
            newRow("Date") = row("Date")
            table2.Rows.Add(newRow)    
            ' note: i have not tested following, but it might work or give you a clue'
            newRow.AcceptChanges()
            newRow.SetModified()
        Next

        ' Execute the update'
        AddHandler adapter.RowUpdated, _
        New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)

        adapter.UpdateBatchSize = 5000   
        adapter.UpdateCommand.CommandTimeout = 6000
        adapter.ContinueUpdateOnError = True                      
        adapter.Update(table2)

    End Using
End Sub
Private Shared Sub OnRowUpdated(sender As Object, args As SqlRowUpdatedEventArgs)
    If args.RecordsAffected = 0 Then
        args.Row.RowError = "Optimistic Concurrency Violation!"
        args.Status = UpdateStatus.SkipCurrentRow
    End If
End Sub