Walkthrough: Adding a Local Database Cache to an N-Tier Application

A local database cache, in the context of Visual Studio, is a SQL Server Compact 3.5 database that is configured to synchronize data with a remote database by using Microsoft Synchronization Services for ADO.NET. This walkthrough provides step-by-step instructions for adding a SQL Server Compact 3.5 database to the application created in the Walkthrough: Creating an N-Tier Data Application topic.

During this walkthrough, you will learn how to perform the following tasks:

• Add a Local Database Cache item to a project.

• Configure data synchronization.

• Incorporate synchronization operations into the existing data service.

• Modify the code that loads data so that it retrieves the Customers table from the local database cache.

• Add code to initiate the synchronization process.

Prerequisites

To complete this walkthrough, you need the following:

• The solution and associated projects created in Walkthrough: Creating an N-Tier Data Application.

• Access to the Northwind sample database. For more information, see How to: Install Sample Databases.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Opening the NTierWalkthrough Solution

To open the NTierWalkthrough solution

• On the File menu, point to Open, click Project/Solution, and then navigate to the location of the NTierWalkthrough.sln file.

  Note

  The NTierWalkthrough.sln file is created in the Walkthrough: Creating an N-Tier Data Application topic and is a prerequisite to completing this walkthrough. Complete the Walkthrough: Creating an N-Tier Data Application topic, saving all projects when you are finished, if you have not yet done this.

Adding Local Database Caching to NTierWalkthrough

Because a local database cache is a SQL Server Compact 3.5 database that is located on the client, add the local database cache to the PresentationTier project. Because this walkthrough shows how to cache the Customers table, you will name the local database cache CustomersCache.

Note

In this walkthrough, the local database cache is named CustomersCache because it uses only the Customers table, but multiple tables can be added to a local database cache.

To add local data caching to the presentation tier

1. In Solution Explorer, right-click PresentationTier and click Add New Item.

2. Click the Local Database Cache template.

3. Type CustomersCache for the Name.

4. Click Add.

   The Configure Data Synchronization dialog box opens.

Configuring Data Synchronization

To configure data synchronization, select the data connection to the server and select the tables that you want to cache locally in your application. For this walkthrough, set the server connection to the SQL Server version of the Northwind database and add the Customers table to the local cache. Additionally, because you are adding the local database cache to an n-tier application, you have to set the advanced options to generate the server synchronization components into a separate project: DataService.

To configure data synchronization in an n-tier application

1. Set the Server Connection to the SQL Server version of the Northwind database.

2. Click the Add button to open the Configure Tables for Offline Use dialog box.

3. Select the check box associated with the Customers table and click OK. (Leave the default values.)

4. Click Advanced.

5. In the Server project location list, select DataService.

6. Click OK.

   The synchronization components are generated, the data is synchronized for the first time (the local database is created in the project and populated with data), and the Data Source Configuration Wizard opens.

7. Select the Customers table and then select the check box associated with it on the Choose Your Database Objects page.

8. Type LocalNorthwindCustomers for the DataSet name and click Finish.

Enabling Synchronization in the Existing Data Service

The generated synchronization components have been added into the DataService project, but still they have to be implemented by the service. The generated SyncContract contains the information that is needed by the service. This information appears as comments in the file. Copy the necessary information into the appropriate section of the service's App.config file.

To add the service information to the App.config file

1. Open the CustomersCache.Server.SyncContract file by double-clicking it in Solution Explorer.

2. Locate the commented line that resembles the following:

   <endpoint address ="" binding="wsHttpBinding" contract="DataService.ICustomersCacheSyncContract"/>

3. Copy the line without the comment character.

4. Open the App.config file of the DataService by double-clicking it in Solution Explorer.

5. Locate the <!-- Metadata Endpoints --> comment. Add the line that you copied in step 3 under the line that starts with <endpoint address = "mex".

6. Type a name (for example, SyncServer) for the endpoint address you just pasted so that you have code similar to the following:

   <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
   <endpoint address ="SyncServer" binding="wsHttpBinding" contract="DataService.ICustomersCacheSyncContract"/>
   

7. On the Build menu, click Build Solution.

To add the synchronization service operations to the existing data service

1. Open the CustomersCache.Server.SyncContract file by double-clicking it in Solution Explorer.

2. Change the class name from CustomersCacheSyncService to Service1. The following code examples show the class declaration as it should appear after you change the class name. (C# users must also change the name of the constructor.)

   Partial Public Class Service1
       Inherits Object
       Implements ICustomersCacheSyncContract
   ...
   

   public partial class Service1 : object, ICustomersCacheSyncContract
   {...
   

3. For C# users:

   Change the class declaration for Service1 (in the Service1.cs file) to be a partial class so that the declaration resembles the following:

   public partial class Service1 : object, ICustomersCacheSyncContract {
   
   private CustomersCacheServerSyncProvider _serverSyncProvider;
   
   public Service1() 
   {...
   

4. On the Build menu, click Build Solution.

Because the synchronization operations were added to the existing service, you must update the service reference in the PresentationTier project.

To update the service reference

1. In Solution Explorer, locate ServiceReference1 in the PresentationTier project.

2. Right-click ServiceReference1 and click Update Service Reference.

Modifying the Form to Load the Customers Data from the Local Cache

The form in the presentation tier currently obtains its data from the data service. Therefore, you have to modify the code to load the data from the local copy of the Customers table in the SQL Server Compact 3.5 database. The Orders table is still loaded with data returned from the DataService.

To modify Form1 to load the Customers data from the local database cache

1. Open Form1 in the Code Editor.

2. Replace the existing Form1_Load code with the following:

   Using DataSvc As New ServiceReference1.Service1Client
   
       ' Create a CustomersTableAdapter to load data from
       ' the local database cache.
       Dim CustomersTableAdapter As New LocalNorthwindCustomersTableAdapters.CustomersTableAdapter
   
       NorthwindDataSet.Customers.Merge(CustomersTableAdapter.GetData)
       NorthwindDataSet.Orders.Merge(DataSvc.GetOrders)
   End Using
   

   using (ServiceReference1.Service1Client DataSvc = new ServiceReference1.Service1Client())
   {
       LocalNorthwindCustomersTableAdapters.CustomersTableAdapter customersTableAdapter
          = new LocalNorthwindCustomersTableAdapters.CustomersTableAdapter();
       northwindDataSet.Customers.Merge(customersTableAdapter.GetData());
       northwindDataSet.Orders.Merge(DataSvc.GetOrders());
   }
   

Testing the Application

Run the application. The data is retrieved from both the local database cache and the data service.

To test the application

1. Press F5.

2. The data from the Customers table is retrieved from the local database, and the data from the Orders table is retrieved from the data service.

3. Close the form.

Synchronizing Data

Now that the presentation tier is set up to display the tables from the correct sources, the next step is to add code to initiate the synchronization. You will add a button to the form to start the synchronization process.

To synchronize data between the local database cache and the remote database

1. Open Form1 in Design view.

2. Click the Toolstrip on the form and add a button to the Toolstrip.

3. Name the button SyncButton.

4. Double-click SyncButton to create a SyncButton_Click event handler.

5. The following code starts the synchronization process. Add it to the event handler.

   Dim syncAgent As CustomersCacheSyncAgent = New CustomersCacheSyncAgent
   
       Using syncClient As New ServiceReference1.CustomersCacheSyncContractClient
       syncAgent.RemoteProvider = New Microsoft.Synchronization.Data.ServerSyncProviderProxy(syncClient)
       Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize
   
       NorthwindDataSet.Customers.Merge(New LocalNorthwindCustomersTableAdapters.CustomersTableAdapter().GetData())
   
       Dim syncSummary As String = "Total changes downloaded: " & _
          syncStats.TotalChangesDownloaded.ToString() & vbCrLf & _
          "Last successful synchronization: " & _
          syncStats.SyncCompleteTime.ToString
       MessageBox.Show(syncSummary)
   End Using 
   

   CustomersCacheSyncAgent syncAgent = new CustomersCacheSyncAgent();
   
   using (ServiceReference1.CustomersCacheSyncContractClient syncClient = new ServiceReference1.CustomersCacheSyncContractClient())
   {
       syncAgent.RemoteProvider = new Microsoft.Synchronization.Data.ServerSyncProviderProxy(syncClient);
       Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
       northwindDataSet.Customers.Merge(new LocalNorthwindCustomersTableAdapters.CustomersTableAdapter().GetData());
   
       string syncSummary = "Total changes downloaded: " + 
       syncStats.TotalChangesDownloaded.ToString() + Environment.NewLine +
       "Last successful synchronization: " +
        syncStats.SyncCompleteTime.ToString();
       MessageBox.Show(syncSummary);
   }
   

6. The data from the Customers table is retrieved from the local database, and the data from the Orders table is retrieved from the data service.

7. Close the form.

Testing the Application

To test the application

1. Press F5.

2. With the application running, use Server Explorer/Database Explorer (or another database management tool) to connect to the remote server database and modify some records.

   1. In Server Explorer/Database Explorer, locate the Customers table on the remote database server (not the connection to Northwind.sdf).

   2. Right-click the Customers table and click Show Table Data.

   3. Modify one or more records and commit the change. (Navigate off the modified row.)

3. Go back to the form and click SyncButton.

4. Verify that the modifications to the remote database are synchronized to the local database and displayed in the grid.

5. Close the form. (Stop debugging.)

Next Steps

Depending on your application requirements, there are several steps that you may want to perform after you add a local database cache to an n-tier application. For example, you could make the following enhancements to this application:

• Add validation to the dataset. For information, see Walkthrough: Adding Validation to an N-Tier Data Application.

• Enable bidirectional synchronization. For information, see How to: Configure a Local and Remote Database for Bidirectional Synchronization.

See Also

Tasks

How to: Add Code to Datasets in N-Tier Applications

Walkthrough: Adding Validation to an N-Tier Data Application

Walkthrough: Creating an Occasionally Connected Application

Walkthrough: Deploying an Occasionally Connected Client Application with the Local Database

How to: Configure a Local and Remote Database for Bidirectional Synchronization

Concepts

N-Tier Data Application Overview

Occasionally Connected Applications Overview

SQL Server Compact 3.5 and Visual Studio

What's New in Data

Other Resources

Hierarchical Update

Accessing Data (Visual Studio)