Configurar y ejecutar la sincronización de colaboración (no SQL Server)
Nota
La finalidad de los temas de esta sección de la documentación, Sincronizar otras bases de datos compatibles con ADO.NET, es mostrar cómo se pueden sincronizar otras bases de datos distintas de SQL Server mediante Sync Framework. En esta versión, se utiliza SQL Server en los ejemplos de código, pero el código puede utilizarse en otras bases de datos compatibles con ADO.NET, siempre y cuando se modifiquen los objetos específicos de SQL Server (como SqlConnection) y las consultas SQL que se muestran. Para obtener información acerca de la sincronización de SQL Server, vea Configurar y ejecutar la sincronización de colaboración (SQL Server).
En este tema se describen los componentes clave de una aplicación que utiliza Sync Framework para sincronizar varias bases de datos. El código de esta aplicación se centra en las clases de Sync Framework siguientes:
Para obtener información acerca de cómo se ejecuta el código de ejemplo, vea "Aplicaciones de ejemplo en los temas sobre procedimientos" en Sincronizar otras bases de datos compatibles con ADO.NET.
Tal y como se describe en Arquitectura y clases de la sincronización de colaboración, la sincronización puede producirse entre dos instancias de DbSyncProvider, dos instancias de SqlCeSyncProvider o una instancia de cada proveedor. El código de ejemplo de este tema procede de una aplicación de dos niveles; por tanto, aquí no se muestra la sincronización de dos instancias de SqlCeSyncProvider, lo que requiere una configuración de n niveles. Para obtener un ejemplo de una configuración de n niveles, vea el ejemplo de WebSharingAppDemo-CEProviderEndToEnd que se incluye en el SDK de Sync Framework.
Configurar la sincronización implica los pasos siguientes:
Crear tablas de seguimiento para almacenar los metadatos y procedimientos almacenados para actualizar los datos y metadatos de cada base de datos servidor. Para obtener más información, vea Aprovisionar una base de datos servidor para la sincronización de colaboración (no SQL Server).
Inicializar cada base de datos servidor con la infraestructura de seguimiento de cambios, los datos y el esquema.
Ejecutar la sincronización implica los pasos siguientes:
Crear un proveedor de sincronización y adaptadores de sincronización de servidor, así como un proveedor de sincronización de cliente.
Inicializar cada base de datos cliente.
Crear un organizador de la sincronización y sincronizar los nodos.
Inicializar una base de datos servidor
Inicializar una base de datos implica copiar en cada base de datos el esquema de tabla y la infraestructura de seguimiento de cambios, además de los datos iniciales que se requieran. En las bases de datos que se sincronizan utilizando DbSyncProvider, Sync Framework no crea el esquema de tabla ni la infraestructura de seguimiento de cada base de datos automáticamente. Una aplicación debe asegurarse de que estos objetos existen antes de intentar sincronizar los nodos. Para copiar los objetos en cada nodo, puede utilizar la tecnología de copias de seguridad y restauración, o cualquier otra, pero solo si los cambios no se están produciendo en la base de datos de la que se toma la copia de seguridad. Si la primera base de datos se está usando y actualizando, recomendamos encarecidamente que solo copie la infraestructura de seguimiento de cambios y el esquema en cada nodo, y que use Sync Framework para copiar los datos. Si copia los datos utilizando otro método, algún nodo podría perder los cambios que se hayan confirmado en el primer nodo. Sync Framework puede inicializar los datos en cada nodo siempre que al menos uno tenga los datos. En el código de ejemplo de este tema se utiliza este enfoque: cada base de datos contiene dos tablas, pero solo las tablas de SyncSamplesDb_Peer1 contienen datos. Los datos se copian en los demás nodos durante la primera sesión de sincronización.
Para obtener información sobre las opciones de inicialización de SqlCeSyncProvider, vea Inicializar una base de datos cliente más adelante en este tema.
Ejecutar la sincronización
Los ejemplos de código de esta sección se dividen en las categorías siguientes:
Los comandos y el objeto DbSyncAdapter de cada tabla que se sincronizarán en la base de datos servidor.
Los comandos y el objeto DbSyncProvider del servidor.
El objeto SqlCeSyncProvider del cliente.
El objeto SyncSchema y el método GenerateSnapshot, que se utilizan para inicializar la base de datos cliente.
El objeto SyncOrchestrator, que se utiliza para sincronizar las bases de datos cliente y servidor.
Adaptador de sincronización
Las propiedades siguientes se establecen en el objeto DbSyncAdapter de cada tabla.
| Propiedad del adaptador de sincronización | Uso |
|---|---|
Especifica las columnas de clave principal de la tabla. Por ejemplo, si una tabla |
|
Selecciona todos los cambios realizados desde la sesión de sincronización anterior mediante la combinación de la tabla base y su tabla de seguimiento de cambios. Se trata del conjunto máximo de cambios que se pudieron sincronizar. Este conjunto de cambios podría reducirse si se tiene en cuenta el conocimiento de sincronización. |
|
Aplican a un nodo las inserciones, actualizaciones y eliminaciones que se seleccionaron en otro nodo. Estos cambios se seleccionaron utilizando la consulta o el procedimiento que se especificó para la propiedad SelectIncrementalChangesCommand. |
|
InsertMetadataCommand, UpdateMetadataCommand y DeleteMetadataCommand |
Actualizan las tablas de seguimiento de cambios en un nodo para reflejar los cambios que se seleccionaron en el nodo y se aplicaron a otro nodo. Estas actualizaciones permiten que Sync Framework realice el seguimiento de dónde y cuándo se produjeron los cambios. |
Selecciona los metadatos de una fila que presenta conflictos durante la sincronización. |
|
Selecciona los metadatos que se pueden limpiar en un nodo. La limpieza normalmente se basa en la retención, es decir, los metadatos se guardan durante un tiempo concreto. Sin embargo, una aplicación puede utilizar otra lógica para determinar cuándo limpiar los metadatos. Para obtener más información, vea Limpiar los metadatos de la sincronización de colaboración (no SQL Server). |
En los ejemplos de código de esta sección, se crean comandos que el objeto DbSyncAdapter ejecuta durante la sincronización. Estos comandos llaman a los procedimientos almacenados que se describen en Aprovisionar una base de datos servidor para la sincronización de colaboración (no SQL Server). El conjunto completo de comandos se incluye en el ejemplo de código completo al final de este tema. En el código, los parámetros con el formato "@" + DbSyncSession son variables de sesión. Por ejemplo, "@" + DbSyncSession.SyncMinTimestamp es una variable que se resuelve como un valor de @sync_min_timestamp. Para obtener más información, vea Usar variables de sesión para la sincronización de colaboración.
Código de la aplicación para SelectIncrementalChangesCommand
SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" & DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
Código de la aplicación para UpdateCommand
SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate";
updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.UpdateCommand = updCustomerCmd;
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
Código de la aplicación para UpdateMetadataCommand
SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
Código de la aplicación para SelectRowCommand
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sync.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectRowCommand = selRowCustomerCmd;
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
End With
adapterCustomer.SelectRowCommand = selRowCustomerCmd
Una vez definidos todos los comandos, el adaptador se agrega al proveedor mediante el código siguiente.
sampleProvider.SyncAdapters.Add(adapterCustomer);
Proveedor de sincronización del servidor
Los propiedades siguientes se establecen en el objeto DbSyncProvider del servidor.
| Propiedad del proveedor de sincronización | Uso |
|---|---|
Nombre de los ámbitos que se van a sincronizar en una sesión determinada. Todos los ámbitos deberían existir ya en la tabla de información de ámbito de la base de datos servidor. |
|
Conexión a través de la que Sync Framework selecciona y aplica los cambios a la base de datos servidor. |
|
Devuelve un valor de marca de tiempo que se utiliza para seleccionar y aplicar los conjuntos de cambios a cada base de datos. Durante la sesión de sincronización actual, el comando proporciona un nuevo valor de marca de tiempo. Se sincronizan los cambios realizados después del último valor de marca de tiempo procedente de la sesión de sincronización anterior y antes del nuevo valor de marca de tiempo. El nuevo valor se almacena y se utiliza como valor inicial de la siguiente sesión. |
|
Selecciona la marca de tiempo máxima de cada tabla base o tabla de seguimiento para determinar si el destino tiene ya todos los cambios del origen para cada tabla. Si el destino ya tiene los cambios, Sync Framework a menudo puede evitar ejecutar consultas de enumeración, lo que puede mejorar el rendimiento. Esta propiedad es opcional; si no se especifica un comando, las consultas de enumeración se ejecutan en todas las tablas. |
|
Devuelve información de la tabla de información de ámbito, como el conocimiento de sincronización y el conocimiento de limpieza que Sync Framework requiere. |
|
Actualiza la información de la tabla de información de ámbito. |
|
Devuelve el nombre de ámbito y el nombre de tabla de todas las tablas del ámbito especificado que también están incluidas en otros ámbitos. |
|
Actualiza la columna |
En los ejemplos de código de esta sección, se crean comandos que el objeto DbSyncProvider ejecuta durante la sincronización. Los comandos para las propiedades UpdateScopeInfoCommand y SelectScopeInfoCommand están incluidos en el ejemplo de código completo al final de este tema.
Código de la aplicación para ScopeName y Connection
En el ejemplo de código siguiente se establece el nombre del ámbito que se va a sincronizar y la conexión a través de la cual se realiza la sincronización. En el ejemplo de código completo, el objeto DbSyncProvider contiene un objeto DbSyncAdapter para la tabla Customer. Cuando esta tabla se sincroniza por primera vez, se establece la definición del ámbito Sales. Una vez que un ámbito se ha sincronizado por primera vez, no debería cambiarse. Si se cambian las tablas del ámbito o las cláusulas de filtro de esas tablas, puede producirse una falta de convergencia de los datos.
SqlConnection peerConnection = new SqlConnection(peerConnString);
sampleDbProvider.Connection = peerConnection;
sampleDbProvider.ScopeName = "Sales";
Dim peerConnection As New SqlConnection(peerConnString)
sampleDbProvider.Connection = peerConnection
sampleDbProvider.ScopeName = "Sales"
Para obtener más información sobre los ámbitos, vea "Definir los ámbitos que se van a sincronizar" en Aprovisionar una base de datos servidor para la sincronización de colaboración (no SQL Server).
Código de la aplicación para SelectNewTimestampCommand
En el ejemplo de código siguiente se crea un comando para la propiedad SelectNewTimestampCommand.
SqlCommand selectNewTimestampCommand = new SqlCommand();
string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
Dim selectNewTimestampCommand As New SqlCommand()
Dim newTimestampVariable As String = "@" & DbSyncSession.SyncNewTimestamp
With selectNewTimestampCommand
.CommandText = "SELECT " & newTimestampVariable & " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand
Código de la aplicación para SelectTableMaxTimestampsCommand
En el ejemplo de código siguiente se crea un comando para la propiedad SelectTableMaxTimestampsCommand. En el ejemplo de código completo, hay sesiones de sincronización en las que no se realizaron cambios nuevos en ningún nodo. En estas sesiones, la llamada a SelectTableMaxTimestampsCommand indica que no hay ningún cambio en los datos disponible para sincronizar, por lo que no se llama a SelectIncrementalChangesCommand.
SqlCommand selTableMaxTsCmd = new SqlCommand();
selTableMaxTsCmd.CommandType = CommandType.Text;
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
"MAX(local_update_peer_timestamp) AS max_timestamp " +
"FROM Sync.Customer_Tracking";
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
Dim selTableMaxTsCmd As New SqlCommand()
selTableMaxTsCmd.CommandType = CommandType.Text
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
& "MAX(local_update_peer_timestamp) AS max_timestamp " _
& "FROM Sync.Customer_Tracking"
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd
Proveedor de sincronización del cliente
En el ejemplo de código siguiente se crea un objeto SqlCeSyncProvider, se establece el ámbito y la conexión, y se registran los controladores de eventos. En SqlCeSyncProvider, los adaptadores son privados y sus comandos se crean automáticamente en función del esquema que se inicializa en la base de datos cliente.
public SqlCeSyncProvider ConfigureCeSyncProvider(string sqlCeConnString)
{
SqlCeSyncProvider sampleCeProvider = new SqlCeSyncProvider();
//Set the scope name
sampleCeProvider.ScopeName = "Sales";
//Set the connection
sampleCeProvider.Connection = new SqlCeConnection(sqlCeConnString);
//Register event handlers
//Register the BeginSnapshotInitialization event handler.
//It is called when snapshot initialization is about to begin
//for a particular scope in a Compact database.
sampleCeProvider.BeginSnapshotInitialization += new EventHandler<DbBeginSnapshotInitializationEventArgs>(sampleCeProvider_BeginSnapshotInitialization);
//Register the EndSnapshotInitialization event handler.
//It is called when snapshot initialization has completed
//for a particular scope in a Compact database.
sampleCeProvider.EndSnapshotInitialization += new EventHandler<DbEndSnapshotInitializationEventArgs>(sampleCeProvider_EndSnapshotInitialization);
return sampleCeProvider;
}
Public Function ConfigureCeSyncProvider(ByVal sqlCeConnString As String) As SqlCeSyncProvider
Dim sampleCeProvider As New SqlCeSyncProvider()
'Set the scope name
sampleCeProvider.ScopeName = "Sales"
'Set the connection
sampleCeProvider.Connection = New SqlCeConnection(sqlCeConnString)
'Register event handlers
'Register the BeginSnapshotInitialization event handler.
'It is called when snapshot initialization is about to begin
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.BeginSnapshotInitialization, AddressOf sampleCeProvider_BeginSnapshotInitialization
'Register the EndSnapshotInitialization event handler.
'It is called when snapshot initialization has completed
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.EndSnapshotInitialization, AddressOf sampleCeProvider_EndSnapshotInitialization
Return sampleCeProvider
End Function
Inicializar una base de datos cliente
Antes de que una base de datos cliente de SQL Server Compact pueda empezar a recibir cambios de otra base de datos, la base de datos cliente debe contener un esquema de tabla y algunos datos iniciales, así como la infraestructura de seguimiento de cambios que Sync Framework necesita. Sync Framework dispone de dos mecanismos para inicializar la base de datos:
La inicialización completa de la base de datos cliente a través de la base de datos servidor
De este modo, las tablas se pueden crear en la base de datos cliente mediante las tablas, las columnas y las tablas de descripción de tipos expuestas por un objeto DbSyncProvider que se conecta a una instancia de la base de datos servidor. A continuación, la base de datos cliente se prepara para la sincronización y todas las filas de la base de datos servidor se descargan como inserciones incrementales. Las inserciones se seleccionan en el servidor utilizando la consulta o el procedimiento que se especificó para la propiedad SelectIncrementalChangesCommand.
La inicialización de la instantánea de la base de datos cliente a través de una base de datos cliente que ya existe
La inicialización de una instantánea está diseñada para reducir el tiempo necesario para inicializar una base de datos cliente. Una vez que se ha inicializado una base de datos cliente a través de la inicialización completa, se pueden inicializar posteriormente otras bases de datos utilizando una instantánea de esta primera base de datos cliente. Una instantánea es una base de datos de SQL Server Compact preparada de manera especial que contiene el esquema de tabla, los datos (opcional) y la infraestructura de seguimiento de cambios. Copie esta instantánea en cada cliente que lo necesite. Durante la primera sesión de sincronización de un cliente, los metadatos específicos del cliente se actualizan y se descarga en la base de datos cliente cualquier cambio que se haya producido desde que se creó la instantánea.
Importante
Las instantáneas deberían generarse únicamente cuando no haya actividad en la base de datos de SQL Server Compact. No se admiten operaciones simultáneas de ningún tipo durante la generación de la instantánea.
En el ejemplo del código siguiente se realiza la inicialización completa. El código comprueba si una base de datos cliente requiere un esquema cada vez que tiene lugar la sincronización. Si el proveedor tiene una conexión a una base de datos cliente que ya se ha inicializado o se trata de una instantánea de la base de datos, el método ScopeExists devuelve true. Si el método devuelve false, se recupera un esquema y se aplica a la base de datos cliente utilizando el método GetScopeDescription.
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName, ceConn))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply(ceConn);
}
}
}
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConfig As New SqlCeSyncScopeProvisioning()
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName, ceConn) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply(ceConn)
End If
End If
End Sub
End Class
En el ejemplo del código siguiente se genera una instantánea. El código se conecta a una base de datos de SQL Server Compact que se sincronizó utilizando el método ConfigureCESyncProvider del ejemplo. Después de conectarse, se llama al método GenerateSnapshot, que crea una copia de la base de datos original. Cuando el proveedor del cliente se conecta posteriormente a esta nueva copia y se ejecuta la sincronización, se inicializa la base de datos.
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Nota
En este ejemplo, la base de datos de la instantánea se genera mientras la aplicación principal se está ejecutando. En muchos casos, las instantáneas se generan en horas de poca actividad independientemente de otras actividades de sincronización.
Crear un organizador de la sincronización y sincronizar bases de datos
En el código que se describe en las secciones anteriores de este tema, se muestra cómo se establecen las propiedades necesarias para la sincronización. Ahora es el momento de sincronizar los nodos. Los nodos siempre se sincronizan por pares, como SyncSamplesDb_Peer1 y SyncSampleCe2. En una aplicación de producción, normalmente se implementaría una copia de la aplicación en cada nodo para que la sincronización se pudiera iniciar desde cualquiera de los nodos.
En el ejemplo de código siguiente, se crea una clase SampleSyncAgent derivada de SyncOrchestrator. El constructor SampleSyncAgent toma dos objetos RelationalSyncProvider. Dado que DbSyncProvider y SqlCeSyncProvider se derivan de RelationalSyncProvider, un objeto SampleSyncAgent puede sincronizar cualquier combinación de los dos tipos de proveedor. El código especifica cuál es el proveedor local y cuál el remoto. A continuación, especifica qué cambios se cargan primero desde la base de datos remota a la base de datos local y posteriormente los descarga en la otra dirección. El código comprueba si alguno de los proveedores es un objeto SqlCeSyncProvider que requiere la inicialización del esquema de una base de datos cliente.
public class SampleSyncAgent : SyncOrchestrator
{
public SampleSyncAgent(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
//Check to see if any provider is a SqlCe provider and if it needs to
//be initialized.
CheckIfProviderNeedsSchema(localProvider as SqlCeSyncProvider, remoteProvider as DbSyncProvider);
CheckIfProviderNeedsSchema(remoteProvider as SqlCeSyncProvider, localProvider as DbSyncProvider);
}
//For Compact databases that are not initialized with a snapshot,
//get the schema and initial data from a server database.
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName, ceConn))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply(ceConn);
}
}
}
}
Public Class SampleSyncAgent
Inherits SyncOrchestrator
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
'Check to see if any provider is a SqlCe provider and if it needs to
'be initialized.
CheckIfProviderNeedsSchema(TryCast(localProvider, SqlCeSyncProvider), _
TryCast(remoteProvider, DbSyncProvider))
CheckIfProviderNeedsSchema(TryCast(remoteProvider, SqlCeSyncProvider), _
TryCast(localProvider, DbSyncProvider))
End Sub
'For Compact databases that are not initialized with a snapshot,
'get the schema and initial data from a server database.
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConfig As New SqlCeSyncScopeProvisioning()
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName, ceConn) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply(ceConn)
End If
End If
End Sub
End Class
En el código siguiente se configuran las sesiones de sincronización llamando a ConfigureDbSyncProvider o ConfigureCeSyncProvider; la aplicación de ejemplo utiliza estos métodos para establecer las propiedades de proveedor adecuadas para cada sesión. A continuación, el código llama al método Synchronize de SampleSyncAgent para sincronizar cada par de bases de datos.
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
//First session: Synchronize two databases by using two instances
//of DbSyncProvider.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
'First session: Synchronize two databases by using two instances
'of DbSyncProvider.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Ejemplo de código completo
En el ejemplo de código completo siguiente se incluyen los ejemplos de código descritos anteriormente en este tema y código adicional para mostrar las estadísticas de sincronización y la información de eventos. Para el ejemplo, se requiere que la clase Utility esté disponible en Clase de utilidad para los temas de procedimientos del proveedor de bases de datos.
using System;
using System.Collections.Generic;
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.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 peerConnection
//string information and making changes to the server database.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, true);
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, false);
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
try
{
//Initial synchronization. Instantiate the SyncOrchestrator
//and call Synchronize.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
//First session: Synchronize two databases by using two instances
//of DbSyncProvider.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Second session: Synchronize two databases by using one instance of
//DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
//database is initialized, it is copied by using GenerateSnapshot and then
//used for the third session.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Copy the Compact database and save it as SyncSampleClient2.sdf.
SqlCeSyncStoreSnapshotInitialization snapshotInit = new SqlCeSyncStoreSnapshotInitialization();
snapshotInit.GenerateSnapshot(new SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf");
//Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer");
//Third session: Synchronize the new Compact database. The five rows
//from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
//change is now downloaded to bring SyncSampleClient2.sdf up to date.
//SyncSampleClient2.sdf will get this row during the next round of
//synchronization sessions.
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Make a change in one of the databases.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer");
try
{
//Subsequent synchronization. Changes are now synchronized between all
//nodes.
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
SyncOrchestrator sampleSyncAgent;
SyncOperationStatistics syncStatistics;
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
sampleSyncAgent = new SampleSyncAgent(
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1),
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2));
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
}
catch (DbOutdatedSyncException ex)
{
Console.WriteLine("Outdated Knowledge: " + ex.OutdatedPeerSyncKnowledge.ToString() +
" Clean up knowledge: " + ex.MissingCleanupKnowledge.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Return data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1);
Utility.CleanUpNode(Utility.ConnStr_DbSync2);
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncOrchestrator.
public class SampleSyncAgent : SyncOrchestrator
{
public SampleSyncAgent(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
{
this.LocalProvider = localProvider;
this.RemoteProvider = remoteProvider;
this.Direction = SyncDirectionOrder.UploadAndDownload;
//Check to see if any provider is a SqlCe provider and if it needs to
//be initialized.
CheckIfProviderNeedsSchema(localProvider as SqlCeSyncProvider, remoteProvider as DbSyncProvider);
CheckIfProviderNeedsSchema(remoteProvider as SqlCeSyncProvider, localProvider as DbSyncProvider);
}
//For Compact databases that are not initialized with a snapshot,
//get the schema and initial data from a server database.
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName, ceConn))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply(ceConn);
}
}
}
}
public class SampleSyncProvider
{
public SqlCeSyncProvider ConfigureCeSyncProvider(string sqlCeConnString)
{
SqlCeSyncProvider sampleCeProvider = new SqlCeSyncProvider();
//Set the scope name
sampleCeProvider.ScopeName = "Sales";
//Set the connection
sampleCeProvider.Connection = new SqlCeConnection(sqlCeConnString);
//Register event handlers
//Register the BeginSnapshotInitialization event handler.
//It is called when snapshot initialization is about to begin
//for a particular scope in a Compact database.
sampleCeProvider.BeginSnapshotInitialization += new EventHandler<DbBeginSnapshotInitializationEventArgs>(sampleCeProvider_BeginSnapshotInitialization);
//Register the EndSnapshotInitialization event handler.
//It is called when snapshot initialization has completed
//for a particular scope in a Compact database.
sampleCeProvider.EndSnapshotInitialization += new EventHandler<DbEndSnapshotInitializationEventArgs>(sampleCeProvider_EndSnapshotInitialization);
return sampleCeProvider;
}
public void sampleCeProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
Console.WriteLine("Full Initialization Process Started.....");
Console.WriteLine(
string.Format("CreatingSchame Event fired for Database {0}", e.Connection.Database)
);
}
public void sampleCeProvider_BeginSnapshotInitialization(object sender, DbBeginSnapshotInitializationEventArgs e)
{
Console.WriteLine("");
Console.WriteLine("Snapshot initialization process started");
Console.WriteLine(
string.Format("BeginSnapshotInitialization event fired for scope {0}", e.ScopeName)
);
}
public void sampleCeProvider_EndSnapshotInitialization(object sender, DbEndSnapshotInitializationEventArgs e)
{
Console.WriteLine("EndSnapshotInitialization event fired");
Dictionary<string, DbSnapshotInitializationTableStatistics> tableStats =
e.TableInitializationStatistics;
foreach (string tableName in tableStats.Keys)
{
DbSnapshotInitializationTableStatistics ts = tableStats[tableName];
Console.WriteLine("\tTable Name: " + tableName);
Console.WriteLine("\tTotal Rows: " + ts.TotalRows);
Console.WriteLine("\tRows Intialized: " + ts.RowsInitialized);
Console.WriteLine("\tStart Time: " + ts.StartTime);
Console.WriteLine("\tEnd Time: " + ts.EndTime);
}
Console.WriteLine("Snapshot initialization process completed");
Console.WriteLine("");
}
public DbSyncProvider ConfigureDbSyncProvider(string peerConnString)
{
DbSyncProvider sampleDbProvider = new DbSyncProvider();
SqlConnection peerConnection = new SqlConnection(peerConnString);
sampleDbProvider.Connection = peerConnection;
sampleDbProvider.ScopeName = "Sales";
//Create a DbSyncAdapter object for the Customer table and associate it
//with the DbSyncProvider. Following the DataAdapter style in ADO.NET,
//DbSyncAdapter is the equivalent for synchronization. The commands that
//are specified for the DbSyncAdapter object call stored procedures
//that are created in each peer database.
DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer");
//Specify the primary key, which Sync Framework uses
//to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId");
//Specify the command to select incremental changes.
//In this command and other commands, session variables are
//used to pass information at runtime. DbSyncSession.SyncMetadataOnly
//and SyncMinTimestamp are two of the string constants that
//the DbSyncSession class exposes. You could also include
//@sync_metadata_only and @sync_min_timestamp directly in your
//queries:
//* sync_metadata_only is used by Sync Framework as an optimization
// in some queries.
//* The value of the sync_min_timestamp session variable is compared to
// values in the sync_row_timestamp column in the tracking table to
// determine which rows to select.
SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sync.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;
//Specify the command to insert rows.
//The sync_row_count session variable is used in this command
//and other commands to return a count of the rows affected by an operation.
//A count of 0 indicates that an operation failed.
SqlCommand insCustomerCmd = new SqlCommand();
insCustomerCmd.CommandType = CommandType.StoredProcedure;
insCustomerCmd.CommandText = "Sync.sp_Customer_ApplyInsert";
insCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
insCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
insCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.InsertCommand = insCustomerCmd;
//Specify the command to update rows.
//The value of the sync_min_timestamp session variable is compared to
//values in the sync_row_timestamp column in the tracking table to
//determine which rows to update.
SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sync.sp_Customer_ApplyUpdate";
updCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updCustomerCmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.UpdateCommand = updCustomerCmd;
//Specify the command to delete rows.
//The value of the sync_min_timestamp session variable is compared to
//values in the sync_row_timestamp column in the tracking table to
//determine which rows to delete.
SqlCommand delCustomerCmd = new SqlCommand();
delCustomerCmd.CommandType = CommandType.StoredProcedure;
delCustomerCmd.CommandText = "Sync.sp_Customer_ApplyDelete";
delCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
adapterCustomer.DeleteCommand = delCustomerCmd;
//Specify the command to select any conflicting rows.
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sync.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectRowCommand = selRowCustomerCmd;
//Specify the command to insert metadata rows.
//The session variables in this command relate to columns in
//the tracking table.
SqlCommand insMetadataCustomerCmd = new SqlCommand();
insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
insMetadataCustomerCmd.CommandText = "Sync.sp_Customer_InsertMetadata";
insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd;
//Specify the command to update metadata rows.
SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sync.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;
//Specify the command to delete metadata rows.
SqlCommand delMetadataCustomerCmd = new SqlCommand();
delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
delMetadataCustomerCmd.CommandText = "Sync.sp_Customer_DeleteMetadata";
delMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd;
//Add the adapter to the provider.
sampleDbProvider.SyncAdapters.Add(adapterCustomer);
// Configure commands that relate to the provider itself rather
// than the DbSyncAdapter object for each table:
// * SelectNewTimestampCommand: Returns the new high watermark for
// the current synchronization session.
// * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge,
// and a scope version (timestamp).
// * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.
// * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table
// or tracking table, to determine whether for each table the destination already
// has all of the changes from the source. If a destination table has all the changes,
// SelectIncrementalChangesCommand is not called for that table.
// There are additional commands related to metadata cleanup that are not
// included in this application.
//Select a new timestamp.
//During each synchronization, the new value and
//the last value from the previous synchronization
//are used: the set of changes between these upper and
//lower bounds is synchronized.
SqlCommand selectNewTimestampCommand = new SqlCommand();
string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
//Specify the command to select local replica metadata.
SqlCommand selReplicaInfoCmd = new SqlCommand();
selReplicaInfoCmd.CommandType = CommandType.Text;
selReplicaInfoCmd.CommandText = "SELECT " +
"scope_id, " +
"scope_local_id, " +
"scope_sync_knowledge, " +
"scope_tombstone_cleanup_knowledge, " +
"scope_timestamp " +
"FROM Sync.ScopeInfo " +
"WHERE scope_name = @" + DbSyncSession.SyncScopeName;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd;
//Specify the command to update local replica metadata.
SqlCommand updReplicaInfoCmd = new SqlCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "UPDATE Sync.ScopeInfo SET " +
"scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
"scope_id = @" + DbSyncSession.SyncScopeId + ", " +
"scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
"WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
" ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
"SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
//Return the maximum timestamp from the Customer_Tracking table.
//If more tables are synchronized, the query should UNION
//all of the results. The table name is not schema-qualified
//in this case because the name was not schema qualified in the
//DbSyncAdapter constructor.
SqlCommand selTableMaxTsCmd = new SqlCommand();
selTableMaxTsCmd.CommandType = CommandType.Text;
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
"MAX(local_update_peer_timestamp) AS max_timestamp " +
"FROM Sync.Customer_Tracking";
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
return sampleDbProvider;
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
Console.WriteLine(String.Empty);
}
}
}
}
Imports System
Imports System.Collections.Generic
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.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 database.
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync1, True)
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeSync2, False)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
Try
'Initial synchronization. Instantiate the SyncOrchestrator
'and call Synchronize.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
'First session: Synchronize two databases by using two instances
'of DbSyncProvider.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Second session: Synchronize two databases by using one instance of
'DbSyncProvider and one instance of SqlCeSyncProvider. After the Compact
'database is initialized, it is copied by using GenerateSnapshot and then
'used for the third session.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Copy the Compact database and save it as SyncSampleClient2.sdf.
Dim snapshotInit As New SqlCeSyncStoreSnapshotInitialization()
snapshotInit.GenerateSnapshot(New SqlCeConnection(Utility.ConnStr_SqlCeSync1), "SyncSampleClient2.sdf")
'Make a change that is synchronized during the third session.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync1, "Customer")
'Third session: Synchronize the new Compact database. The five rows
'from SyncSampleClient1.sdf are already in SyncSampleClient2.sdf. The new
'change is now downloaded to bring SyncSampleClient2.sdf up to date.
'SyncSampleClient2.sdf will get this row during the next round of
'synchronization sessions.
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Make a change in one of the databases.
Utility.MakeDataChangesOnNode(Utility.ConnStr_DbSync2, "Customer")
Try
'Subsequent synchronization. Changes are now synchronized between all
'nodes.
Dim sampleSyncProvider As New SampleSyncProvider()
Dim sampleSyncAgent As SyncOrchestrator
Dim syncStatistics As SyncOperationStatistics
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync1))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
sampleSyncAgent = New SampleSyncAgent(sampleSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_DbSync1), _
sampleSyncProvider.ConfigureCeSyncProvider(Utility.ConnStr_SqlCeSync2))
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
Catch ex As DbOutdatedSyncException
Console.WriteLine(("Outdated Knowledge: " & ex.OutdatedPeerSyncKnowledge.ToString() & " Clean up knowledge: ") _
& ex.MissingCleanupKnowledge.ToString())
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
'Return data back to its original state.
Utility.CleanUpNode(Utility.ConnStr_DbSync1)
Utility.CleanUpNode(Utility.ConnStr_DbSync2)
'Exit.
Console.Write(vbLf & "Press Enter to close the window.")
Console.ReadLine()
End Sub
'Create a class that is derived from
'Microsoft.Synchronization.SyncOrchestrator.
Public Class SampleSyncAgent
Inherits SyncOrchestrator
Public Sub New(ByVal localProvider As RelationalSyncProvider, ByVal remoteProvider As RelationalSyncProvider)
Me.LocalProvider = localProvider
Me.RemoteProvider = remoteProvider
Me.Direction = SyncDirectionOrder.UploadAndDownload
'Check to see if any provider is a SqlCe provider and if it needs to
'be initialized.
CheckIfProviderNeedsSchema(TryCast(localProvider, SqlCeSyncProvider), _
TryCast(remoteProvider, DbSyncProvider))
CheckIfProviderNeedsSchema(TryCast(remoteProvider, SqlCeSyncProvider), _
TryCast(localProvider, DbSyncProvider))
End Sub
'For Compact databases that are not initialized with a snapshot,
'get the schema and initial data from a server database.
Private Sub CheckIfProviderNeedsSchema(ByVal providerToCheck As SqlCeSyncProvider, _
ByVal providerWithSchema As DbSyncProvider)
'If one of the providers is a SqlCeSyncProvider and it needs
'to be initialized, retrieve the schema from the other provider
'if that provider is a DbSyncProvider; otherwise configure a
'DbSyncProvider, connect to the server, and retrieve the schema.
If providerToCheck IsNot Nothing Then
Dim ceConfig As New SqlCeSyncScopeProvisioning()
Dim ceConn As SqlCeConnection = DirectCast(providerToCheck.Connection, SqlCeConnection)
Dim scopeName As String = providerToCheck.ScopeName
If Not ceConfig.ScopeExists(scopeName, ceConn) Then
Dim scopeDesc As DbSyncScopeDescription = providerWithSchema.GetScopeDescription()
ceConfig.PopulateFromScopeDescription(scopeDesc)
ceConfig.Apply(ceConn)
End If
End If
End Sub
End Class
Public Class SampleSyncProvider
Public Function ConfigureCeSyncProvider(ByVal sqlCeConnString As String) As SqlCeSyncProvider
Dim sampleCeProvider As New SqlCeSyncProvider()
'Set the scope name
sampleCeProvider.ScopeName = "Sales"
'Set the connection
sampleCeProvider.Connection = New SqlCeConnection(sqlCeConnString)
'Register event handlers
'Register the BeginSnapshotInitialization event handler.
'It is called when snapshot initialization is about to begin
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.BeginSnapshotInitialization, AddressOf sampleCeProvider_BeginSnapshotInitialization
'Register the EndSnapshotInitialization event handler.
'It is called when snapshot initialization has completed
'for a particular scope in a Compact database.
AddHandler sampleCeProvider.EndSnapshotInitialization, AddressOf sampleCeProvider_EndSnapshotInitialization
Return sampleCeProvider
End Function
Public Sub sampleCeProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
Console.WriteLine("Full initialization process started...")
Console.WriteLine(String.Format("CreatingSchema event fired for database {0}", e.Connection.Database))
End Sub
Public Sub sampleCeProvider_BeginSnapshotInitialization(ByVal sender As Object, ByVal e As DbBeginSnapshotInitializationEventArgs)
Console.WriteLine("")
Console.WriteLine("Snapshot initialization process started...")
Console.WriteLine(String.Format("BeginSnapshotInitialization event fired for scope {0}", e.ScopeName))
End Sub
Public Sub sampleCeProvider_EndSnapshotInitialization(ByVal sender As Object, ByVal e As DbEndSnapshotInitializationEventArgs)
Console.WriteLine("EndSnapshotInitialization event fired")
Dim tableStats As Dictionary(Of String, DbSnapshotInitializationTableStatistics) = e.TableInitializationStatistics
For Each tableName As String In tableStats.Keys
Dim ts As DbSnapshotInitializationTableStatistics = tableStats(tableName)
Console.WriteLine(vbTab & "Table Name: " & tableName)
Console.WriteLine(vbTab & "Total Rows: " & ts.TotalRows)
Console.WriteLine(vbTab & "Rows Intialized: " & ts.RowsInitialized)
Console.WriteLine(vbTab & "Start Time: " & ts.StartTime)
Console.WriteLine(vbTab & "End Time: " & ts.EndTime)
Next
Console.WriteLine("Snapshot initialization process completed")
Console.WriteLine("")
End Sub
Public Function ConfigureDbSyncProvider(ByVal peerConnString As String) As DbSyncProvider
Dim sampleDbProvider As New DbSyncProvider()
Dim peerConnection As New SqlConnection(peerConnString)
sampleDbProvider.Connection = peerConnection
sampleDbProvider.ScopeName = "Sales"
'Create a DbSyncAdapter object for the Customer table and associate it
'with the DbSyncProvider. Following the DataAdapter style in ADO.NET,
'DbSyncAdapter is the equivalent for synchronization. The commands that
'are specified for the DbSyncAdapter object call stored procedures
'that are created in each peer database.
Dim adapterCustomer As New DbSyncAdapter("Customer")
'Specify the primary key, which Sync Framework uses
'to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId")
'Specify the command to select incremental changes.
'In this command and other commands, session variables are
'used to pass information at runtime. DbSyncSession.SyncMetadataOnly
'and SyncMinTimestamp are two of the string constants that
'the DbSyncSession class exposes. You could also include
'@sync_metadata_only and @sync_min_timestamp directly in your
'queries:
'* sync_metadata_only is used by Sync Framework as an optimization
' in some queries.
'* The value of the sync_min_timestamp session variable is compared to
' values in the sync_row_timestamp column in the tracking table to
' determine which rows to select.
Dim chgsCustomerCmd As New SqlCommand()
With chgsCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectChanges"
.Parameters.Add("@" & DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncInitialize, SqlDbType.Int)
End With
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
'Specify the command to insert rows.
'The sync_row_count session variable is used in this command
'and other commands to return a count of the rows affected by an operation.
'A count of 0 indicates that an operation failed.
Dim insCustomerCmd As New SqlCommand()
With insCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyInsert"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.InsertCommand = insCustomerCmd
'Specify the command to update rows.
'The value of the sync_min_timestamp session variable is compared to
'values in the sync_row_timestamp column in the tracking table to
'determine which rows to update.
Dim updCustomerCmd As New SqlCommand()
With updCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyUpdate"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@CustomerName", SqlDbType.NVarChar)
.Parameters.Add("@SalesPerson", SqlDbType.NVarChar)
.Parameters.Add("@CustomerType", SqlDbType.NVarChar)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.UpdateCommand = updCustomerCmd
'Specify the command to delete rows.
'The value of the sync_min_timestamp session variable is compared to
'values in the sync_row_timestamp column in the tracking table to
'determine which rows to delete.
Dim delCustomerCmd As New SqlCommand()
With delCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_ApplyDelete"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
.Parameters.Add("@" & DbSyncSession.SyncForceWrite, SqlDbType.Int)
End With
adapterCustomer.DeleteCommand = delCustomerCmd
'Specify the command to select any conflicting rows.
Dim selRowCustomerCmd As New SqlCommand()
With selRowCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_SelectRow"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
End With
adapterCustomer.SelectRowCommand = selRowCustomerCmd
'Specify the command to insert metadata rows.
'The session variables in this command relate to columns in
'the tracking table.
Dim insMetadataCustomerCmd As New SqlCommand()
With insMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_InsertMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd
'Specify the command to update metadata rows.
Dim updMetadataCustomerCmd As New SqlCommand()
With updMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_UpdateMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeLocalId, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowIsTombstone, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
'Specify the command to delete metadata rows.
Dim delMetadataCustomerCmd As New SqlCommand()
With delMetadataCustomerCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "Sync.sp_Customer_DeleteMetadata"
.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd
'Add the adapter to the provider.
sampleDbProvider.SyncAdapters.Add(adapterCustomer)
' Configure commands that relate to the provider itself rather
' than the DbSyncAdapter object for each table:
' * SelectNewTimestampCommand: Returns the new high watermark for
' the current synchronization session.
' * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge,
' and a scope version (timestamp).
' * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.
' * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table
' or tracking table, to determine whether for each table the destination already
' has all of the changes from the source. If a destination table has all the changes,
' SelectIncrementalChangesCommand is not called for that table.
' There are additional commands related to metadata cleanup that are not
' included in this application.
'Select a new timestamp.
'During each synchronization, the new value and
'the last value from the previous synchronization
'are used: the set of changes between these upper and
'lower bounds is synchronized.
Dim selectNewTimestampCommand As New SqlCommand()
Dim newTimestampVariable As String = "@" & DbSyncSession.SyncNewTimestamp
With selectNewTimestampCommand
.CommandText = "SELECT " & newTimestampVariable & " = min_active_rowversion() - 1"
.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
End With
sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand
'Specify the command to select local replica metadata.
Dim selReplicaInfoCmd As New SqlCommand()
With selReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "SELECT " _
& "scope_id, " _
& "scope_local_id, " _
& "scope_sync_knowledge, " _
& "scope_tombstone_cleanup_knowledge, " _
& "scope_timestamp " _
& "FROM Sync.ScopeInfo " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName
.Parameters.Add("@" & DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
End With
sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd
'Specify the command to update local replica metadata.
Dim updReplicaInfoCmd As New SqlCommand()
With updReplicaInfoCmd
.CommandType = CommandType.Text
.CommandText = "UPDATE Sync.ScopeInfo SET " _
& "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
& "scope_id = @" + DbSyncSession.SyncScopeId + ", " _
& "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " _
& " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " _
& "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount"
.Parameters.Add("@" & DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" & DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000)
.Parameters.Add("@" & DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
.Parameters.Add("@" & DbSyncSession.SyncCheckConcurrency, SqlDbType.Int)
.Parameters.Add("@" & DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier)
.Parameters.Add("@" & DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt)
.Parameters.Add("@" & DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
End With
sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd
'Return the maximum timestamp from the Customer_Tracking table.
'If more tables are synchronized, the query should UNION
'all of the results. The table name is not schema-qualified
'in this case because the name was not schema qualified in the
'DbSyncAdapter constructor.
Dim selTableMaxTsCmd As New SqlCommand()
selTableMaxTsCmd.CommandType = CommandType.Text
selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " _
& "MAX(local_update_peer_timestamp) AS max_timestamp " _
& "FROM Sync.Customer_Tracking"
sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd
Return sampleDbProvider
End Function
End Class
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncOperationStatistics, ByVal syncType As String)
Console.WriteLine([String].Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.UploadChangesTotal)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.DownloadChangesTotal)
Console.WriteLine("Complete Time: " & syncStatistics.SyncEndTime)
Console.WriteLine([String].Empty)
End Sub
End Class
End Class
Vea también
Conceptos
Aprovisionar una base de datos servidor para la sincronización de colaboración (no SQL Server)
Sincronizar otras bases de datos compatibles con ADO.NET