static void Main(string[] args)
{
// Create the connections over which provisioning and sycnhronization
// are performed. The Utility class handles all functionality that is not
//directly related to synchronization, such as holding connection
//string information and making changes to the server database.
SqlConnection serverConn = new SqlConnection(Utility.ServerConnString);
SqlConnection clientSqlConn = new SqlConnection(Utility.ClientSqlConnString);
SqlCeConnection clientSqlCeConn = new SqlCeConnection(Utility.ClientSqlCeConnString);
// Create a scope named "filtered_customer", and add two tables to the scope.
// GetDescriptionForTable gets the schema of each table, so that tracking
// tables and triggers can be created for that table.
//<snippetOCSv3_CS_Basic_SqlPeer_ScopeDesc>
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
scopeDesc.Tables.Add(
SqlSyncDescriptionBuilder.GetDescriptionForTable("Customer", serverConn));
scopeDesc.Tables.Add(
SqlSyncDescriptionBuilder.GetDescriptionForTable("CustomerContact", serverConn));
//</snippetOCSv3_CS_Basic_SqlPeer_ScopeDesc>
// Create a provisioning object for "filtered_customer" and specify that
// base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1).
//<snippetOCSv3_CS_Basic_SqlPeer_ServerConfig>
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.CreateTableDefault = DbSyncCreationOption.Skip;
// Specify which column(s) in the Customer table to use for filtering data,
// and the filtering clause to use against the tracking table.
// "[side]" is an alias for the tracking table.
serverConfig["Customer"].AddFilterColumn("CustomerType");
serverConfig["Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";
// Configure the scope and change tracking infrastructure.
serverConfig.Apply(serverConn);
// Write the configuration script to a file. You can modify
// this script if necessary and run it against the server
// to customize behavior.
File.WriteAllText("SampleConfigScript.txt",
serverConfig.Script("SyncSamplesDb_SqlPeer1"));
//</snippetOCSv3_CS_Basic_SqlPeer_ServerConfig>
// Retrieve scope information from the server and use the schema that is retrieved
// to provision the SQL Server and SQL Server Compact client databases.
//<snippetOCSv3_CS_Basic_SqlPeer_ClientConfig>
// This database already exists on the server.
DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", serverConn);
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlDesc);
clientSqlConfig.Apply(clientSqlConn);
// This database does not yet exist.
Utility.CreateSqlCeDatabase();
DbSyncScopeDescription clientSqlCeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", serverConn);
SqlCeSyncScopeProvisioning clientSqlCeConfig = new SqlCeSyncScopeProvisioning(clientSqlCeDesc);
clientSqlCeConfig.Apply(clientSqlCeConn);
//</snippetOCSv3_CS_Basic_SqlPeer_ClientConfig>
// Initial synchronization sessions. 7 rows are synchronized:
// all rows (4) from CustomerContact, and the 3 rows from Customer
// that satisfy the filtering criteria.
//<snippetOCSv3_CS_Basic_SqlPeer_InitialSync>
SampleSyncOrchestrator syncOrchestrator;
SyncOperationStatistics syncStats;
// Data is downloaded from the server to the SQL Server client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn),
new SqlSyncProvider("filtered_customer", serverConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Data is downloaded from the SQL Server client to the
// SQL Server Compact client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCeConn),
new SqlSyncProvider("filtered_customer", clientSqlConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
//</snippetOCSv3_CS_Basic_SqlPeer_InitialSync>
// Make changes on the server: 1 insert, 1 update, and 1 delete.
Utility.MakeDataChangesOnServer();
// Synchronize again. Three changes were made on the server, but
// only two of them applied to rows that are in the "filtered_customer"
// scope. The other row is not synchronized.
// Notice that the order of synchronization is different from the initial
// sessions, but the two changes are propagated to all nodes.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCeConn),
new SqlSyncProvider("filtered_customer", serverConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn),
new SqlCeSyncProvider("filtered_customer", clientSqlCeConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
serverConn.Close();
serverConn.Dispose();
clientSqlConn.Close();
clientSqlConn.Dispose();
clientSqlCeConn.Close();
clientSqlCeConn.Dispose();
Console.Write("\nPress any key to exit.");
Console.Read();
}
private void UpdateExistingScopeProvision(SqlSyncScopeProvisioning provision, SqlConnection conn, bool isServer)
{
string alterScopeSql = string.Empty;
provision.SetCreateProceduresDefault(DbSyncCreationOption.Create);
provision.SetUseBulkProceduresDefault(true);
provision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
var provisioningScript = provision.Script();
//alterScopeSql = provision.Script();
var stringBuilder = new StringBuilder();
var changedTables = tables.Where(t => t.InScope.Value == 1).ToList();
foreach (var changedTable in changedTables)
{
var tableName = isServer ? changedTable.ServerTableName : changedTable.ClientTableName;
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_bulkinsert];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_bulkupdate];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_bulkdelete];");
stringBuilder.AppendLine("DROP TYPE [" + tableName + "_BulkType];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_selectchanges];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_selectrow];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_insert];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_update];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_delete];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_insertmetadata];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_updatemetadata];");
stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_deletemetadata];");
}
// append the sync provisioning script after the drop statements
alterScopeSql = stringBuilder.Append(provisioningScript).ToString();
int x = alterScopeSql.IndexOf("N'<SqlSyncProviderScopeConfiguration");
int y = alterScopeSql.IndexOf("</SqlSyncProviderScopeConfiguration>");
var configEntry = alterScopeSql.Substring(x, y - x) + "</SqlSyncProviderScopeConfiguration>'";
x = alterScopeSql.IndexOf("- BEGIN Add scope");
y = alterScopeSql.IndexOf("- END Add Scope");
alterScopeSql = alterScopeSql.Remove(x, y - x);
alterScopeSql = alterScopeSql.Replace("scope_status = 'C'", "config_data=" + configEntry);
x = alterScopeSql.IndexOf("WHERE [config_id] =");
alterScopeSql = alterScopeSql.Remove(x, alterScopeSql.Length - x);
alterScopeSql = alterScopeSql
+ " WHERE [config_id] = (SELECT scope_config_id FROM scope_info WHERE sync_scope_name='"
+ configuration.ScopeName + "')";
using (var connection = new SqlConnection(conn.ConnectionString))
{
connection.Open();
string[] commands = alterScopeSql.Split(new string[] { "GO\r\n", "GO ", "GO\t", "GO" }, StringSplitOptions.RemoveEmptyEntries);
foreach (var c in commands)
{
var command = new SqlCommand(c, connection);
command.ExecuteNonQuery();
}
}
}
private static void Provision(SelectedConfigSections selectedConfig, DbSyncScopeDescription scopeDescription, bool script, DirectoryInfo workingDirectory)
{
try
{
SqlSyncScopeProvisioning prov =
new SqlSyncScopeProvisioning(new SqlConnection(selectedConfig.SelectedTargetDatabase.GetConnectionString()),
scopeDescription,
selectedConfig.SelectedSyncScope.IsTemplateScope
? SqlSyncScopeProvisioningType.Template
: SqlSyncScopeProvisioningType.Scope);
// Note: Deprovisioning does not work because of a bug in the provider when you set the ObjectSchema property to “dbo”.
// The workaround is to not set the property (it internally assumes dbo in this case) so that things work on deprovisioning.
if (!String.IsNullOrEmpty(selectedConfig.SelectedSyncScope.SchemaName))
{
prov.ObjectSchema = selectedConfig.SelectedSyncScope.SchemaName;
}
foreach (SyncTableConfigElement tableElement in selectedConfig.SelectedSyncScope.SyncTables)
{
// Check and set the SchemaName for individual table if specified
if (!string.IsNullOrEmpty(tableElement.SchemaName))
{
prov.Tables[tableElement.GlobalName].ObjectSchema = tableElement.SchemaName;
}
prov.Tables[tableElement.GlobalName].FilterClause = tableElement.FilterClause;
foreach (FilterColumnConfigElement filterCol in tableElement.FilterColumns)
{
prov.Tables[tableElement.GlobalName].FilterColumns.Add(
scopeDescription.Tables[tableElement.GlobalName].Columns[filterCol.Name]);
}
foreach (FilterParameterConfigElement filterParam in tableElement.FilterParameters)
{
CheckFilterParamTypeAndSize(filterParam);
prov.Tables[tableElement.GlobalName].FilterParameters.Add(new SqlParameter(filterParam.Name,
(SqlDbType)Enum.Parse(typeof(SqlDbType), filterParam.SqlType, true)));
prov.Tables[tableElement.GlobalName].FilterParameters[filterParam.Name].Size = filterParam.DataSize;
}
}
// enable bulk procedures.
prov.SetUseBulkProceduresDefault(selectedConfig.SelectedSyncScope.EnableBulkApplyProcedures);
// Create a new set of enumeration stored procs per scope.
// Without this multiple scopes share the same stored procedure which is not desirable.
prov.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
if (selectedConfig.SelectedSyncScope.IsTemplateScope)
{
if (!script)
{
if (!prov.TemplateExists(selectedConfig.SelectedSyncScope.Name))
{
Log("Provisioning Database {0} for template scope {1}...",
selectedConfig.SelectedTargetDatabase.Name,
selectedConfig.SelectedSyncScope.Name);
prov.Apply();
}
else
{
throw new InvalidOperationException(
string.Format(
"Database {0} already contains a template scope {1}. Please deprovision the scope and retry.",
selectedConfig.SelectedTargetDatabase.Name,
selectedConfig.SelectedSyncScope.Name));
}
}
else
{
SaveScript("provision.sql", prov.Script(), workingDirectory);
}
}
else
{
if (!script)
{
if (!prov.ScopeExists(selectedConfig.SelectedSyncScope.Name))
{
Log("Provisioning Database {0} for scope {1}...", selectedConfig.SelectedTargetDatabase.Name,
selectedConfig.SelectedSyncScope.Name);
prov.Apply();
}
else
{
throw new InvalidOperationException(
string.Format(
"Database {0} already contains a scope {1}. Please deprovision the scope and retry.",
selectedConfig.SelectedTargetDatabase.Name,
selectedConfig.SelectedSyncScope.Name));
}
}
else
{
SaveScript("provision.sql", prov.Script(), workingDirectory);
}
}
}
catch (ConfigurationErrorsException)
{
throw;
}
catch (InvalidOperationException)
{
throw;
}
catch (Exception e)
{
throw new InvalidOperationException(
"Unexpected error when executing the Provisioning command. See inner exception for details.", e);
}
}