Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 813
Ganesha Avatar asked Jul 06 '11 11:07

Ganesha


People also ask

What is the use of SqlBulkCopy command?

The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

How does SqlBulkCopy update data?

Upload the data to the temporary table, then perform the SqlBulkCopy update. Using SqlBulkCopy(), upload the datatable's data to the temporary table. Then execute a SQL command to update the main table's data from the temporary table. Finally drop the temporary table.

Does SqlBulkCopy use transaction?

By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction.

What is SqlBulkCopy in VB net?

SqlBulkCopy gives you similar functionality from . NET that the bcp utility provides in SQL Server. In a nutshell, you point SqlBulkCopy at a SQL Server table in your database and then provide the WriteToServer method with a DataTable, array of DataRows, or an IDataReader, which it will then stream into SQL Server.


2 Answers

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);
}
like image 195
suryakiran Avatar answered Nov 03 '22 01:11

suryakiran


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

like image 20
Eben Roux Avatar answered Nov 03 '22 01:11

Eben Roux