Getting SqlBulkCopy to honor column names

I'm in the process of converting some stored procedure based reporting routines to run in C#. The general idea is to use all the wonders of C#/.NET Framework and then blast the results back into the DB. Everything has been going swimmingly, except for one issue I ran into yesterday and resolved today.

To do the import, I'm programmatically creating a DataTable and using SqlBulkCopy to move the results to the server.

e.g.

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

The import table had the fields in the following order.

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

Essentially, the value for Receipts was going into Orders, and vice versa.

Evidently, this is because SqlBulkCopy doesn't seem to be honoring the column name I've told it to populate - it just goes by ordinal position.

This is a problem for me as we use Redgate's SQL Compare. When pushing changes from one database to another, the ordinal position of columns isn't necessarily maintained. 开发者_如何学运维 ( e.g. if you insert a new column as the third ordinal field, SQL Compare will just append it to the table on a sync, making it the last column ).

This seriously messes with my solution. Now, these are only 'import' tables, so if need be, I can drop and recreate them as part of any migration script with the ordinal positions intact. I'd rather not do that though.

Is there a way to force SqlBulkCopy to honor the column names you tell it to fill?

Here's a solution to fix this 'bug'.

The default is to map by ordinal/position.

In my case I was loading from a spreadsheet with columns in random order. here's a quick fix(table is my DataTable that is 'out of ordinal order', and bulkCopy is the SqLBulkCopy object)

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

As you can see, i'm just forcing it to re-order by name, even though the names are IDENTICAL.

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

However, if the column counts differ, or the ordinal positions are not consistent, you must use ColumnMappings to make sure that data is copied into the correct columns.

For sample code see "Mapping Columns" at http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server

Here's an extension method that does it:

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

Usage:

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

This clears existing column mappings, then adds a mapping for each column passed in.

There is an option in SQL Compare to Force Column Order in the project Options. This will generate a script that will reorder the columns. I might be wrong but I think that this operation might require the table to be rebuilt, so please use with caution as this could impact performance. The script will, however, preserve the table data.

A simple way of implementing the column mapping in bulk copy, make sure your datatable columns names are exactly the same as in the table you want to dump all the data

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

}

There are overloads that let you specify the ordinal position, or the name of the source column to be mapped to the destination column.

Check out SqlBulkCopyColumnMappingCollection.Add and set the SqlBulkCopy.ColumnMappings property.

I might be missing something, but why couldn't you change the ordering of your columns? I usually don't build my DataTable by hand. I use the FillSchema method of SqlDataAdapter, using a "select * from targetTable" query. So I'm always sure that my DataTablefits the target table.