Como criar exibições de dados em um cliente
Este tópico mostra como usar o Sync Framework para combinar duas ou mais tabelas de servidor em uma única tabela no banco de dados cliente. Os exemplos neste tópico se concentram nos seguintes eventos e 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.
Exemplo
Os exemplos de código a seguir mostram como sincronizar as tabelas Customer e CustomerContact no banco de dados de amostra Sync Framework. Durante a sincronização inicial, os dados das duas tabelas são selecionados com o comando de inserções incrementais, baixados para o cliente e, em seguida, inseridos na tabela CustomerInfo. Durante a sincronização subsequente, as alterações qualificadas são baixadas para o cliente.
Partes Principais da API
Esta seção fornece exemplos de código que destacam as partes principais da API usadas na combinação de tabelas. Os exemplos de código a seguir especificam os objetos SyncTable e SyncAdapter que usam o nome CustomerInfo. A especificação do nome em ambos os locais permite que SyncAgent e provedores convertam seleções das tabelas Customer e CustomerContact em inserções para a tabela CustomerInfo.
SyncTable customerInfoSyncTable = new SyncTable("CustomerInfo");
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerInfoSyncTable);
SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo");
Dim customerInfoSyncTable As New SyncTable("CustomerInfo")
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerInfoSyncTable)
Dim customerInfoSyncAdapter As New SyncAdapter("CustomerInfo")
O exemplo de código a seguir inclui a consulta da propriedade SelectIncrementalInsertsCommand. A consulta usa uma união para garantir que somente os clientes com informações de contato sejam baixados para o cliente durante a sincronização inicial. Nas sincronizações subsequentes, as inserções na tabela CustomerContact são baixadas; as inserções na tabela Customer são baixadas somente se uma linha para o cliente também for inserida na tabela CustomerContact. Observe que a lista selecionada não inclui todas as colunas. Como com todas as consultas usadas na sincronização, a lógica de cada consulta depende dos requisitos do aplicativo. Por exemplo, você pode baixar todos os clientes, mesmo se eles não tiverem informações de contato.
SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand();
customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text;
customerInfoIncrementalInsertsCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.InsertTimestamp > @sync_last_received_anchor " +
"AND c.InsertTimestamp <= @sync_new_received_anchor) OR " +
"(cc.InsertTimestamp > @sync_last_received_anchor " +
"AND cc.InsertTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand;
Dim customerInfoIncrementalInsertsCommand As New SqlCommand()
With customerInfoIncrementalInsertsCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " _
& "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " _
& "(cc.InsertTimestamp > @sync_last_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand
O exemplo de código a seguir inclui a consulta da propriedade SelectIncrementalDeletesCommand. A consulta usa duas instruções SELECT e uma UNION para selecionar exclusões para as tabelas Customer e CustomerContact. A primeira instrução SELECT se une com a tabela CustomerContact para recuperar a coluna PhoneType. Essa coluna é parte da chave composta da tabela CustomerInfo. A lógica é a seguinte:
Se um cliente foi excluído, todas as linhas daquele cliente serão excluídas no cliente.
Se as informações de contato foram excluídas, apenas essa linha é excluída no cliente.
Se uma linha de cliente e informações de contato daquele cliente foram excluídas, pelo menos uma exclusão adicional será baixada. Isso não causará nenhum erro quando as alterações forem aplicadas no banco de dados cliente.
SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand();
customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text;
customerInfoIncrementalDeletesCommand.CommandText =
"SELECT c.CustomerId, cc.PhoneType " +
"FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor)) " +
"UNION " +
"SELECT CustomerId, PhoneType " +
"FROM Sales.CustomerContact_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand;
Dim customerInfoIncrementalDeletesCommand As New SqlCommand()
With customerInfoIncrementalDeletesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, cc.PhoneType " _
& "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor)) " _
& "UNION " _
& "SELECT CustomerId, PhoneType " _
& "FROM Sales.CustomerContact_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_initialized", SqlDbType.Bit)
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand
O exemplo de código a seguir cria uma chave primária composta da tabela CustomerInfo. Essa chave corresponde à chave da tabela CustomerContact. O Sync Framework pode deduzir o esquema das tabelas no servidor, mas a chave deve ser especificada neste caso. Você também pode criar manualmente o esquema, conforme descrito em Como inicializar o banco de dados cliente e trabalhar com esquemas de tabela.
string[] customerInfoPrimaryKey = new string[2];
customerInfoPrimaryKey[0] = "CustomerId";
customerInfoPrimaryKey[1] = "PhoneType";
e.Schema.Tables["CustomerInfo"].PrimaryKey = customerInfoPrimaryKey;
Dim customerInfoPrimaryKey(1) As String
customerInfoPrimaryKey(0) = "CustomerId"
customerInfoPrimaryKey(1) = "PhoneType"
e.Schema.Tables("CustomerInfo").PrimaryKey = customerInfoPrimaryKey
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. Observe que a inserção na tabela Customer na classe Utility não é baixada na sincronização subsequente porque não há uma linha correspondente na tabela CustomerContact.
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.
Utility.MakeDataChangesOnServer("Customer");
Utility.MakeDataChangesOnServer("CustomerContact");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return 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 Customer table: specify a synchronization direction of
//DownloadOnly, and that an existing table should be dropped.
SyncTable customerInfoSyncTable = new SyncTable("CustomerInfo");
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerInfoSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the CustomerInfo table. The CustomerInfo
//table on the client is a combination of the Customer and CustomerContact
//tables on the server. This table is download-only, as specified in
//SampleSyncAgent.
SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo");
//Specify synchronization commands. The CustomerInfo table
//is download-only, so we do not define commands to apply changes to
//the server. Each command joins the base tables or tombstone tables
//to select the appropriate incremental changes. For this application,
//the logic is as follows:
//* Select all inserts for customers that have contact information.
// This results in more than one row for a customer if that customer
// has more than one phone number.
//* Select all updates for customer and contact information that has
// already been downloaded.
//* Select all deletes for customer and contact information that has
// already been downloaded. If a customer has been deleted, delete
// all of the rows for that customer. If a phone number has been
// deleted, delete only that row.
//Select inserts.
SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand();
customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text;
customerInfoIncrementalInsertsCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.InsertTimestamp > @sync_last_received_anchor " +
"AND c.InsertTimestamp <= @sync_new_received_anchor) OR " +
"(cc.InsertTimestamp > @sync_last_received_anchor " +
"AND cc.InsertTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand;
//Select updates.
SqlCommand customerInfoIncrementalUpdatesCommand = new SqlCommand();
customerInfoIncrementalUpdatesCommand.CommandType = CommandType.Text;
customerInfoIncrementalUpdatesCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " +
"AND c.UpdateTimestamp <= @sync_new_received_anchor " +
"AND c.InsertTimestamp <= @sync_last_received_anchor) " +
"OR (cc.UpdateTimestamp > @sync_last_received_anchor " +
"AND cc.UpdateTimestamp <= @sync_new_received_anchor " +
"AND cc.InsertTimestamp <= @sync_last_received_anchor))";
customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalUpdatesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand;
//Select deletes.
SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand();
customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text;
customerInfoIncrementalDeletesCommand.CommandText =
"SELECT c.CustomerId, cc.PhoneType " +
"FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor)) " +
"UNION " +
"SELECT CustomerId, PhoneType " +
"FROM Sales.CustomerContact_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand;
//Add the SyncAdapter to the provider.
this.SyncAdapters.Add(customerInfoSyncAdapter);
}
}
//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;
//Handle the two schema-related events.
this.CreatingSchema += new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
this.SchemaCreated += new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
}
private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
Console.Write("Creating schema for " + e.Table.TableName + " | ");
//Create a compostite primary key for the CustomerInfo table.
string[] customerInfoPrimaryKey = new string[2];
customerInfoPrimaryKey[0] = "CustomerId";
customerInfoPrimaryKey[1] = "PhoneType";
e.Schema.Tables["CustomerInfo"].PrimaryKey = customerInfoPrimaryKey;
}
private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
{
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 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.
Utility.MakeDataChangesOnServer("Customer")
Utility.MakeDataChangesOnServer("CustomerContact")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return 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 Customer table: specify a synchronization direction of
'DownloadOnly, and that an existing table should be dropped.
Dim customerInfoSyncTable As New SyncTable("CustomerInfo")
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerInfoSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the CustomerInfo table. The CustomerInfo
'table on the client is a combination of the Customer and CustomerContact
'tables on the server. This table is download-only, as specified in
'SampleSyncAgent.
Dim customerInfoSyncAdapter As New SyncAdapter("CustomerInfo")
'Specify synchronization commands. The CustomerInfo table
'is download-only, so we do not define commands to apply changes to
'the server. Each command joins the base tables or tombstone tables
'to select the appropriate incremental changes. For this application,
'the logic is as follows:
'* Select all inserts for customers that have contact information.
' This results in more than one row for a customer if that customer
' has more than one phone number.
'* Select all updates for customer and contact information that has
' already been downloaded.
'* Select all deletes for customer and contact information that has
' already been downloaded. If a customer has been deleted, delete
' all of the rows for that customer. If a phone number has been
' deleted, delete only that row.
'Select inserts.
Dim customerInfoIncrementalInsertsCommand As New SqlCommand()
With customerInfoIncrementalInsertsCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " _
& "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " _
& "(cc.InsertTimestamp > @sync_last_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand
'Select updates.
Dim customerInfoIncrementalUpdatesCommand As New SqlCommand()
With customerInfoIncrementalUpdatesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " _
& "AND c.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND c.InsertTimestamp <= @sync_last_received_anchor) " _
& "OR (cc.UpdateTimestamp > @sync_last_received_anchor " _
& "AND cc.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_last_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand
'Select deletes.
Dim customerInfoIncrementalDeletesCommand As New SqlCommand()
With customerInfoIncrementalDeletesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, cc.PhoneType " _
& "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor)) " _
& "UNION " _
& "SELECT CustomerId, PhoneType " _
& "FROM Sales.CustomerContact_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_initialized", SqlDbType.Bit)
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand
'Add the SyncAdapter to the provider.
Me.SyncAdapters.Add(customerInfoSyncAdapter)
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
'Handle the two schema-related events.
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)
Console.Write("Creating schema for " + e.Table.TableName + " | ")
'Create a compostite primary key for the CustomerInfo table.
Dim customerInfoPrimaryKey(1) As String
customerInfoPrimaryKey(0) = "CustomerId"
customerInfoPrimaryKey(1) = "PhoneType"
e.Schema.Tables("CustomerInfo").PrimaryKey = customerInfoPrimaryKey
End Sub 'SampleClientSyncProvider_CreatingSchema
Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
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 Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats
Consulte também
Outros recursos
Programando tarefas comuns de sincronização do cliente e do servidor