How to insert a constant value into a column using ASP.Net SqlBulkCopy

Question

In the below code, I am trying insert the records from excel to Database table, but an additional column is not passed through the excel, which has to be populated with a constant value(foreach loop with a different value) assigned from the requested page.

string CONSTANTVALUE="Test";
bulkCopy.DestinationTableName = "TABLE NAME";
bulkCopy.ColumnMappings.Add("TABLECOLUMN1", "EXCELCOLUMN1");
bulkCopy.ColumnMappings.Add("TABLECOLUMN2", "EXCELCOLUMN2");
bulkCopy.ColumnMappings.Add("TABLECOLUMN3", CONSTANTVALUE);
bulkCopy.WriteToServer(dr);

But the code doesn't work. Any ideas?

Solution 1

I assume your dr is a reader of some kind. How is it populated? It may be possible to select a default value into a column and map that. Something like this (sql syntax)

select 
    EXCELCOLUMN1, 
    EXCELCOLUMN2, 
    'ConstantValueFromPage' as EXCELCUSTOM 
from 
    sheet1

Then have:

bulkCopy.ColumnMappings.Add("TABLECOLUMN3", "EXCELCUSTOM");

HTH

Solution 2

You can do it, by changing your command text. As below

string CONSTANTVALUE="Test";
OleDbCommand command=new OleDbCommand("select *,"+CONSTANTVALUE+" as [ConstantCol] from [sheet$]",ObleDbCon);
using (DbDataReader dr = command.ExecuteReader())
{
bulkCopy.DestinationTableName = "TABLE NAME";
bulkCopy.ColumnMappings.Add("TABLECOLUMN1", "EXCELCOLUMN1");
bulkCopy.ColumnMappings.Add("TABLECOLUMN2", "EXCELCOLUMN2");
bulkCopy.ColumnMappings.Add("TABLECOLUMN3", "ConstantCol");
bulkCopy.WriteToServer(dr);
}

Comments

[suryakiran]

As per the question, I think the constant value might differ for each request. how can you set the default value?

[Saeed Neamati]

In that case, I think you can use SMO (server management objects, the API to work with SQL Server in code) to change the default value of the column, before inserting new records.

[Patrick Fromberg]

Imagine the default value is a very long string. Will the redundantly per row reoccurring default value be optimized away in the transport data structures (Oracle or SqlServer drivers) and in the ADO.NET DataTable for e.g?

[Eric Barr]

This code worked great for me, but two notes: 1) The parameters for ColumnMappings.Add are (sourceColumn, destinationColumn). So if you are trying to copy from Excel to a database table, then the arguments should be reversed, it should be bulkCopy.ColumnMappings.Add("EXCELCOLUMN1", "TABLECOLUMN1"); 2) if you are going to use column names for Excel, then your Excel connection string needs to include HDR=Yes. If found it easier to use indices so that I don't have to rely on the person making the spreadsheet to use the right header names. THANKS!