Answered by:
sync tables with custom queries

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,
MartinMonday, July 20, 2009 2:10 PM
All replies
-
I think this is what you are searching for: How to: Filter Rows and ColumnsMonday, 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,
MartinMonday, 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