将数据表中的列映射到带有SqlBulkCopy的SQL表

I would like to know how i would be able to map coloumns in a database table to the datatable in c# before adding the data to the database

我想知道如何在将数据添加到数据库之前，将数据库表中的coloumns映射到c#中的datatable

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

Ref:如何使用SqlBulkCopyColumnMappingCollection?。。

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

闭目也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

Use the ColumnMappings:

使用ColumnMappings:

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

#4

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集合的方法允许您将您的列从源表映射到目标表。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集合中时，您必须考虑这两列的数据类型

#5

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表相同的名称。

#1