hello everybody,
im using excel table import to sql,
if i already have a same record how can i update it then continue import other data ?
DataTable dt = new DataTable();
dt.Columns.Add("SHEET", typeof(int));
dt.Columns.Add("DATE", typeof(DateTime));
dt.Columns.Add("DATA", typeof(string));
dt.Columns.Add("CODE", typeof(string));
IRow excelRow = default(IRow);
excelRow = sheet.GetRow(0);
for (int Cel = 1; Cel <= excelRow.LastCellNum - 1; Cel++)
{
var cellValue = excelRow.GetCell(Cel);
for (int k = 1; k <= sheet.LastRowNum - 1; k++)
{
string code = cellValue.ToString();
var date = sheet.GetRow(k).GetCell(0);
var value = sheet.GetRow(k).GetCell(Cel);
string dates = date.ToString();
string values = value.ToString();
dt.Rows.Add(b, dates, values, code);
}
}
cnn.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(cnn);
bulkCopy.DestinationTableName = "RMQ_DATA";
bulkCopy.WriteToServer(dt);
cnn.Close();
Nishant MittalPosted Apr 8, 2016, 5:03 AM
SqlBulkCopy as the name suggest is for copying (inserting) bulk records and it cannot perform update operation. Hence comes Table Valued Parameter to the rescue, which allows us to pass multiple records using a DataTable to a Stored Procedure where we can do the processing.