Procédure : utiliser des variables de session
Cette rubrique indique comment utiliser des variables de session dans Sync Framework. Les exemples de cette rubrique reposent sur les types Sync Framework suivants :
Pour plus d'informations sur le mode d'exécution d'un exemple de code, consultez « Exemples d'application dans les rubriques de procédures » dans Programmation des tâches courantes de synchronisation client et serveur.
Présentation des variables de session
Sync Framework comprend un ensemble de variables de session qui vous permettent de transmettre des valeurs aux commandes SyncAdapter et DbServerSyncProvider durant la synchronisation. Ces variables sont spécifiées comme les autres paramètres des requêtes ou des procédures stockées dans les commandes ADO.NET. Au cours d'une session de synchronisation, lorsque chaque objet de commande ADO.NET est appelé par l'objet DbServerSyncProvider, le fournisseur parcourt la collection de paramètres de synchronisation (SyncParameters) pour déterminer s'il peut faire correspondre chaque paramètre à un paramètre de commande ADO.NET sur la base du nom. S'il existe une correspondance avec une variable de session intégrée ou avec un paramètre personnalisé que vous avez défini, la variable est remplie par Sync Framework avant que le fournisseur n'appelle la commande.
Par exemple, la requête suivante sélectionne les modifications de la table Customer à l'aide des variables de session sync_last_received_anchor, sync_new_received_anchor et sync_client_id.
"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"
Les valeurs de ces variables sont fournies par Sync Framework lors de la synchronisation. Vous pouvez utiliser les noms de variables directement comme dans la requête précédente, ou vous pouvez utiliser les constantes qui sont disponibles à partir de l'objet SyncSession.
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @" +
SyncSession.SyncLastReceivedAnchor +
" AND InsertTimestamp <= @" +
SyncSession.SyncNewReceivedAnchor +
" AND InsertId <> @"
SyncSession.SyncClientId
Le tableau suivant présente toutes les variables de sessions disponibles et décrit leur mode d'utilisation.
| Variables de session | Utilisation |
|---|---|
sync_client_id, sync_client_id_hash, sync_client_id_binary et sync_originator_id |
Utilisée pour identifier le client qui fait l'objet d'une synchronisation. Un ID est généralement utilisé pour la détection de conflit et pour empêcher la répercussion des modifications sur le client durant la synchronisation bidirectionnelle. Pour plus d'informations, consultez Procédure : échanger des modifications de données incrémentielles bidirectionnelles entre un client et un serveur. Par défaut, Sync Framework identifie chaque client à l'aide d'un GUID, lequel est retourné par sync_client_id. Vous pouvez également créer un hachage de l'ID et utiliser sync_client_id_hash dans les requêtes. sync_client_id_binary est utile lorsque vous effectuez le suivi des modifications à l'aide du suivi des modifications SQL Server. Vous pouvez mapper le GUID de sync_client_id en un entier et utiliser sync_originator_id. Pour plus d'informations, consultez la section « Exemples » ci-après dans cette rubrique. |
sync_last_received_anchor et sync_new_received_anchor |
Utilisée pour définir le groupe de modifications à synchroniser durant une session. Durant la synchronisation active, la commande spécifiée pour la propriété SelectNewAnchorCommandfournit une nouvelle valeur d'ancre. Les modifications apportées après la réception de la dernière valeur d'ancre et avant la réception de la nouvelle valeur d'ancre sont synchronisées. La nouvelle ancre reçue est alors stockée et utilisée en tant que dernière valeur d'ancre reçue pour la synchronisation suivante. Pour plus d'informations, consultez la section « Identification des modifications de données à télécharger sur un client » de la rubrique Suivi des modifications dans la base de données serveur. |
sync_force_write |
Utilisée avec un objet ApplyAction de RetryWithForceWrite pour forcer l'application d'une modification ayant échoué à cause d'un conflit ou d'une erreur. Pour plus d'informations, consultez Procédure : gérer les conflits de données et les erreurs. |
sync_row_count |
Retourne le nombre de lignes affectées par la dernière opération effectuée sur le serveur. Dans les bases de données SQL Server, @@ROWCOUNT fournit la valeur de cette variable. Un nombre de lignes correspondant à 0 indique qu'une opération a échoué, en général en raison d'un conflit ou d'une erreur. Pour plus d'informations, consultez Procédure : gérer les conflits de données et les erreurs. |
sync_initialized |
Indique si la synchronisation active est la synchronisation initiale (valeur 0) ou une synchronisation ultérieure (valeur 1). |
sync_table_name et sync_group_name |
Utilisée si vous devez spécifier un nom de table ou un nom de groupe dans une requête. |
sync_batch_count, sync_batch_size et sync_max_received_anchor |
Utilisée si vous opérez des modifications par lots. Pour plus d'informations, consultez Procédure : spécifier l'ordre et la taille de lot des modifications. |
sync_session_id |
Retourne une valeur GUID qui identifie la session de synchronisation active. |
Exemple
L'exemple de code suivant indique comment utiliser des variables de session lorsque vous synchronisez la table Vendor de l'exemple de base de données Sync Framework.
Éléments clés de l'API
Cette section contient des exemples de code qui désignent les éléments clés de l'API impliquant des variables de session. L'exemple de code suivant spécifie une requête pour la propriété SelectNewAnchorCommand, qui définit la valeur de la variable sync_new_received_anchor. Cette valeur est utilisée par les commandes de synchronisation qui sélectionnent les modifications dans la base de données serveur.
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;
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
L'exemple de code suivant spécifie une commande permettant de sélectionner les mises à jour incrémentielles du serveur à appliquer au client. La commande inclut des variables d'ancre ainsi que la variable sync_originator_id. La valeur de la variable sync_originator_id est fournie par la requête spécifiée pour la propriété SelectClientIdCommand. Cette requête et cette propriété sont décrites à la fin de cette section.
SqlCommand vendorIncrUpdates = new SqlCommand();
vendorIncrUpdates.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor " +
"WHERE (UpdateTimestamp > @sync_last_received_anchor " +
"AND UpdateTimestamp <= @sync_new_received_anchor " +
"AND UpdateId <> @sync_originator_id " +
"AND NOT (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertId <> @sync_originator_id))";
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrUpdates.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates;
Dim vendorIncrUpdates As New SqlCommand()
With vendorIncrUpdates
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor " _
& "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
& "AND UpdateTimestamp <= @sync_new_received_anchor " _
& "AND UpdateId <> @sync_originator_id " _
& "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertId <> @sync_originator_id))"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates
L'exemple de code suivant spécifie une commande permettant d'appliquer les mises à jour du client au serveur. Cette commande inclut non seulement des variables d'ID et d'ancre, mais aussi les variables sync_force_write et sync_row_count variables.
SqlCommand vendorUpdates = new SqlCommand();
vendorUpdates.CommandText =
"UPDATE Sales.Vendor SET " +
"VendorName = @VendorName, CreditRating = @CreditRating, " +
"PreferredVendor = @PreferredVendor, " +
"UpdateId = @sync_originator_id " +
"WHERE (VendorId = @VendorId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_originator_id)) " +
"SET @sync_row_count = @@rowcount";
vendorUpdates.Parameters.Add("@VendorName", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorUpdates.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorUpdates.Connection = serverConn;
vendorSyncAdapter.UpdateCommand = vendorUpdates;
Dim vendorUpdates As New SqlCommand()
With vendorUpdates
.CommandText = _
"UPDATE Sales.Vendor SET " _
& "VendorName = @VendorName, CreditRating = @CreditRating, " _
& "PreferredVendor = @PreferredVendor, " _
& "UpdateId = @sync_originator_id " _
& "WHERE (VendorId = @VendorId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_originator_id)) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@VendorName", SqlDbType.NVarChar)
.Parameters.Add("@CreditRating", SqlDbType.NVarChar)
.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.UpdateCommand = vendorUpdates
L'exemple de code suivant spécifie une commande permettant de sélectionner les suppressions incrémentielles du serveur à appliquer au client. Cette commande inclut non seulement des paramètres d'ID et d'ancre, mais aussi la variable sync_initialized. Dans ce cas, les lignes sont sélectionnées dans la table tombstone uniquement s'il s'agit d'une synchronisation postérieure à la synchronisation initiale. Les lignes de la table tombstone ne sont pas pertinentes durant une synchronisation initiale.
SqlCommand vendorIncrDeletes = new SqlCommand();
vendorIncrDeletes.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor " +
"AND DeleteId <> @sync_originator_id)";
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrDeletes.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes;
Dim vendorIncrDeletes As New SqlCommand()
With vendorIncrDeletes
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor " _
& "AND DeleteId <> @sync_originator_id)"
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes
L'exemple de code suivant spécifie une commande permettant de mapper un ID de client à un ID d'appelant. Bien que cette opération ne soit pas nécessaire, il peut être utile d'utiliser un entier pour représenter un client, au lieu du GUID utilisé par Sync Framework. La procédure stockée est décrite dans l'exemple de code suivant.
SqlCommand selectClientIdCommand = new SqlCommand();
selectClientIdCommand.CommandType = CommandType.StoredProcedure;
selectClientIdCommand.CommandText = "usp_GetOriginatorId";
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;
selectClientIdCommand.Connection = serverConn;
this.SelectClientIdCommand = selectClientIdCommand;
Dim selectClientIdCommand As New SqlCommand()
With selectClientIdCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_GetOriginatorId"
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectClientIdCommand = selectClientIdCommand
L'exemple de code suivant crée une table de mappage et une procédure stockée afin de lire les données de la table et de remplir cette dernière. La procédure stockée utilise un ID client (un GUID) comme entrée et retourne un ID d'appelant (un entier). Une ligne de mappage est insérée pour le serveur et des lignes supplémentaires sont ajoutées lorsque de nouveaux clients sont synchronisés. Lorsqu'un client s'est synchronisé une fois, la table de mappage contient une entrée pour ce client. Dans la mesure où les commandes SyncAdapter utilisent l'ID d'appelant, les colonnes de suivi de la table Vendor sont de type int et non de type uniqueidentifier.
CREATE TABLE IdMapping(
ClientId uniqueidentifier NOT NULL PRIMARY KEY,
OriginatorId int NOT NULL)
GO
--Insert a mapping for the server.
INSERT INTO IdMapping VALUES ('00000000-0000-0000-0000-000000000000', 0)
GO
CREATE PROCEDURE usp_GetOriginatorId
@sync_client_id uniqueidentifier,
@sync_originator_id int out
AS
SELECT @sync_originator_id = OriginatorId FROM IdMapping WHERE ClientId = @sync_client_id
IF ( @sync_originator_id IS NULL )
BEGIN
SELECT @sync_originator_id = MAX(OriginatorId) + 1 FROM IdMapping
INSERT INTO IdMapping VALUES (@sync_client_id, @sync_originator_id)
END
GO
Exemple de code complet
L'exemple de code complet ci-dessous inclut les exemples de code décrits précédemment, ainsi que du code supplémentaire pour effectuer la synchronisation. L'exemple requiert la classe Utility qui est disponible dans Classe d'utilitaire pour les rubriques de procédures sur le fournisseur de bases de données.
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.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 changes on the server and client.
Utility.MakeDataChangesOnServer("Vendor");
Utility.MakeDataChangesOnClient("Vendor");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return the server data back to its original state.
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 Vendor table: specify a synchronization direction of
//Bidirectional, and that an existing table should be dropped.
SyncTable vendorSyncTable = new SyncTable("Vendor");
vendorSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
vendorSyncTable.SyncDirection = SyncDirection.Bidirectional;
this.Configuration.SyncTables.Add(vendorSyncTable);
}
}
//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 command that enables you to pass in a
//client ID (a GUID) and get back the orginator ID (an integer)
//that is defined in a mapping table on the server.
SqlCommand selectClientIdCommand = new SqlCommand();
selectClientIdCommand.CommandType = CommandType.StoredProcedure;
selectClientIdCommand.CommandText = "usp_GetOriginatorId";
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;
selectClientIdCommand.Connection = serverConn;
this.SelectClientIdCommand = selectClientIdCommand;
//Create a SyncAdapter for the Vendor table, and then define
//the commands to synchronize changes:
//* 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 vendorSyncAdapter = new SyncAdapter("Vendor");
//Select inserts from the server.
//This command includes three session variables:
//@sync_last_received_anchor, @sync_new_received_anchor,
//and @sync_originator_id. The anchor variables are used with
//SelectNewAnchorCommand to determine the set of changes to
//synchronize. In other example code, the commands use
//@sync_client_id instead of @sync_originator_id. In this case,
//@sync_originator_id is used because the SelectClientIdCommand
//is specified.
SqlCommand vendorIncrInserts = new SqlCommand();
vendorIncrInserts.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor " +
"WHERE (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertTimestamp <= @sync_new_received_anchor " +
"AND InsertId <> @sync_originator_id)";
vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrInserts.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalInsertsCommand = vendorIncrInserts;
//Apply inserts to the server.
//This command includes @sync_row_count, which returns
//a count of how many rows were affected by the
//last database operation. In SQL Server, the variable
//is assigned the value of @@rowcount. The count is used
//to determine whether an operation was successful or
//was unsuccessful due to a conflict or an error.
SqlCommand vendorInserts = new SqlCommand();
vendorInserts.CommandText =
"INSERT INTO Sales.Vendor (VendorId, VendorName, CreditRating, PreferredVendor, InsertId, UpdateId) " +
"VALUES (@VendorId, @VendorName, @CreditRating, @PreferredVendor, @sync_originator_id, @sync_originator_id) " +
"SET @sync_row_count = @@rowcount";
vendorInserts.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorInserts.Parameters.Add("@VendorName", SqlDbType.NVarChar);
vendorInserts.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
vendorInserts.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
vendorInserts.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorInserts.Connection = serverConn;
vendorSyncAdapter.InsertCommand = vendorInserts;
//Select updates from the server
SqlCommand vendorIncrUpdates = new SqlCommand();
vendorIncrUpdates.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor " +
"WHERE (UpdateTimestamp > @sync_last_received_anchor " +
"AND UpdateTimestamp <= @sync_new_received_anchor " +
"AND UpdateId <> @sync_originator_id " +
"AND NOT (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertId <> @sync_originator_id))";
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrUpdates.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates;
//Apply updates to the server.
//This command includes @sync_force_write, which can
//be used to apply changes in case of a conflict.
SqlCommand vendorUpdates = new SqlCommand();
vendorUpdates.CommandText =
"UPDATE Sales.Vendor SET " +
"VendorName = @VendorName, CreditRating = @CreditRating, " +
"PreferredVendor = @PreferredVendor, " +
"UpdateId = @sync_originator_id " +
"WHERE (VendorId = @VendorId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_originator_id)) " +
"SET @sync_row_count = @@rowcount";
vendorUpdates.Parameters.Add("@VendorName", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@CreditRating", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorUpdates.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorUpdates.Connection = serverConn;
vendorSyncAdapter.UpdateCommand = vendorUpdates;
//Select deletes from the server.
//This command includes @sync_initialized, which is
//used to determine whether a client has been
//initialized already. If this variable returns 0,
//this is the first synchronization for this client ID
//or originator ID.
SqlCommand vendorIncrDeletes = new SqlCommand();
vendorIncrDeletes.CommandText =
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " +
"FROM Sales.Vendor_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor " +
"AND DeleteId <> @sync_originator_id)";
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorIncrDeletes.Connection = serverConn;
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes;
//Apply deletes to the server.
SqlCommand vendorDeletes = new SqlCommand();
vendorDeletes.CommandText =
"DELETE FROM Sales.Vendor " +
"WHERE (VendorId = @VendorId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_originator_id)) " +
"SET @sync_row_count = @@rowcount " +
"IF (@sync_row_count > 0) BEGIN " +
"UPDATE Sales.Vendor_Tombstone " +
"SET DeleteId = @sync_originator_id " +
"WHERE (VendorId = @VendorId) " +
"END";
vendorDeletes.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier);
vendorDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
vendorDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
vendorDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int);
vendorDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
vendorDeletes.Connection = serverConn;
vendorSyncAdapter.DeleteCommand = vendorDeletes;
//Add the SyncAdapter to the server synchronization provider.
this.SyncAdapters.Add(vendorSyncAdapter);
}
}
//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.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
//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_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.
Console.Write("Creating schema for " + e.Table.TableName + " | ");
e.Schema.Tables["Vendor"].Columns["VendorId"].RowGuid = true;
}
private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
{
//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 util = new Utility();
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName);
Console.WriteLine("Schema created for " + e.Table.TableName);
}
}
//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("Total Changes Uploaded: " + syncStatistics.TotalChangesUploaded);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
}
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.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 changes on the server and client.
Utility.MakeDataChangesOnServer("Vendor")
Utility.MakeDataChangesOnClient("Vendor")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return the server data back to its original state.
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 Vendor table: specify a synchronization direction of
'Bidirectional, and that an existing table should be dropped.
Dim vendorSyncTable As New SyncTable("Vendor")
vendorSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
vendorSyncTable.SyncDirection = SyncDirection.Bidirectional
Me.Configuration.SyncTables.Add(vendorSyncTable)
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 command that enables you to pass in a
'client ID (a GUID) and get back the orginator ID (an integer)
'that is defined in a mapping table on the server.
Dim selectClientIdCommand As New SqlCommand()
With selectClientIdCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_GetOriginatorId"
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectClientIdCommand = selectClientIdCommand
'Create a SyncAdapter for the Vendor table, and then define
'the commands to synchronize changes:
'* 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 vendorSyncAdapter As New SyncAdapter("Vendor")
'Select inserts from the server.
'This command includes three session variables:
'@sync_last_received_anchor, @sync_new_received_anchor,
'and @sync_originator_id. The anchor variables are used with
'SelectNewAnchorCommand to determine the set of changes to
'synchronize. In other example code, the commands use
'@sync_client_id instead of @sync_originator_id. In this case,
'@sync_originator_id is used because the SelectClientIdCommand
'is specified.
Dim vendorIncrInserts As New SqlCommand()
With vendorIncrInserts
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor " _
& "WHERE (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertTimestamp <= @sync_new_received_anchor " _
& "AND InsertId <> @sync_originator_id)"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalInsertsCommand = vendorIncrInserts
'Apply inserts to the server.
'This command includes @sync_row_count, which returns
'a count of how many rows were affected by the
'last database operation. In SQL Server, the variable
'is assigned the value of @@rowcount. The count is used
'to determine whether an operation was successful or
'was unsuccessful due to a conflict or an error.
Dim vendorInserts As New SqlCommand()
With vendorInserts
.CommandText = _
"INSERT INTO Sales.Vendor (VendorId, VendorName, CreditRating, PreferredVendor, InsertId, UpdateId) " _
& "VALUES (@VendorId, @VendorName, @CreditRating, @PreferredVendor, @sync_originator_id, @sync_originator_id) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@VendorName", SqlDbType.NVarChar)
.Parameters.Add("@CreditRating", SqlDbType.NVarChar)
.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.InsertCommand = vendorInserts
'Select updates from the server
Dim vendorIncrUpdates As New SqlCommand()
With vendorIncrUpdates
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor " _
& "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
& "AND UpdateTimestamp <= @sync_new_received_anchor " _
& "AND UpdateId <> @sync_originator_id " _
& "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertId <> @sync_originator_id))"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates
'Apply updates to the server.
'This command includes @sync_force_write, which can
'be used to apply changes in case of a conflict.
Dim vendorUpdates As New SqlCommand()
With vendorUpdates
.CommandText = _
"UPDATE Sales.Vendor SET " _
& "VendorName = @VendorName, CreditRating = @CreditRating, " _
& "PreferredVendor = @PreferredVendor, " _
& "UpdateId = @sync_originator_id " _
& "WHERE (VendorId = @VendorId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_originator_id)) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@VendorName", SqlDbType.NVarChar)
.Parameters.Add("@CreditRating", SqlDbType.NVarChar)
.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.UpdateCommand = vendorUpdates
'Select deletes from the server.
'This command includes @sync_initialized, which is
'used to determine whether a client has been
'initialized already. If this variable returns 0,
'this is the first synchronization for this client ID
'or originator ID.
Dim vendorIncrDeletes As New SqlCommand()
With vendorIncrDeletes
.CommandText = _
"SELECT VendorId, VendorName, CreditRating, PreferredVendor " _
& "FROM Sales.Vendor_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor " _
& "AND DeleteId <> @sync_originator_id)"
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes
'Apply deletes to the server.
Dim vendorDeletes As New SqlCommand()
With vendorDeletes
.CommandText = _
"DELETE FROM Sales.Vendor " _
& "WHERE (VendorId = @VendorId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_originator_id)) " _
& "SET @sync_row_count = @@rowcount " _
& "IF (@sync_row_count > 0) BEGIN " _
& "UPDATE Sales.Vendor_Tombstone " _
& "SET DeleteId = @sync_originator_id " _
& "WHERE (VendorId = @VendorId) " _
& "END"
.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
vendorSyncAdapter.DeleteCommand = vendorDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(vendorSyncAdapter)
End Sub 'New
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.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
'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_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.
Console.Write("Creating schema for " + e.Table.TableName + " | ")
e.Schema.Tables("Vendor").Columns("VendorId").RowGuid = True
End Sub 'SampleClientSyncProvider_CreatingSchema
Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
'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.
Dim util As New Utility()
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName)
Console.WriteLine("Schema created for " + e.Table.TableName)
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("Total Changes Uploaded: " & syncStatistics.TotalChangesUploaded)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats
Voir aussi
Concepts
Programmation des tâches courantes de synchronisation client et serveur