http://www.developer.com/

Back to article

My First Microsoft Sync Framework Application


April 6, 2009

The Microsoft Sync Framework and Visual Studio 2008 provide Windows desktop developers with a powerful tool for creating occasionally-connected applications that can synchronize data with a central repository when connected. In the following example, we will use the Microsoft Sync for ADO.NET portion of the framework to create a local data store with a SQL Compact Edition database that syncs with the AdventureWorks database in SQL Server 2005. We will manage salesperson information from our application while offline and sync this information back to the central AdventureWorks database.

In order to follow along with the sample shown in this article, you will need Visual Studio 2008 with SP1, the Microsoft Sync Framework (MSF), and SQL Server 2005 with the AdventureWorks sample database. Visual support for MSF was introduced in VS 2008 (prior to SP1); however, the "Configure Tables for Offline Use" dialog we will discuss later only shows the tables included in the user's default schema (e.g., dbo). The AdventureWorks database uses several schemas, and the tables we use in this example are outside of the dbo schema and will not appear in the VS 2008 dialog. VS 2008 SP1 shows tables from all schemas in the dialog.

To begin, open Visual Studio 2008 and create a new Windows Forms project called "My MSF App." Next, we will add the local database that will be used to store or cache the data offline. Right click on the project file 'My MSF App' in Solution Explorer, select Add, and then New Item. Select the template for a Local Database Cache. Change the name to "AWLocalDataCache.sync" (the "AW" stands for AdventureWorks).


Click here for larger image

Listing 1.1 Add a Local Database Cache

Click Add. Visual Studio will now launch the "Configure Data Synchronization" dialog. This is where we set up the server and local database connections. From the Server connection dropdown list, create a new connection to the AdventureWorks database. Once you have selected a server connection, Visual Studio will automatically populate the Client connection with a connection to a new SQL Compact database.


Click here for larger image

Listing 1.2 Create the Database Connections

Note that the client connection has an "sdf" extension (as opposed to an SQL Server database "mdf" file extension).

The next step is configuring which tables in the database to cache and make available offline. Click the Add button in the bottom left-hand corner of the dialog. The "Configure Tables for Offline Use" dialog is displayed. Here, we will choose what tables the application will be caching and syncing. For this example, we will be using just a few tables to pull up salesperson information and enable the ability to view, update, and add to that information offline. Using the Tables list, select the following tables to be included:

  • Employee
  • SalesPerson
  • SalesTerritory

We will leave the fields to the right on the default settings. These fields are used to control how the sync framework keeps track of inserts, updates, and deletes.


Click here for larger image

Listing 1.3 Select the tables to synchronize

Click OK to exit the dialog once you have selected all the tables listed above. Visual Studio will return us to the "Configure Data Synchronization" dialog and will now display the tables we selected in the Cached tables list.


Click here for larger image

Listing 1.4 Configuring synchronization cont'd

Note that if you are using SQL Server 2008, the checkbox below the Database Connections group box, "Use SQL Server change tracking," will be enabled. SQL Server change tracking is a new feature in SQL Server 2008 that will dramatically increase performance of syncing. There is unavoidable performance overhead to tracking changes and keeping the data in sync across multiple data sources. SQL Server change tracking reduces this performance overhead to the equivalent of maintaining a second index on your table. This marks a huge improvement over triggers, additional columns of data, and tombstone tables that are traditionally required in a sync environment using SQL Server 2005.

Now click OK. You will notice Visual Studio performing actions on your behalf while it sets up the local cache and sync file and performs synchronization. When you receive a prompt to generate the SQL scripts, click OK. These are the scripts that update the SQL Server with the necessary changes to enable sync functionality.


Listing 1.5 Choose OK to Generate SQL Scripts.


Listing 1.6 VS synchronizes the SQL Compact database with the AdventureWorks database

The following References are added to the project during this process:

  • Microsoft.Synchronization.Data
  • Microsoft.Synchronization.Data.Server
  • Microsoft.Synchronization.Data.SqlServerCe
  • System.Data.DataSetExtensions
  • System.Data.SqlServerCe
  • System.Transactions

Finally, the "Data Source Configuration Wizard" dialog will be opened to allow us to create the datasets that will be used in the application. Select all the Tables and click the Finish button.


Click here for larger image

Listing 1.7 Set up datasets

So far, Visual Studio has enabled us to perform the following actions, all through dialog configuration without writing a single line of code:

  • Configuring the selected SQL Server database for change tracking
  • Creating an SQL Compact database and schema for the local data cache
  • Creating the SQL Scripts and SQL Undo Scripts for data synchronization
  • Creating datasets for use in the application

We are now going to use a few more powerful features of Visual Studio to create our form's controls and link them back to the database. In the Data Sources window, change the Sales_SalesPerson table to use the Details view and TerritoryID to use the combo box. Now drag the Sales_SalesPerson table onto the form. Once the controls are generated, drag the Sales_SalesTerritory table to the Territory ID combo box. When complete, you should have a form that looks similar to the one below when running.


Listing 1.8 The running application

We now have a form that allows user to view and update salesperson information in the local data cache. Next, we will expose a way for the user to initiate synchronization. Since all views, inserts, updates, and deletes are happening locally on the SQL Compact database, we still need a way to receive changes from the AdventureWorks database.

First, we will add a button to the toolbar. Near the save button on the top, click on the Add ToolStripButton dropdown and select Button. Double click the new button to generate the click event handler. Here, we will initiate synchronization. You must be thinking, "Finally I have to write some code." However, Microsoft has us covered again. Double click on the AWLocalDataCache.sync file in Solution Explorer to reopen the "Configure Data Synchronization" dialog. Located in the bottom right, above the OK and Cancel buttons, is a link that reads "Show Code Example…" Click this button to view the code that will initiate synchronization.


Click here for larger image

Listing 1.9 Visual Studio offers you the code for synchronizing the local cache

Click Copy Code to the Clipboard, click Close, and cancel out of the dialogs. Now paste the code into the event handler for the button we just created. As the handy "TODO" in the provided code instructs, we will want to add a simple reload/merge to refresh the form with any new data pulled from the server.

// TODO: Reload your project data source from the local database (for example, call the TableAdapter.Fill method).
            this.adventureWorksDataSet.Sales_SalesPerson.Merge(this.sales_SalesPersonTableAdapter.GetData());

We now have a simple application that allows us to take the salesperson data offline for viewing while disconnected and the functionality to get the latest data while connected. If you run the application now and view some records, make some changes to the server side data, and hit sync, you will see how the data changes get pulled down to the local data cache. This is pretty great, especially considering that only one line of code was written; however, most applications will want to send data changes back to the server. This is where bidirectional sync comes into play.

To enable bidirectional synchronization, right click on the AWLocalDataCache.sync file and select View Code. For each table that requires bidirectional sync (meaning users can update and receive updates for the data), we will need to add a single line of code in the table's OnInitialized method. Since we are interested in bidirectional sync for the SalesPerson table, we will add the following code to the method:

this.Sales_SalesPerson.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional;

Now when we run the application, the changes we make on the client will also be sent to the server. Please note that if are using the AdventureWorks database, you may need to disable existing update triggers to prevent issues from occurring during synchronization.

Conclusion

With the Microsoft Sync Framework and Visual Studio 2008, we can enable occasionally-connected data synchronization capabilities through some simple configuration dialogs and a couple of (really simple) lines of code. This allows us to rapidly create applications that can automatically manage the flow of data across distributed systems. For example, think of an application that is used by a mobile sales force in rural environments where the salespeople do not have reliable Internet connections. With MSF, we can enable our applications for offline support and reach customers even in dreaded Internet dead zones.

About the Authors

Matt Goebel is a manager with Crowe Horwath LLP in the Indianapolis, Indiana, office. He can be reached at 317.208.2555 or . Rachel Baker is a senior developer with Crowe Horwath LLP in the Oak Brook, Illinois, office. She can be reached at 630.990.4434 or .

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date