Vorgehensweise: Behandeln von Datenkonflikten und Fehlern
In diesem Thema wird der Umgang mit Datenkonflikten und Fehlern in Sync Framework erläutert. Die Beispiele in diesem Thema beziehen sich auf die folgenden Sync Framework-Typen und -Ereignisse:
das DbServerSyncProvider-Ereignis ApplyChangeFailed, das SqlCeClientSyncProvider-Ereignis ApplyChangeFailed und ApplyChangeFailedEventArgs
Informationen dazu, wie Sie Beispielcode ausführen können, finden Sie unter "Beispielanwendungen in den Themen zur Vorgehensweise" in Programmieren von allgemeinen Client- und Serversynchronisierungsaufgaben.
Datenkonflikte und Fehler
In Sync Framework werden Konflikte und Fehler auf der Ebene der Zeile erkannt. Eine Zeile befindet sich im Konflikt, wenn sie zwischen den Synchronisierungen an mehr als einem Knoten geändert wurde. Fehler während der Synchronisierung entstehen in der Regel aus einer Einschränkungsverletzung, z. B. einem doppelten Primärschlüssel. Anwendungen sollten so ausgelegt sein, dass sie Konflikte möglichst vermeiden, da die Erkennung und Auflösung von Konflikten mehr Komplexität, einen höheren Verarbeitungsaufwand und mehr Netzwerkverkehr mit sich bringen. Konflikte werden zumeist entweder durch Aktualisierung einer Tabelle an nur einem Knoten (typischerweise dem Server) oder dadurch vermieden, dass die Daten so gefiltert werden, dass pro Zeile immer nur ein Knoten aktualisiert wird. Weitere Informationen zum Filtern finden Sie unter Vorgehensweise: Filtern von Zeilen und Spalten. In einigen Anwendungen können Konflikte nicht vermieden werden. Beispielsweise könnten zwei Vertriebsmitarbeiter in einer Außendienstanwendung für dieselbe Region zuständig sein. Beide Vertriebsmitarbeiter könnten die Daten für den gleichen Kunden und die gleichen Bestellungen aktualisieren. Sync Framework stellt daher eine Reihe von Funktionen bereit, mit denen Konflikte erkannt und aufgelöst werden können.
Datenkonflikte können bei jeder Synchronisierung entstehen, bei der Änderungen an mehr als einem Knoten vorgenommen werden. Konflikte können sowohl bei der bidirektionalen Synchronisierung, als auch bei einer Nur-Download- oder Nur-Upload-Synchronisierung auftreten. Wenn z. B. eine Zeile vom Server gelöscht und dieselbe Zeile auf dem Client aktualisiert wird, kommt es zum Konflikt, sobald Sync Framework versucht, die auf den Server hochgeladene Aktualisierung zu übernehmen. Die Konflikte treten grundsätzlich zwischen dem Server und dem Client auf, der gerade synchronisiert wird. Betrachten wir dazu folgendes Beispiel:
Client A und Client B nehmen eine Synchronisierung mit dem Server vor.
Auf Client A wird eine Zeile aktualisiert, anschließend nimmt Client A eine Synchronisierung vor. Es tritt kein Konflikt auf, und die Zeile wird für den Server übernommen.
Auf Client B wird dieselbe Zeile aktualisiert, anschließend nimmt Client B eine Synchronisierung vor. Die Zeile von Client B befindet sich jetzt aufgrund der vom Client A ausgehenden Aktualisierung im Konflikt mit der Zeile vom Server.
Wenn Sie diesen Konflikt zugunsten des Servers auflösen, kann Sync Framework die Zeile auf dem Server für Client B übernehmen. Wenn Sie den Konflikt hingegen zugunsten von Client B auflösen, kann Sync Framework die Zeile auf dem Client B für den Server übernehmen. Wenn dann später eine Synchronisierung zwischen dem Client A und dem Server erfolgt, wird die ursprünglich vom Client B stammende Aktualisierung für den Client A übernommen.
Konflikt- und Fehlertypen
Sync Framework erkennt die folgenden Konflikttypen, die in der ConflictType-Enumeration definiert sind:
Ein ClientInsertServerInsert-Konflikt liegt vor, wenn der Client und der Server eine Zeile mit demselben Primärschlüssel einfügen. Dieser Konflikttyp wird auch als Primärschlüsselkonflikt bezeichnet.
Ein ClientUpdateServerUpdate-Konflikt liegt vor, wenn der Client und der Server dieselbe Zeile ändern. Dieser Konflikttyp tritt am häufigsten auf.
Ein ClientUpdateServerDelete-Konflikt liegt vor, wenn der Client eine Zeile aktualisiert und der Server dieselbe Zeile löscht.
Ein ClientDeleteServerUpdate-Konflikt liegt vor, wenn der Client eine Zeile löscht und der Server dieselbe Zeile aktualisiert.
Ein ErrorsOccurred-Konflikt tritt auf, wenn eine Zeile aufgrund eines Fehlers nicht übernommen wird.
Konflikt- und Fehlererkennung
Wenn eine Zeile während einer Synchronisierung nicht übernommen werden kann, liegt das typischerweise daran, dass ein Fehler oder ein Datenkonflikt aufgetreten ist. In beiden Fällen wird das DbServerSyncProvider-Ereignis ApplyChangeFailed bzw. das SqlCeClientSyncProvider-Ereignis ApplyChangeFailed ausgelöst, je nachdem, ob der Fehler oder Konflikt während der Upload- oder während der Downloadphase der Synchronisierung aufgetreten ist. Wenn das ApplyChangeFailed-Ereignis für den Client ausgelöst wird, wählt Sync Framework alle sich widersprechenden Zeilen aus. Anschließend können Sie entscheiden, wie diese Konflikte aufgelöst werden sollen. Wenn das ApplyChangeFailed-Ereignis für den Server ausgelöst wird, werden die sich widersprechenden Zeilen mittels zweier Befehle ausgewählt, die Sie tabellenweise auf dem SyncAdapter definieren können:
Die Abfrage oder die gespeicherte Prozedur, die Sie für die SelectConflictUpdatedRowsCommand-Eigenschaft festlegen, wählt die sich widersprechenden Zeilen aus der Basistabelle in der Serverdatenbank aus. Sync Framework führt diesen Befehl aus, wenn ein Einfüge-, Aktualisierungs- oder Löschvorgang den @sync_row_count -Wert 0 zurückgibt. Dieser Wert gibt an, dass die Operation fehlgeschlagen ist. Dieser Befehl wählt die Zeilen aus, bei denen ClientInsertServerInsert-, ClientUpdateServerUpdate- und ClientDeleteServerUpdate-Konflikte vorliegen.
Die Abfrage oder die gespeicherte Prozedur, die Sie für den SelectConflictDeletedRowsCommand festlegen, wählt die sich widersprechenden Zeilen aus der Tombstonetabelle in der Serverdatenbank aus. Sync Framework führt diesen Befehl aus, wenn die sich widersprechende Zeile in der Basistabelle nicht gefunden wurde. Dieser Befehl wählt die Zeilen aus, bei denen ein ClientUpdateServerDelete-Konflikt vorliegt.
Die Daten aus den mit anderen Zeilen in Konflikt stehenden Zeilen werden in einer SyncConflict-Auflistung gespeichert. Diese Auflistung kann so groß werden, dass es in den folgenden Fällen zu Fehlern aufgrund von ungenügendem Arbeitsspeicher kommen kann:
Die Anzahl der miteinander in Konflikt stehenden Zeilen ist sehr hoch. Ziehen Sie das Synchronisieren einer kleineren Anzahl von Zeilen in jeder Sitzung in Betracht, oder reduzieren Sie die Anzahl der Konflikte durch Aktualisieren einer bestimmten Zeile an nur einem Knoten.
Die miteinander in Konflikt stehenden Zeilen enthalten Spalten mit großen Datentypen. Schließen Sie die Spalten mit großen Datentypen aus den Spaltengruppen aus, die synchronisiert werden. Weitere Informationen dazu finden Sie unter Vorgehensweise: Filtern von Zeilen und Spalten.
Konflikt- und Fehlerauflösung
Die Konflikt- und Fehlerauflösung sollte als Reaktion auf das DbServerSyncProvider ApplyChangeFailed-Ereignis und das SqlCeClientSyncProvider ApplyChangeFailed-Ereignis erfolgen. Das ApplyChangeFailedEventArgs-Objekt bietet Zugriff auf mehrere Eigenschaften, die während der Konfliktauflösung verwendet werden können:
Geben Sie an, wie bei der Konfliktauflösung vorgegangen werden soll, indem Sie für die Action-Eigenschaft einen der Werte der ApplyAction-Enumeration festlegen:
Continue: Der Konflikt wird ignoriert, und die Synchronisierung wird fortgesetzt.
RetryApplyingRow: Die Zeile wird erneut übernommen. Wenn Sie es versäumen, die Ursache des Konflikts zu beseitigen, indem Sie eine oder beide der sich widersprechenden Zeilen ändern, wird der erneute Versuch wiederum scheitern, sodass das Ereignis erneut ausgelöst wird.
RetryWithForceWrite: Es wird ein neuer Versuch mit der Logik gestartet, um die Änderungsübernahme zu erzwingen. Der SqlCeClientSyncProvider unterstützt diese Option von sich aus. Um diese Option auf dem Server verwenden zu können, müssen Sie den Parameter @sync_force_write verwenden und in den Befehlen, mit denen die Änderungen für die Serverdatenbank übernommen werden, entsprechende Unterstützung hinzufügen. So können Sie z. B. bei einem ClientUpdateServerDelete-Konflikt den Aktualisierungsvorgang in einen Einfügevorgang ändern, wenn @sync_force_write auf 1 gesetzt ist. Entsprechenden Beispielcode finden Sie weiter unten im Abschnitt "Beispiel".
Verwenden Sie zum Abrufen des Konflikttyps und zum Anzeigen der sich zwischen Client und Server widersprechenden Zeilen die Conflict-Eigenschaft.
Verwenden Sie zum Abrufen des Änderungsdatensatzes, der synchronisiert wird, die Context-Eigenschaft. Die von der Conflict-Eigenschaft verfügbar gemachten Zeilen sind lediglich Kopien, sodass sich Änderungen, die an diesen Zeilen vorgenommen werden, nicht auf die zu übernehmenden Zeilen auswirken. Entwickeln Sie mithilfe des von der Context-Eigenschaft verfügbar gemachten Datasets eigene Lösungsschemas, sofern dieses von der Anwendung benötigt werden. Entsprechenden Beispielcode finden Sie weiter unten im Abschnitt "Beispiele".
Der SqlCeClientSyncProvider enthält auch eine ConflictResolver-Eigenschaft, die Sie zum Auflösen von Konflikten auf dem Client verwenden können. Für jeden Konflikttyp können Sie einen Wert aus der ResolveAction-Enumeration festlegen:
ClientWins: identisch mit dem Festlegen des Werts Continue für ApplyAction
ServerWins: identisch mit dem Festlegen des Werts RetryWithForceWrite für ApplyAction
FireEvent: löst den Standard, das ApplyChangeFailed-Ereignis aus und behandelt dann das Ereignis
Der ConflictResolver muss nicht für jeden Konflikttyp festgelegt werden. Sie können Konflikte genau wie auf dem Server auflösen, indem Sie das ApplyChangeFailed-Ereignis behandeln. Die ConflictResolver-Eigenschaft bietet aber dennoch eine einfache Möglichkeit, Konfliktauflösungsoptionen auf dem Client festzulegen.
Beispiel
Die folgenden Codebeispiele zeigen, wie Sie die Konflikterkennung und -auflösung für die Customer-Tabelle in der Sync Framework-Beispieldatenbank konfigurieren können. Die Synchronisierungsbefehle in diesem Beispiel werden nicht mit SqlSyncAdapterBuilder erstellt, sondern manuell. Sie können die Konflikterkennung und -auflösung auch mit den von SqlSyncAdapterBuilder generierten Befehlen verwenden, aber manuelle Befehle bieten eine wesentlich größere Flexibilität, vor allem wenn es um die erzwungene Übernahme von sich widersprechenden Änderungen geht.
Schlüsselbestandteile der API
In diesem Abschnitt finden Sie Codebeispiele für die Bestandteile der API, die für die Konflikterkennung und -auflösung verwendet werden. Die folgende Abfrage wählt sich widersprechende Zeilen aus der Basistabelle in der Serverdatenbank aus.
SqlCommand customerUpdateConflicts = new SqlCommand();
customerUpdateConflicts.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer " +
"WHERE CustomerId = @CustomerId";
customerUpdateConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerUpdateConflicts.Connection = serverConn;
customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts;
Dim customerUpdateConflicts As New SqlCommand()
With customerUpdateConflicts
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer " + "WHERE CustomerId = @CustomerId"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts
Die folgende Abfrage wählt sich widersprechende Zeilen aus der Tombstonetabelle in der Serverdatenbank aus.
SqlCommand customerDeleteConflicts = new SqlCommand();
customerDeleteConflicts.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer_Tombstone " +
"WHERE CustomerId = @CustomerId";
customerDeleteConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerDeleteConflicts.Connection = serverConn;
customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts;
Dim customerDeleteConflicts As New SqlCommand()
With customerDeleteConflicts
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer_Tombstone " + "WHERE CustomerId = @CustomerId"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts
Das folgende Codebeispiel erstellt eine gespeicherte Prozedur, die Aktualisierungen für die Serverdatenbank übernimmt. In diesem Beispiel wird die UpdateCommand-Eigenschaft (Aktualisierungsvorgang) verwendet, gespeicherte Prozeduren können aber auch für die Übernahme von Einfüge- und Löschvorgängen eingesetzt werden. Beispiele für diese Prozeduren finden Sie unter Setupskripts für Datenbankanbieter - Themen zur Vorgehensweise.
Die Aktualisierungsprozedur usp_CustomerApplyUpdate versucht entsprechend dem Wert des Parameters @sync_force_write und abhängig davon, ob die zu aktualisierende Zeile in der Serverdatenbank vorhanden ist, einen Aktualisierungs- oder einen Einfügevorgang durchzuführen. Wenn die Zeile nicht vorhanden ist, macht die Prozedur aus der Aktualisierung einen Einfügevorgang. In diesem Beispiel ist die Ursache für die fehlende Zeile ein Aktualisierungs-/Löschkonflikt.
CREATE PROCEDURE usp_CustomerApplyUpdate (
@sync_last_received_anchor binary(8),
@sync_client_id uniqueidentifier,
@sync_force_write int,
@sync_row_count int out,
@CustomerId uniqueidentifier,
@CustomerName nvarchar(100),
@SalesPerson nvarchar(100),
@CustomerType nvarchar(100))
AS
-- Try to apply an update if the RetryWithForceWrite option
-- was not specified for the sync adapter's update command.
IF @sync_force_write = 0
BEGIN
UPDATE Sales.Customer
SET CustomerName = @CustomerName, SalesPerson = @SalesPerson,
CustomerType = @CustomerType, UpdateId = @sync_client_id
WHERE CustomerId = @CustomerId
AND (UpdateTimestamp <= @sync_last_received_anchor
OR UpdateId = @sync_client_id)
END
ELSE
-- Try to apply an update if the RetryWithForceWrite option
-- was specified for the sync adapter's update command.
BEGIN
--If the row exists, update it.
-- You might want to include code here to handle
-- possible error conditions.
IF EXISTS (SELECT CustomerId FROM Sales.Customer
WHERE CustomerId = @CustomerId)
BEGIN
UPDATE Sales.Customer
SET CustomerName = @CustomerName, SalesPerson = @SalesPerson,
CustomerType = @CustomerType, UpdateId = @sync_client_id
WHERE CustomerId = @CustomerId
END
-- The row does not exist, possibly due to a client-update/
-- server-delete conflict. Change the update into an insert.
ELSE
BEGIN
INSERT INTO Sales.Customer
(CustomerId, CustomerName, SalesPerson,
CustomerType, UpdateId)
VALUES (@CustomerId, @CustomerName, @SalesPerson,
@CustomerType, @sync_client_id)
END
END
SET @sync_row_count = @@rowcount
Mit dem folgenden Codebeispiel werden Konfliktauflösungsoptionen für den SqlCeClientSyncProvider festgelegt. Wie bereits erwähnt, sind diese Optionen nicht zwingend erforderlich, sie helfen aber bei der Konfliktauflösung. In diesem Beispiel sollten bei Aktualisierungs-/Löschkonflikten immer die Aktualisierungen gewinnen. Bei allen anderen Konflikten wird das ApplyChangeFailed-Ereignis für den Client ausgelöst.
this.ConflictResolver.ClientDeleteServerUpdateAction = ResolveAction.ServerWins;
this.ConflictResolver.ClientUpdateServerDeleteAction = ResolveAction.ClientWins;
//If any of the following conflicts or errors occur, the ApplyChangeFailed
//event is raised.
this.ConflictResolver.ClientInsertServerInsertAction = ResolveAction.FireEvent;
this.ConflictResolver.ClientUpdateServerUpdateAction = ResolveAction.FireEvent;
this.ConflictResolver.StoreErrorAction = ResolveAction.FireEvent;
//Log information for the ApplyChangeFailed event and handle any
//ResolveAction.FireEvent cases.
this.ApplyChangeFailed +=new EventHandler<ApplyChangeFailedEventArgs>(SampleClientSyncProvider_ApplyChangeFailed);
Me.ConflictResolver.ClientDeleteServerUpdateAction = ResolveAction.ServerWins
Me.ConflictResolver.ClientUpdateServerDeleteAction = ResolveAction.ClientWins
'If any of the following conflicts or errors occur, the ApplyChangeFailed
'event is raised.
Me.ConflictResolver.ClientInsertServerInsertAction = ResolveAction.FireEvent
Me.ConflictResolver.ClientUpdateServerUpdateAction = ResolveAction.FireEvent
Me.ConflictResolver.StoreErrorAction = ResolveAction.FireEvent
'Log information for the ApplyChangeFailed event and handle any
'ResolveAction.FireEvent cases.
AddHandler Me.ApplyChangeFailed, AddressOf SampleClientSyncProvider_ApplyChangeFailed
Bei Konflikten zwischen Aktualisierungsvorgängen auf dem Client und Löschvorgängen auf dem Server wird, wie das folgende Codebeispiel zeigt, die Übernahme der Aktualisierung auf dem Server erzwungen. Der Konflikt zwischen dem Aktualisierungsvorgang auf dem Client und dem Löschvorgang auf dem Server wird auf dem Server mithilfe der RetryWithForceWrite-Option im ApplyChangeFailed-Ereignishandler auf dem Server behandelt. Die Verwendung dieser Option hat zur Folge, dass der Parameter @sync_force_write auf 1 gesetzt wird, wenn die gespeicherte Prozedur für den Aktualisierungsvorgang auf dem Server aufgerufen wird.
if (e.Conflict.ConflictType == ConflictType.ClientUpdateServerDelete)
{
//For client-update/server-delete conflicts, we force the client
//change to be applied at the server. The stored procedure specified for
//customerSyncAdapter.UpdateCommand accepts the @sync_force_write parameter
//and includes logic to handle this case.
Console.WriteLine(String.Empty);
Console.WriteLine("***********************************");
Console.WriteLine("A client update / server delete conflict was detected.");
e.Action = ApplyAction.RetryWithForceWrite;
Console.WriteLine("The client change was retried at the server with RetryWithForceWrite.");
Console.WriteLine("***********************************");
Console.WriteLine(String.Empty);
}
If e.Conflict.ConflictType = ConflictType.ClientUpdateServerDelete Then
'For client-update/server-delete conflicts, we force the client
'change to be applied at the server. The stored procedure specified for
'customerSyncAdapter.UpdateCommand accepts the @sync_force_write parameter
'and includes logic to handle this case.
Console.WriteLine(String.Empty)
Console.WriteLine("***********************************")
Console.WriteLine("A client update / server delete conflict was detected.")
e.Action = ApplyAction.RetryWithForceWrite
Console.WriteLine("The client change was retried at the server with RetryWithForceWrite.")
Console.WriteLine("***********************************")
Console.WriteLine(String.Empty)
End If
Das folgende Codebeispiel protokolliert Konfliktinformationen und erzwingt die Übernahme sich widersprechender Einfügevorgänge im ApplyChangeFailed-Ereignishandler auf dem Client.
private void SampleClientSyncProvider_ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)
{
//Log event data from the client side.
EventLogger.LogEvents(sender, e);
//Force write any inserted server rows that are in conflict
//when they are downloaded.
if (e.Conflict.ConflictType == ConflictType.ClientInsertServerInsert)
{
e.Action = ApplyAction.RetryWithForceWrite;
}
if (e.Conflict.ConflictType == ConflictType.ClientUpdateServerUpdate)
{
//Logic goes here.
}
if (e.Conflict.ConflictType == ConflictType.ErrorsOccurred)
{
//Logic goes here.
}
}
Private Sub SampleClientSyncProvider_ApplyChangeFailed(ByVal sender As Object, ByVal e As ApplyChangeFailedEventArgs)
'Log event data from the client side.
EventLogger.LogEvents(sender, e)
'Force write any inserted server rows that are in conflict
'when they are downloaded.
If e.Conflict.ConflictType = ConflictType.ClientInsertServerInsert Then
e.Action = ApplyAction.RetryWithForceWrite
End If
If e.Conflict.ConflictType = ConflictType.ClientUpdateServerUpdate Then
'Logic goes here.
End If
If e.Conflict.ConflictType = ConflictType.ErrorsOccurred Then
'Logic goes here.
End If
End Sub 'SampleClientSyncProvider_ApplyChangeFailed
Vollständiges Codebeispiel
Das folgende vollständige Codebeispiel enthält die Codebeispiele, die weiter oben beschrieben wurden, sowie zusätzlichen Code zum Ausführen der Synchronisierung. Zu beachten ist, dass das Beispiel den Benutzern der Anwendung die Wahl gibt, wie Konflikte bei Aktualisierungsvorgängen aufgelöst werden sollen. Eine der Optionen ist ein benutzerdefiniertes Lösungsschema, bei dem Spaltenwerte in den sich widersprechenden Zeilen kombiniert werden. Der Code für das benutzerdefinierte Lösungsschema ist im SampleServerSyncProvider_ApplyChangeFailed-Ereignishandler und im SampleServerSyncProvider_ChangesApplied-Ereignishandler enthalten. Für das Beispiel wird die in 'Utility'-Klasse für Datenbankanbieter - Themen zur Vorgehensweise enthaltene Utility-Klasse benötigt.
using System;
using System.Collections;
using System.Collections.Generic;
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.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Request a password for the client database, and delete
//and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetPassword_SqlCeClientSync();
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make a change at the client that fails when it is
//applied at the server.
Utility.MakeFailingChangeOnClient();
//Make changes at the client and server that conflict
//when they are synchronized.
Utility.MakeConflictingChangesOnClientAndServer();
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
//Comment out this line if you want to view the
//state of the data after all conflicts are resolved.
Utility.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Add the Customer table: specify a synchronization direction
//of Bidirectional.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.Bidirectional;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the Customer table, and then define
//the commands to synchronize changes:
//* SelectConflictUpdatedRowsCommand SelectConflictDeletedRowsCommand
// are used to detect if there are conflicts on the server during
// synchronization.
//* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
// and SelectIncrementalDeletesCommand are used to select changes
// from the server that the client provider then applies to the client.
//* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
// to the server the changes that the client provider has selected
// from the client.
//Create the SyncAdapter.
SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");
//This command is used if @sync_row_count returns
//0 when changes are applied to the server.
SqlCommand customerUpdateConflicts = new SqlCommand();
customerUpdateConflicts.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer " +
"WHERE CustomerId = @CustomerId";
customerUpdateConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerUpdateConflicts.Connection = serverConn;
customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts;
//This command is used if the server provider cannot find
//a row in the base table.
SqlCommand customerDeleteConflicts = new SqlCommand();
customerDeleteConflicts.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer_Tombstone " +
"WHERE CustomerId = @CustomerId";
customerDeleteConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerDeleteConflicts.Connection = serverConn;
customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts;
//Select inserts from the server.
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer " +
"WHERE (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertTimestamp <= @sync_new_received_anchor " +
"AND InsertId <> @sync_client_id)";
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
//Apply inserts to the server.
SqlCommand customerInserts = new SqlCommand();
customerInserts.CommandType = CommandType.StoredProcedure;
customerInserts.CommandText = "usp_CustomerApplyInsert";
customerInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerInserts.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
customerInserts.Connection = serverConn;
customerSyncAdapter.InsertCommand = customerInserts;
//Select updates from the server.
SqlCommand customerIncrUpdates = new SqlCommand();
customerIncrUpdates.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer " +
"WHERE (UpdateTimestamp > @sync_last_received_anchor " +
"AND UpdateTimestamp <= @sync_new_received_anchor " +
"AND UpdateId <> @sync_client_id " +
"AND NOT (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertId <> @sync_client_id))";
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrUpdates.Connection = serverConn;
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;
//Apply updates to the server.
SqlCommand customerUpdates = new SqlCommand();
customerUpdates.CommandType = CommandType.StoredProcedure;
customerUpdates.CommandText = "usp_CustomerApplyUpdate";
customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
customerUpdates.Connection = serverConn;
customerSyncAdapter.UpdateCommand = customerUpdates;
//Select deletes from the server.
SqlCommand customerIncrDeletes = new SqlCommand();
customerIncrDeletes.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor " +
"AND DeleteId <> @sync_client_id)";
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrDeletes.Connection = serverConn;
customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes;
//Apply deletes to the server.
SqlCommand customerDeletes = new SqlCommand();
customerDeletes.CommandType = CommandType.StoredProcedure;
customerDeletes.CommandText = "usp_CustomerApplyDelete";
customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerDeletes.Connection = serverConn;
customerSyncAdapter.DeleteCommand = customerDeletes;
//Add the SyncAdapter to the server synchronization provider.
this.SyncAdapters.Add(customerSyncAdapter);
//Handle the ApplyChangeFailed and ChangesApplied events.
//This allows us to respond to any conflicts that occur, and to
//make changes that are downloaded to the client during the same
//session.
this.ApplyChangeFailed +=new EventHandler<ApplyChangeFailedEventArgs>(SampleServerSyncProvider_ApplyChangeFailed);
this.ChangesApplied +=new EventHandler<ChangesAppliedEventArgs>(SampleServerSyncProvider_ChangesApplied);
}
//Create a list to hold primary keys from the Customer
//table. This list is used when we handle the ApplyChangeFailed
//and ChangesApplied events.
private List<Guid> _updateConflictGuids = new List<Guid>();
private void SampleServerSyncProvider_ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)
{
//Log information for the ApplyChangeFailed event.
EventLogger.LogEvents(sender, e);
//Respond to four different types of conflicts:
// * ClientDeleteServerUpdate
// * ClientUpdateServerDelete
// * ClientInsertServerInsert
// * ClientUpdateServerUpdate
//
if (e.Conflict.ConflictType == ConflictType.ClientDeleteServerUpdate)
{
//With the commands we are using, the default is for the server
//change to win and be applied to the client. Here, we accept the
//default on the server side. We also set ConflictResolver.ServerWins
//for this conflict in the client provider. This ensures that the server
//change is applied to the client during the download phase.
Console.WriteLine(String.Empty);
Console.WriteLine("***********************************");
Console.WriteLine("A client delete / server update conflict was detected.");
e.Action = ApplyAction.Continue;
Console.WriteLine("The server change will be applied at the client.");
Console.WriteLine("***********************************");
Console.WriteLine(String.Empty);
}
if (e.Conflict.ConflictType == ConflictType.ClientUpdateServerDelete)
{
//For client-update/server-delete conflicts, we force the client
//change to be applied at the server. The stored procedure specified for
//customerSyncAdapter.UpdateCommand accepts the @sync_force_write parameter
//and includes logic to handle this case.
Console.WriteLine(String.Empty);
Console.WriteLine("***********************************");
Console.WriteLine("A client update / server delete conflict was detected.");
e.Action = ApplyAction.RetryWithForceWrite;
Console.WriteLine("The client change was retried at the server with RetryWithForceWrite.");
Console.WriteLine("***********************************");
Console.WriteLine(String.Empty);
}
if (e.Conflict.ConflictType == ConflictType.ClientInsertServerInsert)
{
//Similar to how we handled the client-delete/server-update conflict.
//In this case, we set ConflictResolver.FireEvent and use RetryWithForceWrite
//for this conflict in the client provider. This is equivalent to
//ConflictResolver.ServerWins, and ensures that the server
//change is applied to the client during the download phase.
Console.WriteLine(String.Empty);
Console.WriteLine("***********************************");
Console.WriteLine("A client insert / server insert conflict was detected.");
e.Action = ApplyAction.Continue;
Console.WriteLine("The server change will be applied at the client.");
Console.WriteLine("***********************************");
Console.WriteLine(String.Empty);
}
if (e.Conflict.ConflictType == ConflictType.ClientUpdateServerUpdate)
{
//For client-update/server-update conflicts, we want to
//allow the user to specify the conflict resolution option.
//
//It is possible for the Conflict object from the
//server to have more than one row. Because our custom
//resolution code only works with one row at a time,
//we only allow the user to select a resolution
//option if the object contains a single row.
if (e.Conflict.ServerChange.Rows.Count > 1)
{
Console.WriteLine(String.Empty);
Console.WriteLine("***********************************");
Console.WriteLine("A client update / server update conflict was detected.");
e.Action = ApplyAction.Continue;
Console.WriteLine("The server change will be applied at the client.");
Console.WriteLine("***********************************");
Console.WriteLine(String.Empty);
}
else
{
Console.WriteLine(String.Empty);
Console.WriteLine("***********************************");
Console.WriteLine("A client update / server update conflict was detected.");
Console.WriteLine("Conflicting rows are displayed below.");
Console.WriteLine("***********************************");
//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,
//which is demonstrated in the next section of code
//under ' case "CU" '.
DataTable conflictingServerChange = e.Conflict.ServerChange;
DataTable conflictingClientChange = e.Conflict.ClientChange;
int serverColumnCount = conflictingServerChange.Columns.Count;
int clientColumnCount = conflictingClientChange.Columns.Count;
Console.WriteLine(String.Empty);
Console.WriteLine("Server row: ");
Console.Write(" | ");
//Display the server row.
for (int i = 0; i < serverColumnCount; i++)
{
Console.Write(conflictingServerChange.Rows[0][i] + " | ");
}
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
Console.WriteLine("Client row: ");
Console.Write(" | ");
//Display the client row.
for (int i = 0; i < clientColumnCount; i++)
{
Console.Write(conflictingClientChange.Rows[0][i] + " | ");
}
Console.WriteLine(String.Empty);
Console.WriteLine(String.Empty);
//Ask for a conflict resolution option.
Console.WriteLine("Enter a resolution option for this conflict:");
Console.WriteLine("SE = server change wins");
Console.WriteLine("CL = client change wins");
Console.WriteLine("CU = custom resolution (combine rows)");
string conflictResolution = Console.ReadLine();
conflictResolution.ToUpper();
switch (conflictResolution)
{
case "SE":
//Again, this this is the default for the commands we are using:
//the server change is persisted and then downloaded to the client.
e.Action = ApplyAction.Continue;
Console.WriteLine(String.Empty);
Console.WriteLine("The server change will be applied at the client.");
break;
case "CL":
//Override the default by specifying that the client row
//should be applied at the server. The stored procedure specified
//for customerSyncAdapter.UpdateCommand accepts the @sync_force_write
//parameter and includes logic to handle this case.
e.Action = ApplyAction.RetryWithForceWrite;
Console.WriteLine(String.Empty);
Console.WriteLine("The client change was retried at the server with RetryWithForceWrite.");
break;
case "CU":
//Provide a custom resolution scheme that takes each conflicting
//column and applies the combined contents of the column to the
//client and server. This is not necessarily a resolution scheme
//that you would use in production. Instead, it is used to
//demonstrate the various ways you can interact with conflicting
//data during synchronization.
//
//Get the ID for the conflicting row from the client data table,
//and add it to a list of GUIDs. We update rows at the server
//based on this list.
Guid customerId = (Guid)conflictingClientChange.Rows[0]["CustomerId"];
_updateConflictGuids.Add(customerId);
//Create a dictionary to hold the column ordinal and value for
//any columns that are in confict.
Dictionary<int, string> conflictingColumns = new Dictionary<int, string>();
string combinedColumnValue;
//Determine which columns are different at the client and server.
//We already looped through these columns once, but we wanted to
//keep this code separate from the display code above.
for (int i = 0; i < clientColumnCount; i++)
{
if (conflictingClientChange.Rows[0][i].ToString() != conflictingServerChange.Rows[0][i].ToString())
{
//If we find a column that is different, combine the values from
//the client and server, and write "| conflict |" between them.
combinedColumnValue = conflictingClientChange.Rows[0][i] + " | conflict | " +
conflictingServerChange.Rows[0][i];
conflictingColumns.Add(i, combinedColumnValue);
}
}
//Loop through the rows in the Context object, which exposes
//the set of changes that are uploaded from the client.
//Note: In the ApplyChangeFailed event for the client provider,
//you have access to the set of changes that was downloaded from
//the server.
DataTable allClientChanges = e.Context.DataSet.Tables["Customer"];
int allClientRowCount = allClientChanges.Rows.Count;
int allClientColumnCount = allClientChanges.Columns.Count;
for (int i = 0; i < allClientRowCount; i++)
{
//Find the changed row with the GUID from the Conflict object.
if (allClientChanges.Rows[i].RowState == DataRowState.Modified &&
(Guid)allClientChanges.Rows[i]["CustomerId"] == customerId)
{
//Loop through the columns and check whether the column
//is in the conflictingColumns dictionary. If it is,
//update the value in the allClientChanges Context object.
for (int j = 0; j < allClientColumnCount; j++)
{
if (conflictingColumns.ContainsKey(j))
{
allClientChanges.Rows[i][j] = conflictingColumns[j];
}
}
}
}
//Apply the changed row with its combined values to the server.
//This change will persist at the server, but it will not be
//downloaded with the SelectIncrementalUpdate command that we use.
//It will not download the change because it checks for the UpdateId,
//which is still set to the client that made the upload.
//We use the ChangesApplied event to set the UpdateId for the
//change to a value that represents the server. This ensures
//that the change is applied at the client during the download
//phase of synchronization (see SampleServerSyncProvider_ChangesApplied).
e.Action = ApplyAction.RetryWithForceWrite;
Console.WriteLine(String.Empty);
Console.WriteLine("The custom change was retried at the server with RetryWithForceWrite.");
break;
default:
Console.WriteLine(String.Empty);
Console.WriteLine("Not a valid resolution option.");
break;
}
}
Console.WriteLine(String.Empty);
}
}
private void SampleServerSyncProvider_ChangesApplied(object sender, ChangesAppliedEventArgs e)
{
//If _updateConflictGuids contains at least one GUID, update the UpdateId
//column so that each change is downloaded to the client. For more
//information, see SampleServerSyncProvider_ApplyChangeFailed.
if (_updateConflictGuids.Count > 0)
{
SqlCommand updateTable = new SqlCommand();
updateTable.Connection = (SqlConnection)e.Connection;
updateTable.Transaction = (SqlTransaction)e.Transaction;
updateTable.CommandText = String.Empty;
for (int i = 0; i < _updateConflictGuids.Count; i++)
{
updateTable.CommandText +=
" UPDATE Sales.Customer SET UpdateId = '00000000-0000-0000-0000-000000000000' " +
" WHERE CustomerId='" + _updateConflictGuids[i].ToString() + "'";
}
updateTable.ExecuteNonQuery();
}
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but here we use this class to handle client
//provider events.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
//By default, the client database is created if it does not
//exist.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
//Specify conflict resolution options for each type of
//conflict or error that can occur. The client and server are
//independent; therefore, these settings have no effect when changes
//are applied at the server. However, settings should agree with each
//other. For example:
// * We specify a value of ServerWins for client delete /
// server update. On the server side, by default our commands will
// ignore the conflicting delete and download the update to the
// client. ServerWins is equivalent to setting RetryWithForceWrite
// on the client.
// * Conversely, we specify a value of ClientWins for client update /
// server delete. On the server side, we specify that our commands
// should force write the update by turning it into an insert.
this.ConflictResolver.ClientDeleteServerUpdateAction = ResolveAction.ServerWins;
this.ConflictResolver.ClientUpdateServerDeleteAction = ResolveAction.ClientWins;
//If any of the following conflicts or errors occur, the ApplyChangeFailed
//event is raised.
this.ConflictResolver.ClientInsertServerInsertAction = ResolveAction.FireEvent;
this.ConflictResolver.ClientUpdateServerUpdateAction = ResolveAction.FireEvent;
this.ConflictResolver.StoreErrorAction = ResolveAction.FireEvent;
//Log information for the ApplyChangeFailed event and handle any
//ResolveAction.FireEvent cases.
this.ApplyChangeFailed +=new EventHandler<ApplyChangeFailedEventArgs>(SampleClientSyncProvider_ApplyChangeFailed);
//Use the following events to fix up schema on the client.
//We use the CreatingSchema event to change the schema
//by using the API. We use the SchemaCreated event
//to change the schema by using SQL.
this.CreatingSchema += new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
this.SchemaCreated += new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
}
private void SampleClientSyncProvider_ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)
{
//Log event data from the client side.
EventLogger.LogEvents(sender, e);
//Force write any inserted server rows that are in conflict
//when they are downloaded.
if (e.Conflict.ConflictType == ConflictType.ClientInsertServerInsert)
{
e.Action = ApplyAction.RetryWithForceWrite;
}
if (e.Conflict.ConflictType == ConflictType.ClientUpdateServerUpdate)
{
//Logic goes here.
}
if (e.Conflict.ConflictType == ConflictType.ErrorsOccurred)
{
//Logic goes here.
}
}
private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
//Set the RowGuid property because it is not copied
//to the client by default. This is also a good time
//to specify literal defaults with .Columns[ColName].DefaultValue,
//but we will specify defaults like NEWID() by calling
//ALTER TABLE after the table is created.
e.Schema.Tables["Customer"].Columns["CustomerId"].RowGuid = true;
}
private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
{
string tableName = e.Table.TableName;
Utility util = new Utility();
//Call ALTER TABLE on the client. This must be done
//over the same connection and within the same
//transaction that Sync Framework uses
//to create the schema on the client.
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, "Customer");
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics 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("Upload Changes Applied: " + syncStatistics.UploadChangesApplied);
Console.WriteLine("Upload Changes Failed: " + syncStatistics.UploadChangesFailed);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.TotalChangesUploaded);
Console.WriteLine("Download Changes Applied: " + syncStatistics.DownloadChangesApplied);
Console.WriteLine("Download Changes Failed: " + syncStatistics.DownloadChangesFailed);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
public class EventLogger
{
//Create client and server log files, and write to them
//based on data from the ApplyChangeFailedEventArgs.
public static void LogEvents(object sender, ApplyChangeFailedEventArgs e)
{
string logFile = String.Empty;
string site = String.Empty;
if (sender is SampleServerSyncProvider)
{
logFile = "ServerLogFile.txt";
site = "server";
}
else if (sender is SampleClientSyncProvider)
{
logFile = "ClientLogFile.txt";
site = "client";
}
StreamWriter streamWriter = File.AppendText(logFile);
StringBuilder outputText = new StringBuilder();
outputText.AppendLine("** CONFLICTING CHANGE OR ERROR AT " + site.ToUpper() + " **");
outputText.AppendLine("Table for which error or conflict occurred: " + e.TableMetadata.TableName);
outputText.AppendLine("Sync stage: " + e.Conflict.SyncStage);
outputText.AppendLine("Conflict type: " + e.Conflict.ConflictType);
//If it is a data conflict instead of an error, print out
//the values of the rows at the client and server.
if (e.Conflict.ConflictType != ConflictType.ErrorsOccurred &&
e.Conflict.ConflictType != ConflictType.Unknown)
{
DataTable serverChange = e.Conflict.ServerChange;
DataTable clientChange = e.Conflict.ClientChange;
int serverRows = serverChange.Rows.Count;
int clientRows = clientChange.Rows.Count;
int serverColumns = serverChange.Columns.Count;
int clientColumns = clientChange.Columns.Count;
for (int i = 0; i < serverRows; i++)
{
outputText.Append("Server row: ");
for (int j = 0; j < serverColumns; j++)
{
outputText.Append(serverChange.Rows[i][j] + " | ");
}
outputText.AppendLine(String.Empty);
}
for (int i = 0; i < clientRows; i++)
{
outputText.Append("Client row: ");
for (int j = 0; j < clientColumns; j++)
{
outputText.Append(clientChange.Rows[i][j] + " | ");
}
outputText.AppendLine(String.Empty);
}
}
if (e.Conflict.ConflictType == ConflictType.ErrorsOccurred)
{
outputText.AppendLine("Error message: " + e.Error.Message);
}
streamWriter.WriteLine(DateTime.Now.ToShortTimeString() + " | " + outputText.ToString());
streamWriter.Flush();
streamWriter.Dispose();
}
}
}
Imports System
Imports System.Collections
Imports System.Collections.Generic
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.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Request a password for the client database, and delete
'and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetPassword_SqlCeClientSync()
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, True)
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make a change at the client that fails when it is
'applied at the server.
Utility.MakeFailingChangeOnClient()
'Make changes at the client and server that conflict
'when they are synchronized.
Utility.MakeConflictingChangesOnClientAndServer()
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
'Comment out this line if you want to view the
'state of the data after all conflicts are resolved.
Utility.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Add the Customer table: specify a synchronization direction
'of Bidirectional.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.Bidirectional
Me.Configuration.SyncTables.Add(customerSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the Customer table, and then define
'the commands to synchronize changes:
'* SelectConflictUpdatedRowsCommand SelectConflictDeletedRowsCommand
' are used to detect if there are conflicts on the server during
' synchronization.
'* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
' and SelectIncrementalDeletesCommand are used to select changes
' from the server that the client provider then applies to the client.
'* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
' to the server the changes that the client provider has selected
' from the client.
'Create the SyncAdapter.
Dim customerSyncAdapter As New SyncAdapter("Customer")
'This command is used if @sync_row_count returns
'0 when changes are applied to the server.
Dim customerUpdateConflicts As New SqlCommand()
With customerUpdateConflicts
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer " + "WHERE CustomerId = @CustomerId"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts
'This command is used if the server provider cannot find
'a row in the base table.
Dim customerDeleteConflicts As New SqlCommand()
With customerDeleteConflicts
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer_Tombstone " + "WHERE CustomerId = @CustomerId"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts
'Select inserts from the server.
Dim customerIncrInserts As New SqlCommand()
With customerIncrInserts
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertTimestamp <= @sync_new_received_anchor " _
& "AND InsertId <> @sync_client_id)"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
'Apply inserts to the server.
Dim customerInserts As New SqlCommand()
customerInserts.CommandType = CommandType.StoredProcedure
customerInserts.CommandText = "usp_CustomerApplyInsert"
customerInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
customerInserts.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
customerInserts.Connection = serverConn
customerSyncAdapter.InsertCommand = customerInserts
'Select updates from the server.
Dim customerIncrUpdates As New SqlCommand()
With customerIncrUpdates
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
& "AND UpdateTimestamp <= @sync_new_received_anchor " _
& "AND UpdateId <> @sync_client_id " _
& "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertId <> @sync_client_id))"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates
'Apply updates to the server.
Dim customerUpdates As New SqlCommand()
customerUpdates.CommandType = CommandType.StoredProcedure
customerUpdates.CommandText = "usp_CustomerApplyUpdate"
customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
customerUpdates.Connection = serverConn
customerSyncAdapter.UpdateCommand = customerUpdates
'Select deletes from the server.
Dim customerIncrDeletes As New SqlCommand()
With customerIncrDeletes
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor " _
& "AND DeleteId <> @sync_client_id)"
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes
'Apply deletes to the server.
Dim customerDeletes As New SqlCommand()
customerDeletes.CommandType = CommandType.StoredProcedure
customerDeletes.CommandText = "usp_CustomerApplyDelete"
customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
customerDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
customerDeletes.Connection = serverConn
customerSyncAdapter.DeleteCommand = customerDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(customerSyncAdapter)
'Handle the ApplyChangeFailed and ChangesApplied events.
'This allows us to respond to any conflicts that occur, and to
'make changes that are downloaded to the client during the same
'session.
AddHandler Me.ApplyChangeFailed, AddressOf SampleServerSyncProvider_ApplyChangeFailed
AddHandler Me.ChangesApplied, AddressOf SampleServerSyncProvider_ChangesApplied
End Sub 'New
'Create a list to hold primary keys from the Customer
'table. This list is used when we handle the ApplyChangeFailed
'and ChangesApplied events.
Private _updateConflictGuids As ArrayList = New ArrayList
Private Sub SampleServerSyncProvider_ApplyChangeFailed(ByVal sender As Object, ByVal e As ApplyChangeFailedEventArgs)
'Log information for the ApplyChangeFailed event.
EventLogger.LogEvents(sender, e)
'Respond to four different types of conflicts:
' * ClientDeleteServerUpdate
' * ClientUpdateServerDelete
' * ClientInsertServerInsert
' * ClientUpdateServerUpdate
'
If e.Conflict.ConflictType = ConflictType.ClientDeleteServerUpdate Then
'With the commands we are using, the default is for the server
'change to win and be applied to the client. Here, we accept the
'default on the server side. We also set ConflictResolver.ServerWins
'for this conflict in the client provider. This ensures that the server
'change is applied to the client during the download phase.
Console.WriteLine(String.Empty)
Console.WriteLine("***********************************")
Console.WriteLine("A client delete / server update conflict was detected.")
e.Action = ApplyAction.Continue
Console.WriteLine("The server change will be applied at the client.")
Console.WriteLine("***********************************")
Console.WriteLine(String.Empty)
End If
If e.Conflict.ConflictType = ConflictType.ClientUpdateServerDelete Then
'For client-update/server-delete conflicts, we force the client
'change to be applied at the server. The stored procedure specified for
'customerSyncAdapter.UpdateCommand accepts the @sync_force_write parameter
'and includes logic to handle this case.
Console.WriteLine(String.Empty)
Console.WriteLine("***********************************")
Console.WriteLine("A client update / server delete conflict was detected.")
e.Action = ApplyAction.RetryWithForceWrite
Console.WriteLine("The client change was retried at the server with RetryWithForceWrite.")
Console.WriteLine("***********************************")
Console.WriteLine(String.Empty)
End If
If e.Conflict.ConflictType = ConflictType.ClientInsertServerInsert Then
'Similar to how we handled the client-delete/server-update conflict.
'In this case, we set ConflictResolver.FireEvent and use RetryWithForceWrite
'for this conflict in the client provider. This is equivalent to
'ConflictResolver.ServerWins, and ensures that the server
'change is applied to the client during the download phase.
Console.WriteLine(String.Empty)
Console.WriteLine("***********************************")
Console.WriteLine("A client insert / server insert conflict was detected.")
e.Action = ApplyAction.Continue
Console.WriteLine("The server change will be applied at the client.")
Console.WriteLine("***********************************")
Console.WriteLine(String.Empty)
End If
If e.Conflict.ConflictType = ConflictType.ClientUpdateServerUpdate Then
'For client-update/server-update conflicts, we want to
'allow the user to specify the conflict resolution option.
'
'It is possible for the Conflict object from the
'server to have more than one row. Because our custom
'resolution code only works with one row at a time,
'we only allow the user to select a resolution
'option if the object contains a single row.
If e.Conflict.ServerChange.Rows.Count > 1 Then
Console.WriteLine(String.Empty)
Console.WriteLine("***********************************")
Console.WriteLine("A client update / server update conflict was detected.")
e.Action = ApplyAction.Continue
Console.WriteLine("The server change will be applied at the client.")
Console.WriteLine("***********************************")
Console.WriteLine(String.Empty)
Else
Console.WriteLine(String.Empty)
Console.WriteLine("***********************************")
Console.WriteLine("A client update / server update conflict was detected.")
Console.WriteLine("Conflicting rows are displayed below.")
Console.WriteLine("***********************************")
'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,
'which is demonstrated in the next section of code
'under ' case "CU" '.
Dim conflictingServerChange As DataTable = e.Conflict.ServerChange
Dim conflictingClientChange As DataTable = e.Conflict.ClientChange
Dim serverColumnCount As Integer = conflictingServerChange.Columns.Count
Dim clientColumnCount As Integer = conflictingClientChange.Columns.Count
Console.WriteLine(String.Empty)
Console.WriteLine("Server row: ")
Console.Write(" | ")
'Display the server row.
Dim i As Integer
For i = 0 To serverColumnCount - 1
Console.Write(conflictingServerChange.Rows(0)(i).ToString() & " | ")
Next i
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
Console.WriteLine("Client row: ")
Console.Write(" | ")
'Display the client row.
For i = 0 To clientColumnCount - 1
Console.Write(conflictingClientChange.Rows(0)(i).ToString() & " | ")
Next i
Console.WriteLine(String.Empty)
Console.WriteLine(String.Empty)
'Ask for a conflict resolution option.
Console.WriteLine("Enter a resolution option for this conflict:")
Console.WriteLine("SE = server change wins")
Console.WriteLine("CL = client change wins")
Console.WriteLine("CU = custom resolution (combine rows)")
Dim conflictResolution As String = Console.ReadLine()
conflictResolution.ToUpper()
Select Case conflictResolution
Case "SE"
'Again, this this is the default for the commands we are using:
'the server change is persisted and then downloaded to the client.
e.Action = ApplyAction.Continue
Console.WriteLine(String.Empty)
Console.WriteLine("The server change will be applied at the client.")
Case "CL"
'Override the default by specifying that the client row
'should be applied at the server. The stored procedure specified
'for customerSyncAdapter.UpdateCommand accepts the @sync_force_write
'parameter and includes logic to handle this case.
e.Action = ApplyAction.RetryWithForceWrite
Console.WriteLine(String.Empty)
Console.WriteLine("The client change was retried at the server with RetryWithForceWrite.")
Case "CU"
'Provide a custom resolution scheme that takes each conflicting
'column and applies the combined contents of the column to the
'client and server. This is not necessarily a resolution scheme
'that you would use in production. Instead, it is used to
'demonstrate the various ways you can interact with conflicting
'data during synchronization.
'
'Get the ID for the conflicting row from the client data table,
'and add it to a list of GUIDs. We update rows at the server
'based on this list.
Dim customerId As Guid = CType(conflictingClientChange.Rows(0)("CustomerId"), Guid)
_updateConflictGuids.Add(customerId)
'Create a hashtable to hold the column ordinal and value for
'any columns that are in confict.
Dim conflictingColumns As Hashtable = New Hashtable()
Dim combinedColumnValue As String
'Determine which columns are different at the client and server.
'We already looped through these columns once, but we wanted to
'keep this code separate from the display code above.
For i = 0 To clientColumnCount - 1
If conflictingClientChange.Rows(0)(i).ToString() <> conflictingServerChange.Rows(0)(i).ToString() Then
'If we find a column that is different, combine the values from
'the client and server, and write "| conflict |" between them.
combinedColumnValue = conflictingClientChange.Rows(0)(i).ToString() _
& " | conflict | " & conflictingServerChange.Rows(0)(i).ToString()
conflictingColumns.Add(i, combinedColumnValue)
End If
Next i
'Loop through the rows in the Context object, which exposes
'the set of changes that are uploaded from the client.
'Note: In the ApplyChangeFailed event for the client provider,
'you have access to the set of changes that was downloaded from
'the server.
Dim allClientChanges As DataTable = e.Context.DataSet.Tables("Customer")
Dim allClientRowCount As Integer = allClientChanges.Rows.Count
Dim allClientColumnCount As Integer = allClientChanges.Columns.Count
For i = 0 To allClientRowCount - 1
'Find the changed row with the GUID from the Conflict object.
If allClientChanges.Rows(i).RowState = DataRowState.Modified AndAlso CType(allClientChanges.Rows(i)("CustomerId"), Guid) = customerId Then
'Loop through the columns and check whether the column
'is in the conflictingColumns hashtable. If it is,
'update the value in the allClientChanges Context object.
Dim j As Integer
For j = 0 To allClientColumnCount - 1
If conflictingColumns.ContainsKey(j) Then
allClientChanges.Rows(i)(j) = conflictingColumns(j)
End If
Next j
End If
Next i
'Apply the changed row with its combined values to the server.
'This change will persist at the server, but it will not be
'downloaded with the SelectIncrementalUpdate command that we use.
'It will not download the change because it checks for the UpdateId,
'which is still set to the client that made the upload.
'We use the ChangesApplied event to set the UpdateId for the
'change to a value that represents the server. This ensures
'that the change is applied at the client during the download
'phase of synchronization (see SampleServerSyncProvider_ChangesApplied).
e.Action = ApplyAction.RetryWithForceWrite
Console.WriteLine(String.Empty)
Console.WriteLine("The custom change was retried at the server with RetryWithForceWrite.")
Case Else
Console.WriteLine(String.Empty)
Console.WriteLine("Not a valid resolution option.")
End Select
End If
Console.WriteLine(String.Empty)
End If
End Sub 'SampleServerSyncProvider_ApplyChangeFailed
Private Sub SampleServerSyncProvider_ChangesApplied(ByVal sender As Object, ByVal e As ChangesAppliedEventArgs)
'If _updateConflictGuids contains at least one GUID, update the UpdateId
'column so that each change is downloaded to the client. For more
'information, see SampleServerSyncProvider_ApplyChangeFailed.
If _updateConflictGuids.Count > 0 Then
Dim updateTable As New SqlCommand()
updateTable.Connection = CType(e.Connection, SqlConnection)
updateTable.Transaction = CType(e.Transaction, SqlTransaction)
updateTable.CommandText = String.Empty
Dim i As Integer
For i = 0 To _updateConflictGuids.Count - 1
updateTable.CommandText += _
" UPDATE Sales.Customer SET UpdateId = '00000000-0000-0000-0000-000000000000' " _
+ " WHERE CustomerId='" + _updateConflictGuids(i).ToString() + "'"
Next i
updateTable.ExecuteNonQuery()
End If
End Sub 'SampleServerSyncProvider_ChangesApplied
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but here we use this class to handle client
'provider events.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
'By default, the client database is created if it does not
'exist.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
'Specify conflict resolution options for each type of
'conflict or error that can occur. The client and server are
'independent; therefore, these settings have no effect when changes
'are applied at the server. However, settings should agree with each
'other. For example:
' * We specify a value of ServerWins for client delete /
' server update. On the server side, by default our commands will
' ignore the conflicting delete and download the update to the
' client. ServerWins is equivalent to setting RetryWithForceWrite
' on the client.
' * Conversely, we specify a value of ClientWins for client update /
' server delete. On the server side, we specify that our commands
' should force write the update by turning it into an insert.
Me.ConflictResolver.ClientDeleteServerUpdateAction = ResolveAction.ServerWins
Me.ConflictResolver.ClientUpdateServerDeleteAction = ResolveAction.ClientWins
'If any of the following conflicts or errors occur, the ApplyChangeFailed
'event is raised.
Me.ConflictResolver.ClientInsertServerInsertAction = ResolveAction.FireEvent
Me.ConflictResolver.ClientUpdateServerUpdateAction = ResolveAction.FireEvent
Me.ConflictResolver.StoreErrorAction = ResolveAction.FireEvent
'Log information for the ApplyChangeFailed event and handle any
'ResolveAction.FireEvent cases.
AddHandler Me.ApplyChangeFailed, AddressOf SampleClientSyncProvider_ApplyChangeFailed
'Use the following events to fix up schema on the client.
'We use the CreatingSchema event to change the schema
'by using the API. We use the SchemaCreated event
'to change the schema by using SQL.
AddHandler Me.CreatingSchema, AddressOf SampleClientSyncProvider_CreatingSchema
AddHandler Me.SchemaCreated, AddressOf SampleClientSyncProvider_SchemaCreated
End Sub 'New
Private Sub SampleClientSyncProvider_ApplyChangeFailed(ByVal sender As Object, ByVal e As ApplyChangeFailedEventArgs)
'Log event data from the client side.
EventLogger.LogEvents(sender, e)
'Force write any inserted server rows that are in conflict
'when they are downloaded.
If e.Conflict.ConflictType = ConflictType.ClientInsertServerInsert Then
e.Action = ApplyAction.RetryWithForceWrite
End If
If e.Conflict.ConflictType = ConflictType.ClientUpdateServerUpdate Then
'Logic goes here.
End If
If e.Conflict.ConflictType = ConflictType.ErrorsOccurred Then
'Logic goes here.
End If
End Sub 'SampleClientSyncProvider_ApplyChangeFailed
Private Sub SampleClientSyncProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
'Set the RowGuid property because it is not copied
'to the client by default. This is also a good time
'to specify literal defaults with .Columns[ColName].DefaultValue,
'but we will specify defaults like NEWID() by calling
'ALTER TABLE after the table is created.
e.Schema.Tables("Customer").Columns("CustomerId").RowGuid = True
End Sub 'SampleClientSyncProvider_CreatingSchema
Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
Dim tableName As String = e.Table.TableName
Dim util As New Utility()
'Call ALTER TABLE on the client. This must be done
'over the same connection and within the same
'transaction that Sync Framework uses
'to create the schema on the client.
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, "Customer")
End Sub 'SampleClientSyncProvider_SchemaCreated
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, 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("Upload Changes Applied: " & syncStatistics.UploadChangesApplied)
Console.WriteLine("Upload Changes Failed: " & syncStatistics.UploadChangesFailed)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.TotalChangesUploaded)
Console.WriteLine("Download Changes Applied: " & syncStatistics.DownloadChangesApplied)
Console.WriteLine("Download Changes Failed: " & syncStatistics.DownloadChangesFailed)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats
Public Class EventLogger
'Create client and server log files, and write to them
'based on data from the ApplyChangeFailedEventArgs.
Public Shared Sub LogEvents(ByVal sender As Object, ByVal e As ApplyChangeFailedEventArgs)
Dim logFile As String = String.Empty
Dim site As String = String.Empty
If TypeOf sender Is SampleServerSyncProvider Then
logFile = "ServerLogFile.txt"
site = "server"
ElseIf TypeOf sender Is SampleClientSyncProvider Then
logFile = "ClientLogFile.txt"
site = "client"
End If
Dim streamWriter As StreamWriter = File.AppendText(logFile)
Dim outputText As New StringBuilder()
outputText.AppendLine("** CONFLICTING CHANGE OR ERROR AT " & site.ToUpper() & " **")
outputText.AppendLine("Table for which error or conflict occurred: " & e.TableMetadata.TableName)
outputText.AppendLine("Sync stage: " & e.Conflict.SyncStage.ToString())
outputText.AppendLine("Conflict type: " & e.Conflict.ConflictType.ToString())
'If it is a data conflict instead of an error, print out
'the values of the rows at the client and server.
If e.Conflict.ConflictType <> ConflictType.ErrorsOccurred AndAlso e.Conflict.ConflictType <> ConflictType.Unknown Then
Dim serverChange As DataTable = e.Conflict.ServerChange
Dim clientChange As DataTable = e.Conflict.ClientChange
Dim serverRows As Integer = serverChange.Rows.Count
Dim clientRows As Integer = clientChange.Rows.Count
Dim serverColumns As Integer = serverChange.Columns.Count
Dim clientColumns As Integer = clientChange.Columns.Count
Dim i As Integer
For i = 0 To serverRows - 1
outputText.Append("Server row: ")
Dim j As Integer
For j = 0 To serverColumns - 1
outputText.Append(serverChange.Rows(i)(j).ToString() & " | ")
Next j
outputText.AppendLine(String.Empty)
Next i
For i = 0 To clientRows - 1
outputText.Append("Client row: ")
Dim j As Integer
For j = 0 To clientColumns - 1
outputText.Append(clientChange.Rows(i)(j).ToString() & " | ")
Next j
outputText.AppendLine(String.Empty)
Next i
End If
If e.Conflict.ConflictType = ConflictType.ErrorsOccurred Then
outputText.AppendLine("Error message: " + e.Error.Message)
End If
streamWriter.WriteLine(DateTime.Now.ToShortTimeString() & " | " + outputText.ToString())
streamWriter.Flush()
streamWriter.Dispose()
End Sub 'LogEvents
End Class 'EventLogger
Siehe auch
Konzepte
Programmieren von allgemeinen Client- und Serversynchronisierungsaufgaben