public void CreateScopeDescription(DbSyncScopeDescription scopeDescription)
{
Log("CreateScopeDescription: {0}", this.peerProvider.Connection.ConnectionString);
SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning((SqlConnection)this.peerProvider.Connection, scopeDescription);
prov.Apply();
}
/// <summary>
/// Check to see if the passed in CE provider needs Schema from server
/// </summary>
/// <param name="localProvider"></param>
private void CheckIfProviderNeedsSchema(SqlSyncProvider localProvider)
{
if (localProvider != null)
{
var ceConn = (SqlConnection)localProvider.Connection;
var ceConfig =
new SqlSyncScopeProvisioning(ceConn);
ceConfig.ObjectSchema = "dbo";
var scopeName = localProvider.ScopeName;
//if the scope does not exist in this store
if (!ceConfig.ScopeExists(scopeName))
{
//create a reference to the server proxy
var serverProxy =
new RelationalProviderProxy(scopeName,
Settings.Default.ServiceUrl);
//retrieve the scope description from the server
var scopeDesc = serverProxy.GetScopeDescription();
serverProxy.Dispose();
//use scope description from server to intitialize the client
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.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 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 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();
}
/// <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);
}
private void ProvisionServer(string TableName, SqlParameter Parameter, string ParamValue)
{
try
{
// Create a synchronization scope for OriginState=WA.
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(_serverConnection);
// populate the scope description using the template
serverProvision.PopulateFromTemplate(TableName + _filter, TableName + "_Filter_Template");
// specify the value we want to pass in the filter parameter, in this case we want only orders from WA
serverProvision.Tables[TableName].FilterParameters[Parameter.ParameterName].Value = Guid.Parse(ParamValue);
// Set a friendly description of the template.
serverProvision.UserComment = TableName + " data includes only " + ParamValue;
// Create the new filtered scope in the database.
if (!serverProvision.ScopeExists(serverProvision.ScopeName))
{
serverProvision.Apply();
Log.WriteLogs("Server " + TableName + " was provisioned.");
}
else
{
Log.WriteLogs("Server " + TableName + " was provisioned.");
}
}
catch (Exception ex)
{
Log.WriteErrorLogs(ex);
}
}
/// <summary>
/// Check to see if the passed in SqlSyncProvider needs Schema from server
/// </summary>
/// <param name="localProvider"></param>
private void CheckIfProviderNeedsSchema(SqlSyncProvider localProvider)
{
if (localProvider != null)
{
SqlConnection conn = (SqlConnection)localProvider.Connection;
SqlSyncScopeProvisioning sqlConfig = new SqlSyncScopeProvisioning(conn);
string scopeName = localProvider.ScopeName;
//if the scope does not exist in this store
if (!sqlConfig.ScopeExists(scopeName))
{
//create a reference to the server proxy
SqlSyncProviderProxy serverProxy = new SqlSyncProviderProxy("CardsScope",
connString);
//retrieve the scope description from the server
DbSyncScopeDescription scopeDesc = serverProxy.GetScopeDescription();
serverProxy.Dispose();
//use scope description from server to intitialize the client
sqlConfig.PopulateFromScopeDescription(scopeDesc);
sqlConfig.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();
}
}
/// <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)
{
var provider = new SqlSyncProvider();
provider.ScopeName = scopeName;
provider.Connection = new SqlConnection(conString);
provider.ObjectSchema = "dbo";
// create anew scope description and add the appropriate tables to this scope
var scopeDesc = new DbSyncScopeDescription(scopeName);
// class to be used to provision the scope defined above
var serverConfig =
new SqlSyncScopeProvisioning((SqlConnection)provider.Connection);
serverConfig.ObjectSchema = "dbo";
//determine if this scope already exists on the server and if not go ahead
//and provision
if (!serverConfig.ScopeExists(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();
}
return(provider);
}
public void ProvisionServer(SqlConnection serverConnection = null)
{
var serverConn = serverConnection ?? SqlConnectionFactory.DefaultServerConnection;
var deprovisionScope = new SqlSyncScopeDeprovisioning(serverConn);
deprovisionScope.DeprovisionScope("FullScope");
// define a new scope
var scopeDesc = new DbSyncScopeDescription("FullScope");
// add the table description to the sync scope definition
foreach (var table in DbInfo.Tables) //TODO: view (oder JOINs) synchronisieren?
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(table.Name, serverConn));
//TODO: Server-Event feuern sobald ein Client synchronisiert
// create a server scope provisioning object based on the ProductScope
var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
// start the provisioning process
if (!serverProvision.ScopeExists(scopeDesc.ScopeName))
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;
}
}
internal void Provision(SyncDatabase db, SqlConnection connection, DbSyncScopeDescription scopeDesc)
{
var provision = new SqlSyncScopeProvisioning(connection, scopeDesc);
if (!provision.ScopeExists(scopeDesc.ScopeName))
{
try
{
Log.Info("[DistributedDb] Provision Scope [" + scopeDesc.ScopeName + "] Start", this);
provision.SetCreateTableDefault(DbSyncCreationOption.Skip);
provision.CommandTimeout = 3600;
provision.Apply();
Log.Info("[DistributedDb] Provision Scope [" + scopeDesc.ScopeName + "] End", this);
}
catch (Exception ex)
{
Log.Error("[DistributedDb] Provision Scope [" + scopeDesc.ScopeName + "] Error", ex, this);
}
}
else
{
Log.Info("[DistributedDb] Provision Scope [" + scopeDesc.ScopeName + "] Skipped", this);
}
}
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());
}
}
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 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
}
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();
}
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;
}
}
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);
}
private static void Setup()
{
try
{
SqlConnection sqlServerConn = new SqlConnection(SqlLocalConnectionString);
SqlConnection sqlAzureConn = new SqlConnection(SqlAzureConnectionString);
DbSyncScopeDescription myscope = new DbSyncScopeDescription(scopeName);
DbSyncTableDescription Patient = SqlSyncDescriptionBuilder.GetDescriptionForTable("Patient", sqlServerConn);
//DbSyncTableDescription Album = SqlSyncDescriptionBuilder.GetDescriptionForTable("Album", sqlServerConn);
//Add the tables from above to scope
myscope.Tables.Add(Patient);
// myscope.Tables.Add(Album);
#region Setup Sql Server for sync
SqlSyncScopeProvisioning sqlServerProv = new SqlSyncScopeProvisioning(sqlServerConn, myscope);
if (!sqlServerProv.ScopeExists(scopeName))
{
//Aapply the scope provisioning.
Console.WriteLine("Provisioning SQL Server for sync " + DateTime.Now);
sqlServerProv.Apply();
Console.WriteLine("Done Provisioning SQL Server for sync " + DateTime.Now);
}
else
Console.WriteLine("SQL Server Database server already provisioned for sync " + DateTime.Now);
#endregion
#region Setup Sql Azure for sync
SqlSyncScopeProvisioning sqlAzureProv = new SqlSyncScopeProvisioning(sqlAzureConn, myscope);
if (!sqlAzureProv.ScopeExists(scopeName))
{
//Aapply the scope provisioning.
Console.WriteLine("Provisioning SQL Azure for sync " + DateTime.Now);
sqlAzureProv.Apply();
Console.WriteLine("Done Provisioning SQL Azure for sync " + DateTime.Now);
}
else
Console.WriteLine("SQL Azure Database server already provisioned for sync " + DateTime.Now);
#endregion
sqlAzureConn.Close();
sqlServerConn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
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();
}
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();
}
public void refreshClientScope(int SellerID)
{
DbSyncScopeDescription currentDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(DbInfo.ScopeName(SellerID), ServerCON);
// create a server scope provisioning object
var Provision = new SqlSyncScopeProvisioning(ClientCON, currentDesc);
refreshScope(Provision,currentDesc, ClientCON);
if (!Provision.ScopeExists(currentDesc.ScopeName))
Provision.Apply();
// unstimmigkeiten zwischen scope und realer db (col add/remove /alter type)
}
//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();
}
//TODO: run one time
public void SetClient()
{
// get the description of SyncScope from the server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("MySyncScope", serverConn);
// create server provisioning object based on the SyncScope
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
Console.WriteLine("Client Successfully Provisioned.");
Console.ReadLine();
}
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();
}
}
/// <summary>
/// Check to see if the passed in SqlSyncProvider needs Schema from server
/// </summary>
/// <param name="localProvider"></param>
private void CheckIfProviderNeedsSchema(SqlSyncProvider localProvider)
{
if (localProvider != null)
{
SqlConnection sqlConn = (SqlConnection)localProvider.Connection;
SqlSyncScopeProvisioning sqlConfig = new SqlSyncScopeProvisioning(sqlConn);
string scopeName = localProvider.ScopeName;
if (!sqlConfig.ScopeExists(scopeName))
{
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(SyncUtils.ScopeName, (System.Data.SqlClient.SqlConnection)(sqlSharingForm.providersCollection["Peer1"]).Connection);
sqlConfig.PopulateFromScopeDescription(scopeDesc);
sqlConfig.Apply();
}
}
}
public void ProvisionClient(string TableName)
{
DbSyncScopeDescription scopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope(TableName + _filter, _serverConnection);
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(_localConnection, scopeDescription);
if (!clientProvision.ScopeExists(scopeDescription.ScopeName))
{
clientProvision.Apply();
Log.WriteLogs("Client " + TableName + " was provisioned.");
}
else
{
Log.WriteLogs("Client " + TableName + " was already provisioned.");
}
}
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();
}
}
}
static void Main(string[] args)
{
// Defines the sync scope name
const string scopeName = "UserScope";
// Creates a connexion to the server database.
string SqlConnString = @"Data Source='.\SQLEXPRESS';Initial Catalog=gtd;Integrated Security=True";
SqlConnection serverConn = new SqlConnection(SqlConnString);
// Remove the previous scope from the Sql Server database (no support for updating a scope).
SqlSyncScopeDeprovisioning sqlDepro = new SqlSyncScopeDeprovisioning(serverConn);
sqlDepro.DeprovisionScope(scopeName);
// Defines a new synchronization scope named UserScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);
// Gets the description of the Users table from the gtd database
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Users", serverConn);
// Adds the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// Gets the description of the other tables from the gtd base and add them to the sync scope.
DbSyncTableDescriptionCollection otherTables = new DbSyncTableDescriptionCollection();
otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Tasks", serverConn));
otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Lists", serverConn));
otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Projects", serverConn));
//otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Tasks_Lists", serverConn));
otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Tasks_Tasks", serverConn));
otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Lists_Tasks", serverConn));
otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Projects_Tasks", serverConn));
otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Projects_Lists", serverConn));
otherTables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Projects_Lists_Tasks", serverConn));
foreach (var table in otherTables)
{
scopeDesc.Tables.Add(table);
}
// Creates 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);
// Starts the provisioning process
serverProvision.Apply();
}
public void Run()
{
string scopeName = configuration.ScopeName;
//SERVER PROVISION
var serverScopeDesc = new DbSyncScopeDescription(scopeName);
serverScopeDesc = UpdateServerScopeDesc(serverScopeDesc);
var serverProvision = new SqlSyncScopeProvisioning(configuration.ServerSqlConnection, serverScopeDesc);
if (!serverProvision.ScopeExists(scopeName))
{
serverProvision.PopulateFromScopeDescription(serverScopeDesc);
serverProvision.Apply();
}
else
{
UpdateExistingScopeProvision(serverProvision, configuration.ServerSqlConnection, true);
}
//CLIENT PROVISION
var clientScopeDesc = new DbSyncScopeDescription(scopeName);
clientScopeDesc = UpdateClientScopeDesc(clientScopeDesc);
var clientProvision = new SqlSyncScopeProvisioning(configuration.ClientSqlConnection, clientScopeDesc);
if (!clientProvision.ScopeExists(scopeName))
{
clientProvision.PopulateFromScopeDescription(clientScopeDesc);
clientProvision.Apply();
}
else
{
UpdateExistingScopeProvision(clientProvision, configuration.ClientSqlConnection, false);
}
using (var db = new SystemContext())
{
var changeTables = db.MatchingTableNames.Where(m => m.ScopeIgnore == null || m.ScopeIgnore.Value == 0).OrderBy(m => m.Index).ToList();
foreach (var changeTable in changeTables)
{
changeTable.InScope = 1;
}
db.SaveChanges();
}
}
static void Main(string[] args)
{
// Creates a connection to the localgtd database
SqlConnection clientConn = new SqlConnection(@"Data Source='.\SQLEXPRESS';Initial Catalog=localgtd;Integrated Security=True");
// Creates a connection to the gtd server database
SqlConnection serverConn = new SqlConnection(@"Data Source='.\SQLEXPRESS'; Initial Catalog=gtd; Integrated Security=True");
// Gets the description of UserScope from the gtd server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("UserScope", serverConn);
// Creates CE provisioning object based on the UserScope
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// Starts the provisioning process
clientProvision.Apply();
}
private static void ProvisioningLocal()
{
var connectionLocal = new SqlConnection(ConfigurationManager.AppSettings["connectionLocal"]);
var connectionRemote = new SqlConnection(ConfigurationManager.AppSettings["connectionRemote"]);
var descriptionScope = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductScope", connectionRemote);
var provisionLocal = new SqlSyncScopeProvisioning(connectionLocal, descriptionScope);
try
{
provisionLocal.Apply();
}
catch (Exception ex)
{
Console.WriteLine($"[ERROR] {ex.Message}");
}
}
public void ProvisionClient(SqlConnection clientConnection = null, SqlConnection serverConnection = null)
{
// create a connection to the client database
var clientConn = clientConnection ?? SqlConnectionFactory.DefaultClientConnection;
// create a connection to the master database
var serverConn = serverConnection ?? SqlConnectionFactory.DefaultServerConnection;
// get the description of FullScope from the master database
var scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("FullScope", serverConn);
// create provisioning object based on the FullScope
var clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
}
private void AprovicionarAmbito(DbSyncScopeDescription Ambito, SqlSyncScopeProvisioning proveedorDeAmbito)
{
if (proveedorDeAmbito.ScopeExists(Ambito.ScopeName))
{
Loguear("El ambito " + Ambito.ScopeName + " ya existe!!");
}
else
{
Loguear("Se va a crear el ambito " + Ambito.ScopeName);
proveedorDeAmbito.PopulateFromScopeDescription(Ambito);
proveedorDeAmbito.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
proveedorDeAmbito.Apply();
Loguear("Se creo el ambito " + Ambito.ScopeName);
}
}
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
}
public bool SetupLocalServer()
{
var myscope = new DbSyncScopeDescription(_scopeName);
var patient = SqlSyncDescriptionBuilder.GetDescriptionForTable("Patient", _sqlServerConn);
myscope.Tables.Add(patient);
var sqlServerProv = new SqlSyncScopeProvisioning(_sqlServerConn, myscope);
if (!sqlServerProv.ScopeExists(_scopeName))
{
sqlServerProv.Apply();
_sqlServerConn.Close();
return true;
}
_sqlServerConn.Close();
return false;
}
public static void clientProvision()
{
try
{
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("AllSyncScope", serverConn);
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
localModel.Database.ExecuteSqlCommand("CREATE TABLE [dbo].[SystemIdsSet] ([Id] int NOT NULL,[Type] nvarchar(128) NOT NULL);");
}
catch (Exception)
{
}
}
/// <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 serverConnectionString, Guid clientId)
{
SqlSyncProvider provider = new SqlSyncProvider();
provider.ScopeName = ScopeName;
provider.Connection = new SqlConnection();
provider.Connection.ConnectionString = serverConnectionString;
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(ScopeName);
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)provider.Connection);
if (!serverConfig.ScopeExists(ScopeName))
{
serverConfig.ObjectSchema = "dbo.";
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Client", (System.Data.SqlClient.SqlConnection)provider.Connection));
serverConfig.PopulateFromScopeDescription(scopeDesc);
//indicate that the base table already exists and does not need to be created
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverConfig.Tables["Client"].AddFilterColumn("ClientId");
serverConfig.Tables["Client"].FilterClause = "[side].[ClientId] = '" + clientId + "'";
//Create new selectchanges procedure for our scope
serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
//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;
}
static void Main(string[] args)
{
SqlConnection serverConn = new SqlConnection(@"Data Source=localhost; Initial Catalog=G:\htmlconvertsql\match_analysis_pdm.mdf; Integrated Security=True");
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("match_analysisScope");
string[] tableList = new string[]{
"live_Aibo",
"live_okoo",
"live_Table",
"live_Table_lib",
"match_analysis_collection",
"match_analysis_result",
"match_table_xpath",
"result_tb",
"result_tb_lib"};
DbSyncTableDescription tableDesc;
for (int i = 0; i < tableList.Length; i++)
{
// get the description of the Products table from SyncDB dtabase
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableList[i], 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);
// start the provisioning process
serverProvision.Apply();
}
private static void Main(string[] args)
{
SyncOrchestrator sync = new SyncOrchestrator();
string scopeName = "test";
SqlConnection localData = new SqlConnection(@"Data Source=nipun;Initial Catalog=ClientData;Integrated Security=True;");
SqlConnection serverData = new SqlConnection(@"Data Source=nipun;Initial Catalog=ServerData;Integrated Security=True;");
SqlSyncProvider localProvider = new SqlSyncProvider(scopeName, localData);
SqlSyncProvider serverProvider = new SqlSyncProvider(scopeName, serverData);
SqlSyncScopeProvisioning scopeProvisionLocal = new SqlSyncScopeProvisioning(localData);
if (!scopeProvisionLocal.ScopeExists(scopeName))
{
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("abc", localData));
scopeProvisionLocal.PopulateFromScopeDescription(scopeDesc);
scopeProvisionLocal.SetCreateTableDefault(DbSyncCreationOption.Skip);
scopeProvisionLocal.Apply();
}
SqlSyncScopeProvisioning scopeProvisionRemote = new SqlSyncScopeProvisioning(serverData);
if (!scopeProvisionRemote.ScopeExists(scopeName))
{
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, localData);
scopeProvisionRemote.PopulateFromScopeDescription(scopeDesc);
scopeProvisionRemote.Apply();
}
SqlSyncScopeProvisioning romve = new SqlSyncScopeProvisioning(localData);
sync.LocalProvider = localProvider;
sync.RemoteProvider = serverProvider;
SyncOperationStatistics stats = sync.Synchronize();
Console.WriteLine("Update Data:\t\t {0}", stats.UploadChangesApplied);
Console.WriteLine("Update Data ChangesFailed:\t\t {0}", stats.UploadChangesFailed);
Console.WriteLine("Update Data Changes:\t\t {0}", stats.UploadChangesTotal);
Console.ReadLine();
}
public static void ProvisionDatabase(SqlConnection destination, SqlConnection master, string scopeName, string srid)
{
bool oneway = false;
if (scopeName == "OneWay")
{
oneway = true;
}
bool isSlave = false;
if (!destination.ConnectionString.Equals(master.ConnectionString))
{
isSlave = true;
}
DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(scopeName);
SqlSyncScopeProvisioning destinationConfig = new SqlSyncScopeProvisioning(destination);
if (destinationConfig.ScopeExists(scopeName))
{
return;
}
// TODO: Retrieve actual sync tables
IList<SpatialColumnInfo> twowaytableList = new List<SpatialColumnInfo>();
twowaytableList.Add(new SpatialColumnInfo { TableName = "WaterJobs" });
twowaytableList.Add(new SpatialColumnInfo { TableName = "SewerJobs" });
twowaytableList.Add(new SpatialColumnInfo { TableName = "ParkAssets" });
// TODO: Retrieve actual sync tables
IList<SpatialColumnInfo> onewaytableList = new List<SpatialColumnInfo>();
onewaytableList.Add(new SpatialColumnInfo { TableName = "WaterFittings" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "WaterMains" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "WaterMeters" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "WaterServices" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "SewerNodes" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "SewerPipes" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "RoadLabels" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "Cadastre" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "AddressNumbers" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "Towns" });
onewaytableList.Add(new SpatialColumnInfo { TableName = "LocalityBoundaries" });
if (isSlave)
{
destination.Open();
if (!oneway)
{
DropTables(destination, twowaytableList);
}
else
{
DropTables(destination, onewaytableList);
}
destination.Close();
}
DbSyncColumnDescription identityColumn = null;
DbSyncColumnDescription geometryColumn = null;
if (!oneway)
{
foreach (SpatialColumnInfo spatialTable in twowaytableList)
{
DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(spatialTable.TableName, master);
if (table.PkColumns.Count() != 1 || table.PkColumns.First().Type != "uniqueidentifier")
{
try
{
table.Columns["UniqueID"].IsPrimaryKey = true;
table.PkColumns.FirstOrDefault().IsPrimaryKey = false;
}
catch (Exception)
{
throw new DataSyncException("Tables require a column called 'UniqueID' of type 'uniqueidentifier' to be used with spatial syncing." +
"\nThe UniqueID column should also have a default value of newid() and a UNIQUE, NONCLUSTERED index.");
}
}
foreach (DbSyncColumnDescription item in table.NonPkColumns)
{
if (item.AutoIncrementStepSpecified)
{
identityColumn = item;
spatialTable.IdentityColumn = item.UnquotedName;
continue;
}
if (!item.Type.Contains("geometry"))
{
continue;
}
spatialTable.GeometryColumn = item.UnquotedName;
geometryColumn = item;
geometryColumn.ParameterName += "_was_geometry";
geometryColumn.Type = "nvarchar";
geometryColumn.Size = "max";
geometryColumn.SizeSpecified = true;
}
if (geometryColumn == null || identityColumn == null)
{
throw new DataSyncException("Spatial tables must contain a geometry column and an identity column.");
}
table.Columns.Remove(identityColumn);
if (destination.Equals(master))
{
destinationConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
}
else
{
identityColumn.IsPrimaryKey = true;
destinationConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
}
scopeDescription.Tables.Add(table);
}
}
if (oneway)
{
foreach (var spatialTable in onewaytableList)
{
DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(spatialTable.TableName, master);
spatialTable.IdentityColumn = table.PkColumns.FirstOrDefault().UnquotedName;
foreach (DbSyncColumnDescription item in table.NonPkColumns)
{
if (!item.Type.Contains("geometry"))
{
continue;
}
spatialTable.GeometryColumn = item.UnquotedName;
geometryColumn = item;
geometryColumn.ParameterName += "_was_geometry";
geometryColumn.Type = "nvarchar";
geometryColumn.Size = "max";
geometryColumn.SizeSpecified = true;
}
if (geometryColumn == null)
{
throw new DataSyncException("Spatial tables must contain a geometry column and an identity column.");
}
scopeDescription.Tables.Add(table);
}
}
//It is important to call this after the tables have been added to the scope
destinationConfig.PopulateFromScopeDescription(scopeDescription);
//provision the server
destinationConfig.Apply();
destination.Open();
if (!oneway)
{
foreach (SpatialColumnInfo spatialTable in twowaytableList)
{
string tableName = spatialTable.TableName;
SqlCommand command = destination.CreateCommand();
if (isSlave)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ADD [{1}] int IDENTITY(1,1) NOT NULL", tableName, spatialTable.IdentityColumn);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT PK_{0}", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT PK_{0} PRIMARY KEY CLUSTERED ([{1}])", tableName, spatialTable.IdentityColumn);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD UNIQUE (UniqueID)", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [DF_{0}_UniqueID] DEFAULT (newid()) FOR [UniqueID]", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] geometry", tableName, spatialTable.GeometryColumn);
command.ExecuteNonQuery();
command.CommandText = string.Format("CREATE SPATIAL INDEX [SIndex_{0}_{1}] ON [{0}]([{1}]) USING GEOMETRY_GRID WITH (BOUNDING_BOX =(300000, 6700000, 500000, 7000000), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]", tableName, spatialTable.GeometryColumn);
command.ExecuteNonQuery();
}
try
{
command.CommandText = string.Format("CREATE TRIGGER [dbo].[{0}_GEOMSRID_trigger]\nON [dbo].[{0}]\nAFTER INSERT, UPDATE\nAS UPDATE [dbo].[{0}] SET [{1}].STSrid = {2}\nFROM [dbo].[{0}]\nJOIN inserted\nON [dbo].[{0}].[UniqueID] = inserted.[UniqueID] AND inserted.[{1}] IS NOT NULL", spatialTable.TableName, spatialTable.GeometryColumn, srid);
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (!ex.Message.StartsWith("There is already"))
{
throw;
}
}
Server server = new Server(destination.DataSource);
Database db = server.Databases[destination.Database];
foreach (StoredProcedure sp in db.StoredProcedures.Cast<StoredProcedure>().Where(sp => sp.Name.Equals(tableName + "_selectchanges", StringComparison.InvariantCultureIgnoreCase) || sp.Name.Equals(tableName + "_selectrow", StringComparison.InvariantCultureIgnoreCase)))
{
sp.TextBody = sp.TextBody.Replace(string.Format("[{0}]", spatialTable.GeometryColumn), string.Format("[{0}].STAsText() as [{0}]", spatialTable.GeometryColumn));
sp.Alter();
}
}
}
else
{
foreach (SpatialColumnInfo spatialTable in onewaytableList)
{
string tableName = spatialTable.TableName;
SqlCommand command = destination.CreateCommand();
if (isSlave)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN [{1}] geometry", tableName, spatialTable.GeometryColumn);
command.ExecuteNonQuery();
command.CommandText = string.Format("CREATE SPATIAL INDEX [SIndex_{0}_{1}] ON [{0}]([{1}]) USING GEOMETRY_GRID WITH (BOUNDING_BOX =(300000, 6700000, 500000, 7000000), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]", tableName, spatialTable.GeometryColumn);
command.ExecuteNonQuery();
}
try
{
command.CommandText = string.Format("CREATE TRIGGER [dbo].[{0}_GEOMSRID_trigger]\nON [dbo].[{0}]\nAFTER INSERT, UPDATE\nAS UPDATE [dbo].[{0}] SET [{1}].STSrid = {2}\nFROM [dbo].[{0}]\nJOIN inserted\nON [dbo].[{0}].[{3}] = inserted.[{3}] AND inserted.[{1}] IS NOT NULL", spatialTable.TableName, spatialTable.GeometryColumn, srid, spatialTable.IdentityColumn);
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (!ex.Message.StartsWith("There is already"))
{
throw;
}
}
Server server = new Server(destination.DataSource);
Database db = server.Databases[destination.Database];
foreach (StoredProcedure sp in db.StoredProcedures.Cast<StoredProcedure>()
.Where(sp => sp.Name.Equals(tableName + "_selectchanges", StringComparison.InvariantCultureIgnoreCase) ||
sp.Name.Equals(tableName + "_selectrow", StringComparison.InvariantCultureIgnoreCase)))
{
sp.TextBody = sp.TextBody.Replace(string.Format("[{0}]", spatialTable.GeometryColumn),
string.Format("[{0}].STAsText() as [{0}]", spatialTable.GeometryColumn));
sp.Alter();
}
}
}
}
public void refreshServerScope(int SellerID)
{
//define Scope
DbSyncScopeDescription currentDesc = new DbSyncScopeDescription(DbInfo.ScopeName(SellerID));
//add the tables to the scope discription (defined in DBTable)
foreach (DbTable table in DbInfo.Tables)
currentDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(table.Name,table.Columns, ServerCON));
// create a server scope provisioning object
SqlSyncScopeProvisioning Provision = new SqlSyncScopeProvisioning(ServerCON, currentDesc);
// so use the Skip parameter
Provision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// set the filter column on the Orders table to OriginState
Provision.Tables["sellers"].AddFilterColumn("ID");
// set the filter value to NC
Provision.Tables["sellers"].FilterClause = "[side].[ID] != 2";
//Provision.Tables["sellers"].ObjectSchema
refreshScope(Provision,currentDesc, ServerCON);
if (!Provision.ScopeExists(currentDesc.ScopeName))
Provision.Apply();
}
public static void ProvisionTable(SqlConnection master, SqlConnection destination, string tableName, int srid, bool oneWayOnly)
{
bool isSlave = false;
if (!destination.ConnectionString.Equals(master.ConnectionString))
{
isSlave = true;
}
DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(tableName);
SqlSyncScopeProvisioning destinationConfig = new SqlSyncScopeProvisioning(destination);
if (destinationConfig.ScopeExists(tableName))
{
throw new SyncConstraintConflictNotAllowedException(@"Scope already exists. Please deprovision scope first.");
}
DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, master);
DbSyncColumnDescription uniqueColumn = table.Columns.Where(f => f.Type == "uniqueidentifier").FirstOrDefault();
DbSyncColumnDescription geometryColumn = table.Columns.Where(f => f.Type.EndsWith("geometry")).FirstOrDefault();
DbSyncColumnDescription identityColumn = table.Columns.Where(f => f.AutoIncrementStepSpecified).FirstOrDefault();
DbSyncColumnDescription joinColumn = table.PkColumns.FirstOrDefault();
if (table.PkColumns.Count() != 1)
{
throw new SyncException(@"Table must have a single primary key column to be used with synchronisation.");
}
if (uniqueColumn != null && !uniqueColumn.IsPrimaryKey && !oneWayOnly)
{
table.PkColumns.FirstOrDefault().IsPrimaryKey = false;
uniqueColumn.IsPrimaryKey = true;
joinColumn = uniqueColumn;
}
if (geometryColumn != null)
{
geometryColumn.ParameterName += "_was_geometry";
geometryColumn.Type = "nvarchar";
geometryColumn.Size = "max";
geometryColumn.SizeSpecified = true;
}
if (identityColumn != null && identityColumn != joinColumn)
{
table.Columns.Remove(identityColumn);
}
destinationConfig.SetCreateTableDefault(isSlave ? DbSyncCreationOption.Create : DbSyncCreationOption.Skip);
scopeDescription.Tables.Add(table);
//It is important to call this after the tables have been added to the scope
destinationConfig.PopulateFromScopeDescription(scopeDescription);
//provision the server
destinationConfig.Apply();
destination.Open();
SqlCommand command = destination.CreateCommand();
if (isSlave && identityColumn != null && identityColumn != joinColumn)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ADD {1} int IDENTITY(1,1) NOT NULL", tableName, identityColumn.QuotedName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] DROP CONSTRAINT PK_{0}", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT PK_{0} PRIMARY KEY CLUSTERED ({1})", tableName, identityColumn.QuotedName);
command.ExecuteNonQuery();
}
if (uniqueColumn != null && isSlave && uniqueColumn == joinColumn)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ADD UNIQUE ({1})", tableName, uniqueColumn.QuotedName);
command.ExecuteNonQuery();
command.CommandText = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [DF_{0}_{2}] DEFAULT (newid()) FOR {1}", tableName, uniqueColumn.QuotedName, uniqueColumn.UnquotedName.Replace(' ', '_'));
command.ExecuteNonQuery();
}
if (geometryColumn == null)
{
return;
}
if (isSlave)
{
command.CommandText = string.Format("ALTER TABLE [{0}] ALTER COLUMN {1} geometry", tableName, geometryColumn.QuotedName);
command.ExecuteNonQuery();
try
{
command.CommandText = string.Format("CREATE SPATIAL INDEX [ogr_{2}_sidx] ON [{0}]({1}) USING GEOMETRY_GRID WITH (BOUNDING_BOX =(300000, 6700000, 500000, 7000000), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]", tableName, geometryColumn.QuotedName, geometryColumn.UnquotedName);
command.ExecuteNonQuery();
}
catch (SqlException sqlException)
{
if (!sqlException.Message.Contains("already exists"))
{
throw;
}
}
}
command.CommandText = string.Format("IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_GEOMSRID_trigger]')) DROP TRIGGER [dbo].[{0}_GEOMSRID_trigger]", tableName);
command.ExecuteNonQuery();
command.CommandText = string.Format("CREATE TRIGGER [dbo].[{0}_GEOMSRID_trigger]\nON [dbo].[{0}]\nAFTER INSERT, UPDATE\nAS UPDATE [dbo].[{0}] SET [{1}].STSrid = {2}\nFROM [dbo].[{0}]\nJOIN inserted\nON [dbo].[{0}].{3} = inserted.{3} AND inserted.[{1}] IS NOT NULL", tableName, geometryColumn.UnquotedName, srid, joinColumn.QuotedName);
command.ExecuteNonQuery();
Server server = new Server(destination.DataSource);
Database db = server.Databases[destination.Database];
foreach (StoredProcedure sp in db.StoredProcedures.Cast<StoredProcedure>().Where(sp => sp.Name.Equals(tableName + "_selectchanges", StringComparison.InvariantCultureIgnoreCase) || sp.Name.Equals(tableName + "_selectrow", StringComparison.InvariantCultureIgnoreCase)))
{
sp.TextBody = sp.TextBody.Replace(geometryColumn.QuotedName, string.Format("{0}.STAsText() as {0}", geometryColumn.QuotedName));
sp.Alter();
}
}
/// <summary>
/// Cài đặt SCOPE cho client từ Server SCOPE,
/// Nếu CLIENT có SCOPE rồi sẽ bỏ qua
/// </summary>
/// <param name="client_connectionString"></param>
/// <param name="server_connectionString"></param>
public static int fetch_sync_scope(String client_connectionString, String server_connectionString, String server_scope_name)
{
try{
// create a connection to the SyncCompactDB database
SqlConnection clientConn = new SqlConnection(client_connectionString);
// create a connection to the SyncDB server database
SqlConnection serverConn = new SqlConnection(server_connectionString);
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(server_scope_name, serverConn);
// create provisioning object based on the Scope
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
//starts the provisioning process
clientProvision.Apply();
return 1;
}catch(Exception ex)
{
Debug.WriteLine(ex.ToString());
return -1;
}
}
/// <summary>
/// Cài đặt Sync SCOPE ở database chỉ định,
/// yêu cầu bắt buộc phải có cấu trúc CSDL trước,
/// Nếu Server có Scope trùng tên rồi thì không làm gì cả
/// </summary>
/// <param name="connectionString"></param>
/// <param name="scopeName"></param>
/// <param name="tracking_tables"></param>
public static int setup_sync_scope(String connectionString, String scope_name, String[] tracking_tables)
{
SqlConnection serverConn = new SqlConnection(connectionString);
try
{
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope_name);
// get the description of the Products table from SyncDB dtabase
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);
}
// 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();
return 1;
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
return -1;
}
finally
{
serverConn.Dispose();
}
}
public static void CreateDatabaseProvisionFilter(SqlConnection serverConn, SqlCeConnection clientConn, string scopeName, string filterName, string filterValue, Collection<string> columnsToInclude)
{
string filterTemplate = scopeName + "_filter_template";
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(filterTemplate);
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(scopeName.Replace("Scope", ""), columnsToInclude, serverConn);
scopeDesc.Tables.Add(tableDesc);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
serverProvision.Tables[scopeName.Replace("Scope", "")].AddFilterColumn(filterName);
serverProvision.Tables[scopeName.Replace("Scope", "")].FilterClause = "[side].[" + filterName + "] = @" + filterName;
SqlParameter parameter = new SqlParameter("@" + filterName, SqlDbType.NVarChar, 8);
serverProvision.Tables[scopeName.Replace("Scope", "")].FilterParameters.Add(parameter);
if (!serverProvision.TemplateExists(filterTemplate))
{
serverProvision.Apply();
}
serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.PopulateFromTemplate(scopeName + "-" + filterName + filterValue, filterTemplate);
serverProvision.Tables[scopeName.Replace("Scope", "")].FilterParameters["@" + filterName].Value = filterValue;
if (!serverProvision.ScopeExists(scopeName + "-" + filterName + filterValue))
{
serverProvision.Apply();
}
scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName + "-" + filterName + filterValue, serverConn);
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
if (!clientProvision.ScopeExists(scopeName + "-" + filterName + filterValue))
{
try
{
clientProvision.Apply();
}
catch (Exception ex)
{
Console.WriteLine("Error {0}", ex.Message);
}
}
}
private void AprovicionarAmbito(DbSyncScopeDescription Ambito, SqlSyncScopeProvisioning proveedorDeAmbito)
{
if (proveedorDeAmbito.ScopeExists(Ambito.ScopeName))
{
this.loguear("El ambito " + Ambito.ScopeName + " ya existe!! Inicio:\t" + DateTime.Now);
}
else
{
this.loguear("Se va a crear el ambito " + Ambito.ScopeName + " Inicio:\t" + DateTime.Now);
proveedorDeAmbito.PopulateFromScopeDescription(Ambito);
proveedorDeAmbito.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
proveedorDeAmbito.Apply();
this.loguear("Se creo el ambito " + Ambito.ScopeName + " fin:\t" + DateTime.Now);
}
}
public void Provision(String[] args)
{
ArgsParser parser = ArgsParser.ParseArgs(args);
System.Configuration.Configuration config = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap() { ExeConfigFilename = parser.ConfigFile }, ConfigurationUserLevel.None);
SyncConfigurationSection syncConfig = config.GetSection(Constants.SyncConfigurationSectionName) as SyncConfigurationSection;
SelectedConfigSections selectedConfig = FillDefaults(parser, syncConfig);
DbSyncScopeDescription scopeDescription = GetDbSyncScopeDescription(selectedConfig);
try
{
SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning(new SqlConnection(selectedConfig.SelectedTargetDatabase.GetConnectionString()),
scopeDescription, selectedConfig.SelectedSyncScope.IsTemplateScope ? SqlSyncScopeProvisioningType.Template : SqlSyncScopeProvisioningType.Scope);
prov.CommandTimeout = 60;
// 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 (!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
{
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));
}
}
}
catch (Exception)
{
throw;
}
}
public void CreateScopeDescription(DbSyncScopeDescription scopeDescription)
{
SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning((SqlConnection)this.peerProvider.Connection, scopeDescription);
prov.Apply();
}
public static void CreateDatabaseProvision(SqlConnection serverConn, SqlCeConnection clientConn, string scopeName)
{
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(scopeName.Replace("Scope", ""), serverConn);
scopeDesc.Tables.Add(tableDesc);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
if (!serverProvision.ScopeExists(scopeName))
{
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Apply();
}
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
if (!clientProvision.ScopeExists(scopeName))
{
clientProvision.Apply();
}
}
public static void ServerProvision()
{
if (serverConn.State == ConnectionState.Closed)
serverConn.Open();
List<string> tables = new List<string>();
DataTable dt = serverConn.GetSchema("Tables");
foreach (DataRow row in dt.Rows)
{
string tablename = (string)row[2];
if (tablename == "SystemIdsSet")
continue;
tables.Add(tablename);
}
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("AllSyncScope");
foreach (string table in tables)
{
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table, 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);
// start the provisioning process
serverProvision.Apply();
}
/// <summary>
/// Check to see if the passed in SqlSyncProvider needs Schema from server
/// </summary>
/// <param name="localProvider"></param>
private void CheckIfProviderNeedsSchema(SqlSyncProvider localProvider, string serverConnectionString)
{
if (localProvider != null)
{
SqlConnection conn = (SqlConnection)localProvider.Connection;
SqlSyncScopeProvisioning sqlConfig = new SqlSyncScopeProvisioning(conn);
string scopeName = localProvider.ScopeName;
if (!sqlConfig.ScopeExists(scopeName))
{
SqlSyncProviderProxy serverProxy = new SqlSyncProviderProxy(scopeName, serverConnectionString);
DbSyncScopeDescription scopeDesc = serverProxy.GetScopeDescription(scopeName, serverConnectionString);
serverProxy.Dispose();
sqlConfig.PopulateFromScopeDescription(scopeDesc);
sqlConfig.Apply();
}
}
}
private static void ProcessConfigFile(ArgsParser parser)
{
DbSyncScopeDescription scopeDescription;
Dictionary<string, Dictionary<string, string>> tablesToColumnMappingsInfo = new Dictionary<string, Dictionary<string, string>>();
if (string.IsNullOrEmpty(parser.ConfigFile))
{
LogArgsError("Required argument /scopeconfig is not specified.");
return;
}
if (!System.IO.File.Exists(parser.ConfigFile))
{
LogArgsError("Unable to find scopeconfig file '" + parser.ConfigFile + "'");
return;
}
System.Configuration.Configuration config = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap() { ExeConfigFilename = parser.ConfigFile }, ConfigurationUserLevel.None);
Log("Reading specified config file...");
SyncConfigurationSection syncConfig = config.GetSection(Constants.SyncConfigurationSectionName) as SyncConfigurationSection;
// ValidateConfigFile the config and the passed in input parameters
ValidateConfigFile(parser, syncConfig);
// Fill in the defaults value for the parser.
SelectedConfigSections selectedConfig = FillDefaults(parser, syncConfig);
Log("Generating DbSyncScopeDescription for scope {0}...", selectedConfig.SelectedSyncScope.Name);
scopeDescription = GetDbSyncScopeDescription(selectedConfig);
tablesToColumnMappingsInfo = BuildColumnMappingInfo(selectedConfig);
switch (parser.OperationMode)
{
case OperationMode.Provision:
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 (!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
{
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));
}
}
}
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);
}
break;
case OperationMode.Deprovision:
try
{
SqlSyncScopeDeprovisioning deprov = new SqlSyncScopeDeprovisioning(new SqlConnection(selectedConfig.SelectedTargetDatabase.GetConnectionString()));
// Set the ObjectSchema property.
if (!String.IsNullOrEmpty(selectedConfig.SelectedSyncScope.SchemaName))
{
deprov.ObjectSchema = selectedConfig.SelectedSyncScope.SchemaName;
}
Log("Deprovisioning Database {0} for scope {1}...", selectedConfig.SelectedTargetDatabase.Name, selectedConfig.SelectedSyncScope.Name);
if (selectedConfig.SelectedSyncScope.IsTemplateScope)
{
deprov.DeprovisionTemplate(selectedConfig.SelectedSyncScope.Name);
}
else
{
deprov.DeprovisionScope(selectedConfig.SelectedSyncScope.Name);
}
}
catch (Exception e)
{
throw new InvalidOperationException("Unexpected error when executing the Deprovisioning command. See inner exception for details.", e);
}
break;
case OperationMode.Deprovisionstore:
try
{
SqlSyncScopeDeprovisioning deprov = new SqlSyncScopeDeprovisioning(new SqlConnection(selectedConfig.SelectedTargetDatabase.GetConnectionString()));
Log("Deprovisioning Store Database {0} ...", selectedConfig.SelectedTargetDatabase.Name);
deprov.DeprovisionStore();
}
catch (Exception e)
{
throw new InvalidOperationException("Unexpected error when executing the Deprovisioning command. See inner exception for details.", e);
}
break;
case OperationMode.Codegen:
Log("Generating files...");
EntityGenerator generator = EntityGeneratorFactory.Create(parser.CodeGenMode, selectedConfig.SelectedSyncScope.SchemaName);
generator.GenerateEntities(parser.GeneratedFilePrefix,
string.IsNullOrEmpty(parser.Namespace)
? string.IsNullOrEmpty(parser.GeneratedFilePrefix) ? scopeDescription.ScopeName : parser.GeneratedFilePrefix
: parser.Namespace,
scopeDescription, tablesToColumnMappingsInfo, parser.WorkingDirectory, parser.Language, null/*serviceUri*/);
break;
default:
break;
}
}
//TODO: run for all required tables
public void setServer()
{
// define a new scope named MySyncScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("MySyncScope");
// get the description of the CUSTOMER & PRODUCT table from SERVER database
DbSyncTableDescription questionsTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("questions", serverConn);
DbSyncTableDescription answersTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("answers", serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(questionsTableDesc);
scopeDesc.Tables.Add(answersTableDesc);
// create a server scope provisioning object based on the MySyncScope
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.");
Console.ReadLine();
}
/// <summary>
/// Create a new scope for a client. This method is called when GetChanges is passed a null blob.
/// The requested scope is compared to an existing scope or a template and a new scope is provisioned
/// If the requested scope is an existing template, filter parameters, if present, are added when provisioning.
///
/// Note: If both scope and template match the requested scope, we prefer the scope. We would need to expose this
/// out to the service if we want to make this choice configurable.
/// </summary>
private void CreateNewScopeForClient()
{
using (var serverConnection = new SqlConnection(_configuration.ServerConnectionString))
{
// Default's to scope.
// Note: Do not use constructors that take in a DbSyncScopeDescription since there are checks internally
// to ensure that it has atleast 1 table. In this case we would be passing in a non-existing scope which throws an
// exception.
var provisioning = new SqlSyncScopeProvisioning(serverConnection);
// Set the ObjectSchema property. Without this, the TemplateExists and ScopeExists method
// always return false if the sync objects are provisioned in a non-dbo schema.
if (!String.IsNullOrEmpty(_configuration.SyncObjectSchema))
{
provisioning.ObjectSchema = _configuration.SyncObjectSchema;
}
// Determine if this is a scope or a template.
//Note: Scope has a higher priority than a template. See method summary for more info.
bool isTemplate;
if (provisioning.ScopeExists(_scopeName))
{
isTemplate = false;
}
else if (provisioning.TemplateExists(_scopeName))
{
isTemplate = true;
}
else
{
throw SyncServiceException.CreateBadRequestError(Strings.NoScopeOrTemplateFound);
}
// If scope...
if (!isTemplate)
{
DbSyncScopeDescription scopeDescription = String.IsNullOrEmpty(_configuration.SyncObjectSchema) ?
SqlSyncDescriptionBuilder.GetDescriptionForScope(_scopeName, serverConnection) :
SqlSyncDescriptionBuilder.GetDescriptionForScope(_scopeName, string.Empty /*objectPrefix*/, _configuration.SyncObjectSchema, serverConnection);
scopeDescription.ScopeName = _clientScopeName;
provisioning.PopulateFromScopeDescription(scopeDescription);
// If scope then disable bulk procedures.
// Template provisioning does not create anything.
provisioning.SetUseBulkProceduresDefault(false);
}
// If template...
else
{
provisioning.PopulateFromTemplate(_clientScopeName, _scopeName);
// Add filter parameters.
if (null != _filterParams && 0 != _filterParams.Count)
{
foreach (var param in _filterParams)
{
provisioning.Tables[param.TableName].FilterParameters[param.SqlParameterName].Value = param.Value;
}
}
}
if (!provisioning.ScopeExists(_clientScopeName))
{
provisioning.Apply();
}
}
}
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();
}
