C# 使用 SqlBulkCopy 将 DataTable 中的列映射到 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17469349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mapping columns in a DataTable to a SQL table with SqlBulkCopy
提问by user2545743
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.
我想知道如何在将数据添加到数据库之前将数据库表中的列映射到 c# 中的数据表。
using (SqlBulkCopy s = new SqlBulkCopy(conn))
{
s.DestinationTableName = destination;
s.WriteToServer(Ads_api_ReportData);
}
采纳答案by StackTrace
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?. .
参考：如何使用 SqlBulkCopyColumnMappingCollection？. .
Seel also http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy
另见http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy
回答by Ben Gulapa
Use the ColumnMappings:
s.ColumnMappings.Add("Name", "Name");
s.ColumnMappings.Add("Address", "Address");
回答by Laxmikant
The method Add on ColumnMappings collection allows you to map your columns from source table to destination table. The method ColumnMappings.Add accepts four different ways to map your columns.
ColumnMappings 集合上的 Add 方法允许您将列从源表映射到目标表。ColumnMappings.Add 方法接受四种不同的方式来映射您的列。
SQLBulkCopy is very much strict on data type of both the columns which you have to consider while adding it to ColumnMappings collection
SQLBulkCopy 对将其添加到 ColumnMappings 集合时必须考虑的两个列的数据类型都非常严格
回答by Hugo Nava Kopp
It might be useful to know that ifthe columns in the source query (or table) and the target table have the same nameand are in the exact same order, then there is no need to write out the mappings explicitly, because SqlBulkCopywill create a default mapping with this default order.
知道如果源查询（或表）和目标表中的列具有相同的名称并且顺序完全相同，则无需显式写出映射，因为这SqlBulkCopy将创建一个使用此默认顺序的默认映射。
回答by Evan M
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 DataTablemyself, I named its columns the same as the SQL table.
由于我是DataTable自己创建的，因此我将其列命名为与 SQL 表相同的名称。
