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 bool NeedsScope(string scopeName, string clientConnectionString)
{
this.peerProvider.Connection.ConnectionString = clientConnectionString;
SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning((SqlConnection)this.peerProvider.Connection);
return(!prov.ScopeExists(scopeName));
}
/// <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);
}
}
/// <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();
}
}
}
/// <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);
}
internal void Deprovision(SyncDatabase db, SqlConnection connection, DbSyncScopeDescription scopeDesc)
{
this.GetDescriptionForTables(db.Tables, connection, ref scopeDesc);
var provision = new SqlSyncScopeProvisioning(connection, scopeDesc);
if (provision.ScopeExists(scopeDesc.ScopeName))
{
try
{
var deprovision = new SqlSyncScopeDeprovisioning(connection);
Log.Info("[DistributedDb] Deprovision Scope [" + scopeDesc.ScopeName + "] Start", this);
deprovision.DeprovisionScope(scopeDesc.ScopeName);
Log.Info("[DistributedDb] Deprovision Scope [" + scopeDesc.ScopeName + "] End", this);
}
catch (Exception ex)
{
Log.Error("[DistributedDb] Deprovision Scope [" + scopeDesc.ScopeName + "] Error", ex, this);
}
}
else
{
Log.Info("[DistributedDb] Deprovision Scope [" + scopeDesc.ScopeName + "] Skipped", this);
}
}
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>
/// 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 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();
}
}
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);
}
}
/// <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();
}
}
}
internal void ProvisionTriggerAndProcedureUpdates(SyncDatabase db, SqlConnection connection, DbSyncScopeDescription scopeDesc)
{
var provision = new SqlSyncScopeProvisioning(connection, scopeDesc);
if (provision.ScopeExists(scopeDesc.ScopeName))
{
try
{
Log.Info("[DistributedDb] Provision Scope Trigger And Procedure Updates [" + scopeDesc.ScopeName + "] Start", this);
provision.SetCreateTableDefault(DbSyncCreationOption.Skip);
provision.CommandTimeout = 3600;
provision.ApplyTriggerAndProcedureUpdates(connection);
Log.Info("[DistributedDb] Provision Scope Trigger And Procedure Updates [" + scopeDesc.ScopeName + "] End", this);
}
catch (Exception ex)
{
Log.Error("[DistributedDb] Provision Scope Trigger And Procedure Updates [" + scopeDesc.ScopeName + "] Error", ex, this);
}
}
else
{
Log.Info("[DistributedDb] Provision Scope Trigger And Procedure Updates [" + scopeDesc.ScopeName + "] Skipped", this);
}
}
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 bool NeedsScope()
{
Log("NeedsSchema: {0}", this.peerProvider.Connection.ConnectionString);
SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning((SqlConnection)this.peerProvider.Connection);
return(!prov.ScopeExists(this.peerProvider.ScopeName));
}
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);
}
}
public override void DeProvisionDb()
{
SqlSyncScopeProvisioning clientProvision = CreateScopeProvision();
if (clientProvision.ScopeExists(SyncScopeName))
{
SqlSyncScopeDeprovisioning clientDeprovision = CreateScopeDeProvision();
clientDeprovision.DeprovisionScope(SyncScopeName);
}
}
public override void DeProvisionDb()
{
SqlSyncScopeProvisioning serverProvision = CreateScopeProvision();
SqlSyncScopeDeprovisioning serverDeprovision = null;
if (serverProvision.ScopeExists(SyncScopeName))
{
serverDeprovision = CreateScopeDeProvision();
serverDeprovision.DeprovisionScope(SyncScopeName);
}
serverProvision = CreateScopeProvisionByType(SqlSyncScopeProvisioningType.Template);
if (serverProvision.ScopeExists(SyncScopeName + "_Template"))
{
if (serverDeprovision == null)
{
serverDeprovision = CreateScopeDeProvision();
}
serverDeprovision.DeprovisionScope(SyncScopeName);
}
}
private bool ExisteAmbito(string esquemaMetadataSyncFramework, string prefijoMetadataSyncFramework, SqlConnection conexionSql, DbSyncScopeDescription ambito)
{
Boolean resultado = false;
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(conexionSql);
serverConfig.ObjectPrefix = prefijoMetadataSyncFramework;
serverConfig.ObjectSchema = esquemaMetadataSyncFramework;
resultado = serverConfig.ScopeExists(ambito.ScopeName);
return(resultado);
}
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)
}
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.");
}
}
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();
}
}
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 DesaprovicionarAmbito(SqlConnection conexionSql, DbSyncScopeDescription Ambito, SqlSyncScopeProvisioning proveedorDeAmbito)
{
SqlSyncScopeDeprovisioning DesaprovicionadorDeAmbito = new SqlSyncScopeDeprovisioning(conexionSql);
DesaprovicionadorDeAmbito.ObjectSchema = this.esquemaMetadataSyncFramework;
DesaprovicionadorDeAmbito.ObjectPrefix = this.prefijoMetadataSyncFramework;
this.loguear("Se va a eliminar el ambito " + Ambito.ScopeName + " Inicio:\t" + DateTime.Now);
lock (this.obj)
{
if (proveedorDeAmbito.ScopeExists(Ambito.ScopeName))
{
DesaprovicionadorDeAmbito.DeprovisionScope(Ambito.ScopeName);
}
}
this.loguear("Se elimino el ambito " + Ambito.ScopeName + " fin:\t" + DateTime.Now);
}
/// <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);
}
protected virtual void ProvisionSyncScope(SqlSyncScopeProvisioning clientProvision, DbSyncScopeDescription serverScopeDescription, SqlSyncScopeProvisioningType provisionType)
{
if (serverScopeDescription != null)
{
// Create provisioning object
if (!clientProvision.ScopeExists(serverScopeDescription.ScopeName))
{
clientProvision.PopulateFromScopeDescription(serverScopeDescription);
if (provisionType == SqlSyncScopeProvisioningType.Scope)
{
ApplyStaticFilters(clientProvision, serverScopeDescription.ScopeName);
}
clientProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
// Start the provisioning process
clientProvision.Apply();
}
}
}
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 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();
}
/// <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);
}
private static bool ProvisionSqlServer(SqlConnection con, Scope scope)
{
var sqlProv = new SqlSyncScopeProvisioning(con);
sqlProv.ObjectSchema = prefix;
if (!sqlProv.ScopeExists(scope.ToScopeString()))
{
var scopeDescr = new DbSyncScopeDescription(scope.ToScopeString());
AddTablesToScopeDescr(scope.ToTableNames(), scopeDescr, con);
sqlProv.PopulateFromScopeDescription(scopeDescr);
sqlProv.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
sqlProv.Apply();
return(true);
}
return(false);
}
public void ProvisionClient()
{
SqlConnection serverConn = new SqlConnection(sServerConnection);
SqlConnection clientConn = new SqlConnection(sClientConnection);
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(sScope, serverConn);
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
if (!clientProvision.ScopeExists(sScope))
{
clientProvision.Apply();
}
}
private void ApplyProvision(DbSynchronizerConfiguration configuration, Action <DbSyncScopeDescription> defineScope)
{
using (var connection = new SqlConnection(configuration.ConnectionString))
{
var provision = new SqlSyncScopeProvisioning(connection)
{
ObjectPrefix = configuration.ObjectPrefix ?? string.Empty,
ObjectSchema = configuration.ObjectSchema ?? string.Empty,
};
if (!provision.ScopeExists(configuration.ScopeName))
{
var scopeDescription = new DbSyncScopeDescription(configuration.ScopeName);
defineScope(scopeDescription);
provision.SetCreateTableDefault(DbSyncCreationOption.Skip);
provision.PopulateFromScopeDescription(scopeDescription);
provision.Apply();
}
}
}
private void ReadTableValuesForSelectedTab()
{
TabPage tabPage = this.peerTabsControl.SelectedTab;
SqlSyncProvider sqlProvider = providersCollection[tabPage.Text] as SqlSyncProvider;
if (sqlProvider != null)
{
SqlConnection conn = (SqlConnection)sqlProvider.Connection;
SqlSyncScopeProvisioning sqlConfig = new SqlSyncScopeProvisioning(conn);
string scopeName = sqlProvider.ScopeName;
if (!sqlConfig.ScopeExists(scopeName))
{
MessageBox.Show(string.Format("The database of '{0}' does not contain schema. Synchronize this peer with the Peer1 first to retrieve data.", tabPage.Text),
"UnInitialized Peer Database");
return;
}
}
RefreshTables(providersCollection[tabPage.Text].Connection, (TablesViewControl)this.peerTabsControl.SelectedTab.Controls["TablesViewCtrl"]);
}
/// <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;
}
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 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 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();
}
}
}
}
private void refreshScope(SqlSyncScopeProvisioning Provision, DbSyncScopeDescription currentDesc, SqlConnection CON)
{
// start the provisioning process
if (Provision.ScopeExists(currentDesc.ScopeName))
{
DbSyncScopeDescription dbDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(currentDesc.ScopeName, CON);
MessageBox.Show(currentDesc.Tables[0].Columns[1].QuotedName + ": " + currentDesc.Tables[0].Columns[1].Type + " <" + currentDesc.Tables[0].Columns[1].Size + ">\n" +
dbDesc.Tables[0].Columns[1].QuotedName + ": " + dbDesc.Tables[0].Columns[1].Type + " <" + dbDesc.Tables[0].Columns[1].Size + ">"
);
if (!compareDescriptions(currentDesc, dbDesc) || true)
{
if (MessageBox.Show("The Scope '" + currentDesc.ScopeName + "' is out of sync with the Database!\nShould the Scope be rewritten?\nWarning:\nAll Scope-Data (Trigger etc.) will be lost!",
"Warning, Scope is invalid!", MessageBoxButtons.YesNo, MessageBoxIcon.Stop) == DialogResult.Yes)
{
SqlSyncScopeDeprovisioning DeProvision = new SqlSyncScopeDeprovisioning(CON);
DeProvision.DeprovisionScope(currentDesc.ScopeName);
}
}
}
}
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();
}
}
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 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();
}
}
/// <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();
}
}
}
/// <summary>
/// Kiểm tra có SCOPE chưa
/// </summary>
/// <param name="connectionString"></param>
/// <param name="scope_name"></param>
/// <returns></returns>
public static int isHasScope(String connectionString, String scope_name, String[] tracking_tables)
{
SqlConnection serverConn = new SqlConnection(connectionString);
if (!isExist(serverConn))
{
return -1;
}
try
{
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope_name);
DbSyncTableDescription tableDesc = null;
foreach (String item in tracking_tables)
{
//parse
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(item, serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
}
SqlSyncScopeProvisioning tmp = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
return tmp.ScopeExists(scope_name)?1:-1;
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return -1;
}
}
private static void 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;
}
}
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 bool ExisteAmbito(string esquemaMetadataSyncFramework, string prefijoMetadataSyncFramework, SqlConnection conexionSql, DbSyncScopeDescription ambito)
{
Boolean resultado = false;
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning( conexionSql );
serverConfig.ObjectPrefix = prefijoMetadataSyncFramework;
serverConfig.ObjectSchema = esquemaMetadataSyncFramework;
resultado = serverConfig.ScopeExists(ambito.ScopeName);
return resultado;
}
private void DesaprovicionarAmbito(SqlConnection conexionSql, DbSyncScopeDescription Ambito, SqlSyncScopeProvisioning proveedorDeAmbito)
{
SqlSyncScopeDeprovisioning DesaprovicionadorDeAmbito = new SqlSyncScopeDeprovisioning(conexionSql);
DesaprovicionadorDeAmbito.ObjectSchema = this.esquemaMetadataSyncFramework;
DesaprovicionadorDeAmbito.ObjectPrefix = this.prefijoMetadataSyncFramework;
this.loguear("Se va a eliminar el ambito " + Ambito.ScopeName + " Inicio:\t" + DateTime.Now);
lock(this.obj)
{
if (proveedorDeAmbito.ScopeExists(Ambito.ScopeName))
DesaprovicionadorDeAmbito.DeprovisionScope(Ambito.ScopeName);
}
this.loguear("Se elimino el ambito " + Ambito.ScopeName + " fin:\t" + DateTime.Now);
}
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);
}
}
/// <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();
}
}
}
/// <summary>Check if the database is provisioned and has a template/scope that the service is configured for.</summary>
/// <param name="configuration">Service configuration</param>
/// <returns>Result of the diagnostic check</returns>
private static DiagTestResult CheckDbProvisioning(SyncServiceConfiguration configuration)
{
Debug.Assert(configuration.ScopeNames.Count > 0, "configuration.ScopeNames.Count > 0");
var result = new DiagTestResult();
try
{
using (var connection = new SqlConnection(configuration.ServerConnectionString))
{
var provisioning = new SqlSyncScopeProvisioning(connection);
// 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;
}
// Current implementation only supports 1 scope per service head.
string scopeName = configuration.ScopeNames[0];
if (provisioning.ScopeExists(scopeName) || provisioning.TemplateExists(scopeName))
{
result.TestResult = DiagConstants.SUCCESS;
}
else
{
result.TestResult = DiagConstants.TEMPLATE_OR_SCOPE_DOES_NOT_EXIST;
}
}
}
catch (Exception e)
{
result.TestResult = DiagConstants.UNKNOWN_ERROR;
AddExceptionInfo(e, result);
}
return result;
}
/// <summary>
/// Check if a scope exists.
/// </summary>
/// <returns>True - if the scope exists, false - otherwise.</returns>
private bool CheckIfScopeExists()
{
using (var serverConnection = new SqlConnection(_serverConnectionString))
{
var provisioning = new SqlSyncScopeProvisioning(serverConnection);
if (!String.IsNullOrEmpty(_configuration.SyncObjectSchema))
{
provisioning.ObjectSchema = _configuration.SyncObjectSchema;
}
if (provisioning.ScopeExists(_clientScopeName))
{
return true;
}
return false;
}
}
public bool NeedsScope(string scopeName, string clientConnectionString)
{
this.peerProvider.Connection.ConnectionString = clientConnectionString;
SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning((SqlConnection)this.peerProvider.Connection);
return !prov.ScopeExists(scopeName);
}
