none
Sync Framework "Filter rows " using Data Source Configuration Wizard (SyncAdapter) RRS feed

  • Question

  • Hi,

    I am developing an application which will run on Windows Ce 5.0. I used the DataSource Configuration Wizard  of (Visual Studio 2008  SP1 ) to develop an application which syncs two databases on remote computers. But the problem is  I do not want to Sync the entire database,instead i want only only select rows to be synchronized between databases. I know that SqlSyncAdapterBuilder can be used for filtering the rows, but the Designer generated code is deriving from "SyncAdapter" which doesn't have the filter property. Please tell a way by which the filtering of rows and columns can be done using the designer wizard itself. And does the upcoming visual studio 2010 support the selective sync framework code to be generated from the Wizard.
    • Edited by Joe Manjiyil Friday, January 29, 2010 10:44 AM Edited some lines
    • Moved by Bob Beauchemin Friday, January 29, 2010 3:58 PM Moved to a more appropriate group (From:.NET Framework inside SQL Server)
    Friday, January 29, 2010 10:37 AM

Answers

  • You could make your filter column as part of the composite key when using change tracking. This way that filter column is returned by change tracking and you could use it in your query to filter the rows that are interesting to you.
    This posting is provided AS IS with no warranties, and confers no rights
    Friday, February 12, 2010 5:13 AM

All replies

  • The commands in the SyncAdapter implements IDBCommand. So you can manipulate the command object, concatenate a filter clause to the command text and pass parameters. Quick and dirty way :)

    Friday, January 29, 2010 5:55 PM
    Moderator
  • hi JuneT,

    Can you explain it in elaborate manner? If possible provide a code snippet or a link to any such article.
    Monday, February 1, 2010 4:24 AM
  • It is proving unbelievably impossible to get an answer to this question ... every possible place on the web I have found where this question has been asked results in a vague answer and then no answer when a request for more detailed information is made!

    (It is almost comical ... I wonder if it is really possible to do ...)

    Like Joe, I would like to see a code snipet of how to filter rows by modifying the Designer's code (which I guess will preclude using the designer in the future).  I was not able to follow the answer above because I don't know what "IDBCommand" is.

    Thanks in advance for anyone who can answer this question!
    Wednesday, February 3, 2010 2:53 PM
  • hi JuneT,

    Can you explain it in elaborate manner? If possible provide a code snippet or a link to any such article.

    Quick and dirty way, locate the Adapter generated for your table, find InitializeCommands(), and then the Incremental Selects for Inserts/Updates/Deletes.

    public partial class TestSyncAdapter : Microsoft.Synchronization.Data.Server.SyncAdapter {
            
            partial void OnInitialized();
    
            public TestSyncAdapter() {
                this.InitializeCommands();
                this.InitializeAdapterProperties();
                this.OnInitialized();
            }
            
            [System.Diagnostics.DebuggerNonUserCodeAttribute()]
            private void InitializeCommands() {
                // TestSyncTableInsertCommand command.
                    //code removed to shorten posting
                // TestSyncTableDeleteCommand command.
                    //code removed to shorten posting
                // TestSyncTableUpdateCommand command.
                    //code removed to shorten posting
                // TestSyncTableSelectConflictDeletedRowsCommand command.
                    //code removed to shorten posting
                // TestSyncTableSelectConflictUpdatedRowsCommand command.
                    //code removed to shorten posting
    
                // TestSyncTableSelectIncrementalInsertsCommand command.
                this.SelectIncrementalInsertsCommand = new System.Data.SqlClient.SqlCommand();
                this.SelectIncrementalInsertsCommand.CommandText = "SELECT [EmpId], [Firstname], [Lastname], [LastEditDate], [CreationDate] FROM dbo." +
                    "Test WHERE ([CreationDate] > @sync_last_received_anchor AND [CreationDate] <= @s" +
                    "ync_new_received_anchor)";
                this.SelectIncrementalInsertsCommand.CommandType = System.Data.CommandType.Text;
                this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
                this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.DateTime));
                
                // TestSyncTableSelectIncrementalDeletesCommand command.
                    //code removed to shorten posting  
                
                // TestSyncTableSelectIncrementalUpdatesCommand command.
                    //code removed to shorten posting
            }
            
            [System.Diagnostics.DebuggerNonUserCodeAttribute()]
            private void InitializeAdapterProperties() {
                this.TableName = "Test";
            }
        }
    You can simply concatenate your filter clause to the CommandText property

    this.SelectIncrementalInsertsCommand.CommandText = "SELECT [EmpId], [Firstname], [Lastname], [LastEditDate], [CreationDate] FROM dbo." +
                    "Test WHERE ([CreationDate] > @sync_last_received_anchor AND [CreationDate] <= @s" +
                    "ync_new_received_anchor AND [EmpId]=@myEmpdIdFilter)";


    this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myEmpdIdFilter", System.Data.SqlDbType.Int));
    Then assign a value to your filter before you sync
    // Call SyncAgent.Synchronize() to initiate the synchronization process.
    // Synchronization only updates the local database, not your project's data source.
    LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent(); syncAgent.Configuration.SyncParameters.Add(new SyncParameter("@myEmpdIdFilter", 1)); Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
    Is this what you're looking for? This would however be overwritten when the Designer regenerates the code,
    • Proposed as answer by P H T Wednesday, February 3, 2010 9:47 PM
    Wednesday, February 3, 2010 4:27 PM
    Moderator
  • Thanks June - can't speak for Joe ... but that is exactly what I wanted to know ... so simple, but I have not been able to find it anywhere (or more likely ... I did not understand what I was reading ;)

    When the designer creates the .sdf it appends all data for each table by default - when I deploy to the emulator all the data goes to the device - I want the "startign point" on the device to be filtered data ... so it is ready for the first sync.

    Here is what I think I should do (is there a better "best practice"?):
    - Manually delete all the data from the .sdf table first to minimize deployment time.
    - Run a snapshot sync to initialize the table for first time use (with the WHERE filter that future sync's will use)
    - future sync's will be BiDirectional

    It seems that I need an initial sync routine on the device to set a starting point for the data - am I right or is there a better way?

    Thanks again for your help!
    Wednesday, February 3, 2010 9:03 PM
  • Well is only the initial sync filtered for you? If not, then the code that you use to do initial sync would still work for subsequent syncs too.
    You dont have to really use snapshot sync direction for initial sync.
    If the filter and code are same, you can use bidirectional (assuming your .sdf file is going to be freshly created) for both initial sync and subsequent sync.
    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, February 4, 2010 7:46 AM
  • Thanks JuneT. I almost started using merge replication with a filter clause and it is working fine.
    But we will be using the sync Framework also for some tables. Is it enough ,if we add the filter clause in the SelectIncrementalInsertsCommand  alone? Should we add the filter clause to SelectConflictUpdatedRowsCommand, SelectConflictDeletedRowsCommand etc. ?

    Cant we Override the code in the LocalDataCache.designer.cs in the LocalDatacache.cs. ?


    I tried what you said ,but still it is downloading everything.


    This line was changed in the designer file,

    this

     

    .SelectIncrementalInsertsCommand.CommandText =

    @"IF @sync_initialized = 0 SELECT dbo.empDetails.[empNo], [empName] FROM dbo.empDetails LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.empDetails, @sync_last_received_anchor) CT ON CT.[empNo] = dbo.empDetails.[empNo]

    WHERE dbo.empDetails.[empNo] = @myEmpdIdFilter AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)

    ELSE BEGIN SELECT dbo.empDetails.[empNo], [empName] FROM dbo.empDetails JOIN CHANGETABLE(CHANGES dbo.empDetails, @sync_last_received_anchor) CT ON CT.[empNo] = dbo.empDetails.[empNo]

    WHERE dbo.empDetails.[empNo]=@myEmpdIdFilter 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.empDetails')) > @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.empDetails') END "

     

    ;

    this

     

    .SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myEmpdIdFilter", System.Data.SqlDbType.Int));

    This line I added in my Form1.c:
    syncAgent.Configuration.SyncParameters.Add(new Microsoft.Synchronization.Data.SyncParameter("@myEmpdIdFilter", 1));

     



    Thursday, February 4, 2010 11:10 AM
  • Thanks for the reply!

    I want initial and subsequent syncs to be filtered.  My issue is that the Designer builds an .sdf in the solution and does an initial sync to that .sdf before I am permitted to define a filter - so the initial sync (done by Designer) sends all data (unfiltered) to the solution's .sdf.

    How are others addressing this?  The snapshot sync just fixes the problem by removing unwanted records from the device - ideally, I'd like to not have the unfiltered data on the device at all.

    I am thinking now I have to let the Designer do its thing, then I will delete all records from the .sdf in the solution, then add my filters, then deploy to device.

    Seems like a lot of unnecessary work (as process will have to be repeated every time I change my tables, etc.) ... am I on track or is there a better way?

    Thanks again!

    Thursday, February 4, 2010 2:38 PM
  • just truncate the contents of your SDF file and use that as an empty template DB when deploying. Or delete the SDF and in your code, check if the SDF does not exists, create the SDF and set the TableCreationOption for each table to CreateNewTableOrFail  on first sync then use UseExistingTableOrFail on subsequent syncs.


    As for the filter, you have to apply it on all the IncrementalSelect commands.

    To confirm if the filters are being applied, you can run SQL Profiler to check for the actual SQL statements being sent your SQL Server.

    • Proposed as answer by P H T Thursday, February 4, 2010 4:09 PM
    Thursday, February 4, 2010 3:12 PM
    Moderator
  • Hi

    I added the filter clause in all the Incremental select commands.But I keep getting the {"The multi-part identifier \"dbo.empDetails.empNo\" could not be bound."} error.

    Here is the code which I have changed



    this.SelectIncrementalInsertsCommand.CommandText =
    @"IF @sync_initialized = 0 SELECT dbo.empDetails.[empNo], [empName] FROM dbo.empDetails LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.empDetails, @sync_last_received_anchor) CT ON CT.[empNo] = dbo.empDetails.[empNo] 
    WHERE ((CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) AND (dbo.empDetails.[empNo] = @myEmpdIdFilter))   
    
    ELSE   BEGIN SELECT dbo.empDetails.[empNo], [empName] FROM dbo.empDetails JOIN CHANGETABLE(CHANGES dbo.empDetails, @sync_last_received_anchor) CT ON CT.[empNo] = dbo.empDetails.[empNo] 
    WHERE(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) AND (dbo.empDetails.[empNo]= @myEmpdIdFilter)); 
    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.empDetails')) > @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.empDetails')  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));
    
    
    
                this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myEmpdIdFilter", System.Data.SqlDbType.Int));
    
    
    
                // empDetailsSyncTableSelectIncrementalDeletesCommand command.
                this.SelectIncrementalDeletesCommand = new System.Data.SqlClient.SqlCommand();
                this.SelectIncrementalDeletesCommand.CommandText = @"IF @sync_initialized > 0  BEGIN 
    SELECT CT.[empNo] FROM CHANGETABLE(CHANGES dbo.empDetails, @sync_last_received_anchor) CT 
    WHERE (CT.SYS_CHANGE_OPERATION = 'D' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND(CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)AND (dbo.empDetails.[empNo] = @myEmpdIdFilter)); 
    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.empDetails')) > @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.empDetails')  END ";
                this.SelectIncrementalDeletesCommand.CommandType = System.Data.CommandType.Text;
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_initialized", System.Data.SqlDbType.Bit));
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.BigInt));
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.BigInt));
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_client_id_binary", System.Data.SqlDbType.VarBinary));
    
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myEmpdIdFilter", System.Data.SqlDbType.Int));
    
                // empDetailsSyncTableSelectIncrementalUpdatesCommand command.
                this.SelectIncrementalUpdatesCommand = new System.Data.SqlClient.SqlCommand();
                this.SelectIncrementalUpdatesCommand.CommandText = @"IF @sync_initialized > 0  
    BEGIN SELECT dbo.empDetails.[empNo], [empName] FROM dbo.empDetails JOIN CHANGETABLE(CHANGES dbo.empDetails, @sync_last_received_anchor) CT ON CT.[empNo] = dbo.empDetails.[empNo] 
    WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)AND (dbo.empDetails.[empNo] = @myEmpdIdFilter)); 
    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.empDetails')) > @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.empDetails')  END ";
                this.SelectIncrementalUpdatesCommand.CommandType = System.Data.CommandType.Text;
                this.SelectIncrementalUpdatesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_initialized", System.Data.SqlDbType.Bit));
                this.SelectIncrementalUpdatesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.BigInt));
                this.SelectIncrementalUpdatesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.BigInt));
                this.SelectIncrementalUpdatesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_client_id_binary", System.Data.SqlDbType.VarBinary));
                
                this.SelectIncrementalUpdatesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@myEmpdIdFilter", System.Data.SqlDbType.Int));





    Please tell what is the problem in the code.

    Friday, February 5, 2010 6:16 AM
  • Did you try running some of your code snippets in management studio to see which part is erroring out?
    This posting is provided AS IS with no warranties, and confers no rights
    Friday, February 5, 2010 7:04 AM
  • hi Mahesh,

    But we cannot run these snippets in Management studio right?


    @"IF @sync_initialized = 0 SELECT dbo.empDetails.[empNo], [empName] FROM dbo.empDetails LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.empDetails, @sync_last_received_anchor) CT ON CT.[empNo] = dbo.empDetails.[empNo] 
    WHERE ((CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) AND <strong>(dbo.empDetails.[empNo] = @myEmpdIdFilter)</strong>)   
    
    ELSE   BEGIN SELECT dbo.empDetails.[empNo], [empName] FROM dbo.empDetails JOIN CHANGETABLE(CHANGES dbo.empDetails, @sync_last_received_anchor) CT ON CT.[empNo] = dbo.empDetails.[empNo] 
    WHERE(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) AND <strong>(dbo.empDetails.[empNo]= @myEmpdIdFilter)</strong>); 
    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.empDetails')) > @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.empDetails')  END ";











    Like this has the variables like sync_initialised.... If we can run these please tell how to do it.
    Friday, February 5, 2010 7:10 AM
  • Remove the bits that you cannot run from the query and try to see which one it is complaining on.

    Also can you try aliasing the empDetails table and see if that helps?


    This posting is provided AS IS with no warranties, and confers no rights
    Friday, February 5, 2010 7:22 AM
  • Hi Joe,

    To be able to run the statements in SSMS (Sql Server Management Studio) you need to pass in all the parameters.

    The easiest way to get a complete statement (with all the parameters defined and values provided) is to use SQL Profiler and capture the statement.
    To use SQL Profiler, start SMSS, Tools => SQL Profiler. Connect to your database server, just load the default template and start the trace.
    Then run your application, until it fails.
    Go back to SQL Profiler, look for the correct line and you should see a complete statement that you can copy 'n paste into SMSS.
    Friday, February 5, 2010 3:11 PM
  • Hi Joe,

    Your incrementalDeletes statement has no table aliased to dbo.empDetails.  The SELECT is made against CHANGETABLE. so the prefix dbo.empDetails is invalid in your WHERE clause.
    Friday, February 5, 2010 4:46 PM
    Moderator
  • hi,

    thanks Rudi and JuneT.
    I was getting the mistake in the Incremental delete statement. Now it is clear. By the way I found that the "Local Database cache" is unavailable in the Visual Studio when I select the project type as "Smart Device"and windows CE. Does this mean that this sync framework cannot be used with the DataSource Configuration Wizard in the Smart Device Project types?

    Thanks again guys for all the help.
    Monday, February 8, 2010 4:07 AM
  • Joe, hope this walkthrough is helpful to you: <!--Content type: Devdiv1. Transform: orcas2mtps.xslt.-->

    <!---->


    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, February 9, 2010 8:57 PM
  • To sync for a filtered subset of records (i.e. one sales person - not whole table), the discussion in this forum has been saying to change all 3 "incremental" SQL statements.  Is it not only necessary to change the IncrementalUpdates and IncrementalInserts and NOT the IncrementalDeletes?

    The IncrementalDeletes SQL only references the ChangeTable ... and the ChangeTable records contain the Key field of deleted records thus making it impossible to Join in the original table (based on some other field in that table ... ie SalesPersonKey) as, by definition, records for key fields contained in the ChangeTable have been deleted in the original table.

    What will ultimately happen when the IncrementalDeletes is run is that it will pass Key fields of ALL records to be deleted to the client (could be inefficient if dealing with large sales force and a table that has many deletes) ... but the client will only delete the ones it has in common with the results of the IncrementalDeletes SQL.

    Am I understanding this correctly or what am I missing?
    Thursday, February 11, 2010 5:14 PM
  • you're right. if you can't filter the deletes on the server side, you will be downloading deletes to the client which  won't have counterpart rows in the client side.
    • Proposed as answer by P H T Thursday, February 11, 2010 9:14 PM
    Thursday, February 11, 2010 9:13 PM
    Moderator
  • You could make your filter column as part of the composite key when using change tracking. This way that filter column is returned by change tracking and you could use it in your query to filter the rows that are interesting to you.
    This posting is provided AS IS with no warranties, and confers no rights
    Friday, February 12, 2010 5:13 AM
  • June,

    This is great information, thank you for posting. Two follow-up questions:

    1. Is the filter clause also appended to the CommandText property for the SelectIncrementalDeletesCommand and the SelectIncrementalUpdatesCommand?

    2. Is it possible to suppress code regeneration by the designer to avoid inadvertent overwrites?

    Thanks again for your post.

    Thursday, July 15, 2010 12:28 PM
  • 1. Yes, you have to include the filter clause separately on each select incremental command

    2. No. However, there are ways to implement your changes outside the generated code. check out a sample here: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1203.entry

     

    • Proposed as answer by P H T Thursday, August 12, 2010 2:17 PM
    Sunday, July 18, 2010 2:46 PM
    Moderator