Implementing offlinemode - Caching database

Question

Hi,

I'm trying to cache a database that I use from a connection string implemented in the web.config for use on a website in offline mode.

If anyone could answer a few of my questions that would be helpful.

1. How do I cache the database locally for use on the client side.

2. How do I use this database cached copy in a similar way to a connectionstring so it can swap over easily in offline mode.

Thanks in advance.

Answer 1

Just store all your tables in strongly typed dataset and use LINQ to do selection.

In this way the only difference could be whether to call the dataadapter to fill it or not before accessing the dataset.

Note that on online mode, you may want to use select command with criteria to limit the datasize being downloaded if the table rowsize is large.

You may also want to disable insert/delete/update mode on offline mode to prevent data consistancy problem.

Answer 2

The database has more than 15 tables and also has stored procedures.

It would take time for me to store the 15 tables or is there a way to copy across all the database in one go including these stored procedures which will sometimes need to be called or do I need to implementing the procedures directly in the code instead?

Answer 3

If you need stored procedures, I think you'd better download the data to SQL server express / compact edition. Most of the syntax are the same as corresponding version of fully blown SQL servers.

By selecting ROUTINE_DEFINATION from INFORMATION_SCHEMA.ROUTINES (the use of this view is defined in SQL-92 standard so you can be sure it's supported across versions), you can get the code of stored procedures that is not encrypted to run on your local database. You check whether it exist on local version and replace "create procedure" or "alter procedure" with "create procedure" if you cannot find the name or "alter procedure" if not.

If you choose SQL server compact edition, it's a good idea to have a blank database with updated schema available for your client to download. This will save you the trouble of updating schema every time. (Your function will just need to fill the tables)