c# - 使用 DataTable 的 SqlBulkInsert 到链接服务器

Question

我在不同的机器上使用 2 个 SQL 2008 服务器。服务器名称是 source.ex.com 和 destination.ex.com。

destination.ex.com 链接到 source.ex.com 并且为 source.ex.com 设置了适当的权限写入 destination.ex.com

上名为 bacon-wrench 的数据库

我已经通过 SMS 登录到 source.ex.com 并测试了这个查询(成功):

INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES (4,6);

在 C# .NET 4.0 网页中，我连接到 source.ex.com 并执行类似的查询(成功):

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
    c.Open();
    String sql = @"
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
        (PunchID, BaconID) VALUES (34,56);";
    using(SqlCommand cmd = new SqlCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

对于小型插入语句集(比如说 20 条或更少)，执行这样的操作效果很好:

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
    c.Open();
    String sql = @"
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES (34,56);
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES (22,11);
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES (33,55);
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES (1,2);";
    using(SqlCommand cmd = new SqlCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

我正在尝试对大约 20000 条记录执行类似的操作。上面的方法需要 11 分钟才能完成——我假设这是服务器向我发出信号以使其成为某种批量操作。在其他 StackOverflow 线程中，推荐使用 SqlBulkCopy 类并将其作为参数 DataTable，完美!

因此我构建了一个 DataTable 并尝试将其写入服务器(失败):

DataTable dt = new DataTable();
dt.Columns.Add("PunchID", typeof(int));
dt.Columns.Add("BaconID", typeof(int));
for(int i = 0; i < 20000; i++)
{
    //I realize this would make 20000 duplicate 
    //rows but its not important 
    dt.Rows.Add(new object[] {
        11, 33 
    });
}

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
    c.Open();
    using(SqlBulkCopy bulk = new SqlBulkCopy(c))
    {
        bulk.DestinationTableName = "[destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]";
        bulk.ColumnMappings.Add("PunchID", "PunchID");
        bulk.ColumnMappings.Add("BaconID", "BaconID");
        bulk.WriteToServer(dt);
    }
}

EDIT2:以下消息是我要修复的内容:

网页在 bulk.WriteToServer(dt); 崩溃并显示错误消息 Database bacon-wrench does not exist please ensure it is type correctly. 我是什么做错了吗？我该如何更改它才能使其正常工作？

编辑1:

我能够使用以下语法显着加快查询速度。但是对于这么小的记录集来说仍然很慢。

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
    c.Open();
    String sql = @"
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES 
         (34,56),
         (22,11),
         (33,55),
         (1,2);";
    using(SqlCommand cmd = new SqlCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

Answer 1

如果您使用的是 SQL Server 2008+，则可以引入表用户数据类型。准备类型、接收表和存储过程，如下所示。数据类型和存储过程在本地系统上。我通常在代码中有一个 if 语句来检测表是远程的还是本地的，远程我这样做，本地我使用 SqlBulkCopy。

if(TYPE_ID(N'[Owner].[TempTableType]') is null)
begin
CREATE TYPE [Owner].[TempTableType] AS TABLE ( [PendingID] uniqueidentifier, [Reject] bit)
end
IF  NOT EXISTS (SELECT * FROM [LinkedServer].[DatabaseOnLS].sys.tables where name = 'TableToReceive') 
EXEC('
CREATE TABLE [DatabaseOnLS].[Owner].[TableToReceive] ( [PendingID] uniqueidentifier, [Reject] bit)
') AT [LinkedServer]
else
EXEC('
TRUNCATE TABLE [DatabaseOnLS].[Owner].[TableToReceive]
') AT [LinkedServer]

CREATE PROCEDURE [Owner].[TempInsertTable]
@newTableType TempTableType readonly
AS
BEGIN
insert into [LinkedServer].[DatabaseOnLS].[Owner].[TableToReceive] select * from @newTableType 
END

然后在 C# 代码中，您可以执行类似这样的操作，将 DataTable 插入到链接服务器上的表中(我使用的是现有的 UnitOfWork，它已经具有连接和事务):

using (var command = new SqlCommand("TempInsertTable",
                    oUoW.Database.Connection as SqlConnection) { CommandType = CommandType.StoredProcedure }
                )
{
    command.Transaction = oUoW.Database.CurrentTransaction as SqlTransaction;
    command.Parameters.Add(new SqlParameter("@newTableType", oTempTable));
    drResults = command.ExecuteReader();
    drResults.Close();
}