SqlDataAdapter不更新

Question

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

我使用了以下代码示例来完成这项工作：http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

DataTable在另一个Public Shared函数中填充。

我不知道是怎么回事。没有报告错误消息。手表显示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

正如您在https://stackoverflow.com/questions/7152273/batch-update-table-across-non-linked-sql-servers/7153047#7153047中所评论的，下面是我的回答：

您应该将UpdateBatchSize属性的SqlDataAdapter设置为0(无限)。我看不出有什么方法可以在不循环table2的情况下更新table1。

下面是一个示例代码，向您展示实现这一目标的一种方法：

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

Answer 2

指定命令类型如何？：

adapter.UpdateCommand.CommandType = CommandType.Text;

“如果您查看Reflector，您会发现_commandType没有初始化为任何东西。”

Answer 3

我可以想到几个可能导致数据不更新的问题。

首先-您是试图更新TableTwo中的现有行，还是尝试插入新记录。如果要插入，则使用update命令将无法工作。

第二，TableOne中是否有修改过的行？我看到的一个错误是，一旦有人填充了datatable，他们就调用Table.AcceptChanges()，它将所有行状态设置为不变。检查是否已修改数据的快速测试是在更新之前运行此代码：

For Each row as DataRow In MyDataTable.Rows
  If row.RowState = DataRowState.Modified Then
    MsgBox("Found a modified row!")

    Exit For
  End If
Next