vendredi 29 mai 2015

C# database syncing application identification issue

So I'm trying to sync data between SQLServer and SQLite (where the structure of the databases match) within a C# application. The app has the ability to update, add, remove information, and so does the handheld (motorola rfidreader), so either-or could've have possibly updated the information, and the application won't know until the syncing begins.

My issues is with knowing what row within the database has been added/removed/ or updated. I have a history table that keeps track of when CRUD operations occur, but the only unique identifiers within each database is an auto-incremented ID column.

The problem is, if I've added a row on the handheld, and then try to sync the two databases, and add a row on the server as well, the ID values will (probably) be off. That means I can't search for a ID and expect 100% of the time that the ID correctly matches both databases. mismatched ID's between the databases will throw off the Remove and Update functions as well, since I don't know if I'm working on the right row item.

How can I sync these two databases and keep the IDs intact?

The only solution I've come up with so far (and I'm not convinced its the best option) is to have a table in the server database that keeps track of any ADDS when syncing. So essential there'd be a column for which table is effected, and the HandheldRowID and the Corresponding ServerRowID - so I'd have a connection to each, just in case they are not matched correctly

Aucun commentaire:

Enregistrer un commentaire