I have a situation in regard to synchronizing a .NET client application data into the SQL Server over HTTP.
I am using webserivce to pass a Datset that has the schema(XSD) of the database and the all the changes made from the client offline. This dataset virtually will represent entire database. The user makes changes to record in terms of Add,Update and delete in offline mode which is stored in XML format locally using diffgram mode. Then an event triggers the sync process.
1) Issue is when I do update to a local master/child tables, there are unique keys generated. In the meantime someone adds the server records, and if this key is already generated then on calling the dataset.Merge(changes) method there is a update of the record.
2) The second issue is how do I manage the foreign key constraints in this scenario. Because on insert I have to make sure that all the FK columns refer to the PK of the master tables.
Please help me out. Also is there any better way to sync. I would really appreciate if you could help me on this