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?

score 6 · Answer 1 · answered Jul 06 '11 at 11:37

6

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

answered Jul 06 '11 at 11:37

suryakiran

1,976
25
41

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! – Eric Barr Feb 21 '14 at 16:53

score 5 · Accepted Answer · answered Jul 06 '11 at 11:32

5

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

answered Jul 06 '11 at 11:32

Eben Roux

12,983
2
27
48

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? – Patrick Fromberg Jan 31 '14 at 13:30

score 0 · Answer 3 · answered Jul 06 '11 at 11:12

0

Did you try setting a default value for you column in database? I think it's the most easiest way, as after inserting any record, the default value would also get inserted into the specified column (it acts like triggers).

answered Jul 06 '11 at 11:12

Saeed Neamati

35,341
41
136
188

1

As per the question, I think the constant value might differ for each request. how can you set the default value? – suryakiran Jul 06 '11 at 11:13
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. – Saeed Neamati Jul 06 '11 at 11:17

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

3 Answers3