Procedura: specificare l'ordine e la dimensione dei batch di modifiche
In questo argomento viene descritto come specificare l'ordine e la dimensione dei batch delle modifiche scaricate in un database client tramite Sync Framework. Gli esempi contenuti in questo argomento si concentrano sui tipi e sugli eventi seguenti di Sync Framework:
Per informazioni su come eseguire il codice di esempio, vedere "Applicazioni di esempio nelle procedure" in Programmazione di attività comuni di sincronizzazione client e server.
Informazioni sull'ordine di sincronizzazione e la divisione in batch
Per ogni tabella sincronizzata, le modifiche vengono selezionate dal database server nell'ordine di inserimenti, aggiornamenti e quindi eliminazioni e vengono applicate al database client nell'ordine di eliminazioni, inserimenti e quindi aggiornamenti. Quando vengono sincronizzate diverse tabelle, l'ordine di elaborazione di ogni tabella dipende dall'ordine in cui il relativo oggetto SyncTable è stato aggiunto alla raccolta di tabelle per l'agente di sincronizzazione. Ad esempio, se le tabelle Customer e OrderHeader vengono aggiunte in questo ordine, vengono selezionati innanzitutto gli inserimenti nella tabella Customer, seguiti dagli aggiornamenti e dalle eliminazioni. Successivamente vengono selezionate le modifiche apportate alla tabella OrderHeader. Tutte le modifiche apportate alla tabella Customer vengono applicate al database client in un'unica transazione (se non si utilizza l'invio in batch), seguite dalle modifiche apportate a OrderHeader che vengono applicate in una seconda transazione. Se le tabelle Customer e OrderHeader vengono assegnate allo stesso oggetto SyncGroup, gli inserimenti, gli aggiornamenti e le eliminazioni per entrambe le tabelle vengono selezionati una sola volta. Tutte le modifiche vengono applicate al database client in un'unica transazione (anche in questo caso se non si utilizza la divisione in batch).
Per impostazione predefinita, in Sync Framework le modifiche non vengono divise in batch, ma vengono scaricate e caricate dal database client come un'unità. Per molte applicazioni, risulta utile dividere le modifiche in batch più piccoli. Se ad esempio una sessione di sincronizzazione viene interrotta, il processo può essere riavviato dall'ultimo batch, evitando la necessità di inviare di nuovo tutte le modifiche. È possibile riscontrare anche un vantaggio per le prestazioni, perché il client può gestire un batch più piccolo di modifiche alla volta. A causa di questi vantaggi, Sync Framework consente alle applicazioni di scaricare batch di modifiche nel client. La divisione in batch non è supportata per il caricamento.
Per abilitare la divisione in batch, è necessario specificare un valore per la proprietà BatchSize e creare un comando per la proprietà SelectNewAnchorCommand in grado di restituire valori non ricorsivi per ciascun batch di modifiche. Senza divisione in batch, le applicazioni utilizzano i valori relativi a nuovo ancoraggio e ultimo ancoraggio per definire i limiti superiore e inferiore per l'intero set di modifiche da scaricare. Per ulteriori informazioni, vedere Guida introduttiva: sincronizzazione client e server. Con la divisione in batch, il valore di ancoraggio massimo ricevuto definisce il limite superiore per l'intero set di modifiche, mentre i valori relativi a nuovo ancoraggio e ultimo ancoraggio definiscono i limiti superiore e inferiore per ogni batch di modifiche. L'evento SessionProgress consente il monitoraggio dello stato di avanzamento complessivo della sincronizzazione, mentre la proprietà BatchProgress fornisce l'accesso a informazioni sullo stato a livello di batch.
Esempio
Negli esempi di codice seguenti viene illustrato come sincronizzare le tabelle Customer e OrderHeader nel database di esempio di Sync Framework. Le modifiche relative a queste tabelle sono scaricabili in batch, ognuno dei quali contiene 50 modifiche. Con la sincronizzazione iniziale vengono scaricate 10 righe. Tutte le righe vengono scaricate in un unico batch e applicate in un'unica transazione. Con la sincronizzazione successiva vengono scaricate 92 righe in due batch. Ogni batch contiene le modifiche della tabella Customer e della tabella OrderHeader e viene applicato in un'unica transazione.
Componenti chiave dell'API
In questa sezione vengono forniti esempi di codice in cui sono evidenziati i componenti chiave dell'API da utilizzare per l'ordinamento e la divisione in batch delle modifiche. Nell'esempio di codice riportato di seguito viene illustrata una classe che deriva da SyncAgent. Viene creato un oggetto SyncGroup per le tabelle Customer e OrderHeader.
//Create a SyncGroup so that changes to Customer
//and OrderHeader are made in one transaction.
SyncGroup customerOrderSyncGroup = new SyncGroup("CustomerOrder");
//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
'Create a SyncGroup so that changes to Customer
'and OrderHeader are made in one transaction.
Dim customerOrderSyncGroup As New SyncGroup("CustomerOrder")
'Add each table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
customerSyncTable.SyncGroup = customerOrderSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
Nell'esempio di codice seguente viene illustrata una classe che deriva da DbServerSyncProvider. Viene creato un comando di ancoraggio per un'applicazione che non utilizza la divisione in batch. Viene restituito un nuovo valore di ancoraggio una sola volta per l'intero set di modifiche da sincronizzare. Questo esempio è stato incluso per consentire di rilevare la differenza tra un comando che utilizza la divisione in batch e uno che non la utilizza.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand
Nell'esempio di codice seguente viene creato un comando di ancoraggio che è possibile utilizzare se le modifiche vengono inviate in batch. In questo caso viene restituito un nuovo valore di ancoraggio per ogni batch di modifiche, anziché un unico valore per l'intero set di modifiche. Vengono utilizzate la proprietà BatchSize per specificare il numero di modifiche da includere in ogni batch e le variabili di sessione per trasferire i valori di ancoraggio tra una stored procedure e il runtime di sincronizzazione. Se i comandi dell'adattatore di sincronizzazione vengono scritti manualmente, vengono comunque utilizzate le variabili di sessione @sync_new_received_anchor e @sync_last_received_anchor. La variabile di sessione @sync_max_received_anchor viene utilizzata solo dal comando di nuovo ancoraggio.
SqlCommand selectNewAnchorCommand = new SqlCommand();
selectNewAnchorCommand.Connection = serverConn;
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncMaxReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncNewReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncBatchCount].Direction = ParameterDirection.InputOutput;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
this.BatchSize = 50;
Dim selectNewAnchorCommand As New SqlCommand()
selectNewAnchorCommand.Connection = serverConn
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor"
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4)
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncMaxReceivedAnchor).Direction = ParameterDirection.Output
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncNewReceivedAnchor).Direction = ParameterDirection.Output
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncBatchCount).Direction = ParameterDirection.InputOutput
Me.SelectNewAnchorCommand = selectNewAnchorCommand
Me.BatchSize = 50
Nell'esempio di codice seguente viene creata una stored procedure che genera nuovi valori di ancoraggio, un valore massimo di ancoraggio e il conteggio corrente di batch per inserimenti e aggiornamenti. La procedura consente al provider di sincronizzazione server di selezionare i batch di modifiche dal database server. La logica di questa stored procedure è un esempio, ma è possibile utilizzare qualsiasi logica purché fornisca i valori di output illustrati in questo esempio. Uno dei limiti del codice di esempio è che si possono verificare batch vuoti se una riga viene modificata più di 50 volte tra una sincronizzazione e l'altra. È possibile aggiungere logica per gestire questa situazione.
CREATE PROCEDURE usp_GetNewBatchAnchor (
@sync_last_received_anchor timestamp,
@sync_batch_size bigint,
@sync_max_received_anchor timestamp out,
@sync_new_received_anchor timestamp out,
@sync_batch_count int output)
AS
-- Set a default batch size if a valid one is not passed in.
IF @sync_batch_size IS NULL OR @sync_batch_size <= 0
SET @sync_batch_size = 1000
-- Before selecting the first batch of changes,
-- set the maximum anchor value for this synchronization session.
-- After the first time that this procedure is called,
-- Sync Framework passes a value for @sync_max_received_anchor
-- to the procedure. Batches of changes are synchronized until this
-- value is reached.
IF @sync_max_received_anchor IS NULL
SELECT @sync_max_received_anchor = MIN_ACTIVE_ROWVERSION() - 1
-- If this is the first synchronization session for a database,
-- get the lowest timestamp value from the tables. By default,
-- Sync Framework uses a value of 0 for @sync_last_received_anchor
-- on the first synchronization. If you do not set @sync_last_received_anchor,
-- this can cause empty batches to be downloaded until the lowest
-- timestamp value is reached.
IF @sync_last_received_anchor IS NULL OR @sync_last_received_anchor = 0
BEGIN
SELECT @sync_last_received_anchor = MIN(TimestampCol) FROM (
SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.Customer
UNION
SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.Customer
UNION
SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.OrderHeader
UNION
SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.OrderHeader
) MinTimestamp
SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
-- Determine how many batches are required during the initial synchronization.
IF @sync_batch_count <= 0
SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_last_received_anchor / @sync_batch_size))
END
ELSE
BEGIN
SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
-- Determine how many batches are required during subsequent synchronizations.
IF @sync_batch_count <= 0
SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_new_received_anchor / @sync_batch_size)) + 1
END
-- Check whether this is the last batch.
IF @sync_new_received_anchor >= @sync_max_received_anchor
BEGIN
SET @sync_new_received_anchor = @sync_max_received_anchor
IF @sync_batch_count <= 0
SET @sync_batch_count = 1
END
GO
Esempio di codice completo
L'esempio di codice completo seguente include gli esempi di codice descritti precedentemente e il codice aggiuntivo per eseguire la sincronizzazione. L'esempio richiede la classe Utility, disponibile in Classe di utilità per le procedure relative al provider di database.
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 and
//from SyncAgent events.
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("CustomerAndOrderHeader");
//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();
//Create a SyncGroup so that changes to Customer
//and OrderHeader are made in one transaction.
SyncGroup customerOrderSyncGroup = new SyncGroup("CustomerOrder");
//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
}
}
//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 call a stored procedure
//that returns an anchor that can be used with batches
//of changes.
SqlCommand selectNewAnchorCommand = new SqlCommand();
selectNewAnchorCommand.Connection = serverConn;
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncMaxReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncNewReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncBatchCount].Direction = ParameterDirection.InputOutput;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
this.BatchSize = 50;
//Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// and where changes are made.
// * Specify download only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
//Customer table
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
//OrderHeader table.
SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);
orderHeaderBuilder.TableName = "Sales.OrderHeader";
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
orderHeaderSyncAdapter.TableName = "OrderHeader";
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//Handle the ChangesSelected event, and display
//information to the console.
this.ChangesSelected += new EventHandler<ChangesSelectedEventArgs>(SampleServerSyncProvider_ChangesSelected);
}
public void SampleServerSyncProvider_ChangesSelected(object sender, ChangesSelectedEventArgs e)
{
Console.WriteLine("Total number of batches: " + e.Context.BatchCount);
Console.WriteLine("Changes applied for group " + e.GroupMetadata.GroupName);
Console.WriteLine("Inserts applied for group: " + e.Context.GroupProgress.TotalInserts.ToString());
Console.WriteLine("Updates applied for group: " + e.Context.GroupProgress.TotalUpdates.ToString());
Console.WriteLine("Deletes applied for group: " + e.Context.GroupProgress.TotalDeletes.ToString());
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
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 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 Stats ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization Stats ****");
}
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 and
'from SyncAgent events.
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("CustomerAndOrderHeader")
'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()
'Create a SyncGroup so that changes to Customer
'and OrderHeader are made in one transaction.
Dim customerOrderSyncGroup As New SyncGroup("CustomerOrder")
'Add each table: specify a synchronization direction of
'DownloadOnly.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
customerSyncTable.SyncGroup = customerOrderSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
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 call a stored procedure
'that returns an anchor that can be used with batches
'of changes.
Dim selectNewAnchorCommand As New SqlCommand()
selectNewAnchorCommand.Connection = serverConn
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor"
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4)
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4)
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncMaxReceivedAnchor).Direction = ParameterDirection.Output
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncNewReceivedAnchor).Direction = ParameterDirection.Output
selectNewAnchorCommand.Parameters("@" + SyncSession.SyncBatchCount).Direction = ParameterDirection.InputOutput
Me.SelectNewAnchorCommand = selectNewAnchorCommand
Me.BatchSize = 50
'Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
' * Specify the base table and tombstone table names.
' * Specify the columns that are used to track when
' and where changes are made.
' * Specify download only synchronization.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
'Customer table
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
customerBuilder.SyncDirection = SyncDirection.DownloadOnly
customerBuilder.CreationTrackingColumn = "InsertTimestamp"
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp"
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp"
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
'OrderHeader table.
Dim orderHeaderBuilder As New SqlSyncAdapterBuilder(serverConn)
orderHeaderBuilder.TableName = "Sales.OrderHeader"
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone"
orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp"
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp"
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp"
Dim orderHeaderSyncAdapter As SyncAdapter = orderHeaderBuilder.ToSyncAdapter()
orderHeaderSyncAdapter.TableName = "OrderHeader"
Me.SyncAdapters.Add(orderHeaderSyncAdapter)
'Handle the ChangesSelected event, and display
'information to the console.
AddHandler Me.ChangesSelected, AddressOf SampleServerSyncProvider_ChangesSelected
End Sub 'New
Public Sub SampleServerSyncProvider_ChangesSelected(ByVal sender As Object, ByVal e As ChangesSelectedEventArgs)
Console.WriteLine("Total number of batches: " & e.Context.BatchCount)
Console.WriteLine("Changes applied for group " & e.GroupMetadata.GroupName)
Console.WriteLine("Inserts applied for group: " & e.Context.GroupProgress.TotalInserts.ToString())
Console.WriteLine("Updates applied for group: " & e.Context.GroupProgress.TotalUpdates.ToString())
Console.WriteLine("Deletes applied for group: " & e.Context.GroupProgress.TotalDeletes.ToString())
End Sub 'SampleServerSyncProvider_ChangesSelected
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
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
End Sub 'New
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 Stats ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization Stats ****")
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
Vedere anche
Concetti
Programmazione di attività comuni di sincronizzazione client e server