locked
How choose which rows that will be synchronized? RRS feed

  • Question

  • Hi !

    Is there a way to choose which row in my table will be sync ? (or updates)

    For a sample, I have a table with 1000 rows, Can I sync only first 500 rows?

    Regards,
    Andre.
    • Moved by Max Wang_1983 Thursday, April 21, 2011 12:56 AM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, October 30, 2009 7:19 PM

Answers

  • Hi,

    If you only want to sync Sales.CustomerId < 500 in your sync scope, you can use this way to do it. It is a static filter. It means that you cannot change the filter column and filter clause after the initial sync. Otherwise, the knowledge will not be correct, and some rows will not be synced until you update them again. For your scenarios, do you only want to sync a subset of rows which has columns values can always satisfy this static filter? If not, it will not solve your problem. For example, if you want to sync Sales.CustomerId < 600 or Sales.CustomerType = "Retail" next time, you need to create different sync scopes with new static filter. If you sync selection condition will keep changing (dynamic filtering) every time, you will have many many sync scopes, and every sync is an initial sync.

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Monday, November 2, 2009 10:04 PM
    Moderator

All replies

  • Hi,

    Sync Framework database providers do not support dynamic syncs.  It means that there is not simple setting or APIs for you to sync the first 500 rows of a table.

    It is possible to make it work still. For example, if you use Collaboration providers, you can consider writing your insert/update commands to check if the rows are what you want for the current sync. If not, you can fail the SQL command to cause ChangeFailedEvent and use RetryNextSync to handle this failure. In this case, only a subset of rows of the table will be applied in this sync session. But all rows will be sent to change application side anyway.

    If you don't care about the correctness of the knowledge (or change tracking medadata), you can change the select command to only select the subset of rows. But the remaining rows will not be synced in the following syncs until you update them again.

    Thanks,
    Dong 
    This posting is provided AS IS with no warranties, and confers no rights.
    Friday, October 30, 2009 10:33 PM
    Moderator
  • Hi Dong.

    I unproposed your first answer to drill down in this issue. Let's do it...

    - Can I use "AddFilterColumn" method to select the first 500 rows (or IDs) ?

    I'm thinking in a filter like :
    serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerId");
    serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerId] < 500";

    This is the sample that I'm studing:
    SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
    serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    serverConfig.ObjectSchema = "Sync";
    
    // 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.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
    serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";
    
    // Configure the scope and change-tracking infrastructure.
    serverConfig.Apply(serverConn);

    Thanks,
    André


    Saturday, October 31, 2009 7:42 PM
  • Hi,

    If you only want to sync Sales.CustomerId < 500 in your sync scope, you can use this way to do it. It is a static filter. It means that you cannot change the filter column and filter clause after the initial sync. Otherwise, the knowledge will not be correct, and some rows will not be synced until you update them again. For your scenarios, do you only want to sync a subset of rows which has columns values can always satisfy this static filter? If not, it will not solve your problem. For example, if you want to sync Sales.CustomerId < 600 or Sales.CustomerType = "Retail" next time, you need to create different sync scopes with new static filter. If you sync selection condition will keep changing (dynamic filtering) every time, you will have many many sync scopes, and every sync is an initial sync.

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Monday, November 2, 2009 10:04 PM
    Moderator

  • Thanks for your answers, was very helpful.
    Monday, November 2, 2009 11:57 PM
  • Hey,

    I am facing the excatly same problem.

    Have you found a solution for dynamic filtering yet?

    Would be great if you can provide me some help. This stuff is quite important for my current app, 'cause I wanna filter a huge database and cannot effort it synching all the rows of the tables.

    Best regards,
    Harald
    Sunday, December 13, 2009 8:45 PM
  • Dynamic filtering is not officially supported out of the box in Sync framework V2 for Database providers.
    We are working on improving the filtering support in next versions.

    However, as Dong mentioned above (post @Friday, October 30, 2009 10:33 PM)
    "It is possible to make it work still. For example, if you use Collaboration providers, you can consider writing your insert/update commands to check if the rows are what you want for the current sync. If not, you can fail the SQL command to cause ChangeFailedEvent and use RetryNextSync to handle this failure. In this case, only a subset of rows of the table will be applied in this sync session. But all rows will be sent to change application side anyway.
    "

    Monday, December 14, 2009 6:11 PM
    Answerer