Cómo descargar una instantánea de datos en un cliente
En este tema se muestra cómo puede descargar una instantánea de datos desde una base de datos de servidor a una base de datos de cliente de Microsoft SQL Server Compact 3.5 SP1. Los ejemplos de este tema se centran en los siguientes tipos de Sync Services:
Para obtener información acerca de cómo ejecutar el código de ejemplo, vea "Aplicaciones de ejemplo en los temas "Cómo..."" en Programar tareas comunes de sincronización de cliente y servidor.
Ejemplo
El ejemplo de código de este tema muestra cómo puede configurar la sincronización de instantáneas para las tablas Customer, OrderHeader y OrderDetail de la base de datos de ejemplo Sync Services. Para obtener una introducción a las propiedades que se usan para especificar la dirección de sincronización, vea Cómo especificar una sincronización de instantáneas, de descarga, de carga y bidireccional.
Partes principales de la API
Esta sección proporciona ejemplos de código que destacan las partes principales de la API que se usan en la sincronización de instantáneas. El ejemplo de código siguiente especifica la dirección de sincronización y cómo debe crearse la tabla en el cliente.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.Snapshot;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.Snapshot
customerSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
El ejemplo de código siguiente especifica un comando que selecciona filas en el servidor y las aplica al cliente. Consta de una instrucción SELECT que incluye las columnas que se sincronizan. También puede agregar una cláusula WHERE para filtrar las filas. Para obtener más información sobre los filtros, vea Cómo filtrar filas y columnas. Dado que los datos se actualizan completamente durante una sincronización, el comando no incluye las columnas de seguimiento que se requieren para la sincronización de sólo descarga y bidireccional.
SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer";
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
this.SyncAdapters.Add(customerSyncAdapter);
Dim customerSyncAdapter As New SyncAdapter("Customer")
Dim customerIncrInserts As New SqlCommand()
customerIncrInserts.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer"
customerIncrInserts.Connection = serverConn
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
Me.SyncAdapters.Add(customerSyncAdapter)
Ejemplo de código completo
El ejemplo de código completo siguiente incluye los ejemplos de código descritos anteriormente y un código adicional para realizar la sincronización. Tenga en cuenta también cómo se usa SyncStatistics para mostrar información de la sesión de sincronización. Para el ejemplo, se requiere que la clase Utility esté disponible en Clase de utilidad para los temas "Cómo..." de Sync Services.
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 Utility class handles all functionality that is not
//directly related to synchronization, such as holding
//connection string information and making changes to the
//server and client databases.
Utility util = new Utility();
//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.
util.SetClientPassword();
util.RecreateClientDatabase();
//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.
util.MakeDataChangesOnServer("Customer");
//Subsequent synchronization. There was one insert,
//one update, and one delete made on the server;
//therefore, the row count is identical, but the
//data is different.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return server data back to its original state.
util.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
//Snapshot, and that any existing tables should be dropped.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.Snapshot;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.Snapshot;
orderHeaderSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderDetailSyncTable.SyncDirection = SyncDirection.Snapshot;
orderDetailSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderDetailSyncTable);
}
}
//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(util.ServerConnString);
this.Connection = serverConn;
//Create a SyncAdapter for each table, and then define
//the command to select rows from the table. With the Snapshot
//option, you do not download incremental changes. However,
//you still use the SelectIncrementalInsertsCommand to select
//the rows to download for each snapshot. The commands include
//only those columns that you want on the client.
//Customer table.
SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");
SqlCommand customerIncrInserts = new SqlCommand();
customerIncrInserts.CommandText =
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
"FROM Sales.Customer";
customerIncrInserts.Connection = serverConn;
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;
this.SyncAdapters.Add(customerSyncAdapter);
//OrderHeader table.
SyncAdapter orderHeaderSyncAdapter = new SyncAdapter("OrderHeader");
SqlCommand orderHeaderIncrInserts = new SqlCommand();
orderHeaderIncrInserts.CommandText =
"SELECT OrderId, CustomerId, OrderDate, OrderStatus " +
"FROM Sales.OrderHeader";
orderHeaderIncrInserts.Connection = serverConn;
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts;
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//OrderDetail table.
SyncAdapter orderDetailSyncAdapter = new SyncAdapter("OrderDetail");
SqlCommand orderDetailIncrInserts = new SqlCommand();
orderDetailIncrInserts.CommandText =
"SELECT OrderDetailId, OrderId, Product, Quantity " +
"FROM Sales.OrderDetail";
orderDetailIncrInserts.Connection = serverConn;
orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts;
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 = util.ClientConnString;
}
}
//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 Utility class handles all functionality that is not
'directly related to synchronization, such as holding
'connection string information and making changes to the
'server and client databases.
Dim util As New Utility()
'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.
util.SetClientPassword()
util.RecreateClientDatabase()
'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.
util.MakeDataChangesOnServer("Customer")
'Subsequent synchronization. There was one insert,
'one update, and one delete made on the server;
'therefore, the row count is identical, but the
'data is different.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return server data back to its original state.
util.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
'Snapshot, and that any existing tables should be dropped.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerSyncTable.SyncDirection = SyncDirection.Snapshot
customerSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.Snapshot
orderHeaderSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
Dim orderDetailSyncTable As New SyncTable("OrderDetail")
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderDetailSyncTable.SyncDirection = SyncDirection.Snapshot
orderDetailSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderDetailSyncTable)
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(util.ServerConnString)
Me.Connection = serverConn
'Create a SyncAdapter for each table, and then define
'the command to select rows from the table. With the Snapshot
'option, you do not download incremental changes. However,
'you still use the SelectIncrementalInsertsCommand to select
'the rows to download for each snapshot. The commands include
'only those columns that you want on the client.
'Customer table.
Dim customerSyncAdapter As New SyncAdapter("Customer")
Dim customerIncrInserts As New SqlCommand()
customerIncrInserts.CommandText = _
"SELECT CustomerId, CustomerName, SalesPerson, CustomerType " _
& "FROM Sales.Customer"
customerIncrInserts.Connection = serverConn
customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts
Me.SyncAdapters.Add(customerSyncAdapter)
'OrderHeader table.
Dim orderHeaderSyncAdapter As New SyncAdapter("OrderHeader")
Dim orderHeaderIncrInserts As New SqlCommand()
orderHeaderIncrInserts.CommandText = _
"SELECT OrderId, CustomerId, OrderDate, OrderStatus " _
& "FROM Sales.OrderHeader"
orderHeaderIncrInserts.Connection = serverConn
orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts
Me.SyncAdapters.Add(orderHeaderSyncAdapter)
'OrderDetail table.
Dim orderDetailSyncAdapter As New SyncAdapter("OrderDetail")
Dim orderDetailIncrInserts As New SqlCommand()
orderDetailIncrInserts.CommandText = _
"SELECT OrderDetailId, OrderId, Product, Quantity " _
& "FROM Sales.OrderDetail"
orderDetailIncrInserts.Connection = serverConn
orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts
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 = util.ClientConnString
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
Vea también
Conceptos
Arquitectura y clases para la sincronización del cliente y el servidor
Cómo especificar una sincronización de instantáneas, de descarga, de carga y bidireccional