How to: Configure N-Tier Peer-to-Peer Synchronization
This topic describes how to configure N-tier peer-to-peer synchronization for Sync Services for ADO.NET 2.0. The examples in this topic focus on the following Sync Services types:
The KnowledgeSyncProvider and SyncOrchestrator classes are documented in the Sync Framework core documentation at this Microsoft Web site.
Understanding N-Tier Synchronization
In an N-tier synchronization architecture, synchronization components are spread across multiple tiers, as shown in the following illustration.
.gif "N-tier peer synchronization topology")
The local peer is the peer from which synchronization is initiated. If you want to begin synchronization from more than one peer, each peer must contain all the components that are shown in the illustration. To begin synchronization from the local peer, follow these steps:
Instantiate a provider on the local peer. This provider has a direct connection to the database on the local peer.
Call the proxy on the local peer. Unlike client and server synchronization, the proxy is not part of the Sync Services API. You must write a proxy that derives from KnowledgeSyncProvider. This proxy communicates with a service such as Windows Communication Foundation (WCF) on the remote peer. This service in turn instantiates the remote provider. This provider has a direct connection to the database on the remote peer.
Specify the local and remote providers for the SyncOrchestrator LocalProvider and RemoteProvider properties.
Call the SyncOrchestrator Synchronize method to synchronize the two peers.
Example
The following code examples show the main components that are involved in an N-tier architecture. Additional WCF components are required. These components are generated for you if you use Visual Studio 2008. For more information, see the Visual Studio documentation.
Key Parts of the API
This section provides code examples that show the key parts of the API to use when you are configuring N-tier synchronization. The following code example first instantiates two KnowledgeSyncProvider objects. The local provider is created by using the SetupSyncProvider method. This is the same method that is used in other topics for two-tier applications. The remote provider is created by using the SyncProxy class. This is shown in the following code example. The scope is a logical grouping of tables that is passed to each provider. After the providers are created, a SyncOrchestrator object is instantiated, the synchronization direction is set, and the Synchronize method is called.
Note
The KnowledgeSyncProvider object is used for the providers instead of DbSyncProvider because the SyncProxy class derives from 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()
The following code example creates a proxy class that derives from KnowledgeSyncProvider. This class creates a channel to the SyncService service, which is shown in the following code example. The class implements an IdFormats property because DbSyncProvider requires it and KnowledgeSyncProvider does not implement it. The implementation in the SyncProxy class matches the DbSyncProvider implementation. The SyncProxy class also contains several methods that call methods on the SyncService service over HTTP. These methods handle the data and metadata that Sync Services sends between the local and remote peers. In the ProcessChangeBatch method, remoteSessionStatistics is passed by reference so that values can be set on the remote peer and returned back to the local peer.
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
The following code example creates on the remote peer the service with which the SyncProxy communicates. SyncService implements ISyncContract, which is shown in the following code example. In the BeginSession method, the service creates a DbSyncProvider by calling the same method that the local peer called, but this time from the service at the remote peer. The other methods are called by SyncProxy, and SyncService translates these into calls to the DbSyncProvider object that was created in BeginSession. The GetFullEnumerationChangeBatch and ProcessFullEnumerationChangeBatch methods are not used by DbSyncprovider. Therefore, these methods are not implemented. You can implement these methods if your application requires them. For more information about these methods, see the Sync Framework core documentation. To download this documentation, go to this Microsoft Web site.
[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
The following code example shows the ISyncContract interface. This interface defines the methods that are required for peer-to-peer synchronization, including the methods to begin and end a service session. Stateless synchronization is not supported. Therefore, BeginSession and EndSession methods are required.
[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
The following code example shows the SampleSyncProvider class. This class can be used in two-tier and N-tier applications. This SetupSyncProvider method creates a provider, an adapter for the Customers table, and all the required commands to select and apply data and metadata to and from the peer databases. For more information about this class, see How to: Configure Change Tracking and Synchronize Peers.
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