Procédure : créer des vues de données sur un client
Cette rubrique indique comment utiliser Sync Framework pour combiner deux tables serveur ou plus dans une table unique de la base de données client. Les exemples de cette rubrique sont axés sur les types et les événements Sync Framework suivants :
Pour plus d'informations sur le mode d'exécution d'un exemple de code, consultez « Exemples d'application dans les rubriques de procédures » dans la rubrique Programmation des tâches courantes de synchronisation client et serveur.
Exemple
Les exemples de code suivants indiquent comment synchroniser les tables Customer et CustomerContact de l'exemple de base de données Sync Framework. Durant la synchronisation initiale, les données des deux tables sont sélectionnées à l'aide de la commande d'insertions incrémentielles, téléchargées sur le client, puis insérées dans la table CustomerInfo. Au cours de la synchronisation suivante, les modifications applicables sont téléchargées sur le client.
Éléments clés de l'API
Cette section contient des exemples de code qui désignent les sections clés de l'API à utiliser lorsque vous combinez des tables. Les exemples de code suivants spécifient les objets SyncTable et SyncAdapter qui utilisent tous deux le nom CustomerInfo. La spécification d'un nom à ces deux emplacements permet à l'objet SyncAgent et aux fournisseurs de convertir les sélections des tables Customer et CustomerContact en insertions pour la table CustomerInfo.
SyncTable customerInfoSyncTable = new SyncTable("CustomerInfo");
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerInfoSyncTable);
SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo");
Dim customerInfoSyncTable As New SyncTable("CustomerInfo")
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerInfoSyncTable)
Dim customerInfoSyncAdapter As New SyncAdapter("CustomerInfo")
L'exemple de code suivant inclut la requête pour la propriété SelectIncrementalInsertsCommand. La requête utilise une jointure pour garantir que seuls les clients disposant d'informations de contact sont téléchargés vers le client au cours de la synchronisation initiale. Pour les synchronisations suivantes, les insertions effectuées dans la table CustomerContact sont téléchargées ; les insertions effectuées dans la table Customer sont téléchargées uniquement si une ligne du client est également insérée dans la table CustomerContact. Notez que la liste de sélection n'inclut pas toutes les colonnes. Comme pour toutes les requêtes qui sont utilisées dans la synchronisation, la logique de chaque requête dépend de la configuration requise de l'application. Par exemple, vous pouvez télécharger tous les clients, même s'ils ne disposent pas d'informations de contact.
SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand();
customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text;
customerInfoIncrementalInsertsCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.InsertTimestamp > @sync_last_received_anchor " +
"AND c.InsertTimestamp <= @sync_new_received_anchor) OR " +
"(cc.InsertTimestamp > @sync_last_received_anchor " +
"AND cc.InsertTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand;
Dim customerInfoIncrementalInsertsCommand As New SqlCommand()
With customerInfoIncrementalInsertsCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " _
& "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " _
& "(cc.InsertTimestamp > @sync_last_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand
L'exemple de code suivant inclut la requête pour la propriété SelectIncrementalDeletesCommand. La requête utilise deux instructions SELECT et un opérateur UNION pour sélectionner les suppressions des tables Customer et CustomerContact. La première instruction SELECT est jointe à la table CustomerContact pour extraire la colonne PhoneType. Cette colonne fait partie de la clé composite de la table CustomerInfo. La logique est la suivante :
Si un client a été supprimé, toutes les lignes de ce client sont supprimées sur le client.
Si des informations de contact ont été supprimées, seule cette ligne est supprimée sur le client.
Si une ligne d'un client ainsi que les informations de contact de ce client ont été supprimées, une suppression supplémentaire au minimum est téléchargée. Ceci ne provoquera aucune erreur lorsque les modifications seront appliquées à la base de données client.
SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand();
customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text;
customerInfoIncrementalDeletesCommand.CommandText =
"SELECT c.CustomerId, cc.PhoneType " +
"FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor)) " +
"UNION " +
"SELECT CustomerId, PhoneType " +
"FROM Sales.CustomerContact_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand;
Dim customerInfoIncrementalDeletesCommand As New SqlCommand()
With customerInfoIncrementalDeletesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, cc.PhoneType " _
& "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor)) " _
& "UNION " _
& "SELECT CustomerId, PhoneType " _
& "FROM Sales.CustomerContact_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_initialized", SqlDbType.Bit)
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand
L'exemple de code suivant crée une clé primaire composite pour la table CustomerInfo. Cette clé correspond à la clé de la table CustomerContact. Sync Framework peut déduire le schéma des tables sur le serveur, mais la clé doit être spécifiée dans ce cas. Vous pouvez aussi créer le schéma manuellement, comme le décrit la rubrique Procédure : initialiser la base de données client et travailler avec un schéma de table.
string[] customerInfoPrimaryKey = new string[2];
customerInfoPrimaryKey[0] = "CustomerId";
customerInfoPrimaryKey[1] = "PhoneType";
e.Schema.Tables["CustomerInfo"].PrimaryKey = customerInfoPrimaryKey;
Dim customerInfoPrimaryKey(1) As String
customerInfoPrimaryKey(0) = "CustomerId"
customerInfoPrimaryKey(1) = "PhoneType"
e.Schema.Tables("CustomerInfo").PrimaryKey = customerInfoPrimaryKey
Exemple de code complet
L'exemple de code complet ci-dessous inclut les exemples de code décrits précédemment, ainsi que du code supplémentaire pour effectuer la synchronisation. L'exemple requiert la classe Utility qui est disponible dans Classe d'utilitaire pour les rubriques de procédures sur le fournisseur de bases de données. Notez que l'insertion dans la table Customer dans la classe Utility n'est pas téléchargée lors de la synchronisation suivante, car il n'existe aucune ligne correspondante dans la table CustomerContact.
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Request a password for the client database, and delete
//and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetPassword_SqlCeClientSync();
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server.
Utility.MakeDataChangesOnServer("Customer");
Utility.MakeDataChangesOnServer("CustomerContact");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
Utility.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Add the Customer table: specify a synchronization direction of
//DownloadOnly, and that an existing table should be dropped.
SyncTable customerInfoSyncTable = new SyncTable("CustomerInfo");
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerInfoSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the CustomerInfo table. The CustomerInfo
//table on the client is a combination of the Customer and CustomerContact
//tables on the server. This table is download-only, as specified in
//SampleSyncAgent.
SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo");
//Specify synchronization commands. The CustomerInfo table
//is download-only, so we do not define commands to apply changes to
//the server. Each command joins the base tables or tombstone tables
//to select the appropriate incremental changes. For this application,
//the logic is as follows:
//* Select all inserts for customers that have contact information.
// This results in more than one row for a customer if that customer
// has more than one phone number.
//* Select all updates for customer and contact information that has
// already been downloaded.
//* Select all deletes for customer and contact information that has
// already been downloaded. If a customer has been deleted, delete
// all of the rows for that customer. If a phone number has been
// deleted, delete only that row.
//Select inserts.
SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand();
customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text;
customerInfoIncrementalInsertsCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.InsertTimestamp > @sync_last_received_anchor " +
"AND c.InsertTimestamp <= @sync_new_received_anchor) OR " +
"(cc.InsertTimestamp > @sync_last_received_anchor " +
"AND cc.InsertTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalInsertsCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand;
//Select updates.
SqlCommand customerInfoIncrementalUpdatesCommand = new SqlCommand();
customerInfoIncrementalUpdatesCommand.CommandType = CommandType.Text;
customerInfoIncrementalUpdatesCommand.CommandText =
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " +
"FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " +
"AND c.UpdateTimestamp <= @sync_new_received_anchor " +
"AND c.InsertTimestamp <= @sync_last_received_anchor) " +
"OR (cc.UpdateTimestamp > @sync_last_received_anchor " +
"AND cc.UpdateTimestamp <= @sync_new_received_anchor " +
"AND cc.InsertTimestamp <= @sync_last_received_anchor))";
customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalUpdatesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand;
//Select deletes.
SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand();
customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text;
customerInfoIncrementalDeletesCommand.CommandText =
"SELECT c.CustomerId, cc.PhoneType " +
"FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " +
"c.CustomerId = cc.CustomerId " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor)) " +
"UNION " +
"SELECT CustomerId, PhoneType " +
"FROM Sales.CustomerContact_Tombstone " +
"WHERE (@sync_initialized = 1 " +
"AND (DeleteTimestamp > @sync_last_received_anchor " +
"AND DeleteTimestamp <= @sync_new_received_anchor))";
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp);
customerInfoIncrementalDeletesCommand.Connection = serverConn;
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand;
//Add the SyncAdapter to the provider.
this.SyncAdapters.Add(customerInfoSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but here we use this class to handle client
//provider events.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
//Handle the two schema-related events.
this.CreatingSchema += new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
this.SchemaCreated += new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
}
private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
Console.Write("Creating schema for " + e.Table.TableName + " | ");
//Create a compostite primary key for the CustomerInfo table.
string[] customerInfoPrimaryKey = new string[2];
customerInfoPrimaryKey[0] = "CustomerId";
customerInfoPrimaryKey[1] = "PhoneType";
e.Schema.Tables["CustomerInfo"].PrimaryKey = customerInfoPrimaryKey;
}
private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
{
Console.WriteLine("Schema created for " + e.Table.TableName);
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Request a password for the client database, and delete
'and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetPassword_SqlCeClientSync()
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, True)
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server.
Utility.MakeDataChangesOnServer("Customer")
Utility.MakeDataChangesOnServer("CustomerContact")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
Utility.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Add the Customer table: specify a synchronization direction of
'DownloadOnly, and that an existing table should be dropped.
Dim customerInfoSyncTable As New SyncTable("CustomerInfo")
customerInfoSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerInfoSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerInfoSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the CustomerInfo table. The CustomerInfo
'table on the client is a combination of the Customer and CustomerContact
'tables on the server. This table is download-only, as specified in
'SampleSyncAgent.
Dim customerInfoSyncAdapter As New SyncAdapter("CustomerInfo")
'Specify synchronization commands. The CustomerInfo table
'is download-only, so we do not define commands to apply changes to
'the server. Each command joins the base tables or tombstone tables
'to select the appropriate incremental changes. For this application,
'the logic is as follows:
'* Select all inserts for customers that have contact information.
' This results in more than one row for a customer if that customer
' has more than one phone number.
'* Select all updates for customer and contact information that has
' already been downloaded.
'* Select all deletes for customer and contact information that has
' already been downloaded. If a customer has been deleted, delete
' all of the rows for that customer. If a phone number has been
' deleted, delete only that row.
'Select inserts.
Dim customerInfoIncrementalInsertsCommand As New SqlCommand()
With customerInfoIncrementalInsertsCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " _
& "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " _
& "(cc.InsertTimestamp > @sync_last_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand
'Select updates.
Dim customerInfoIncrementalUpdatesCommand As New SqlCommand()
With customerInfoIncrementalUpdatesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " _
& "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " _
& "AND c.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND c.InsertTimestamp <= @sync_last_received_anchor) " _
& "OR (cc.UpdateTimestamp > @sync_last_received_anchor " _
& "AND cc.UpdateTimestamp <= @sync_new_received_anchor " _
& "AND cc.InsertTimestamp <= @sync_last_received_anchor))"
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand
'Select deletes.
Dim customerInfoIncrementalDeletesCommand As New SqlCommand()
With customerInfoIncrementalDeletesCommand
.CommandType = CommandType.Text
.CommandText = _
"SELECT c.CustomerId, cc.PhoneType " _
& "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " _
& "c.CustomerId = cc.CustomerId " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor)) " _
& "UNION " _
& "SELECT CustomerId, PhoneType " _
& "FROM Sales.CustomerContact_Tombstone " _
& "WHERE (@sync_initialized = 1 " _
& "AND (DeleteTimestamp > @sync_last_received_anchor " _
& "AND DeleteTimestamp <= @sync_new_received_anchor))"
.Parameters.Add("@sync_initialized", SqlDbType.Bit)
.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp)
.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp)
.Connection = serverConn
End With
customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand
'Add the SyncAdapter to the provider.
Me.SyncAdapters.Add(customerInfoSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but here we use this class to handle client
'provider events.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
'Handle the two schema-related events.
AddHandler Me.CreatingSchema, AddressOf SampleClientSyncProvider_CreatingSchema
AddHandler Me.SchemaCreated, AddressOf SampleClientSyncProvider_SchemaCreated
End Sub 'New
Private Sub SampleClientSyncProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
Console.Write("Creating schema for " + e.Table.TableName + " | ")
'Create a compostite primary key for the CustomerInfo table.
Dim customerInfoPrimaryKey(1) As String
customerInfoPrimaryKey(0) = "CustomerId"
customerInfoPrimaryKey(1) = "PhoneType"
e.Schema.Tables("CustomerInfo").PrimaryKey = customerInfoPrimaryKey
End Sub 'SampleClientSyncProvider_CreatingSchema
Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
Console.WriteLine("Schema created for " + e.Table.TableName)
End Sub 'SampleClientSyncProvider_SchemaCreated
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats
Voir aussi
Concepts
Programmation des tâches courantes de synchronisation client et serveur