INSERT object or UPDATE if it already exists using BulkCopy (C#, SQL)

Question

I'm trying to update a table with a List<T>. I created an EnumExtension class and created a method which converts my Enumerable to a DataTable:

public static DataTable AsDataTable<T>(this IEnumerable<T> data)...

And then I started creating a method which should use BulkCopy to insert or update my table, using the enumerable. Right now, it's written like this:

public void BulkInsertOrUpdate(DatabaseEnum database, IEnumerable<Object> enumerable, string TableName)
    {
        var connection = this.GetSqlConnection(database);

        var transaction = connection.BeginTransaction();

        using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
        {
            bulkCopy.BatchSize = 100;
            bulkCopy.DestinationTableName = TableName;
            try
            {
                bulkCopy.WriteToServer(enumerable.AsDataTable());
            }
            catch (Exception)
            {
                transaction.Rollback();
                connection.Close();
            }
        }
        transaction.Commit();
    }

(OBS: Method GetSqlConnection creates and opens my connection using the appropiate connection string.)

But I don't know how to create the update feature. Is there a simple way to do so, with an efficient code? I'm fond of BulkCopy, but I can try another method, hopefully not hurting my architecture (and, ultimately, I'll have to make this sacrifice if I can't find a way).

Thanks for your attention, I'm ready to answer doubts about my situation.

do a google search on how to Insert bulk records to database using xml this way if bulk inserts are not enabled on the database inserting all the records via xml will still work passing XML file to a stored procedure on the database — MethodMan, Oct 08 '15 at 23:18
This one might answer your question http://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp. But from my personal point of view the answer give by @Locke is more appropriate.You can also take advantage of MERGE statement in your case. — J-D, Oct 09 '15 at 05:18
Thanks for the guindance J-D. I'm using a temp table to bulk insert my data. After that I'm merging this table into my log table with a WHERE clause. — Lucas Garcia, Oct 09 '15 at 22:02

Lucas Garcia · Accepted Answer · 2015-10-09T22:28:13.703

So I solved my problem (thanks #J-D). I am basically using a temp table and inserting the data inside it. First I truncate it every time it's used:

connection.Query(database_name, sql_truncate_query);

Then I insert the data using the method I created previously:

connection.BulkInsert(database_name, ienumerable, table_name);

OBS: Changed the method name from BulkInsertOrUpdate to BulkInsert.

Later, I update my log table using a simple MERGE sql query:

INSERT [database].[table]  (col1, col2)
SELECT col1, col2
FROM [database].[table2] t2 
WHERE NOT EXISTS (SELECT col1 FROM [database].[table] t1 WHERE t1.col1= t2.col1);

OBS: My problem changed while I was developing, so I am no longer using the 'update' feature, I just need to check the existence of the row. Of course, you can write a code in which it updates the value if it's caught by the WHERE clause.

I don't know if it's the optimized way to resolve this problem, but it's certainly better than inserting or updating each row at a time.

INSERT object or UPDATE if it already exists using BulkCopy (C#, SQL)

1 Answers1