Set constant value to some columns in SqlBulkCopy

Question

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!

Answer 1

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.

Answer 2

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