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();
}
/// <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 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();
}
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 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();
}
}
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);
}
}
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();
}
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;
}
}
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);
}
}
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);
}
}
/// <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);
}
}
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 DescribeTableSchema(string tableName)
// Definition for the whole table
{
DbSyncTableDescription customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, this.ServerConn);
this.ScopeDesc.Tables.Add(customerDescription);
}
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);
}
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);
}
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);
}
}
}
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();
}
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();
}
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();
}
}
protected void GetDescriptionForTables(List <Sitecore.Takeaway.DistributedDb.Configuration.SyncTable> tables, SqlConnection connection, ref DbSyncScopeDescription scopeDesc)
{
foreach (var table in tables)
{
var tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table.Name, connection);
if (table.PrimaryKeys != null)
{
foreach (var column in table.PrimaryKeys)
{
try
{
if (tableDesc.Columns[column] != null)
{
tableDesc.Columns[column].IsPrimaryKey = true;
}
}
catch (Exception ex)
{
Log.Error("[DistributedDb] primary key column error", ex, this);
}
}
}
if (table.PrimaryKeysToRemove != null)
{
foreach (var column in table.PrimaryKeysToRemove)
{
try
{
if (tableDesc.Columns[column] != null)
{
tableDesc.Columns[column].IsPrimaryKey = false;
}
}
catch (Exception ex)
{
Log.Error("[DistributedDb] primary key column error", ex, this);
}
}
}
if (!scopeDesc.Tables.Contains(tableDesc))
{
scopeDesc.Tables.Add(tableDesc);
}
}
}
private static void Initialize(string table, string serverConnectionString, string clientConnectionString)
{
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();
}
}
}
private DbSyncScopeDescription UpdateServerScopeDesc(DbSyncScopeDescription serverScopeDesc)
{
foreach (var table in tables)
{
var serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table.ServerTableName, configuration.ServerSqlConnection);
serverTableDesc.GlobalName = table.ServerTableName;
serverScopeDesc.Tables.Add(serverTableDesc);
var tableColumns = columns.Where(c => c.MatchingTableNameID == table.ID).Select(c => c.ServerColumnName).ToList();
var columnsForRemove = serverScopeDesc.Tables[table.ServerTableName].Columns.Select(c => c.UnquotedName).Except(tableColumns).ToList();
foreach (var columnForRemove in columnsForRemove)
{
serverScopeDesc.Tables[table.ServerTableName].Columns.Remove(serverScopeDesc.Tables[table.ServerTableName].Columns[columnForRemove]);
}
}
return(serverScopeDesc);
}
/// <summary>
/// Добавляет таблицы в область синхронизации.
/// </summary>
/// <param name="tableNames"></param>
/// <param name="scope"></param>
/// <param name="connection"></param>
/// <returns>Таблицы, которые не были добавлены.</returns>
private static IList <string> AddTablesToScopeDescr(IEnumerable <string> tableNames, DbSyncScopeDescription scope, DbConnection connection)
{
var failed = new List <string>();
Poster.PostMessage("Adding tables to scope '{0}' in '{1}'...", scope.ScopeName, connection.ConnectionString);
foreach (var name in tableNames)
{
try
{
DbSyncTableDescription desc;
// не создаем описание для таблицы повторно, после провизионирования другой области с этой таблицей
var nameWithSchema = connection is SqlCeConnection ? name : name.GetSchemaForTable() + '.' + name;
var nameWithConn = connection.ConnectionString + nameWithSchema;
if (!map.TryGetValue(nameWithConn, out desc))
{
if (connection is SqlCeConnection)
{
desc = SqlCeSyncDescriptionBuilder.GetDescriptionForTable(nameWithSchema, connection as SqlCeConnection);
}
else if (connection is SqlConnection)
{
desc = SqlSyncDescriptionBuilder.GetDescriptionForTable(nameWithSchema, connection as SqlConnection);
}
map[nameWithConn] = desc;
}
else
{
Poster.PostMessage("Reuse created Description For Table '{0}'", name);
}
desc.GlobalName = name;
scope.Tables.Add(desc);
Poster.PostMessage("Table '{0}' added, columns: {1}", name, string.Join(", ", desc.Columns.Select(x => x.UnquotedName)));
}
catch (Exception ex)
{
Poster.PostMessage(ex);
failed.Add(name);
}
}
return(failed);
}
/// <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 hostName)
{
SqlSyncProvider provider = new SqlSyncProvider();
provider.ScopeName = SyncUtils.ScopeName;
provider.Connection = new SqlConnection(SyncUtils.GenerateSqlConnectionString(hostName,
SyncUtils.FirstPeerDBName, null, null, true));
//create a new scope description and add the appropriate tables to this scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(SyncUtils.ScopeName);
//class to be used to provision the scope defined above
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)provider.Connection);
//determine if this scope already exists on the server and if not go ahead and provision
//Note that provisioning of the server is oftentimes a design time scenario and not something
//that would be exposed into a client side app as it requires DDL permissions on the server.
//However, it is demonstrated here for purposes of completentess.
//
//Note the default assumption is that SQL Server is installed as localhost. If it's not,
//please replace Environment.MachineName with the correct instance name in
//SqlSharingForm.SqlSharingForm_Shown().
if (!serverConfig.ScopeExists(SyncUtils.ScopeName))
{
//add the approrpiate tables to this scope
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("orders", (System.Data.SqlClient.SqlConnection)provider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("order_details", (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();
}
//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);
}
private void setupScopeToolStripMenuItem_Click(object sender, EventArgs e)
{
#if (DEBUG)
SqlConnection clientConn = new SqlConnection(Program.str_debug_clientConn);
#else
SqlConnection clientConn = new SqlConnection(Program.str_release_clientConn);
#endif
SqlConnection serverConn = new SqlConnection(Program.str_serverConn);
// define a new scope named PayPalSyncScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("PayPalSyncScope");
// get the description of the tblpaypal table from SERVER database
DbSyncTableDescription tblpaypalDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("tblpaypal", serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tblpaypalDesc);
// create a server scope provisioning object based on the PayPalSyncScope
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();
Console.WriteLine("Server Successfully Provisioned.");
// get the description of SyncScope from the server database
DbSyncScopeDescription scopeDesc2 = SqlSyncDescriptionBuilder.GetDescriptionForScope("PayPalSyncScope", serverConn);
// create server provisioning object based on the SyncScope
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc2);
// starts the provisioning process
clientProvision.Apply();
DialogResult result = DotNetPerls.BetterDialog.ShowDialog(
"Trans2Summa3060", //titleString
"Client and Server Successfully Provisioned.", //bigString
null, //smallString
null, //leftButton
"OK", //rightButton
global::Trans2Summa3060.Properties.Resources.Message_info); //iconSet
}
/// <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 hostName)
{
SqlSyncProvider provider = new SqlSyncProvider();
provider.ScopeName = SyncUtils.ScopeName;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = hostName;
builder.IntegratedSecurity = true;
builder.InitialCatalog = "peer1";
builder.ConnectTimeout = 1;
provider.Connection = new SqlConnection(builder.ToString());
//create a new scope description and add the appropriate tables to this scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(SyncUtils.ScopeName);
//class to be used to provision the scope defined above
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)provider.Connection);
//determine if this scope already exists on the server and if not go ahead and provision
if (!serverConfig.ScopeExists(SyncUtils.ScopeName))
{
//add the approrpiate tables to this scope
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("orders", (System.Data.SqlClient.SqlConnection)provider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("order_details", (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();
}
//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);
}
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();
}
protected override void DefineRemoteScope(DbSyncScopeDescription scopeDescription)
{
try
{
using (var connection = new SqlConnection(RemoteConfiguration.ConnectionString))
{
foreach (var tableMapping in _tableMappings)
{
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableMapping.TableNameSrc, connection);
tableDesc.GlobalName = tableMapping.GlobalName;
scopeDescription.Tables.Add(tableDesc);
}
}
}
catch (Exception e)
{
_logger.Error(e);
}
}
