private void RefreshServerProvider()
{
//Configure the SQL Server sync provider
OracleDbSyncProvider serverSyncProvider = synchronizationHelper.ConfigureDBSyncProvider(this.oracleConnectionString);
//Add the server provider to the collection
providersCollection["Server"] = serverSyncProvider;
}
/// <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);
}
