.net - SQL Server online/offline scenario -

i have build application have read ms access data several (+100) shop locations, , save data on remote ms sql server @ end point of vpn. because quality of connection sites hq site precarious, customer ask me provide online/offline implementation synchronization capabilities.

so made function detects reachability of ip of sql server, , execute function in various conditions: before of relevant insert/update operations, , on timer basis. timer intended forcing in case of long time connexion drop-down save locally in temporary locations (and bulk synchronizations when comm more reliable, e.g. in night)

i started supposing connexion failures somehow occasional, find in reality more frequent.

i wondering can more reliable approach.

the situation fear, loose connexion after start group of 3 inserts , 1 update (based on global performance estimate validations done server based data, execution time 2000 ms per saving). provided kind of commit - rollback approach, not convinced useful.

any suggestions welcomed.

l.e.

i have online/real-time scenario important because have update information shop customer: if buys accumulates reward points, if return product (after 5 minutes or 5 days) have remove points account. kind of evening/night synchronization not desirable regarding @ aspect.

in fact wonder how avoid wrong insert/update of points if loose connection in time of transaction, dealing fact after 5 minutes maybe customer buy something, , have deal last record pos application (because have know if customer member or not, , if eligible achieve points; , in pos app no info customers, sales made shop).

i implement msmq/wcf solution advanced "poison queue" handling. catch specific exception(s) related off line'ness. or take advantage of re-try count.

http://blogpad-online.blogspot.com/2012/04/wcf-msmq-message-retries.html

and this

http://www.codeproject.com/articles/10841/surviving-poison-messages-in-msmq

(those not end-all url articles, give taste of functionality)

you don't mention if "synchronizations" must chronological. different beast.

edit:

you should @ sending data "set based" instead of 1 one. (rbar).

take here:

http://granadacoder.wordpress.com/2009/01/27/bulk-insert-example-using-an-idatareader-to-strong-dataset-to-sql-server-xml/

that article old, shows how can send xml down sql server in atomic mode.

the "3 inserts , 1 update" packaged in xml (or json if work on wcf/webapi service) , send them on set.

but i'm not smart phone app expert..... on/off wifi different art.