/// <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();
}
}
}
/// <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);
}
/// <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);
}
/// <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();
}
}
}
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);
}
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 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);
}
}
/// <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();
}
}
}
/// <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);
}
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();
}
}
}
/// <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>
/// 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);
}
/// <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 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 bool Sync(SqlConnection clientConnection)
{
// ServerSideSync
String scope = "VideoScope";
SqlSyncProvider serverProvider = new SqlSyncProvider();
serverProvider.ScopeName = scope;
serverProvider.Connection = new SqlConnection();
serverProvider.Connection.ConnectionString = @"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = MoviesDB; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; MultiSubnetFailover = False";
//Config of the Scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope);
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)serverProvider.Connection);
if (!serverConfig.ScopeExists(scope))
{
serverProvider.ObjectSchema = "dbo.";
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Actor", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Director", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Genre", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Movie", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieActor", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieDirector", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieGenre", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
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();
}
//ClientSideSyncserverConfig
// create the sync orhcestrator
SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
syncOrchestrator.LocalProvider = serverProvider;
// set the remote provider of orchestrator to a server sync provider associated with
// the ProductsScope in the SyncDB server database
syncOrchestrator.RemoteProvider = new SqlSyncProvider(scope, clientConnection);
// set the direction of sync session to Upload and Download
syncOrchestrator.Direction = SyncDirectionOrder.Upload;
// execute the synchronization process
SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
// print statistics
Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
Console.WriteLine(String.Empty);
return(false);
}
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>
/// Synchronizes tracked client and server tables
/// </summary>
static public Task Sync()
{
return(Task.Run(() =>
{
// bit to flip if detected new table
bool dbChanged = false;
// main loop for all tables in database
for (int i = 0; i < main.context.LocalTables.Count; i++)
{
// string[] registerInfo = main.context.LocalTables[i];
// get table info
string tblname = main.context.LocalTables[i].name;
string id = main.context.LocalTables[i].pkColumn;
// build description for tables
DbSyncTableDescription serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tblname, serverConn);
var clientTableDesc = new DbSyncTableDescription();
// if the table is not set up in the database, provision the server
if (!serverProvision.ScopeExists(tblname))
{
// db is changed because new table
dbChanged = true;
// get the server scope description from table name
var serverScopeDesc = new DbSyncScopeDescription(tblname);
// table description settings
serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tblname, serverConn);
serverTableDesc.GlobalName = tblname;
serverTableDesc.LocalName = tblname;
serverScopeDesc.Tables.Add(serverTableDesc);
// remove the PK as we do not need to track it (causes issues)
serverScopeDesc.Tables[tblname].Columns.Remove(serverScopeDesc.Tables[tblname].Columns[id]);
// trick the framework so it thinks the GUID is PK
try
{
serverScopeDesc.Tables[tblname].Columns["GUID"].IsPrimaryKey = true;
}
catch (Exception ex)
{
Debug.WriteLine(tblname + " does not have a GUID column. Please add a not null GUID column with newid() for default value.");
Debug.WriteLine(ex.Message);
}
// provision the server
serverProvision.PopulateFromScopeDescription(serverScopeDesc);
serverProvision.ObjectPrefix = trackingPrefix;
serverProvision.Apply();
}
// if the table is not set up in the database, provision the client
if (!clientProvision.ScopeExists(tblname))
{
// db is changed because new table
dbChanged = true;
var clientScopeDesc = new DbSyncScopeDescription(tblname);
// try to find the table locally. if exception, catch it
// and create the table from the server definition with CreateLocalFromServer()
try
{
// table exists, then
clientTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tblname, clientConn);
}
catch (Exception)
{
// table does not exist, then
CreateLocalFromServer(serverConnectionString, clientConnectionString, tblname, id);
clientTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tblname, clientConn);
}
// settings for client table description
clientTableDesc.GlobalName = tblname;
clientTableDesc.LocalName = tblname;
clientScopeDesc.Tables.Add(clientTableDesc);
clientScopeDesc.Tables[tblname].Columns.Remove(clientScopeDesc.Tables[tblname].Columns[id]);
// provision the client
clientProvision.PopulateFromScopeDescription(clientScopeDesc);
clientProvision.ObjectPrefix = trackingPrefix;
clientProvision.Apply();
}
// SyncTable(tblname, id);
// start the synchronize process, setup providers
var localProvider = new SqlSyncProvider(tblname, clientConn);
var remoteProvider = new SqlSyncProvider(tblname, serverConn);
// set the prefix for the tracking tables
localProvider.ObjectPrefix = trackingPrefix;
remoteProvider.ObjectPrefix = trackingPrefix;
try
{
// attach client and server to sync orchestrator
syncOrchestrator.LocalProvider = localProvider;
syncOrchestrator.RemoteProvider = remoteProvider;
// set the direction of sync session. Upload/download favors server changes over client(?)
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
// execute the synchronization process
SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
// print syncing stats
Debug.WriteLine("Sync table: " + tblname);
Debug.WriteLine("Start Time: " + syncStats.SyncStartTime);
Debug.WriteLine("Changes sent to server: " + syncStats.UploadChangesTotal);
Debug.WriteLine("Changes downloaded from server: " + syncStats.DownloadChangesTotal);
Debug.WriteLine("Download changes failed: " + syncStats.DownloadChangesFailed);
Debug.WriteLine("Complete Time: " + syncStats.SyncEndTime);
// below is a routine that will update local PKs with server generated
// PKs if they are different after sync'd.
// this is VERY important if adding/editing multiple related tables, as the PK update
// will trigger a cascade to all foreign keys, keeping the relationships in tact
// and sync'd up properly with the server.
// this could probably be rewritten, pretty crude at the moment.
//------------------------------------------------------
// query back to the server to get the autogenerated PKs
using (var connection = new SqlConnection(serverConnectionString))
{
var cmdServerPK = new SqlCommand("SELECT " + id + ", GUID FROM " + tblname, connection);
cmdServerPK.Connection.Open();
SqlDataReader serverPKs = cmdServerPK.ExecuteReader();
var dtServerPK = new DataTable();
// load the PK, GUID into a datatable
dtServerPK.Load(serverPKs);
// foreach row in PK, GUID
foreach (DataRow row in dtServerPK.Rows)
{
// open a connection to local database
using (var clientconnection = new SqlConnection(clientConnectionString))
{
// query where GUID and PK are equal to server GUID and PK
string sql = "SELECT " + id + " FROM " + tblname + " WHERE GUID = '" + row["GUID"] + "' AND " + id + " = '" + row[id] + "'";
var clientpk = new SqlCommand(sql, clientconnection);
clientpk.Connection.Open();
SqlDataReader clientPKs = clientpk.ExecuteReader();
// load the PK, GUID into a datatable
var clientdt = new DataTable();
clientdt.Load(clientPKs);
clientpk.Connection.Close();
// if there are no rows, that means the PK in client is different than server
if (clientdt.Rows.Count == 0)
{
// update the local PK to server PK where GUID is the same
// again, this will trigger a cascade on FKs in the client database,
// so when the table referencing it is sync'd, it will be congruent with
// both client and server db.
var updatepk = new SqlCommand("UPDATE " + tblname +
" SET " + id + " = " + row[id] +
" WHERE GUID = '" + row["GUID"] + "'", clientconnection);
updatepk.Connection.Open();
updatepk.ExecuteScalar();
updatepk.Connection.Close();
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
// recreate all database constraints after building tables if the database has changed
if (dbChanged)
{
CreateConstraints();
}
}));
}
public bool Sync(string clientConnectionstring)
{
SqlConnection clientConnection = new SqlConnection();
clientConnection.ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=VideothequeDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
// ServerSideSync
String scope = "VideoScope";
SqlSyncProvider serverProvider = new SqlSyncProvider();
serverProvider.ScopeName = scope;
serverProvider.Connection = new SqlConnection();
serverProvider.Connection.ConnectionString = @"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = MoviesDB; Integrated Security = True; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; MultiSubnetFailover = False";
//Config of the Scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope);
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)serverProvider.Connection);
if (!serverConfig.ScopeExists(scope))
{
serverProvider.ObjectSchema = "dbo.";
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Actor", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Director", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Genre", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Movie", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieActor", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieDirector", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieGenre", (System.Data.SqlClient.SqlConnection)serverProvider.Connection));
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();
}
//ClientSideSyncserverConfig
SqlSyncProvider clientProvider = new SqlSyncProvider();
clientProvider.ScopeName = scope;
clientProvider.Connection = new SqlConnection();
clientProvider.Connection.ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=VideothequeDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
//Config of the Scope
DbSyncScopeDescription scopeDescClient = new DbSyncScopeDescription(scope);
SqlSyncScopeProvisioning clientConfig = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)clientProvider.Connection);
if (!clientConfig.ScopeExists(scope))
{
serverProvider.ObjectSchema = "dbo.";
scopeDescClient.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Actor", (System.Data.SqlClient.SqlConnection)clientProvider.Connection));
scopeDescClient.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Director", (System.Data.SqlClient.SqlConnection)clientProvider.Connection));
scopeDescClient.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Genre", (System.Data.SqlClient.SqlConnection)clientProvider.Connection));
scopeDescClient.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Movie", (System.Data.SqlClient.SqlConnection)clientProvider.Connection));
scopeDescClient.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieActor", (System.Data.SqlClient.SqlConnection)clientProvider.Connection));
scopeDescClient.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieDirector", (System.Data.SqlClient.SqlConnection)clientProvider.Connection));
scopeDescClient.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("MovieGenre", (System.Data.SqlClient.SqlConnection)clientProvider.Connection));
clientConfig.PopulateFromScopeDescription(scopeDescClient);
//indicate that the base table already exists and does not need to be created
clientConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
//provision the server
clientConfig.Apply();
}
// create the sync orhcestrator
SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
syncOrchestrator.LocalProvider = serverProvider;
// set the remote provider of orchestrator to a server sync provider associated with
// the ProductsScope in the SyncDB server database
syncOrchestrator.RemoteProvider = clientProvider;
// set the direction of sync session to Upload and Download
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
// subscribe for errors that occur when applying changes to the client
((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler <DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);
SyncConflictResolver ConflictResolver = new SyncConflictResolver();
ConflictResolver.ClientDeleteServerUpdateAction = ResolveAction.ServerWins;
ConflictResolver.ClientUpdateServerDeleteAction = ResolveAction.ServerWins;
ConflictResolver.ClientInsertServerInsertAction = ResolveAction.ServerWins;
ConflictResolver.ClientUpdateServerUpdateAction = ResolveAction.ServerWins;
// execute the synchronization process
SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
// print statistics
return(true);
}
/// <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();
}
}
}
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 static void ProvisionTable(SqlConnection server, SqlConnection client,
string tableName, bool deprovisonScopeFirst)
{
bool clientMode = !client.ConnectionString.Equals(server.ConnectionString);
if (client.State == System.Data.ConnectionState.Closed)
{
client.Open();
}
SqlCommand command = client.CreateCommand();
DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(tableName);
SqlSyncScopeProvisioning destinationConfig = new SqlSyncScopeProvisioning(client);
if (deprovisonScopeFirst && destinationConfig.ScopeExists(tableName))
{
Deprovisioning.DropTable(client, tableName);
Deprovisioning.DeprovisonScope(client, tableName);
}
// Get table info from server
DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, server);
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.");
}
// Force uniqueidentifier as primary key to enable two way, if needed.
if (uniqueColumn != null &&
!uniqueColumn.IsPrimaryKey)
{
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;
}
// Remove identity columns from scope so that we don't get key conflicts.
if (identityColumn != null && identityColumn != joinColumn)
{
table.Columns.Remove(identityColumn);
}
destinationConfig.SetCreateTableDefault(clientMode ? DbSyncCreationOption.Create
: DbSyncCreationOption.Skip);
// Add the table that we found on the server to the description.
scopeDescription.Tables.Add(table);
//It is important to call this after the tables have been added to the scope
destinationConfig.PopulateFromScopeDescription(scopeDescription);
// Drop the table from the client if we are in client mode
// TODO We should sync the table first, but only if it is upload.
if (clientMode)
{
if (client.State == System.Data.ConnectionState.Closed)
{
client.Open();
}
Deprovisioning.DropTable(client, tableName);
}
try
{
//provision the client
destinationConfig.Apply();
}
catch (DbProvisioningException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.Error.WriteLine(ex.Message);
Console.ResetColor();
}
// Readd indentity column back onto client as primary key.
if (clientMode && identityColumn != null && identityColumn != joinColumn)
{
string sql = @"ALTER TABLE [{1}] ADD {0} int IDENTITY(1,1) NOT NULL;
ALTER TABLE [{1}] DROP CONSTRAINT PK_{1};
ALTER TABLE [{1}] ADD CONSTRAINT PK_{1} PRIMARY KEY CLUSTERED ({0});";
command.CommandText = String.Format(sql, identityColumn.QuotedName, tableName);
command.ExecuteNonQuery();
}
// If we have a uniqueidentifier column and on client. Add index and default value.
if (uniqueColumn != null && clientMode && uniqueColumn == joinColumn)
{
string sql = @"ALTER TABLE [{1}] ADD UNIQUE ({0});
ALTER TABLE [{1}] ADD CONSTRAINT [DF_{1}_{2}]
DEFAULT (newid()) FOR {0};";
command.CommandText = String.Format(sql, uniqueColumn.QuotedName,
tableName,
uniqueColumn.UnquotedName.Replace(' ', '_'));
command.ExecuteNonQuery();
}
if (geometryColumn == null)
{
return;
}
if (server.State == System.Data.ConnectionState.Closed)
{
server.Open();
}
SqlCommand servercommand = server.CreateCommand();
servercommand.CommandText = string.Format(@"SELECT TOP 1 [srid]
FROM [geometry_columns]
WHERE [f_table_name] = '{0}'",
tableName);
int?srid = servercommand.ExecuteScalar() as int?;
if (!srid.HasValue)
{
srid = libsyncing.Properties.Settings.Default.defaultsrid;
ProgressUpdate("No SRID found in geometry_columns. Using default: " + srid);
}
// Everything after this point is for geometry based tables only.
if (clientMode)
{
try
{
string sql = String.Format(@"ALTER TABLE [{0}] ALTER COLUMN [{1}] geometry;
CREATE SPATIAL INDEX [ogr_{0}_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.UnquotedName);
// Index
command.CommandText = sql;
//command.Parameters.AddWithValue("@table", tableName);
//command.Parameters.AddWithValue("!geomcolumn", geometryColumn.QuotedName);
command.ExecuteNonQuery();
}
catch (SqlException sqlException)
{
if (!sqlException.Message.Contains("already exists"))
{
throw;
}
}
}
// Server and client. Drop trigger and create WKT transfer trigger.
Deprovisioning.DropTableGeomTrigger(client, tableName);
command.CommandText = string.Format(@"CREATE TRIGGER [dbo].[{0}_GEOMSRID_trigger]
ON [dbo].[{0}]
AFTER INSERT, UPDATE
AS
BEGIN
IF CONTEXT_INFO() = 0xFF
RETURN
SET CONTEXT_INFO 0xFF
UPDATE [dbo].[{0}]
SET [{1}].STSrid = {2}
FROM [dbo].[{0}]
WHERE {3} IN (SELECT {3} FROM inserted
WHERE inserted.[{1}] IS NOT NULL
AND [{1}].STSrid <> {2}
)
SET CONTEXT_INFO 0x00
END", tableName, geometryColumn.UnquotedName, srid.Value, joinColumn.QuotedName);
command.ExecuteNonQuery();
// Alter selectedchanges stored procedure to convert geometry to WKT on fly.
Server clientserver = new Server(client.DataSource);
Database db = clientserver.Databases[client.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));
try
{
sp.Alter();
}
catch (FailedOperationException ex)
{
if (ex.Operation != "Alter")
{
throw;
}
}
}
}
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();
}
}
}
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();
}
}