Procedura: scambiare le modifiche incrementali bidirezionali ai dati tra un client e un server
In questo argomento viene illustrato come scambiare le modifiche ai dati eseguendo una sincronizzazione bidirezionale tra un database server e un database client di SQL Server Compact. 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.
Esempio
Il codice di esempio contenuto in questo argomento illustra come configurare la sincronizzazione bidirezionale per la tabella Customer
dal database di esempio Sync Framework. Per una panoramica delle proprietà utilizzate per specificare la direzione di sincronizzazione, vedere Procedura: specificare la sincronizzazione snapshot, di download, di caricamento e bidirezionale.
Per la sincronizzazione bidirezionale è necessario tenere traccia delle modifiche incrementali ai dati e applicarle ai database server e client. Sync Framework gestisce il database client. Tuttavia, è necessario configurare il database server e specificare i comandi che selezionano le modifiche e le applicano al database server. Tali comandi sono inclusi nel codice di esempio. Per informazioni sulla configurazione del database server, vedere Rilevamento delle modifiche nel database server.
Componenti chiave dell'API
Questa sezione fornisce esempi di codice che segnalano i componenti chiave dell'API utilizzati nella sincronizzazione bidirezionale. Nell'esempio di codice riportato di seguito viene specificata la direzione di sincronizzazione e come creare la tabella sul client.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.Bidirectional;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.Bidirectional
customerSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Nell'esempio di codice riportato di seguito viene specificato un comando per recuperare un nuovo valore di ancoraggio dal server. In questo caso, nel database client viene recuperato e memorizzato un valore di timestamp. Durante ogni sincronizzazione viene utilizzato il nuovo valore di ancoraggio e l'ultimo valore di ancoraggio proveniente dalla precedente sincronizzazione: la sincronizzazione interesserà il set di modifiche tra il limite superiore e inferiore. Verranno visualizzati i parametri di ancoraggio utilizzati nei comandi che selezionano le modifiche dal database server.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
Nell'esempio di codice riportato di seguito viene specificato un comando per selezionare gli aggiornamenti incrementali provenienti dal server da applicare al client. Si tratta di un'istruzione SELECT
che include le colonne da sincronizzare. È inclusa una clausola WHERE
che utilizza le colonne di rilevamento e i valori di ancoraggio recuperati da SelectNewAnchorCommand. La clausola WHERE
include anche UpdateId <> @sync_client_id
. Tale parametro impedisce il download immediato nel client delle modifiche caricate durante la stessa sincronizzazione. Se l'applicazione richiede la restituzione al client delle modifiche, omettere questa istruzione. Per informazioni su come filtrare i dati scaricati, vedere Procedura: filtrare righe e colonne.
SqlCommand customerIncrUpdates = new SqlCommand();
customerIncrUpdates.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer " +
"WHERE (UpdateTimestamp > @sync_last_received_anchor " +
"AND UpdateTimestamp <= @sync_new_received_anchor " +
"AND UpdateId <> @sync_client_id " +
"AND NOT (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertId <> @sync_client_id))";
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrUpdates.Connection = serverConn;
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;
Dim customerIncrUpdates As New SqlCommand()
With customerIncrUpdates
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
& "AND UpdateTimestamp <= @sync_new_received_anchor " _
& "AND UpdateId <> @sync_client_id " _
& "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertId <> @sync_client_id))"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates
Nell'esempio di codice riportato di seguito viene specificato un comando per applicare gli aggiornamenti dal client al server. Si tratta di un'istruzione UPDATE
che include le colonne da aggiornare. È inclusa una clausola WHERE
che utilizza nuovamente i valori di ancoraggio e l'ID client. La clausola WHERE
include anche @sync_force_write = 1
. Tale parametro forza l'applicazione degli aggiornamenti in conflitto. Per ulteriori informazioni su @sync_force_write
e su altre variabili di sessione, vedere Procedura: utilizzare le variabili di sessione.
SqlCommand customerUpdates = new SqlCommand();
customerUpdates.CommandText =
"UPDATE Sales.Customer SET " +
"CustomerName = @CustomerName, SalesPerson = @SalesPerson, " +
"CustomerType = @CustomerType, " +
"UpdateId = @sync_client_id " +
"WHERE (CustomerId = @CustomerId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_client_id)) " +
"SET @sync_row_count = @@rowcount";
customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
customerUpdates.Connection = serverConn;
customerSyncAdapter.UpdateCommand = customerUpdates;
Dim customerUpdates As New SqlCommand()
With customerUpdates
.CommandText = _
"UPDATE Sales.Customer SET " _
& "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " _
& "CustomerType = @CustomerType, " _
& "UpdateId = @sync_client_id " _
& "WHERE (CustomerId = @CustomerId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_client_id)) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
customerSyncAdapter.UpdateCommand = customerUpdates
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. Tenere inoltre presente l'utilizzo di SyncStatistics
per visualizzare le informazioni sulla sessione di sincronizzazione e degli eventi CreatingSchema
e SchemaCreated
per aggiungere un valore predefinito per la colonna CustomerId
. 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.
SampleStats sampleStats = new SampleStats();
//Request a password for the client database, and delete
//and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetPassword_SqlCeClientSync();
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server and client.
Utility.MakeDataChangesOnServer("Customer");
Utility.MakeDataChangesOnClient("Customer");
//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 Customer SyncGroup. This is not required
//for the single table we are synchronizing; it is typically
//used so that changes to multiple related tables are
//synchronized at the same time.
SyncGroup customerSyncGroup = new SyncGroup("Customer");
//Add the Customer table: specify a synchronization direction of
//Bidirectional, and that an existing table should be dropped.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.Bidirectional;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//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 Customer table, and then define
//the commands to synchronize changes:
//* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
// and SelectIncrementalDeletesCommand are used to select changes
// from the server that the client provider then applies to the client.
//* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
// to the server the changes that the client provider has selected
// from the client.
//Create the SyncAdapter.
SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");
//Select inserts from the server.
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer " +
"WHERE (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertTimestamp <= @sync_new_received_anchor " +
"AND InsertId <> @sync_client_id)";
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
//Apply inserts to the server.
SqlCommand customerInserts = new SqlCommand();
customerInserts.CommandText =
"INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson, CustomerType, InsertId, UpdateId) " +
"VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType, @sync_client_id, @sync_client_id) " +
"SET @sync_row_count = @@rowcount";
customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
customerInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
customerInserts.Connection = serverConn;
customerSyncAdapter.InsertCommand = customerInserts;
//Select updates from the server.
SqlCommand customerIncrUpdates = new SqlCommand();
customerIncrUpdates.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer " +
"WHERE (UpdateTimestamp > @sync_last_received_anchor " +
"AND UpdateTimestamp <= @sync_new_received_anchor " +
"AND UpdateId <> @sync_client_id " +
"AND NOT (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertId <> @sync_client_id))";
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrUpdates.Connection = serverConn;
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;
//Apply updates to the server.
SqlCommand customerUpdates = new SqlCommand();
customerUpdates.CommandText =
"UPDATE Sales.Customer SET " +
"CustomerName = @CustomerName, SalesPerson = @SalesPerson, " +
"CustomerType = @CustomerType, " +
"UpdateId = @sync_client_id " +
"WHERE (CustomerId = @CustomerId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_client_id)) " +
"SET @sync_row_count = @@rowcount";
customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
customerUpdates.Connection = serverConn;
customerSyncAdapter.UpdateCommand = customerUpdates;
//Select deletes from the server.
SqlCommand customerIncrDeletes = new SqlCommand();
customerIncrDeletes.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor " +
"AND DeleteId <> @sync_client_id)";
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerIncrDeletes.Connection = serverConn;
customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes;
//Apply deletes to the server.
SqlCommand customerDeletes = new SqlCommand();
customerDeletes.CommandText =
"DELETE FROM Sales.Customer " +
"WHERE (CustomerId = @CustomerId) " +
"AND (@sync_force_write = 1 " +
"OR (UpdateTimestamp <= @sync_last_received_anchor " +
"OR UpdateId = @sync_client_id)) " +
"SET @sync_row_count = @@rowcount " +
"IF (@sync_row_count > 0) BEGIN " +
"UPDATE Sales.Customer_Tombstone " +
"SET DeleteId = @sync_client_id " +
"WHERE (CustomerId = @CustomerId) " +
"END";
customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
customerDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
customerDeletes.Connection = serverConn;
customerSyncAdapter.DeleteCommand = customerDeletes;
//Add the SyncAdapter to the server synchronization provider.
this.SyncAdapters.Add(customerSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but here we use this class to handle client
//provider events.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
//We use the CreatingSchema event to change the schema
//by using the API. We use the SchemaCreated event to
//change the schema by using SQL.
this.CreatingSchema +=new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
this.SchemaCreated +=new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
}
private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
//Set the RowGuid property because it is not copied
//to the client by default. This is also a good time
//to specify literal defaults with .Columns[ColName].DefaultValue;
//but we will specify defaults like NEWID() by calling
//ALTER TABLE after the table is created.
Console.Write("Creating schema for " + e.Table.TableName + " | ");
e.Schema.Tables["Customer"].Columns["CustomerId"].RowGuid = true;
}
private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
{
//Call ALTER TABLE on the client. This must be done
//over the same connection and within the same
//transaction that Sync Framework uses
//to create the schema on the client.
Utility util = new Utility();
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName);
Console.WriteLine("Schema created for " + e.Table.TableName);
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.TotalChangesUploaded);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Request a password for the client database, and delete
'and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetPassword_SqlCeClientSync()
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, True)
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server and client.
Utility.MakeDataChangesOnServer("Customer")
Utility.MakeDataChangesOnClient("Customer")
'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 Customer SyncGroup. This is not required
'for the single table we are synchronizing; it is typically
'used so that changes to multiple related tables are
'synchronized at the same time.
Dim customerSyncGroup As New SyncGroup("Customer")
'Add the Customer table: specify a synchronization direction of
'Bidirectional, and that an existing table should be dropped.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.Bidirectional
customerSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
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 Customer table, and then define
'the commands to synchronize changes:
'* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,
' and SelectIncrementalDeletesCommand are used to select changes
' from the server that the client provider then applies to the client.
'* InsertCommand, UpdateCommand, and DeleteCommand are used to apply
' to the server the changes that the client provider has selected
' from the client.
'Create the SyncAdapter.
Dim customerSyncAdapter As New SyncAdapter("Customer")
'Select inserts from the server.
Dim customerIncrInserts As New SqlCommand()
With customerIncrInserts
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertTimestamp <= @sync_new_received_anchor " _
& "AND InsertId <> @sync_client_id)"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
'Apply inserts to the server.
Dim customerInserts As New SqlCommand()
With customerInserts
.CommandText = _
"INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson, CustomerType, InsertId, UpdateId) " _
& "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType, @sync_client_id, @sync_client_id) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
customerSyncAdapter.InsertCommand = customerInserts
'Select updates from the server.
Dim customerIncrUpdates As New SqlCommand()
With customerIncrUpdates
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE (UpdateTimestamp > @sync_last_received_anchor " _
& "AND UpdateTimestamp <= @sync_new_received_anchor " _
& "AND UpdateId <> @sync_client_id " _
& "AND NOT (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertId <> @sync_client_id))"
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates
'Apply updates to the server.
Dim customerUpdates As New SqlCommand()
With customerUpdates
.CommandText = _
"UPDATE Sales.Customer SET " _
& "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " _
& "CustomerType = @CustomerType, " _
& "UpdateId = @sync_client_id " _
& "WHERE (CustomerId = @CustomerId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_client_id)) " _
& "SET @sync_row_count = @@rowcount"
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
customerSyncAdapter.UpdateCommand = customerUpdates
'Select deletes from the server.
Dim customerIncrDeletes As New SqlCommand()
With customerIncrDeletes
.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor " _
& "AND DeleteId <> @sync_client_id)"
.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes
'Apply deletes to the server.
Dim customerDeletes As New SqlCommand()
With customerDeletes
.CommandText = _
"DELETE FROM Sales.Customer " _
& "WHERE (CustomerId = @CustomerId) " _
& "AND (@sync_force_write = 1 " _
& "OR (UpdateTimestamp <= @sync_last_received_anchor " _
& "OR UpdateId = @sync_client_id)) " _
& "SET @sync_row_count = @@rowcount " _
& "IF (@sync_row_count > 0) BEGIN " _
& "UPDATE Sales.Customer_Tombstone " _
& "SET DeleteId = @sync_client_id " _
& "WHERE (CustomerId = @CustomerId) " _
& "END"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int)
.Connection = serverConn
End With
customerSyncAdapter.DeleteCommand = customerDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(customerSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but here we use this class to handle client
'provider events.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
'We use the CreatingSchema event to change the schema
'by using the API. We use the SchemaCreated event to
'change the schema by using SQL.
AddHandler Me.CreatingSchema, AddressOf SampleClientSyncProvider_CreatingSchema
AddHandler Me.SchemaCreated, AddressOf SampleClientSyncProvider_SchemaCreated
End Sub 'New
Private Sub SampleClientSyncProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
'Set the RowGuid property because it is not copied
'to the client by default. This is also a good time
'to specify literal defaults with .Columns[ColName].DefaultValue;
'but we will specify defaults like NEWID() by calling
'ALTER TABLE after the table is created.
Console.Write("Creating schema for " + e.Table.TableName + " | ")
e.Schema.Tables("Customer").Columns("CustomerId").RowGuid = True
End Sub 'SampleClientSyncProvider_CreatingSchema
Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
'Call ALTER TABLE on the client. This must be done
'over the same connection and within the same
'transaction that Sync Framework uses
'to create the schema on the client.
Dim util As New Utility()
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, e.Table.TableName)
Console.WriteLine("Schema created for " + e.Table.TableName)
End Sub 'SampleClientSyncProvider_SchemaCreated
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.TotalChangesUploaded)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats
Vedere anche
Concetti
Architettura e classi per la sincronizzazione client e server
Procedura: specificare la sincronizzazione snapshot, di download, di caricamento e bidirezionale