Microsoft & .NET My First Microsoft Sync Framework Application

My First Microsoft Sync Framework Application

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.


  • 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 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 .

Latest Posts

Related Stories