c# - DataAdapter : if row already exist, then update, else insert -

i have windows mobile ppc local sql ce database i'm trying replicate remote sql server database when connected. can't use bulk replication or other "automated" method platform obsolete , sql ce limited.

i adapted solution here i'm kinda lost, procedure works when destination database empty, fail (primary key error) when has pre-existing data. dataadapter making inserts , can't updates.

here's code:

private void stocksqlbuilder()  { dataset sqlcedataset = new dataset("source"); dataset sqldataset = new dataset("destination");  try {     _conn.open();     // pull data local database sqlce     using (sqlcedataadapter sourceda = new sqlcedataadapter("select * stock", _conn))     {         messagebox.show("pull stock ce");         sourceda.fill(sqlcedataset, "stock");     }     _conn.close();      _remoteconn.open();     // connect sql server database push data     using (sqldataadapter destinationda = new sqldataadapter("select * stock", _remoteconn))     {         // table of remote sql server, loop through rows of sqlce , add them dataset         sqlcommandbuilder cmdbuilder = new sqlcommandbuilder(destinationda);         destinationda.fill(sqldataset, "stock");          foreach (datarow row in sqlcedataset.tables["stock"].rows)         {             sqldataset.tables[0].newrow();             sqldataset.tables[0].rows.add(row.itemarray);         }           destinationda.update(sqldataset, "stock");     }     _remoteconn.close(); } catch (sqlexception sqlex) {     messagebox.show(sqlex.message);     this.returnstatus = false; } catch (sqlceexception sqlceex) {     messagebox.show(sqlceex.message);     this.returnstatus = false; } } 

also if have other solutions two-way sql sync project, i'm ears tried lot of different things.

got results merge query using temporary table, , comparing date selective update:

       string mergequery = @"--synchronize target table refreshed data source table             merge dbo.stock target             using dbo.stock_sync source              on (target.product_id = source.product_id)              --when records matched, update records if there change             when matched , source.date_stock > target.date_stock             update set target.spot= source.spot,              target.date_stock = source.date_stock,             target.loading_id = source.loading_id              --when no records matched, insert incoming records source table target table             when not matched target              insert (spot, product_id, date_stock, loading_id)              values (source.spot, source.product_id, source.date_stock, source.loading_id);              select * dbo.stock;";              // merge 2 tables matching code_barre row             sqlcommand mergesql = new sqlcommand(mergequery, _remoteconn);             int rowsaffected = mergesql.executenonquery();             messagebox.show("données affectées : " + rowsaffected);              // clean temporary source table             sqlcommand recleansql = new sqlcommand("truncate table dbo.stock_sync", _remoteconn);             recleansql.executenonquery();