CODEWITHSUNDEEP
c#csvmappingsqlbulkcopy
user3818229
user3818229

Reputation: 1617

Set constant value to some columns in SqlBulkCopy

I'm trying transfer data from "csv" file to SQL Database. Is it possible to map some fields by default vale which not exsits in "csv" file? Something like shown below: bulkCopy.ColumnMappings.Add("Destenition_column_name", "constant_value");

Thanks for advance!

Upvotes: 3

Views: 4439

Answers (2)

Stanislav
Stanislav

Reputation: 143

Anytoe's answer can be useful if you create DataTable by yourself. But there could be a situation when you are getting the dataTable from some library method so you trick with default value will not work. For this case I found this solution:

var dt = await  GetDataTable();
dt.Columns.Add("ImportFileId", typeof(long), importFileId.ToString());

This is a feature named "Expression Columns". For example, you can use this solution with the ExcelDataReader library which has ".dataset()" method.

And you always can just loop through the rows if you don't want to use "Expression Columns" :

 foreach (DataRow dr in dt.Rows)
 {
   dr["ImportFileId"] = importFileId.ToString();
 }

Info: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/creating-expression-columns

Upvotes: 1

Anytoe
Anytoe

Reputation: 1675

What about setting a default column BEFORE populating the DataTable?

var returnTable = new DataTable();
returnTable.Columns.Add("Constant_Column_Name").DefaultValue = "Constant_Value";

If you then add rows each row will always have the specified default value for this column without explicitly setting it.

Upvotes: 5

Related Questions