public RelationalProviderProxy(string scopeName, string hostName)
{
this.scopeName = scopeName;
this.hostName = hostName;
this.CreateProxy(hostName);
this.proxy.Initialize(scopeName, hostName);
DbSyncAdapter dbSyncAdapter = new DbSyncAdapter();
}
/// <summary>
/// Create a new BatchInfo, containing all BatchPartInfo
/// </summary>
public BatchInfo(SyncSet inSchema, string rootDirectory = null, string directoryName = null)
{
// We need to create a change table set, containing table with columns not readonly
foreach (var table in inSchema.Tables)
{
DbSyncAdapter.CreateChangesTable(inSchema.Tables[table.TableName, table.SchemaName], this.SanitizedSchema);
}
this.DirectoryRoot = rootDirectory;
this.BatchPartsInfo = new List <BatchPartInfo>();
this.DirectoryName = string.IsNullOrEmpty(directoryName) ? string.Concat(DateTime.UtcNow.ToString("yyyy_MM_dd_ss"), Path.GetRandomFileName().Replace(".", "")) : directoryName;
}
public static DbSyncAdapter CreateSyncAdpters()
{
DbSyncAdapter syncAdapter = new DbSyncAdapter("cards");
syncAdapter.RowIdColumns.Add("card_id");
syncAdapter.SelectIncrementalChangesCommand = SyncAdapterCommandHelper.GetSelectIncrementalChangesCommand();
syncAdapter.InsertCommand = SyncAdapterCommandHelper.GetInsertCommand();
syncAdapter.UpdateCommand = SyncAdapterCommandHelper.GetUpdateCommand();
syncAdapter.DeleteCommand = SyncAdapterCommandHelper.GetDeleteCommand();
syncAdapter.SelectRowCommand = SyncAdapterCommandHelper.GetSelectRowCommand();
syncAdapter.InsertMetadataCommand = SyncAdapterCommandHelper.GetInsertMetadataCommand();
syncAdapter.UpdateMetadataCommand = SyncAdapterCommandHelper.GetUpdateMetadataCommand();
syncAdapter.DeleteMetadataCommand = SyncAdapterCommandHelper.GetDeleteMetadataCommand();
syncAdapter.SelectMetadataForCleanupCommand = SyncAdapterCommandHelper.GetSelectMetadataForCleanupCommand(100);
return(syncAdapter);
}
DbSyncProvider SetupSyncProvider(string connectionString, DbSyncProvider peerProvider)
{
const int TombstoneAgingInHours = 100;
SqlConnection connection = new SqlConnection(connectionString);
peerProvider.Connection = connection;
//
// 1. Create sync adapter for each sync table and attach it to the provider
// Following DataAdapter style in ADO.NET, DbSyncAdapter is the equivelent for sync.
// The code below shows how to create DbSyncAdapter objects for orders
// and order_details tables using stored procedures stored on the database.
//
peerProvider.ScopeName = "Sales";
// orders table
DbSyncAdapter adaptorOrders = new DbSyncAdapter("orders");
adaptorOrders.RowIdColumns.Add("order_id");
// select incremental changes command
SqlCommand chgsOrdersCmd = new SqlCommand();
chgsOrdersCmd.CommandType = CommandType.StoredProcedure;
chgsOrdersCmd.CommandText = "sp_orders_selectchanges";
chgsOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
adaptorOrders.SelectIncrementalChangesCommand = chgsOrdersCmd;
// insert row command
SqlCommand insOrdersCmd = new SqlCommand();
insOrdersCmd.CommandType = CommandType.StoredProcedure;
insOrdersCmd.CommandText = "sp_orders_applyinsert";
insOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
insOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime);
insOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.InsertCommand = insOrdersCmd;
// update row command
SqlCommand updOrdersCmd = new SqlCommand();
updOrdersCmd.CommandType = CommandType.StoredProcedure;
updOrdersCmd.CommandText = "sp_orders_applyupdate";
updOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
updOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime);
updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.UpdateCommand = updOrdersCmd;
// delete row command
SqlCommand delOrdersCmd = new SqlCommand();
delOrdersCmd.CommandType = CommandType.StoredProcedure;
delOrdersCmd.CommandText = "sp_orders_applydelete";
delOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
delOrdersCmd.Parameters.Add("@sync_force_write", SqlDbType.Int);
delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.DeleteCommand = delOrdersCmd;
// get row command
SqlCommand selRowOrdersCmd = new SqlCommand();
selRowOrdersCmd.CommandType = CommandType.StoredProcedure;
selRowOrdersCmd.CommandText = "sp_orders_selectrow";
selRowOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
adaptorOrders.SelectRowCommand = selRowOrdersCmd;
// insert row metadata command
SqlCommand insMetadataOrdersCmd = new SqlCommand();
insMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
insMetadataOrdersCmd.CommandText = "sp_orders_insertmetadata";
insMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.InsertMetadataCommand = insMetadataOrdersCmd;
// update row metadata command
SqlCommand updMetadataOrdersCmd = new SqlCommand();
updMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
updMetadataOrdersCmd.CommandText = "sp_orders_updatemetadata";
updMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.UpdateMetadataCommand = updMetadataOrdersCmd;
// delete row metadata command
SqlCommand delMetadataOrdersCmd = new SqlCommand();
delMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
delMetadataOrdersCmd.CommandText = "sp_orders_deletemetadata";
delMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.DeleteMetadataCommand = delMetadataOrdersCmd;
// get tombstones for cleanup
SqlCommand selTombstonesOrdersCmd = new SqlCommand();
selTombstonesOrdersCmd.CommandType = CommandType.StoredProcedure;
selTombstonesOrdersCmd.CommandText = "sp_orders_selecttombstones";
selTombstonesOrdersCmd.Parameters.Add("@tombstone_aging_in_hours", SqlDbType.Int).Value = TombstoneAgingInHours;
adaptorOrders.SelectMetadataForCleanupCommand = selTombstonesOrdersCmd;
peerProvider.SyncAdapters.Add(adaptorOrders);
// order_details table
DbSyncAdapter adaptorOrderDetails = new DbSyncAdapter("order_details");
adaptorOrderDetails.RowIdColumns.Add("order_id");
// select incremental inserts command
SqlCommand chgsOrderDetailsCmd = new SqlCommand();
chgsOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
chgsOrderDetailsCmd.CommandText = "sp_order_details_selectchanges";
chgsOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
adaptorOrderDetails.SelectIncrementalChangesCommand = chgsOrderDetailsCmd;
// insert row command
SqlCommand insOrderDetailsCmd = new SqlCommand();
insOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
insOrderDetailsCmd.CommandText = "sp_order_details_applyinsert";
insOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
insOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int);
insOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100);
insOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int);
insOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.InsertCommand = insOrderDetailsCmd;
// update row command
SqlCommand updOrderDetailsCmd = new SqlCommand();
updOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
updOrderDetailsCmd.CommandText = "sp_order_details_applyupdate";
updOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100);
updOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.UpdateCommand = updOrderDetailsCmd;
// delete row command
SqlCommand delOrderDetailsCmd = new SqlCommand();
delOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
delOrderDetailsCmd.CommandText = "sp_order_details_applydelete";
delOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
delOrderDetailsCmd.Parameters.Add("@sync_force_write", SqlDbType.Int);
delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.DeleteCommand = delOrderDetailsCmd;
// get row command
SqlCommand selRowOrderDetailsCmd = new SqlCommand();
selRowOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
selRowOrderDetailsCmd.CommandText = "sp_order_details_selectrow";
selRowOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
adaptorOrderDetails.SelectRowCommand = selRowOrderDetailsCmd;
// insert row metadata command
SqlCommand insMetadataOrderDetailsCmd = new SqlCommand();
insMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
insMetadataOrderDetailsCmd.CommandText = "sp_order_details_insertmetadata";
insMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.InsertMetadataCommand = insMetadataOrderDetailsCmd;
// update row metadata command
SqlCommand updMetadataOrderDetailsCmd = new SqlCommand();
updMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
updMetadataOrderDetailsCmd.CommandText = "sp_order_details_updatemetadata";
updMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.UpdateMetadataCommand = updMetadataOrderDetailsCmd;
// delete row metadata command
SqlCommand delMetadataOrderDetailsCmd = new SqlCommand();
delMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
delMetadataOrderDetailsCmd.CommandText = "sp_order_details_deletemetadata";
delMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.DeleteMetadataCommand = delMetadataOrderDetailsCmd;
// get tombstones for cleanup
SqlCommand selTombstonesOrderDetailsCmd = new SqlCommand();
selTombstonesOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
selTombstonesOrderDetailsCmd.CommandText = "sp_order_details_selecttombstones";
selTombstonesOrderDetailsCmd.Parameters.Add("@tombstone_aging_in_hours", SqlDbType.Int).Value = TombstoneAgingInHours;
adaptorOrderDetails.SelectMetadataForCleanupCommand = selTombstonesOrderDetailsCmd;
peerProvider.SyncAdapters.Add(adaptorOrderDetails);
//
// 2. Setup provider wide commands
// There are few commands on the provider itself and not on a table sync adapter:
// SelectNewTimestampCommand: Returns the new high watermark for current sync
// SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge and scope version (timestamp)
// UpdateScopeInfoCommand: Sets the new values for sync knowledge and cleanup knowledge
//
SqlCommand anchorCmd = new SqlCommand();
anchorCmd.CommandType = CommandType.Text;
anchorCmd.CommandText = "select @" + DbSyncSession.SyncNewTimestamp + " = @@DBTS"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"
anchorCmd.Parameters.Add("@" + DbSyncSession.SyncNewTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output;
peerProvider.SelectNewTimestampCommand = anchorCmd;
//
// Select local replica info
//
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 scope_info " +
"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;
//
// Update local replica info
//
SqlCommand updReplicaInfoCmd = new SqlCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "update scope_info set " +
"scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
"scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
"where scope_name = @" + DbSyncSession.SyncScopeName + " ;" +
"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.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
//
// 3. Track sync process
//
peerProvider.SyncProgress += new EventHandler <DbSyncProgressEventArgs>(ShowProgress);
peerProvider.ApplyChangeFailed += new EventHandler <DbApplyChangeFailedEventArgs>(ShowFailures);
peerProvider.SyncPeerOutdated += new EventHandler <DbOutdatedEventArgs>(ShowOutdated);
return(peerProvider);
}
InternalApplyThenGetChangesAsync(ClientScopeInfo clientScopeInfo, SyncContext context, BatchInfo clientBatchInfo, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
await using var runner = await this.GetConnectionAsync(context, SyncMode.Reading, SyncStage.ChangesApplying, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
SyncSet schema;
ServerScopeInfo serverScopeInfo;
// is it something that could happens ?
if (clientScopeInfo.Schema == null)
{
// Make a remote call to get Schema from remote provider
(context, serverScopeInfo) = await this.InternalGetServerScopeInfoAsync(
context, null, runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);
schema = serverScopeInfo.Schema;
}
else
{
schema = clientScopeInfo.Schema;
}
schema.EnsureSchema();
// if we don't have any BatchPartsInfo, just generate a new one to get, at least, something to send to the server
// and get a response with new data from server
if (clientBatchInfo == null)
{
clientBatchInfo = new BatchInfo(schema);
}
// --------------------------------------------------------------
// STEP 1 : Send everything to the server side
// --------------------------------------------------------------
HttpResponseMessage response = null;
// If not in memory and BatchPartsInfo.Count == 0, nothing to send.
// But we need to send something, so generate a little batch part
if (clientBatchInfo.BatchPartsInfo.Count == 0)
{
var changesToSend = new HttpMessageSendChangesRequest(context, clientScopeInfo);
var containerSet = new ContainerSet();
changesToSend.Changes = containerSet;
changesToSend.IsLastBatch = true;
changesToSend.BatchIndex = 0;
changesToSend.BatchCount = clientBatchInfo.BatchPartsInfo == null ? 0 : clientBatchInfo.BatchPartsInfo.Count;
var inMemoryRowsCount = changesToSend.Changes.RowsCount();
context.ProgressPercentage += 0.125;
await this.InterceptAsync(new HttpSendingClientChangesRequestArgs(changesToSend, inMemoryRowsCount, inMemoryRowsCount, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
// serialize message
var serializer = this.SerializerFactory.GetSerializer <HttpMessageSendChangesRequest>();
var binaryData = await serializer.SerializeAsync(changesToSend);
response = await this.httpRequestHandler.ProcessRequestAsync
(this.HttpClient, context, this.ServiceUri, binaryData, HttpStep.SendChangesInProgress,
this.SerializerFactory, this.Converter, this.Options.BatchSize, this.SyncPolicy, cancellationToken, progress).ConfigureAwait(false);
}
else
{
int tmpRowsSendedCount = 0;
// Foreach part, will have to send them to the remote
// once finished, return context
var initialPctProgress1 = context.ProgressPercentage;
var localSerializer = new LocalJsonSerializer();
var interceptorsReading = this.interceptors.GetInterceptors <DeserializingRowArgs>();
if (interceptorsReading.Count > 0)
{
localSerializer.OnReadingRow(async(schemaTable, rowString) =>
{
var args = new DeserializingRowArgs(context, schemaTable, rowString);
await this.InterceptAsync(args);
return(args.Result);
});
}
foreach (var bpi in clientBatchInfo.BatchPartsInfo.OrderBy(bpi => bpi.Index))
{
// Get the updatable schema for the only table contained in the batchpartinfo
var schemaTable = DbSyncAdapter.CreateChangesTable(schema.Tables[bpi.Tables[0].TableName, bpi.Tables[0].SchemaName]);
// Generate the ContainerSet containing rows to send to the user
var containerSet = new ContainerSet();
var containerTable = new ContainerTable(schemaTable);
var fullPath = Path.Combine(clientBatchInfo.GetDirectoryFullPath(), bpi.FileName);
containerSet.Tables.Add(containerTable);
// read rows from file
foreach (var row in localSerializer.ReadRowsFromFile(fullPath, schemaTable))
{
containerTable.Rows.Add(row.ToArray());
}
// Call the converter if needed
if (this.Converter != null && containerTable.HasRows)
{
BeforeSerializeRows(containerTable, schemaTable, this.Converter);
}
// Create the send changes request
var changesToSend = new HttpMessageSendChangesRequest(context, clientScopeInfo)
{
Changes = containerSet,
IsLastBatch = bpi.IsLastBatch,
BatchIndex = bpi.Index,
BatchCount = clientBatchInfo.BatchPartsInfo.Count
};
tmpRowsSendedCount += containerTable.Rows.Count;
context.ProgressPercentage = initialPctProgress1 + ((changesToSend.BatchIndex + 1) * 0.2d / changesToSend.BatchCount);
await this.InterceptAsync(new HttpSendingClientChangesRequestArgs(changesToSend, tmpRowsSendedCount, clientBatchInfo.RowsCount, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
// serialize message
var serializer = this.SerializerFactory.GetSerializer <HttpMessageSendChangesRequest>();
var binaryData = await serializer.SerializeAsync(changesToSend);
response = await this.httpRequestHandler.ProcessRequestAsync
(this.HttpClient, context, this.ServiceUri, binaryData, HttpStep.SendChangesInProgress,
this.SerializerFactory, this.Converter, this.Options.BatchSize, this.SyncPolicy, cancellationToken, progress).ConfigureAwait(false);
// See #721 for issue and #721 for PR from slagtejn
if (!bpi.IsLastBatch)
{
response.Dispose();
}
}
}
// --------------------------------------------------------------
// STEP 2 : Receive everything from the server side
// --------------------------------------------------------------
// Now we have sent all the datas to the server and now :
// We have a FIRST response from the server with new datas
// 1) Could be the only one response
// 2) Could be the first response and we need to download all batchs
context.SyncStage = SyncStage.ChangesSelecting;
var initialPctProgress = 0.55;
context.ProgressPercentage = initialPctProgress;
// Create the BatchInfo
var serverBatchInfo = new BatchInfo(schema);
HttpMessageSummaryResponse summaryResponseContent = null;
// Deserialize response incoming from server
using (var streamResponse = await response.Content.ReadAsStreamAsync().ConfigureAwait(false))
{
var responseSerializer = this.SerializerFactory.GetSerializer <HttpMessageSummaryResponse>();
summaryResponseContent = await responseSerializer.DeserializeAsync(streamResponse);
}
serverBatchInfo.RowsCount = summaryResponseContent.BatchInfo.RowsCount;
serverBatchInfo.Timestamp = summaryResponseContent.RemoteClientTimestamp;
context = summaryResponseContent.SyncContext;
if (summaryResponseContent.BatchInfo.BatchPartsInfo != null)
{
foreach (var bpi in summaryResponseContent.BatchInfo.BatchPartsInfo)
{
serverBatchInfo.BatchPartsInfo.Add(bpi);
}
}
// From here, we need to serialize everything on disk
// Generate the batch directory
var batchDirectoryRoot = this.Options.BatchDirectory;
var batchDirectoryName = string.Concat(DateTime.UtcNow.ToString("yyyy_MM_dd_ss"), Path.GetRandomFileName().Replace(".", ""));
serverBatchInfo.DirectoryRoot = batchDirectoryRoot;
serverBatchInfo.DirectoryName = batchDirectoryName;
if (!Directory.Exists(serverBatchInfo.GetDirectoryFullPath()))
{
Directory.CreateDirectory(serverBatchInfo.GetDirectoryFullPath());
}
// If we have a snapshot we are raising the batches downloading process that will occurs
await this.InterceptAsync(new HttpBatchesDownloadingArgs(context, serverBatchInfo, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
// function used to download one part
var dl = new Func <BatchPartInfo, Task>(async(bpi) =>
{
if (cancellationToken.IsCancellationRequested)
{
return;
}
var changesToSend3 = new HttpMessageGetMoreChangesRequest(context, bpi.Index);
var serializer3 = this.SerializerFactory.GetSerializer <HttpMessageGetMoreChangesRequest>();
var binaryData3 = await serializer3.SerializeAsync(changesToSend3).ConfigureAwait(false);
var step3 = HttpStep.GetMoreChanges;
await this.InterceptAsync(new HttpGettingServerChangesRequestArgs(bpi.Index, serverBatchInfo.BatchPartsInfo.Count, summaryResponseContent.SyncContext, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
// Raise get changes request
context.ProgressPercentage = initialPctProgress + ((bpi.Index + 1) * 0.2d / serverBatchInfo.BatchPartsInfo.Count);
var response = await this.httpRequestHandler.ProcessRequestAsync(
this.HttpClient, context, this.ServiceUri, binaryData3, step3,
this.SerializerFactory, this.Converter, 0, this.SyncPolicy, cancellationToken, progress).ConfigureAwait(false);
if (this.SerializerFactory.Key != "json")
{
var webSerializer = this.SerializerFactory.GetSerializer <HttpMessageSendChangesResponse>();
using var responseStream = await response.Content.ReadAsStreamAsync().ConfigureAwait(false);
var getMoreChanges = await webSerializer.DeserializeAsync(responseStream);
context = getMoreChanges.SyncContext;
if (getMoreChanges != null && getMoreChanges.Changes != null && getMoreChanges.Changes.HasRows)
{
var localSerializer = new LocalJsonSerializer();
var interceptorsWriting = this.interceptors.GetInterceptors <SerializingRowArgs>();
if (interceptorsWriting.Count > 0)
{
localSerializer.OnWritingRow(async(syncTable, rowArray) =>
{
var args = new SerializingRowArgs(context, syncTable, rowArray);
await this.InterceptAsync(args, progress, cancellationToken).ConfigureAwait(false);
return(args.Result);
});
}
// Should have only one table
var table = getMoreChanges.Changes.Tables[0];
var schemaTable = DbSyncAdapter.CreateChangesTable(schema.Tables[table.TableName, table.SchemaName]);
var fullPath = Path.Combine(serverBatchInfo.GetDirectoryFullPath(), bpi.FileName);
// open the file and write table header
await localSerializer.OpenFileAsync(fullPath, schemaTable).ConfigureAwait(false);
foreach (var row in table.Rows)
{
await localSerializer.WriteRowToFileAsync(new SyncRow(schemaTable, row), schemaTable).ConfigureAwait(false);
}
// Close file
await localSerializer.CloseFileAsync(fullPath, schemaTable).ConfigureAwait(false);
}
}
else
{
// Serialize
await SerializeAsync(response, bpi.FileName, serverBatchInfo.GetDirectoryFullPath(), this).ConfigureAwait(false);
}
// Raise response from server containing a batch changes
await this.InterceptAsync(new HttpGettingServerChangesResponseArgs(serverBatchInfo, bpi.Index, bpi.RowsCount, summaryResponseContent.SyncContext, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
});
// Parrallel download of all bpis (which will launch the delete directory on the server side)
await serverBatchInfo.BatchPartsInfo.ForEachAsync(bpi => dl(bpi), this.MaxDownladingDegreeOfParallelism).ConfigureAwait(false);
// Send order of end of download
var lastBpi = serverBatchInfo.BatchPartsInfo.FirstOrDefault(bpi => bpi.IsLastBatch);
if (lastBpi != null)
{
var endOfDownloadChanges = new HttpMessageGetMoreChangesRequest(context, lastBpi.Index);
var serializerEndOfDownloadChanges = this.SerializerFactory.GetSerializer <HttpMessageGetMoreChangesRequest>();
var binaryData3 = await serializerEndOfDownloadChanges.SerializeAsync(endOfDownloadChanges).ConfigureAwait(false);
var endResponse = await this.httpRequestHandler.ProcessRequestAsync(
this.HttpClient, context, this.ServiceUri, binaryData3, HttpStep.SendEndDownloadChanges,
this.SerializerFactory, this.Converter, 0, this.SyncPolicy, cancellationToken, progress).ConfigureAwait(false);
// Deserialize response incoming from server
// This is the last response
// Should contains step HttpStep.SendEndDownloadChanges
using var streamResponse = await endResponse.Content.ReadAsStreamAsync().ConfigureAwait(false);
var endResponseSerializer = this.SerializerFactory.GetSerializer <HttpMessageSendChangesResponse>();
var endResponseContent = await endResponseSerializer.DeserializeAsync(streamResponse);
context = endResponseContent.SyncContext;
}
// generate the new scope item
this.CompleteTime = DateTime.UtcNow;
await this.InterceptAsync(new HttpBatchesDownloadedArgs(summaryResponseContent, summaryResponseContent.SyncContext, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
var serverSyncChanges = new ServerSyncChanges(
summaryResponseContent.RemoteClientTimestamp,
serverBatchInfo,
summaryResponseContent.ServerChangesSelected
);
return(context, serverSyncChanges, summaryResponseContent.ClientChangesApplied, summaryResponseContent.ConflictResolutionPolicy);
}
//</snippetOCSv2_CS_Peer_Cleanup_ConfigureCeSyncProvider>
//<snippetOCSv2_CS_Peer_Cleanup_ConfigureDbSyncProvider>
public DbSyncProvider ConfigureDbSyncProvider(string peerConnString, int metadataAgingInDays)
{
DbSyncProvider sampleDbProvider = new DbSyncProvider();
//<snippetOCSv2_CS_Peer_Cleanup_Scope>
SqlConnection peerConnection = new SqlConnection(peerConnString);
sampleDbProvider.Connection = peerConnection;
sampleDbProvider.ScopeName = "Sales";
//</snippetOCSv2_CS_Peer_Cleanup_Scope>
//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.
//<snippetOCSv2_CS_Peer_Cleanup_AdapterCustomer>
DbSyncAdapter adapterCustomer = new DbSyncAdapter("Customer");
//Specify the primary key, which Sync Services uses
//to identify each row during synchronization.
adapterCustomer.RowIdColumns.Add("CustomerId");
//</snippetOCSv2_CS_Peer_Cleanup_AdapterCustomer>
//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 Services 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.
//<snippetOCSv2_CS_Peer_Cleanup_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;
//</snippetOCSv2_CS_Peer_Cleanup_SelectIncrementalChangesCommand>
//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.
//<snippetOCSv2_CS_Peer_Cleanup_InsertCommand>
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;
//</snippetOCSv2_CS_Peer_Cleanup_InsertCommand>
//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.
//<snippetOCSv2_CS_Peer_Cleanup_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;
//</snippetOCSv2_CS_Peer_Cleanup_UpdateCommand>
//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.
//<snippetOCSv2_CS_Peer_Cleanup_DeleteCommand>
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;
//</snippetOCSv2_CS_Peer_Cleanup_DeleteCommand>
//Specify the command to select any conflicting rows.
//<snippetOCSv2_CS_Peer_Cleanup_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;
//</snippetOCSv2_CS_Peer_Cleanup_SelectRowCommand>
//Specify the command to insert metadata rows.
//The session variables in this command relate to columns in
//the tracking table.
//<snippetOCSv2_CS_Peer_Cleanup_InsertMetadataCommand>
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;
//</snippetOCSv2_CS_Peer_Cleanup_InsertMetadataCommand>
//Specify the command to update metadata rows.
//<snippetOCSv2_CS_Peer_Cleanup_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;
//</snippetOCSv2_CS_Peer_Cleanup_UpdateMetadataCommand>
//Specify the command to delete metadata rows.
//<snippetOCSv2_CS_Peer_Cleanup_DeleteMetadataCommand>
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;
//</snippetOCSv2_CS_Peer_Cleanup_DeleteMetadataCommand>
//Specify the command to select metadata rows for cleanup.
//<snippetOCSv2_CS_Peer_Cleanup_SelectMetadataForCleanupCommand>
SqlCommand selMetadataCustomerCmd = new SqlCommand();
selMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
selMetadataCustomerCmd.CommandText = "Sync.sp_Customer_SelectMetadata";
selMetadataCustomerCmd.Parameters.Add("@metadata_aging_in_days", SqlDbType.Int).Value = metadataAgingInDays;
selMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
adapterCustomer.SelectMetadataForCleanupCommand = selMetadataCustomerCmd;
//</snippetOCSv2_CS_Peer_Cleanup_SelectMetadataForCleanupCommand>
//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.
// * SelectOverlappingScopesCommand: returns the scope name and table name for all tables
// in the specified scope that are also included in other scopes.
// * UpdateScopeCleanupTimestampCommand: updates the scope_cleanup_timestamp column for a
// particular scope in the scope_info table, to mark the point up to which cleanup
// has been performed for the scope.
//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.
//<snippetOCSv2_CS_Peer_Cleanup_NewAnchorCommand>
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;
//</snippetOCSv2_CS_Peer_Cleanup_NewAnchorCommand>
//Specify the command to select local replica metadata.
//<snippetOCSv2_CS_Peer_Cleanup_SelectScopeInfoCommand>
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;
//</snippetOCSv2_CS_Peer_Cleanup_SelectScopeInfoCommand>
//Specify the command to update local replica metadata.
//<snippetOCSv2_CS_Peer_Cleanup_UpdateScopeInfoCommand>
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;
//</snippetOCSv2_CS_Peer_Cleanup_UpdateScopeInfoCommand>
//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.
//<snippetOCSv2_CS_Peer_Cleanup_SelectTableMaxTimestampsCommand>
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;
//</snippetOCSv2_CS_Peer_Cleanup_SelectTableMaxTimestampsCommand>
//Specify the command to select table and scope names for
//any tables that are in more than one scope.
//<snippetOCSv2_CS_Peer_Cleanup_SelectOverlappingScopesCommand>
SqlCommand overlappingScopesCmd = new SqlCommand();
overlappingScopesCmd.CommandType = CommandType.StoredProcedure;
overlappingScopesCmd.CommandText = "Sync.sp_SelectSharedScopes";
overlappingScopesCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
sampleDbProvider.SelectOverlappingScopesCommand = overlappingScopesCmd;
//</snippetOCSv2_CS_Peer_Cleanup_SelectOverlappingScopesCommand>
//Specify the command that updates the scope information table
//to indicate to which point metadata has been cleaned up for a scope.
//<snippetOCSv2_CS_Peer_Cleanup_UpdateScopeCleanupTimestampCommand>
SqlCommand updScopeCleanupInfoCmd = new SqlCommand();
updScopeCleanupInfoCmd.CommandType = CommandType.Text;
updScopeCleanupInfoCmd.CommandText = "UPDATE scope_info set " +
" scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp +
" WHERE scope_name = @" + DbSyncSession.SyncScopeName +
" AND(scope_cleanup_timestamp is null or scope_cleanup_timestamp < @" + DbSyncSession.SyncScopeCleanupTimestamp + ");" +
" SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupTimestamp, SqlDbType.BigInt);
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
updScopeCleanupInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
sampleDbProvider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;
//</snippetOCSv2_CS_Peer_Cleanup_UpdateScopeCleanupTimestampCommand>
return(sampleDbProvider);
}
DbSyncProvider SetupSyncProvider(string connectionString, DbSyncProvider peerProvider)
{
const int TombstoneAgingInHours = 100;
SqlConnection connection = new SqlConnection(connectionString);
peerProvider.Connection = connection;
//
// 1. Create sync adapter for each sync table and attach it to the provider
// Following DataAdapter style in ADO.NET, DbSyncAdapter is the equivelent for sync.
// The code below shows how to create DbSyncAdapter objects for orders
// and order_details tables using stored procedures stored on the database.
//
peerProvider.ScopeName = "Sales";
// orders table
DbSyncAdapter adaptorOrders = new DbSyncAdapter("orders");
adaptorOrders.RowIdColumns.Add("order_id");
// select incremental changes command
SqlCommand chgsOrdersCmd = new SqlCommand();
chgsOrdersCmd.CommandType = CommandType.StoredProcedure;
chgsOrdersCmd.CommandText = "sp_orders_selectchanges";
chgsOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
adaptorOrders.SelectIncrementalChangesCommand = chgsOrdersCmd;
// insert row command
SqlCommand insOrdersCmd = new SqlCommand();
insOrdersCmd.CommandType = CommandType.StoredProcedure;
insOrdersCmd.CommandText = "sp_orders_applyinsert";
insOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
insOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime);
insOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.InsertCommand = insOrdersCmd;
// update row command
SqlCommand updOrdersCmd = new SqlCommand();
updOrdersCmd.CommandType = CommandType.StoredProcedure;
updOrdersCmd.CommandText = "sp_orders_applyupdate";
updOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
updOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime);
updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
updOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.UpdateCommand = updOrdersCmd;
// delete row command
SqlCommand delOrdersCmd = new SqlCommand();
delOrdersCmd.CommandType = CommandType.StoredProcedure;
delOrdersCmd.CommandText = "sp_orders_applydelete";
delOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
delOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.DeleteCommand = delOrdersCmd;
// get row command
SqlCommand selRowOrdersCmd = new SqlCommand();
selRowOrdersCmd.CommandType = CommandType.StoredProcedure;
selRowOrdersCmd.CommandText = "sp_orders_selectrow";
selRowOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
adaptorOrders.SelectRowCommand = selRowOrdersCmd;
// insert row metadata command
SqlCommand insMetadataOrdersCmd = new SqlCommand();
insMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
insMetadataOrdersCmd.CommandText = "sp_orders_insertmetadata";
insMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
insMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.InsertMetadataCommand = insMetadataOrdersCmd;
// update row metadata command
SqlCommand updMetadataOrdersCmd = new SqlCommand();
updMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
updMetadataOrdersCmd.CommandText = "sp_orders_updatemetadata";
updMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.UpdateMetadataCommand = updMetadataOrdersCmd;
// delete row metadata command
SqlCommand delMetadataOrdersCmd = new SqlCommand();
delMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
delMetadataOrdersCmd.CommandText = "sp_orders_deletemetadata";
delMetadataOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
delMetadataOrdersCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrders.DeleteMetadataCommand = delMetadataOrdersCmd;
// get tombstones for cleanup
SqlCommand selTombstonesOrdersCmd = new SqlCommand();
selTombstonesOrdersCmd.CommandType = CommandType.StoredProcedure;
selTombstonesOrdersCmd.CommandText = "sp_orders_selecttombstones";
selTombstonesOrdersCmd.Parameters.Add("@tombstone_aging_in_hours", SqlDbType.Int).Value = TombstoneAgingInHours;
adaptorOrders.SelectMetadataForCleanupCommand = selTombstonesOrdersCmd;
peerProvider.SyncAdapters.Add(adaptorOrders);
// order_details table
DbSyncAdapter adaptorOrderDetails = new DbSyncAdapter("order_details");
adaptorOrderDetails.RowIdColumns.Add("order_id");
// select incremental inserts command
SqlCommand chgsOrderDetailsCmd = new SqlCommand();
chgsOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
chgsOrderDetailsCmd.CommandText = "sp_order_details_selectchanges";
chgsOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMetadataOnly, SqlDbType.Int);
chgsOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
adaptorOrderDetails.SelectIncrementalChangesCommand = chgsOrderDetailsCmd;
// insert row command
SqlCommand insOrderDetailsCmd = new SqlCommand();
insOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
insOrderDetailsCmd.CommandText = "sp_order_details_applyinsert";
insOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
insOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int);
insOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100);
insOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int);
insOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.InsertCommand = insOrderDetailsCmd;
// update row command
SqlCommand updOrderDetailsCmd = new SqlCommand();
updOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
updOrderDetailsCmd.CommandText = "sp_order_details_applyupdate";
updOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100);
updOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.UpdateCommand = updOrderDetailsCmd;
// delete row command
SqlCommand delOrderDetailsCmd = new SqlCommand();
delOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
delOrderDetailsCmd.CommandText = "sp_order_details_applydelete";
delOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
delOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.DeleteCommand = delOrderDetailsCmd;
// get row command
SqlCommand selRowOrderDetailsCmd = new SqlCommand();
selRowOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
selRowOrderDetailsCmd.CommandText = "sp_order_details_selectrow";
selRowOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
adaptorOrderDetails.SelectRowCommand = selRowOrderDetailsCmd;
// insert row metadata command
SqlCommand insMetadataOrderDetailsCmd = new SqlCommand();
insMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
insMetadataOrderDetailsCmd.CommandText = "sp_order_details_insertmetadata";
insMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
insMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.InsertMetadataCommand = insMetadataOrderDetailsCmd;
// update row metadata command
SqlCommand updMetadataOrderDetailsCmd = new SqlCommand();
updMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
updMetadataOrderDetailsCmd.CommandText = "sp_order_details_updatemetadata";
updMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
updMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.UpdateMetadataCommand = updMetadataOrderDetailsCmd;
// delete row metadata command
SqlCommand delMetadataOrderDetailsCmd = new SqlCommand();
delMetadataOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
delMetadataOrderDetailsCmd.CommandText = "sp_order_details_deletemetadata";
delMetadataOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
delMetadataOrderDetailsCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
adaptorOrderDetails.DeleteMetadataCommand = delMetadataOrderDetailsCmd;
// get tombstones for cleanup
SqlCommand selTombstonesOrderDetailsCmd = new SqlCommand();
selTombstonesOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
selTombstonesOrderDetailsCmd.CommandText = "sp_order_details_selecttombstones";
selTombstonesOrderDetailsCmd.Parameters.Add("@tombstone_aging_in_hours", SqlDbType.Int).Value = TombstoneAgingInHours;
adaptorOrderDetails.SelectMetadataForCleanupCommand = selTombstonesOrderDetailsCmd;
peerProvider.SyncAdapters.Add(adaptorOrderDetails);
//
// 2. Setup provider wide commands
// There are few commands on the provider itself and not on a table sync adapter:
// SelectNewTimestampCommand: Returns the new high watermark for current sync
// SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge and scope version (timestamp)
// UpdateScopeInfoCommand: Sets the new values for sync knowledge and cleanup knowledge
//
SqlCommand anchorCmd = new SqlCommand();
anchorCmd.CommandType = CommandType.Text;
anchorCmd.CommandText = "select @" + DbSyncSession.SyncNewTimestamp + " = @@DBTS"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"
anchorCmd.Parameters.Add("@" + DbSyncSession.SyncNewTimestamp, SqlDbType.BigInt).Direction = ParameterDirection.Output;
peerProvider.SelectNewTimestampCommand = anchorCmd;
//
// Select local replica info
//
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 scope_info " +
"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;
//
// Update local replica info
//
SqlCommand updReplicaInfoCmd = new SqlCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "update scope_info set " +
"scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
"scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
"where scope_name = @" + DbSyncSession.SyncScopeName + " ;" +
"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.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
peerProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
//
// 3. Track sync process
//
peerProvider.SyncProgress += new EventHandler<DbSyncProgressEventArgs>(ShowProgress);
peerProvider.ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(ShowFailures);
return peerProvider;
}
/// <summary>
/// Create a response message content based on a requested index in a server batch info
/// </summary>
private HttpMessageSendChangesResponse GetChangesResponse(SyncContext syncContext, long remoteClientTimestamp, BatchInfo serverBatchInfo,
DatabaseChangesSelected serverChangesSelected, int batchIndexRequested, ConflictResolutionPolicy policy)
{
// 1) Create the http message content response
var changesResponse = new HttpMessageSendChangesResponse(syncContext);
changesResponse.ChangesSelected = serverChangesSelected;
changesResponse.ServerStep = HttpStep.GetChanges;
changesResponse.ConflictResolutionPolicy = policy;
// If nothing to do, just send back
if (serverBatchInfo.InMemory || serverBatchInfo.BatchPartsInfo.Count == 0)
{
if (this.ClientConverter != null && serverBatchInfo.InMemoryData.HasRows)
{
BeforeSerializeRows(serverBatchInfo.InMemoryData, this.ClientConverter);
}
changesResponse.Changes = serverBatchInfo.InMemoryData.GetContainerSet();
changesResponse.BatchIndex = 0;
changesResponse.IsLastBatch = true;
changesResponse.RemoteClientTimestamp = remoteClientTimestamp;
return(changesResponse);
}
// Get the batch part index requested
var batchPartInfo = serverBatchInfo.BatchPartsInfo.First(d => d.Index == batchIndexRequested);
// if we are not in memory, we set the BI in session, to be able to get it back on next request
// create the in memory changes set
var changesSet = new SyncSet(Schema.ScopeName);
foreach (var table in Schema.Tables)
{
DbSyncAdapter.CreateChangesTable(Schema.Tables[table.TableName, table.SchemaName], changesSet);
}
batchPartInfo.LoadBatch(changesSet, serverBatchInfo.GetDirectoryFullPath());
// if client request a conversion on each row, apply the conversion
if (this.ClientConverter != null && batchPartInfo.Data.HasRows)
{
BeforeSerializeRows(batchPartInfo.Data, this.ClientConverter);
}
changesResponse.Changes = batchPartInfo.Data.GetContainerSet();
changesResponse.BatchIndex = batchIndexRequested;
changesResponse.IsLastBatch = batchPartInfo.IsLastBatch;
changesResponse.RemoteClientTimestamp = remoteClientTimestamp;
changesResponse.ServerStep = batchPartInfo.IsLastBatch ? HttpStep.GetChanges : HttpStep.GetChangesInProgress;
// If we have only one bpi, we can safely delete it
if (batchPartInfo.IsLastBatch)
{
// delete the folder (not the BatchPartInfo, because we have a reference on it)
if (this.Options.CleanFolder)
{
var shouldDeleteFolder = true;
if (!string.IsNullOrEmpty(this.Options.SnapshotsDirectory))
{
var dirInfo = new DirectoryInfo(serverBatchInfo.DirectoryRoot);
var snapInfo = new DirectoryInfo(this.Options.SnapshotsDirectory);
shouldDeleteFolder = dirInfo.FullName != snapInfo.FullName;
}
if (shouldDeleteFolder)
{
serverBatchInfo.TryRemoveDirectory();
}
}
}
return(changesResponse);
}
/// <summary>
/// Get changes from
/// </summary>
internal async Task <HttpMessageSendChangesResponse> ApplyThenGetChangesAsync(
HttpMessageSendChangesRequest httpMessage, SessionCache sessionCache, int clientBatchSize, CancellationToken cancellationToken)
{
// Get if we need to serialize data or making everything in memory
var clientWorkInMemory = clientBatchSize == 0;
// Check schema.
// If client has stored the schema, the EnsureScope will not be called on server.
if (this.Schema == null || !this.Schema.HasTables || !this.Schema.HasColumns)
{
var(_, newSchema) = await this.EnsureSchemaAsync(
httpMessage.SyncContext, this.Setup, cancellationToken).ConfigureAwait(false);
newSchema.EnsureSchema();
this.Schema = newSchema;
}
// ------------------------------------------------------------
// FIRST STEP : receive client changes
// ------------------------------------------------------------
// We are receiving changes from client
// BatchInfo containing all BatchPartInfo objects
// Retrieve batchinfo instance if exists
// Get batch info from session cache if exists, otherwise create it
if (sessionCache.ClientBatchInfo == null)
{
sessionCache.ClientBatchInfo = new BatchInfo(clientWorkInMemory, Schema, this.Options.BatchDirectory);
}
// create the in memory changes set
var changesSet = new SyncSet(Schema.ScopeName);
foreach (var table in httpMessage.Changes.Tables)
{
DbSyncAdapter.CreateChangesTable(Schema.Tables[table.TableName, table.SchemaName], changesSet);
}
changesSet.ImportContainerSet(httpMessage.Changes, false);
// If client has made a conversion on each line, apply the reverse side of it
if (this.ClientConverter != null && changesSet.HasRows)
{
AfterDeserializedRows(changesSet, this.ClientConverter);
}
// add changes to the batch info
sessionCache.ClientBatchInfo.AddChanges(changesSet, httpMessage.BatchIndex, httpMessage.IsLastBatch);
// Clear the httpMessage set
if (!clientWorkInMemory && httpMessage.Changes != null)
{
httpMessage.Changes.Clear();
}
// Until we don't have received all the batches, wait for more
if (!httpMessage.IsLastBatch)
{
return new HttpMessageSendChangesResponse(httpMessage.SyncContext)
{
ServerStep = HttpStep.SendChangesInProgress
}
}
;
// ------------------------------------------------------------
// SECOND STEP : apply then return server changes
// ------------------------------------------------------------
// get changes
var(context, remoteClientTimestamp, serverBatchInfo, policy, serverChangesSelected) =
await this.ApplyThenGetChangesAsync(httpMessage.SyncContext,
httpMessage.Scope, this.Schema, sessionCache.ClientBatchInfo, this.Options.DisableConstraintsOnApplyChanges,
this.Options.UseBulkOperations, false, this.Options.CleanFolder, clientBatchSize,
this.Options.BatchDirectory, this.Options.ConflictResolutionPolicy, cancellationToken).ConfigureAwait(false);
// Save the server batch info object to cache if not working in memory
if (!clientWorkInMemory)
{
sessionCache.RemoteClientTimestamp = remoteClientTimestamp;
sessionCache.ServerBatchInfo = serverBatchInfo;
sessionCache.ServerChangesSelected = serverChangesSelected;
}
// Get the firt response to send back to client
return(GetChangesResponse(context, remoteClientTimestamp, serverBatchInfo, serverChangesSelected, 0, policy));
}
/// <summary>
/// Configure the Oracle DbSyncprovider. Usual configuration similar to OCS V2 samples.
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
public OracleDbSyncProvider ConfigureDBSyncProvider(string connectionString)
{
OracleDbSyncProvider provider = new OracleDbSyncProvider();
provider.ScopeName = SyncUtils.ScopeName;
provider.Connection = new OracleConnection();
provider.Connection.ConnectionString = connectionString;
for (int i = 0; i < SyncUtils.SyncAdapterTables.Length; i++)
{
//Add each table as a DbSyncAdapter to the provider
DbSyncAdapter adapter = new DbSyncAdapter(SyncUtils.SyncAdapterTables[i]);
adapter.RowIdColumns.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i]);
// select incremental changes command
OracleCommand chgsOrdersCmd = new OracleCommand();
chgsOrdersCmd.CommandType = CommandType.StoredProcedure;
chgsOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_selectchanges";
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncMetadataOnly, OracleType.Int32);
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, OracleType.Number);
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, OracleType.Int32);
chgsOrdersCmd.Parameters.Add("sync_changes", OracleType.Cursor).Direction = ParameterDirection.Output;
adapter.SelectIncrementalChangesCommand = chgsOrdersCmd;
// insert row command
OracleCommand insOrdersCmd = new OracleCommand();
insOrdersCmd.CommandType = CommandType.StoredProcedure;
insOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applyinsert";
insOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], OracleType.Int32);
if (SyncUtils.SyncAdapterTables[i] == "orders")
{
insOrdersCmd.Parameters.Add("order_date", OracleType.DateTime);
}
else
{
insOrdersCmd.Parameters.Add("product", OracleType.NVarChar, 100);
insOrdersCmd.Parameters.Add("quantity", OracleType.Int32);
insOrdersCmd.Parameters.Add("order_id", OracleType.Int32);
}
insOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output;
adapter.InsertCommand = insOrdersCmd;
// update row command
OracleCommand updOrdersCmd = new OracleCommand();
updOrdersCmd.CommandType = CommandType.StoredProcedure;
updOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applyupdate";
updOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], OracleType.Int32);
if (SyncUtils.SyncAdapterTables[i] == "orders")
{
updOrdersCmd.Parameters.Add("order_date", OracleType.DateTime);
}
else
{
updOrdersCmd.Parameters.Add("product", OracleType.NVarChar, 100);
updOrdersCmd.Parameters.Add("quantity", OracleType.Int32);
updOrdersCmd.Parameters.Add("order_id", OracleType.Int32);
}
updOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, OracleType.Number);
updOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output;
updOrdersCmd.Parameters.Add(DbSyncSession.SyncForceWrite, OracleType.Int32);
adapter.UpdateCommand = updOrdersCmd;
// delete row command
OracleCommand delOrdersCmd = new OracleCommand();
delOrdersCmd.CommandType = CommandType.StoredProcedure;
delOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applydelete";
delOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], OracleType.Int32);
delOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, OracleType.Number);
delOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output;
delOrdersCmd.Parameters.Add(DbSyncSession.SyncForceWrite, OracleType.Int32);
adapter.DeleteCommand = delOrdersCmd;
// get row command
OracleCommand selRowOrdersCmd = new OracleCommand();
selRowOrdersCmd.CommandType = CommandType.StoredProcedure;
selRowOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_selectrow";
selRowOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], OracleType.Int32);
selRowOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, OracleType.Int32);
selRowOrdersCmd.Parameters.Add("selectedRow", OracleType.Cursor).Direction = ParameterDirection.Output;
adapter.SelectRowCommand = selRowOrdersCmd;
// insert row metadata command
OracleCommand insMetadataOrdersCmd = new OracleCommand();
insMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
insMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_insert_md";
insMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], OracleType.Int32);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, OracleType.Int32);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, OracleType.Number);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerKey, OracleType.Int32);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerTimestamp, OracleType.Number);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerKey, OracleType.Int32);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerTimestamp, OracleType.Number);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowIsTombstone, OracleType.Int32);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, OracleType.Int32);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output;
adapter.InsertMetadataCommand = insMetadataOrdersCmd;
// update row metadata command
OracleCommand updMetadataOrdersCmd = new OracleCommand();
updMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
updMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_update_md";
updMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], OracleType.Int32);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, OracleType.Int32);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, OracleType.Number);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerKey, OracleType.Int32);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerTimestamp, OracleType.Number);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerKey, OracleType.Int32);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerTimestamp, OracleType.Number);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowIsTombstone, OracleType.Int32);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, OracleType.Int32);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output;
adapter.UpdateMetadataCommand = (IDbCommand)updMetadataOrdersCmd.Clone();
// delete row metadata command
OracleCommand delMetadataOrdersCmd = new OracleCommand();
delMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
delMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_delete_md";
delMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], OracleType.Int32);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, OracleType.Int32);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, OracleType.Number);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output;
adapter.DeleteMetadataCommand = delMetadataOrdersCmd;
// get tombstones for cleanup
OracleCommand selTombstonesOrdersCmd = new OracleCommand();
selTombstonesOrdersCmd.CommandType = CommandType.StoredProcedure;
selTombstonesOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_select_ts";
selTombstonesOrdersCmd.Parameters.Add("tombstone_aging_in_hours", OracleType.Int32).Value = SyncUtils.TombstoneAgingInHours;
selTombstonesOrdersCmd.Parameters.Add("sync_scope_local_id", OracleType.Int32);
adapter.SelectMetadataForCleanupCommand = selTombstonesOrdersCmd;
provider.SyncAdapters.Add(adapter);
}
// 2. Setup provider wide commands
// There are few commands on the provider itself and not on a table sync adapter:
// SelectNewTimestampCommand: Returns the new high watermark for current sync
// SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge and scope version (timestamp)
// UpdateScopeInfoCommand: Sets the new values for sync knowledge and cleanup knowledge
//
OracleCommand anchorCmd = new OracleCommand();
anchorCmd.CommandType = CommandType.StoredProcedure;
anchorCmd.CommandText = "SP_GET_TIMESTAMP"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"
anchorCmd.Parameters.Add(DbSyncSession.SyncNewTimestamp, OracleType.UInt32).Direction = ParameterDirection.Output;
provider.SelectNewTimestampCommand = anchorCmd;
//
// Select local replica info
//
OracleCommand selReplicaInfoCmd = new OracleCommand();
selReplicaInfoCmd.CommandType = CommandType.Text;
selReplicaInfoCmd.CommandText = "select " +
"scope_id, " +
"scope_local_id, " +
"scope_sync_knowledge, " +
"scope_forgotten_knowledge, " +
"scope_timestamp " +
"from scope_info " +
"where scope_name = :" + DbSyncSession.SyncScopeName;
selReplicaInfoCmd.Parameters.Clear();
selReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeName, OracleType.NVarChar).Direction = ParameterDirection.Input;
provider.SelectScopeInfoCommand = selReplicaInfoCmd;
//
// Update local replica info
//
OracleCommand updReplicaInfoCmd = new OracleCommand();
updReplicaInfoCmd.CommandType = CommandType.Text;
updReplicaInfoCmd.CommandText = "begin update scope_info set " +
"scope_sync_knowledge = :" + DbSyncSession.SyncScopeKnowledge + ", " +
"scope_id = :" + DbSyncSession.SyncScopeId + ", " +
"scope_forgotten_knowledge = :" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
"where scope_name = :" + DbSyncSession.SyncScopeName + " and " +
" ( :" + DbSyncSession.SyncCheckConcurrency + " = 0 or scope_timestamp = :" + DbSyncSession.SyncScopeTimestamp + "); " +
":" + DbSyncSession.SyncRowCount + " := sql%rowcount; End;";
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeKnowledge, OracleType.Raw, 10000);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeCleanupKnowledge, OracleType.Raw, 10000);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeName, OracleType.NVarChar, 100);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, OracleType.Int32);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeId, OracleType.Raw);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeTimestamp, OracleType.Number);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output;
provider.UpdateScopeInfoCommand = updReplicaInfoCmd;
//
// Select overlapping scopes
//
// get tombstones for cleanup
OracleCommand overlappingScopesCmd = new OracleCommand();
overlappingScopesCmd.CommandType = CommandType.StoredProcedure;
overlappingScopesCmd.CommandText = "sp_select_shared_scopes";
overlappingScopesCmd.Parameters.Add(":" + DbSyncSession.SyncScopeName, OracleType.NVarChar, 100);
provider.SelectOverlappingScopesCommand = overlappingScopesCmd;
//
// Update table cleanup info
//
OracleCommand updScopeCleanupInfoCmd = new OracleCommand();
updScopeCleanupInfoCmd.CommandType = CommandType.Text;
updScopeCleanupInfoCmd.CommandText = "update scope_info set " +
"scope_cleanup_timestamp = :" + DbSyncSession.SyncScopeCleanupTimestamp + " " +
"where scope_name = :" + DbSyncSession.SyncScopeName + " and " +
"(scope_cleanup_timestamp is null or scope_cleanup_timestamp < :" + DbSyncSession.SyncScopeCleanupTimestamp + ");" +
"set :" + DbSyncSession.SyncRowCount + " = ::rowcount";
updScopeCleanupInfoCmd.Parameters.Add(":" + DbSyncSession.SyncScopeCleanupTimestamp, OracleType.Number);
updScopeCleanupInfoCmd.Parameters.Add(":" + DbSyncSession.SyncScopeName, OracleType.NVarChar, 100);
updScopeCleanupInfoCmd.Parameters.Add(":" + DbSyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output;
provider.UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;
//Register the BatchSpooled and BatchApplied events. These are fired when a provider is either enumerating or applying changes in batches.
provider.BatchApplied += new EventHandler <DbBatchAppliedEventArgs>(provider_BatchApplied);
provider.BatchSpooled += new EventHandler <DbBatchSpooledEventArgs>(provider_BatchSpooled);
return(provider);
}
InternalGetSnapshotAsync(ServerScopeInfo serverScopeInfo, SyncContext context, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
await using var runner = await this.GetConnectionAsync(context, SyncMode.Reading, SyncStage.ScopeLoading, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
// Make a remote call to get Schema from remote provider
if (serverScopeInfo.Schema == null)
{
(context, serverScopeInfo) = await this.InternalGetServerScopeInfoAsync(
context, null, false, runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);
serverScopeInfo.Schema.EnsureSchema();
}
// Generate a batch directory
var batchDirectoryRoot = this.Options.BatchDirectory;
var batchDirectoryName = string.Concat(DateTime.UtcNow.ToString("yyyy_MM_dd_ss"), Path.GetRandomFileName().Replace(".", ""));
var batchDirectoryFullPath = Path.Combine(batchDirectoryRoot, batchDirectoryName);
if (!Directory.Exists(batchDirectoryFullPath))
{
Directory.CreateDirectory(batchDirectoryFullPath);
}
// Create the BatchInfo serialized (forced because in a snapshot call, so we are obviously serialized on disk)
var serverBatchInfo = new BatchInfo(serverScopeInfo.Schema, batchDirectoryRoot, batchDirectoryName);
// Firstly, get the snapshot summary
var changesToSend = new HttpMessageSendChangesRequest(context, null);
var serializer = this.SerializerFactory.GetSerializer <HttpMessageSendChangesRequest>();
var binaryData = await serializer.SerializeAsync(changesToSend);
var response0 = await this.httpRequestHandler.ProcessRequestAsync(
this.HttpClient, context, this.ServiceUri, binaryData, HttpStep.GetSummary,
this.SerializerFactory, this.Converter, 0, this.SyncPolicy, cancellationToken, progress).ConfigureAwait(false);
HttpMessageSummaryResponse summaryResponseContent = null;
using (var streamResponse = await response0.Content.ReadAsStreamAsync().ConfigureAwait(false))
{
var responseSerializer = this.SerializerFactory.GetSerializer <HttpMessageSummaryResponse>();
if (streamResponse.CanRead)
{
summaryResponseContent = await responseSerializer.DeserializeAsync(streamResponse);
serverBatchInfo.RowsCount = summaryResponseContent.BatchInfo?.RowsCount ?? 0;
if (summaryResponseContent.BatchInfo?.BatchPartsInfo != null)
{
foreach (var bpi in summaryResponseContent.BatchInfo.BatchPartsInfo)
{
serverBatchInfo.BatchPartsInfo.Add(bpi);
}
}
}
}
if (summaryResponseContent == null)
{
throw new Exception("Summary can't be null");
}
context = summaryResponseContent.SyncContext;
// no snapshot
if ((serverBatchInfo.BatchPartsInfo == null || serverBatchInfo.BatchPartsInfo.Count <= 0) && serverBatchInfo.RowsCount <= 0)
{
return(context, 0, null, new DatabaseChangesSelected());
}
// If we have a snapshot we are raising the batches downloading process that will occurs
await this.InterceptAsync(new HttpBatchesDownloadingArgs(context, serverBatchInfo, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
await serverBatchInfo.BatchPartsInfo.ForEachAsync(async bpi =>
{
var changesToSend3 = new HttpMessageGetMoreChangesRequest(context, bpi.Index);
var serializer3 = this.SerializerFactory.GetSerializer <HttpMessageGetMoreChangesRequest>();
var binaryData3 = await serializer3.SerializeAsync(changesToSend3);
await this.InterceptAsync(new HttpGettingServerChangesRequestArgs(bpi.Index, serverBatchInfo.BatchPartsInfo.Count, summaryResponseContent.SyncContext, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
var response = await this.httpRequestHandler.ProcessRequestAsync(
this.HttpClient, context, this.ServiceUri, binaryData3, HttpStep.GetMoreChanges,
this.SerializerFactory, this.Converter, 0, this.SyncPolicy, cancellationToken, progress).ConfigureAwait(false);
if (this.SerializerFactory.Key != "json")
{
var s = this.SerializerFactory.GetSerializer <HttpMessageSendChangesResponse>();
using var responseStream = await response.Content.ReadAsStreamAsync().ConfigureAwait(false);
var getMoreChanges = await s.DeserializeAsync(responseStream);
if (getMoreChanges != null && getMoreChanges.Changes != null && getMoreChanges.Changes.HasRows)
{
var localSerializer = new LocalJsonSerializer();
var interceptorsWriting = this.interceptors.GetInterceptors <SerializingRowArgs>();
if (interceptorsWriting.Count > 0)
{
localSerializer.OnWritingRow(async(syncTable, rowArray) =>
{
var args = new SerializingRowArgs(context, syncTable, rowArray);
await this.InterceptAsync(args, progress, cancellationToken).ConfigureAwait(false);
return(args.Result);
});
}
// Should have only one table
var table = getMoreChanges.Changes.Tables[0];
var schemaTable = DbSyncAdapter.CreateChangesTable(serverScopeInfo.Schema.Tables[table.TableName, table.SchemaName]);
var fullPath = Path.Combine(batchDirectoryFullPath, bpi.FileName);
// open the file and write table header
await localSerializer.OpenFileAsync(fullPath, schemaTable).ConfigureAwait(false);
foreach (var row in table.Rows)
{
await localSerializer.WriteRowToFileAsync(new SyncRow(schemaTable, row), schemaTable).ConfigureAwait(false);
}
// Close file
await localSerializer.CloseFileAsync(fullPath, schemaTable).ConfigureAwait(false);
}
}
else
{
// Serialize
await SerializeAsync(response, bpi.FileName, batchDirectoryFullPath, this).ConfigureAwait(false);
}
// Raise response from server containing a batch changes
await this.InterceptAsync(new HttpGettingServerChangesResponseArgs(serverBatchInfo, bpi.Index, bpi.RowsCount, summaryResponseContent.SyncContext, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
});
await this.InterceptAsync(new HttpBatchesDownloadedArgs(summaryResponseContent, summaryResponseContent.SyncContext, this.GetServiceHost()), progress, cancellationToken).ConfigureAwait(false);
return(context, summaryResponseContent.RemoteClientTimestamp, serverBatchInfo, summaryResponseContent.ServerChangesSelected);
}
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 FirebirdDbSyncProvider()
{
// We need to modify this particular column name because the default is longer than Firebird allows.
this.ScopeForgottenKnowledgeColName = "scope_forgotten_knowledge";
for (int i = 0; i < SyncUtils.SyncAdapterTables.Length; i++)
{
//Add each table as a DbSyncAdapter to the provider
DbSyncAdapter adapter = new DbSyncAdapter(SyncUtils.SyncAdapterTables[i]);
adapter.RowIdColumns.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i]);
// select incremental changes command
FbCommand chgsOrdersCmd = new FbCommand();
chgsOrdersCmd.CommandType = CommandType.StoredProcedure;
chgsOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_selectchanges";
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, FbDbType.Integer); // TODO: timestamp?
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncMetadataOnly, FbDbType.Integer);
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, FbDbType.Integer);
// chgsOrdersCmd.Parameters.Add("sync_changes", FbDbType.Cursor).Direction = ParameterDirection.Output;
adapter.SelectIncrementalChangesCommand = chgsOrdersCmd;
// insert row command
FbCommand insOrdersCmd = new FbCommand();
insOrdersCmd.CommandType = CommandType.StoredProcedure;
insOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applyinsert";
if (SyncUtils.SyncAdapterTables[i] == "order_details")
{
insOrdersCmd.Parameters.Add("order_id", FbDbType.Integer);
}
insOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
if (SyncUtils.SyncAdapterTables[i] == "orders")
{
insOrdersCmd.Parameters.Add("order_date", FbDbType.Date);
}
else
{
insOrdersCmd.Parameters.Add("product", FbDbType.VarChar, 100);
insOrdersCmd.Parameters.Add("quantity", FbDbType.Integer);
}
insOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.InsertCommand = insOrdersCmd;
// update row command
FbCommand updOrdersCmd = new FbCommand();
updOrdersCmd.CommandType = CommandType.StoredProcedure;
updOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applyupdate";
if (SyncUtils.SyncAdapterTables[i] == "order_details")
{
updOrdersCmd.Parameters.Add("order_id", FbDbType.Integer);
}
updOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
if (SyncUtils.SyncAdapterTables[i] == "orders")
{
updOrdersCmd.Parameters.Add("order_date", FbDbType.Date);
}
else
{
updOrdersCmd.Parameters.Add("quantity", FbDbType.Integer);
updOrdersCmd.Parameters.Add("product", FbDbType.VarChar, 100);
}
updOrdersCmd.Parameters.Add(DbSyncSession.SyncForceWrite, FbDbType.Integer);
updOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, FbDbType.Integer);
updOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.UpdateCommand = updOrdersCmd;
// delete row command
FbCommand delOrdersCmd = new FbCommand();
delOrdersCmd.CommandType = CommandType.StoredProcedure;
delOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applydelete";
delOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
delOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, FbDbType.Integer);
delOrdersCmd.Parameters.Add(DbSyncSession.SyncForceWrite, FbDbType.Integer);
delOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.DeleteCommand = delOrdersCmd;
// get row command
FbCommand selRowOrdersCmd = new FbCommand();
selRowOrdersCmd.CommandType = CommandType.StoredProcedure;
selRowOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_selectrow";
selRowOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
selRowOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, FbDbType.Integer);
// selRowOrdersCmd.Parameters.Add("selectedRow", FbDbType.Cursor).Direction = ParameterDirection.Output;
adapter.SelectRowCommand = selRowOrdersCmd;
// insert row metadata command
FbCommand insMetadataOrdersCmd = new FbCommand();
insMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
insMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_insert_md";
insMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerKey, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerTimestamp, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerKey, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerTimestamp, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowIsTombstone, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.InsertMetadataCommand = insMetadataOrdersCmd;
// update row metadata command
FbCommand updMetadataOrdersCmd = new FbCommand();
updMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
updMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_update_md";
updMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowIsTombstone, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerKey, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerTimestamp, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerKey, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerTimestamp, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.UpdateMetadataCommand = updMetadataOrdersCmd;
// delete row metadata command
FbCommand delMetadataOrdersCmd = new FbCommand();
delMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
delMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_delete_md";
delMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, FbDbType.Integer);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, FbDbType.Integer);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.DeleteMetadataCommand = delMetadataOrdersCmd;
// get tombstones for cleanup
FbCommand selTombstonesOrdersCmd = new FbCommand();
selTombstonesOrdersCmd.CommandType = CommandType.StoredProcedure;
selTombstonesOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_select_ts";
selTombstonesOrdersCmd.Parameters.Add("tombstone_aging_in_hours", FbDbType.Integer).Value = SyncUtils.TombstoneAgingInHours;
selTombstonesOrdersCmd.Parameters.Add("sync_scope_local_id", FbDbType.Integer);
adapter.SelectMetadataForCleanupCommand = selTombstonesOrdersCmd;
SyncAdapters.Add(adapter);
}
// 2. Setup provider wide commands
// There are few commands on the provider itself and not on a table sync adapter:
// SelectNewTimestampCommand: Returns the new high watermark for current sync
// SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge and scope version (timestamp)
// UpdateScopeInfoCommand: Sets the new values for sync knowledge and cleanup knowledge
//
FbCommand anchorCmd = new FbCommand();
anchorCmd.CommandType = CommandType.StoredProcedure;
anchorCmd.CommandText = "SP_GET_TIMESTAMP"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"
anchorCmd.Parameters.Add(DbSyncSession.SyncNewTimestamp, FbDbType.Integer).Direction = ParameterDirection.Output; // TODO: Should this be BigInt
SelectNewTimestampCommand = anchorCmd;
//
// Select local replica info
//
RecreateSelectScope();
//
// Update local replica info
//
FbCommand updReplicaInfoCmd = new FbCommand();
updReplicaInfoCmd.CommandType = CommandType.StoredProcedure;
updReplicaInfoCmd.CommandText = "SP_UPDATE_SCOPE_INFO";
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeKnowledge, FbDbType.Binary, 10000);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeId, FbDbType.Guid);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeCleanupKnowledge, FbDbType.Binary, 10000);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeName, FbDbType.VarChar, 100);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, FbDbType.Integer);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeTimestamp, FbDbType.Integer);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
UpdateScopeInfoCommand = updReplicaInfoCmd;
//
// Select overlapping scopes
//
// get tombstones for cleanup
FbCommand overlappingScopesCmd = new FbCommand();
overlappingScopesCmd.CommandType = CommandType.StoredProcedure;
overlappingScopesCmd.CommandText = "sp_select_shared_scopes";
overlappingScopesCmd.Parameters.Add(DbSyncSession.SyncScopeName, FbDbType.VarChar, 100);
SelectOverlappingScopesCommand = overlappingScopesCmd;
//
// Update table cleanup info
//
FbCommand updScopeCleanupInfoCmd = new FbCommand();
updScopeCleanupInfoCmd.CommandType = CommandType.Text;
updScopeCleanupInfoCmd.CommandText = "update scope_info set " +
"scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp + " " +
"where scope_name = @" + DbSyncSession.SyncScopeName + " and " +
"(scope_cleanup_timestamp is null or scope_cleanup_timestamp < @" + DbSyncSession.SyncScopeCleanupTimestamp + "2);" +
"@" + DbSyncSession.SyncRowCount + " = ROW_COUNT;";
updScopeCleanupInfoCmd.Parameters.Add(DbSyncSession.SyncScopeCleanupTimestamp, FbDbType.Integer);
updScopeCleanupInfoCmd.Parameters.Add(DbSyncSession.SyncScopeName, FbDbType.VarChar, 100);
updScopeCleanupInfoCmd.Parameters.Add(DbSyncSession.SyncScopeCleanupTimestamp + "2", FbDbType.Integer);
updScopeCleanupInfoCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;
}
/// <summary>
/// Get changes from
/// </summary>
internal async Task <HttpMessageSendChangesResponse> ApplyThenGetChangesAsync(HttpMessageSendChangesRequest httpMessage, SessionCache sessionCache,
int clientBatchSize, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
// Overriding batch size options value, coming from client
// having changes from server in batch size or not is decided by the client.
// Basically this options is not used on the server, since it's always overriden by the client
this.Options.BatchSize = clientBatchSize;
// Get if we need to serialize data or making everything in memory
var clientWorkInMemory = clientBatchSize == 0;
// Get context from request message
var ctx = httpMessage.SyncContext;
// Set the context coming from the client
this.SetContext(ctx);
// Check schema.
// If client has stored the schema, the EnsureScope will not be called on server.
if (this.Schema == null || !this.Schema.HasTables || !this.Schema.HasColumns)
{
var serverScopeInfo = await this.EnsureSchemaAsync(cancellationToken, progress).ConfigureAwait(false);
this.Schema = serverScopeInfo.Schema;
this.Schema.EnsureSchema();
}
// ------------------------------------------------------------
// FIRST STEP : receive client changes
// ------------------------------------------------------------
// We are receiving changes from client
// BatchInfo containing all BatchPartInfo objects
// Retrieve batchinfo instance if exists
// Get batch info from session cache if exists, otherwise create it
if (sessionCache.ClientBatchInfo == null)
{
sessionCache.ClientBatchInfo = new BatchInfo(clientWorkInMemory, Schema, this.Options.BatchDirectory);
}
// create the in memory changes set
var changesSet = new SyncSet();
foreach (var table in httpMessage.Changes.Tables)
{
DbSyncAdapter.CreateChangesTable(Schema.Tables[table.TableName, table.SchemaName], changesSet);
}
changesSet.ImportContainerSet(httpMessage.Changes, false);
// If client has made a conversion on each line, apply the reverse side of it
if (this.ClientConverter != null && changesSet.HasRows)
{
AfterDeserializedRows(changesSet, this.ClientConverter);
}
// add changes to the batch info
await sessionCache.ClientBatchInfo.AddChangesAsync(changesSet, httpMessage.BatchIndex, httpMessage.IsLastBatch, this);
// Clear the httpMessage set
if (!clientWorkInMemory && httpMessage.Changes != null)
{
httpMessage.Changes.Clear();
}
// Until we don't have received all the batches, wait for more
if (!httpMessage.IsLastBatch)
{
return(new HttpMessageSendChangesResponse(httpMessage.SyncContext)
{
ServerStep = HttpStep.SendChangesInProgress
});
}
// ------------------------------------------------------------
// SECOND STEP : apply then return server changes
// ------------------------------------------------------------
// get changes
var(remoteClientTimestamp, serverBatchInfo, _, clientChangesApplied, serverChangesSelected) =
await this.ApplyThenGetChangesAsync(httpMessage.Scope, sessionCache.ClientBatchInfo, cancellationToken, progress).ConfigureAwait(false);
// Save the server batch info object to cache if not working in memory
if (!clientWorkInMemory)
{
sessionCache.RemoteClientTimestamp = remoteClientTimestamp;
sessionCache.ServerBatchInfo = serverBatchInfo;
sessionCache.ServerChangesSelected = serverChangesSelected;
sessionCache.ClientChangesApplied = clientChangesApplied;
}
// Get the firt response to send back to client
return(await GetChangesResponseAsync(ctx, remoteClientTimestamp, serverBatchInfo, clientChangesApplied, serverChangesSelected, 0));
}
