Mapping columns in a DataTable to a SQL table with SqlBulkCopy
Solution 1
You probably need some thing like
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);
}
}
Ref: How to use SqlBulkCopyColumnMappingCollection? . .
Seel also http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy
Solution 2
This became such a common task that I wrote this helper for it:
public static void AutoMapColumns(SqlBulkCopy sbc, DataTable dt)
{
foreach (DataColumn column in dt.Columns)
{
sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
}
Since I was creating the DataTable
myself, I named its columns the same as the SQL table.
Solution 3
It might be useful to know that if the columns in the source query (or table) and the target table have the same name and are in the exact same order, then there is no need to write out the mappings explicitly, because SqlBulkCopy
will create a default mapping with this default order.
Solution 4
Use the ColumnMappings:
s.ColumnMappings.Add("Name", "Name");
s.ColumnMappings.Add("Address", "Address");
user2545743
Updated on April 19, 2020Comments
-
user2545743 about 4 years
I would like to know how I can map columns in a database table to the datatable in c# before adding the data to the database.
using (SqlBulkCopy s = new SqlBulkCopy(conn)) { s.DestinationTableName = destination; s.WriteToServer(Ads_api_ReportData); }