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.
Accepted Answer
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.