Geek Answers Handbook

How to pass parameter to Microsoft Sync 2.1 generated stored procedures

I am using Microsoft sync framework version 2.1

we are trying to implement version control of the database, that is, if there is a change in the table schema in the server database, all or some client should still be able to synchronize their data without making the same schema changes or without accepting an updated database. At a higher level, we need to support multiple versions of applications for different clients with the same DB.

I am trying to pass the version of the application as a parameter to all stored procedures so that I can process versions of the data for multiple clients. I can pass the Parameter to the select_chagnes stored procedure .. but My question is how to pass the version number to all synchronized generated stored procedures so that the @version parameter is generated in all synchronized procedures.

OR

Any suggestions for maintaining customer data are welcomed by our primary goal, allowing existing customers to synchronize their database without making the latest changes to the database. so clients can have multiple versions of applications that point to the same server database.

+7
source share
1 answer

Note The solution I have does not work if you delete columns or tables that are provided by old clients. If you want to delete columns, you need to follow several steps. First, upgrade each to version 1. If all clients are updated, you can delete columns and tables.

Possible Solution

If you understand me well, you want one area or template to have several prepared configurations.

YourScope:

  • (Version1)
    • Table 1 (ColumnA, ColumnB)
  • (Version2)
    • Table 1 (ColumnA, ColumnB, NewColumnC)
    • NewTable2 (ColumnX, ColumnY, ColumnZ)

In my opinion, it is better to use:

Version1_YourScope:

  • Table 1 (ColumnA, ColumnB)

Version2_YourScope:

  • Table 1 (ColumnA, ColumnB, NewColumnC)
  • NewTable2 (ColumnX Columny ColumnZ)

Thus, in this case, you do not need to process the procedures inside the Sync Framework, now you need to process the versions outside , giving the right clients the right set of scopes.

What to do:

This change requires some changes during preparation. The presence of areas overlapping each other gives some problems:

  • You should have two BulkTypes for table 1 (1 without NewColumnC and 1 type with this new column inside)
  • You should have two choices for table 1
  • You must have two BulkInsert sp for Table1
  • You want to have only one set of triggers for Table1
  • You want to have only one tracking table for table 1
  • ...

During preparation, you are probably using SqlSyncScopeProvider.Apply() . There is also a function that returns a script instead of using a script: SqlSyncScopeProvider.Script() . This returns the initialization of the script.

So you can do something like this:

1: Use the following settings to make overlapping areas possible:

 _scopeProvisioning.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create); _scopeProvisioning.SetCreateTableDefault(DbSyncCreationOption.Skip); _scopeProvisioning.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting); _scopeProvisioning.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting); _scopeProvisioning.SetCreateTriggersDefault(DbSyncCreationOption.CreateOrUseExisting);

2: Get provisioning script

 var builder = new StringBuilder(_scopeProvisioning.Script());

3: for each table, rename <tablename>_<procedure/bulktype> to <scopename>_<tablename>_<procedure/bulktype>

 // Rename <tablename>_selectchanges to <scopename>_<tablename>_selectchanges and also all other stored procedures and bulk type builder = builder.Replace(String.Format("CREATE PROCEDURE [{0}_selectchanges", table.Name), String.Format("CREATE PROCEDURE [sync].[{1}_{0}_selectchanges", table.Name, scope.Name)); builder = builder.Replace(String.Format("SelChngProc=\"[{0}_selectchanges", table.Name), String.Format("SelChngProc=\"[sync].[{1}_{0}_selectchanges", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_BulkType]", table.Name), String.Format("[{1}_{0}_BulkType]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_selectrow]", table.Name), String.Format("[{1}_{0}_selectrow]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_insert]", table.Name), String.Format("[{1}_{0}_insert]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_update]", table.Name), String.Format("[{1}_{0}_update]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_delete]", table.Name), String.Format("[{1}_{0}_delete]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_insertmetadata]", table.Name), String.Format("[{1}_{0}_insertmetadata]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_updatemetadata]", table.Name), String.Format("[{1}_{0}_updatemetadata]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_deletemetadata]", table.Name), String.Format("[{1}_{0}_deletemetadata]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_bulkinsert]", table.Name), String.Format("[{1}_{0}_bulkinsert]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_bulkupdate]", table.Name), String.Format("[{1}_{0}_bulkupdate]", table.Name, scope.Name)); builder = builder.Replace(String.Format("[{0}_bulkdelete]", table.Name), String.Format("[{1}_{0}_bulkdelete]", table.Name, scope.Name));

4: For each table that overlaps with an existing area, change CREATE TRIGGER to ALTER TRIGGER, as they already exist in the database

 builder = builder.Replace(String.Format("CREATE TRIGGER [{0}_insert_trigger]", table.Name), String.Format("ALTER TRIGGER [{0}_insert_trigger]", table.Name)); builder = builder.Replace(String.Format("CREATE TRIGGER [{0}_update_trigger]", table.Name), String.Format("ALTER TRIGGER [{0}_update_trigger]", table.Name)); builder = builder.Replace(String.Format("CREATE TRIGGER [{0}_delete_trigger]", table.Name), String.Format("ALTER TRIGGER [{0}_delete_trigger]", table.Name));

5: Run the new script. Note that the script contains many GO statements. You need to execute everything between two GO in one SqlCommand.

 string[] seperatedScript = GetProvisionScriptSplittedOnGOstatement(builder.ToString); foreach(string command in seperatedScript) { new SqlCommand(command, connection).ExecuteNonQuery(); // make sure you dispose SqlCommand correctly. Not in this example }

6: Ensure that the positions of old clients are only Version1_YourScope and the new clients are only Version2_YourScope, so the client side does not overlap between multiple versions.

If you use templates because you want to pass filter parameters, you need to know about the following things:

  • the different filtered columns for the table, which are mentioned in many areas, cause problems because the triggers are not aware of multiple areas using multiple filtered columns.
  • Creating a new filtered column requires a new column for an existing tracking table.

Good luck

+4
source

More articles:


All Articles