Disconnected app with sql server compact

[John]

Hi

I am developing an app in vb.net 2008 with sql server 2000 backend. I also
need disconnected version of the app for laptop users who would connect off
and on with LAN and thereby with the company sql server 2000. I am wondering
if I can use sql server compact 3.5 for local caching when laptops are
disconnected. My question is; how would my app use views and stored
procedures in disconnected mode as sql server compact does not support
either of these. Any help to clarify this would be appreciated.

Thanks

Regards

 

[William Vaughn]

The local Compact Edition database could be used for data caching. You could
write your own managed code to access it but as you found, there is no
support for SPs or Views. Your only other alternative would be to use the
Express edition on the client system which does support SPs and Views. In
either case you would want to use one of the various synchronization
paradigms to keep the two databases in sync.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[W.G. Ryan]

Like Bill V said, the main point is that you'd need to synchronize your
data. since there aren't views or stored procs, there's no way to do it
directly. You can create a backgroun thread or other mechanism to determine
when you are connected to the network, and when you are, flip a flag and
then use your DALC to talk directly to the big sql server.

You can mimick views (although not anything close to the real thing) by
filling a datatable with a query that corresponds to what the view was.

Although I'm not going to even touch the stored proc vs dynamic sql debate,
I can say that much of the benefit of using stored procs diminishes when
you're using a local db like Compact Edition in this manner.

You can also just bypass CE altogether. Pull down the data from sql server
into a dataset that contains everything you need to work with, interact with
that local data, buy some persistence by saving it to XML using writexml and
then when you're connected again, you can just run your Update() on the full
sql server. There's a ton of tradeoffs here so don't take this is a
recommendation, rather it's just a statement saying if you only need a
local cache of data, you don't necessarily need Compact Edition at all.

If it were mine though, i'd do as Mr Vaughn says and use it w/
synchronization.