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(); }
//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(); } }