Chapter 23. Mobile data strategies

John Baird

In recent months, there have been many headlines decrying the loss of thousands and millions of sensitive data records on disks, laptops, and portable media. This is a concern to many users of Windows Mobile and handheld devices. If my phone is lost or stolen, how safe is the data contained within it? If I lose network connectivity, what happens to my data? In this chapter, we’ll discuss how to solve the data issues that arise from carrying a device that must store its data locally and operate independently of any network connectivity.

Microsoft has recognized this need and has provided a number of strategies for the mobile developer to use in solving the disconnected data problem: two older techniques and one recent addition are available to the mobile developer. This chapter will focus mainly on the recent release of the Microsoft Sync Framework (MSF) and how it applies to mobile development and synchronization of data between the .NET Compact Framework (.NET CF) and SQL Server.

Much information exists on older technologies, such as remote data access (RDA) and merge replication (MR), and is readily available via an internet search. The following books provide excellent coverage of these topics:

• Rob Tiffany’s Windows Mobile Data Synchronization with SQL Server 2005 and SQL Server Compact 3.1, Hood Canal Press, 2007.
• Andy Wigley, Daniel Moth, and Peter Foot’s Mobile Development Handbook, Microsoft Press, 2007.

By utilizing SQL Server Compact Edition (SQL CE) as the local data store, we can take advantage of both RDA and MR to maintain the integrity of our data, whether connected or not. With SQL CE, the mobile application can read from the database using typical SQL data access techniques. Subsequently, when and if we are connected, the chosen data management strategy kicks in to synchronize our data with the remote server.

---

Note

Tables 1 through 5 are reproduced from MSDN, courtesy of Microsoft, Inc.

---

Microsoft Sync Framework (MSF)

MSF is Microsoft’s newest entry into the information replication and synchronization arena. It combines the ease and usefulness of RDA with the power and scalability of MR. It is highly developer centric and requires little setup. RDA will soon be deprecated in favor of MSF because of its ease of use and scalability.

MSF uses the provider model as shown in figure 1, and is transport agnostic. The provider model allows developers to create synchronization technology for any client and for any server. A number of providers are already available as listed here:

• File Systems
• Sync Services for Feed Sync
• Sync Services for ADO.NET

Figure 1. Synchronization architecture

Most major software vendors are developing providers for their technology to interface with MSF.

The main pieces of this architecture are the client and server synchronization providers, the synchronization adapter, and the synchronization agent.

Client synchronization provider

The client synchronization provider’s main responsibilities are the following:

• Store information in client tables that are enabled for synchronization.
• Retrieve changes from client database since the last synchronization.
• Apply incremental changes to the client database.
• Detect conflicting changes.

Server synchronization provider

The server synchronization provider’s main responsibilities are the following:

• Store information in server tables that are enabled for synchronization.
• Retrieve changes from server database since the last synchronization.
• Apply incremental changes to the server database.
• Detect conflicting changes.

Synchronization adapter

The synchronization adapter is the communication gateway between the server synchronization provider and the server database. It is analogous to the data adapter in ADO.NET. Each table added to the syncTables collection will have an adapter defined for it. The adapter stores and serves the commands necessary for it to interact with the provider. These commands include the basic create, read, update, and delete (CRUD) operations. The commands can use any command type supported by ADO.NET, such as T-SQL, stored procedures, views, and functions. The properties and public methods available are shown in tables 1 and 2.

Table 1. SyncAdapter properties

Name	Description
ColumnMappings	Gets a collection of SyncColumnMapping objects for the table. These objects map columns in a server table to the corresponding columns in a client table.
DeleteCommand	Gets or sets the query or stored procedure that is used to delete data from the server database.
Description	Gets or sets a description for the synchronization adapter.
InsertCommand	Gets or sets the query or stored procedure that is used to insert data into the server database.
SelectConflictDeletedRowsCommand	Gets or sets the query or stored procedure that is used to identify deleted rows that conflict with other changes.
SelectConflictUpdatedRowsCommand	Gets or sets the query or stored procedure that is used to identify updated rows that conflict with other changes.
SelectIncrementalDeletesCommand	Gets or sets the query or stored procedure that is used to retrieve deletes made in the server database since the last synchronization.
SelectIncrementalInsertsCommand	Gets or sets the query or stored procedure that is used to retrieve inserts made in the server database since the last synchronization.
SelectIncrementalUpdatesCommand	Gets or sets the query or stored procedure that is used to retrieve updates made in the server database since the last synchronization.
TableName	Gets or sets the name of the table at the server for which to create the SyncAdapter.
UpdateCommand	Gets or sets the query or stored procedure that is used to update data in the server database.

Table 2. SyncAdapter methods

Name	Description
FillSchema	Populates the schema information for the table that is specified in TableName.
GetClientColumnFromServerColumn	Gets the client column name that corresponds to the specified server column name.
ToString	Overridden. Returns a string that represents the SyncAdapter object.

Synchronization agent

The synchronization agent’s responsibilities include the following:

• Track tables involved in the synchronization through a synchronization group.
• Instantiate the client and server synchronization providers.
• Retrieve changes from the client synchronization provider and apply them to the client database.
• Retrieve changes from the server synchronization provider and apply them to the server database.

The synchronization agent class has one major method: synchronize. This method makes sure the data in both databases is the same. A number of properties and events are available (table 3). For more information on these items, go to MSDN and the MSF online information. When adding tables to a synchronization group, a couple of enumerations are used to specify how to create the database (table 4) and for which direction the updates are allowed (table 5).

Table 3. Synchronization agent properties

Property	Description
Configuration	Gets a SyncConfiguration object that contains information about tables and synchronization parameters.
LocalProvider	Gets or sets an object derived from ClientSyncProvider that is used to communicate with the local data store.
RemoteProvider	Gets or sets an object derived from ServerSyncProvider that is used to communicate with the remote data store.
SessionState	Gets or sets a SyncSessionState object that is used to define whether the session is currently synchronizing. This property is not compliant with Common Language Specification (CLS).
SyncStatistics	Gets a SyncStatistics object that represents statistics for a synchronization session.

Table 4. TableCreationOption enumerations

Enumeration	Description
CreateNewTableOrFail	Create the table in the client database. If an existing table has the same name, throw an exception.
DropExistingOrCreateNewTable	Create the table in the client database. If an existing table has the same name, drop the existing table first.
TruncateExistingOrCreateNewTable	Create the table in the client database if the table does not exist. If an existing table has the same name, delete all rows from this table.
UploadExistingOrCreateNewTable	Create the table in the client database if the table does not exist. If an existing table has the same name, upload all rows from this table on the first synchronization. This option is only valid with a SyncDirection of Bidirectional or UploadOnly.
UseExistingTableOrFail	Use an existing table in the client database that has the same name. If the table does not exist, throw an exception.

Table 5. SyncDirection enumerations

Enumeration	Description
Bidirectional	During the first synchronization, the client typically downloads schema and an initial data set from the server. On subsequent synchronizations, the client uploads changes to the server and then downloads changes from the server.
DownloadOnly	During the first synchronization, the client typically downloads schema and an initial data set from the server. On subsequent synchronizations, the client downloads changes from the server.
Snapshot	The client downloads a set of data from the server. The data is completely refreshed during each synchronization.
UploadOnly	During the first synchronization, the client typically downloads schema from the server. On subsequent synchronizations, the client uploads changes to the server.

Using MSF

I will explain how to create a Windows Communication Foundation (WCF) service, which will be used as the transport mechanism between the client synchronization agent (which lives on the mobile device) and the server synchronization provider (which is part of the WCF service). To use the MSF, you must supply the client and server synchronization providers and the sync agent.

To do this, you need code that creates a proxy service to connect to the WCF service, which will request synchronization of the SQL CE database on the mobile device with the SQL Server on the network. The code discussed in this section is referred to in an excellent post on the MSF blog; links to both the blog and the code can be found on this book’s web page: http://www.manning.com/SQLServerMVPDeepDives.

The WCF Service

Listings 1, 2, and 3 show the code necessary to create the service. After you have created the project, added the files, added the references indicated in the USING statements, and compiled the service project, you need to deploy the service to Internet Information Services (IIS). You will add the code in listing 3 to your project’s app.config.

Listing 1. WCF service IService.cs

using System.Runtime.Serialization;
using System.ServiceModel;
using System.Data;
using Microsoft.Synchronization.Data;
namespace WcfSyncService
{
  [XmlSerializerFormat]
  [ServiceContract]
  public interface IService
  {
    [OperationContract]
    string GetData(int value);

    [OperationContract]
    CompositeType GetDataUsingDataContract(CompositeType composite);
    [OperationContract]
    SyncServerInfo GetServerInfo(SyncSession syncSession);

    [OperationContract]
    SyncContext ApplyChanges(
                            SyncGroupMetadata groupMetadata,
                            DataSet dataSet,
                            SyncSession syncSession);

    [OperationContract]
    SyncContext GetChanges(
                          SyncGroupMetadata groupMetadata,
                          SyncSession syncSession);

    [OperationContract]
    SyncSchema GetSchema(
                        string[] tableNames,
                        SyncSession syncSession);
  }

  [DataContract]
  public class CompositeType
  {
    bool boolValue = true;
    string stringValue = "Hello ";

    [DataMember]
    public bool BoolValue
    {
      get { return boolValue; }
      set { boolValue = value; }
    }

    [DataMember]
    public string StringValue
    {
      get { return stringValue; }
      set { stringValue = value; }
    }
  }
}

Listing 2. WCF service.cs

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using System.Collections.ObjectModel;
using System.IO;
namespace WcfSyncService {
  public class Service : IService {
    static DbServerSyncProvider _serverSyncProvider;
    public Service(){
      if (_serverSyncProvider == null) {
        CreateServerProvider();
      }
    }
    private void CreateServerProvider() {
      SqlConnection conn = new SqlConnection(
             @"server=Thinkpad;database=AdventureWorks2008;
               integrated security = true");
      conn.Open();
      _serverSyncProvider = new DbServerSyncProvider { Connection = conn };

      SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder {
                        Connection = conn,
                        SyncDirection = SyncDirection.Bidirectional,
                        TableName = "SyncTable",
                        CreationTrackingColumn = "CreationDate",
                        UpdateTrackingColumn = "LastEditDate",
                        DeletionTrackingColumn = "DeletionDate",
                        TombstoneTableName= ("SyncTable_Tombstone")};
      _serverSyncProvider.SyncAdapters.Add(builder.ToSyncAdapter());
      SqlCommand selectNewAnchorCmd = new SqlCommand {
                          CommandText = ("SELECT @" +
                                 SyncSession.SyncNewReceivedAnchor +
                                 " = GETUTCDATE()")};

      SqlParameter newRecAnchor = new SqlParameter {
                        ParameterName = ("@" +
                           SyncSession.SyncNewReceivedAnchor),
                           DbType = DbType.DateTime,
                           Direction = ParameterDirection.Output};
      selectNewAnchorCmd.Parameters.Add(newRecAnchor);
      _serverSyncProvider.SelectNewAnchorCommand =
                                 selectNewAnchorCmd;
    }

    public string GetData(int value)
    {
      return string.Format("You entered: {0}", value);
    }

    public CompositeType GetDataUsingDataContract(
                                      CompositeType composite) {
      if (composite.BoolValue)
      {
        composite.StringValue += "Suffix";
      }
      return composite;
    }

    public SyncServerInfo GetServerInfo(SyncSession syncSession) {
      try {
        Logger.Log("In GetServerInfo()");
        return _serverSyncProvider.GetServerInfo(syncSession);
      }
      catch (Exception e) {
        Logger.Log(e.ToString());
        throw;
      }
    }

    public SyncContext ApplyChanges(SyncGroupMetadata groupMetadata,
                                    DataSet dataSet,
                                    SyncSession syncSession)  {
      try {
        Logger.Log("In ApplyChanges()");
        return _serverSyncProvider.ApplyChanges(groupMetadata,
                                                dataSet,
                                                syncSession);
      }
      catch (Exception e) {
        Logger.Log(e.ToString());
        throw;
      }
    }

    public SyncContext GetChanges(SyncGroupMetadata groupMetadata,
                                  SyncSession syncSession) {
      SyncContext context;
      Try {
        Logger.Log("In EnumerateChanges()");
        context = _serverSyncProvider.GetChanges(groupMetadata,
                                                 syncSession);
      }
      catch (Exception e) {
        Logger.Log(e.ToString());
        throw;
      }
      return context;
    }

    public SyncSchema GetSchema(string[] tableNames,
                                SyncSession syncSession)  {
      try {
        Logger.Log("In GetSchema()");
        Collection<string> tables = new Collection<string>();
        foreach (string s in tableNames) {
          tables.Add(s);
        }
        return _serverSyncProvider.GetSchema(tables, syncSession);
      }
      catch (Exception e) {
        Logger.Log(e.ToString());
        ApplicationException newex = new ApplicationException(
                                    "failed in GetSchema()", e);
        throw newex;
      }
    }
  }

  public class Logger
  {
    static readonly Object thisLock = new Object();
    private const string logfile = @"wcftestlog.log";

    public static void Log(string msg)
    {
      using (StreamWriter sw = File.AppendText(logfile))
      {
        sw.WriteLine(DateTime.Now + " : " + msg);
        sw.Flush();
      }
    }
  }
}

 

Listing 3. WCF service App.config

<system.serviceModel>
  <services>
   <service name="Service" behaviorConfiguration="ServiceBehavior">
    <endpoint address="" binding="basicHttpBinding"
        contract="IService">
     <identity>
      <dns value="localhost" />
     </identity>
    </endpoint>
    <endpoint address="mex"
              binding="mexHttpBinding"
              contract="IMetadataExchange" />
    <host>
     <baseAddresses>
      <add baseAddress="http://localhost/WcfSyncService/Service/" />
     </baseAddresses>
    </host>
   </service>
  </services>
  <behaviors>
   <serviceBehaviors>
    <behavior name="WcfSyncService.Service1Behavior">
     <serviceMetadata httpGetEnabled="True"/>
     <serviceDebug includeExceptionDetailInFaults="True" />
    </behavior>
   </serviceBehaviors>
  </behaviors>
</system.serviceModel>

The Mobile Application

This example involves setting up the client to test the MSF via the WCF service. You need to create a console application and add the two classes in listings 4 and 5. After you have compiled and built the project, add a service reference to the WCF service you created and hosted in IIS.

Listing 4. Mobile console app TestSync.cs

using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
namespace SyncSample {
  public class TestSync {
    static void Main(string[] args) {
      ServiceClient svc = new ServiceClient();
      string testString = svc.GetData(3);
      SyncClient client = new SyncClient();
      client.SetupAgent();
      client.Sync();
    }
  }
}

Listing 5. Mobile class SyncClient

using System;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace SyncSample {
  public class SyncClient {
    SyncAgent sa;
    public void SetupAgent(){
      SqlCeClientSyncProvider clientProvider = new
             SqlCeClientSyncProvider(
                          @"data source=testsync.sdf", true);
      ServiceClient svc = new ServiceClient();

      ServerSyncProvider serverProvider = new
                           ServerSyncProviderProxy(svc);
    cagent
      sa = new SyncAgent {
                          LocalProvider = clientProvider,
                          RemoteProvider = serverProvider};
      if (sa != null) {
        sa.Configuration.SyncTables.Add(
                    "MyTableToSync",
                    TableCreationOption.CreateNewTableOrFail,
                    SyncDirection.DownloadOnly);
      }
    }
    public void Sync() {
       try {
        sa.Synchronize();
      }
      catch (Exception e) {
        Console.WriteLine(e.ToString());
      }
    }
  }
}

The Microsoft .NET Compact Framework (.NET CF) 3.5 includes a subset of WCF for smart devices, enabling them to communicate with remote WCF components. The .NET CF version of WCF ships with support only for message-level communications; a code generation tool is available which allows you to consume other WCF services.

On the desktop, this utility is called svcutil.exe and generates a proxy client in code, which can consume the WCF service; however, its output does not compile against the .NET CF. Microsoft has provided the NETCFSvcUtil.exe tool contained in the Power Toys for .NET Compact Framework 3.5. You can find this download at http://www.microsoft.com/downloads/details.aspx.

This tool is analogous to the svcutil.exe and supports many of the same command-line switches but performs a few additional and modified steps, including the following:

• Verifies that the service being consumed offers endpoints compatible with the feature subset included in .NET CF 3.5.
• Generates the client proxy class in C# or VB that compiles against .NET CF or desktop WCF.
• Generates code for a .NETCFClientBase class that fills in where .NET CF doesn’t support calling WCF services.

Unlike svcutil.exe, this process will not generate a .config file to store the endpoint information. This information is stored in the client proxy code.

To use this tool:

1.  

Open a Visual Studio command window.

2.  

Change the directory to your mobile application directory.

3.  

Enter this command:

NETCFsvcutil.exe http://<machinename>/yourservice/service.svc

The preceding command should generate two source files. The one named after your service will contain the proxy class you will use to consume the WCF service you created. Add these classes to your project and compile the code. The code can be found at this book’s website, http://www.manning.com/SQLServerMVPDeepDives.

Comparison

Much thought has been given to creating solutions to ensure that data remains in sync during periods of connectivity. All of the techniques—RDA, MR, and MSF—allow data synchronization and replication; however, each has its own strengths and disadvantages, as shown in table 6.

Table 6. Key feature comparison of the three synchronization methodologies

Key feature	RDA	MR	MSF
Synchronize by using services	No	No	Yes
Supports heterogeneous databases	No	No	Yes
Incremental change tracking	No	Yes	Yes
Conflict detection and resolution	No	Yes	Yes
Easily create data views on the client	No	No	Yes
Automatically initialize schema and data	Yes	Yes	Yes
Supports large data sets	Yes	Yes	Yes
Query processor is locally available	Yes	Yes	Yes
Automatically propagate schema changes	No	Yes	No
Automatically repartition data	No	Yes	No
Use on devices	Yes	Yes	Yes

Summary

Which replication strategy you use is up to you, but if you are looking for a robust, developer-centric, scalable solution, the MSF should be your choice. Notice the comparison in table 6. MSF replaces the functionality in RDA with an enhanced plan and copies the functionality of MR. If you require a DBA-centric approach with little code to set up the process, then MR is your choice. Both MSF and MR will keep your data in sync. With the techniques and code samples we have shown in this chapter, you will be able to take advantage of these methodologies to transfer, create, modify, and replicate your data from your mobile device to your desktop server.

About the author

John Baird began his computer programming career while in the US Navy. In 1982, he helped form and direct the first PC-based computer processing department for training and manpower in the Department of Defense. After leaving the military in 1988, John began a varied career as a consultant developing business applications ranging from computer-based training to vertical market software for resellers.

Today, John is working for the industry leader in financial software for fund administration. John was awarded his MVP status for device application development beginning in 2008.