locked
sync tables with custom queries RRS feed

  • Question

  • I've manage to sync 50 tables using this technology.Is there a way to send custom SELECT queries to specify subsets of data to be synced.
    sorry for my english.
    Any guidness will be helpfull.
    Thanks in advance.

    • Moved by Hengzhe Li Friday, April 22, 2011 2:37 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, July 17, 2009 8:59 AM

Answers

  • On Server-Side add a SyncParameter.

    E.g.:

    you add a WHERE-Clause to your this.Update.CommandText "... WHERE employee = @employee"

    or something like that, and a new sync-parameter employee

    this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMPLOYEE", System.Data.SqlDbType.NVarChar));
    
    On client side you have to add this SyncParameter in the Configuration object of your ClientSyncAgent.

    e.g.:

    this.Configuration.SyncParameters.Add(new SyncParameter("employee", "makis_g3"));


    HTH,

    Martin
    • Proposed as answer by macap Monday, July 20, 2009 2:11 PM
    • Marked as answer by makis_g3 Tuesday, July 21, 2009 2:07 PM
    Monday, July 20, 2009 2:10 PM

All replies

  • I think this is what you are searching for: How to: Filter Rows and Columns
    • Proposed as answer by macap Monday, July 20, 2009 12:43 PM
    • Marked as answer by makis_g3 Monday, July 20, 2009 1:01 PM
    • Unmarked as answer by makis_g3 Monday, July 20, 2009 1:06 PM
    Monday, July 20, 2009 12:43 PM
  • Thanks for the response,i have read the article.
    In my case :
    I have a table name EmployeeCustomers,and i want to pass  parameter for the employee.
    How can i do that?
    This is the code that Local Database cache have generated.

        public partial class EMPLOYEECUSTOMERSSyncAdapter : Microsoft.Synchronization.Data.Server.SyncAdapter {
            
            partial void OnInitialized();
            
            public EMPLOYEECUSTOMERSSyncAdapter() {
                this.InitializeCommands();
                this.InitializeAdapterProperties();
                this.OnInitialized();
            }
            
            [System.Diagnostics.DebuggerNonUserCodeAttribute()]
            private void InitializeCommands() {
                this.InsertCommand = new System.Data.SqlClient.SqlCommand();
                this.InsertCommand.CommandText = @";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) INSERT INTO dbo.EMPLOYEECUSTOMERS ([ID], [EMPLOYEEID], [CUSTOMERSID], [UPDEMPLOYEE], [CUSTOMERCENTERID]) VALUES (@ID, @EMPLOYEEID, @CUSTOMERSID, @UPDEMPLOYEE, @CUSTOMERCENTERID) SET @sync_row_count = @@rowcount; IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.EMPLOYEECUSTOMERS')) > @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.EMPLOYEECUSTOMERS') ";
                this.InsertCommand.CommandType = System.Data.CommandType.Text;
                this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_client_id_binary", System.Data.SqlDbType.VarBinary));
                this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.NVarChar));
                this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMPLOYEEID", System.Data.SqlDbType.NVarChar));
                this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CUSTOMERSID", System.Data.SqlDbType.NVarChar));
                this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UPDEMPLOYEE", System.Data.SqlDbType.NVarChar));
                this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CUSTOMERCENTERID", System.Data.SqlDbType.NVarChar));
                System.Data.SqlClient.SqlParameter insertcommand_sync_row_countParameter = new System.Data.SqlClient.SqlParameter("@sync_row_count", System.Data.SqlDbType.Int);
                insertcommand_sync_row_countParameter.Direction = System.Data.ParameterDirection.Output;
                this.InsertCommand.Parameters.Add(insertcommand_sync_row_countParameter);
                this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.BigInt));
                // EMPLOYEECUSTOMERSSyncTableDeleteCommand command.
                this.DeleteCommand = new System.Data.SqlClient.SqlCommand();
                this.DeleteCommand.CommandText = @";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) DELETE dbo.EMPLOYEECUSTOMERS FROM dbo.EMPLOYEECUSTOMERS JOIN CHANGETABLE(VERSION dbo.EMPLOYEECUSTOMERS, ([ID]), (@ID)) CT  ON CT.[ID] = dbo.EMPLOYEECUSTOMERS.[ID] WHERE ( @sync_force_write = 1 OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) SET @sync_row_count = @@rowcount; IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.EMPLOYEECUSTOMERS')) > @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.EMPLOYEECUSTOMERS') ";
                this.DeleteCommand.CommandType = System.Data.CommandType.Text;
                this.DeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_client_id_binary", System.Data.SqlDbType.VarBinary));
                this.DeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.NVarChar));
                this.DeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_force_write", System.Data.SqlDbType.Bit));
                this.DeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.BigInt));
                System.Data.SqlClient.SqlParameter deletecommand_sync_row_countParameter = new System.Data.SqlClient.SqlParameter("@sync_row_count", System.Data.SqlDbType.Int);
                deletecommand_sync_row_countParameter.Direction = System.Data.ParameterDirection.Output;
                this.DeleteCommand.Parameters.Add(deletecommand_sync_row_countParameter);
                // EMPLOYEECUSTOMERSSyncTableUpdateCommand command.
                this.UpdateCommand = new System.Data.SqlClient.SqlCommand();
                this.UpdateCommand.CommandText = @";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) UPDATE dbo.EMPLOYEECUSTOMERS SET [EMPLOYEEID] = @EMPLOYEEID, [CUSTOMERSID] = @CUSTOMERSID, [UPDEMPLOYEE] = @UPDEMPLOYEE, [CUSTOMERCENTERID] = @CUSTOMERCENTERID FROM dbo.EMPLOYEECUSTOMERS  JOIN CHANGETABLE(VERSION dbo.EMPLOYEECUSTOMERS, ([ID]), (@ID)) CT  ON CT.[ID] = dbo.EMPLOYEECUSTOMERS.[ID] WHERE ( @sync_force_write = 1 OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) SET @sync_row_count = @@rowcount; IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.EMPLOYEECUSTOMERS')) > @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.EMPLOYEECUSTOMERS') ";
                this.UpdateCommand.CommandType = System.Data.CommandType.Text;
                this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMPLOYEEID", System.Data.SqlDbType.NVarChar));
                this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CUSTOMERSID", System.Data.SqlDbType.NVarChar));
                this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UPDEMPLOYEE", System.Data.SqlDbType.NVarChar));
                this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CUSTOMERCENTERID", System.Data.SqlDbType.NVarChar));
                this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.NVarChar));
                this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_force_write", System.Data.SqlDbType.Bit));
                this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.BigInt));
                this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_client_id_binary", System.Data.SqlDbType.VarBinary));
                System.Data.SqlClient.SqlParameter updatecommand_sync_row_countParameter = new System.Data.SqlClient.SqlParameter("@sync_row_count", System.Data.SqlDbType.Int);
                updatecommand_sync_row_countParameter.Direction = System.Data.ParameterDirection.Output;
                this.UpdateCommand.Parameters.Add(updatecommand_sync_row_countParameter);
                // EMPLOYEECUSTOMERSSyncTableSelectConflictDeletedRowsCommand command.
                this.SelectConflictDeletedRowsCommand = new System.Data.SqlClient.SqlCommand();
                this.SelectConflictDeletedRowsCommand.CommandText = "SELECT CT.[ID], CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION FROM CHANGETABLE(CHA" +
                    "NGES dbo.EMPLOYEECUSTOMERS, @sync_last_received_anchor) CT WHERE (CT.[ID] = @ID " +
                    "AND CT.SYS_CHANGE_OPERATION = \'D\')";
                this.SelectConflictDeletedRowsCommand.CommandType = System.Data.CommandType.Text;
                this.SelectConflictDeletedRowsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.BigInt));
                this.SelectConflictDeletedRowsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.NVarChar));
                // EMPLOYEECUSTOMERSSyncTableSelectConflictUpdatedRowsCommand command.
                this.SelectConflictUpdatedRowsCommand = new System.Data.SqlClient.SqlCommand();
                this.SelectConflictUpdatedRowsCommand.CommandText = @"SELECT dbo.EMPLOYEECUSTOMERS.[ID], [EMPLOYEEID], [CUSTOMERSID], [UPDEMPLOYEE], [CUSTOMERCENTERID], CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION FROM dbo.EMPLOYEECUSTOMERS JOIN CHANGETABLE(VERSION dbo.EMPLOYEECUSTOMERS, ([ID]), (@ID)) CT  ON CT.[ID] = dbo.EMPLOYEECUSTOMERS.[ID]";
                this.SelectConflictUpdatedRowsCommand.CommandType = System.Data.CommandType.Text;
                this.SelectConflictUpdatedRowsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.NVarChar));
                // EMPLOYEECUSTOMERSSyncTableSelectIncrementalInsertsCommand command.
                this.SelectIncrementalInsertsCommand = new System.Data.SqlClient.SqlCommand();
                this.SelectIncrementalInsertsCommand.CommandText = @"IF @sync_initialized = 0 SELECT dbo.EMPLOYEECUSTOMERS.[ID], [EMPLOYEEID], [CUSTOMERSID], [UPDEMPLOYEE], [CUSTOMERCENTERID] FROM dbo.EMPLOYEECUSTOMERS LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.EMPLOYEECUSTOMERS, @sync_last_received_anchor) CT ON CT.[ID] = dbo.EMPLOYEECUSTOMERS.[ID] WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) ELSE  BEGIN SELECT dbo.EMPLOYEECUSTOMERS.[ID], [EMPLOYEEID], [CUSTOMERSID], [UPDEMPLOYEE], [CUSTOMERCENTERID] FROM dbo.EMPLOYEECUSTOMERS JOIN CHANGETABLE(CHANGES dbo.EMPLOYEECUSTOMERS, @sync_last_received_anchor) CT ON CT.[ID] = dbo.EMPLOYEECUSTOMERS.[ID] 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)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.EMPLOYEECUSTOMERS')) > @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.EMPLOYEECUSTOMERS')  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));
                // EMPLOYEECUSTOMERSSyncTableSelectIncrementalDeletesCommand command.
                this.SelectIncrementalDeletesCommand = new System.Data.SqlClient.SqlCommand();
                this.SelectIncrementalDeletesCommand.CommandText = @"IF @sync_initialized > 0  BEGIN SELECT CT.[ID] FROM CHANGETABLE(CHANGES dbo.EMPLOYEECUSTOMERS, @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)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.EMPLOYEECUSTOMERS')) > @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.EMPLOYEECUSTOMERS')  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));
                // EMPLOYEECUSTOMERSSyncTableSelectIncrementalUpdatesCommand command.
                this.SelectIncrementalUpdatesCommand = new System.Data.SqlClient.SqlCommand();
                this.SelectIncrementalUpdatesCommand.CommandText = @"IF @sync_initialized > 0  BEGIN SELECT dbo.EMPLOYEECUSTOMERS.[ID], [EMPLOYEEID], [CUSTOMERSID], [UPDEMPLOYEE], [CUSTOMERCENTERID] FROM dbo.EMPLOYEECUSTOMERS JOIN CHANGETABLE(CHANGES dbo.EMPLOYEECUSTOMERS, @sync_last_received_anchor) CT ON CT.[ID] = dbo.EMPLOYEECUSTOMERS.[ID] 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)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.EMPLOYEECUSTOMERS')) > @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.EMPLOYEECUSTOMERS')  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));
            }
            
            [System.Diagnostics.DebuggerNonUserCodeAttribute()]
            private void InitializeAdapterProperties() {
                this.TableName = "EMPLOYEECUSTOMERS";
            }
        }
    Monday, July 20, 2009 12:55 PM
  • On Server-Side add a SyncParameter.

    E.g.:

    you add a WHERE-Clause to your this.Update.CommandText "... WHERE employee = @employee"

    or something like that, and a new sync-parameter employee

    this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMPLOYEE", System.Data.SqlDbType.NVarChar));
    
    On client side you have to add this SyncParameter in the Configuration object of your ClientSyncAgent.

    e.g.:

    this.Configuration.SyncParameters.Add(new SyncParameter("employee", "makis_g3"));


    HTH,

    Martin
    • Proposed as answer by macap Monday, July 20, 2009 2:11 PM
    • Marked as answer by makis_g3 Tuesday, July 21, 2009 2:07 PM
    Monday, July 20, 2009 2:10 PM
  • if i  add the parameter at UpdateCommand it doesn't work neither with InsertCommand.

    It works if i add it at SelectIncrementalInsertsCommand  but again not for all cases(inserted /updated/deleted rows).
    Can someone explain the difference between these commands and give me a full scenario.
    Will it work if i set as parameter value another select query?
    Thanks again.

    Tuesday, July 21, 2009 10:17 AM
  • this was only an example. You have to set it for all commands, because you set the parameter on client site in the Configuration, which is valid vor _all_ commands.
    Tuesday, July 21, 2009 1:47 PM
  • I'm confused with the above code,because of the parameters and system tables and not knowing when its command is fired,
    although i will keep trying.
    Thanks for response.
    Tuesday, July 21, 2009 2:07 PM