I can successfully add excel file to sql database usy SQLBulkCopy, I need to UPDATE a record if the ‘Case Number’ exists else INSERT the record. Please note that I DO NOT have SQL Epress 2008 to use MERGE statement.
C#:
OleDbConnection excelConnection = new
OleDbConnection(excelConnectionString);
// connect to database
SqlConnection myConnection = new SqlConnection
("Data Source=.\\SQLEXPRESS; Initial Catalog=mineTest;Integrated Security=True");
excelConnection.Open();
// determine worksheet name
DataTable dbSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception("Error: Could not determine the name of the sheet");
}
string sheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
// fetch data from excel file
OleDbCommand cmd = new OleDbCommand("SELECT * from [" + sheetName + "]", excelConnection);
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(myConnection);
myConnection.Open();
//column mapping
sqlBulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Mine", "Mine"));
sqlBulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("LTI", "LTI"));
sqlBulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Case Number", "CaseNumber"));
sqlBulk.ColumnMappings.Add(new
// write data
sqlBulk.DestinationTableName = "details";
sqlBulk.WriteToServer(dReader);
myConnection.Close();
excelConnection.Close();