How to use SqlBulkCopyColumnMappingCollection?

Question

I want to make one SqlBulkCopy method that I can use for all my bulk inserts by passing in specific data through the parameters.

Now I need to do mapping on some of them. I don't know how to make a SqlBulkCopyColumnMappingCollection since that was my plan to pass in the mapping collection in and use it. However I don't know how to make it. I can't make a new object of it.

This is what I have now. How can I add it do mapping put pass it in?

public void BatchBulkCopy(DataTable dataTable, string DestinationTbl, int batchSize) { // Get the DataTable DataTable dtInsertRows = dataTable; using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = DestinationTbl; // Number of records to be processed in one go sbc.BatchSize = batchSize; // Finally write to server sbc.WriteToServer(dtInsertRows); } }

you cannot add SqlBulkCopyColumnMappingCollection to bulkCopy.ColumnMappings as later is get only. you can use Add method of bulkCopy.ColumnMappings to add mappings one at a time. — iTSrAVIE
– iTSrAVIE, Commented Jun 16, 2016 at 15:50

abatishchev · Accepted Answer · 2010-09-24 07:16:11Z

You don't need to create a new instance of it - the SqlBulkCopy class has a property which is a mapping collection that you can use:

public void BatchBulkCopy(DataTable dataTable, string DestinationTbl, int batchSize) { // Get the DataTable DataTable dtInsertRows = dataTable; using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = DestinationTbl; // Number of records to be processed in one go sbc.BatchSize = batchSize; // Add your column mappings here sbc.ColumnMappings.Add("field1","field3"); sbc.ColumnMappings.Add("foo","bar"); // Finally write to server sbc.WriteToServer(dtInsertRows); } }

EDIT:

Based on the comments, the goal was to make a generic function, e.g. not have to hardcode the mapping explicitly in the function. Since the ColumnMappingCollection cannot be instantiated, I would recommend passing a List<string> or similar that contains the column mapping definition into the function. For example:

var columnMapping = new List<string>(); columnMapping.Add("field1,field3"); columnMapping.Add("foo,bar");

Then re-define the function as

public void BatchBulkCopy(DataTable dataTable, string DestinationTbl, int batchSize, List<string> columnMapping) { // Get the DataTable DataTable dtInsertRows = dataTable; using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = DestinationTbl; // Number of records to be processed in one go sbc.BatchSize = batchSize; // Add your column mappings here foreach(var mapping in columnMapping) { var split = mapping.Split(new[] { ',' }); sbc.ColumnMappings.Add(split.First(), split.Last()); } // Finally write to server sbc.WriteToServer(dtInsertRows); } }

I am aware of that however. Now you just tied this method to a single table(Unless I have every single table with the same column names). I am trying to make a generic method here that I can pass in any table and do a sqlbulkcopy. So if I need 5 different sqlbulkcopy inserts I don't have 5 of these methods with almost the same code except for the mapping.
Given that the function should be generic, and you can't instantiate the ColumnMappingCollection, I'd recommend passing in a representation of the mapping - see the edit above.
Ya that's the way I figured out too expect I just send in a List of ColumnMappings instead of strings.

Collectives™ on Stack Overflow

How to use SqlBulkCopyColumnMappingCollection?

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related