Como usar variáveis de sessão
Este tópico mostra como usar variáveis de sessão no Sync Framework. Os exemplos deste tópico focalizam os seguintes tipos do Sync Framework:
Para obter informações sobre como executar o código de exemplo, consulte "Exemplo de aplicativos nos tópicos de instruções" em Programando tarefas comuns de sincronização do cliente e do servidor.
Compreendendo as variáveis de sessão
O Sync Framework fornece um conjunto de variáveis de sessão que permitem a passagem de valores para os comandos SyncAdapter e DbServerSyncProvider durante a sincronização. Essas variáveis são especificadas como outros parâmetros para consultas ou procedimentos armazenados em comandos do ADO.NET. Durante uma sessão de sincronização, quando cada objeto de comando ADO.NET é invocado por DbServerSyncProvider, o provedor passa pela coleção de parâmetros de sincronização (SyncParameters) para determinar se pode fazer a correspondência de cada parâmetro com um parâmetro de comando ADO.NET com base no nome. Se houver uma correspondência com uma variável de sessão interna ou com um parâmetro personalizado que você tenha definido, a variável será populada pelo Sync Framework antes de o provedor chamar o comando.
Por exemplo, a consulta a seguir seleciona alterações a partir da tabela Customer, com as variáveis de sessão sync_last_received_anchor, sync_new_received_anchor e 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"
Os valores para essas variáveis são fornecidos pelo Sync Framework durante a sincronização. Você pode usar os nomes de variáveis diretamente como na consulta anterior ou pode usar as constantes disponíveis a partir do objeto SyncSession.
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @" +
SyncSession.SyncLastReceivedAnchor +
" AND InsertTimestamp <= @" +
SyncSession.SyncNewReceivedAnchor +
" AND InsertId <> @"
SyncSession.SyncClientId
A tabela a seguir lista todas as variáveis de sessão disponíveis e como elas são usadas.
| Variável de sessão | Uso |
|---|---|
sync_client_id, sync_client_id_hash, sync_client_id_binary e sync_originator_id |
Usada para identificar o cliente que está sendo sincronizado atualmente. Uma ID geralmente é usada para detecção de conflitos e para impedir a repetição de alterações novamente para o cliente durante a sincronização bidirecional. Para obter mais informações, consulte Como trocar alterações de dados incrementais bidirecionais entre um cliente e um servidor. Por padrão, o Sync Framework identifica cada cliente com uma GUID, retornada por sync_client_id. Você também pode criar um hash da ID e usar sync_client_id_hash em consultas. sync_client_id_binary é útil quando você controla alterações usando o controle de alterações do SQL Server. Você pode mapear a GUID de sync_client_id para um inteiro e usar sync_originator_id. Para obter mais informações, consulte a seção "Exemplos", posteriormente neste tópico. |
sync_last_received_anchor e sync_new_received_anchor |
Usado para definir o conjunto de alterações a ser sincronizado durante uma sessão. Durante a sincronização atual, o comando especificado para a propriedade SelectNewAnchorCommand fornece um novo valor de âncora. As alterações feitas depois do último valor de âncora recebido e antes do novo valor de âncora recebido são sincronizadas. A nova âncora recebida é armazenada e usada como o último valor de âncora recebido para a próxima sincronização. Para obter mais informações, consulte "Determinando quais alterações de dados devem ser baixadas para um cliente" em Controlando alterações no banco de dados do servidor. |
sync_force_write |
Usado com um ApplyAction do RetryWithForceWrite para forçar a aplicação de uma alteração que falhou devido a um conflito ou erro. Para obter mais informações, consulte Como tratar conflitos de dados e erros. |
sync_row_count |
Retorna o número de linhas afetadas pela última operação no servidor. Nos bancos de dados SQL Server, @@ROWCOUNT fornece o valor para essa variável. Uma contagem de linhas igual a 0 indica que uma operação falhou, em geral devido a um conflito ou erro. Para obter mais informações, consulte Como tratar conflitos de dados e erros. |
sync_initialized |
Retorna se a sincronização atual é a sincronização inicial (uma valor de 0) ou uma sincronização subsequente (um valor de 1). |
sync_table_name e sync_group_name |
Usado se você tiver que especificar um nome de tabela ou de grupo em uma consulta. |
sync_batch_count, sync_batch_size e sync_max_received_anchor |
Usado se você fizer alterações em lote. Para obter mais informações, consulte Como especificar a ordem e o tamanho do lote de alterações. |
sync_session_id |
Retorna um valor de GUID que identifica a sessão de sincronização atual. |
Exemplo
Os exemplos de código a seguir mostram como usar as variáveis da sessão quando você estiver sincronizando a tabela Vendor no banco de dados de exemplo do Sync Framework.
Partes principais da API
Esta seção fornece exemplos de código que destacam as partes principais da API que abrangem variáveis de sessão. O exemplo de código a seguir especifica uma consulta da propriedade SelectNewAnchorCommand, que define o valor da variável sync_new_received_anchor. Esse valor é usado pelos comandos de sincronização que selecionam alterações do banco de dados do servidor.
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
O exemplo de código a seguir especifica um comando para selecionar as atualizações incrementais do servidor a serem aplicadas no cliente. O comando inclui variáveis de âncora e a variável sync_originator_id. O valor da variável sync_originator_id é fornecido pela consulta especificada para a propriedade SelectClientIdCommand. Essa consulta e propriedade são descritas no final desta seção.
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
O exemplo de código a seguir especifica um comando para aplicar atualizações do cliente para o servidor. Além das variáveis de ID e âncora, este comando inclui sync_force_write e 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
O exemplo de código a seguir especifica um comando para selecionar as exclusões incrementais do servidor a serem aplicadas no cliente. Além dos parâmetros de ID e âncora, este comando inclui a variável sync_initialized. Nesse caso, as linhas são selecionadas a partir da tabela de desativação somente se esta for uma sincronização subsequente. As linhas na tabela de desativação não são relevantes durante uma sincronização inicial.
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
O exemplo de código a seguir especifica um comando para mapear uma ID de cliente para uma ID de originador. Isso não é obrigatório, mas pode ser útil usar um número inteiro para representar um cliente em vez de a GUID usada pelo Sync Framework. O procedimento armazenado é descrito no próximo exemplo de código.
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
O exemplo de código a seguir cria uma tabela de mapeamento e um procedimento armazenado a ser lido a partir da tabela, preenchendo-a. O procedimento armazenado assume uma ID de cliente (uma GUID) como uma entrada e retorna uma ID originadora (um número inteiro). Uma linha de mapeamento é inserida para o servidor, e linhas extras são adicionadas à medida que novos clientes sincronizam. Depois que um cliente faz a primeira sincronização, a tabela de mapeamento contém um entrada para este cliente. Uma vez que os comandos SyncAdapter usam a ID originadora, as colunas de controle na tabela Vendor são do tipo int em vez de 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
Exemplo de código completo
O exemplo de código completo a seguir inclui os exemplos de código descritos anteriormente e o código adicional para executar a sincronização. O exemplo requer a classe Utility disponível em Classe de utilitário para tópicos de instruções do provedor de banco de dados.
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
Consulte também
Conceitos
Programando tarefas comuns de sincronização do cliente e do servidor