Procedura: filtrare righe e colonne
In questo argomento viene descritto come filtrare righe e colonne nelle tabelle sincronizzate. Gli esempi contenuti in questo argomento riguardano i tipi 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 sui filtri
Spesso il database client richiede solo un subset dei dati disponibili nel server. Sync Framework consente di specificare le righe e le colonne richieste da un client e se costruire i comandi di sincronizzazione manualmente o utilizzando SqlSyncAdapterBuilder. Filtrando i dati, è possibile eseguire le attività seguenti:
Ridurre la quantità di dati inviati in rete.
Ridurre la quantità di spazio di archiviazione richiesto nel client.
Fornire partizioni dati personalizzate basate sui requisiti del singolo client.
Evitare o ridurre i conflitti in fase di aggiornamento dei dati da parte di un client, dato che è possibile inviare partizioni dati diverse a client diversi. (Due client non potranno aggiornare gli stessi valori dei dati.)
I filtri possono basarsi su una singola tabella, oppure fare riferimento a più tabelle utilizzando le clausole JOIN o più istruzioni SELECT. Basare i filtri su più tabelle consente a un utente di scaricare una partizione dei dati: utile per un venditore che richiede soltanto i dati per i propri clienti e tutti i relativi ordini. Il filtro su più tabelle fornisce flessibilità. Tuttavia, è necessario mantenere i filtri più semplici possibile e analizzarne le prestazioni con l'aumentare del numero di tabelle. È inoltre richiesta l'indicizzazione delle colonne su cui si basano i filtri.
Importante
Non fare affidamento sui filtri per la sicurezza. La possibilità di filtrare i dati dal server in base a un ID client o utente non rappresenta una caratteristica di sicurezza. In altre parole, non è possibile utilizzare tale approccio per impedire a un client di leggere i dati appartenenti a un altro client. Questo tipo di filtro è utile solo per il partizionamento dei dati e per ridurre la quantità di dati inviati al database client.
Sync Framework non fornisce la gestione automatica delle partizioni. Le conseguenze sono le seguenti:
Se si aggiorna una riga e si modifica il valore di una colonna utilizzata per un filtro, la riga non viene eliminata automaticamente dalle partizioni dei client in cui era inclusa. Si consideri un'applicazione che scarica i dati dei clienti per un venditore in base al codice postale. Se un cliente sposta i propri uffici in un'area con un nuovo codice postale, i dati relativi a tale cliente non vengono rimossi dall'archivio del venditore in cui si trovavano in origine. Se è necessaria tale funzionalità, è possibile sviluppare un sistema che consenta a Sync Framework di scaricare tale aggiornamento come un'eliminazione.
Non esistono meccanismi che impediscano a un'applicazione di inserire nel client dati esterni alla partizione del client. È comunque possibile aggiungere vincoli al client per impedire inserimenti e aggiornamenti esterni alla partizione.
Filtri basati sulle colonne non chiave
Con il rilevamento delle modifiche di SQL Server e con alcuni sistemi di rilevamento personalizzati, per le righe eliminate viene mantenuta solo la chiave primaria. Se un filtro è basato solo sulla chiave primaria, la query specificata per la proprietà SelectIncrementalDeletesCommand può identificare il corretto subset di righe e scaricarle nel client. Se il filtro è basato su colonne esterne alla chiave primaria, la query ha esito negativo in quanto fa riferimento a colonne che non esistono più per le righe eliminate. Per risolvere questo problema, utilizzare uno degli approcci seguenti:
Includere tutte le colonne di filtro nella chiave primaria. Inserire le colonne aggiuntive alla fine della chiave, in modo da non influire sulla selettività della chiave.
Filtrare solo gli inserimenti e gli aggiornamenti. Le eliminazioni superflue verranno scaricate nel client, ma saranno ignorate.
Eseguire eliminazioni logiche nel server. Anziché eliminare la riga, utilizzare un trigger ON DELETE per aggiornare una colonna che contrassegna la riga come eliminata o archiviata. La modifica viene quindi inviata al client come un aggiornamento.
Eseguire l'overload della colonna del contesto nel rilevamento delle modifiche di SQL Server (SYS_CHANGE_CONTEXT) con valori aggiuntivi che possono essere utilizzati per filtrare i dati. Questa opzione è probabilmente la migliore in termini di prestazioni, ma potrebbe essere anche la più complessa in quanto è necessario analizzare questa colonna.
Esempio
Il codice di esempio contenuto in questo argomento illustra come filtrare i dati per le tabelle Customer, OrderHeader e OrderDetail dal database di esempio Sync Framework. La tabella Customer è filtrata in modo che vengano scaricate solo le righe con un valore Brenda Diaz per la colonna SalesPerson. Il filtro viene quindi esteso alle altre due tabelle. Nell'esempio viene mostrato come filtrare i dati utilizzando SqlSyncAdapterBuilder e creando manualmente i comandi di sincronizzazione. Per una panoramica sui comandi di sincronizzazione, vedere Procedura: specificare la sincronizzazione snapshot, di download, di caricamento e bidirezionale.
Utilizzo di SqlSyncAdapterBuilder
In questa sezione viene descritta l'API utilizzata nei filtri per i comandi creati con SqlSyncAdapterBuilder. La sezione fornisce esempi di codice che segnalano i componenti chiave dell'API e un codice di esempio completo.
Componenti chiave dell'API
Nell'esempio di codice seguente viene creato un parametro di filtro utilizzato nella clausola di filtro per tutte le tre tabelle.
SqlParameter filterParameter = new SqlParameter("@SalesPerson", SqlDbType.NVarChar);
Dim filterParameter As New SqlParameter("@SalesPerson", SqlDbType.NVarChar)
Nell'esempio di codice seguente viene specificato quali colonne scaricare per la tabella Customer.
string[] customerDataColumns = new string[3];
customerDataColumns[0] = "CustomerId";
customerDataColumns[1] = "CustomerName";
customerDataColumns[2] = "CustomerType";
customerBuilder.DataColumns.AddRange(customerDataColumns);
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns);
Dim customerDataColumns(2) As String
customerDataColumns(0) = "CustomerId"
customerDataColumns(1) = "CustomerName"
customerDataColumns(2) = "CustomerType"
customerBuilder.DataColumns.AddRange(customerDataColumns)
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns)
Nell'esempio di codice seguente viene specificato quali righe scaricare per la tabella Customer. È possibile impostare come hardcoded un valore per SalesPerson. Tuttavia, in genere viene utilizzato un parametro con un valore modificabile, come mostrato nell'esempio. Viene utilizzato il parametro ricavato dal primo esempio di codice.
string customerFilterClause = "SalesPerson=@SalesPerson";
customerBuilder.FilterClause = customerFilterClause;
customerBuilder.FilterParameters.Add(filterParameter);
customerBuilder.TombstoneFilterClause = customerFilterClause;
customerBuilder.TombstoneFilterParameters.Add(filterParameter);
Dim customerFilterClause As String = "SalesPerson=@SalesPerson"
With customerBuilder
.FilterClause = customerFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = customerFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
Nell'esempio di codice riportato di seguito si effettua un'estensione del filtro dalla tabella Customer alla tabella OrderHeader. In questo caso, nella clausola di filtro si utilizza un'istruzione SELECT. Per i comandi manuali, si utilizza una clausola JOIN, dato che fornisce maggiore controllo su come specificare i comandi.
string orderHeaderFilterClause =
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " +
"WHERE SalesPerson=@SalesPerson)";
orderHeaderBuilder.FilterClause = orderHeaderFilterClause;
orderHeaderBuilder.FilterParameters.Add(filterParameter);
orderHeaderBuilder.TombstoneFilterClause = orderHeaderFilterClause;
orderHeaderBuilder.TombstoneFilterParameters.Add(filterParameter);
Dim orderHeaderFilterClause As String = _
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " _
& "WHERE SalesPerson=@SalesPerson)"
With orderHeaderBuilder
.FilterClause = orderHeaderFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = orderHeaderFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
Nell'esempio di codice seguente è specificato un valore per il parametro @SalesPerson in una classe che deriva da SyncAgent. In un'applicazione, il valore potrebbe provenire da un ID di accesso o da un altro input dell'utente.
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))
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. Durante l'esecuzione dell'esempio, prestare attenzione alle informazioni restituite da SyncStatistics: un subset di righe viene scaricato sia nella sincronizzazione iniziale che in quelle successive.
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");
//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 two SyncGroups so that changes to OrderHeader
//and OrderDetail are made in one transaction. Depending on
//application requirements, you might include Customer
//in the same group.
SyncGroup customerSyncGroup = new SyncGroup("Customer");
SyncGroup orderSyncGroup = new SyncGroup("Order");
//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderDetailSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderDetailSyncTable);
//Specify a value for the @SalesPerson parameter that is added
//in the server synchronization provider. This value would
//typically be provided by a user in the application, but we
//have hardcoded it here for convenience.
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
}
}
//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 filter parameter that will be used in the filter clause for
//all three tables.
SqlParameter filterParameter = new SqlParameter("@SalesPerson", SqlDbType.NVarChar);
//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
// changes are made.
// * Specify download-only synchronization.
// * Specify if you want only certain columns at the client.
// * Specify filter clauses for the base tables and tombstone
// tables.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name that is 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";
//Specify the columns that you want at the client. If you
//want all columns, this code is not required. In this
//case, we filter out SalesPerson.
string[] customerDataColumns = new string[3];
customerDataColumns[0] = "CustomerId";
customerDataColumns[1] = "CustomerName";
customerDataColumns[2] = "CustomerType";
customerBuilder.DataColumns.AddRange(customerDataColumns);
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns);
//Specify a filter clause, which is an SQL WHERE clause
//without the WHERE keyword. Use the parameter that is
//created above. The value for the parameter is specified
//in the SyncAgent Configuration object.
string customerFilterClause = "SalesPerson=@SalesPerson";
customerBuilder.FilterClause = customerFilterClause;
customerBuilder.FilterParameters.Add(filterParameter);
customerBuilder.TombstoneFilterClause = customerFilterClause;
customerBuilder.TombstoneFilterParameters.Add(filterParameter);
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";
//Filter properties: extend the filter to the OrderHeader table.
string orderHeaderFilterClause =
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " +
"WHERE SalesPerson=@SalesPerson)";
orderHeaderBuilder.FilterClause = orderHeaderFilterClause;
orderHeaderBuilder.FilterParameters.Add(filterParameter);
orderHeaderBuilder.TombstoneFilterClause = orderHeaderFilterClause;
orderHeaderBuilder.TombstoneFilterParameters.Add(filterParameter);
SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
orderHeaderSyncAdapter.TableName = "OrderHeader";
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//OrderDetail table.
SqlSyncAdapterBuilder orderDetailBuilder = new SqlSyncAdapterBuilder(serverConn);
orderDetailBuilder.TableName = "Sales.OrderDetail";
orderDetailBuilder.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone";
orderDetailBuilder.SyncDirection = SyncDirection.DownloadOnly;
orderDetailBuilder.CreationTrackingColumn = "InsertTimestamp";
orderDetailBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderDetailBuilder.DeletionTrackingColumn = "DeleteTimestamp";
//Filter properties: extend the filter to the OrderDetail table.
string orderDetailFilterClause =
"OrderId IN (SELECT OrderId FROM Sales.OrderHeader " +
"WHERE CustomerId IN " +
"(SELECT CustomerId FROM Sales.Customer " +
"WHERE SalesPerson=@SalesPerson))";
orderDetailBuilder.FilterClause = orderDetailFilterClause;
orderDetailBuilder.FilterParameters.Add(filterParameter);
orderDetailBuilder.TombstoneFilterClause = orderDetailFilterClause;
orderDetailBuilder.TombstoneFilterParameters.Add(filterParameter);
SyncAdapter orderDetailSyncAdapter = orderDetailBuilder.ToSyncAdapter();
orderDetailSyncAdapter.TableName = "OrderDetail";
this.SyncAdapters.Add(orderDetailSyncAdapter);
}
}
//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 you could use this class to handle client
//provider events and other client-side processing.
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 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")
'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 two SyncGroups so that changes to OrderHeader
'and OrderDetail are made in one transaction. Depending on
'application requirements, you might include Customer
'in the same group.
Dim customerSyncGroup As New SyncGroup("Customer")
Dim orderSyncGroup As New SyncGroup("Order")
'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 = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderHeaderSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
Dim orderDetailSyncTable As New SyncTable("OrderDetail")
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderDetailSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderDetailSyncTable)
'Specify a value for the @SalesPerson parameter that is added
'in the server synchronization provider. This value would
'typically be provided by a user in the application, but we
'have hardcoded it here for convenience.
Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))
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 filter parameter that will be used in the filter clause for
'all three tables.
Dim filterParameter As New SqlParameter("@SalesPerson", SqlDbType.NVarChar)
'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
' changes are made.
' * Specify download-only synchronization.
' * Specify if you want only certain columns at the client.
' * Specify filter clauses for the base tables and tombstone
' tables.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name that is specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
'Customer table.
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
With customerBuilder
.TableName = "Sales.Customer"
.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.DownloadOnly
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
End With
'Specify the columns that you want at the client. If you
'want all columns, this code is not required. In this
'case, we filter out SalesPerson.
Dim customerDataColumns(2) As String
customerDataColumns(0) = "CustomerId"
customerDataColumns(1) = "CustomerName"
customerDataColumns(2) = "CustomerType"
customerBuilder.DataColumns.AddRange(customerDataColumns)
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns)
'Specify a filter clause, which is an SQL WHERE clause
'without the WHERE keyword. Use the parameter that is
'created above. The value for the parameter is specified
'in the SyncAgent Configuration object.
Dim customerFilterClause As String = "SalesPerson=@SalesPerson"
With customerBuilder
.FilterClause = customerFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = customerFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
'OrderHeader table.
Dim orderHeaderBuilder As New SqlSyncAdapterBuilder(serverConn)
With orderHeaderBuilder
.TableName = "Sales.OrderHeader"
.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.DownloadOnly
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
End With
'Filter properties: extend the filter to the OrderHeader table.
Dim orderHeaderFilterClause As String = _
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " _
& "WHERE SalesPerson=@SalesPerson)"
With orderHeaderBuilder
.FilterClause = orderHeaderFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = orderHeaderFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
Dim orderHeaderSyncAdapter As SyncAdapter = orderHeaderBuilder.ToSyncAdapter()
orderHeaderSyncAdapter.TableName = "OrderHeader"
Me.SyncAdapters.Add(orderHeaderSyncAdapter)
'OrderDetail table.
Dim orderDetailBuilder As New SqlSyncAdapterBuilder(serverConn)
With orderDetailBuilder
.TableName = "Sales.OrderDetail"
.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.DownloadOnly
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
'Filter properties: extend the filter to the OrderDetail table.
Dim orderDetailFilterClause As String = _
"OrderId IN (SELECT OrderId FROM Sales.OrderHeader " _
& "WHERE CustomerId IN " _
& "(SELECT CustomerId FROM Sales.Customer " _
& "WHERE SalesPerson=@SalesPerson))"
.FilterClause = orderDetailFilterClause
.FilterParameters.Add(filterParameter)
.TombstoneFilterClause = orderDetailFilterClause
.TombstoneFilterParameters.Add(filterParameter)
End With
Dim orderDetailSyncAdapter As SyncAdapter = orderDetailBuilder.ToSyncAdapter()
orderDetailSyncAdapter.TableName = "OrderDetail"
Me.SyncAdapters.Add(orderDetailSyncAdapter)
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 you could use this class to handle client
'provider events and other client-side processing.
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 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
Utilizzo dei comandi manuali
In questa sezione viene descritta l'API utilizzata per il filtro in caso di creazione manuale dei comandi. La sezione fornisce esempi di codice che segnalano i componenti chiave dell'API e un esempio di codice completo.
Componenti chiave dell'API
Nell'esempio di codice seguente viene specificato quali colonne e righe inserite scaricare per la tabella Customer. È possibile impostare come hardcoded un valore per SalesPerson. Tuttavia, in genere viene utilizzato un parametro con un valore modificabile, come mostrato nell'esempio. Nell'esempio viene passato il parametro del filtro insieme agli altri parametri richiesti per scaricare gli inserimenti incrementali.
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
"SELECT CustomerId, CustomerName, CustomerType " +
"FROM Sales.Customer " +
"WHERE SalesPerson = @SalesPerson " +
"AND (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertTimestamp <= @sync_new_received_anchor " +
"AND InsertId <> @sync_client_id)";
customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
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;
Dim customerIncrInserts As New SqlCommand()
With customerIncrInserts
.CommandText = _
"SELECT CustomerId, CustomerName, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertTimestamp <= @sync_new_received_anchor " _
& "AND InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.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
Nell'esempio di codice riportato di seguito si effettua un'estensione del filtro dalla tabella Customer alla tabella OrderHeader. In questo caso, viene utilizzata una clausola JOIN per definire la relazione tra le due tabelle.
SqlCommand orderHeaderIncrInserts = new SqlCommand();
orderHeaderIncrInserts.CommandText =
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
"FROM Sales.OrderHeader oh " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE c.SalesPerson = @SalesPerson " +
"AND (oh.InsertTimestamp > @sync_last_received_anchor " +
"AND oh.InsertTimestamp <= @sync_new_received_anchor " +
"AND oh.InsertId <> @sync_client_id)";
orderHeaderIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrInserts.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts;
Dim orderHeaderIncrInserts As New SqlCommand()
With orderHeaderIncrInserts
.CommandText = _
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
& "FROM Sales.OrderHeader oh " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE c.SalesPerson = @SalesPerson " _
& "AND (oh.InsertTimestamp > @sync_last_received_anchor " _
& "AND oh.InsertTimestamp <= @sync_new_received_anchor " _
& "AND oh.InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts
Nell'esempio di codice seguente è specificato un valore per il parametro @SalesPerson in una classe che deriva da SyncAgent. In un'applicazione, il valore potrebbe provenire da un ID di accesso o da un altro input dell'utente.
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
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. Durante l'esecuzione dell'esempio, prestare attenzione alle informazioni restituite da SyncStatistics: un subset di righe viene scaricato sia nella sincronizzazione iniziale che in quelle successive.
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 recreate 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");
//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 two SyncGroups, so that changes to OrderHeader
//and OrderDetail are made in one transaction. Depending on
//application requirements, you might include Customer
//in the same group.
SyncGroup customerSyncGroup = new SyncGroup("Customer");
SyncGroup orderSyncGroup = new SyncGroup("Order");
//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderDetailSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderDetailSyncTable);
//Specify a value for the @SalesPerson parameter that is added
//in the server synchronization provider. This value would
//typically be provided by a user in the application, but we
//have hardcoded it here for convenience.
this.Configuration.SyncParameters.Add(
new SyncParameter("@SalesPerson", "Brenda Diaz"));
}
}
//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 each 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.
//* Specify if you want only certain columns at the client by
// using the SELECT statement in the command.
//* Filter rows by using the WHERE clause in the command.
// In this case, we filter out SalesPerson.
//
//Customer table
//
//Create the SyncAdapter
SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");
//Select inserts from the server
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
"SELECT CustomerId, CustomerName, CustomerType " +
"FROM Sales.Customer " +
"WHERE SalesPerson = @SalesPerson " +
"AND (InsertTimestamp > @sync_last_received_anchor " +
"AND InsertTimestamp <= @sync_new_received_anchor " +
"AND InsertId <> @sync_client_id)";
customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
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;
//Select updates from the server
SqlCommand customerIncrUpdates = new SqlCommand();
customerIncrUpdates.CommandText =
"SELECT CustomerId, CustomerName, CustomerType " +
"FROM Sales.Customer " +
"WHERE SalesPerson = @SalesPerson " +
"AND (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("@SalesPerson", SqlDbType.NVarChar);
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;
//Select deletes from the server
SqlCommand customerIncrDeletes = new SqlCommand();
customerIncrDeletes.CommandText =
"SELECT CustomerId, CustomerName, CustomerType " +
"FROM Sales.Customer_Tombstone " +
"WHERE SalesPerson = @SalesPerson " +
"AND (@sync_initialized = 1 " +
"AND DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor " +
"AND DeleteId <> @sync_client_id)";
customerIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
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;
//Add the SyncAdapter to the server synchronization provider
this.SyncAdapters.Add(customerSyncAdapter);
//
//OrderHeader table
//
//Create the SyncAdapter
SyncAdapter orderHeaderSyncAdapter = new SyncAdapter("OrderHeader");
//Select inserts from the server
SqlCommand orderHeaderIncrInserts = new SqlCommand();
orderHeaderIncrInserts.CommandText =
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
"FROM Sales.OrderHeader oh " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE c.SalesPerson = @SalesPerson " +
"AND (oh.InsertTimestamp > @sync_last_received_anchor " +
"AND oh.InsertTimestamp <= @sync_new_received_anchor " +
"AND oh.InsertId <> @sync_client_id)";
orderHeaderIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrInserts.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts;
//Select updates from the server
SqlCommand orderHeaderIncrUpdates = new SqlCommand();
orderHeaderIncrUpdates.CommandText =
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " +
"FROM Sales.OrderHeader oh " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE c.SalesPerson = @SalesPerson " +
"AND (oh.UpdateTimestamp > @sync_last_received_anchor " +
"AND oh.UpdateTimestamp <= @sync_new_received_anchor " +
"AND oh.UpdateId <> @sync_client_id " +
"AND NOT (oh.InsertTimestamp > @sync_last_received_anchor " +
"AND oh.InsertId <> @sync_client_id))";
orderHeaderIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrUpdates.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalUpdatesCommand = orderHeaderIncrUpdates;
//Select deletes from the server
SqlCommand orderHeaderIncrDeletes = new SqlCommand();
orderHeaderIncrDeletes.CommandText =
"SELECT oht.OrderId, oht.CustomerId, oht.OrderDate, oht.OrderStatus " +
"FROM Sales.OrderHeader_Tombstone oht " +
"JOIN Sales.Customer c ON oht.CustomerId = c.CustomerId " +
"WHERE c.SalesPerson = @SalesPerson " +
"AND (@sync_initialized = 1 " +
"AND oht.DeleteTimestamp > @sync_last_received_anchor " +
"AND oht.DeleteTimestamp <= @sync_new_received_anchor " +
"AND oht.DeleteId <> @sync_client_id)";
orderHeaderIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderHeaderIncrDeletes.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalDeletesCommand = orderHeaderIncrDeletes;
//Add the SyncAdapter to the server synchronization provider
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//
//OrderDetail table
//
//Create the SyncAdapter
SyncAdapter orderDetailSyncAdapter = new SyncAdapter("OrderDetail");
//Select inserts from the server
SqlCommand orderDetailIncrInserts = new SqlCommand();
orderDetailIncrInserts.CommandText =
"SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " +
"FROM Sales.OrderDetail od " +
"JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE SalesPerson = @SalesPerson " +
"AND (od.InsertTimestamp > @sync_last_received_anchor " +
"AND od.InsertTimestamp <= @sync_new_received_anchor " +
"AND od.InsertId <> @sync_client_id)";
orderDetailIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderDetailIncrInserts.Connection = serverConn;
orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts;
//Select updates from the server
SqlCommand orderDetailIncrUpdates = new SqlCommand();
orderDetailIncrUpdates.CommandText =
"SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " +
"FROM Sales.OrderDetail od " +
"JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE SalesPerson = @SalesPerson " +
"AND (od.UpdateTimestamp > @sync_last_received_anchor " +
"AND od.UpdateTimestamp <= @sync_new_received_anchor " +
"AND od.UpdateId <> @sync_client_id " +
"AND NOT (od.InsertTimestamp > @sync_last_received_anchor " +
"AND od.InsertId <> @sync_client_id))";
orderDetailIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderDetailIncrUpdates.Connection = serverConn;
orderDetailSyncAdapter.SelectIncrementalUpdatesCommand = orderDetailIncrUpdates;
//Select deletes from the server
SqlCommand orderDetailIncrDeletes = new SqlCommand();
orderDetailIncrDeletes.CommandText =
"SELECT odt.OrderDetailId, odt.OrderId, odt.Product, odt.Quantity " +
"FROM Sales.OrderDetail_Tombstone odt " +
"JOIN Sales.OrderHeader oh ON odt.OrderId = oh.OrderId " +
"JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " +
"WHERE SalesPerson = @SalesPerson " +
"AND (@sync_initialized = 1 " +
"AND odt.DeleteTimestamp > @sync_last_received_anchor " +
"AND odt.DeleteTimestamp <= @sync_new_received_anchor " +
"AND odt.DeleteId <> @sync_client_id)";
orderDetailIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
orderDetailIncrDeletes.Connection = serverConn;
orderDetailSyncAdapter.SelectIncrementalDeletesCommand = orderDetailIncrDeletes;
//Add the SyncAdapter to the server synchronization provider
this.SyncAdapters.Add(orderDetailSyncAdapter);
}
}
//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 you could use this class to handle client
//provider events and other client-side processing.
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 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")
'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 two SyncGroups so that changes to OrderHeader
'and OrderDetail are made in one transaction. Depending on
'application requirements, you might include Customer
'in the same group.
Dim customerSyncGroup As New SyncGroup("Customer")
Dim orderSyncGroup As New SyncGroup("Order")
'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 = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderHeaderSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
Dim orderDetailSyncTable As New SyncTable("OrderDetail")
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly
orderDetailSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderDetailSyncTable)
'Specify a value for the @SalesPerson parameter that is added
'in the server synchronization provider. This value would
'typically be provided by a user in the application, but we
'have hardcoded it here for convenience.
Me.Configuration.SyncParameters.Add(New SyncParameter("@SalesPerson", "Brenda Diaz"))
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 each 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.
'* Specify if you want only certain columns at the client by
' using the SELECT statement in the command.
'* Filter rows by using the WHERE clause in the command.
' In this case, we filter out SalesPerson.
'
'Customer table.
'
'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, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (InsertTimestamp > @sync_last_received_anchor " _
& "AND InsertTimestamp <= @sync_new_received_anchor " _
& "AND InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.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
'Select updates from the server.
Dim customerIncrUpdates As New SqlCommand()
With customerIncrUpdates
.CommandText = _
"SELECT CustomerId, CustomerName, CustomerType " _
& "FROM Sales.Customer " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (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("@SalesPerson", SqlDbType.NVarChar)
.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
'Select deletes from the server.
Dim customerIncrDeletes As New SqlCommand()
With customerIncrDeletes
.CommandText = _
"SELECT CustomerId, CustomerName, CustomerType " _
& "FROM Sales.Customer_Tombstone " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (@sync_initialized = 1 " _
& "AND DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor " _
& "AND DeleteId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.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
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(customerSyncAdapter)
'
'OrderHeader table.
'
'Create the SyncAdapter.
Dim orderHeaderSyncAdapter As New SyncAdapter("OrderHeader")
'Select inserts from the server.
Dim orderHeaderIncrInserts As New SqlCommand()
With orderHeaderIncrInserts
.CommandText = _
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
& "FROM Sales.OrderHeader oh " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE c.SalesPerson = @SalesPerson " _
& "AND (oh.InsertTimestamp > @sync_last_received_anchor " _
& "AND oh.InsertTimestamp <= @sync_new_received_anchor " _
& "AND oh.InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts
'Select updates from the server.
Dim orderHeaderIncrUpdates As New SqlCommand()
With orderHeaderIncrUpdates
.CommandText = _
"SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " _
& "FROM Sales.OrderHeader oh " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE c.SalesPerson = @SalesPerson " _
& "AND (oh.UpdateTimestamp > @sync_last_received_anchor " _
& "AND oh.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND oh.UpdateId <> @sync_client_id " _
& "AND NOT (oh.InsertTimestamp > @sync_last_received_anchor " _
& "AND oh.InsertId <> @sync_client_id))"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderHeaderSyncAdapter.SelectIncrementalUpdatesCommand = orderHeaderIncrUpdates
'Select deletes from the server.
Dim orderHeaderIncrDeletes As New SqlCommand()
With orderHeaderIncrDeletes
.CommandText = _
"SELECT oht.OrderId, oht.CustomerId, oht.OrderDate, oht.OrderStatus " _
& "FROM Sales.OrderHeader_Tombstone oht " _
& "JOIN Sales.Customer c ON oht.CustomerId = c.CustomerId " _
& "WHERE c.SalesPerson = @SalesPerson " _
& "AND (@sync_initialized = 1 " _
& "AND oht.DeleteTimestamp > @sync_last_received_anchor " _
& "AND oht.DeleteTimestamp <= @sync_new_received_anchor " _
& "AND oht.DeleteId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.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
orderHeaderSyncAdapter.SelectIncrementalDeletesCommand = orderHeaderIncrDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(orderHeaderSyncAdapter)
'
'OrderDetail table.
'
'Create the SyncAdapter.
Dim orderDetailSyncAdapter As New SyncAdapter("OrderDetail")
'Select inserts from the server.
Dim orderDetailIncrInserts As New SqlCommand()
With orderDetailIncrInserts
.CommandText = _
"SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " _
& "FROM Sales.OrderDetail od " _
& "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (od.InsertTimestamp > @sync_last_received_anchor " _
& "AND od.InsertTimestamp <= @sync_new_received_anchor " _
& "AND od.InsertId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts
'Select updates from the server.
Dim orderDetailIncrUpdates As New SqlCommand()
With orderDetailIncrUpdates
.CommandText = _
"SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " _
& "FROM Sales.OrderDetail od " _
& "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (od.UpdateTimestamp > @sync_last_received_anchor " _
& "AND od.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND od.UpdateId <> @sync_client_id " _
& "AND NOT (od.InsertTimestamp > @sync_last_received_anchor " _
& "AND od.InsertId <> @sync_client_id))"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp)
.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier)
.Connection = serverConn
End With
orderDetailSyncAdapter.SelectIncrementalUpdatesCommand = orderDetailIncrUpdates
'Select deletes from the server.
Dim orderDetailIncrDeletes As New SqlCommand()
With orderDetailIncrDeletes
.CommandText = _
"SELECT odt.OrderDetailId, odt.OrderId, odt.Product, odt.Quantity " _
& "FROM Sales.OrderDetail_Tombstone odt " _
& "JOIN Sales.OrderHeader oh ON odt.OrderId = oh.OrderId " _
& "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " _
& "WHERE SalesPerson = @SalesPerson " _
& "AND (@sync_initialized = 1 " _
& "AND odt.DeleteTimestamp > @sync_last_received_anchor " _
& "AND odt.DeleteTimestamp <= @sync_new_received_anchor " _
& "AND odt.DeleteId <> @sync_client_id)"
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.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
orderDetailSyncAdapter.SelectIncrementalDeletesCommand = orderDetailIncrDeletes
'Add the SyncAdapter to the server synchronization provider.
Me.SyncAdapters.Add(orderDetailSyncAdapter)
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 you could use this class to handle client
'provider events and other client-side processing.
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 ******")
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
Vedere anche
Concetti
Programmazione di attività comuni di sincronizzazione client e server
Strumenti che facilitano lo sviluppo di applicazioni