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

Question

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

c# sqlbulkcopy sql-server

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.

Lucas Garcia

View more on Stack Overflow

Lucas Garcia · Accepted Answer · 10/9/2015 10:28:13 PM

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)

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator