Como configurar e executar a sincronização de colaboração (não SQL Server)
Dica
Os tópicos desta seção da documentação, Sincronizando outros bancos de dados compatíveis com ADO.NET, foram criados para demonstrar como bancos de dados diferentes do SQL Server podem ser sincronizados usando o Sync Framework. Nesta versão, o SQL Server é usado em exemplos de código, mas o código pode ser usado para outros bancos de dados compatíveis do ADO.NET, com algumas modificações nos objetos específicos do SQL Server (como SqlConnection) e nas consultas SQL mostradas. Para obter informações sobre a sincronização do SQL Server, consulte Como configurar e executar a sincronização de bancos de dados (SQL Server).
Este tópico descreve as partes principais de um aplicativo que usa o Sync Framework para sincronizar vários bancos de dados. O código neste aplicativo enfoca as seguintes classes 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 Sincronizando outros bancos de dados compatíveis com ADO.NET.
Conforme descrito em Arquitetura e classes para sincronização de bancos de dados, a sincronização pode ocorrer entre duas instâncias do DbSyncProvider, duas instâncias do SqlCeSyncProvider ou uma instância de cada. O código de exemplo deste tópico é de um aplicativo de duas camadas; portanto, ele não demonstra a sincronização de duas instâncias do SqlCeSyncProvider, o que requer uma configuração de n camadas. Para obter um exemplo de uma configuração de n camadas, consulte o exemplo de WebSharingAppDemo-CEProviderEndToEnd incluído no SDK do Sync Framework.
A configuração da sincronização envolve as seguintes etapas:
Criação de tabelas de controle para armazenar metadados e procedimentos armazenados para atualizar dados e metadados em cada banco de dados servidor. Para obter mais informações, consulte Como provisionar um banco de dados de servidor para sincronização de colaboração (não SQL Server).
Inicialização de cada banco de dados servidor com esquema, dados e infraestrutura de controle de alterações.
A execução da sincronização envolve as seguintes etapas:
A criação de adaptadores de sincronização de servidor, de um provedor de sincronização de servidor e de um provedor de sincronização de cliente.
Inicialização de cada banco de dados cliente.
Criação de um orquestrador de sincronização e sincronização dos nós.
Inicializando um banco de dados servidor
A inicialização de um banco de dados envolve copiar para cada banco de dados o esquema de tabela e a infraestrutura de controle de alterações, além de todos os dados iniciais necessários. Para bancos de dados sincronizados com o uso do DbSyncProvider, o Sync Framework não cria automaticamente um esquema de tabela ou uma infraestrutura de controle em cada banco de dados. Um aplicativo deve verificar que esses objetos existem antes de tentar sincronizar nós. Você pode usar o backup e a restauração ou outra tecnologia para copiar os objetos para cada nó, mas isso só deve ser feito se as alterações não estiverem ocorrendo no banco de dados do qual é feito o backup. Se o primeiro banco de dados estiver sendo usado e atualizado, é recomendável copiar apenas o esquema e a infraestrutura de controle de alterações para cada nó e usar o Sync Framework para copiar os dados. Se você copiar os dados usando outro método, um nó poderá perder alterações que foram confirmadas no primeiro nó. O Sync Framework pode inicializar os dados em cada nó desde que pelo menos um nó tenha os dados. O código de exemplo deste tópico usa a seguinte abordagem: cada banco de dados contém duas tabelas, mas só as tabelas de SyncSamplesDb_Peer1 contêm dados. Os dados são copiados para os outros nós durante a primeira sessão de sincronização.
Para obter informações sobre as opções de inicialização para SqlCeSyncProvider, consulte Inicializando um banco de dados cliente mais adiante neste tópico.
Executando a sincronização
Os exemplos de código desta seção são divididos nas seguintes categorias:
O objeto DbSyncAdapter e os comandos para cada tabela que será sincronizada no banco de dados servidor.
O objeto DbSyncProvider e os comandos para o servidor.
O objeto SqlCeSyncProvider para o cliente.
O objeto SyncSchema e o método GenerateSnapshot, que são usados para inicializar o banco de dados cliente.
O objeto SyncOrchestrator, que é usado para sincronizar os bancos de dados servidor e cliente.
Adaptador de sincronização
As propriedades a seguir são definidas no objeto DbSyncAdapter para cada tabela.
Propriedade do adaptador de sincronização |
Uso |
|---|---|
Especifica as colunas de chaves primárias para a tabela. Por exemplo, se uma tabela Customer tiver uma chave primária CustomerId, o código será semelhante ao seguinte: adapter.RowIdColumns.Add(CustomerId). |
|
Selecione todas as alterações desde a sessão de sincronização anterior unindo a tabela base e sua respectiva tabela de controle de alterações. Esse é o conjunto máximo de alterações que pode ser sincronizado. Esse conjunto de alterações pode ser reduzido quando o conhecimento da sincronização é considerado. |
|
Aplique em um nó as inserções, as atualizações e as exclusões selecionadas no outro nó. Essas alterações foram selecionadas com a consulta ou o procedimento que você especificou para a propriedade SelectIncrementalChangesCommand. |
|
InsertMetadataCommand, UpdateMetadataCommand e DeleteMetadataCommand |
Atualize as tabelas de controle de alterações em um nó para refletir as alterações que foram selecionadas no nó e aplicadas em outro nó. Essas atualizações habilitam o Sync Framework a controlar onde e quando as alterações ocorreram. |
Selecione metadados para uma linha que esteja em conflito durante a sincronização. |
|
Selecione metadados que possam ser removidos de um nó. Normalmente, a limpeza tem com base a retenção: os metadados são mantidos durante um período específico. Porém, um aplicativo pode usar outra lógica para determinar quando será feita a limpeza dos metadados. Para obter mais informações, consulte Como limpar metadados para sincronização de colaboração (não SQL Server). |
Os exemplos de código nesta seção criam comandos que o objeto DbSyncAdapter executa durante a sincronização. Esses comandos chamam os procedimentos armazenados que são descritos em Como provisionar um banco de dados de servidor para sincronização de colaboração (não SQL Server). O conjunto completo de comandos é incluído no exemplo de código completo no fim deste tópico. No código, os parâmetros no formato "@" + DbSyncSession são variáveis de sessão. Por exemplo, "@" + DbSyncSession.SyncMinTimestamp é uma variável resolvida com o valor @sync\_min\_timestamp. Para obter mais informações, consulte Como usar variáveis de sessão para a sincronização de colaboração (não SQL Server).
Código do aplicativo para SelectIncrementalChangesCommand
SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" & DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" & DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
Código do aplicativo para UpdateCommand
SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate";
updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.UpdateCommand = updCustomerCmd;
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
Código do aplicativo para UpdateMetadataCommand
SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
Código do aplicativo para SelectRowCommand
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sync.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectRowCommand = selRowCustomerCmd;
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
End With
adapterCustomer.SelectRowCommand = selRowCustomerCmd
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
End With
adapterCustomer.SelectRowCommand = selRowCustomerCmd
Depois que todos os comandos estiverem definidos, o adaptador será adicionado ao provedor com o uso do seguinte código.
sampleProvider.SyncAdapters.Add(adapterCustomer);
Provedor de sincronização do servidor
As propriedades a seguir são definidas no objeto DbSyncProvider para o servidor.
Propriedade do provedor de sincronização |
Uso |
|---|---|
O nome dos escopos a serem sincronizados para uma sessão específica. Cada escopo já deve existir na tabela de informações de escopo no banco de dados servidor. |
|
A conexão através da qual o Sync Framework seleciona e aplica alterações no banco de dados servidor. |
|
Retorna um valor de carimbo de data/hora usado para selecionar e aplicar conjuntos de alterações em cada banco de dados. Durante a sessão de sincronização atual, o comando fornece um novo valor de carimbo de data/hora. As alterações feitas após o valor de carimbo de data/hora da sessão de sincronização anterior e antes do novo valor de carimbo de data/hora ser sincronizado. O novo valor é armazenado e usado como o ponto inicial da próxima sessão. |
|
Seleciona o carimbo de data/hora máximo de cada tabela base ou tabela de controle para determinar se, para cada tabela, o destino já tem todas as alterações da origem. Se o destino já tiver as alterações, muitas vezes o Sync Framework poderá evitar a execução de consultas de enumeração, o que pode melhorar o desempenho. Essa propriedade é opcional. Se um comando não for especificado, as consultas de enumeração serão executadas para todas as tabelas. |
|
Retorna informações da tabela de informações de escopo, como o conhecimento de sincronização e o conhecimento de limpeza que o Sync Framework requer. |
|
Atualiza informações na tabela de informações de escopo. |
|
Retorna o nome do escopo e o nome da tabela de todas as tabelas no escopo especificado que também estão incluídas em outros escopos. |
|
Atualiza a coluna scope_cleanup_timestamp para um escopo específico na tabela de informações de escopo, para marcar o ponto até o qual a limpeza foi executada para o escopo. Para obter mais informações, consulte Como limpar metadados para sincronização de colaboração (não SQL Server). |
Os exemplos de código nesta seção criam comandos que o objeto DbSyncProvider executa durante a sincronização. Os comandos para as propriedades SelectScopeInfoCommand e UpdateScopeInfoCommand são incluídos no exemplo de código completo no fim deste tópico.
Código do aplicativo para ScopeName e Connection
O exemplo de código a seguir define o nome do escopo a ser sincronizado e a conexão através da qual a sincronização será executada. No exemplo de código completo, o objeto DbSyncProvider contém um objeto DbSyncAdapter para a tabela Customer. Quando essa tabela é sincronizada pela primeira vez, a definição do escopo Sales é definida. Depois que um escopo tiver sido sincronizado pela primeira vez, ele não deverá ser alterado. A alteração das tabelas no escopo ou a filtragem de cláusulas para essas tabelas podem resultar na não convergência dos dados.
SqlConnection peerConnection = new SqlConnection(peerConnString);
sampleDbProvider.Connection = peerConnection;
sampleDbProvider.ScopeName = "Sales";
Dim peerConnection As New SqlConnection(peerConnString)
sampleDbProvider.Connection = peerConnection
sampleDbProvider.ScopeName = "Sales"
Dim peerConnection As New SqlConnection(peerConnString)
sampleDbProvider.Connection = peerConnection
sampleDbProvider.ScopeName = "Sales"
Para obter mais informações sobre escopos, consulte "Definir escopos a serem sincronizados", em Como provisionar um banco de dados de servidor para sincronização de colaboração (não SQL Server).
Código do aplicativo para SelectNewTimestampCommand
O exemplo de código a seguir cria um comando para a propriedade SelectNewTimestampCommand.
SqlCommand selectNewTimestampCommand = new SqlCommand();
string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
Dim selectNewTimestampCommand As New SqlCommand()
Dim newTimestampVariable As String = "@" & DbSyncSession.SyncNewTimestamp
With selectNewTimestampCommand
.CommandText = "SELECT " & newTimestampVariable & " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand
Dim selectNewTimestampCommand As New SqlCommand()
Dim newTimestampVariable As String = "@" & DbSyncSession.SyncNewTimestamp
With selectNewTimestampCommand
.CommandText = "SELECT " & newTimestampVariable & " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand
Código do aplicativo para SelectTableMaxTimestampsCommand
O exemplo de código a seguir cria um comando para a propriedade SelectTableMaxTimestampsCommand. No exemplo de código completo, há sessões de sincronização nas quais nenhuma nova alteração foi feita em qualquer nó. Nessas sessões, a chamada para SelectTableMaxTimestampsCommand indica que nenhuma alteração de dados está disponível para sincronização. Portanto, SelectIncrementalChangesCommand não é chamado.
SqlCommand selTableMaxTsCmd = new SqlCommand();
selTableMaxTsCmd.CommandType = CommandType.Text;
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
"MAX(local_update_peer_timestamp) AS max_timestamp " +
"FROM Sync.Customer_Tracking";
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
Dim selTableMaxTsCmd As New SqlCommand()
selTableMaxTsCmd.CommandType = CommandType.Text
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
& "MAX(local_update_peer_timestamp) AS max_timestamp " _
& "FROM Sync.Customer_Tracking"
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd
Dim selTableMaxTsCmd As New SqlCommand()
selTableMaxTsCmd.CommandType = CommandType.Text
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
& "MAX(local_update_peer_timestamp) AS max_timestamp " _
& "FROM Sync.Customer_Tracking"
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd
Provedor de sincronização do cliente
O exemplo de código a seguir cria um objeto SqlCeSyncProvider, define o escopo e a conexão e registra manipuladores de eventos. Para o SqlCeSyncProvider, os adaptadores são privados e seus comandos são criados automaticamente com base no esquema inicializado no banco de dados cliente.
public SqlCeSyncProvider ConfigureCeSyncProvider(string sqlCeConnString)
{
SqlCeSyncProvider sampleCeProvider = new SqlCeSyncProvider();
//Set the scope name
sampleCeProvider.ScopeName = "Sales";
//Set the connection
sampleCeProvider.Connection = new SqlCeConnection(sqlCeConnString);
//Register event handlers
//Register the BeginSnapshotInitialization event handler.
//It is called when snapshot initialization is about to begin
//for a particular scope in a Compact database.
sampleCeProvider.BeginSnapshotInitialization += new EventHandler<DbBeginSnapshotInitializationEventArgs>(sampleCeProvider_BeginSnapshotInitialization);
//Register the EndSnapshotInitialization event handler.
//It is called when snapshot initialization has completed
//for a particular scope in a Compact database.
sampleCeProvider.EndSnapshotInitialization += new EventHandler<DbEndSnapshotInitializationEventArgs>(sampleCeProvider_EndSnapshotInitialization);
return sampleCeProvider;
}
Public Function ConfigureCeSyncProvider(ByVal sqlCeConnString As String) As SqlCeSyncProvider
Dim sampleCeProvider As New SqlCeSyncProvider()
'Set the scope name
sampleCeProvider.ScopeName = "Sales"
'Set the connection
sampleCeProvider.Connection = New SqlCeConnection(sqlCeConnString)
'Register event handlers
'Register the BeginSnapshotInitialization event handler.
'It is called when snapshot initialization is about to begin
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.BeginSnapshotInitialization, AddressOf sampleCeProvider_BeginSnapshotInitialization
'Register the EndSnapshotInitialization event handler.
'It is called when snapshot initialization has completed
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.EndSnapshotInitialization, AddressOf sampleCeProvider_EndSnapshotInitialization
Return sampleCeProvider
End Function
Inicializando um banco de dados cliente
Para que um banco de dados cliente do SQL Server Compact possa começar a receber alterações de outro banco de dados, o banco de dados cliente deve conter o esquema de tabela e todos os dados iniciais, como também a infraestrutura de controle de alterações necessária para o Sync Framework. O Sync Framework fornece dois modos de inicializar o banco de dados:
Inicialização completa do banco de dados cliente com o uso do banco de dados servidor
Isso permite a criação de tabelas no banco de dados cliente com o uso de tabelas, colunas e tabelas de descrição de tipo expostas por um objeto DbSyncProvider que se conecta a uma instância de banco de dados servidor. O banco de dados cliente é então preparado para sincronização, e todas as linhas do banco de dados servidor são baixadas como inserções incrementais. As inserções são selecionadas do servidor com o uso da consulta ou do procedimento que você especifica para a propriedade SelectIncrementalChangesCommand.
Inicialização do instantâneo do banco de dados cliente com o uso do banco de dados cliente pré-existente
A inicialização de instantâneo foi criada para reduzir o tempo necessário para inicializar um banco de dados de cliente. Depois que um banco de dados cliente tiver sido inicializado com o uso da inicialização completa, os bancos de dados subsequentes poderão ser inicializados com o uso de um instantâneo do primeiro banco de dados cliente. Um instantâneo é um banco de dados do SQL Server Compact especialmente preparado que contém esquema de tabela, dados (opcionais) e infraestrutura de controle de alterações. Copie o instantâneo para cada cliente que precisar. Durante a primeira sessão de sincronização para um cliente, os metadados específicos do cliente são atualizados e as alterações ocorridas desde que o instantâneo foi criado são baixadas para o banco de dados cliente.
Importante
Instantâneos devem ser gerados só quando não há atividade no banco de dados do SQL Server Compact. Não há suporte para operações simultâneas de qualquer tipo durante a geração do instantâneo.
O seguinte exemplo de código executa a inicialização completa. O código verifica se um banco de dados cliente exige um esquema sempre que a sincronização ocorre. Se o provedor tiver uma conexão com um banco de dados cliente que já foi inicializado ou que é um banco de dados de instantâneo, o método ScopeExists retornará true. Se o método retornar false, um esquema será recuperado e aplicado ao banco de dados cliente com o uso do método GetScopeDescription.
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(ceConn);
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply();
}
}
}
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(ceConn);
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply();
}
}
}
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim ceConfig As New SqlCeSyncScopeProvisioning(ceConn)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply()
End If
End If
End Sub
End Class
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim ceConfig As New SqlCeSyncScopeProvisioning(ceConn)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply()
End If
End If
End Sub
End Class
O exemplo de código a seguir gera um instantâneo. O código se conecta a um banco de dados do SQL Server Compact que acabou de ser sincronizado com o uso do método ConfigureCESyncProvider do exemplo. Depois da conexão, o método GenerateSnapshot é chamado, o que cria uma cópia do banco de dados original. Quando o provedor cliente se conecta em seguida a essa nova cópia, e a sincronização é executada, o banco de dados é inicializado.
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Dica
Neste exemplo, o banco de dados de instantâneo é gerado enquanto o aplicativo principal está em execução. Em muitos casos, instantâneos são gerados fora do horário de pico, de forma separada de outras atividades de sincronização.
Criando um orquestrador de sincronização e sincronizando bancos de dados
O código descrito nas seções anteriores deste tópico demonstra como definir as propriedades necessárias para a sincronização. Agora é a hora de sincronizar nós. Nós são sempre sincronizados como pares, como SyncSamplesDb_Peer1 e SyncSampleCe2. Para um aplicativo de produção, uma cópia do aplicativo é normalmente implantada em cada nó para que a sincronização possa ser iniciada de qualquer um dos nós.
O exemplo de código a seguir cria uma classe SampleSyncAgent derivada de SyncOrchestrator. O construtor do SampleSyncAgent obtém dois objetos RelationalSyncProvider. Como DbSyncProvider e SqlCeSyncProvider derivam de RelationalSyncProvider, um objeto SampleSyncAgent pode sincronizar qualquer combinação dos dois tipos de provedor. O código especifica qual provedor é o provedor local e qual é o remoto. Especifica também que as alterações serão carregadas primeiro do banco de dados remoto para o banco de dados local e depois baixadas na ordem inversa. O código verifica se os provedores são objetos SqlCeSyncProvider que exigem a inicialização de esquema para um banco de dados cliente.
public class SampleSyncAgent : SyncOrchestrator
{
public SampleSyncAgent(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
//Check to see if any provider is a SqlCe provider and if it needs to
//be initialized.
CheckIfProviderNeedsSchema(localProvider as SqlCeSyncProvider, remoteProvider as DbSyncProvider);
CheckIfProviderNeedsSchema(remoteProvider as SqlCeSyncProvider, localProvider as DbSyncProvider);
}
//For Compact databases that are not initialized with a snapshot,
//get the schema and initial data from a server database.
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(ceConn);
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply();
}
}
}
}
Public Class SampleSyncAgent
Inherits SyncOrchestrator
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
'Check to see if any provider is a SqlCe provider and if it needs to
'be initialized.
CheckIfProviderNeedsSchema(TryCast(localProvider, SqlCeSyncProvider), _
TryCast(remoteProvider, DbSyncProvider))
CheckIfProviderNeedsSchema(TryCast(remoteProvider, SqlCeSyncProvider), _
TryCast(localProvider, DbSyncProvider))
End Sub
'For Compact databases that are not initialized with a snapshot,
'get the schema and initial data from a server database.
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim ceConfig As New SqlCeSyncScopeProvisioning(ceConn)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply()
End If
End If
End Sub
End Class
O código a seguir configura sessões de sincronização chamando ConfigureDbSyncProvider ou ConfigureCeSyncProvider, métodos que o aplicativo de exemplo usa para definir as propriedades de provedor apropriadas para cada sessão. O código chama, em seguida, o método Synchronize de SampleSyncAgent para sincronizar cada par de bancos de dados.
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
//First session: Synchronize two databases by using two instances
//of DbSyncProvider.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
'First session: Synchronize two databases by using two instances
'of DbSyncProvider.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Exemplo de código completo
O exemplo de código completo a seguir inclui os exemplos de código descritos anteriormente e outros códigos para exibir estatísticas de sincronização e informações de eventos. 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.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.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The Utility class handles all functionality that is not
//directly related to synchronization, such as holding peerConnection
//string information and making changes to the server database.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
//First session: Synchronize two databases by using two instances
//of DbSyncProvider.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Make a change in one of the databases.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer");
try
{
//Subsequent synchronization. Changes are now synchronized between all
//nodes.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Return data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1);
Utility.CleanUpNode(Utility.ConnStr_DbSync2);
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncOrchestrator.
public class SampleSyncAgent : SyncOrchestrator
{
public SampleSyncAgent(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
//Check to see if any provider is a SqlCe provider and if it needs to
//be initialized.
CheckIfProviderNeedsSchema(localProvider as SqlCeSyncProvider, remoteProvider as DbSyncProvider);
CheckIfProviderNeedsSchema(remoteProvider as SqlCeSyncProvider, localProvider as DbSyncProvider);
}
//For Compact databases that are not initialized with a snapshot,
//get the schema and initial data from a server database.
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(ceConn);
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply();
}
}
}
}
public class SampleSyncProvider
{
public SqlCeSyncProvider ConfigureCeSyncProvider(string sqlCeConnString)
{
SqlCeSyncProvider sampleCeProvider = new SqlCeSyncProvider();
//Set the scope name
sampleCeProvider.ScopeName = "Sales";
//Set the connection
sampleCeProvider.Connection = new SqlCeConnection(sqlCeConnString);
//Register event handlers
//Register the BeginSnapshotInitialization event handler.
//It is called when snapshot initialization is about to begin
//for a particular scope in a Compact database.
sampleCeProvider.BeginSnapshotInitialization += new EventHandler<DbBeginSnapshotInitializationEventArgs>(sampleCeProvider_BeginSnapshotInitialization);
//Register the EndSnapshotInitialization event handler.
//It is called when snapshot initialization has completed
//for a particular scope in a Compact database.
sampleCeProvider.EndSnapshotInitialization += new EventHandler<DbEndSnapshotInitializationEventArgs>(sampleCeProvider_EndSnapshotInitialization);
return sampleCeProvider;
}
public void sampleCeProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
Console.WriteLine("Full Initialization Process Started.....");
Console.WriteLine(
string.Format("CreatingSchame Event fired for Database {0}", e.Connection.Database)
);
}
public void sampleCeProvider_BeginSnapshotInitialization(object sender, DbBeginSnapshotInitializationEventArgs e)
{
Console.WriteLine("");
Console.WriteLine("Snapshot initialization process started");
Console.WriteLine(
string.Format("BeginSnapshotInitialization event fired for scope {0}", e.ScopeName)
);
}
public void sampleCeProvider_EndSnapshotInitialization(object sender, DbEndSnapshotInitializationEventArgs e)
{
Console.WriteLine("EndSnapshotInitialization event fired");
Dictionary<string, DbSnapshotInitializationTableStatistics> tableStats =
e.TableInitializationStatistics;
foreach (string tableName in tableStats.Keys)
{
DbSnapshotInitializationTableStatistics ts = tableStats[tableName];
Console.WriteLine("\tTable Name: " + tableName);
Console.WriteLine("\tTotal Rows: " + ts.TotalRows);
Console.WriteLine("\tRows Intialized: " + ts.RowsInitialized);
Console.WriteLine("\tStart Time: " + ts.StartTime);
Console.WriteLine("\tEnd Time: " + ts.EndTime);
}
Console.WriteLine("Snapshot initialization process completed");
Console.WriteLine("");
}
public DbSyncProvider ConfigureDbSyncProvider(string peerConnString)
{
DbSyncProvider sampleDbProvider = new DbSyncProvider();
SqlConnection peerConnection = new SqlConnection(peerConnString);
sampleDbProvider.Connection = peerConnection;
sampleDbProvider.ScopeName = "Sales";
//Create a DbSyncAdapter object for the Customer table and associate it
//with the DbSyncProvider. Following the DataAdapter style in ADO.NET,
//DbSyncAdapter is the equivalent for synchronization. The commands that
//are specified for the DbSyncAdapter object call stored procedures
//that are created in each peer database.
DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer");
//Specify the primary key, which Sync Framework uses
//to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId");
//Specify the command to select incremental changes.
//In this command and other commands, session variables are
//used to pass information at runtime. DbSyncSession.SyncMetadataOnly
//and SyncMinTimestamp are two of the string constants that
//the DbSyncSession class exposes. You could also include
//@sync_metadata_only and @sync_min_timestamp directly in your
//queries:
//* sync_metadata_only is used by Sync Framework as an optimization
// in some queries.
//* The value of the sync_min_timestamp session variable is compared to
// values in the sync_row_timestamp column in the tracking table to
// determine which rows to select.
SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;
//Specify the command to insert rows.
//The sync_row_count session variable is used in this command
//and other commands to return a count of the rows affected by an operation.
//A count of 0 indicates that an operation failed.
SqlCommand insCustomerCmd = new SqlCommand();
insCustomerCmd.CommandType = CommandType.StoredProcedure;
insCustomerCmd.CommandText = "Sync.sp_Customer_ApplyInsert";
insCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
insCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.InsertCommand = insCustomerCmd;
//Specify the command to update rows.
//The value of the sync_min_timestamp session variable is compared to
//values in the sync_row_timestamp column in the tracking table to
//determine which rows to update.
SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate";
updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.UpdateCommand = updCustomerCmd;
//Specify the command to delete rows.
//The value of the sync_min_timestamp session variable is compared to
//values in the sync_row_timestamp column in the tracking table to
//determine which rows to delete.
SqlCommand delCustomerCmd = new SqlCommand();
delCustomerCmd.CommandType = CommandType.StoredProcedure;
delCustomerCmd.CommandText = "Sync.sp_Customer_ApplyDelete";
delCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.DeleteCommand = delCustomerCmd;
//Specify the command to select any conflicting rows.
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sync.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectRowCommand = selRowCustomerCmd;
//Specify the command to insert metadata rows.
//The session variables in this command relate to columns in
//the tracking table.
SqlCommand insMetadataCustomerCmd = new SqlCommand();
insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
insMetadataCustomerCmd.CommandText = "Sync.sp_Customer_InsertMetadata";
insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd;
//Specify the command to update metadata rows.
SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;
//Specify the command to delete metadata rows.
SqlCommand delMetadataCustomerCmd = new SqlCommand();
delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
delMetadataCustomerCmd.CommandText = "Sync.sp_Customer_DeleteMetadata";
delMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd;
//Add the adapter to the provider.
sampleDbProvider.SyncAdapters.Add(adapterCustomer);
// Configure commands that relate to the provider itself rather
// than the DbSyncAdapter object for each table:
// * SelectNewTimestampCommand: Returns the new high watermark for
// the current synchronization session.
// * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge,
// and a scope version (timestamp).
// * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.
// * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table
// or tracking table, to determine whether for each table the destination already
// has all of the changes from the source. If a destination table has all the changes,
// SelectIncrementalChangesCommand is not called for that table.
// There are additional commands related to metadata cleanup that are not
// included in this application.
//Select a new timestamp.
//During each synchronization, the new value and
//the last value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
SqlCommand selectNewTimestampCommand = new SqlCommand();
string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
//Specify the command to select local replica metadata.
SqlCommand selReplicaInfoCmd = new SqlCommand();
selReplicaInfoCmd.CommandType = CommandType.Text;
selReplicaInfoCmd.CommandText = "SELECT " +
"scope_id, " +
"scope_local_id, " +
"scope_sync_knowledge, " +
"scope_tombstone_cleanup_knowledge, " +
"scope_timestamp " +
"FROM Sync.ScopeInfo " +
"WHERE scope_name = @" + DbSyncSession.SyncScopeName;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd;
//Specify the command to update local replica metadata.
SqlCommand updReplicaInfoCmd = new SqlCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "UPDATE Sync.ScopeInfo SET " +
"scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
"scope_id = @" + DbSyncSession.SyncScopeId + ", " +
"scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
"WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
" ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
"SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
//Return the maximum timestamp from the Customer_Tracking table.
//If more tables are synchronized, the query should UNION
//all of the results. The table name is not schema-qualified
//in this case because the name was not schema qualified in the
//DbSyncAdapter constructor.
SqlCommand selTableMaxTsCmd = new SqlCommand();
selTableMaxTsCmd.CommandType = CommandType.Text;
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
"MAX(local_update_peer_timestamp) AS max_timestamp " +
"FROM Sync.Customer_Tracking";
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
return sampleDbProvider;
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
Console.WriteLine(String.Empty);
}
}
}
}
Imports System
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.SqlServerCe
Class Program
Shared Sub Main(ByVal args As String())
'The Utility class handles all functionality that is not
'directly related to synchronization, such as holding connection
'string information and making changes to the server database.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
'First session: Synchronize two databases by using two instances
'of DbSyncProvider.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Make a change in one of the databases.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer")
Try
'Subsequent synchronization. Changes are now synchronized between all
'nodes.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Return data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1)
Utility.CleanUpNode(Utility.ConnStr_DbSync2)
'Exit.
Console.Write(vbLf & "Press Enter to close the window.")
Console.ReadLine()
End Sub
'Create a class that is derived from
'Microsoft.Synchronization.SyncOrchestrator.
Public Class SampleSyncAgent
Inherits SyncOrchestrator
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
'Check to see if any provider is a SqlCe provider and if it needs to
'be initialized.
CheckIfProviderNeedsSchema(TryCast(localProvider, SqlCeSyncProvider), _
TryCast(remoteProvider, DbSyncProvider))
CheckIfProviderNeedsSchema(TryCast(remoteProvider, SqlCeSyncProvider), _
TryCast(localProvider, DbSyncProvider))
End Sub
'For Compact databases that are not initialized with a snapshot,
'get the schema and initial data from a server database.
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim ceConfig As New SqlCeSyncScopeProvisioning(ceConn)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply()
End If
End If
End Sub
End Class
Public Class SampleSyncProvider
Public Function ConfigureCeSyncProvider(ByVal sqlCeConnString As String) As SqlCeSyncProvider
Dim sampleCeProvider As New SqlCeSyncProvider()
'Set the scope name
sampleCeProvider.ScopeName = "Sales"
'Set the connection
sampleCeProvider.Connection = New SqlCeConnection(sqlCeConnString)
'Register event handlers
'Register the BeginSnapshotInitialization event handler.
'It is called when snapshot initialization is about to begin
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.BeginSnapshotInitialization, AddressOf sampleCeProvider_BeginSnapshotInitialization
'Register the EndSnapshotInitialization event handler.
'It is called when snapshot initialization has completed
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.EndSnapshotInitialization, AddressOf sampleCeProvider_EndSnapshotInitialization
Return sampleCeProvider
End Function
Public Sub sampleCeProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
Console.WriteLine("Full initialization process started...")
Console.WriteLine(String.Format("CreatingSchema event fired for database {0}", e.Connection.Database))
End Sub
Public Sub sampleCeProvider_BeginSnapshotInitialization(ByVal sender As Object, ByVal e As DbBeginSnapshotInitializationEventArgs)
Console.WriteLine("")
Console.WriteLine("Snapshot initialization process started...")
Console.WriteLine(String.Format("BeginSnapshotInitialization event fired for scope {0}", e.ScopeName))
End Sub
Public Sub sampleCeProvider_EndSnapshotInitialization(ByVal sender As Object, ByVal e As DbEndSnapshotInitializationEventArgs)
Console.WriteLine("EndSnapshotInitialization event fired")
Dim tableStats As Dictionary(Of String, DbSnapshotInitializationTableStatistics) = e.TableInitializationStatistics
For Each tableName As String In tableStats.Keys
Dim ts As DbSnapshotInitializationTableStatistics = tableStats(tableName)
Console.WriteLine(vbTab & "Table Name: " & tableName)
Console.WriteLine(vbTab & "Total Rows: " & ts.TotalRows)
Console.WriteLine(vbTab & "Rows Intialized: " & ts.RowsInitialized)
Console.WriteLine(vbTab & "Start Time: " & ts.StartTime)
Console.WriteLine(vbTab & "End Time: " & ts.EndTime)
Next
Console.WriteLine("Snapshot initialization process completed")
Console.WriteLine("")
End Sub
Public Function ConfigureDbSyncProvider(ByVal peerConnString As String) As DbSyncProvider
Dim sampleDbProvider As New DbSyncProvider()
Dim peerConnection As New SqlConnection(peerConnString)
sampleDbProvider.Connection = peerConnection
sampleDbProvider.ScopeName = "Sales"
'Create a DbSyncAdapter object for the Customer table and associate it
'with the DbSyncProvider. Following the DataAdapter style in ADO.NET,
'DbSyncAdapter is the equivalent for synchronization. The commands that
'are specified for the DbSyncAdapter object call stored procedures
'that are created in each peer database.
Dim adapterCustomer As New DbSyncAdapter("Customer")
'Specify the primary key, which Sync Framework uses
'to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId")
'Specify the command to select incremental changes.
'In this command and other commands, session variables are
'used to pass information at runtime. DbSyncSession.SyncMetadataOnly
'and SyncMinTimestamp are two of the string constants that
'the DbSyncSession class exposes. You could also include
'@sync_metadata_only and @sync_min_timestamp directly in your
'queries:
'* sync_metadata_only is used by Sync Framework as an optimization
' in some queries.
'* The value of the sync_min_timestamp session variable is compared to
' values in the sync_row_timestamp column in the tracking table to
' determine which rows to select.
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" & DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
'Specify the command to insert rows.
'The sync_row_count session variable is used in this command
'and other commands to return a count of the rows affected by an operation.
'A count of 0 indicates that an operation failed.
Dim insCustomerCmd As New SqlCommand()
With insCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyInsert"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.InsertCommand = insCustomerCmd
'Specify the command to update rows.
'The value of the sync_min_timestamp session variable is compared to
'values in the sync_row_timestamp column in the tracking table to
'determine which rows to update.
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
'Specify the command to delete rows.
'The value of the sync_min_timestamp session variable is compared to
'values in the sync_row_timestamp column in the tracking table to
'determine which rows to delete.
Dim delCustomerCmd As New SqlCommand()
With delCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyDelete"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.DeleteCommand = delCustomerCmd
'Specify the command to select any conflicting rows.
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
End With
adapterCustomer.SelectRowCommand = selRowCustomerCmd
'Specify the command to insert metadata rows.
'The session variables in this command relate to columns in
'the tracking table.
Dim insMetadataCustomerCmd As New SqlCommand()
With insMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_InsertMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd
'Specify the command to update metadata rows.
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
'Specify the command to delete metadata rows.
Dim delMetadataCustomerCmd As New SqlCommand()
With delMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_DeleteMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd
'Add the adapter to the provider.
sampleDbProvider.SyncAdapters.Add(adapterCustomer)
' Configure commands that relate to the provider itself rather
' than the DbSyncAdapter object for each table:
' * SelectNewTimestampCommand: Returns the new high watermark for
' the current synchronization session.
' * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge,
' and a scope version (timestamp).
' * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.
' * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table
' or tracking table, to determine whether for each table the destination already
' has all of the changes from the source. If a destination table has all the changes,
' SelectIncrementalChangesCommand is not called for that table.
' There are additional commands related to metadata cleanup that are not
' included in this application.
'Select a new timestamp.
'During each synchronization, the new value and
'the last value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
Dim selectNewTimestampCommand As New SqlCommand()
Dim newTimestampVariable As String = "@" & DbSyncSession.SyncNewTimestamp
With selectNewTimestampCommand
.CommandText = "SELECT " & newTimestampVariable & " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand
'Specify the command to select local replica metadata.
Dim selReplicaInfoCmd As New SqlCommand()
With selReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "SELECT " _
& "scope_id, " _
& "scope_local_id, " _
& "scope_sync_knowledge, " _
& "scope_tombstone_cleanup_knowledge, " _
& "scope_timestamp " _
& "FROM Sync.ScopeInfo " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName
.Parameters.Add("@" & DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
End With
sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd
'Specify the command to update local replica metadata.
Dim updReplicaInfoCmd As New SqlCommand()
With updReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "UPDATE Sync.ScopeInfo SET " _
& "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
& "scope_id = @" + DbSyncSession.SyncScopeId + ", " _
& "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " _
& " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " _
& "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"
.Parameters.Add("@" & DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" & DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" & DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd
'Return the maximum timestamp from the Customer_Tracking table.
'If more tables are synchronized, the query should UNION
'all of the results. The table name is not schema-qualified
'in this case because the name was not schema qualified in the
'DbSyncAdapter constructor.
Dim selTableMaxTsCmd As New SqlCommand()
selTableMaxTsCmd.CommandType = CommandType.Text
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
& "MAX(local_update_peer_timestamp) AS max_timestamp " _
& "FROM Sync.Customer_Tracking"
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd
Return sampleDbProvider
End Function
End Class
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
Console.WriteLine([String].Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
Console.WriteLine([String].Empty)
End Sub
End Class
End Class
Consulte também
Outros recursos
Como provisionar um banco de dados de servidor para sincronização de colaboração (não SQL Server)
Sincronizando outros bancos de dados compatíveis com ADO.NET