How To: DALs for both SQL and SQLite in same project

How To: DALs for both SQL and SQLite in same project

Old forum URL:

TSF posted on Wednesday, October 03, 2012

I'm trying to build a connected WinRT app, and have realized I really need to provide offline storage capabilities.  So I'm going down the road of using SQLite on the client and SQL Server in the cloud.  What I think I need to do is to use two different DAL implementations: one for SQL Server, the other for SQLite.

I would think that the DP methods would determine when to call either of the DAL implementations.  Does this sound right??

In the end, what I'm after is for the primary storage tasks to always be done on the client, and when a connection is available, for the app to push/pull data to/from the cloud to sync things up.  Thanks for any suggestions.

RockfordLhotka replied on Thursday, October 04, 2012

If you want to dynamically switch between a local implementation and a remote implementation based on connectivity, you need to create a custom client-side data portal proxy that delegates to LocalProxy or WcfProxy based on whether the device is online.

That's problematic obviously, because anything done on the client machine ultimately does need to sync with the server.

So you are correct, the right answer is really to always have the app talk to the local database, and then to have a sync process that runs whenever you are online to sync with the server.

That's simpler from an app perspective, because your app always interacts with one database (the local one). And you have to write a sync agent anyway, so there's no extra work there.

TSF replied on Thursday, October 04, 2012

Thanks, Rocky.  If you have time, could you tell me if you've seen (or done) this scenario much in Win 8 apps yet?  I have found very few articles/blogs/etc talking about this occasionally-connected scenario when user data is in view.  It seems that most Win 8 apps are either self-contained with respect to their data or the type of data being sync'd is simple enough (e.g. app/user preferences) so that one can use the roaming capabilities of Win 8.  But my app isn't like that.  I feel bound by two scenarios that I'd like to support, the first of which drives the second:

  1. Data entered on one device is accessible from any other device or web app (which means remote storage is required)
  2. The user should be able to access (and update) his data even if not connected to the cloud

Maybe I'm getting too complicated or attempting to provide what user's don't really expect.  But if I purchased this app, I would (1) definitely want to have this kind of data stored in the cloud for easy accessibility, and (2) I would be frustrated if I couldn't use the app unless it was connected to the internet.

RockfordLhotka replied on Thursday, October 04, 2012

Sadly no. We're all at the very start of the new WinRT runtime's lifecycle, and it seems to me that Microsoft has left a number of important issues (like this one) for later...

Copyright (c) Marimer LLC