locked
Please help me? How to prohibit delete of rows? RRS feed

  • Question

  • If

    it's possible, how to prohibit row deletion from target database during synchronization?
    In the scenario when remote user deleted rows in his local database, but do not have permission to delete rows in target (central) database.

    We did it inside SQL server as a permission for a user role, but this leads to synchronization failures which never resolve

    (always occurs event ApplyChangesFailed).

    Thursday, February 17, 2011 3:06 PM

Answers

  • There are a couple of ways I can think of. One is to register to the Applyingchanges event and modify what you want to apply and not apply.

    Another way is to write your own provider inheriting from DBSyncProvider and selectively enumerate only those changes that you want to send to the target database.

     

    Note that with either option, you will have non-convergence of data ont the end points, but I think you know that and do not expect the 2 end points to be completely in sync.


    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, February 17, 2011 8:04 PM
  • check out the approach outlined here: http://social.microsoft.com/Forums/en-ZA/syncdevdiscussions/thread/fbc354b7-6b5e-43f7-9560-e1d86477b161

    basically, what you need to do is intercept the changes and remove the rows you dont want to sync.

    Monday, February 21, 2011 2:01 AM
  • check out the approach outlined here: http://social.microsoft.com/Forums/en-ZA/syncdevdiscussions/thread/fbc354b7-6b5e-43f7-9560-e1d86477b161

    basically, what you need to do is intercept the changes and remove the rows you dont want to sync.

    It's not work in our case. Count of rows marked for deleted  in DataSet not equals 0 only if we has some collision (if row was changed in central database after last sync). Otherwise rows count always equals 0 . How to get rows which will be deleted in this case?


    Thanks every one, i resolved these problems. May be it's not the best way but it worked:

    void dbProvider_ApplyingChanges(object sender, DbApplyingChangesEventArgs e)
        {
          if (e.Context.DataSet.Tables.Contains("Contacts"))
          {
            DataView contacts = new DataView(e.Context.DataSet.Tables["Contacts"], string.Empty, string.Empty, DataViewRowState.Deleted);
            
            DataView edges=null;
            if (e.Context.DataSet.Tables.Contains("Edges"))
              edges = new DataView(e.Context.DataSet.Tables["Edges"], string.Empty, string.Empty, DataViewRowState.Deleted);
    
            if (contacts.Count > 0)
            {
              foreach (DataRowView c in contacts)
              {
                c.Row.RejectChanges();
                Guid id =(Guid)c.Row["ContactId"];
                if (edges != null && !id.Equals(Guid.Empty))
                {
                  foreach (DataRowView ed in edges)
                  {
                    ed.Row.RejectChanges();
                    Guid from_contact=(Guid)ed.Row["NodeFrom"];
                    Guid to_contact=(Guid)ed.Row["NodeTo"];
                    if (!from_contact.Equals(id) && !to_contact.Equals(id)) ed.Row.Delete();
                    else
                    {
                      e.Context.DataSet.Tables["Edges"].Rows.Remove(ed.Row);
                    }
                  }
                }
                e.Context.DataSet.Tables["Contacts"].Rows.Remove(c.Row);
              }
            }
          }
    
        }
    

    • Marked as answer by l.d.v. _ Thursday, February 24, 2011 7:02 AM
    Tuesday, February 22, 2011 1:50 PM

All replies

  • There are a couple of ways I can think of. One is to register to the Applyingchanges event and modify what you want to apply and not apply.

    Another way is to write your own provider inheriting from DBSyncProvider and selectively enumerate only those changes that you want to send to the target database.

     

    Note that with either option, you will have non-convergence of data ont the end points, but I think you know that and do not expect the 2 end points to be completely in sync.


    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, February 17, 2011 8:04 PM
  • There are a couple of ways I can think of. One is to register to the Applyingchanges event and modify what you want to apply and not apply.

    Another way is to write your own provider inheriting from DBSyncProvider and selectively enumerate only those changes that you want to send to the target database.

     

    Note that with either option, you will have non-convergence of data ont the end points, but I think you know that and do not expect the 2 end points to be completely in sync.


    This posting is provided AS IS with no warranties, and confers no rights
    ----------------------------------------------------------------------------------------------------- Thanks you for fast answer, if it is not difficult, please show me example how to resolve this problem inside ApplyingChanges event. In our case to get end points back in sync it would be perfect to restore deleted data in the local database. Note: This needed to protect data in the central base in case user removed data from the local base by means of Microsoft Managment Studio or another SQL client and start sync process. In that case I can not see other way.
    Friday, February 18, 2011 8:07 AM
  • check out the approach outlined here: http://social.microsoft.com/Forums/en-ZA/syncdevdiscussions/thread/fbc354b7-6b5e-43f7-9560-e1d86477b161

    basically, what you need to do is intercept the changes and remove the rows you dont want to sync.

    Monday, February 21, 2011 2:01 AM
  • check out the approach outlined here: http://social.microsoft.com/Forums/en-ZA/syncdevdiscussions/thread/fbc354b7-6b5e-43f7-9560-e1d86477b161

    basically, what you need to do is intercept the changes and remove the rows you dont want to sync.

    It's not work in our case. Count of rows marked for deleted  in DataSet not equals 0 only if we has some collision (if row was changed in central database after last sync). Otherwise rows count always equals 0 . How to get rows which will be deleted in this case?

    Tuesday, February 22, 2011 12:56 PM
  • check out the approach outlined here: http://social.microsoft.com/Forums/en-ZA/syncdevdiscussions/thread/fbc354b7-6b5e-43f7-9560-e1d86477b161

    basically, what you need to do is intercept the changes and remove the rows you dont want to sync.

    It's not work in our case. Count of rows marked for deleted  in DataSet not equals 0 only if we has some collision (if row was changed in central database after last sync). Otherwise rows count always equals 0 . How to get rows which will be deleted in this case?


    Thanks every one, i resolved these problems. May be it's not the best way but it worked:

    void dbProvider_ApplyingChanges(object sender, DbApplyingChangesEventArgs e)
        {
          if (e.Context.DataSet.Tables.Contains("Contacts"))
          {
            DataView contacts = new DataView(e.Context.DataSet.Tables["Contacts"], string.Empty, string.Empty, DataViewRowState.Deleted);
            
            DataView edges=null;
            if (e.Context.DataSet.Tables.Contains("Edges"))
              edges = new DataView(e.Context.DataSet.Tables["Edges"], string.Empty, string.Empty, DataViewRowState.Deleted);
    
            if (contacts.Count > 0)
            {
              foreach (DataRowView c in contacts)
              {
                c.Row.RejectChanges();
                Guid id =(Guid)c.Row["ContactId"];
                if (edges != null && !id.Equals(Guid.Empty))
                {
                  foreach (DataRowView ed in edges)
                  {
                    ed.Row.RejectChanges();
                    Guid from_contact=(Guid)ed.Row["NodeFrom"];
                    Guid to_contact=(Guid)ed.Row["NodeTo"];
                    if (!from_contact.Equals(id) && !to_contact.Equals(id)) ed.Row.Delete();
                    else
                    {
                      e.Context.DataSet.Tables["Edges"].Rows.Remove(ed.Row);
                    }
                  }
                }
                e.Context.DataSet.Tables["Contacts"].Rows.Remove(c.Row);
              }
            }
          }
    
        }
    

    • Marked as answer by l.d.v. _ Thursday, February 24, 2011 7:02 AM
    Tuesday, February 22, 2011 1:50 PM