Caching SQL Server Tables Locally with SQL Server Compact Edition
When connecting from Visual Studio 2008 to an SQL Server 2008 database, Visual Studio allows you to enable local database caching by creating a local copy of the database as a SQL Server Compact Edition (CE) database. This database becomes an embedded database in your project.
This is great; it allows you to develop and run a database application without requiring a connection to an SQL Server or even installing SQL Server on the client PC that runs the application. You can then use this as a standalone single user application or synchronize data between the application’s local cache and a running SQL Server database.
When you create a new strongly typed DataSet in the Studio via Data > Add New Data Source, if the data source is an SQL Server database, you get the option to “Enable local database caching”. If checked, Visual Studio will automatically create a Local Database Cache (.sync) file and add it to your project. The code in your DataSet is created using System.Data.SqlServerCe.SqlCexx so that your code can connect to the SQLCE database. It also creates the local SQLCE database (a .sdf file), and adds it to your project.
The key piece in this process is the Local Database Cache (.sync) file. This is what allows you to configure which tables can be cached in your local SQLCE database. If you bring up the Configure DataSet Wizard to either create or edit a DataSet and it won’t allow you to select some tables to be cached locally, you can create or edit the .sync file. The How to: Configure Data Synchronization to Use SQL Server Change Tracking page shows you how to create this file from scratch.
If you already have a .sync file, you can configure it by right-clicking on the file in Solution Explorer and choosing View Designer from the context menu. It’s kind of hidden, but if you click the Add button to add a table (provided there are more tables available in your database that can be added), you will see a link to Learn more about which tables can be cached…, which explains the rules determining which tables can be cached.