使用SqlBulkCopy将DataTable中的列映射到SQL表

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);}

5 个解决方案

#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?..

参考：如何使用SqlBulkCopyColumnMappingCollection？..

Seel also http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy

Seel也是http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy

#2

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.

知道如果源查询（或表）中的列和目标表具有相同的名称并且顺序完全相同，那么可能很有用，因此不需要显式写出映射，因为SqlBulkCopy将创建具有此默认顺序的默认映射。

#3

This became such an 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(            new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));    }}

Since I was creating the DataTable myself, I named its columns the same as the SQL table.

由于我自己创建了DataTable，因此我将其列命名为与SQL表相同。

#4

Use the ColumnMappings:

使用ColumnMappings：

s.ColumnMappings.Add("Name", "Name");s.ColumnMappings.Add("Address", "Address");

#5

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.

Add on ColumnMappings集合方法允许您将列从源表映射到目标表。 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集合时必须考虑这些列

#1