Question SqlBulkCopy update if record exists

Meccer

New member
Joined
Jun 4, 2012
Messages
1
Programming Experience
Beginner
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();
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,219
Location
Sydney, Australia
Programming Experience
10+
It can't be done. SqlBulkCopy only knows the destination table name and the column mappings. The point of SqlBulkCopy is that it's fast because it makes certain assumptions. If you are unable to make those assumptions with your data then you can't use SqlBulkCopy. You will need to use data adapters instead, for which you can specify exactly what SQL code to use. That will allow you to use IF EXISTS and then INSERT or UPDATE depending on the result.

You can use an OleDbDataAdapter to populate a DataTable from the source. Make sure that you set its AcceptChangesDuringFill property to False, so that all RowStates remain Added, meaning that the rows are ready to be inserted. You can then use a SqlDataAdapter with its InsertCommand set to process all the rows with a RowState of Added, i.e. all the rows. In the CommandText of the InsertCommand, you will use some conditional SQL to decide whether to INSERT or UPDATE as suggested earlier.

While you won't find an example of exactly what you're doing, this will give you some ideas of how to construct the data adapters if you need them:

Retrieving and Saving Data in Databases
 

Jonathan.Magnan

New member
Joined
Mar 19, 2014
Messages
2
Programming Experience
10+
Hello,

If someone are still looking for this answer, make sure to check my post:

Extension Methods - SqlBulkCopy

This post cover the following extension methods from my library:

- SqlBulkCopy.BulkUpdate
- SqlBulkCopy.BulkDelete
- SqlBulkCopy.BulkMerge (BulkUpsert)
- SqlBulkCopy.BulkInsert

Source code is available on CodePlex if you want to create your own methods.

More information about this project can be found on my website:

- C# - Extension Methods Library

Jonathan Magnan
 

Jonathan.Magnan

New member
Joined
Mar 19, 2014
Messages
2
Programming Experience
10+
Please notify that the links provided above are not valid anymore.

The ZZZ Projects Bulk Operations Library is the solution for you.


It allows you to effectuate various operations such as: Delete, Insert, Update and Merge on a substantial amount of data.

var operation = new SqlBulkOperation();

// ... Custom Settings ....
operation.BulkDelete(dt);
operation.BulkInsert(dt);
operation.BulkUpdate(dt);
operation.BulkMerge(dt);

It also allows more advanced features like output value, column formula, auditing, intercepting, logging and more.

Find out more on Bulk Operations | ZZZ Projects about the most advanced and flexible Bulk Operations library.
 
Top Bottom