开发者

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

开发者 https://www.devze.com 2023-03-17 11:19 出处:网络
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 w

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("TABL开发者_StackOverflow社区ECOLUMN1", "EXCELCOLUMN1");
bulkCopy.ColumnMappings.Add("TABLECOLUMN2", "EXCELCOLUMN2");
bulkCopy.ColumnMappings.Add("TABLECOLUMN3", CONSTANTVALUE);
bulkCopy.WriteToServer(dr);

But the code doesn't work. Any ideas?


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


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


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).

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号