sql server - C# database syncing application identification issue -
i'm trying sync data between sql server , sqlite (where structure of databases match) within c# application.
the app has ability update, add, remove information, , handheld (motorola rfidreader), either-or could've have possibly updated information, , application won't know until syncing begins.
my issues knowing row within database has been added/removed/ or updated. have history table keeps track of when crud operations occur, unique identifiers within each database auto-incremented id column.
the problem is, if i've added row on handheld, , try sync 2 databases, , add row on server well, id values (probably) off. means can't search id , expect 100% of time id correctly matches both databases. mismatched id's between databases throw off remove , update functions well, since don't know if i'm working on right row item.
how can sync these 2 databases , keep ids intact?
the solution i've come far (and i'm not convinced best option) have table in server database keeps track of adds when syncing. essential there column table effected, , handheldrowid , corresponding serverrowid - i'd have connection each, in case not matched correctly
if cannot change pk field types (as indicated in comment, recommend use uniqueidentifier column) (as always) have couple of options. going highlight favourite first.
in order preserve referential integrity, , auto-incremented id field, should save state history table. commence merge between 2 databases when work complete. handheld should send it's history table server, should make appropriate changes , inform handheld of specific modifications need.
example of history table on handheld:
table action id ----------------------- add 1502 edit 1502 delete 134 same table on server:
table action id ----------------------- add 1502 delete 138 now, , both know record 1502 in table a on both devices not same record. tell server?
well, server should receive devices history table, , enumerate each action in it. on add, should create it's own auto-incremented id value (1503 in case). add record it's history table, new id. once done request actual data record client device, , save table.
then, server see edit record same id, , dispose of it. (no sense in recording edit have latest for.)
then server see remove record, , remove id it's own database. add record of having done history table.
once done (and records added history table on server), send copy of it's history table handheld, apply same changes. note: handheld should rollback changes made extent can. (if add 1502 present, remove 1502.) receive server's copy of changes. once done same thing server did, except need not worry of auto-incrementation on id columns, server handled that.
this process of course can extended (albeit more complexity) having more one handheld.
using guids/uniqueidentifiers
if can use guid or uniqueidentifier pk, process incredibly simple. merge changes. chances of guid/uniqueidentifier values overlapping minimal none.
my favourite quote (from wikipedia article):
they may or may not generated random (or pseudo-random) numbers. guids generated random numbers contain 6 fixed bits (these indicate guid random) , 122 random bits; total number of unique such guids 2122 (approximately 5.3×1036). number large probability of same number being generated randomly twice negligible; other guid versions have different uniqueness properties , probabilities, ranging guaranteed uniqueness duplicates. assuming uniform probability simplicity, probability of 1 duplicate 50% if every person on earth of 2014 owned 600 million guids.
Comments
Post a Comment