Walkthrough: Using Entities with a Local Database Cache

Article
04/15/2009

When you work with entity data by using the Entity Framework, you need a connection to the data source. In situations where an application may be only occasionally connected to the data source, you should consider creating a local database cache that can be periodically synchronized with the remote data source. The data model can use the local database cache instead of the remote data source. This walkthrough provides step-by-step directions for developing a data model for an occasionally connected application by incorporating Synchronization Services.

During this walkthrough, you will follow these steps:

Create a new Windows Forms application.
Run the Data Source Configuration Wizard to create and configure a typed dataset that has local database caching.
Run the Entity Data Model Wizard to create a data model based on the local database cache.
Create a Windows Form to display the entity data and confirm synchronization.
Bind entity data to Windows Form controls.
Add code to initiate the synchronization between databases.
Add a message box to display information about the number of records synchronized.

Prerequisites

To complete this walkthrough, you need the following:

Access to the AdventureWorks running on an instance of SQL Server.
SQL Server Compact 3.5 installed on the computer that is running Visual Studio 2008 SP1.

Note

The 64-bit version of SQL Server Compact 3.5 must be installed before you compile and run the application on a 64-bit computer.

Creating the Windows Forms Application

Because you will be displaying the entity data on a Windows Form, the first step in this walkthrough is to create a new Windows Forms application.

To create the new Windows Forms application

From the File menu, create a new project.
Name the project AdvWorksLocalCache.
Click the Windows Forms Application template and then click OK.

The AdvWorksLocalCache project is created and added to Solution Explorer.

Creating the Local Database Cache

Next you will perform the following tasks to create local database cache:

Create a local database cache that is based on the SalesOrderHeader and SalesOrderDetail tables in the AdventureWorks sample database.
Disable existing triggers. Triggers that are generated by the wizard conflict with existing triggers on the SalesOrderHeader and SalesOrderDetail tables. These triggers must be disabled to successfully run the sample. In a real-world application, the synchronization scripts in the new triggers must be merged into the existing triggers.
Define a relationship between the two tables that is based on a foreign key constraint. This relationship is needed to automatically create an association in the conceptual model, but it is not automatically created as part of the local database cache. Without this relationship in the database, the .edmx file would have to be edited manually to define an association in the storage model.

Note

The foreign key constraint can be created programmatically by using the SyncSchema class in synchronization services. For more information, see SyncSchema Class.

To create the local database cache

On the Project menu, click Add new item.
In the Templates pane, select Local Database Cache, then type AdventureWorks.sync in the Name field and click Add.
On the Configure Data Synchronization page, perform one of the following steps:
- If a data connection to the AdventureWorks sample database is available in the Server connection drop-down list, select it.
  
  -or-
- Click New next to the Server connection to open the Add Connection dialog box and create a new connection to the AdventureWorks database.
Leave the default value of AdventureWorks.sdf (new) for the Client connection. This creates a new SQL Server Compact 3.5 SP1 database and adds it to your project.
Click Add to open the Configure Tables for Offline Use dialog box.
Check the SalesOrderHeader and SalesOrderDetail tables, and then click OK.
In the Generate SQL Scripts dialog box, make sure that Update server for incremental changes and Save SQL scripts in the project for later use are selected.

Note

The previous step is not available when synchronization metadata already exists for the SalesOrderHeader and SalesOrderDetail tables.
Click OK to close the wizard and create and synchronize the local database cache.
In the Data Source Configuration Wizard dialog box, click Finish, and then click Yes when warned about creating an empty DataSet.

Note

The AdventureWorksDataSet will not be used in this application. Instead an object data source based on the SalesOrderHeader entity type will be used to enable drag-and-drop data binding.
In the Solution Explorer, expand the AdventureWorksLocalCache project, right-click the AdventureWorksDataSet.xsd dataset, click Delete, and then click OK.

To disable existing triggers

In SQL Server Management Studio, connect to the instance of SQL Server used to create the local database cache.

Execute the following query to disable the existing triggers.

USE AdventureWorks;
GO

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 1 AND name = 'uSalesOrderHeader')
BEGIN
    DISABLE TRIGGER Sales.uSalesOrderHeader ON Sales.SalesOrderHeader;
END

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 1 AND name = 'iduSalesOrderDetail')
BEGIN
    DISABLE TRIGGER Sales.iduSalesOrderDetail ON Sales.SalesOrderDetail;
END
GO

To create a relationship between the tables that were created in the previous procedure

In Server Explorer/Database Explorer, expand Data Connection, and then expand AdventureWorks.sdf and Tables.
Right-click the Sales_SalesOrderDetail table and then click Table Properties.
Click Add Relations.
Type FK_SalesOrderHeader_SalesOrderDetail in the Relation Name box.
Select Sales_SalesOrderHeader in the Primary Key Table list.
Select CASCADE in the Delete Rule list.
Click Add Columns.
Click Add Relation.
Click OK to complete the process and create the relationship in the database.
Click OK again to close the Table Properties dialog box.

Creating the Data Model and an Object Binding Source

Once the local database cache has been created, the next step is to define a data model that uses the local database cache as the data source.

Note

Data storage for the local database cache is provided by an instance of SQL Server Compact. For more information, see Occasionally Connected Applications Overview.

To add the Entity Data Model to the project

On the Project menu, click Add new item.
In the Templates pane, select ADO.NET Entity Data Model.
Type AdventureWorksLocal.edmx for the model name and then click Add.

The first page of the Entity Data Model Wizard is displayed.
In the Choose Model Contents dialog box, select Generate from database. Then click Next.
On the Which data connection should your application use to connect to the database? drop-down, select ClientAdventureWorksConnectionString (Settings).
Ensure that the Save entity connection settings in App.Config as: check box is selected and the value is set to AdventureWorksLocalEntities. Then click Next.
In the Choose Your Database Objects dialog box, clear all objects, expand Tables, and select the following table objects:
- Sales_SalesOrderHeader
- Sales_SalesOrderDetail
Click Finish to complete the wizard.

To create a data source based on the SalesOrderHeader type

On the Data menu, click Add New Data Source.
On the Choose a Data Source Type page, select Object.
On the Select an Object You Wish to Bind to page, expand the project node and locate the AdvWorksLocalCache node.
Expand the AdvWorksLocalCache node and select the Sales_SalesOrderHeader type the tree view.
Click Finish.

The Sales_SalesOrderHeader data source is added to the Data Sources window.

Adding Data Bound Controls and Synchronization Functionality

After you create your data model and configure data synchronization, you must then display data on the form and add synchronization functionality to your application.

To create a data-bound form to initiate and verify data synchronization

On the Data menu, click Show Data Sources.
In the Data Sources window expand the Sales_SalesOrderHeader node.
Drag the SalesOrderID, SalesOrderNumber, and TotalDue property items from the Data Sources window to Form1.
Drag the Sales_SalesOrderDetail property node from the Data Sources window to Form1.
Drag a Button control from the Toolbox to Form1. Set its Name property to closeButton and its Text property to Close.
Drag a Button control from the Toolbox to Form1. Set its Name property to saveChangesSyncButton and its Text property to Save Changes and Sync.
Double-click the form to create a form load event handler and open the form in the Code Editor.

Add the following using statement (Imports in Visual Basic).

using System.Data.SqlClient;
using System.Data.Objects;

In the partial class that defines the form, add the following code that creates an ObjectContext instance and defines the customerID constant.

// Create an ObjectContext instance based on AdventureWorksEntities.
private AdventureWorksEntities context;
private const int customerId = 277;

In the Load event handler, copy and paste the following code.

try
{
    // Initialize the object context.
    context = new AdventureWorksEntities();

    // Call the method that queries the local database cache
    // and binds the results to the form controls.
    this.GetOrderData();

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Add the GetOrderData method to the Form1 class.

private void GetOrderData()
{
    // Create a query for orders and related items.
    ObjectQuery<Sales_SalesOrderHeader> orderQuery = context.Sales_SalesOrderHeader
        .Where("it.CustomerID = @customerId",
        new ObjectParameter("customerId", customerId))
        .Include("Sales_SalesOrderDetail");

    // Set the data source of the binding source to the ObjectResult 
    // returned when the query is executed. Use OverwriteChanges to ensure 
    // that the context is always updated with the latest data from the 
    // local database cache.
    sales_SalesOrderHeaderBindingSource.DataSource =
        orderQuery.Execute(MergeOption.OverwriteChanges);

}

Double-click the Close button, and add the following code to the created button-click event handler.
```
// Dispose the object context.
context.Dispose();

// Close the form.
this.Close();
```
Double-click the Save Changes and Sync button to create a button-click event handler.

Add the following code to save changes, start the synchronization process, and then re-query the local database cache.

// Define a bidirectional synchronization.
// Ideally, conflict detection should also be defined.
this.Sales_SalesOrderHeader.SyncDirection =
    Microsoft.Synchronization.Data.SyncDirection.Bidirectional;
this.Sales_SalesOrderDetail.SyncDirection =
    Microsoft.Synchronization.Data.SyncDirection.Bidirectional;

In the Solution Explorer, right-click AdventureWorks.sync, and click View Code.
In the AdventureWorks class file, add the following code to the OnInitialized method to enable bidirectional synchronization.
```
// Define a bidirectional synchronization.
// Ideally, conflict detection should also be defined.
this.Sales_SalesOrderHeader.SyncDirection =
    Microsoft.Synchronization.Data.SyncDirection.Bidirectional;
this.Sales_SalesOrderDetail.SyncDirection =
    Microsoft.Synchronization.Data.SyncDirection.Bidirectional;
```
Note

In a real-world application, bidirectional synchronization requires additional code to handle conflicts during synchronization. For more information, see How to: Handle Data Conflicts and Errors.

Running the Application

Use the following procedure to compile and run the application.

Note

Before compiling and running the application on a 64-bit computer, ensure that the 64-bit version of SQL Server Compact is installed. Otherwise, set the target platform in the build configuration for the solution to x86.

To run the application

Press F5.
On the form, modify data on the form, and then click Save local changes.

This persists changes made from data-bound objects in the ObjectContext to the local database cache.
On the form, click Save Changes and Sync.

This saves changes from the object context to the local database cache and initializes synchronization between the local database cache and the remote AdventureWorks database.
Click Close to close the form.

Uninstalling the Application

To uninstall the application

Start SQL Server Management Studio and browse to the SQLUndoScripts project folder
Open and execute each of the three script files in the SQLUndoScripts folder.

Partager via