Building Occasionally Connected Smart Clients with WPF

Abstract

Building occasionally connected clients becomes a whole lot easier with Visual Studio 2008 and ADO .NET Sync Services. Gone are the days of manually creating and managing cache files locally yourself, now using ADO.NET Sync Services and Visual Studio 2008 you can visually design your local data cache and have it automatically synchronize your data. This article takes you through creating a data-bound WPF client, and using Visual Studio and sync services to cache data locally into a SQL Compact database. We will explore data synchronization options as well as how to set up your SQL database server to enable change tracking.

In this article

Having reliable, high speed network access is essential to business applications today. These applications are designed to work on a wide range of devices, from desktops and laptops to cellular phones, PDAs and wearable devices. It is challenging to have reliable, high speed access to data for these applications running on mobile or remote devices. Things like network interruption, firewalls and wireless reception restrictions are examples where data may not be accessible at all, while network speed and bandwidth limitations are examples where data may be accessible but not fast enough. Setting up local data cache is a great way to mitigate some of these limitations. This also improves scalability of the applications by reducing the demand on the real-time data access bandwidth.

An application can take a snapshot of the data that it needs and store it locally. Thus, even when disconnected from the original source the data is available. When connectivity is available the application can synchronize the local data with the original store. Applications that are designed to perform at least a subset of their functions using a local store are called occasionally connected applications.

Visual Studio 2008 Service Pack 1 makes it greatly easier for developers to build occasionally connected applications while also saving time. This article will take you through the steps to build such an application. This application uses WPF for the presentation, Microsoft SQL Server Compact for the local data store and Microsoft SQL Server 2008 as the main data source.

At the end of this article you will know how to:

·         Build a data bound WPF application

·         Build an occasionally connected application using ADO .NET Synchronization Services and SQL Server Compact

For the purposes of illustration we will use the Northwind database that Microsoft ships as a sample database. This sample stores data for Northwind Traders, a fictitious company that deals with packaged food items. This application is a single, simple order entry form that a Northwind Traders sales person would use to view, update or enter new orders.

To build this application you need:

·         Visual Studio 2008 Service Pack 1

·         SQL Server 2008 (or SQL Server Express 2008)

·         SQL Compact 3.51 (this is installed with Visual Studio 2008 SP1)

Creating a data bound WPF application

The first steps are to create a WPF application project and add dataset to it.

1.       In Visual Studio, create a new WPF application project, and call it SalesOrders

2.       From the data menu select, Add New Data Source…

The Data Sources Wizard is displayed.

3.       Select Database on the Choose a Data Source Type page and click Next

4.       On the Choose Your Data Connection create a new connection to a Northwind database running on SQL Server 2008.

5.       On the Choose Your Database Objects page expand the Tables node and select the tables: Orders and OrderDetails. Click Finish.

This will create a dataset in the project. Press Shift-Alt-D to switch to the data sources window. This shows the tables in your dataset. If you are familiar with WinForms binding to DataSets, note that unlike WinForms you cannot drag and drop tables and columns from the data sources window to the WPF form to create data bound controls. We need to write the XAML and code to do this.

Add the following XAML snippet to your Window declaration in Window1.xaml. The portions to add are highlighted.

<Window x:Class="Window1"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    xmlns:app="clr-namespace:SalesOrder"

    Title="Sales Orders" Height="480" Width="580" Name="Window1">

    <Window.Resources>

        <app:NorthwindDataSet x:Key="NorthwindDataSet" />

    Window.Resources>

Figure1: Declaring the dataset as a resource in XAML

We have now included the application namespace, app and instantiated the dataset in the resources section of the window. This will create the instance when the window is constructed at runtime, but before the window’s Loaded event is fired.

Controls that we add to this form will be bound to tables from this dataset. We will use the CollectionViewSource as the binding component. CollectionViewSource is a very effective way to bind to a data source collection. CollectionViewSouce has a View property and a Source property. The source property is set to our table. One extremely valuable benefit of CollectionViewSources is currency management. Later in the article we will see how this can be used. For now, let us add a CollectionViewSource for the Orders table after the dataset declaration in Window.Resources.

<CollectionViewSource x:Key="OrdersViewSource"

                      Source="\{Binding Source=\{StaticResource NorthwindDataSet\}, Path=Orders\}" />

Although we now have the dataset instantiated and CollectionViewSources setup for the various tables, we still have not filled the dataset. For this we would need to add code to the window’s Loaded event handler. Double clicking on the window in the designer will create a Loaded event handler and open the source code window for you. Add the following code:

' Get the dataset instance from Window.Resources,

' then fill the Orders table using the orders table adapter

Dim ds As NorthwindDataSet = FindResource("NorthwindDataSet")

Dim ordersAdapter As New NorthwindDataSetTableAdapters.OrdersTableAdapter

ordersAdapter.Fill(ds.Orders)

' Move the current position of the View property of the OrdersCollectionViewSource

Dim ordersCVS As CollectionViewSource = FindResource("OrdersViewSource")

ordersCVS.View.MoveCurrentToFirst()

Figure 2: Filling the dataset

You can bind a control to a CollectionViewSource by setting the bindings to the property of the control. For example, to bind a textbox to the Orders.OrderDate column, we can write:

<TextBox Text="\{Binding Source=\{StaticResource OrdersViewSource\}, Path=OrderDate\}" />

Often we need multiple controls bound to different paths within a CollectionViewSource, each path pointing to a column in the table. If all such controls are contained in a common parent, such as a Grid or a Panel, it is usually cleaner to declare a DataContext on the containing control. For instance:

Instead of:

<Grid>

    <TextBox Text="\{Binding Source=\{StaticResource OrdersViewSource\}, Path=OrderDate\}" />

    <TextBox Text="\{Binding Source=\{StaticResource OrdersViewSource\}, Path=ShippedDate\}" />

Grid>

We could write:

<Grid DataContext=\{StaticResource OrdersViewSource\}>

    <TextBox Text="\{Binding Path=OrderDate\}" />

    <TextBox Text="\{Binding Path=ShippedDate\}" />

Grid>

In the above snippet all binding sources for the control refer to the parent container’s DataContext. Let us now add a set of controls for the different columns of the Orders table. Add the following XAML between the Grid tags of the window.

<Grid DataContext="\{StaticResource OrdersViewSource\}" Margin="12, 40, 0, 0"

      HorizontalAlignment="Left" VerticalAlignment="Top" Width="260" Height="170">

   <Grid.ColumnDefinitions>

       <ColumnDefinition Width="Auto" />

       <ColumnDefinition Width="120*" />

   Grid.ColumnDefinitions>

   <Grid.RowDefinitions>

       <RowDefinition Height="Auto" />

       <RowDefinition Height="Auto" />

       <RowDefinition Height="Auto" />

       <RowDefinition Height="Auto" />

       <RowDefinition Height="Auto" />

   Grid.RowDefinitions>

   <Label Content="Order ID:" Grid.Column="0" Grid.Row="0" Margin="3" VerticalAlignment="Center"/>

   <Label Content="\{Binding Path=OrderID\}" Grid.Column="1" Grid.Row="0"

          Style="\{StaticResource dataFieldStyle\}"/>

   <Label Content="Customer:" Grid.Column="0" Grid.Row="1" Margin="3"  VerticalAlignment="Center"/>

   <ComboBox ItemsSource="\{Binding\}" DisplayMemberPath="CustomerID" Grid.Column="1" Grid.Row="1"

             Style="\{StaticResource dataFieldStyle\}" />

   <Label Content="Order Date:" Grid.Column="0" Grid.Row="2" Margin="3"  VerticalAlignment="Center"/>

   <TextBox Text="\{Binding Path=OrderDate\}" Grid.Column="1" Grid.Row="2"

            Style="\{StaticResource dataFieldStyle\}" />

    <Label Content="Shipped Date:" Grid.Column="0" Grid.Row="3" Margin="3"  VerticalAlignment="Center"/>

    <TextBox Text="\{Binding Path=ShippedDate\}" Grid.Column="1" Grid.Row="3"

             Style="\{StaticResource dataFieldStyle\}"/>

    <Label Content="Ship Via:" Grid.Column="0" Grid.Row="4" Margin="3"  VerticalAlignment="Center"/>

    <ComboBox ItemsSource="\{Binding\}" DisplayMemberPath="ShipVia" SelectedIndex="0"

              Grid.Column="1" Grid.Row="4" Style="\{StaticResource dataFieldStyle\}" />

Grid>

<Grid DataContext="\{StaticResource OrdersViewSource\}" Margin="278,40,12,0"

      VerticalAlignment="Top" Height="170">

    <Grid.ColumnDefinitions>

        <ColumnDefinition Width="Auto" />

        <ColumnDefinition Width="120*" />

    Grid.ColumnDefinitions>

    <Grid.RowDefinitions>

        <RowDefinition Height="Auto" />

        <RowDefinition Height="Auto" />

        <RowDefinition Height="Auto" />

        <RowDefinition Height="Auto" />

        <RowDefinition Height="Auto" />

    Grid.RowDefinitions>

    <Label Content="Ship To:" Grid.Column="0" Grid.Row="0" Margin="3" VerticalAlignment="Center"/>

    <TextBox Text="\{Binding Path=ShipName\}" Grid.Column="1" Grid.Row="0"

             Style="\{StaticResource dataFieldStyle\}" />

    <Label Content="Address:" Grid.Column="0" Grid.Row="1" Margin="3"  VerticalAlignment="Center"/>

    <TextBox Text="\{Binding Path=ShipAddress\}" Grid.Column="1" Grid.Row="1"

             Style="\{StaticResource dataFieldStyle\}" />

    <Label Content="City:" Grid.Column="0" Grid.Row="2" Margin="3"  VerticalAlignment="Center"/>

    <TextBox Text="\{Binding Path=ShipCity\}" Grid.Column="1" Grid.Row="2"

             Style="\{StaticResource dataFieldStyle\}" />

    <Label Content="Postal Code:" Grid.Column="0" Grid.Row="3" Margin="3"  VerticalAlignment="Center"/>

    <TextBox Text="\{Binding Path=ShipPostalCode\}" Grid.Column="1" Grid.Row="3"

             Style="\{StaticResource dataFieldStyle\}"/>

    <Label Content="Country:" Grid.Column="0" Grid.Row="4" Margin="3"  VerticalAlignment="Center"/>

    <TextBox Text="\{Binding Path=ShipCountry\}" Grid.Column="1" Grid.Row="4"

             Style="\{StaticResource dataFieldStyle\}" />

Grid>

Figure 3: Controls bound to the Orders table

Notice that we have applied a style, dataFieldStyle, to controls bound to the columns. So we need to declare that style in the resources section. Add the following to Windows.Resources.

<Style x:Key="dataFieldStyle" >

    <Setter Property="Control.Margin" Value="3" />

    <Setter Property="Control.Height" Value="28" />

    <Setter Property="Control.VerticalAlignment" Value="Center" />

    <Setter Property="Control.VerticalContentAlignment" Value="Center" />

Style>

Figure 4: Style resource for field controls

Now let us add navigation buttons to the top of the form. Using the Toolbox, create a toolbar and set the Height to 28 and VerticalAlignment to Top. Add two toolbar buttons, one named Previous, and the other named Next. Double click on the buttons to create event handlers for the buttons and in the handlers for these buttons add the following code.

For the handler of the Previous button:

' Move current item of the collectionview the previous row

' ... unless it is the first one

Dim ordersCVS As CollectionViewSource = FindResource("OrdersViewSource")

ordersCVS.View.MoveCurrentToPrevious()

If (ordersCVS.View.IsCurrentBeforeFirst) Then

    ordersCVS.View.MoveCurrentToFirst()

End If

For the handler of the Next button:

' Move current item of the collectionview the next row

' ... unless it is the last one

Dim ordersCVS As CollectionViewSource = FindResource("OrdersViewSource")

ordersCVS.View.MoveCurrentToNext()

If (ordersCVS.View.IsCurrentAfterLast) Then

    ordersCVS.View.MoveCurrentToLast()

End If

At this point you should have an application that runs and displays the details of the Orders table. The Previous and Nextbuttons should navigate the rows of the Orders table.

Figure 5: Display Orders

Now we need to add a tabular view at the bottom of the form. We will use a ListView control for this. We will set the View property of the ListView to a GridView, then add controls for each of the column as data templates. But first, we need to declare a CollectionViewSource for our OrderDetails table.

Add the following just after the OrdersViewSource declaration in your Windows.Resources section.

<CollectionViewSource x:Key="OrderOrderDetailsViewSource"

                      Source="\{Binding Source=\{StaticResource OrdersViewSource\}, Path=FK_Order_Details_Orders\}" />

There are a couple of important things to note here. The first is that the CollectionViewSource for OrderDetails is not bound to OrderDetails table from the dataset. Instead the source of the CollectionViewSource is set to the CollectionViewSource of the OrdersTable and the Path is set to the foreign key member of the Orders table. Since Orders and OrderDetails have a master-details relationship, this effectively sets up a master-details relationship between their corresponding CollectionViewSources.

We also need to fill the OrderDetails table, similar to what we do for the Orders table as shown in figure 2. Add the following code after the code in figure 2.

' Fill the OrderDetails table using the OrderDetails table adapter

Dim orderDetailsAdapter As New NorthwindDataSetTableAdapters.Order_DetailsTableAdapter

orderDetailsAdapter.Fill(ds.Order_Details)

Dim orderOrderDetailsCVS As CollectionViewSource = FindResource("OrderOrderDetailsViewSource")

orderOrderDetailsCVS.View.MoveCurrentToFirst()

Figure 6: Filling the OrderDetails table

Now that we have the CollectionViewSource for the OrderDetails table declared and the datatable filled, we can add the ListView control to display some of the columns of the OrderDetails table.

From the toolbox, drag and drop a list view control. Then modify the contents as shown below.

<ListView Margin="12,216,12,12"

          ItemsSource="\{Binding Source=\{StaticResource OrderOrderDetailsViewSource\}\}"

          IsSynchronizedWithCurrentItem="True" SelectedIndex="0">

    <ListView.ItemContainerStyle>

        <Style>

            <Setter Property="Control.HorizontalContentAlignment" Value="Stretch" />

            <Setter Property="Control.VerticalContentAlignment" Value="Stretch" />

        Style>

    ListView.ItemContainerStyle>

    <ListView.View>

        <GridView>

            <GridViewColumn Header="Product ID" Width="80">

                <GridViewColumn.CellTemplate>

                    <DataTemplate>

                        <TextBox Margin="-6,-1" Text="\{Binding Path=ProductID\}"/>

                    DataTemplate>

                GridViewColumn.CellTemplate>

            GridViewColumn>

            <GridViewColumn Header="Unit Price" Width="80">

                <GridViewColumn.CellTemplate>

                    <DataTemplate>

                        <TextBox Text="\{Binding Path=UnitPrice\}" />

                    DataTemplate>

                GridViewColumn.CellTemplate>

            GridViewColumn>

            <GridViewColumn Header="Quantity" Width="80">

                <GridViewColumn.CellTemplate>

                    <DataTemplate>

                        <TextBox Margin="-6,-1" Text="\{Binding Path=Quantity\}" />

                    DataTemplate>

                GridViewColumn.CellTemplate>

            GridViewColumn>

            <GridViewColumn Header="Discount" Width="80">

                <GridViewColumn.CellTemplate>

                    <DataTemplate>

                        <TextBox Margin="-6,-1" Text="\{Binding Path=Discount\}" />

                    DataTemplate>

                GridViewColumn.CellTemplate>

            GridViewColumn>

        GridView>

    ListView.View>

ListView>

Figure 7: ListView with a GridView

And that’s it. That will display the Orders and OrderDetails as shown in Figure 7A.

To add the ability to save changes, just add a Save button to the toolbar and add the standard DataSet code to save:

TableAdapterManager.UpdateAll(Me.NorthwindDataSet)

Occasionally Connected Application

So far what we have built is an ordinary WPF data bound application. What about the local cache? Before we go into the steps to go through to set up a local data cache in Visual Studio 2008, let us take a few moments to discuss what is required to build an occasionally connected application.

There are multiple approaches to configuring a local data source that would be available to an occasionally connected application. Using a local data cache is one of them, and the one we will use here. The application uses a local database as a cache to store the data that it needs. This cache acts as the primary source of data for data access, even when the application is connected to the main source. To ensure that the data is current, the cache is periodically synchronized with the main data source. The frequency with which the two data sources are synchronized depends on how frequently the relevant data changes. Synchronization has its own set of challenges.

1.       Knowing that data has changed – It is unnecessary and inefficient to synchronize data when nothing has changed in either data source. Thus we need a mechanism to keep track of when the two data sources were last synchronized and if data has changed since then.

2.       Knowing what has changed—Not only is it necessary to determine that data has changed, we must also determine which rows have changed. Thus we must track of which rows have been updated, inserted, or deleted.

One way to keep track of changes is to modify the schema on the main database with tracking columns and tables. For instance, if we wish to cache the Orders table, we would need to add two columns to the table: One to track when the row was updated and one to keep track of when it was inserted. In addition we would need a table to store every row that was deleted from the Orders table. We would need update, insert and delete triggersfor the table. These triggers would update the tracking elements of the table whenever changes occur. In the cache we would need to store the timestamp of last synchronization.

Once the database is setup with this the application can then query the tables for rows where insert time, update time is greater than the time of last synchronization.

Fortunately SQL Server 2008 has built-in change tracking capabilities. You do need to turn it on for a database and for a given table, but don’t have to worry about the timestamps or triggers or maintaining a deleted items table. The query commands are also simpler.

But that still sounds fairly cumbersome. All of this is mostly standard code. A lot of code, but barely related to the application itself. It’s just plumbing.

Visual Studio 2008 SP1 makes it really easy to set everything up. All you need to take care of is:

1.       Making a call to synchronize the data

2.       Handling conflicts

Let’s have a look.

Modifying the application to work when offline

Following are the steps to convert our Sales Order application into an occasionally connected application.

1.       In the data sources window, right click on the dataset node and select Configure DataSet with Wizard…

This launches the data sources wizard again. One change in Visual Studio 2008 SP1 is a checkbox at the bottom of the Choose Your Database Objects page labeled Enable local database caching.

2.       Check the checkbox and click Next.

 This brings up the Choose Tables to Cache page. Figure 7B. This page shows all the tables in the dataset.

3.       Check the check box for all tables

For each table you see a choice of incremental and snapshot. When incremental is selected, only changed rows will be synchronized. When snapshot is selected all rows will be synchronized. Snapshot is useful if the table rows are few (such as a lookup table for state codes) and it is not worth the overhead of tracking and querying for changes.

4.       Click Finish. When prompted to recreate tables in your dataset, click OK.

At this point Visual Studio checks to see if change tracking is already enabled for the tables you have selected in your database. If not, a dialog will be displayed asking if you wish to setup the server. Note that you will require administrative privileges to the database to do this.

You also have the option of generating SQL scripts that can be used to set up change tracking later.

5.       Keep the checkbox Update server for incremental changes checked. Uncheck the checkbox for Save SQL scripts in the project for later use. Click OK.

When you click OK the following things happen:

1.       A .sync file is added to your project. This stores your synchronization settings – tables to synchronize, mode of synchronization (incremental) and information used to track changes.

2.       A SQL Compact data file (.sdf) is added as a local data cache to your project and an initial sync performed.

3.       The dataset is reconfigured such that the cached tables now point to the local cache instead of the main database.

The application is now completely setup to work in occasionally connected mode. The local data cache, a SQL Compact database file, is a file in the project. The schema for the cached tables is automatically created for you. All assembly references to the SQL Compact runtime as well as the ADO .NET sync services runtime are added to the project. Visual Studio 2008 SP1 also includes a ClickOnce bootstraper. When a local cache is added to a project the SQL compact bootstrapper is automatically selected as a prerequisite. Thus the local cache can be easily deployed with the application using ClickOnce.

We now need to add code to initiate a sync. To do this, add a button to the toolbar, call it Synchronize. Double click the button to create a click event handler and in the handler add the following code.

' Synchronize the cache

Dim cache As New NorthwindCacheSyncAgent

cache.Synchronize()

'Update the dataset

Dim ds As NorthwindDataSet = FindResource("NorthwindDataSet")

Dim ordersAdapter As New NorthwindDataSetTableAdapters.OrdersTableAdapter

ordersAdapter.Fill(ds.Orders)

Dim orderDetailsAdapter As New NorthwindDataSetTableAdapters.Order_DetailsTableAdapter

orderDetailsAdapter.Fill(ds.Order_Details)

Figure 8: Synchronizing the cache

As you can see in figure 8 all that is required to synchronize the cache is a single call to the Synchronize method of the NorthwindCacheSyncAgent. Because data may have changed in the cache you may need to fill the data tables again.

By default Visual Studio sets up the local cache for one way synchronization. That is rows will only be downloaded during a sync operation. To enable bi-directional synchronization, right click on the NorthwindCache.sync file in your project and from the context menu select View Code.

This opens the code file for the partial class for the SyncAgent. In the OnInitialized method add the following two lines:

Me.Orders.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional

Me.Order_Details.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional

Database servers are typically configured to handle concurrency and policies define how conflicts are handled. However for the local cache the application must handle conflicts.

The following code shows how update conflicts are resolved by forcing the cache change to be applied (cache wins). Add this to the bottom of the NorthwindCache.vb file, after the NorthwindCacheSyncAgent class.

Partial Public Class NorthwindCacheServerSyncProvider

    ' Handler for server updates

    Private Sub NorthwindCacheServerSyncProvider_ApplyChangeFailed( _

                    ByVal sender As Object, _

                    ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) Handles Me.ApplyChangeFailed

        If e.Conflict.ConflictType = Microsoft.Synchronization.Data.ConflictType.ClientUpdateServerUpdate Then

            ' if there is an update conflict force write the change to the server (client wins)

            e.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite

        End If

    End Sub

End Class

Partial Public Class NorthwindCacheClientSyncProvider

    ' Handler for client updates

    Private Sub NorthwindCacheClientSyncProvider_ApplyChangeFailed( _

                    ByVal sender As Object, _

                    ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) Handles Me.ApplyChangeFailed

        If e.Conflict.ConflictType = Microsoft.Synchronization.Data.ConflictType.ClientUpdateServerUpdate Then

            ' Keep the cache change (cache wins)

            e.Action = Microsoft.Synchronization.Data.ApplyAction.Continue

        End If

    End Sub

End Class

Figure 9: Conflict handling

Of course how you handle conflicts, the logic that is used to filter and apply changes will depend on the specifics of your application.

SQL Compact has the advantages of being lightweight and fast. This lends itself well for use as a local cache. However it has limitations. One such limitation is the maximum database size. SQL compact data files cannot be greater than 4 GB.

Typically you would use a local data cache to store only a subset of the data, such as lookup tables, user data, or in general data that is accessed frequently, but changes rarely. Caching it locally avoids having to hit the main server for data that hasn’t changed, thus making it more efficient. Another example is data that is user-specific.

Summary

In this article we have used a fairly simple example of an application that uses datasets. We have seen how the data binding capabilities of WPF can be used to bind these controls to the data tables in the dataset using CollectionViewSources. We have seen how Visual Studio 2008 SP1, SQL Compact, SQL Server 2008 and ADO .NET Synchronization Services can be used to quickly and easily setup an application to work even when occasionally connected. Visual Studio takes care of most of the plumbing work to get the infrastructure setup and working. All you really need to do is call the synchronization method at the appropriate place and define how conflicts should be handled.

About Milind Lele

Milind Lele is a Program Manager in Visual Studio. Milind works on different data tools within Visual Studio such as server explorer, dataset designer, drag-drop data binding, local data cache, data tools extensibility, etc. Before joining Microsoft over seven years ago, Milind spent several years building applications in areas of networking, security and enterprise administration.