Mapping columns in a DataTable to a SQL table with SqlBulkCopy

Question

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

SQL bulk copy works without the column mappings also,however there is a catch that the order in which datatable rows are initialized - sql server expects the same column order in the table. Hence adding the columnmappings is must while using sqlbulkcopy @user2545743 — dhinesh, Dec 31 '19 at 14:17

score 50 · Accepted Answer · edited May 23 '17 at 12:10

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

score 40 · Answer 2 · edited Apr 18 '19 at 20:34

40

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.

edited Apr 18 '19 at 20:34

L. Norman

483
7
21

answered Sep 06 '17 at 20:47

Evan M

2,573
1
31
36

Great method. Thanks – Darthchai Jul 06 '18 at 18:11
This little snippet just saved me a ton of time! Thank you so much. – Dave Morris Feb 24 '19 at 22:57
2

I just came up with this method myself, and thought "Damn, I'm a genius!" So I came to SO to share it. You beat me to the punch! – Michael Glass Jun 02 '20 at 19:15

Hugo Nava Kopp · Answer 3 · 2017-03-15T10:49:12.807

14

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.

edited Mar 15 '17 at 10:49

answered Aug 09 '15 at 22:44

Hugo Nava Kopp

2,906
2
23
41

1

Yes, but in this case, the idea is to map 1:1 all of the columns EXCEPT the identity column.... – David V. Corbin Oct 13 '22 at 11:11

score 2 · Answer 4 · answered Jul 04 '13 at 11:46

2

Use the ColumnMappings:

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

answered Jul 04 '13 at 11:46

Ben Gulapa

1,619
14
10

score 1 · Answer 5 · edited Oct 07 '15 at 15:06

1

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.

SQLBulkCopy is very much strict on data type of both the columns which you have to consider while adding it to ColumnMappings collection

edited Oct 07 '15 at 15:06

Brad Larson

170,088
45
397
571

answered Jul 08 '13 at 17:30

Laxmikant

588
4
11

score 0 · Answer 6 · answered Apr 26 '22 at 17:35

You can use below code

                using (OleDbConnection con = new OleDbConnection(excelCS))
                {
                    OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);
                    con.Open();
                    // Create DbDataReader to Data Worksheet  
                    DbDataReader dr = cmd.ExecuteReader();
                    // SQL Server Connection String  
                    //string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                    string CS = objCommon.TestConnection;
                    // Bulk Copy to SQL Server   
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(CS);

                    bulkInsert.ColumnMappings.Add("Kcode", "Kcode");
                    bulkInsert.ColumnMappings.Add("MDMCode", "MDMCode");

                    bulkInsert.DestinationTableName = "tbl_MDMKcodeMapping";
                    bulkInsert.WriteToServer(dr);
                    
                    lblMessage.Text = "Your file uploaded successfully";
                    lblMessage.ForeColor = System.Drawing.Color.Green;
                }

Please note that in

  bulkInsert.ColumnMappings.Add("Kcode", "Kcode");

The first parameter should be your Excel column name and the second parameter should be your SQL table column name

Mapping columns in a DataTable to a SQL table with SqlBulkCopy

6 Answers6

Linked