Adding Filter to Local Database Cache generated sync


One of the commonly asked question in the Sync Framework forums is on how to add filtering to the synchronization code generated by the Local Database Cache designer in Visual Studio.

When you use the designer, you are not given an option to filter the initial sync. Likewise, the code generated uses SyncAdapter instead of SqlSyncAdapterBuilder which would have allowed you to add the filter easily.

You can of course modify the designer generated code by adding your filter in the InitializeCommands section of the generated code. You can modify the CommandText to add the filter condition and add a new parameter as well. However, you risk losing all of your changes once you re-generate the synchronization codes when you re-launch the designer.

So here’s a quick workaround.

Code Snippet
  1. private void button1_Click(object sender, EventArgs e)
  2. {
  3.     // Call SyncAgent.Synchronize() to initiate the synchronization process.
  4.     // Synchronization only updates the local database, not your project’s data source.
  5.     LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
  6.  
  7.     AddFilters(syncAgent);
  8.  
  9.     //now, let’s pass a value to our filter before calling the sync
  10.     syncAgent.Configuration.SyncParameters.Add(new SyncParameter("@CustomerID", 1));
  11.  
  12.     Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
  13.     
  14.     string message = "Total Changes downloaded and applied: " + syncStats.DownloadChangesApplied.ToString();
  15.     message = message + Environment.NewLine + "Total Changes uploaded and applied: " + syncStats.UploadChangesApplied.ToString();
  16.     
  17.     MessageBox.Show(message);
  18.     
  19.     // TODO: Reload your project data source from the local database (for example, call the TableAdapter.Fill method).
  20.  
  21. }

The designer by default creates SyncAgent that encapsulates the local and remote providers for the sync.  So I created a new method that takes the SyncAgent as the parameter and retrieve the provider where we want to add the filter. In this sample code, we are applying the filter on the remote provider.

Code Snippet
  1. private static void AddFilters(LocalDataCache1SyncAgent syncAgent)
  2. {
  3.     //let’s retrieve our sync provider so we can get access to the adapter
  4.     LocalDataCache1ServerSyncProvider remoteProvider = (LocalDataCache1ServerSyncProvider)syncAgent.RemoteProvider;
  5.  
  6.     //let’s build our filter
  7.     //filter on the change table instead of the table being synched
  8.     string myFilter = " (CT.CustomerId=@CustomerId) AND ";
  9.  
  10.     //let’s take the command objects from the adapter
  11.     IDbCommand selectIncrementalInsertsCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand;
  12.     IDbCommand selectIncrementalUpdatesCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalUpdatesCommand;
  13.     IDbCommand selectIncrementalDeletesCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalDeletesCommand;
  14.  
  15.     //there are two select statements in the generated command for SelectingInserts
  16.     //the first is for @sync_initialized = 0, the other one for subsequent syncs
  17.     //so we need to insert our filter on both statements
  18.     selectIncrementalInsertsCommand.CommandText = selectIncrementalInsertsCommand.CommandText.Insert(selectIncrementalInsertsCommand.CommandText.IndexOf("WHERE") + 5, myFilter);
  19.     selectIncrementalInsertsCommand.CommandText = selectIncrementalInsertsCommand.CommandText.Insert(selectIncrementalInsertsCommand.CommandText.LastIndexOf("WHERE") + 5, myFilter);
  20.  
  21.     //let’s add the filter for the select deletes and select updates command
  22.     selectIncrementalDeletesCommand.CommandText = selectIncrementalDeletesCommand.CommandText.Insert(selectIncrementalDeletesCommand.CommandText.IndexOf("WHERE") + 5, myFilter);
  23.     selectIncrementalUpdatesCommand.CommandText = selectIncrementalUpdatesCommand.CommandText.Insert(selectIncrementalUpdatesCommand.CommandText.IndexOf("WHERE") + 5, myFilter);
  24.  
  25.     //let’s add our filter parameter
  26.     selectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.Int));
  27.     selectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.Int));
  28.     selectIncrementalUpdatesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.Int));
  29.  
  30.     //now, let’s replace the designer generated command with our new command with filter
  31.     remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand = selectIncrementalInsertsCommand;
  32.     remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalDeletesCommand = selectIncrementalDeletesCommand;
  33.     remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalUpdatesCommand = selectIncrementalUpdatesCommand;
  34. }

Once we have access to the provider, we can then retrieve the Command objects for each of the Adapters, insert our filter clause and add the corresponding parameter.

Then we pass the filter parameter value prior to calling the sync.

You can find the full code here: SyncFilterDemo.zip

Hope this helps.

Advertisements

31 comments

  1. Johnny · · Reply

    After myFilter is added to the command objects, runtime error occurs:

    Microsoft.Synchronization.Data.SyncException: Unable to enumerate changes at the DbServerSyncProvider for table ‘Customer’ in synchronization group ‘CustomerSyncTableSyncGroup’. —> System.Data.SqlClient.SqlException: The multi-part identifier “CT.CustomerId” could not be bound.

    The CHANGETABLE does not contain the ‘CustomerId’ column, do you know how to solve it? Thanks.

    1. can you confirm that your table indeed has a CustomerId column? check your sync adapters as well if you have CustomerId

      1. I run the following statement in MSSQL2008:

        SELECT * FROM CHANGETABLE (CHANGES Products,0) as CT

        CustomerId column not exists in CT, there are 7 columns in CT:
        SYS_CHANGE_VERSION
        SYS_CHANGE_CREATION_VERSION
        SYS_CHANGE_OPERATION
        SYS_CHANGE_COLUMNS
        SYS_CHANGE_CONTEXT
        ID

      2. do you have a CustomerId in your Products table?

      3. Yes. Products table contains the CustomerId column, but it does not appear in CT table.

      4. is Customer Id part of the PK? if not, CHANGETABLE CHANGES will not return it and you will have to join the results to the original table.

        see http://msdn.microsoft.com/en-us/library/bb934145.aspx

  2. Thanks. If CustomerId is part of PK, CHANGETABLE CHANGES will return it.

    But, if I want to add filter to Local Database Cache generated sync on the column (e.g. ProductName) other than PK column, is it possible to do that?

    1. yes, you will have to join the CHANGETABLE CHANGES to the base table and do the filter on the base table columns

      CORRECTION: just reference the base table directly instead of using CT as the alias. if your tablename is CustomerTable, just reference the column directly as CustomerTable.MyColumn

  3. Will this code for windows mobile devices? Because I see that Microsoft Sync Services for devices is available only in version 1 and It doesnt include Microsoft.Synchronization.Data.Server.dll. Without this dll we can’t create ServerSyncProvider using DBServerSyncProvider as described above. Please help me with this .

    1. nope. what you have on the device is just a proxy to the server provider. you will have to change the code to make it work for devices.

  4. >yes, you will have to join the CHANGETABLE CHANGES to the base table and do the filter on the base table columns

    Could you provide an example of this process. I used to wizard for adding local database cache and now I want to filter sync table according to UserID which is not part of the primary key. I am using MSSQL Server 2008 with Change Tracking.
    A code snippet would help me a lot. Thanks for your article 😉

    1. please disregard that statement. i just havent edited it yet.

      the sync adapters generated by the wizard already does the join with the CHANGETABLE.

      to add your filter just reference the column from your base table instead of using CT as the alias. so if your table name is CustomerTable, just reference the column as CustomerTable.MyColumn in the filter.

      1. unfortunately that does not work 😦 DataSyncException is thrown with this text: Unable to enumerate changes at the DbServerSyncProvider for table ….

      2. run sql profiler and see what sql is being sent if its sending any at all. or locate one of the incremental commands in the wizard generated code and post it here.

      3. OK, here is the the command:

        this.SelectIncrementalInsertsCommand = new System.Data.SqlClient.SqlCommand();
        this.SelectIncrementalInsertsCommand.CommandText = “IF @sync_initialized = 0 SELECT dbo.tpr_denik_2011.[id], [datum], dbo.tpr_denik_2” +
        “011.[sportovec], [trener], [aktualizace], [aktualizace_p], [shoda], [dopoledne],” +
        ” [dopoledne_p], [odpoledne], [odpoledne_p], [poznamky], [poznamky_p], [IO], [JZ]” +
        “, [JZ_p], [KS], [KS_p], [BS], [BS_p], [HZ], [HZ_p], [REG], [REG_p], [BEH], [BEH_” +
        “p], [KOLO], [KOLO_p], [KOLYZE], [KOLYZE_p], [LYZE], [LYZE_p], [JINE], [JINE_p], ” +
        “[IMV], [IMV_p], [SSI], [SSI_p], [VN], [VN_p], [KXT], [KXT_p], [OSI], [OSI_p], [S” +
        “S], [SS_p], [NT], [NT_p] FROM dbo.tpr_denik_2011 LEFT OUTER JOIN CHANGETABLE(CHA” +
        “NGES dbo.tpr_denik_2011, @sync_last_received_anchor) CT ON CT.[id] = dbo.tpr_den” +
        “ik_2011.[id] AND CT.[sportovec] = dbo.tpr_denik_2011.[sportovec] WHERE (CT.SYS_C” +
        “HANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT @sync_client_id_binary) ELSE ” +
        “BEGIN SELECT dbo.tpr_denik_2011.[id], [datum], dbo.tpr_denik_2011.[sportovec], [” +
        “trener], [aktualizace], [aktualizace_p], [shoda], [dopoledne], [dopoledne_p], [o” +
        “dpoledne], [odpoledne_p], [poznamky], [poznamky_p], [IO], [JZ], [JZ_p], [KS], [K” +
        “S_p], [BS], [BS_p], [HZ], [HZ_p], [REG], [REG_p], [BEH], [BEH_p], [KOLO], [KOLO_” +
        “p], [KOLYZE], [KOLYZE_p], [LYZE], [LYZE_p], [JINE], [JINE_p], [IMV], [IMV_p], [S” +
        “SI], [SSI_p], [VN], [VN_p], [KXT], [KXT_p], [OSI], [OSI_p], [SS], [SS_p], [NT], ” +
        “[NT_p] FROM dbo.tpr_denik_2011 JOIN CHANGETABLE(CHANGES dbo.tpr_denik_2011, @syn” +
        “c_last_received_anchor) CT ON CT.[id] = dbo.tpr_denik_2011.[id] AND CT.[sportove” +
        “c] = dbo.tpr_denik_2011.[sportovec] WHERE (CT.SYS_CHANGE_OPERATION = \’I\’ AND CT.” +
        “SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CON" +
        "TEXT IS NULL OR CT.SYS_CHANGE_CONTEXT @sync_client_id_binary)); IF CHANGE_TRA” +
        “CKING_MIN_VALID_VERSION(object_id(N\’dbo.tpr_denik_2011\’)) > @sync_last_received_” +
        “anchor RAISERROR (N\’SQL Server Change Tracking has cleaned up tracking informati” +
        “on for table \’\’%s\’\’. To recover from this error, the client must reinitialize it” +
        “s local database and try again\’,16,3,N\’dbo.tpr_denik_2011\’) END “;
        this.SelectIncrementalInsertsCommand.CommandType = System.Data.CommandType.Text;
        this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(“@sync_initialized”, System.Data.SqlDbType.Bit));
        this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(“@sync_last_received_anchor”, System.Data.SqlDbType.BigInt));
        this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(“@sync_client_id_binary”, System.Data.SqlDbType.VarBinary));
        this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(“@sync_new_received_anchor”, System.Data.SqlDbType.BigInt));

      4. and what is the filter clause you’re adding? can you debug the part after you added your filter clause and see how the entire command text looks like?

  5. here is what I am adding:

    string myFilter = ” (tpr_denik_2011.sportovec=@UserID) AND “;

    I will try to debug it and see the command, just a sec..

  6. here it is:

    dl.dropbox.com/u/13503816/CommandTextWithFilterText.PNG

    just add http : // at the beginning

  7. IF @sync_initialized = 0 SELECT dbo.tpr_denik_2011.[id], [datum], dbo.tpr_denik_2011.[sportovec], [trener], [aktualizace], [aktualizace_p], [shoda], [dopoledne], [dopoledne_p], [odpoledne], [odpoledne_p], [poznamky], [poznamky_p], [IO], [JZ], [JZ_p], [KS], [KS_p], [BS], [BS_p], [HZ], [HZ_p], [REG], [REG_p], [BEH], [BEH_p], [KOLO], [KOLO_p], [KOLYZE], [KOLYZE_p], [LYZE], [LYZE_p], [JINE], [JINE_p], [IMV], [IMV_p], [SSI], [SSI_p], [VN], [VN_p], [KXT], [KXT_p], [OSI], [OSI_p], [SS], [SS_p], [NT], [NT_p] FROM dbo.tpr_denik_2011 LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.tpr_denik_2011, @sync_last_received_anchor) CT ON CT.[id] = dbo.tpr_denik_2011.[id] AND CT.[sportovec] = dbo.tpr_denik_2011.[sportovec] WHERE (tpr_denik_2011.sportovec=@UserID) AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT @sync_client_id_binary) ELSE BEGIN SELECT dbo.tpr_denik_2011.[id], [datum], dbo.tpr_denik_2011.[sportovec], [trener], [aktualizace], [aktualizace_p], [shoda], [dopoledne], [dopoledne_p], [odpoledne], [odpoledne_p], [poznamky], [poznamky_p], [IO], [JZ], [JZ_p], [KS], [KS_p], [BS], [BS_p], [HZ], [HZ_p], [REG], [REG_p], [BEH], [BEH_p], [KOLO], [KOLO_p], [KOLYZE], [KOLYZE_p], [LYZE], [LYZE_p], [JINE], [JINE_p], [IMV], [IMV_p], [SSI], [SSI_p], [VN], [VN_p], [KXT], [KXT_p], [OSI], [OSI_p], [SS], [SS_p], [NT], [NT_p] FROM dbo.tpr_denik_2011 JOIN CHANGETABLE(CHANGES dbo.tpr_denik_2011, @sync_last_received_anchor) CT ON CT.[id] = dbo.tpr_denik_2011.[id] AND CT.[sportovec] = dbo.tpr_denik_2011.[sportovec] WHERE (tpr_denik_2011.sportovec=@UserID) AND (CT.SYS_CHANGE_OPERATION = ‘I’ AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N’dbo.tpr_denik_2011′)) > @sync_last_received_anchor RAISERROR (N’SQL Server Change Tracking has cleaned up tracking information for table ”%s”. To recover from this error, the client must reinitialize its local database and try again’,16,3,N’dbo.tpr_denik_2011′) END

    1. statement looks valid to me. and looking at the query, it seems that your filter column is in the Change Tracking afterall (CT.[sportovec]).

      you can simply set your filter to :” CT.[sportovec] = @UserID ) AND “

      i suggest you run SQL Profiler to catch the exact SQL statement being sent to SQL Server, copy and paste them in Query Analyzer and execute them yourself to see if it returns a value or if it generates an error.

      you might want to check as well your Change Tracking retention period just in case it might have been cleaned up already.

      1. Ok, thanks

  8. One more question. I want to synchronize records for logged user (that’s why I use the filter). The question is, how should I empty the local database when new user logs in so it can by filled with their data? This also means I want to deploy the app with empty local database. how to do that?

    1. i suggest you just have one SDF per user. if you’re sharing the SDF, you have to worry about cleaning it up and filtering in your app to make sure a user doesnt see the other user’s data. you can deploy and empty SDF and the initial sync should create the tables on first sync.

      1. yes, I would like to do that, but how do you ensure, that only one user can use it if there is a log on window? how do you restrict logging as another user after the first log in? Also, how do you deploy an empty local database? The database is synced once added to the project. thanks for your answers..I really appreciate your willingness

  9. This worked great when I only filtered 1 table. If I have multiple tables that have a customerID in them, I tried setting the filters on each table’s sync adapter. I duplicated the AddFilters function for each of the tables using the table’s correct sync adapter. It locks up when I try to run the SyncAgent.Synchronize() method. Any ideas?

    1. try SQL Profiler to see what exactly its doing.

  10. Each of my tables has a CustomerID, so I ran this on each of the tables:

    serverTemplate.Tables(“Class”).AddFilterColumn(“CustomerID”)
    serverTemplate.Tables(“Class”).FilterClause = “[side].[SecurityUserID] = @CustomerID”
    Dim param2 As New SqlParameter(“@CustomerID”, SqlDbType.UniqueIdentifier)
    serverTemplate.Tables(“Class”).FilterParameters.Add(param2)

    – This successfully created the tracking table and it successfully includes the CustomerID column.

    When I look at SQL Profiler and copy the SQL that it is trying to run, then run it in a new Query window, it is telling me that CustomerID is not found. I see the column in the real table and in the tracking table… so it looks like the column is there, but SQL Server query is telling me Invalid Column Name ‘CustomerID’

    CustomerID is not the Primary key of this table… does this matter? I need to be able to filter on a column that is not the primary key.

    Any ideas?

    1. which provider are you using? this post is for the offline providers while the code you posted seems to be for the newer collaboration/peer-to-peer providers…

      also, you added CustomerId as a filter column but you’re filtering on [side].[SecurityUserId]

      1. Sorry for the confusion. I was using CustomerID to be consistent with your sample. My actual column that I am working with is SecurityUserID ….I have a table that has tests in it. There is a SecurityUserID that ties all of the tests back to the user. So I want the user to see only their tests. SecurityUserID is not the Primary key of this table however I do have this column indexed..

        I am using VB.Net in Visual Studio 2010. I added a LocalDataCache to my project and I am simply running the standard :
        Private WithEvents SyncAgent As LocalDataCache1SyncAgent = New LocalDataCache1SyncAgent()
        Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = SyncAgent.Synchronize()

        So when you ask what provider am I using, I am working with Compact SQL 3.5 and using Sync 2.1 Does this answer your question? I am sorry, I am really new to this sync technology.

        Thank you,
        Jeff

      2. if its from the Local Database Cache Wizard, thats the older offline provider. you can’t use the [side] alias because i dont think it exists in the queries generated by the wizard. if SecurityUserId is not a PK, you can’t access it via the Change Tracking table alias CT as well.

        try not setting a filter on the SelectIncrementalDeletes command.

  11. Thanks JuneT! The only problem I was having was the column I was trying to filter by was not in the PK for the tables. As soon as I did this, filtering worked great on all my tables!!

    Thank you for all your help!!

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: