Cómo configurar la sincronización punto a punto en arquitecturas n-tier
En este tema se describe cómo configurar la sincronización punto a punto en arquitecturas n-tier para Sync Services for ADO.NET. Los ejemplos de este tema se centran en los siguientes tipos de Sync Services:
Las clases SyncOrchestrator y KnowledgeSyncProvider se documentan en la documentación básica de Sync Framework en este sitio web de Microsoft.
Introducción a la sincronización n-tier
En una arquitectura de sincronización n-tier, los componentes de sincronización se reparten en varios niveles, según se muestra en la ilustración siguiente.
.gif "Topología de sincronización de elementos del mismo nivel de n-tier (n niveles)")
El elemento del mismo nivel local es aquél desde el que se inicia la sincronización. Si desea comenzar la sincronización desde más de un elemento del mismo nivel, cada elemento del mismo nivel debe contener todos los componentes que se muestran en la ilustración. Para comenzar la sincronización desde el elemento del mismo nivel local, siga estos pasos:
Cree una instancia de un proveedor en el elemento del mismo nivel local. Este proveedor tiene una conexión directa con la base de datos en el elemento del mismo nivel local.
Llame al proxy en el elemento del mismo nivel local. A diferencia de la sincronización entre cliente y servidor, el proxy no forma parte de la API de Sync Services. Debe escribir un proxy que derive de KnowledgeSyncProvider. Este proxy comunica con un servicio como Windows Communication Foundation (WCF) en el elemento del mismo nivel remoto. Este servicio crea a su vez una instancia del proveedor remoto. Este proveedor tiene una conexión directa con la base de datos en el elemento del mismo nivel remoto.
Especifique los proveedores local y remoto para las propiedades LocalProvider y RemoteProvider de SyncOrchestrator.
Llame al método SyncOrchestratorSynchronize para sincronizar los dos elementos del mismo nivel.
Ejemplo
En los ejemplos de código siguientes se muestran los componentes principales implicados en una arquitectura n-tier. Se requieren componentes de WCF adicionales. Estos componentes se generan automáticamente cuando se utiliza Visual Studio 2008. Para obtener más información, vea la documentación de Visual Studio.
Partes principales de la API
En esta sección se proporcionan ejemplos de código que muestran las partes principales de la API que se usa cuando se configura una sincronización n-tier. En el ejemplo de código siguiente primero se crean instancias de dos objetos KnowledgeSyncProvider. El proveedor local se crea utilizando el método SetupSyncProvider. Este es el mismo método que se utiliza en otros temas para las aplicaciones de dos niveles. El proveedor remoto se crea utilizando la clase SyncProxy. Esto se muestra en el ejemplo de código siguiente. El ámbito es una agrupación lógica de tablas que se pasa a cada proveedor. Una vez creados los proveedores, se crea una instancia del objeto SyncOrchestrator, se establece la dirección de sincronización y se llama al método Synchronize.
Nota
El objeto KnowledgeSyncProvider se utiliza para los proveedores en lugar de DbSyncProvider porque la clase SyncProxy deriva de KnowledgeSyncProvider.
KnowledgeSyncProvider localProvider;
KnowledgeSyncProvider remoteProvider;
string localConnection = @"Data Source = localhost; Initial Catalog = SyncSamplesDb_Peer1; " +
"Integrated Security = True";
string remoteConnection = @"https://localhost:8000/Sync/SyncService";
string scopeName = "Sales";
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
localProvider = sampleSyncProvider.SetupSyncProvider(scopeName, localConnection);
remoteProvider = new SyncProxy(scopeName, remoteConnection);
SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
syncOrchestrator.LocalProvider = localProvider;
syncOrchestrator.RemoteProvider = remoteProvider;
syncOrchestrator.Direction = SyncDirectionOrder.Download;
syncOrchestrator.Synchronize();
Dim localProvider As KnowledgeSyncProvider
Dim remoteProvider As KnowledgeSyncProvider
Dim localConnection As String = "Data Source = localhost; Initial Catalog = SyncSamplesDb_Peer1; " _
& "Integrated Security = True"
Dim remoteConnection As String = "https://localhost:8000/Sync/SyncService"
Dim scopeName As String = "Sales"
Dim sampleSyncProvider As New SampleSyncProvider()
localProvider = sampleSyncProvider.SetupSyncProvider(scopeName, localConnection)
remoteProvider = New SyncProxy(scopeName, remoteConnection)
Dim syncOrchestrator As New SyncOrchestrator()
syncOrchestrator.LocalProvider = localProvider
syncOrchestrator.RemoteProvider = remoteProvider
syncOrchestrator.Direction = SyncDirectionOrder.Download
syncOrchestrator.Synchronize()
En el ejemplo de código siguiente se crea una clase de proxy que deriva de KnowledgeSyncProvider. Esta clase crea un canal al servicio SyncService, que se muestra en el ejemplo de código siguiente. La clase implementa una propiedad IdFormats porque DbSyncProvider la necesita y KnowledgeSyncProvider no lo implementa. La implementación en la clase SyncProxy coincide con la implementación de DbSyncProvider. La clase SyncProxy también contiene varios métodos que llaman a métodos en el servicio SyncService a través de HTTP. Estos métodos administran los datos y los metadatos que Sync Services envía entre los elementos del mismo nivel local y remoto. En el método ProcessChangeBatch, remoteSessionStatistics se pasa por referencia para que los valores se puedan establecer en el elemento del mismo nivel remoto y devolver al elemento del mismo nivel local.
public class SyncProxy : KnowledgeSyncProvider
{
public SyncProxy(string scopeName, string url)
{
this.scopeName = scopeName;
WSHttpBinding binding = new WSHttpBinding();
binding.ReceiveTimeout = new TimeSpan(0, 10, 0);
binding.OpenTimeout = new TimeSpan(0, 1, 0);
ChannelFactory<ISyncContract> factory = new ChannelFactory<ISyncContract>(binding,
new EndpointAddress(url));
proxy = factory.CreateChannel();
}
//The IdFormat settings for the peer provider.
public override SyncIdFormatGroup IdFormats
{
get
{
if (idFormatGroup == null)
{
idFormatGroup = new SyncIdFormatGroup();
//
// 1 byte change unit ID
//
idFormatGroup.ChangeUnitIdFormat.IsVariableLength = false;
idFormatGroup.ChangeUnitIdFormat.Length = 1;
//
// GUID replica ID
//
idFormatGroup.ReplicaIdFormat.IsVariableLength = false;
idFormatGroup.ReplicaIdFormat.Length = 16;
//
// Global ID for item IDs
//
idFormatGroup.ItemIdFormat.IsVariableLength = true;
idFormatGroup.ItemIdFormat.Length = 10 * 1024;
}
return idFormatGroup;
}
}
public override void BeginSession(SyncProviderPosition position, SyncSessionContext syncSessionContext)
{
proxy.BeginSession(scopeName);
}
public override void EndSession(SyncSessionContext syncSessionContext)
{
proxy.EndSession();
}
public override void GetSyncBatchParameters(out uint batchSize, out SyncKnowledge knowledge)
{
proxy.GetKnowledge(out batchSize, out knowledge);
}
public override ChangeBatch GetChangeBatch(uint batchSize, SyncKnowledge destinationKnowledge,
out object changeDataRetriever)
{
return proxy.GetChanges(batchSize, destinationKnowledge, out changeDataRetriever);
}
public override void ProcessChangeBatch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges,
object changeDataRetriever, SyncCallbacks syncCallback, SyncSessionStatistics sessionStatistics)
{
SyncSessionStatistics remoteSessionStatistics = new SyncSessionStatistics();
proxy.ApplyChanges(resolutionPolicy, sourceChanges, changeDataRetriever, ref remoteSessionStatistics);
sessionStatistics.ChangesApplied = remoteSessionStatistics.ChangesApplied;
sessionStatistics.ChangesFailed = remoteSessionStatistics.ChangesFailed;
}
public override FullEnumerationChangeBatch GetFullEnumerationChangeBatch(uint batchSize, SyncId lowerEnumerationBound,
SyncKnowledge knowledgeForDataRetrieval, out object changeDataRetriever)
{
//Do nothing.
//
throw new NotImplementedException();
}
public override void ProcessFullEnumerationChangeBatch(ConflictResolutionPolicy resolutionPolicy,
FullEnumerationChangeBatch sourceChanges, object changeDataRetriever, SyncCallbacks syncCallback,
SyncSessionStatistics sessionStatistics)
{
//Do nothing.
//
throw new NotImplementedException();
}
private string scopeName;
private ISyncContract proxy;
private SyncIdFormatGroup idFormatGroup = null;
}
Public Class SyncProxy
Inherits KnowledgeSyncProvider
Public Sub New(ByVal scopeName As String, ByVal url As String)
Me.scopeName = scopeName
Dim binding As New WSHttpBinding()
binding.ReceiveTimeout = New TimeSpan(0, 10, 0)
binding.OpenTimeout = New TimeSpan(0, 1, 0)
'Dim factory As New ChannelFactory(< ISyncContract > binding, New EndpointAddress(url))
'proxy = factory.CreateChannel()
End Sub 'New
'The IdFormat settings for the peer provider.
Public Overrides ReadOnly Property IdFormats() As SyncIdFormatGroup
Get
If idFormatGroup Is Nothing Then
idFormatGroup = New SyncIdFormatGroup()
'
' 1 byte change unit ID
'
idFormatGroup.ChangeUnitIdFormat.IsVariableLength = False
idFormatGroup.ChangeUnitIdFormat.Length = 1
'
' GUID replica ID
'
idFormatGroup.ReplicaIdFormat.IsVariableLength = False
idFormatGroup.ReplicaIdFormat.Length = 16
'
' Global ID for item IDs
'
idFormatGroup.ItemIdFormat.IsVariableLength = True
idFormatGroup.ItemIdFormat.Length = 10 * 1024
End If
Return idFormatGroup
End Get
End Property
Public Overrides Sub BeginSession(ByVal position As SyncProviderPosition, ByVal syncSessionContext As SyncSessionContext)
proxy.BeginSession(scopeName)
End Sub
Public Overrides Sub EndSession(ByVal syncSessionContext As SyncSessionContext)
proxy.EndSession()
End Sub
Public Overrides Sub GetSyncBatchParameters(ByRef batchSize As System.UInt32, ByRef knowledge As SyncKnowledge) 'ToDo: Unsigned Integers not supported
proxy.GetKnowledge(batchSize, knowledge)
End Sub
Public Overrides Function GetChangeBatch(ByVal batchSize As System.UInt32, ByVal destinationKnowledge As SyncKnowledge, ByRef changeDataRetriever As Object) As ChangeBatch 'ToDo: Unsigned Integers not supported
Return proxy.GetChanges(batchSize, destinationKnowledge, changeDataRetriever)
End Function
Public Overrides Sub ProcessChangeBatch(ByVal resolutionPolicy As ConflictResolutionPolicy, ByVal sourceChanges As ChangeBatch, ByVal changeDataRetriever As Object, ByVal syncCallback As SyncCallbacks, ByVal sessionStatistics As SyncSessionStatistics)
Dim remoteSessionStatistics As New SyncSessionStatistics()
proxy.ApplyChanges(resolutionPolicy, sourceChanges, changeDataRetriever, remoteSessionStatistics)
sessionStatistics.ChangesApplied = remoteSessionStatistics.ChangesApplied
sessionStatistics.ChangesFailed = remoteSessionStatistics.ChangesFailed
End Sub
Public Overrides Function GetFullEnumerationChangeBatch(ByVal batchSize As System.UInt32, ByVal lowerEnumerationBound As SyncId, ByVal knowledgeForDataRetrieval As SyncKnowledge, ByRef changeDataRetriever As Object) As FullEnumerationChangeBatch 'ToDo: Unsigned Integers not supported
'Do nothing.
'
Throw New NotImplementedException()
End Function
Public Overrides Sub ProcessFullEnumerationChangeBatch(ByVal resolutionPolicy As ConflictResolutionPolicy, ByVal sourceChanges As FullEnumerationChangeBatch, ByVal changeDataRetriever As Object, ByVal syncCallback As SyncCallbacks, ByVal sessionStatistics As SyncSessionStatistics)
'Do nothing.
'
Throw New NotImplementedException()
End Sub
Private scopeName As String
Private proxy As ISyncContract
Private idFormatGroup As SyncIdFormatGroup = Nothing
End Class 'SyncProxy
El ejemplo de código siguiente crea en el elemento del mismo nivel remoto el servicio con el que SyncProxy se comunica. SyncService implementa ISyncContract, que se muestra en el ejemplo de código siguiente. En el método BeginSession, el servicio crea un DbSyncProvider llamando al mismo método al que el elemento del mismo nivel local llamó, pero esta vez desde el servicio en el elemento del mismo nivel remoto. Los demás métodos son llamados por SyncProxy y SyncService los traduce en llamadas al objeto DbSyncProvider que se creó en BeginSession. Los métodos GetFullEnumerationChangeBatch y ProcessFullEnumerationChangeBatch no son utilizados por DbSyncprovider. Por lo tanto, estos métodos no se implementan. Puede implementar estos métodos si su aplicación los necesita. Para obtener más información sobre estos métodos, vea la documentación básica de Sync Framework. Para descargar esta documentación, visite este sitio web de Microsoft.
[ServiceBehavior(IncludeExceptionDetailInFaults = true)]
public class SyncService : ISyncContract
{
public void BeginSession(string scopeName)
{
string localConnection = @"Data Source = localhost; Initial Catalog = SyncSamplesDb_Peer2; " +
"Integrated Security = True";
SampleSyncProvider sampleSyncProvider = new SampleSyncProvider();
peerProvider = sampleSyncProvider.SetupSyncProvider(scopeName, localConnection);
}
public void GetKnowledge(
out uint batchSize,
out SyncKnowledge knowledge)
{
peerProvider.GetSyncBatchParameters(out batchSize, out knowledge);
}
public ChangeBatch GetChanges(
uint batchSize,
SyncKnowledge destinationKnowledge,
out object changeData)
{
return peerProvider.GetChangeBatch(batchSize, destinationKnowledge, out changeData);
}
public void ApplyChanges(
ConflictResolutionPolicy resolutionPolicy,
ChangeBatch sourceChanges,
object changeData,
ref SyncSessionStatistics sessionStatistics)
{
SyncCallbacks syncCallback = new SyncCallbacks();
peerProvider.ProcessChangeBatch(resolutionPolicy, sourceChanges, changeData, syncCallback,
sessionStatistics);
}
public void EndSession()
{
peerProvider = null;
}
private DbSyncProvider peerProvider = null;
}
<ServiceBehavior(IncludeExceptionDetailInFaults:=True)> _
Public Class SyncService
Implements ISyncContract
Public Sub BeginSession(ByVal scopeName As String) Implements ISyncContract.BeginSession
Dim localConnection As String = "Data Source = localhost; Initial Catalog = SyncSamplesDb_Peer2; " _
& "Integrated Security = True"
Dim sampleSyncProvider As New SampleSyncProvider()
peerProvider = sampleSyncProvider.SetupSyncProvider(scopeName, localConnection)
End Sub
Public Sub GetKnowledge(ByRef batchSize As System.UInt32, ByRef knowledge As SyncKnowledge) Implements ISyncContract.GetKnowledge
peerProvider.GetSyncBatchParameters(batchSize, knowledge)
End Sub
Public Function GetChanges(ByVal batchSize As System.UInt32, ByVal destinationKnowledge As SyncKnowledge, ByRef changeData As Object) As ChangeBatch Implements ISyncContract.GetChanges
Return peerProvider.GetChangeBatch(batchSize, destinationKnowledge, changeData)
End Function
Public Sub ApplyChanges(ByVal resolutionPolicy As ConflictResolutionPolicy, ByVal sourceChanges As ChangeBatch, ByVal changeData As Object, ByRef sessionStatistics As SyncSessionStatistics) Implements ISyncContract.ApplyChanges
Dim syncCallback As New SyncCallbacks()
peerProvider.ProcessChangeBatch(resolutionPolicy, sourceChanges, changeData, syncCallback, sessionStatistics)
End Sub
Public Sub EndSession() Implements ISyncContract.EndSession
peerProvider = Nothing
End Sub
Private peerProvider As DbSyncProvider = Nothing
End Class
En el ejemplo de código siguiente se muestra la interfaz ISyncContract. Esta interfaz define los métodos que se requieren para la sincronización punto a punto, incluidos los métodos para iniciar y finalizar la sesión de un servicio. No se admite la sincronización sin estado. Por lo tanto, se requieren los métodos EndSession y BeginSession.
[ServiceContract(SessionMode = SessionMode.Required)]
[ServiceKnownType(typeof(SyncIdFormatGroup))]
[ServiceKnownType(typeof(DbSyncContext))]
public interface ISyncContract
{
[OperationContract(IsInitiating = true, IsTerminating = false)]
void BeginSession(string scopeName);
[OperationContract(IsInitiating = false, IsTerminating = false)]
void GetKnowledge(out uint batchSize, out SyncKnowledge knowledge);
[OperationContract(IsInitiating = false, IsTerminating = false)]
ChangeBatch GetChanges(uint batchSize, SyncKnowledge destinationKnowledge, out object changeData);
[OperationContract(IsInitiating = false, IsTerminating = false)]
void ApplyChanges(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, object changeData,
ref SyncSessionStatistics sessionStatistics);
[OperationContract(IsInitiating = false, IsTerminating = true)]
void EndSession();
}
<ServiceContract(SessionMode:=SessionMode.Required), _
ServiceKnownType(GetType(SyncIdFormatGroup)), _
ServiceKnownType(GetType(DbSyncContext))> _
Public Interface ISyncContract
<OperationContract(IsInitiating:=True, IsTerminating:=False)> _
Sub BeginSession(ByVal scopeName As String)
<OperationContract(IsInitiating:=False, IsTerminating:=False)> _
Sub GetKnowledge(ByRef batchSize As System.UInt32, ByRef knowledge As SyncKnowledge) 'ToDo: Unsigned Integers not supported
<OperationContract(IsInitiating:=False, IsTerminating:=False)> _
Function GetChanges(ByVal batchSize As System.UInt32, ByVal destinationKnowledge As SyncKnowledge, ByRef changeData As Object) As ChangeBatch 'ToDo: Unsigned Integers not supported
<OperationContract(IsInitiating:=False, IsTerminating:=False)> _
Sub ApplyChanges(ByVal resolutionPolicy As ConflictResolutionPolicy, ByVal sourceChanges As ChangeBatch, ByVal changeData As Object, ByRef sessionStatistics As SyncSessionStatistics)
<OperationContract(IsInitiating:=False, IsTerminating:=True)> _
Sub EndSession()
End Interface
En el ejemplo de código siguiente se muestra la clase SampleSyncProvider. Esta clase se puede utilizar en aplicaciones de dos niveles y n-tier. Este método SetupSyncProvider crea un proveedor, un adaptador para la tabla Customers y todos los comandos necesarios para seleccionar y aplicar datos y metadatos en las bases de datos del mismo nivel. Para obtener más información sobre esta clase, vea Cómo configurar el seguimiento de cambios y sincronizar los elementos del mismo nivel.
public class SampleSyncProvider
{
public DbSyncProvider SetupSyncProvider(string scopeName, string peerConnString)
{
const int MetadataAgingInHours = 100;
DbSyncProvider peerProvider = new DbSyncProvider();
SqlConnection peerConnection = new SqlConnection(peerConnString);
peerProvider.Connection = peerConnection;
peerProvider.ScopeName = scopeName;
DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer");
adapterCustomer.RowIdColumns.Add("CustomerId");
SqlCommand chgsCustomerCmd = new SqlCommand();
chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
chgsCustomerCmd.CommandText = "Sales.sp_Customer_SelectChanges";
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int);
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;
SqlCommand insCustomerCmd = new SqlCommand();
insCustomerCmd.CommandType = CommandType.StoredProcedure;
insCustomerCmd.CommandText = "Sales.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;
SqlCommand updCustomerCmd = new SqlCommand();
updCustomerCmd.CommandType = CommandType.StoredProcedure;
updCustomerCmd.CommandText = "Sales.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;
SqlCommand delCustomerCmd = new SqlCommand();
delCustomerCmd.CommandType = CommandType.StoredProcedure;
delCustomerCmd.CommandText = "Sales.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;
adapterCustomer.DeleteCommand = delCustomerCmd;
SqlCommand selRowCustomerCmd = new SqlCommand();
selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
selRowCustomerCmd.CommandText = "Sales.sp_Customer_SelectRow";
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
adapterCustomer.SelectRowCommand = selRowCustomerCmd;
SqlCommand insMetadataCustomerCmd = new SqlCommand();
insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
insMetadataCustomerCmd.CommandText = "Sales.sp_Customer_InsertMetadata";
insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
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.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd;
SqlCommand updMetadataCustomerCmd = new SqlCommand();
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
updMetadataCustomerCmd.CommandText = "Sales.sp_Customer_UpdateMetadata";
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
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.SyncCheckConcurrency, SqlDbType.Int);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;
SqlCommand delMetadataCustomerCmd = new SqlCommand();
delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
delMetadataCustomerCmd.CommandText = "Sales.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;
SqlCommand selMetadataCustomerCmd = new SqlCommand();
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
selMetadataCustomerCmd.CommandText = "Sales.sp_Customer_SelectMetadata";
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours;
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;
peerProvider.SyncAdapters.Add(adapterCustomer);
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;
peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
SqlCommand selReplicaInfoCmd = new SqlCommand();
selReplicaInfoCmd.CommandType = CommandType.Text;
selReplicaInfoCmd.CommandText = "SELECT " +
"@" + DbSyncSession.SyncScopeId + " = scope_id, " +
"@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " +
"@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " +
"@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " +
"FROM Sales.ScopeInfo " +
"WHERE scope_name = @" + DbSyncSession.SyncScopeName;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output;
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output;
peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd;
SqlCommand updReplicaInfoCmd = new SqlCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "UPDATE Sales.ScopeInfo SET " +
"scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
"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.SyncScopeTimestamp, SqlDbType.BigInt);
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
return peerProvider;
}
}
Public Class SampleSyncProvider
Public Function SetupSyncProvider(ByVal scopeName As String, ByVal peerConnString As String) As DbSyncProvider
Const MetadataAgingInHours As Integer = 100
Dim peerProvider As New DbSyncProvider()
Dim peerConnection As New SqlConnection(peerConnString)
peerProvider.Connection = peerConnection
peerProvider.ScopeName = scopeName
Dim adapterCustomer As New DbSyncAdapter("Customer")
adapterCustomer.RowIdColumns.Add("CustomerId")
Dim chgsCustomerCmd As New SqlCommand()
chgsCustomerCmd.CommandType = CommandType.StoredProcedure
chgsCustomerCmd.CommandText = "Sales.sp_Customer_SelectChanges"
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int)
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt)
chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncInitialize, SqlDbType.Int)
adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd
Dim insCustomerCmd As New SqlCommand()
insCustomerCmd.CommandType = CommandType.StoredProcedure
insCustomerCmd.CommandText = "Sales.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
Dim updCustomerCmd As New SqlCommand()
updCustomerCmd.CommandType = CommandType.StoredProcedure
updCustomerCmd.CommandText = "Sales.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 delCustomerCmd As New SqlCommand()
delCustomerCmd.CommandType = CommandType.StoredProcedure
delCustomerCmd.CommandText = "Sales.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
adapterCustomer.DeleteCommand = delCustomerCmd
Dim selRowCustomerCmd As New SqlCommand()
selRowCustomerCmd.CommandType = CommandType.StoredProcedure
selRowCustomerCmd.CommandText = "Sales.sp_Customer_SelectRow"
selRowCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
adapterCustomer.SelectRowCommand = selRowCustomerCmd
Dim insMetadataCustomerCmd As New SqlCommand()
insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
insMetadataCustomerCmd.CommandText = "Sales.sp_Customer_InsertMetadata"
insMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
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.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd
Dim updMetadataCustomerCmd As New SqlCommand()
updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
updMetadataCustomerCmd.CommandText = "Sales.sp_Customer_UpdateMetadata"
updMetadataCustomerCmd.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier)
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.SyncCheckConcurrency, SqlDbType.Int)
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt)
updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd
Dim delMetadataCustomerCmd As New SqlCommand()
delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
delMetadataCustomerCmd.CommandText = "Sales.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
Dim selMetadataCustomerCmd As New SqlCommand()
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure
selMetadataCustomerCmd.CommandText = "Sales.sp_Customer_SelectMetadata"
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_hours", SqlDbType.Int).Value = MetadataAgingInHours
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd
peerProvider.SyncAdapters.Add(adapterCustomer)
Dim selectNewTimestampCommand As New SqlCommand()
Dim newTimestampVariable As String = "@" + DbSyncSession.SyncNewTimestamp
selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1"
selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp)
selectNewTimestampCommand.Parameters(newTimestampVariable).Direction = ParameterDirection.Output
peerProvider.SelectNewTimestampCommand = selectNewTimestampCommand
Dim selReplicaInfoCmd As New SqlCommand()
selReplicaInfoCmd.CommandType = CommandType.Text
selReplicaInfoCmd.CommandText = "SELECT " _
& "@" + DbSyncSession.SyncScopeId + " = scope_id, " _
& "@" + DbSyncSession.SyncScopeKnowledge + " = scope_sync_knowledge, " _
& "@" + DbSyncSession.SyncScopeCleanupKnowledge + " = scope_tombstone_cleanup_knowledge, " _
& "@" + DbSyncSession.SyncScopeTimestamp + " = scope_timestamp " _
& "FROM Sales.ScopeInfo " _
& "WHERE scope_name = @" + DbSyncSession.SyncScopeName
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100)
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000).Direction = ParameterDirection.Output
selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output
peerProvider.SelectScopeInfoCommand = selReplicaInfoCmd
Dim updReplicaInfoCmd As New SqlCommand()
updReplicaInfoCmd.CommandType = CommandType.Text
updReplicaInfoCmd.CommandText = "UPDATE Sales.ScopeInfo SET " _
& "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " _
& "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.SyncScopeTimestamp, SqlDbType.BigInt)
updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output
peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd
Return peerProvider
End Function 'SetupSyncProvider
End Class 'SampleSyncProvider