Update previous record when i import from excel to sql database

c# excel import sql sqlbulkcopy

Question

I need help with this code. I am trying to import into SQL database from excel. A column is set to unique so that if it discovers duplicate data it ignores it. In this case instead of ignoring it I want it to update the data if there is an update. Here is the code;

if (con.State == ConnectionState.Open)
        {
            con.Close();
        }

        con.Open();

        if (comboBox1.SelectedItem.ToString() == "Comissioned Sites")
        {
            string pathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFilePath.Text + ";Extended Properties=\"Excel 12.0;HDR=Yes;\";";

            // Create Connection to Excel Workbook
            using (OleDbConnection connection = new OleDbConnection(pathConn))
            {
                OleDbCommand command = new OleDbCommand("Select * FROM [" + txtSheet.Text + "$]", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
                    {
                        con.Open();
                        bulkCopy.DestinationTableName = "tblView";
                        bulkCopy.WriteToServer(dr);
                        con.Close();
                    }
                }
            }
            MessageBox.Show("File Imported to Database Successfully");
        }

Popular Answer

So what you want is an upsert? Take a look at MERGE

MERGE - MSDN




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why