public void ProvisionServer()
{
SqlConnection serverConn = new SqlConnection(sServerConnection);
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(sScope);
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("CUSTOMER", serverConn);
scopeDesc.Tables.Add(tableDesc);
DbSyncTableDescription productDescription2 =
SqlSyncDescriptionBuilder.GetDescriptionForTable("MOB",
serverConn);
scopeDesc.Tables.Add(productDescription2);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
if (!serverProvision.ScopeExists(sScope))
{
serverProvision.Apply();
}
}
static void Main(string[] args)
{
//create connection to the server DB
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// define the OrdersScope-NC filtered scope
// this scope filters records in the Orders table with OriginState set to NC"
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope-NC");
// get the description of the Orders table and add it to the scope
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
scopeDesc.Tables.Add(tableDesc);
// create server provisioning object
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// no need to create the Orders table since it already exists,
// so use the Skip parameter
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// set the filter column on the Orders table to OriginState
serverProvision.Tables["Orders"].AddFilterColumn("OriginState");
// set the filter value to NC
serverProvision.Tables["Orders"].FilterClause = "[side].[OriginState] = 'NC'";
// start the provisioning process
serverProvision.Apply();
}
static void Main(string[] args)
{
string strscopename = Properties.Settings.Default["ScopeName"].ToString();
string strclientConn = Properties.Settings.Default["clientConn"].ToString();
string strserverConn = Properties.Settings.Default["serverConn"].ToString();
try
{
SqlConnection clientConn = new SqlConnection(@strclientConn);
SqlConnection serverConn = new SqlConnection(@strserverConn);
// Obtain the description of VLSummaryReport table from the server using the scope name
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(strscopename, serverConn);
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
clientProvision.CommandTimeout = 180000;
clientProvision.Apply();
Console.WriteLine("Client Successfully Provisioned.");
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + ex.Data.ToString());
Console.ReadLine();
}
}
public void ActivateSqlSync()
{
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=Balda; Integrated Security=True");
// Определить новую область с именем ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("BaldaScope");
// Получаем описание таблицы Изделия из SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Dictionary", serverConn);
// Добавить описание таблицы для определения синхронизации области видимости
scopeDesc.Tables.Add(tableDesc);
// List<DbSyncTableDescription> tableDescD = new List<DbSyncTableDescription>();
for (int i = 3; i < MaxLengthWord; i++)
{
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Dictionary" + i, serverConn));
}
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
}
public static void ProvisionTableOnClient(string pScopeName, string pProviderConnectionString, string pClientConnectionString)
{
try
{
// create a connection to the SyncExpressDB database
SqlConnection clientConn = new SqlConnection(pClientConnectionString);
// create a connection to the SyncDB server database
SqlConnection serverConn = new SqlConnection(pProviderConnectionString);
// connection string for Eskimos test
//SqlConnection serverConn = new SqlConnection("Data Source=q6.2eskimos.com; Initial Catalog=EskLeeTest; uid=test ; pwd=test1test");
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(pScopeName, serverConn);
// create server provisioning object based on the ProductsScope
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
}
catch (Exception e)
{
string tempErrorMessage = "There was an exception whilst creating a client provision: " + e;
Debug.WriteLine(tempErrorMessage);
Logs.ProvisioningLog.WriteLine(tempErrorMessage);
throw e;
}
}
private void ProvisionServer(string TableName)
{
try
{
// define a new scope named tableNameScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(TableName + _filter);
// get the description of the tableName
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(TableName, _serverConnection);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the tableNameScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(_serverConnection, scopeDesc);
// start the provisioning process
if (!serverProvision.ScopeExists(scopeDesc.ScopeName))
{
serverProvision.Apply();
//Console.WriteLine("Server " + TableName + " was provisioned.");
Log.WriteLogs("Server " + TableName + " was provisioned.");
}
else
{
//Console.WriteLine("Server " + TableName + " was already provisioned.");
Log.WriteLogs("Server " + TableName + " was already provisioned.");
}
}
catch (Exception ex)
{
Log.WriteErrorLogs(ex);
}
}
protected override void DefineLocalScope(DbSyncScopeDescription scopeDescription)
{
using (var connection = new SqlConnection(LocalConfiguration.ConnectionString))
{
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_currencies", connection);
tableDesc.GlobalName = "currencies";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_products", connection);
tableDesc.GlobalName = "products";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_product_cost", connection);
tableDesc.GlobalName = "product_cost";
//tableDesc.Columns.Remove(tableDesc.Columns["cost_tenge"]);
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_certifications", connection);
tableDesc.GlobalName = "certifications";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_certification_copies", connection);
tableDesc.GlobalName = "certification_copies";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_appendix", connection);
tableDesc.GlobalName = "appendix";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_appendix_series", connection);
tableDesc.GlobalName = "appendix_series";
scopeDescription.Tables.Add(tableDesc);
}
}
/// <summary>
/// Configure the SqlSyncprovider. Note that this method assumes you have a direct conection
/// to the server as this is more of a design time use case vs. runtime use case. We think
/// of provisioning the server as something that occurs before an application is deployed whereas
/// provisioning the client is somethng that happens during runtime (on intitial sync) after the
/// application is deployed.
///
/// </summary>
/// <param name="hostName"></param>
/// <returns></returns>
public SqlSyncProvider ConfigureSqlSyncProvider(string scopeName, string hostName)
{
SqlSyncProvider provider = new SqlSyncProvider();
provider.ApplyChangeFailed += new EventHandler <DbApplyChangeFailedEventArgs>(Provider_ApplyingChanges);
provider.ScopeName = scopeName;
SqlConn conn = new SqlConn();
provider.Connection = new SqlConnection(conn.connString);
MakeBackUp();
//create anew scope description and add the appropriate tables to this scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("CardsScope");
//class to be used to provision the scope defined above
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((SqlConnection)provider.Connection);
//determine if this scope already exists on the server and if not go ahead and provision
if (!serverConfig.ScopeExists("CardsScope"))
{
//add the approrpiate tables to this scope
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("[" + conn.schema + "].[cards]", (System.Data.SqlClient.SqlConnection)provider.Connection));
//note that it is important to call this after the tables have been added to the scope
serverConfig.PopulateFromScopeDescription(scopeDesc);
//indicate that the base table already exists and does not need to be created
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
//provision the server
serverConfig.Apply();
}
conn.close();
return(provider);
}
public DbSyncScopeDescription GetScopeDescription(string scopeName, string serverConnectionString)
{
this.peerProvider.Connection.ConnectionString = serverConnectionString;
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, (SqlConnection)this.dbProvider.Connection);
return(scopeDesc);
}
public static void ProvisionTableOnProvider(string pScopeName, string pTableName, string pProviderConnectionString)
{
try
{
// connect to server database
SqlConnection serverConn = new SqlConnection(pProviderConnectionString);
// connection string for Eskimos test
// SqlConnection serverConn = new SqlConnection("Data Source=q6.2eskimos.com; Initial Catalog=EskLeeTest; uid=test ; pwd=test1test");
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(pScopeName);
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(pTableName, serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Apply();
}
catch (Exception e)
{
string tempErrorMessage = "There was an exception whilst creating a provider provision: " + e;
Debug.WriteLine(tempErrorMessage);
Logs.ProvisioningLog.WriteLine(tempErrorMessage);
throw e;
}
}
/// <summary>
/// Kiểm tra có SCOPE chưa
/// </summary>
/// <param name="connectionString"></param>
/// <param name="scope_name"></param>
/// <returns></returns>
public static int isHasScope(String connectionString, String scope_name, String[] tracking_tables)
{
SqlConnection serverConn = new SqlConnection(connectionString);
if (!isExist(serverConn))
{
return(-1);
}
try
{
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope_name);
DbSyncTableDescription tableDesc = null;
foreach (String item in tracking_tables)
{
//parse
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(item, serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
}
SqlSyncScopeProvisioning tmp = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
return(tmp.ScopeExists(scope_name)?1:-1);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(-1);
}
}
private static void createScope(SqlConnection connection)
{
var scopeDesc = new DbSyncScopeDescription("SyncScope");
// Definition for Customer.
DbSyncTableDescription customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Company", connection);
scopeDesc.Tables.Add(customerDescription);
customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Contact", connection);
scopeDesc.Tables.Add(customerDescription);
// Create a provisioning object for "SyncScope". We specify that
// base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1),
// and that all synchronization-related objects should be created in a
// database schema named "Sync". If you specify a schema, it must already exist
// in the database.
var serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(connection);
connection.Close();
}
void provsisonDB(SqlConnection clientConn)
{
try
{
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("CardsScope");
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("cards", clientConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
}
catch (Exception exc)
{
Console.WriteLine(exc.ToString());
logger.Error(exc.ToString());
}
}
protected override void DefineRemoteScope(DbSyncScopeDescription scopeDescription)
{
using (var connection = new SqlConnection(RemoteConfiguration.ConnectionString))
{
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("currencies", connection);
tableDesc.GlobalName = "currencies";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("products", connection);
tableDesc.GlobalName = "products";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("product_cost", connection);
tableDesc.GlobalName = "product_cost";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("certifications", connection);
tableDesc.GlobalName = "certifications";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("certification_copies", connection);
tableDesc.GlobalName = "certification_copies";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("appendix", connection);
tableDesc.GlobalName = "appendix";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("appendix_series", connection);
tableDesc.GlobalName = "appendix_series";
scopeDescription.Tables.Add(tableDesc);
}
}
static void Main(string[] args)
{
// 客户端的连接.
// create a connection to the SyncExpressDB database
SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncExpressDB; Trusted_Connection=Yes");
// 服务端的连接.
// create a connection to the SyncDB server database
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// 从 服务器上， 获取 同步范围的数据.
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);
// 将同步范围的数据， 写入到客户端.
// create server provisioning object based on the ProductsScope
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
// 该应用程序，运行完毕后， 会在 SQL Server Express 服务器上面， 创建这些表：
// Products, Products_Tracking, schema_info, scope_config, scope_info
}
private static void Initialize
(string table,
string serverConnectionString,
string clientConnectionString)
{
try
{
using (SqlConnection serverConnection = new
SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection = new
SqlConnection(clientConnectionString))
{
DbSyncScopeDescription scopeDescription = new
DbSyncScopeDescription(table);
DbSyncTableDescription tableDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(table,
serverConnection);
scopeDescription.Tables.Add(tableDescription);
SqlSyncScopeProvisioning serverProvision = new
SqlSyncScopeProvisioning(serverConnection,
scopeDescription);
serverProvision.Apply();
SqlSyncScopeProvisioning clientProvision = new
SqlSyncScopeProvisioning(clientConnection,
scopeDescription);
clientProvision.Apply();
}
}
}
catch (Exception ex)
{
Common.WriteLog(System.DateTime.Now.ToString() + ": " + ex.ToString());
}
}
public static bool ConfigureSqlSyncProvider(ProvisionStruct provisionStruct)
{
SqlSyncProvider provider = null;
bool returnVal = false;
try
{
provider = new SqlSyncProvider();
provider.ScopeName = provisionStruct.scopeName;
provider.Connection = new SqlConnection(provisionStruct.connectionString);
//create a new scope description and add the appropriate tables to this scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(provider.ScopeName);
if (provisionStruct.tableNames != null)
{
foreach (var tableName in provisionStruct.tableNames)
{
var info = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, (SqlConnection)provider.Connection);
//FixPrimaryKeysForTable(info);
scopeDesc.Tables.Add(info);
}
}
//class to be used to provision the scope defined above
SqlSyncScopeProvisioning serverConfig = null;
serverConfig = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)provider.Connection);
if (serverConfig.ScopeExists(provisionStruct.scopeName))
{
return(false);
}
if (!serverConfig.ScopeExists(provisionStruct.scopeName))
{
//note that it is important to call this after the tables have been added to the scope
serverConfig.PopulateFromScopeDescription(scopeDesc);
//indicate that the base table already exists and does not need to be created
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
//provision the server
serverConfig.Apply();
returnVal = true;
}
}
catch
{
throw;
}
finally
{
//provider.Dispose();
}
return(returnVal);
}
public void DescribePartialTableSchema(string tableName, Collection <string> columnsToInclude)
// Definition for part of the table
{
DbSyncTableDescription customerContactDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, columnsToInclude, this.ServerConn);
this.ScopeDesc.Tables.Add(customerContactDescription);
}
public void DescribeTableSchema(string tableName)
// Definition for the whole table
{
DbSyncTableDescription customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, this.ServerConn);
this.ScopeDesc.Tables.Add(customerDescription);
}
private DbSyncScopeDescription CrearAmbito(String nombreDeAmbito, string TablaDentroDelAmbito, IDbConnection conexionSql)
{
DbSyncScopeDescription Ambito = new DbSyncScopeDescription(nombreDeAmbito);
DbSyncTableDescription descripcionDeTabla = SqlSyncDescriptionBuilder.GetDescriptionForTable(TablaDentroDelAmbito, (SqlConnection)conexionSql);
Ambito.Tables.Add(descripcionDeTabla);
return(Ambito);
}
public DbSyncScopeDescription GetScopeDescription()
{
Log("GetSchema: {0}", this.peerProvider.Connection.ConnectionString);
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(SyncUtils.ScopeName, (SqlConnection)this.dbProvider.Connection);
return(scopeDesc);
}
static void Main(string[] args)
{
SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\dev\SyncTest\SyncSQLServerAndSQLCompact\data\SyncCompactDB.sdf'");
SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQL2008; Initial Catalog=SyncDB; Integrated Security=True");
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
clientProvision.Apply();
}
private void tablesBox_ItemCheck(object sender, ItemCheckEventArgs e)
{
if (tablesBox.SelectedIndex > -1)
{
colsView.Rows.Clear();
var tableName = tablesBox.SelectedItem.ToString();
if (e.NewValue == CheckState.Unchecked)
{
// Remove it from the SyncTables collection
selectedScope.SyncTables.Remove(tableName);
filterClauseTxtBox.Text = string.Empty;
}
else if (e.NewValue == CheckState.Checked)
{
var table = new SyncTableConfigElement();
table.Name = tableName;
table.IncludeAllColumns = true;
var db = WizardHelper.Instance.SyncConfigSection.Databases.GetElementAt(dbsComboBox.SelectedIndex);
statusLbl.Visible = true;
try
{
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName,
new SqlConnection(db.GetConnectionString()));
// Issue a query to get list of all tables
foreach (var col in tableDesc.Columns)
{
var colConfig = new SyncColumnConfigElement
{
Name = col.UnquotedName,
IsPrimaryKey = col.IsPrimaryKey,
IsNullable = col.IsNullable,
SqlType = col.Type
};
table.SyncColumns.Add(colConfig);
}
DisplaySyncTableDetails(table);
}
catch (SqlException exp)
{
MessageBox.Show("Error in querying database. " + exp.Message, "Target Database Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
statusLbl.Visible = false;
}
// Add it to the sync table list
selectedScope.SyncTables.Add(table);
}
}
}
public DbSyncScopeDescription GetScopeDescription()
{
Log("GetSchema: {0}", sqlProvider.Connection.ConnectionString);
var scopeDesc =
SqlSyncDescriptionBuilder.GetDescriptionForScope(
sqlProvider.ScopeName, (SqlConnection)sqlProvider.Connection);
return(scopeDesc);
}
static void Main(string[] args)
{
SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQL2008; Initial Catalog=SyncDB; Integrated Security=true");
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope");
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConn);
scopeDesc.Tables.Add(tableDesc);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Apply();
}
//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 override void ProvisionTriggerAndProcedureUpdates()
{
Log.Info("[DistributedDb] ProvisionTriggerAndProcedureUpdates Client Start", this);
foreach (var db in base.Databases)
{
var clientConn = new SqlConnection(db.ClientConnectionString);
var serverConn = new SqlConnection(db.ServerConnectionString);
var scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(db.Scope, serverConn);
base.ProvisionTriggerAndProcedureUpdates(db, clientConn, scopeDesc);
}
Log.Info("[DistributedDb] ProvisionTriggerAndProcedureUpdates Client End", this);
}
static void Main(string[] args)
{
SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQL2008; Initial Catalog=SyncDB; Integrated Security=true");
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope-NC");
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
scopeDesc.Tables.Add(tableDesc);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Tables["Orders"].AddFilterColumn("OriginState");
serverProvision.Tables["Orders"].FilterClause = "[side].[OriginState] = 'NC'";
serverProvision.Apply();
}
public void TruncateClientTables()
{
Log.Info("[DistributedDb] TruncateClientTables Client Start", this);
foreach (var db in base.Databases)
{
var clientConn = new SqlConnection(db.ClientConnectionString);
var serverConn = new SqlConnection(db.ServerConnectionString);
var scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(db.Scope, serverConn);
base.TruncateTables(db.Tables, clientConn, scopeDesc);
}
Log.Info("[DistributedDb] TruncateClientTables Client End", this);
}
protected virtual void ProvisionSyncScope(SqlSyncScopeProvisioning serverProvision, string syncScope, ICollection <string> syncTables, SqlConnection serverConnect, SqlSyncScopeProvisioningType provisionType)
{
// Create a sync scope if it is not existed yet
if (!string.IsNullOrEmpty(syncScope) && syncTables != null && syncTables.Any())
{
// Check if the sync scope or template exists
if (provisionType == SqlSyncScopeProvisioningType.Scope && serverProvision.ScopeExists(syncScope))
{
return;
}
if (provisionType == SqlSyncScopeProvisioningType.Template && serverProvision.TemplateExists(syncScope))
{
return;
}
// Define a new sync scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(syncScope);
// Generate and add table descriptions to the sync scope
foreach (string tblName in syncTables)
{
// Get the description of a specific table
DbSyncTableDescription tblDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tblName, serverConnect);
// add the table description to the sync scope
scopeDesc.Tables.Add(tblDesc);
}
// Set the scope description from which the database should be provisioned
serverProvision.PopulateFromScopeDescription(scopeDesc);
if (provisionType == SqlSyncScopeProvisioningType.Template)
{
serverProvision.ObjectSchema = "Sync";
// apply dynamic filters
ApplyDynamicFilters(serverProvision, syncScope);
}
else
{
// apply static filters
ApplyStaticFilters(serverProvision, syncScope);
}
// Indicate that the base table already exists and does not need to be created
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
// start the provisioning process
serverProvision.Apply();
}
}