Use a local database is a remote connection is unavailable.

Use a local database is a remote connection is unavailable.

Old forum URL:

Roger_wgnr posted on Wednesday, March 09, 2011

This is a general question as I have not seen an answer anywhere (at least so far) and thought I may get a jump on something that I know will have to be addressed.

We have an situation with the program I am working on where it will normally connect to a remote database (SQL Server). However some users do not have internet access and the program also needs to function if they loose internet connection or the remote server goes down.

What we want to do is  attempt to connect to the remote server and if the connection can not be established use the local database (when connected via remote an FTP background thread will download an updated local copy of the companies data if needed).

We will provide an option that if the remote connection fails that the user can select to always use the local database (for those without internet connections) or to use the local database for this session only.

Any ideas on how this can be accomplished would be greatly appriciated.

tmg4340 replied on Wednesday, March 09, 2011

This has been discussed a few times on the forum...

The easiest model to use is sometimes referred to as "the Outlook model".  Basically, everything is local, as far as your app is concerned.  You write everything to a local database, and then you have a background process/thread/separate service that syncs up your local database with your remote one.  Microsoft provides an SDK to help manage this if you want - just look for the Sync Framework/Sync Services (one of those names should get you what you want).  In CSLA terms, you would use a local DataPortal which would write to a SQLCE database (or SQL Express, if you can get it on the client), and then the Sync Services framework would automatically sync your local database with the remote one.  I believe they have even included some code to re-try if a network connection is available.  If not, it's a relatively simple matter to write that.  You have to do some setup to your database to make the sync work, but from what I remember it's pretty minimal.

If Sync Services won't work, then you do have another option, though you'll end up replicating a lot of what Sync Services would do.  You can treat your app in a SOA-style fashion.  You again use a local DataPortal, and in your DP_ methods you determine whether a network connection is available.  If so, you call out to a WCF service to update your database/get data/whatever you need.  If not, then you deal with it locally, either using a local database or some kind of message serialization scheme.  You still have to deal with synchronizing the local data when a connection does become available.  You also lose some of what CSLA does for you, since in this scenario you're responsible for managing the CSLA object state.  I think Rocky covers some of this in the Data Access e-book he's working on (or will if he hasn't already).

Note that neither of these options use the remote DataPortal.  I think that version 4 allows for some kind of "custom pipeline creation" for the DataPortal processing, but I don't think you can use that to get what you want.  But I admit that I haven't really looked into it, so I might be wrong there.  If you can do it entirely within the CSLA DataPortal pipeline, that might make some things easier.

Regardless of how you solve the sync problem, you do have to do some sort of local caching of data, from the basic lookup lists to the actual data the user works with.  How you manage that, and how you refresh that data, is a separate question.  From a Sync Services perspective, you should be able to let the framework do much of that for you.  Anything else requires that you roll your own solution.


- Scott

Copyright (c) Marimer LLC