在整个非链接的SQL服务器的批量更新表

问题描述

我想更新整个使用非关联SQL服务器的表：C＃或VB.net和ADO.net 的SqlDataAdapter 。

I am trying to update a table across "non-linked" SQL servers using:C# or VB.net and ADO.net SqlDataAdapter.

我需要使用数据表和的SqlDataAdapter 。

非常重要：我需要使用 BATCHUPDATE ，并避免通过数据表循环

Very Important: I need to use BatchUpdate and avoid looping through the DataTable.

在服务器1台的设计从表设计不同的服务器2。

The table designs in Server 1 differ from table design in Server 2.

源表：

Server 1. Table 1
ID INT
NAME Varchar(30)
Date DateTime

目标表：

Server 2. Table 2
ID INT
TableOneId INT (Foreign Key from Server 1. Table 1)
NAME Varchar(30)
Date DateTime

我要如何更新表2服务器2通过一个示例的SqlDataAdapter ，或替代料的方法。

I need a sample on how to update table 2 on server 2 using SqlDataAdapter, or an alternative batch method.

推荐答案

您应该设置中的SqlDataAdapter为0（无限制）UpdateBatchSize 的财产。我不明白的方式，而不循环表1至表2更新

You should set the UpdateBatchSize property of the SqlDataAdapter to 0 (unlimited).I don't see a way to update table2 without looping table1.

下面是一个简单的code向您展示了实现这一目标的一种方法：

Here is a sample code to show you one way to achieve this:

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

        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'
        adapter.Update(table2)
    End Using
End Sub

这篇关于在整个非链接的SQL服务器的批量更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！