A good way to check for duplicates and remove them from a DataTable against a database table?

Question

I have a populated DataTable in my code:

I am using SQL Server CE 4.0 and to solve performance problems, I am using SqlCeBulkCopy :

 SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); options = options |= SqlCeBulkCopyOptions.KeepNulls; // Check for DB duplicates using (SqlCeBulkCopy bc = new SqlCeBulkCopy(strConn, options)) { dt = RemoveDuplicateRows(dt, "Email"); bc.DestinationTableName = "Recipients"; bc.WriteToServer(dt); } 

RemoveDuplicateRows will remove duplicates from the DataTable, but there is no verification that already exists in the database.

I want to effectively delete all the elements in the DataTable that exist in the actual database table before passing it to WriteToServer(dt) .

What would be a good performance, cost-effective solution to this problem?

Answer 1

So, you need to pave the data table and the existing table correctly? I'm not sure if sql ce supports a temporary table, I did something simulative with ms sql, here is the pseudo code

 string tmpTableDefinition = "create table #tmpEmails (...)"; using(var connection = new SqlCeConnection(connectionString)) { //Create temp table var tmpTableCommand = new SqlCeCommand(tmpTableDefiniton, connection); tmpTableCommand.ExecuteNonQuery(); //Bulk copy to the temp table, note that bulk copy run faster if the teble is empty //which is always true in this case... using (var bc = new SqlCeBulkCopy(connection, options)) { bc.DestinationTableName = "#tmpEmails"; bc.WriteToServer(dt); } //Run a sp, that have temp table and original one, and marge as you wish in sql //for sp to compile properly, you would have to copy tmp table to script too var spCommand = new SqlCommand("sp_MargeTempEmailsWithOriginal", connection); spCommand.Type = SP //Don't remember exact prop name and enum value spCommand.ExecuteNonQuery(); }