让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)
...

基本上，Receipts的值被放入了Orders中，反之亦然。

显然，这是因为SqlBulkCopy似乎没有遵守我告诉它要填充的列名 - 它只按序数位置进行操作。

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

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

有没有办法强制SqlBulkCopy遵守您要填写的列名？

- Paul Alan Taylor

---

在Red Gate SQL Compare的项目选项中有一个叫做“强制列顺序”的选项，它可以在同步新列时保持列的顺序不变。（请注意，这需要删除并重新构建表格，但在过去我使用它时效果很好，当绝对需要时可以考虑使用。） - Funka

Answer 1

这里有一个解决方案来修复这个“bug”。

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

在我的情况下，我正在从一个列次序随机的电子表格中加载数据。这是一个快速修复（表格是我的不按序号排列的 DataTable，bulkCopy 是 SqLBulkCopy 对象）。

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

正如您所看到的，我只是通过名称强制重新排序，即使名称完全相同。

- ttomsen

---

这是一个非常好的解决方案，适用于已经运行并需要在另一个领域中使用的代码。没有与预先存在的功能相关的问题。 - Forklift

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

- Yahia

---

除了我想要的（列数差异），其他都可以。 - Somnath Kadam

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);

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

- Eric Eskildsen

Answer 4

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

- David Atkinson

Answer 5

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

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);

}

- Amit Bisht

Answer 6

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

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

- jason

Answer 7

我可能漏掉了什么，但是为什么你不能更改列的顺序呢？我通常不会手动构建我的DataTable。我使用SqlDataAdapter的FillSchema方法，使用"select * from targetTable"查询。所以我总是确保我的DataTable适合目标表。

- Achim