Remove From My Forums

Problem with Synchronizing Relative tables in Sync Framework 2.1

Question

Hello!!

I am trying to synchronize Sql Express 2008 with Sql Compact 3.5 with help of Sync Framework 2.1

I have 2 tables

Customers(PK Id , string Name )
Orders (PK Id , int Price , string Name , FK Customer_Id)

I Use the following code

public static void CreateScope(string userName)
{
	var serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=Test; Integrated Security=True");

	var scopeDescription = new DbSyncScopeDescription(string.Format("Customer-{0}", userID));
 
	var customersTable = SqlSyncDescriptionBuilder.GetDescriptionForTable("Customers", serverConn);
	var ordersTable = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
	
	ordersTable.Constraints.Add("FK_Customer_Id", "Customers", "Orders", "Id", "Customer_Id");
 
	scopeDesc.Tables.Add(azmanotTable);
	scopeDesc.Tables.Add(customersTable);
 
	var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
	serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

	serverProvision.Tables["Customers"].AddFilterColumn("CustumerName");
	serverProvision.Tables["Customers"].FilterClause = string.Format("[side].[CustomerName] = '{0}'", userName);

	serverProvision.Apply();
}

During the Sync I do receive filtered "Customers" table but i also receive full orders table when i wanted only subset of this data , I mean only orders that belong to specific customer.

I have tried all the options i found in web for example to change insert order adding foreign key manually but still no success.

Thanks in Advance.

Edited by Robob Saturday, May 28, 2011 7:09 AM

Saturday, May 28, 2011 12:45 AM

Answers

I found Solution using Nested Query

serverProvision.Tables["Customers"].AddFilterColumn("CustumerName");
serverProvision.Tables["Customers"].FilterClause = string.Format([side].[CustumerName] = '{0}'", userName);

serverProvision.Tables["Orders"].AddFilterColumn("Customer_Id");
serverProvision.Tables["Orders"].FilterClause = string.Format( "[side].[Customer_Id] IN (select Id from Customers where CustumerName ='{0}')", userName);

Is it the only possible solution because it means that each time table Orders needs to be synchronized additional sql query is executed

Thanks.

Sabi Reuven

Marked as answer by Yunwen Bai Sunday, May 29, 2011 5:48 AM

Saturday, May 28, 2011 9:22 AM

All replies

0

Sign in to vote

Sync Framework syncs tables independently regardless of the foreign key relationships.

in your case, you have to add a filter on the order's table as well.

e.g.,

serverProvision.Tables["Orders"].AddFilterColumn("CustomerID");
serverProvision.Tables["Orders"].FilterClause = string.Format("[side].[CustomerID] = '{0}'", userID);

Saturday, May 28, 2011 1:22 AM
0

Sign in to vote

O.K how it can be done if i don't know the custumerID (I made a mistake in the example and instead of filtering based on name i filtered based on id , the example is already updated ).

Thanks

Sabi Reuven

Saturday, May 28, 2011 7:12 AM