private static bool ProvisionSqlCe(SqlCeConnection con, Scope scope, DbConnection conToGetScopeDescr)
{
var sqlceProv = new SqlCeSyncScopeProvisioning(con);
sqlceProv.ObjectPrefix = prefix;
if (!sqlceProv.ScopeExists(scope.ToScopeString()))
{
var scopeDescr = new DbSyncScopeDescription(scope.ToScopeString());
var failedTables = AddTablesToScopeDescr(scope.ToTableNames(), scopeDescr, con);
if (failedTables.Count > 0 && conToGetScopeDescr != null)
{
Poster.PostMessage("GetScopeDescription for scope '{0}' from '{1}'", scope.ToScopeString(), conToGetScopeDescr.ConnectionString);
//use scope description from server to intitialize the client
scopeDescr = GetScopeDescription(scope, conToGetScopeDescr);
}
sqlceProv.PopulateFromScopeDescription(scopeDescr);
sqlceProv.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
sqlceProv.Apply();
return(true);
}
return(false);
}
private static void createScope(SqlCeConnection connection)
{
var scopeDesc = new DbSyncScopeDescription("SyncScope");
// Definition for Customer.
DbSyncTableDescription customerDescription =
SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Company", connection);
scopeDesc.Tables.Add(customerDescription);
customerDescription =
SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Contact", connection);
scopeDesc.Tables.Add(customerDescription);
// Create a provisioning object for "SyncScope". We specify that
// base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1),
// and that all synchronization-related objects should be created in a
// database schema named "Sync". If you specify a schema, it must already exist
// in the database.
var serverConfig = new SqlCeSyncScopeProvisioning(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(connection);
connection.Close();
}
static void Main(string[] args)
{
SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\dev\SyncTest\SyncSQLServerAndSQLCompact\data\SyncCompactDB.sdf'");
SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQL2008; Initial Catalog=SyncDB; Integrated Security=True");
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
clientProvision.Apply();
}
static void Main(string[] args)
{
SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\dev\SyncTest\SyncSQLServerAndSQLCompact\data\SyncCompactDB.sdf'");
SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQL2008; Initial Catalog=SyncDB; Integrated Security=True");
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
clientProvision.Apply();
}
//For Compact databases that are not initialized with a snapshot,
//get the schema and initial data from a server database.
//<snippetOCSv2_CS_Basic_PeerWithCe_CheckIfProviderNeedsSchema>
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider providerToCheck, DbSyncProvider providerWithSchema)
{
//If one of the providers is a SqlCeSyncProvider and it needs
//to be initialized, retrieve the schema from the other provider
//if that provider is a DbSyncProvider; otherwise configure a
//DbSyncProvider, connect to the server, and retrieve the schema.
if (providerToCheck != null)
{
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
SqlCeConnection ceConn = (SqlCeConnection)providerToCheck.Connection;
string scopeName = providerToCheck.ScopeName;
if (!ceConfig.ScopeExists(scopeName, ceConn))
{
DbSyncScopeDescription scopeDesc = providerWithSchema.GetScopeDescription();
ceConfig.ScopeDescription = scopeDesc;
ceConfig.Apply(ceConn);
}
}
}
private void ReadTableValuesForSelectedTab()
{
TabPage tabPage = this.peerTabsControl.SelectedTab;
SqlCeSyncProvider ceProvider = providersCollection[tabPage.Text] as SqlCeSyncProvider;
if (ceProvider != null)
{
SqlCeConnection ceConn = (SqlCeConnection)ceProvider.Connection;
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(ceConn);
string scopeName = ceProvider.ScopeName;
if (!ceConfig.ScopeExists(scopeName))
{
MessageBox.Show(string.Format("Client '{0}' does not contain schema. Synchronize this client with the server to retrieve data.", tabPage.Text),
"UnInitialized Client Database");
}
}
RefreshTables(providersCollection[tabPage.Text].Connection, (TablesViewControl)this.peerTabsControl.SelectedTab.Controls["TablesViewCtrl"]);
}
static void Main(string[] args)
{
string dbpath = @"SyncSoccerScore.sdf";
SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='" + dbpath + "'");
// create a connection to the SyncCompactDB database
//SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='" + dbpath + "'");
SqlConnection serverConn = new SqlConnection(@"Data Source=localhost; Initial Catalog=G:\htmlconvertsql\match_analysis_pdm.mdf; Integrated Security=True");
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("match_analysisScope", serverConn);
// create CE provisioning object based on the ProductsScope
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
}
static void Main(string[] args)
{
//UpgradeDatabasewithCaseSensitive();
// create a connection to the SyncCompactDB database
SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\VisualStudioProjects\SyncSQLServerAndSQLCompact\ClientSQLCompactDB\SyncCompactDB.sdf'");
// create a connection to the SyncDB server database
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);
// create CE provisioning object based on the ProductsScope
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
}
/// <summary>
/// Check to see if the passed in CE provider needs Schema from server.
/// If it does, we'll need to do some extra manipulating of the DbSyncScopeDescription given from the Oracle provider, before
/// we apply it to CE.
/// </summary>
/// <param name="localProvider"></param>
private void CheckIfProviderNeedsSchema(SqlCeSyncProvider localProvider)
{
if (localProvider != null)
{
SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
SqlCeConnection ceConn = (SqlCeConnection)localProvider.Connection;
string scopeName = localProvider.ScopeName;
if (!ceConfig.ScopeExists(scopeName, ceConn))
{
DbSyncScopeDescription scopeDesc = ((DbSyncProvider)ceSharingForm.providersCollection["Server"]).GetScopeDescription();
// We have to manually fix up the Oracle types on the DbSyncColumnDescriptions. Alternatively we could just construct the DbScopeDescription
// from scratch.
for (int i = 0; i < scopeDesc.Tables.Count; i++)
{
for (int j = 0; j < scopeDesc.Tables[i].Columns.Count; j++)
{
// When grabbing the Oracle schema table the type field gets set to the index of that type in the OracleType enumeration.
// We will have to change it to the actual name instead of the index.
scopeDesc.Tables[i].Columns[j].Type = ((OracleType)Int32.Parse(scopeDesc.Tables[i].Columns[j].Type)).ToString().ToLower();
// We also have to convert number to a decimal for CE
if (scopeDesc.Tables[i].Columns[j].Type == "number")
{
scopeDesc.Tables[i].Columns[j].Type = "decimal";
}
// Because the DbSyncColumnDescription only had a number for the Type (which it does not understand), it could not
// auto-fill in the required attributes for that field type. So in the case of a string field, we have to manually
// set the length ourselves. If we wanted to set scale and precision for the previous decimal field we need to do the same.
if (scopeDesc.Tables[i].Columns[j].Type == "nvarchar")
{
scopeDesc.Tables[i].Columns[j].Size = "100";
}
}
}
ceConfig.PopulateFromScopeDescription(scopeDesc);
ceConfig.Apply(ceConn);
}
}
}
public virtual void ApplyStaticFilters(SqlCeSyncScopeProvisioning provision, string syncScope)
{
GetFilters(syncScope);
if (_filters != null && _filters.Count > 0)
{
foreach (SqlCeSyncTableProvisioning syncTable in provision.Tables)
{
if (syncTable.UnquotedLocalName.Equals(Constants.TABLE_EMAILTEMPLATES, StringComparison.InvariantCultureIgnoreCase) ||
syncTable.UnquotedLocalName.Equals(Constants.TABLE_EVENTS, StringComparison.InvariantCultureIgnoreCase))
{
//syncTable.AddFilterColumn("User_Id");
//syncTable.FilterClause = "[side].[User_Id] = " + _filters[0];
}
else if (syncTable.UnquotedLocalName.Equals(Constants.TABLE_GUESTS, StringComparison.InvariantCultureIgnoreCase) ||
syncTable.UnquotedLocalName.Equals(Constants.TABLE_GUESTPHOTOES, StringComparison.InvariantCultureIgnoreCase))
{
//syncTable.AddFilterColumn("Event_EventId");
//syncTable.FilterClause = "[side].[Event_EventId] = " + _filters[0];
}
else if (syncTable.UnquotedLocalName.Equals(Constants.TABLE_PHOTOS, StringComparison.InvariantCultureIgnoreCase))
{
//syncTable.AddFilterColumn("Event_EventId");
//syncTable.FilterClause = "[side].[Event_EventId] = " + _filters[0];
}
else if (syncTable.UnquotedLocalName.Equals("Users", StringComparison.InvariantCultureIgnoreCase))
{
//syncTable.AddFilterColumn("Id");
//syncTable.FilterClause = "[side].[Id] = " + _filters[0];
}
else if (syncTable.UnquotedLocalName.Equals("UserAuthorizations", StringComparison.InvariantCultureIgnoreCase))
{
//syncTable.AddFilterColumn("Id");
//syncTable.FilterClause = "[side].[Id] = " + _filters[1];
}
else if (syncTable.UnquotedLocalName.Equals("Accounts", StringComparison.InvariantCultureIgnoreCase))
{
//syncTable.AddFilterColumn("Id");
//syncTable.FilterClause = "[side].[Id] = " + _filters[2];
}
}
}
}
static void Main(string[] args)
{
// Create the connections over which provisioning and sycnhronization
// are performed. The Utility class handles all functionality that is not
//directly related to synchronization, such as holding connection
//string information and making changes to the server database.
SqlConnection serverConn = new SqlConnection(Utility.ServerConnString);
SqlConnection clientSqlConn = new SqlConnection(Utility.ClientSqlConnString);
SqlCeConnection clientSqlCeConn = new SqlCeConnection(Utility.ClientSqlCeConnString);
// Create a scope named "filtered_customer", and add two tables to the scope.
// GetDescriptionForTable gets the schema of each table, so that tracking
// tables and triggers can be created for that table.
//<snippetOCSv3_CS_Basic_SqlPeer_ScopeDesc>
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
scopeDesc.Tables.Add(
SqlSyncDescriptionBuilder.GetDescriptionForTable("Customer", serverConn));
scopeDesc.Tables.Add(
SqlSyncDescriptionBuilder.GetDescriptionForTable("CustomerContact", serverConn));
//</snippetOCSv3_CS_Basic_SqlPeer_ScopeDesc>
// Create a provisioning object for "filtered_customer" and specify that
// base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1).
//<snippetOCSv3_CS_Basic_SqlPeer_ServerConfig>
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.CreateTableDefault = DbSyncCreationOption.Skip;
// Specify which column(s) in the Customer table to use for filtering data,
// and the filtering clause to use against the tracking table.
// "[side]" is an alias for the tracking table.
serverConfig["Customer"].AddFilterColumn("CustomerType");
serverConfig["Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";
// Configure the scope and change tracking infrastructure.
serverConfig.Apply(serverConn);
// Write the configuration script to a file. You can modify
// this script if necessary and run it against the server
// to customize behavior.
File.WriteAllText("SampleConfigScript.txt",
serverConfig.Script("SyncSamplesDb_SqlPeer1"));
//</snippetOCSv3_CS_Basic_SqlPeer_ServerConfig>
// Retrieve scope information from the server and use the schema that is retrieved
// to provision the SQL Server and SQL Server Compact client databases.
//<snippetOCSv3_CS_Basic_SqlPeer_ClientConfig>
// This database already exists on the server.
DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", serverConn);
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlDesc);
clientSqlConfig.Apply(clientSqlConn);
// This database does not yet exist.
Utility.CreateSqlCeDatabase();
DbSyncScopeDescription clientSqlCeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", serverConn);
SqlCeSyncScopeProvisioning clientSqlCeConfig = new SqlCeSyncScopeProvisioning(clientSqlCeDesc);
clientSqlCeConfig.Apply(clientSqlCeConn);
//</snippetOCSv3_CS_Basic_SqlPeer_ClientConfig>
// Initial synchronization sessions. 7 rows are synchronized:
// all rows (4) from CustomerContact, and the 3 rows from Customer
// that satisfy the filtering criteria.
//<snippetOCSv3_CS_Basic_SqlPeer_InitialSync>
SampleSyncOrchestrator syncOrchestrator;
SyncOperationStatistics syncStats;
// Data is downloaded from the server to the SQL Server client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn),
new SqlSyncProvider("filtered_customer", serverConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
// Data is downloaded from the SQL Server client to the
// SQL Server Compact client.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCeConn),
new SqlSyncProvider("filtered_customer", clientSqlConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "initial");
//</snippetOCSv3_CS_Basic_SqlPeer_InitialSync>
// Make changes on the server: 1 insert, 1 update, and 1 delete.
Utility.MakeDataChangesOnServer();
// Synchronize again. Three changes were made on the server, but
// only two of them applied to rows that are in the "filtered_customer"
// scope. The other row is not synchronized.
// Notice that the order of synchronization is different from the initial
// sessions, but the two changes are propagated to all nodes.
syncOrchestrator = new SampleSyncOrchestrator(
new SqlCeSyncProvider("filtered_customer", clientSqlCeConn),
new SqlSyncProvider("filtered_customer", serverConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
syncOrchestrator = new SampleSyncOrchestrator(
new SqlSyncProvider("filtered_customer", clientSqlConn),
new SqlCeSyncProvider("filtered_customer", clientSqlCeConn)
);
syncStats = syncOrchestrator.Synchronize();
syncOrchestrator.DisplayStats(syncStats, "subsequent");
serverConn.Close();
serverConn.Dispose();
clientSqlConn.Close();
clientSqlConn.Dispose();
clientSqlCeConn.Close();
clientSqlCeConn.Dispose();
Console.Write("\nPress any key to exit.");
Console.Read();
}
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);
}
}
}
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();
}
}
private static void createScope(SqlCeConnection connection)
{
var scopeDesc = new DbSyncScopeDescription("SyncScope");
// Definition for Customer.
DbSyncTableDescription customerDescription =
SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Company", connection);
scopeDesc.Tables.Add(customerDescription);
customerDescription =
SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Contact", connection);
scopeDesc.Tables.Add(customerDescription);
// Create a provisioning object for "SyncScope". We specify that
// base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1),
// and that all synchronization-related objects should be created in a
// database schema named "Sync". If you specify a schema, it must already exist
// in the database.
var serverConfig = new SqlCeSyncScopeProvisioning(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(connection);
connection.Close();
}