Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 752
Part V Data Connectivity
Sync Framework example
The best way to understand the Microsoft Sync Framework is to see it in action. So before we dive into
any technical mumbo jumbo (I’m starting a new trend; ‘‘mumbo jumbo’’ is the new technical term for
really cool technical stuff), let’s dive right into an example.
If you have not installed synchronization services as part of your SQL Server 2008 installation, then
rerun setup (it is an optional feature at the bottom of the Shared Features section), or download the
appropriate Sync Framework. Version 2.0 can be found at
www.microsoft.com/downloads/
details.aspx?FamilyId=109DB36E-CDD0-4514-9FB5-B77D9CEA37F6&displaylang=en
.
This file is a zip file that contains, and installs, the following:
■ Microsoft Sync Framework Runtime v2.0
■ Microsoft Sync Framework SDK v2.0
■ Microsoft Sync Framework Services v2.0
■ Microsoft Sync Framework Services for ADO.NET v3.0
This example also assumes that you have the SQL Server
AdventureWorks databases installed. These
databases are installed as part of the Microsoft SQL Server installation. They are not installed by default,
so if you do not have them, rerun the SQL Server installation and select the Sample Databases feature.
Once everything has been installed, start Visual Studio 2008 and create a new Windows Forms appli-
cation. It does not matter if you create a VB.NET or C# project, but this example will be using C#. On
the New Project dialog, make sure that .NET Framework 3.5 is selected. This example names the project
‘‘SyncFrameworkDemo,’’ but feel free to name the project whatever suits you.
Once the project has been created, the first thing that needs to be done is to add a local data cache.
Right-click on the project (it will be called SyncFrameworkDemo) in Solution Explorer and select Add
➪ New Item from the context menu. This will open the Add New Item dialog.
In the Add New Item dialog, select the Data category tab, and then select the Local Database Cache tem-
plate. Name the data cache ‘‘DataCache’’ and click Add.
Clicking Add will open the Configuration Data Synchronization dialog. This dialog serves several

purposes. As it states near the top of the dialog, this is where a connection to a remote database is either
created or selected, and the client connection is created. The client connection is the local database
where cached tables are used for synchronization.
This dialog is also where the tables are selected to be included in the synchronization and cached
locally. If any connections have been previously created, they can be selected in the Server connection
drop-down. Otherwise, click the New button next to Server connection, which enables you to define a
connection to the remote database.
You can also click the New button next to Client connection to define the connection to the client data
cache. Expand the Advanced section to view advanced configuration items, such as synchronization
transactions. For this example, no changes will be made to these items.
Once connections have been defined, this dialog should look like Figure 33-1.
752
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 753
Sync Framework 33
FIGURE 33-1
The Configure Data Synchronization dialog
Once the connections to the server and the client have been created, tables can now be selected. To do
this, click the Add button below the Cached Tables list. This will open the dialog shown in Figure 33-2.
The Configure Tables dialog enables you to select the tables to be included in the client-side cache.
Think about this for a minute. When dealing with Sync Services, you need to consider where this
functionality will more than likely be used. In a real-world scenario, you would probably see Sync
Services used in applications that run on laptops, but you might also see Sync Services running on
tablets or PDAs — devices that really don’t have a lot of storage capabilities. For this reason, you don’t
want to select every single table. You want to select only the tables that are practical for the application.
In this example I have connected to the
AdventureWorks database, which has just over 70 tables. For
this example, only the
HumanResources.Employee table will be used. Notice that prior to selecting
any tables, the information on the right is grayed out. As you select a table, the options on the right are

enabled, providing the capability to define synchronization properties per table.
■ Data to download: ‘‘New and incremental changes after first synchronization’’ retrieves records
from the server that have been modified since the last time data was synchronized. The entire
table is downloaded the first time synchronization is called. ‘‘Entire table each time’’ drops the
local table and replaces it with the version on the server.
753
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 754
Part V Data Connectivity
■ Compare updates using: This option enables you to select the column name in the selected
table that is used to track when the last update of a record was made. Any c olumn in the table
that is defined as a
datetime or timestamp data type will appear in this list. You can also
choose to let a new column called
LastEditDate be created in the table.
■ Compare inserts using: Enables you to select the column name in the selected table that
is used to track when new records a re added to the table. Any column in the table that is
defined as a
datetime or timestamp data type will appear in this list. You can also choose
to let a new column called CreationDate be created in the table.
■ Move deleted items to: Enables you to pick the table on the server that will be used to store
deleted records. If a table named
tablename_Deleted or tablename_Tombstone already
exists on the server, that table will be used for the deleted items; otherwise, a table named
tablename_Tombstone will be created.
FIGURE 33-2
The Configure Tables for Offline Use dialog box is where you select tables.
After you have selected the tables you want to cache, click OK. This will bring you back to the Config-
uration Data Synchronization dialog with the tables you selected listed under the Cached Tables section.
Click OK on this dialog.

A small dialog will appear informing you that it needs to update the server, giving you the option to
save SQL scripts to be used later if needed. By default, b oth of these options are selected. Click OK on
this dialog.
754
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 755
Sync Framework 33
The wizard will then create the local database cache (.sdf), create the selected tables inside that database,
and then copy over the records for each of those tables. Once all of the data is copied over, the next
step in the wizard will appear, asking you which of the selected tables in the database cache should be
included in the dataset, as shown in Figure 33-3.
FIGURE 33-3
Use this dialog box to choose which objects sets you want included in your dataset.
Select all of the tables in this dialog and click Finish. The dataset is then created with the selected
tables.
At this point, your Solution Explorer should look like Figure 33-4, containing the client database cache,
the appropriate references, SQL scripts, and a blank form called Form1. From here it is time to start
writing code to synchronize the local cache with the main database and vice versa. L uckily, Microsoft
makes this really easy — very little code is required to get synchronization working.
In the Visual Studio IDE, make sure that the Data Sources Explorer bar is displayed. If it is not, then
select Show Data Sources from the Data menu. The Data Sources Explorer bar will open on the left side
of the IDE by default.
Open form1 in design view; and i n the Data Sources Explorer (see Figure 33-5), select the
Human
_Resources_Employee
table, ensuring that the DataGridView option is selected from the drop-down
menu. Drag the Employee table onto the form.
755
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 756

Part V Data Connectivity
FIGURE 33-4
Check your Solution Explorer for the client database cache, the appropriate references, SQL scripts,
and a blank form.
FIGURE 33-5
The Data Sources Explorer lets you select the DataGridView.
Here is where it gets cool. Visual Studio automatically creates the data grid and navigation controls for
you. Not only that, it also creates a ton of the code behind the form as well, such as saving and
loading of the data. How awesome is that! Visual Studio will automatically place the navigation b ar at
the top of the form, and randomly place the grid below that. Select the grid on the form, and in the
properties of that grid set the
Dock property to Fill. This will ensure that the grid fills the rest of
the form and even expand with the form as it is resized. Notice that the navigation bar has most of the
button functionality needed to work with the grid, such as add, delete, and save.
As the form sits now, it really can’t do any synchronization. It can and will load the data and save the
data back to the cache if any changes are made and the Save button on the navigation bar is clicked.
Go ahead and r un the application as is. Notice that the data is loaded when the form loads. While this
is nice, it can also be a nuisance. For example, the
SalesOrderHeader table contains roughly 31,500
records. That might take a moment to load. However, what if there were 100,000 records or more?
From a user-experience standpoint, you really don’t want the application, or any form for that matter, to
take a long time to display while it loads a ton of data.
756
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 757
Sync Framework 33
The next steps, therefore, are going to implement synchronization logic and change when the loading
of the data takes place. The really neat part about this is that it takes only a few lines of code to do all
of this. Sweet.
The first thing that needs to be done is to add a couple of buttons to the navigation bar. Again, this is

really easy. Simply click on the navigation bar next to the Save button and a new object placeholder will
appear with a drop-down arrow. When the arrow is selected, a list of objects appears that can be added
to the navigation bar, as shown in Figure 33-6. For this example, select the top option, Button. This will
be the button that loads the data when clicked. Add another button for the synchronization.
FIGURE 33-6
A list of objects can be added to the navigation bar.
To make things easy, rename the first button from toolStripButton1 to ‘‘btnLoadData’’ and set its Text
property to Load Data. Rename the second button to ‘‘btnSync’’ and set its Text property to Sync
Data.
Setting the Text property will display the text when you hover the mouse over each button.
Double-click the first button, which will open the code behind the form and create the
Click()
event for this button. This event will not contain code, but the code we do want is in the load event
of the form. Therefore, find the Form1_Load event and copy a nd paste the following line of code from
the form load event to the btnLoadData click event:
this.humanResources_EmployeeTableAdapter.Fill(
this.adventureWorksDataSet.HumanResources_Employee);
This line of code was placed in the load event of the form by Visual Studio when the controls were
placed on the form. Moving this line of code from the load event to the click event of the button will
speed up the l oading of the form.
Go ahead and test what you currently have by saving all the changes and running the project. From
the Debug menu, select Start Debugging. When the form opens, it will show an empty grid. Click the
Load Data button. After a couple of seconds the grid should populate with all of the employee records.
Remember that the data is coming from the local cache (the .sdf).
Close the form. The next step is to add the sync functionality. Once again, this is easy. Referring to
Figure 33-1, notice that the bottom right-hand corner of that form contains a link named Show Code
Example. That is exactly what is needed for this example, so in Visual Studio open the Solution Explorer
and double-click
DataCache.sync. This will open the Configure Data Synchronization dialog shown in
Figure 33-1. Click the Show Code Example link. The Code Example dialog will be displayed, showing

a fragment of code that can actually be used. Simply click the Copy Code to Clipboard button. This
copies the displayed code to the Windows clipboard. Close the Code Example dialog, and then close the
Configure Data Synchronization dialog.
757
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 758
Part V Data Connectivity
With the synchronization code on the clipboard, go back to the project form and double-click on the
Data Sync button, which will open the code b ehind the form and create the
Click() event for this
button. In the
Click() event for the DataSync button, paste the code from the Windows clipboard
into this event. The
Click() event should now look like the following:
private void SyncData_Click(object sender, EventArgs e)
{
// Call SyncAgent.Synchronize() to initiate the synchronization
process.
// Synchronization only updates the local database, not your
// project’s data source.
DataCacheSyncAgent syncAgent = new DataCacheSyncAgent();
Microsoft.Synchronization.Data.SyncStatistics syncStats =
syncAgent.Synchronize();
// TODO: Reload your project data source from the local database
(for example, call the TableAdapter.Fill method).
}
Not quite done, because the code that does the actual merge to the local cache needs to be added. Right
below the
TODO comment in the code, add the following line:
this.adventureWorksDataSet.Merge(

humanResources_EmployeeTableAdapter.GetData());
With that, your first sync example is done and ready to test. Run the project again and click the Load
Data button. With the data loaded, keep the form open and open SQL Server Management Studio
and the data server where the source data resides. Open the
AdventureWorks database, and in the
Tables node, r ight-click on the
HumanResources.Employee table and select Open Table from the
context menu.
To test synchronization, within SSMS (SQL Server M anagement Studio) change the Title of one of the
employees. For example, change the Title for EmployeeID 2 from ‘‘Marketing Assistant’’ to ‘‘Marketing
Director.’’
Now go back to the project form and click the Sync Data button. The title ‘‘Marketing Assistant’’ in the
local cache will also change to ‘‘Marketing Director.’’ Try to sync in the other direction by changing
some data on the form. Make sure you click the Save button so that the data is saved back to the cache.
Now click the Sync Data button.
Requery the source data and you will see that it has not been changed. This is because, by default, syn-
chronization is only one direction; from the source to the client. To fix this and set synchronization to
bi-directional is a really easy c hange. In the Solution Explorer, right-click on the
DataCache.sync and
select View Code from the context menu. What you will see is the following:
public partial class DataCacheSyncAgent {
partial void OnInitialized(){
}
}
758
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 759
Sync Framework 33
Within the OnInitialized() method, add the following statement:
HumanResources_Employee.SyncDirection =

Microsoft.Synchronization.Data.SyncDirection.Bidirectional;
This statement simply tells the synchronization agent to sync both ways, not just up or down. Now try
the change again. Start the application, load the data, make a change on the client, save the changes,
and then click the Sync Data button. Then, go back to the source data and verify that the data on the
server reflects the change or changes made on the client.
One last note for this example: Bi-directional sync can be tricky. What if both the client and the server
update the same record? Who wins, and how does sync handle this?
Luckily, synchronization provides a way to take care of this. Any and all conflicts automatically
raise an
ApplyChangesFailed event. This can be accomplished by adding a method to the
SyncProvider partial class. In the same class to which you added code earlier, add the following
below the
OnInitialized method:
void DataCache_ApplyChangeFailed(object sender,
Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs e)
{
DataTable cc = e.Conflict.ClientChange;
DataTable sc = e.Conflict.ServerChange;
if ((System.DateTime)(cc.Rows[0]["ModifiedDate"]) >=
(System.DateTime)(sc.Rows[0]["ModifiedDate"]))
{
e.Action = Microsoft.Synchronization.Data.ApplyAction
.RetryWithForceWrite;
}
}
In this method, the ModifiedDate on the server is compared to the ModifiedDate on the client.
If the client
ModifiedDate is later than the ModifiedDate on the server, then the server record is
forcefully updated with that of the client.
To test this, run the project and modify a record on the client and save it. Next, modify the same record

on the server. Click the Sync Data button. Because the server record was modified later, this will win
the synchronization battle. Had the client date been later than the server date, the client would have
won the synchronization battle and updated the server.
This example i s a very simple demonstration of how the Sync Framework and synchronization works.
Obviously, additional business logic can be placed in many areas to provide additional sync capabilities
or to satisfy business rules, but it should give you a good idea of how powerful and flexible the Sync
Framework is and how easy it is to use.
Keep in mind that SQL Server 2008 introduces change tracking. This is a lightweight and very thin
solution that provides a very robust change tracking mechanism for applications. While this chapter
does not cover SQL Server change tracking specifically, it would be worth your while to understand
change tracking and how it can be used in your environment.
So with that introduction, it is time to get into the nitty-gritty. We begin with a brief overview of the
Sync Framework b efore diving into some o f its great technologies.
759
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 760
Part V Data Connectivity
Sync Framework overview
The Microsoft Sync Framework is a very broad and wide-ranging synchronization platform that enables
developers to architect and develop applications targeted toward offline and collaboration scenarios. It
would be quite narrow-minded to think that this technology can be used only in applications, however,
because included in the list of uses for offline and collaboration scenarios are services and mobile
devices, and each of these is a prime target for synchronization technology. The Sync Framework allows
for the building of systems that integrate any application to any data store by using any protocol over
any network. How sweet is that!
Also data is not limited to information stored in databases. Data also comes in the form of files. The
Microsoft Sync Framework enables business applications to share documents to guarantee that all team
members receive the necessary data.
The Sync Framework contains the following technologies:
■ Sync Framework core components: Used to create sync providers for any type of data store

■ Microsoft Sync Services for ADO.NET: Used to sync databases for offline and joint
scenarios
■ Metadata Storage Services: Used to store sync metadata
■ Sync Services for File Systems: Used to sync file system files and folders
■ Sync Services for FeedSync: Used to sync RSS and Atom feeds with a local data store
At least 25 pages could be written on each one of these technologies, but due to space constraints
Metadata Storage Services, Sync Services for File Systems, and Sync Services for FeedSync will not
be covered i n this chapter. Plenty of information, though, can be found on Microsoft’s MSDN site at
/>The rest of this chapter provides an overview of the Sync Framework core components, a discussion of
the Sync architecture and synchronization fundamentals, as well as a good discussion of Sync Services
for ADO.NET 2.0.
Sync architecture
The Microsoft Sync Framework architecture enables the flow of data between many devices and services
by using a set of building blocks that incorporate all of the synchronization functionality, such as the
data store and transfer systems. These building blocks are the essential components that make up the
synchronization runtime, metadata services, and synchronization providers.
During synchronization, the sync runtime drives and controls synchronization between the providers. It
is the job of the metadata services to process and store metadata that is used by the providers.
For example, Figure 33-7 illustrates a simple high-level architecture of the Microsoft Sync Framework in
which synchronization is attained by exposing a provider interface from a data store to a synchronization
object.
This example is quite simple, in that it is illustrating a synchronization between a single data store and a
controlling application such as a mobile device and a Microsoft SQL Server database.
760
www.getcoolebook.com
Nielsen c33.tex V4 - 07/21/2009 2:06pm Page 761
Sync Framework 33
FIGURE 33-7
A simple high-level architecture
Master Sync

Application
Sync Session Sync Provider
Data Store
FIGURE 33-8
An architecture of multiple data stores synchronizing with a single controlling application, with each
data store using its own synchronization provider
Master Sync
Application
Sync Session
Sync Provider
Data Store
Sync Provider
Data Store
However, the Sync Framework is much more flexible and interoperable than that. For example, the
illustration shown in Figure 33-8 shows multiple data stores synchronizing with a single controlling
application, with each data store using its own synchronization provider. This is important because the
two data stores could be two completely different and unique types of data stores.
In both scenarios, the sync session connects to both sync providers. It then makes appropriate API calls
to determine what changes have been made and what changes need to be applied. Again, this informa-
tion is supplied by the metadata services.
The sync runtime has the primary responsibility of driving and managing synchronization. This includes
starting and hosting the sync process, and disposing of the session when synchronization is complete.
The runtime also controls all communication with the client application, including status, conflicts,
and any errors. The runtime initiates and begins synchronization for the client application by making
requests through the sync session to the sync providers.
The role of the metadata service is to manage synchronization metadata. The metadata service has the
sole responsibility of understanding the details of the metadata, thereby freeing the application and
provider from needing to understand items such as the structure of the data. By assisting the application
in this way, it allows for maximum flexibility when designing applications. It also provides improved
synchronization performance by enabling the other components to focus on what they excel at.

The sync provider is the main integration point into the Sync Framework, as it contains a layer that
shields the runtime from the complexities of the datastore.Intermsofaprovider,thesyncprovider
is the component that enables synchronization between data stores. The great thing about the Sync
761
www.getcoolebook.com