//Get Data From Client Provision
public static void ProvisionClient()
{
SqlConnection serverConn = new SqlConnection(sServerConnection);
SqlConnection clientConn = new SqlConnection(sClientConnection);
//Drop scope_Info Table
string cmdText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='scope_info') DROP table scope_info";
clientConn.Open();
SqlCommand cmd = new SqlCommand(cmdText, clientConn);
cmd.ExecuteScalar();
clientConn.Close();
List <string> tables = new List <string>();
// tables.Add("Accounts"); // Add Tables in List
// tables.Add("Drinks");
// tables.Add("Roles");
// tables.Add("Tables");
tables.Add("Toppings");
var scopeDesc = new DbSyncScopeDescription("MainScope");
foreach (var tbl in tables) //Add Tables in Scope
{
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tbl, clientConn));
}
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc); //Provisioning
//skip creating the user tables
clientProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
//skip creating the change tracking tables
clientProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);
//skip creating the change tracking triggers
clientProvision.SetCreateTriggersDefault(DbSyncCreationOption.Skip);
//skip creating the insert/update/delete/selectrow SPs including those for metadata
clientProvision.SetCreateProceduresDefault(DbSyncCreationOption.Skip);
//create new SelectChanges SPs for selecting changes for the new scope
//the new SelectChanges SPs will have a guid suffix
clientProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
clientProvision.Apply();
}
public static void SetUp(string _pTableName)
{
// Connection to SQL Server database
SqlConnection serverConn =
new SqlConnection(ServerConnString);
// Connection to SQL client database
SqlConnection clientConn =
new SqlConnection(ClientConnString);
// Create a scope named "product" and add tables to it.
Console.WriteLine(_pTableName);
DbSyncScopeDescription productScope = new DbSyncScopeDescription(_pTableName + "_SCOP");
// Define the Products table.
DbSyncTableDescription productDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(_pTableName, serverConn);
// Add the Table to the scope object.
productScope.Tables.Add(productDescription);
// Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);
serverProvision.ObjectSchema = ".dbo";
serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
serverProvision.SetCreateTriggersDefault(DbSyncCreationOption.CreateOrUseExisting);
if (!serverProvision.ScopeExists(_pTableName + "_SCOP"))
{
serverProvision.Apply();
}
// Provision the SQL client database from the on-premise SQL Server database if one does not exist.
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);
if (!clientProvision.ScopeExists(_pTableName + "_SCOP"))
{
clientProvision.Apply();
}
// Shut down database connections.
serverConn.Close();
serverConn.Dispose();
clientConn.Close();
clientConn.Dispose();
}
//Set Data To Server Provision
public static void ProvisionServer()
{
SqlConnection serverConn = new SqlConnection(sServerConnection);
string cmdText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='scope_info') DROP table scope_info";
serverConn.Open();
SqlCommand cmd = new SqlCommand(cmdText, serverConn);
cmd.ExecuteScalar();
serverConn.Close();
List <string> tables = new List <string>();
// tables.Add("Accounts"); // Add Tables in List
// tables.Add("Drinks");
// tables.Add("Roles");
// tables.Add("Tables");
tables.Add("Toppings");
var scopeDesc = new DbSyncScopeDescription("MainScope");
foreach (var tbl in tables)
{
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tbl, serverConn));
}
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc); // Create Provision From All Tables
//skip creating the user tables
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
//skip creating the change tracking tables
serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);
//skip creating the change tracking triggers
serverProvision.SetCreateTriggersDefault(DbSyncCreationOption.Skip);
//skip creating the insert/update/delete/selectrow SPs including those for metadata
serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.Skip);
serverProvision.Apply();
}
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();
}
}
}