Retrieving Sync Framework scope definition details


Some of the lacking features in the SyncFx V2’s sync scope provisioning API is the ability to list all scopes, modify/delete/cleanup scope or access the scope details from the database.

So I thought I’d post some quick workarounds on how to go about doing the above tasks.

First will be on accessing scope details from the database.

Let’s take this example  from the SyncFx documentation for creating a new scope:

Code Snippet
  1.  
  2. // Create a scope named "filtered_customer", and add two tables to the scope.
  3. // GetDescriptionForTable gets the schema of each table, so that tracking
  4. // tables and triggers can be created for that table. For Customer, we add
  5. // the entire table. For CustomerContact, we add only two of the columns.
  6. DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
  7.  
  8. // Definition for Customer.
  9. DbSyncTableDescription customerDescription =
  10.     SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);
  11.  
  12. scopeDesc.Tables.Add(customerDescription);
  13.  
  14. // Definition for CustomerContact, including the list of columns to include.
  15. Collection<string> columnsToInclude = new Collection<string>();
  16. columnsToInclude.Add("CustomerId");
  17. columnsToInclude.Add("PhoneType");
  18. DbSyncTableDescription customerContactDescription =
  19.     SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", columnsToInclude, serverConn);
  20.  
  21. scopeDesc.Tables.Add(customerContactDescription);
  22.  
  23. // Create a provisioning object for "filtered_customer". We specify that
  24. // base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1),
  25. // and that all synchronization-related objects should be created in a
  26. // database schema named "Sync". If you specify a schema, it must already exist
  27. // in the database.
  28. SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
  29. serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
  30. serverConfig.ObjectSchema = "Sync";
  31.  
  32. // Specify which column(s) in the Customer table to use for filtering data,
  33. // and the filtering clause to use against the tracking table.
  34. // "[side]" is an alias for the tracking table.
  35. serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
  36. serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = ‘Retail’";
  37.  
  38. // Configure the scope and change-tracking infrastructure.
  39. serverConfig.Apply(serverConn);

 

Assuming you’d like to retrieve the objects created by the above sync scope provisioning such as the name of the tracking table, triggers and stored procedures.

Here’s an example code for retrieving those information:

Code Snippet
  1. SqlConnection serverConn = new SqlConnection(Utility.ConnStr_SqlSync_Server);
  2. SqlCommand command = new SqlCommand(@"SELECT config_data
  3.                                       FROM Sync.scope_info
  4.                                         INNER JOIN Sync.scope_config
  5.                                         ON Sync.scope_info.scope_config_id = Sync.scope_config.config_id
  6.                                       WHERE scope_name = ‘filtered_customer’", serverConn);
  7. using (serverConn)
  8. {
  9.     serverConn.Open();
  10.     SqlDataReader reader = command.ExecuteReader();
  11.  
  12.     if (reader.Read())
  13.     {
  14.         StringReader textReader = new StringReader(reader.GetString(0));
  15.         XmlSerializer serializer = new XmlSerializer(typeof(SqlSyncProviderScopeConfiguration));
  16.         SqlSyncProviderScopeConfiguration scopeConfig = (SqlSyncProviderScopeConfiguration)serializer.Deserialize(textReader);
  17.         textReader.Dispose();
  18.  
  19.         foreach (SqlSyncProviderAdapterConfiguration adapterConfig in scopeConfig.AdapterConfigurations)
  20.         {
  21.             Console.WriteLine("Tracking Tablename: " + adapterConfig.TrackingTableName);
  22.             Console.WriteLine("Synched Tablename: " + adapterConfig.TableName);
  23.  
  24.             Console.WriteLine("Included Columns: " + adapterConfig.UpdateProcName);
  25.        
  26.             foreach (DbSyncColumnDescription syncCol in adapterConfig.Columns)
  27.             {
  28.                 Console.WriteLine( "\t" + syncCol.UnquotedName + " " + syncCol.Type + " " + (syncCol.IsPrimaryKey?"PK":""));
  29.             }
  30.  
  31.             Console.WriteLine("Insert Metadata SP Name: " + adapterConfig.InsertMetadataProcName);
  32.             Console.WriteLine("Update Metadata SP Name: " + adapterConfig.UpdateMetadataProcName);
  33.             Console.WriteLine("Delete Metadata SP Name: " + adapterConfig.DeleteMetadataProcName);
  34.  
  35.             Console.WriteLine("Insert Trigger Name: " + adapterConfig.InsertTriggerName);
  36.             Console.WriteLine("Update Trigger Name: " + adapterConfig.UpdateTriggerName);
  37.             Console.WriteLine("Delete Trigger Name: " + adapterConfig.DeleteTriggerName);
  38.  
  39.             Console.WriteLine("Insert SP Name: " + adapterConfig.InsertProcName);
  40.             Console.WriteLine("Update SP Name: " + adapterConfig.UpdateProcName);
  41.             Console.WriteLine("Delete SP Name: " + adapterConfig.DeleteProcName);
  42.  
  43.             Console.WriteLine("Select Changes SP Name: " + adapterConfig.SelectChangesProcName);
  44.             Console.WriteLine("Select Row SP Name: " + adapterConfig.SelectRowProcName);
  45.         }
  46.     }
  47.     reader.Close();
  48. }

 

Hope this helps.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: