locked
Controlling what data is syncd back to the server RRS feed

  • Question

  • We're using SQL Server 2008, SQL Server CE SP2, VS2010, Sync Framework 2.1

    To prevent desktops from being cluttered with unneeded data, we want to delete data records on the desktop when the user is done with them. We do not want these deletions to be synchronized to the server.

    With prior versions of Sync Framework, I believe the solution was to use SqlCeClientSyncProvider.AcceptChanges() to accomplish this.

    The the 2.1 Overview (http://msdn.microsoft.com/en-us/library/bb902818(SQL.110).aspx), indicates that SqlCeClientSyncProvider should be replaced with SqlCeSyncProvider for new applications, but there is no AcceptChanges method in this class.

    What is the appropriate technique to block synchronization of some client changes when using SqlCeSyncProvider??

    I thought it might be to use SqlCeChangeTracking.Disable(), perform the deletion and then re-enable, but I cannot get .Disable to work. It issues an error:

    System.Data.SqlServerCe.SqlCeException was unhandled
      Message=Column or constraint could not be dropped because it is referenced by a dependent view or constraint. [ __sysChangeTxBsn ]
      Source=SQL Server Compact ADO.NET Data Provider
      HResult=-2147217776
      NativeError=0
      StackTrace:
           at System.Data.SqlServerCe.SqlCeChangeTracking.ProcessResults(Int32 hr)
           at System.Data.SqlServerCe.SqlCeChangeTracking.DisableTracking(String tableName)
           at Ontario.Justice.LSIOR.CommonInfrastructure.DisableEnableSync.Disable() in D:\Projects\LSIORDbProvision\ProvisionServer\DisableEnableSync.cs:line 23
           at DisableAndDelete.Program.Main(String[] args) in D:\Projects\LSIORDbProvision\DisableAndDelete\Program.cs:line 15
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:

    Product versions and locations are:

    Microsoft.Synchronization - C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\x86\Microsoft.Synchronization.dll. Runtime is: v2.0.50727. Version is: 2.1.0.0
    Microsoft.Synchronization.Data - C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86\Microsoft.Synchronization.Data.dll Runtime is: v2.0.50727. Version is: 3.1.0.0
    Microsoft.Synchronization.Data.Server - C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86\Microsoft.Synchronization.Data.Server.dll. Runtime is: v2.0.50727. Version is: 3.1.0.0
    Microsoft.Synchronization.Data.SqlServer - C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86\Microsoft.Synchronization.Data.SqlServer.dll. Runtime is: v2.0.50727. Version is: 3.1.0.0
    Microsoft.Synchronization.Data.SqlServerCe - C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86\Microsoft.Synchronization.Data.SqlServerCe.dll. Runtime is: v2.0.50727. Version is: 3.1.0.0

     

    Any help would be appreciated, thanks.

     

     

    Monday, November 8, 2010 3:33 PM

Answers

  • your SqlCeClientSyncProvider would run just fine in 2.1 and you dont have to change it to SqlSyncProvider. the providers work differently and its not just a matter of changing the provider.

    if you want to manipulate client changes before they are uploaded and applied to the server, you can subscribe to the ChangesSelected event on the client side and go thru the change dataset and manipulate it.

    here's a sample:

    void LocalDataCache1ServerSyncProvider_ChangesSelected(object sender, Microsoft.Synchronization.Data.ChangesSelectedEventArgs e)
            {
                //let's check if we're synching the table we're interested
                if (e.Context.DataSet.Tables.Contains("WorkOrders"))
                {
                    var dataTable = e.Context.DataSet.Tables["WorkOrders"];
                    for (int j = 0; j < dataTable.Rows.Count; j++)
                    {
                        DataRow row = dataTable.Rows[j];

                        // we're only interested in updates
                        if (row.RowState == DataRowState.Modified)
                        {
                            // check if the status is Completed
                            if (row["Status"].ToString() == "Completed")
                            {
                               // let's delete the row so it gets applied as a delete instead of applying it as an update
                                dataTable.Rows[j].Delete();
                            }
                        }
                    }
                }
           }

    Tuesday, November 9, 2010 4:24 PM

All replies

  • your SqlCeClientSyncProvider would run just fine in 2.1 and you dont have to change it to SqlSyncProvider. the providers work differently and its not just a matter of changing the provider.

    if you want to manipulate client changes before they are uploaded and applied to the server, you can subscribe to the ChangesSelected event on the client side and go thru the change dataset and manipulate it.

    here's a sample:

    void LocalDataCache1ServerSyncProvider_ChangesSelected(object sender, Microsoft.Synchronization.Data.ChangesSelectedEventArgs e)
            {
                //let's check if we're synching the table we're interested
                if (e.Context.DataSet.Tables.Contains("WorkOrders"))
                {
                    var dataTable = e.Context.DataSet.Tables["WorkOrders"];
                    for (int j = 0; j < dataTable.Rows.Count; j++)
                    {
                        DataRow row = dataTable.Rows[j];

                        // we're only interested in updates
                        if (row.RowState == DataRowState.Modified)
                        {
                            // check if the status is Completed
                            if (row["Status"].ToString() == "Completed")
                            {
                               // let's delete the row so it gets applied as a delete instead of applying it as an update
                                dataTable.Rows[j].Delete();
                            }
                        }
                    }
                }
           }

    Tuesday, November 9, 2010 4:24 PM
  • Thanks. That gives me something to work with.

    I'm working through the sample provided at: http://msdn.microsoft.com/en-us/library/ff928701(v=SQL.110).aspx

    After data is synched to the client, I insert a new record on the client and sync this back to the server. All is ok.

    I then delete the record from the client and attempt to block the deletion from synching to the server using:

    SqlCeClientSyncProvider csp = new SqlCeClientSyncProvider(Utility.ConnStr_SqlCeClientSync);
    csp.AcceptChanges("Sales.Customer");

    This fails with error:

    System.Data.SqlServerCe.SqlCeException was unhandled
      Message=Column or constraint could not be dropped because it is referenced by a dependent view or constraint. [ __sysChangeTxBsn ]
      Source=SQL Server Compact ADO.NET Data Provider
      HResult=-2147217776
      NativeError=0
      StackTrace:
           at System.Data.SqlServerCe.SqlCeChangeTracking.ProcessResults(Int32 hr)
           at System.Data.SqlServerCe.SqlCeChangeTracking.DisableTracking(String tableName)
           at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.DisableOcsTracking(String tableName)
           at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.EnableOcsTracking(String tableName, TrackingKeyType seTrackingType)
           at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.CreateOcsTable(String fmtTableName, Boolean createTable, SyncTable syncTable, SyncSchema syncSchema, SqlCeCommand cmdUtil)
           at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.CreateSchema(SyncTable syncTable, SyncSchema syncSchema)
           at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.GetChanges(IEnumerable`1 tableNames)
           at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.AcceptChanges(IEnumerable`1 tableNames)
           at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.AcceptChanges(String tableName)
           at SyncExampleConsoleForDelete.Program.Main(String[] args) in D:\Projects\LSIORDbProvision\SyncExampleConsoleForDelete\Program.cs:line 30
      InnerException:

    So there appears to be some issue with SqlCeClientSyncProvider calling into SqlCeChangeTracking.

    Anyhoo, I took your advice and used event code to trap the deletion at the client. It appears to work. The deletion on the client is not synched to the server.

    static void ClientSyncProvider_ChangesSelected(object sender, DbChangesSelectedEventArgs e)
    {
     if (e.Context.DataSet.Tables.Contains("Sales.Customer"))
     {
      DataTable dt = e.Context.DataSet.Tables["Sales.Customer"];
      for (int j = 0; j < dt.Rows.Count; j++)
      {
       DataRow row = dt.Rows[j];
       if (row.RowState == DataRowState.Deleted)
       {
        row.AcceptChanges();
       }
      }
     }
    }

    I have a lot of tables to deal with, so I'm not keen on looping thru a bunch of rows, but the client won't have a lot of entries in the tables.

     

    Thanks again.

     

     

     

     

    Tuesday, November 9, 2010 8:43 PM