Introducción: sincronización de cliente y servidor
En este tema se explica una aplicación de consola que descarga un conjunto de datos inicial y después un conjunto de cambios incrementales desde la misma tabla. La aplicación es sencilla, pero proporciona un código basado en las tareas de muchas secciones de la documentación de Sync Framework. Si ha leído Arquitectura y clases para la sincronización del cliente y el servidor, ya conocerá las clases principales que se usan en la aplicación.
Puede conocerlas sencillamente leyendo el código del ejemplo. No obstante, es más instructivo ejecutar la aplicación y verla en acción. Antes de ejecutar el código, asegúrese de instalar los siguientes componentes:
Sync Framework
La aplicación requiere referencias a Microsoft.Synchronization.Data.dll, Microsoft.Synchronization.Data.Server.dll y Microsoft.Synchronization.Data.SqlServerCe.dll.
SQL Server Compact Service Pack 1
La aplicación requiere una referencia System.Data.SqlServerCe.dll.
Una versión de SQL Server distinta de SQL Server Compact para que actúe como base de datos servidor.
En el código de ejemplo se utiliza
localhosten las cadenas de conexión. Para usar la instancia de SQL Server Express que se instala con Visual Studio, cambielocalhosta.\sqlexpress. Para usar un servidor remoto, cambielocalhostal nombre de servidor pertinente.Las bases de datos de ejemplo de Sync Framework. Ejecute los dos scripts que están disponibles en Scripts de configuración para los temas de procedimientos del proveedor de base de datos. Se recomienda revisar estos scripts para ver cómo se administra el seguimiento de cambios en la base de datos servidor.
La aplicación está compuesta por seis clases:
SampleSyncAgent. Esta clase se deriva de SyncAgent y contiene SyncTable.SampleServerSyncProvider. Esta clase se deriva de DbServerSyncProvider y contiene SyncAdapter.SampleClientSyncProvider. Esta clase se deriva de SqlCeClientSyncProvider. En este ejemplo, esta clase sólo contiene una cadena de conexión con la base de datos cliente.SampleStats. Esta clase usa las estadísticas que devuelveSyncAgent.Program. Esta clase configura la sincronización y llama a métodos de la claseUtility.Utility. Esta clase controla toda la funcionalidad que no está relacionada directamente con la sincronización, como conservar la información de las cadenas de conexión y efectuar cambios en la base de datos servidor. En otros temas se usa una claseUtilitycompleta. La clase completa está disponible en Clase de utilidad para los temas de procedimientos del proveedor de bases de datos.
Partes principales de la API
Antes de examinar el código de ejemplo completo, se recomienda revisar los ejemplos siguientes que ilustran varias secciones principales de la API utilizadas en esta aplicación.
Crear una SyncTable
El ejemplo de código siguiente crea un objeto SyncTable para la tabla Customer, especifica la dirección de sincronización y especifica cómo debe crearse la tabla en el cliente. En este caso, si la tabla ya existe en la base de datos cliente, se eliminará durante la primera sincronización.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
Utilizar SqlSyncAdapterBuilder
Cada uno de los ejemplos de código de esta sección crea un SyncAdapter para la tabla Customer. El adaptador de sincronización proporciona al proveedor de sincronización del servidor los comandos específicos necesarios para interactuar con la base de datos servidor. En esta aplicación, el adaptador de sincronización se crea por medio de SqlSyncAdapterBuilder. En el primer ejemplo se muestra cómo utilizar SqlSyncAdapterBuilder con un sistema de seguimiento de cambios personalizado. En el segundo ejemplo se muestra cómo utilizar SqlSyncAdapterBuilder con el sistema de seguimiento de cambios de SQL Server (disponible en SQL Server 2008). Para obtener más información acerca del seguimiento de cambios, vea Seguimiento de cambios en la base de datos servidor.
Para obtener información sobre cómo crear comandos manualmente en lugar de usar el generador, vea Descargar cambios de datos incrementales a un cliente.
Usar un sistema de seguimiento de cambios personalizado
Para utilizar un sistema de seguimiento de cambios personalizado, especifique la información siguiente para SqlSyncAdapterBuilder y SyncAdapter:
El nombre de la tabla que se va a sincronizar y la tabla de marcadores de exclusión. Una tabla de marcadores de exclusión facilita el seguimiento de las operaciones de eliminación en la base de datos servidor. Para obtener más información, vea Seguimiento de cambios en la base de datos servidor. Si las tablas se encuentran en un esquema distinto de dbo, se debe especificar el esquema.
La dirección de sincronización. Controla los comandos creados por
SqlSyncAdapterBuilder. Para obtener más información acerca de los comandos, vea Especificar una sincronización de instantáneas, de descarga, de carga y bidireccional.Las columnas de seguimiento de la base de datos servidor. Las columnas se utilizan para saber cuándo se efectúan los cambios, para que sólo se descarguen los cambios nuevos. Puede incluir columnas adicionales para saber dónde se efectúan los cambios. Para obtener más información, vea Utilizar un sistema de seguimiento de cambios personalizado.
El nombre de
SyncAdapter. Debe coincidir con el nombre deSyncTable. Por consiguiente, no debe incluir el nombre del esquema.
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter(false, false, false, false);
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
customerBuilder.SyncDirection = SyncDirection.DownloadOnly
customerBuilder.CreationTrackingColumn = "InsertTimestamp"
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp"
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp"
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
Usar el sistema de seguimiento de cambios de SQL Server
Para utilizar el sistema de seguimiento de cambios de SQL Server, especifique la información siguiente para SqlSyncAdapterBuilder y SyncAdapter:
El nombre de la tabla que se va a sincronizar.
La dirección de sincronización. Controla los comandos creados por
SqlSyncAdapterBuilder. Para obtener más información acerca de los comandos, vea Especificar una sincronización de instantáneas, de descarga, de carga y bidireccional.El tipo de seguimiento de cambios que se va a utilizar. De forma predeterminada, Sync Framework espera que el usuario especifique las columnas de seguimiento de cambios personalizado. En este ejemplo de código, se especifica el sistema de seguimiento de cambios de SQL Server.
El nombre de
SyncAdapter. Debe coincidir con el nombre deSyncTable. Por consiguiente, no debe incluir el nombre del esquema.
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
Especificar el nuevo comando de delimitador
El ejemplo de código siguiente especifica un comando que recupera un valor de delimitador nuevo del servidor. El valor se almacena en la base de datos cliente y lo utilizan los comandos que sincronizan los cambios. Durante cada sincronización, se usan el valor del delimitador nuevo y el del último delimitador de la sincronización anterior: se sincroniza el conjunto de cambios existentes entre estos límites superior e inferior.
En este caso, MIN_ACTIVE_ROWVERSION devuelve una marca de tiempo de una base de datos SQL Server. (MIN_ACTIVE_ROWVERSION se introdujo en SQL Server 2005 Service Pack 2.) Se usa un valor de marca de tiempo porque las columnas de seguimiento especificadas para SqlSyncAdapterBuilder contienen valores de este tipo. Si las columnas de seguimiento tuvieran valores de fecha, podría usar una función como GETUTCDATE() en lugar de MIN_ACTIVE_ROWVERSION. Para obtener más información sobre los delimitadores, vea Seguimiento de cambios en la base de datos servidor.
La clase SyncSession contiene varias constantes de cadena que pueden utilizarse en los comandos de sincronización. SyncNewReceivedAnchor es una de ellas. También puede usar el literal @sync_new_received_anchor directamente en las consultas.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand
Llamar al método Synchronize
En el ejemplo de código siguiente se crea una instancia de SampleSyncAgent y se llama al método Synchronize. En la clase SampleSyncAgent, se especifica el SampleClientSyncProvider como el LocalProvider y el SampleServerSyncProvider se especifica como el RemoteProvider, así como la tabla de sincronización que ya se ha descrito.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
En la clase SampleStats, las estadísticas que devuelve el SyncAgent permiten proporcionar al usuario sugerencias sobre la sesión de sincronización. Para obtener más información, vea Trabajar con eventos y programar la lógica empresarial.
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
Ejemplos de código completo
Ahora que ya conoce las secciones principales de código utilizadas en la sincronización, estas secciones se combinan en una aplicación completa profusamente comentada. Después de ejecutar esta aplicación, se recomienda que lea los temas de la sección Programar tareas comunes de sincronización de cliente y servidor. Verá las mismas clases que las utilizadas en los ejemplos de código de este tema. No obstante, se aplican en otras tablas y de una manera más sofisticada.
Ejemplo completo utilizando el seguimiento de cambios personalizado
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();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetClientPassword();
Utility.RecreateCompactDatabase();
//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();
//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.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the Customer table 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.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter(false, false, false, false);
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but 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 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);
}
}
public class Utility
{
private static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public static void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public static string ConnStr_SqlCeClientSync
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
//Return the server connection string.
public static string ConnStr_DbServerSync
{
get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public static void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public static void CleanUpServer()
{
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public static void RecreateCompactDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(Utility.ConnStr_SqlCeClientSync))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(Utility.ConnStr_SqlCeClientSync);
sqlCeEngine.CreateDatabase();
}
}
}
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()
'Delete and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetClientPassword()
Utility.RecreateCompactDatabase()
'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()
'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.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
selectNewAnchorCommand.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
selectNewAnchorCommand.Connection = serverConn
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the Customer 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.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
customerBuilder.SyncDirection = SyncDirection.DownloadOnly
customerBuilder.CreationTrackingColumn = "InsertTimestamp"
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp"
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp"
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
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
Public Class Utility
Private Shared _clientPassword As String
'Get and set the client database password.
Public Shared Property Password() As String
Get
Return _clientPassword
End Get
Set(ByVal value As String)
_clientPassword = value
End Set
End Property
'Have the user enter a password for the client database file.
Public Shared Sub SetClientPassword()
Console.WriteLine("Type a strong password for the client")
Console.WriteLine("database, and then press Enter.")
Utility.Password = Console.ReadLine()
End Sub 'SetClientPassword
'Return the client connection string with the password.
Public Shared ReadOnly Property ConnStr_SqlCeClientSync() As String
Get
Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
End Get
End Property
'Return the server connection string.
Public Shared ReadOnly Property ConnStr_DbServerSync() As String
Get
Return "Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"
End Get
End Property
'Make server changes that are synchronized on the second
'synchronization.
Public Shared Sub MakeDataChangesOnServer()
Dim rowCount As Integer = 0
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandText = _
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
& "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
& "UPDATE Sales.Customer " _
& "SET SalesPerson = 'James Bailey' " _
& "WHERE CustomerName = 'Tandem Bicycle Store' " _
& "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
serverConn.Open()
rowCount = sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)
End Sub 'MakeDataChangesOnServer
'Revert changes that were made during synchronization.
Public Shared Sub CleanUpServer()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = "usp_InsertSampleData"
serverConn.Open()
sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
End Sub 'CleanUpServer
'Delete the client database.
Public Shared Sub RecreateCompactDatabase()
Dim clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)
Try
If File.Exists(clientConn.Database) Then
File.Delete(clientConn.Database)
End If
Finally
clientConn.Dispose()
End Try
Dim sqlCeEngine As New SqlCeEngine(Utility.ConnStr_SqlCeClientSync)
sqlCeEngine.CreateDatabase()
End Sub 'RecreateClientDatabase
End Class 'Utility
Ejemplo completo utilizando el seguimiento de cambios de SQL Server
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();
//Delete and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetClientPassword();
Utility.RecreateCompactDatabase();
//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();
//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.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we use a timestamp value
//that is retrieved and stored in the client database.
//During each synchronization, the new anchor value and
//the last anchor value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
//
//SyncSession.SyncNewReceivedAnchor is a string constant;
//you could also use @sync_new_received_anchor directly in
//your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = change_tracking_current_version()";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the Customer table by using
//the SqlSyncAdapterBuilder:
// * Specify the base table name.
// * Specify that the server uses SQL Server change tracking.
// * Specify download-only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but 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 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);
}
}
public class Utility
{
private static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public static void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public static string ConnStr_SqlCeClientSync
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
//Return the server connection string.
public static string ConnStr_DbServerSync
{
get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public static void MakeDataChangesOnServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' " +
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Revert changes that were made during synchronization.
public static void CleanUpServer()
{
using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Delete the client database.
public static void RecreateCompactDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(Utility.ConnStr_SqlCeClientSync))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(Utility.ConnStr_SqlCeClientSync);
sqlCeEngine.CreateDatabase();
}
}
}
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()
'Delete and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetClientPassword()
Utility.RecreateCompactDatabase()
'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()
'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.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly
Me.Configuration.SyncTables.Add(customerSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.Connection = serverConn
'Create a command to retrieve a new anchor value from
'the server. In this case, we use a timestamp value
'that is retrieved and stored in the client database.
'During each synchronization, the new anchor value and
'the last anchor value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
'
'SyncSession.SyncNewReceivedAnchor is a string constant;
'you could also use @sync_new_received_anchor directly in
'your queries.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = _
"SELECT " + newAnchorVariable + " = change_tracking_current_version()"
.Parameters.Add(newAnchorVariable, SqlDbType.BigInt)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.SelectNewAnchorCommand = selectNewAnchorCommand
'Create a SyncAdapter for the Customer table by using
'the SqlSyncAdapterBuilder:
' * Specify the base table names.
' * Specify that the server uses SQL Server change tracking.
' * Specify download-only synchronization.
' * Call ToSyncAdapter to create the SyncAdapter.
' * Specify a name for the SyncAdapter that matches the
' the name specified for the corresponding SyncTable.
' Do not include the schema names (Sales in this case).
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
customerBuilder.TableName = "Sales.Customer"
customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but 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
Public Class Utility
Private Shared _clientPassword As String
'Get and set the client database password.
Public Shared Property Password() As String
Get
Return _clientPassword
End Get
Set(ByVal value As String)
_clientPassword = value
End Set
End Property
'Have the user enter a password for the client database file.
Public Shared Sub SetClientPassword()
Console.WriteLine("Type a strong password for the client")
Console.WriteLine("database, and then press Enter.")
Utility.Password = Console.ReadLine()
End Sub 'SetClientPassword
'Return the client connection string with the password.
Public Shared ReadOnly Property ConnStr_SqlCeClientSync() As String
Get
Return "Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password
End Get
End Property
'Return the server connection string.
Public Shared ReadOnly Property ConnStr_DbServerSync() As String
Get
Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"
End Get
End Property
'Make server changes that are synchronized on the second
'synchronization.
Public Shared Sub MakeDataChangesOnServer()
Dim rowCount As Integer = 0
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandText = _
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
& "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
& "UPDATE Sales.Customer " _
& "SET SalesPerson = 'James Bailey' " _
& "WHERE CustomerName = 'Tandem Bicycle Store' " _
& "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
serverConn.Open()
rowCount = sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)
End Sub 'MakeDataChangesOnServer
'Revert changes that were made during synchronization.
Public Shared Sub CleanUpServer()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Try
Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = "usp_InsertSampleData"
serverConn.Open()
sqlCommand.ExecuteNonQuery()
serverConn.Close()
Finally
serverConn.Dispose()
End Try
End Sub 'CleanUpServer
'Delete the client database.
Public Shared Sub RecreateCompactDatabase()
Dim clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)
Try
If File.Exists(clientConn.Database) Then
File.Delete(clientConn.Database)
End If
Finally
clientConn.Dispose()
End Try
Dim sqlCeEngine As New SqlCeEngine(Utility.ConnStr_SqlCeClientSync)
sqlCeEngine.CreateDatabase()
End Sub 'RecreateClientDatabase
End Class 'Utility
Vea también
Conceptos
Arquitectura y clases para la sincronización del cliente y el servidor
Escenarios sin conexión