SqlBulkCopyColumnMappingCollection: How to Use

.net c# columnmappings sqlbulkcopy

Question

I want to create a single SqlBulkCopy method that I can use to input particular data via the parameters into for all of my bulk inserts.

I now need to map some of them. Since that was my intended method of supplying and using the mapping collection, I am unable to create a SqlBulkCopyColumnMappingCollection. But I'm not sure how to accomplish it. I can't use it to create a new object.

I currently have this. How do I add it, map it, and then send 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);
    }
}
1
12
9/24/2010 7:14:21 AM

Accepted Answer

You may utilize the mapping collection that is a property of the SqlBulkCopy class rather than having to construct a new instance of it:

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:

According to the comments, the objective was to create a generic function so that the mapping would not need to be explicitly hardcoded in the function. I would suggest giving a ColumnMappingCollection instead since it cannot be constructed.List<string> or something like, where the function includes the specification of the column mapping. For instance:

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

The function may then be redefined 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);
    }
}
21
9/24/2010 7:16:11 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow