Mapping Columns in a Datatable to a Sql Table with Sqlbulkcopy
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...
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);
}
6 Answers
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
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.
It might be useful to know that if the columns in the source query (or table) and the target table have exactly 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.
Use the ColumnMappings:
s.ColumnMappings.Add("Name", "Name");
s.ColumnMappings.Add("Address", "Address");
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
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
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
