How to: Handle Data Conflicts and Errors for Collaborative Synchronization (SQL Server)
This topic shows you how to handle data conflicts and errors when using Sync Framework to synchronize SQL Server and SQL Server Compact databases. The examples in this topic focus on the following Sync Framework types and events:
ApplyChangeFailed event, and DbApplyChangeFailedEventArgs
For more information about how to run sample code, see "Example Applications in the How to Topics" in Synchronizing SQL Server and SQL Server Compact.
Understanding Data Conflicts and Errors
In Sync Framework database providers, conflicts and errors are detected at the level of the row. A row is in conflict when it has been changed at more than one node between synchronizations. Errors that occur during synchronization typically involve a constraint violation, such as a duplicate primary key. Applications should be designed to avoid conflicts if they can, because conflict detection and resolution introduce additional complexity, processing, and network traffic. The most common ways to avoid conflicts are as follows: update a table at only one node, or filter data so that only one node updates a particular row. In some applications, conflicts cannot be avoided. For example, in a sales force application, two salespeople might share a territory. Both salespeople could update the data for the same customer and orders. Therefore, Sync Framework provides a set of features that applications can use to detect and resolve conflicts.
Data conflicts can occur in any synchronization scenario in which changes are made at more than one node. Conflicts can occur in bidirectional synchronization, but they can also occur in download-only and upload-only synchronization. For example, if a row is deleted at one node and the same row is updated at another node, there is a conflict when Sync Framework tries to upload and apply the update at the first node.
Conflicts are always between the two nodes that are currently synchronizing. Consider the following scenario:
Node A and node B both perform bidirectional synchronization with node C.
A row is updated at node A, and then node A synchronizes. There is no conflict, and the row is applied at node C.
The same row is updated at node B, and then node B synchronizes. The row from node B is now in conflict with the row from node C because of the update that originated at node A.
If you resolve this conflict in favor of node C, Sync Framework can apply the row from node C to node B. If you resolve in favor of node B, Sync Framework can apply the row from node B to node. During a later synchronization between node A and node C, the update that originated at node B is applied to node A.
Kinds of Conflicts and Errors
Sync Framework detects the following kinds of conflicts. These are defined in the DbConflictType enumeration:
A LocalInsertRemoteInsert conflict occurs when two nodes insert a row that has the same primary key. This kind of conflict is also known as a primary key collision.
A LocalUpdateRemoteUpdate conflict occurs when two nodes change the same row. This is the most common kind of conflict.
A LocalUpdateRemoteDelete and LocalDeleteRemoteUpdate conflicts occur when one node updates a row that the other node has deleted.
An ErrorsOccurred conflict occurs when an error prevents a row from being applied.
Conflict and Error Detection
If a row cannot be applied during synchronization, it is typically because either an error or a data conflict occurred. In both cases, the ApplyChangeFailed event is raised. The provider raises the error for the node at which the conflict is detected. For example, if you specify a value of UploadAndDownload for the Direction property, changes are first uploaded from the local provider to the remote provider. In this case, the event is raised by the provider that you specified for the RemoteProvider property. If changes were first downloaded and then uploaded, the event would be raised by the provider that you specified for the LocalProvider property. Regardless of which provider raises the event and where the synchronization components are located, the data change at the node on which the event is raised is considered the local change (LocalChange), and the other row is considered the remote change (RemoteChange). This differs from client and server synchronization, in which ClientChange and ServerChange are always associated with, respectively, the client database and the server database.
After the ApplyChangeFailed event is raised, conflicting rows are selected by a stored procedure that Sync Framework creates for each table when a database is provisioned for synchronization. By default, this procedure is named <TableName>_selectrow. Sync Framework executes this procedure when an insert, update or delete operation returns a @sync_row_count value of 0. This value indicates that the operation failed.
Conflict and Error Resolution
Conflict and error resolution should be handled in response to the ApplyChangeFailed event. The DbApplyChangeFailedEventArgs object provides access to several properties that can be used during conflict resolution:
Specify how to resolve the conflict by setting the Action property to one of the values of the ApplyAction enumeration:
Continue: ignore the conflict and continue synchronization.
RetryApplyingRow and RetryNextSync: retry applying the row. The retry will fail, and the event will be raised again if you do not address the cause of the conflict by changing one or both of the conflicting rows.
RetryWithForceWrite: retry with logic to force applying the change. Specifying this option sets the session variable
@sync_force_writeto 1. The "Examples" section of this topic shows how a remote change is forced to overwrite a local change based on the logic in the update stored procedure that Sync Framework creates.
Obtain the conflict type and view the conflicting rows from the each node by using the Conflict property.
Obtain the dataset of changes that is being synchronized by using the Context property. The rows that are exposed by the Conflict property are copies. Therefore, overwriting them does not change the rows that are applied. Use the dataset exposed by the Context property to develop custom resolution schemes if the application requires them.
Note
The Sync Framework API contains one type and one property that are related to conflict resolution but that are not used in this version of the API: DbResolveAction and ConflictResolutionPolicy.
Examples
The following code examples show how to configure conflict detection and resolution.
Key Parts of the API
This section provides code examples that point out the key parts of the API used in conflict detection and resolution. The following code example shows the stored procedure that Sync Framework uses to apply updates to the Customer table. This procedure performs an update based on the value of the @sync_force_write parameter. If the row has been updated in the local database and the parameter is set to 0, the remote update is not applied. However, if the parameter is set to 1, the remote update overwrites the local update.
CREATE PROCEDURE [Sales].[Customer_update]
@CustomerId UniqueIdentifier,
@CustomerName NVarChar(100),
@SalesPerson NVarChar(100),
@CustomerType NVarChar(100),
@sync_force_write Int,
@sync_min_timestamp BigInt,
@sync_row_count Int OUTPUT
AS
BEGIN
UPDATE [Sales].[Customer] SET [CustomerName] = @CustomerName,
[SalesPerson] = @SalesPerson, [CustomerType] = @CustomerType FROM
[Sales].[Customer] [base] JOIN [Sales].[Customer_tracking] [side] ON
[base].[CustomerId] = [side].[CustomerId] WHERE
([side].[local_update_peer_timestamp] <= @sync_min_timestamp OR
@sync_force_write = 1) AND ([base].[CustomerId] = @CustomerId); SET
@sync_row_count = @@ROWCOUNT;
END
GO
The following code example shows how update-update conflicts can be processed in an ApplyChangeFailed event handler. In the example, the conflicting rows are displayed to the console with an option to specify which row should win the conflict. If you run the complete code example at the end of this topic, you will see two sets of conflicting rows: when node 1 synchronizes with node 2 and when node 2 synchronizes with node 3.
if (e.Conflict.Type == DbConflictType.LocalUpdateRemoteUpdate)
{
//Get the conflicting changes from the Conflict object
//and display them. The Conflict object holds a copy
//of the changes; updates to this object will not be
//applied. To make changes, use the Context object.
DataTable conflictingRemoteChange = e.Conflict.RemoteChange;
DataTable conflictingLocalChange = e.Conflict.LocalChange;
int remoteColumnCount = conflictingRemoteChange.Columns.Count;
int localColumnCount = conflictingLocalChange.Columns.Count;
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Row from database " + DbConflictDetected);
Console.Write(" | ");
//Display the local row. As mentioned above, this is the row
//from the database at which the conflict was detected.
for (int i = 0; i < localColumnCount; i++)
{
Console.Write(conflictingLocalChange.Rows[0][i] + " | ");
}
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Row from database " + DbOther);
Console.Write(" | ");
//Display the remote row.
for (int i = 0; i < remoteColumnCount; i++)
{
Console.Write(conflictingRemoteChange.Rows[0][i] + " | ");
}
//Ask for a conflict resolution option.
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Enter a resolution option for this conflict:");
Console.WriteLine("A = change from " + DbConflictDetected + " wins.");
Console.WriteLine("B = change from " + DbOther + " wins.");
string conflictResolution = Console.ReadLine();
conflictResolution.ToUpper();
if (conflictResolution == "A")
{
e.Action = ApplyAction.Continue;
}
else if (conflictResolution == "B")
{
e.Action = ApplyAction.RetryWithForceWrite;
}
else
{
Console.WriteLine(String.Empty);
Console.WriteLine("Not a valid resolution option.");
}
}
If e.Conflict.Type = DbConflictType.LocalUpdateRemoteUpdate Then
'Get the conflicting changes from the Conflict object
'and display them. The Conflict object holds a copy
'of the changes; updates to this object will not be
'applied. To make changes, use the Context object.
Dim conflictingRemoteChange As DataTable = e.Conflict.RemoteChange
Dim conflictingLocalChange As DataTable = e.Conflict.LocalChange
Dim remoteColumnCount As Integer = conflictingRemoteChange.Columns.Count
Dim localColumnCount As Integer = conflictingLocalChange.Columns.Count
Console.WriteLine([String].Empty)
Console.WriteLine([String].Empty)
Console.WriteLine("Row from database " & DbConflictDetected)
Console.Write(" | ")
'Display the local row. As mentioned above, this is the row
'from the database at which the conflict was detected.
For i As Integer = 0 To localColumnCount - 1
Console.Write(conflictingLocalChange.Rows(0)(i).ToString() & " | ")
Next
Console.WriteLine([String].Empty)
Console.WriteLine([String].Empty)
Console.WriteLine([String].Empty)
Console.WriteLine("Row from database " & DbOther)
Console.Write(" | ")
'Display the remote row.
For i As Integer = 0 To remoteColumnCount - 1
Console.Write(conflictingRemoteChange.Rows(0)(i).ToString() & " | ")
Next
'Ask for a conflict resolution option.
Console.WriteLine([String].Empty)
Console.WriteLine([String].Empty)
Console.WriteLine("Enter a resolution option for this conflict:")
Console.WriteLine("A = change from " & DbConflictDetected & " wins.")
Console.WriteLine("B = change from " & DbOther & " wins.")
Dim conflictResolution As String = Console.ReadLine()
conflictResolution.ToUpper()
If conflictResolution = "A" Then
e.Action = ApplyAction.Continue
ElseIf conflictResolution = "B" Then
e.Action = ApplyAction.RetryWithForceWrite
Else
Console.WriteLine([String].Empty)
Console.WriteLine("Not a valid resolution option.")
End If
The following code example logs error information to a file.
else if (e.Conflict.Type == DbConflictType.ErrorsOccurred)
{
string logFile = @"C:\SyncErrorLog.txt";
Console.WriteLine(String.Empty);
Console.WriteLine("An error occurred during synchronization.");
Console.WriteLine("This error has been logged to " + logFile + ".");
StreamWriter streamWriter = File.AppendText(logFile);
StringBuilder outputText = new StringBuilder();
outputText.AppendLine("** APPLY CHANGE FAILURE AT " + DbConflictDetected.ToUpper() + " **");
outputText.AppendLine("Error source: " + e.Error.Source);
outputText.AppendLine("Error message: " + e.Error.Message);
streamWriter.WriteLine(DateTime.Now.ToShortTimeString() + " | " + outputText.ToString());
streamWriter.Flush();
streamWriter.Dispose();
}
ElseIf e.Conflict.Type = DbConflictType.ErrorsOccurred Then
Dim logFile As String = "C:\SyncErrorLog.txt"
Console.WriteLine([String].Empty)
Console.WriteLine("An error occurred during synchronization.")
Console.WriteLine("This error has been logged to " & logFile & ".")
Dim streamWriter As StreamWriter = File.AppendText(logFile)
Dim outputText As New StringBuilder()
outputText.AppendLine("** APPLY CHANGE FAILURE AT " & DbConflictDetected.ToUpper() & " **")
outputText.AppendLine("Error source: " & e.[Error].Source)
outputText.AppendLine("Error message: " & e.[Error].Message)
streamWriter.WriteLine((DateTime.Now.ToShortTimeString() & " | ") + outputText.ToString())
streamWriter.Flush()
streamWriter.Dispose()
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.
// NOTE: Before running this application, run the database sample script that is
// available in the documentation. The script drops and re-creates the tables that
// are used in the code, and ensures that synchronization objects are dropped so that
// Sync Framework can re-create them.
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
// Create the connections over which provisioning and synchronization
// are performed. The Utility class handles all functionality that is not
//directly related to synchronization, such as holding connection
//string information and making changes to the server database.
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_SqlSync_Server);
SqlConnection clientSqlConn = new SqlConnection(Utility.ConnStr_SqlSync_Client);
SqlCeConnection clientSqlCe1Conn = new SqlCeConnection(Utility.ConnStr_SqlCeSync1);
// Create a scope named "customer", and add the Customer table to the scope.
// GetDescriptionForTable gets the schema of the table, so that tracking
// tables and triggers can be created for that table.
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("customer");
scopeDesc.Tables.Add(
SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn));
// Create a provisioning object for "customer" and specify that
// base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1).
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn);
// Retrieve scope information from the server and use the schema that is retrieved
// to provision the SQL Server and SQL Server Compact client databases.
// This database already exists on the server.
DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn);
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlDesc);
clientSqlConfig.Apply(clientSqlConn);
// This database does not yet exist.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
DbSyncScopeDescription clientSqlCeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn);
SqlCeSyncScopeProvisioning clientSqlCeConfig = new SqlCeSyncScopeProvisioning(clientSqlCeDesc);
clientSqlCeConfig.Apply(clientSqlCe1Conn);
// Initial synchronization sessions.
SampleSyncOrchestrator syncOrchestrator;
SyncOperationStatistics syncStats;
// Data is downloaded from the server to the SQL Server client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("customer", clientSqlConn),
new SqlSyncProvider("customer", serverConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Data is downloaded from the SQL Server client to the
// SQL Server Compact client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("customer", clientSqlCe1Conn),
new SqlSyncProvider("customer", clientSqlConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Make conflicting changes in two databases.
Utility.MakeConflictingChangeOnNode(Utility.ConnStr_SqlSync_Client, "Customer");
Utility.MakeConflictingChangeOnNode(Utility.ConnStr_SqlSync_Server, "Customer");
// Subsequent synchronization sessions.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("customer", clientSqlConn),
new SqlSyncProvider("customer", serverConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("customer", clientSqlCe1Conn),
new SqlSyncProvider("customer", clientSqlConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
//Make a change in SyncSamplesDb_Peer2 that will fail when it
//is synchronized with SyncSamplesDb_Peer1.
Utility.MakeFailingChangeOnNode(Utility.ConnStr_SqlSync_Client);
// Subsequent synchronization sessions.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("customer", clientSqlConn),
new SqlSyncProvider("customer", serverConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("customer", clientSqlCe1Conn),
new SqlSyncProvider("customer", clientSqlConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
public class SampleSyncOrchestrator : SyncOrchestrator
{
//Create class-level variables so that the ApplyChangeFailedEvent
//handler can use them.
private string _localProviderDatabase;
private string _remoteProviderDatabase;
public SampleSyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
_localProviderDatabase = localProvider.Connection.Database.ToString();
_remoteProviderDatabase = remoteProvider.Connection.Database.ToString();
//Specify event handlers for the ApplyChangeFailed event for each provider.
//The handlers are used to resolve conflicting rows and log error information.
localProvider.ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(dbProvider_ApplyChangeFailed);
remoteProvider.ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(dbProvider_ApplyChangeFailed);
}
public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
Console.WriteLine(String.Empty);
}
private void dbProvider_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
{
//For conflict detection, the "local" database is the one at which the
//ApplyChangeFailed event occurs. We determine at which database the event
//fired and then compare the name of that database to the names of
//the databases specified as the LocalProvider and RemoteProvider.
string DbConflictDetected = e.Connection.Database.ToString();
string DbOther;
DbOther = DbConflictDetected == _localProviderDatabase ? _remoteProviderDatabase : _localProviderDatabase;
Console.WriteLine(String.Empty);
Console.WriteLine("Conflict of type " + e.Conflict.Type + " was detected at " + DbConflictDetected + ".");
if (e.Conflict.Type == DbConflictType.LocalUpdateRemoteUpdate)
{
//Get the conflicting changes from the Conflict object
//and display them. The Conflict object holds a copy
//of the changes; updates to this object will not be
//applied. To make changes, use the Context object.
DataTable conflictingRemoteChange = e.Conflict.RemoteChange;
DataTable conflictingLocalChange = e.Conflict.LocalChange;
int remoteColumnCount = conflictingRemoteChange.Columns.Count;
int localColumnCount = conflictingLocalChange.Columns.Count;
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Row from database " + DbConflictDetected);
Console.Write(" | ");
//Display the local row. As mentioned above, this is the row
//from the database at which the conflict was detected.
for (int i = 0; i < localColumnCount; i++)
{
Console.Write(conflictingLocalChange.Rows[0][i] + " | ");
}
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Row from database " + DbOther);
Console.Write(" | ");
//Display the remote row.
for (int i = 0; i < remoteColumnCount; i++)
{
Console.Write(conflictingRemoteChange.Rows[0][i] + " | ");
}
//Ask for a conflict resolution option.
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Enter a resolution option for this conflict:");
Console.WriteLine("A = change from " + DbConflictDetected + " wins.");
Console.WriteLine("B = change from " + DbOther + " wins.");
string conflictResolution = Console.ReadLine();
conflictResolution.ToUpper();
if (conflictResolution == "A")
{
e.Action = ApplyAction.Continue;
}
else if (conflictResolution == "B")
{
e.Action = ApplyAction.RetryWithForceWrite;
}
else
{
Console.WriteLine(String.Empty);
Console.WriteLine("Not a valid resolution option.");
}
}
//Write any errors to a log file.
else if (e.Conflict.Type == DbConflictType.ErrorsOccurred)
{
string logFile = @"C:\SyncErrorLog.txt";
Console.WriteLine(String.Empty);
Console.WriteLine("An error occurred during synchronization.");
Console.WriteLine("This error has been logged to " + logFile + ".");
StreamWriter streamWriter = File.AppendText(logFile);
StringBuilder outputText = new StringBuilder();
outputText.AppendLine("** APPLY CHANGE FAILURE AT " + DbConflictDetected.ToUpper() + " **");
outputText.AppendLine("Error source: " + e.Error.Source);
outputText.AppendLine("Error message: " + e.Error.Message);
streamWriter.WriteLine(DateTime.Now.ToShortTimeString() + " | " + outputText.ToString());
streamWriter.Flush();
streamWriter.Dispose();
}
}
}
}
' NOTE: Before running this application, run the database sample script that is
' available in the documentation. The script drops and re-creates the tables that
' are used in the code, and ensures that synchronization objects are dropped so that
' Sync Framework can re-create them.
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.SqlServer
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Public Shared Sub Main(ByVal args As String())
' Create the connections over which provisioning and synchronization
' are performed. The Utility class handles all functionality that is not
'directly related to synchronization, such as holding connection
'string information and making changes to the server database.
Dim serverConn As New SqlConnection(Utility.ConnStr_SqlSync_Server)
Dim clientSqlConn As New SqlConnection(Utility.ConnStr_SqlSync_Client)
Dim clientSqlCe1Conn As New SqlCeConnection(Utility.ConnStr_SqlCeSync1)
' Create a scope named "customer", and add the Customer table to the scope.
' GetDescriptionForTable gets the schema of the table, so that tracking
' tables and triggers can be created for that table.
Dim scopeDesc As New DbSyncScopeDescription("customer")
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn))
' Create a provisioning object for "customer" and specify that
' base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1).
Dim serverConfig As New SqlSyncScopeProvisioning(scopeDesc)
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip)
' Configure the scope and change-tracking infrastructure.
serverConfig.Apply(serverConn)
' Retrieve scope information from the server and use the schema that is retrieved
' to provision the SQL Server and SQL Server Compact client databases.
' This database already exists on the server.
Dim clientSqlDesc As DbSyncScopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn)
Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlDesc)
clientSqlConfig.Apply(clientSqlConn)
' This database does not yet exist.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Dim clientSqlCeDesc As DbSyncScopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope("customer", serverConn)
Dim clientSqlCeConfig As New SqlCeSyncScopeProvisioning(clientSqlCeDesc)
clientSqlCeConfig.Apply(clientSqlCe1Conn)
' Initial synchronization sessions.
Dim syncOrchestrator As SampleSyncOrchestrator
Dim syncStats As SyncOperationStatistics
' Data is downloaded from the server to the SQL Server client.
syncOrchestrator = New SampleSyncOrchestrator(New SqlSyncProvider("customer", clientSqlConn), New SqlSyncProvider("customer", serverConn))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Data is downloaded from the SQL Server client to the
' SQL Server Compact client.
syncOrchestrator = New SampleSyncOrchestrator(New SqlCeSyncProvider("customer", clientSqlCe1Conn), New SqlSyncProvider("customer", clientSqlConn))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "initial")
' Make conflicting changes in two databases.
Utility.MakeConflictingChangeOnNode(Utility.ConnStr_SqlSync_Client, "Customer")
Utility.MakeConflictingChangeOnNode(Utility.ConnStr_SqlSync_Server, "Customer")
' Subsequent synchronization sessions.
syncOrchestrator = New SampleSyncOrchestrator(New SqlSyncProvider("customer", clientSqlConn), New SqlSyncProvider("customer", serverConn))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
syncOrchestrator = New SampleSyncOrchestrator(New SqlCeSyncProvider("customer", clientSqlCe1Conn), New SqlSyncProvider("customer", clientSqlConn))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
'Make a change in SyncSamplesDb_Peer2 that will fail when it
'is synchronized with SyncSamplesDb_Peer1.
Utility.MakeFailingChangeOnNode(Utility.ConnStr_SqlSync_Client)
' Subsequent synchronization sessions.
syncOrchestrator = New SampleSyncOrchestrator(New SqlSyncProvider("customer", clientSqlConn), New SqlSyncProvider("customer", serverConn))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
syncOrchestrator = New SampleSyncOrchestrator(New SqlCeSyncProvider("customer", clientSqlCe1Conn), New SqlSyncProvider("customer", clientSqlConn))
syncStats = syncOrchestrator.Synchronize()
syncOrchestrator.DisplayStats(syncStats, "subsequent")
'Exit.
Console.Write(vbLf & "Press Enter to close the window.")
Console.ReadLine()
End Sub
End Class
Public Class SampleSyncOrchestrator
Inherits SyncOrchestrator
'Create class-level variables so that the ApplyChangeFailedEvent
'handler can use them.
Private _localProviderDatabase As String
Private _remoteProviderDatabase As String
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
_localProviderDatabase = localProvider.Connection.Database.ToString()
_remoteProviderDatabase = remoteProvider.Connection.Database.ToString()
'Specify event handlers for the ApplyChangeFailed event for each provider.
'The handlers are used to resolve conflicting rows and log error information.
AddHandler localProvider.ApplyChangeFailed, AddressOf dbProvider_ApplyChangeFailed
AddHandler remoteProvider.ApplyChangeFailed, AddressOf dbProvider_ApplyChangeFailed
End Sub
Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
Console.WriteLine([String].Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
Console.WriteLine([String].Empty)
End Sub
Private Sub dbProvider_ApplyChangeFailed(ByVal sender As Object, ByVal e As DbApplyChangeFailedEventArgs)
'For conflict detection, the "local" database is the one at which the
'ApplyChangeFailed event occurs. We determine at which database the event
'fired and then compare the name of that database to the names of
'the databases specified as the LocalProvider and RemoteProvider.
Dim DbConflictDetected As String = e.Connection.Database.ToString()
Dim DbOther As String
DbOther = If(DbConflictDetected = _localProviderDatabase, _remoteProviderDatabase, _localProviderDatabase)
Console.WriteLine([String].Empty)
Console.WriteLine(("Conflict of type " & e.Conflict.Type & " was detected at ") + DbConflictDetected & ".")
If e.Conflict.Type = DbConflictType.LocalUpdateRemoteUpdate Then
'Get the conflicting changes from the Conflict object
'and display them. The Conflict object holds a copy
'of the changes; updates to this object will not be
'applied. To make changes, use the Context object.
Dim conflictingRemoteChange As DataTable = e.Conflict.RemoteChange
Dim conflictingLocalChange As DataTable = e.Conflict.LocalChange
Dim remoteColumnCount As Integer = conflictingRemoteChange.Columns.Count
Dim localColumnCount As Integer = conflictingLocalChange.Columns.Count
Console.WriteLine([String].Empty)
Console.WriteLine([String].Empty)
Console.WriteLine("Row from database " & DbConflictDetected)
Console.Write(" | ")
'Display the local row. As mentioned above, this is the row
'from the database at which the conflict was detected.
For i As Integer = 0 To localColumnCount - 1
Console.Write(conflictingLocalChange.Rows(0)(i).ToString() & " | ")
Next
Console.WriteLine([String].Empty)
Console.WriteLine([String].Empty)
Console.WriteLine([String].Empty)
Console.WriteLine("Row from database " & DbOther)
Console.Write(" | ")
'Display the remote row.
For i As Integer = 0 To remoteColumnCount - 1
Console.Write(conflictingRemoteChange.Rows(0)(i).ToString() & " | ")
Next
'Ask for a conflict resolution option.
Console.WriteLine([String].Empty)
Console.WriteLine([String].Empty)
Console.WriteLine("Enter a resolution option for this conflict:")
Console.WriteLine("A = change from " & DbConflictDetected & " wins.")
Console.WriteLine("B = change from " & DbOther & " wins.")
Dim conflictResolution As String = Console.ReadLine()
conflictResolution.ToUpper()
If conflictResolution = "A" Then
e.Action = ApplyAction.Continue
ElseIf conflictResolution = "B" Then
e.Action = ApplyAction.RetryWithForceWrite
Else
Console.WriteLine([String].Empty)
Console.WriteLine("Not a valid resolution option.")
End If
'Write any errors to a log file.
ElseIf e.Conflict.Type = DbConflictType.ErrorsOccurred Then
Dim logFile As String = "C:\SyncErrorLog.txt"
Console.WriteLine([String].Empty)
Console.WriteLine("An error occurred during synchronization.")
Console.WriteLine("This error has been logged to " & logFile & ".")
Dim streamWriter As StreamWriter = File.AppendText(logFile)
Dim outputText As New StringBuilder()
outputText.AppendLine("** APPLY CHANGE FAILURE AT " & DbConflictDetected.ToUpper() & " **")
outputText.AppendLine("Error source: " & e.[Error].Source)
outputText.AppendLine("Error message: " & e.[Error].Message)
streamWriter.WriteLine((DateTime.Now.ToShortTimeString() & " | ") + outputText.ToString())
streamWriter.Flush()
streamWriter.Dispose()
End If
End Sub
End Class