让 SqlBulkCopy 遵循列名

Question

我正在将一些基于存储过程的报告例程转换为在 C# 中运行。总体思路是利用 C#/.NET Framework 的所有功能，然后将结果返回到数据库中。一切都进展顺利，除了我昨天遇到并今天解决的一个问题。

为了进行导入，我以编程方式创建一个 DataTable 并使用 SqlBulkCopy 将结果移动到服务器。

例如，

 DataTable detail = new DataTable();
 detail.Columns.Add(new DataColumn("Stock", Type.GetType("System.Decimal")));
 detail.Columns.Add(new DataColumn("Receipts", Type.GetType("System.Decimal")));
 detail.Columns.Add(new DataColumn("Orders", Type.GetType("System.Decimal")));

导入表的字段按以下顺序排列。

...
Stock decimal(18,4),
Orders decimal(18,4),
Receipts decimal(18,4)
...

本质上，收据的价值会转化为订单，反之亦然。

显然，这是因为 SqlBulkCopy 似乎不遵守我告诉它填充的列名称 - 它只是按顺序位置。

这对我来说是一个问题，因为我们使用 Redgate 的 SQL Compare。将更改从一个数据库推送到另一个数据库时，不一定会维护列的顺序位置。 （例如，如果您插入一个新列作为第三个序数字段，SQL Compare 只会在同步时将其附加到表中，使其成为最后一列）。

这严重扰乱了我的解决方案。现在，这些只是“导入”表，因此如果需要，我可以删除并重新创建它们，作为任何迁移脚本的一部分，并且序号位置保持不变。但我宁愿不这样做。

有没有办法强制 SqlBulkCopy 遵循您告诉它填充的列名称？

Answer 1

这是修复此“错误”的解决方案。

默认是按序号/位置映射。

就我而言，我从电子表格中加载，其中的列按随机顺序排列。这是一个快速修复（table 是我的 DataTable，它“不按顺序排列”，bulkCopy 是 SqLBulkCopy 对象）

foreach (DataColumn col in table.Columns)
{
    bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}

如您所见，我只是强制它按名称重新排序，即使名称相同。

Answer 2

来自 http://msdn.microsoft.com/en-us/library /system.data.sqlclient.sqlbulkcopy.columnmappings.aspx :

但是，如果列数不同，或者序数位置不同
一致，您必须使用 ColumnMappings 来确保数据是
复制到正确的列中。

有关示例代码，请参阅 http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server

Answer 3

下面是执行此操作的扩展方法：

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

public static class SqlBulkCopyExtensions
{
    public static SqlBulkCopy WithColumnMappings(this SqlBulkCopy sqlBulkCopy, DataColumnCollection columns) => WithColumnMappings(sqlBulkCopy, columns.Cast<DataColumn>());

    public static SqlBulkCopy WithColumnMappings(this SqlBulkCopy sqlBulkCopy, IEnumerable<DataColumn> columns)
    {
        sqlBulkCopy.ColumnMappings.Clear();

        foreach (DataColumn column in columns)
        {
            sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
        }

        return sqlBulkCopy;
    }
}

用法：

bulkCopy
    .WithColumnMappings(table.Columns)
    .WriteToServer(table);

这会清除现有的列映射，然后为传入的每个列添加映射。

Answer 4

项目选项中的 SQL Compare 中有一个选项是强制列顺序。这将生成一个脚本，对列进行重新排序。我可能是错的，但我认为此操作可能需要重建表，因此请谨慎使用，因为这可能会影响性能。但是，该脚本将保留表数据。

Answer 5

在批量复制中实现列映射的一种简单方法是，确保数据表列名称与要转储所有数据的表中的列名称完全相同

using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dc.ConnectionString))
{
        bulkcopy.BulkCopyTimeout = 150; 

        // column mapping defined
        dt.Columns.Cast<DataColumn>().ToList().ForEach(f =>
        {
            SqlBulkCopyColumnMapping bccm = new SqlBulkCopyColumnMapping();
            bccm.DestinationColumn = f.ColumnName;
            bccm.SourceColumn = f.ColumnName;
            bulkcopy.ColumnMappings.Add(bccm);
         });
        // column mapping defined

         bulkcopy.DestinationTableName = outputTable;
         bulkcopy.WriteToServer(dt);

}

Answer 6

有一些重载可让您指定序数位置或要映射到目标列的源列的名称。

查看 SqlBulkCopyColumnMappingCollection.Add 并设置 SqlBulkCopy.ColumnMappings 属性。

Answer 7

我可能遗漏了一些东西，但是为什么你不能更改列的顺序呢？我通常不会手动构建我的DataTable。我使用 SqlDataAdapter 的 FillSchema 方法，使用 “select * from targetTable” 查询。因此，我始终确信我的 DataTable 适合目标表。