Getting SqlBulkCopy to honor column names

Question

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?

P.S., there is a project option in Red Gate SQL Compare called "Force column order" which will keep the order of columns the same during synchronizations of new columns. (Mind you, this requires a drop and re-build of the table, but has worked well for me in the past when absolutely needed.) — Funka, Jan 30 '13 at 17:35

score 37 · Answer 1 · edited Jul 15 '20 at 19:40

37

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.

edited Jul 15 '20 at 19:40

Bob Horn

33,387
34
113
219

answered Oct 15 '15 at 14:27

ttomsen

848
9
15

This is a great solution for code that is already working and needs to be utilized in another area. There's no issues with pre-existing functionality. – Forklift Feb 03 '20 at 16:10

score 20 · Accepted Answer · answered Aug 23 '11 at 15:57

20

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

answered Aug 23 '11 at 15:57

Yahia

69,653
9
115
144

excepted what I want ( column count difference) – Somnath Kadam Sep 06 '17 at 13:14

Eric Eskildsen · Answer 3 · 2021-02-04T18:07:43.967

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.

score 1 · Answer 4 · answered Jul 10 '18 at 09:36

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

}

score 1 · Answer 5 · answered Aug 23 '11 at 21:03

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.

jason · Answer 6 · 2011-08-23T16:00:54.210

0

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.

edited Aug 23 '11 at 16:00

answered Aug 23 '11 at 15:54

jason

236,483
35
423
525

score 0 · Answer 7 · answered Aug 23 '11 at 16:54

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.

Getting SqlBulkCopy to honor column names

7 Answers7