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?

Answer 1

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.