Answered by:
Controlling what data is syncd back to the server

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.0Any 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();
}
}
}
}
}- Marked as answer by Kyle LeckieEditor Wednesday, November 10, 2010 1:23 AM
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();
}
}
}
}
}- Marked as answer by Kyle LeckieEditor Wednesday, November 10, 2010 1:23 AM
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