Getting SqlBulkCopy to honor column names

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.

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

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.