Deleting rows after sync, but not passing those deletes to the server RRS feed

  • Question

  • I have a situation whereby I am collecting information on a mobile device (SQLCE 3.5) and then uploading that data to an SQL2005 server via synchronisation services.

    I want to be able to delete some of the data captured on the device once it has been uploaded, but after this I do not want the deletion to be sent to the server on the next synchronisation.

    I have attempted to acheive this by overriding the SetTableSentAnchor method of the SqlCeClientSyncAdapter and then running a delete on the table before the accept changes is called as follows...

    public override void SetTableSentAnchor(string tableName, SyncAnchor anchor) {
    	if (!this.preventRecursion) {
    		try {
    			// The call below to AcceptChanges will result in a recursive
    			// re-entry to this method. This variable is used to track that
    			// re-entry and fallback to base functionality when required.
    			this.preventRecursion = true;
    			// Do we need to do anything special with the selected table?
    			string[] transientTableNames = null;
    			switch (tableName) {
    				case "tablename1":
    				case "tablename2":
    					transientTableNames = new string[] { tableName };
    			if (transientTableNames == null) {
    				// The table that was sent does not need to be cleared.
    				base.SetTableSentAnchor(tableName, anchor);
    			else {
    				// The table that has been sent should now be cleared,
    				// possibly along with other retlated tables...
    				using (SqlCeCommand command = new SqlCeCommand()) {
    					command.CommandType = CommandType.Text;
    					command.Connection = this.Connection as SqlCeConnection;
    					foreach (string transientTableName in transientTableNames) {
    						command.CommandText = string.Format(@"delete from [{0}]", transientTableName);
    		catch {
    		finally {
    			// We have finished, any further call will not be caused
    			// by recursive re-entry...
    			this.preventRecursion = false;
    	else {
    		// This is a re-entry caused by the call to AcceptChanges above,
    		// so simply pass it back to the base functionality...
    		base.SetTableSentAnchor(tableName, anchor);


    this however does not always seem to work.

    Can anybody recommend another way to acheive this? I need to delete the data as otherwise it will simply continue to grow on the device.


    Wednesday, April 14, 2010 11:57 AM

All replies

  • have you tried intercepting the change dataset in GetChanges(), then loop thru the dataset and remove the deleted rows so they dont get uploaded?
    Wednesday, April 14, 2010 12:53 PM
  • JuneT,

    I am actually looking at this at the moment. I am subscribing to the SqlCeClientSyncProvider.ChangesSelected event and removing rows from all e.Context.DataSet.Tables where those rows have row.RowState == DataRowState.Deleted - this seems to be working well now.

    I am performing the deletes within the SyncAgent.StateChanged event when e.SessionState == SyncSessionState.Ready (which seems to be only once the sync is completed). I also only delete when SyncStatistics.UploadChangesFailed == 0.

    Would you say these are the best times to perform the operations or do you have any alternative suggestions?



    Wednesday, April 14, 2010 3:53 PM
  • SqlCeClientSyncProvider.ChangesSelected is the best place to do this work.
    Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar
    • Proposed as answer by Jandeep Thursday, April 29, 2010 11:49 PM
    Wednesday, April 14, 2010 5:30 PM