How to sync only specific rows in a database
I am trying to sync a couple of tables from a central database (sql-server) dbA with a local (sql-ce) client dbB database. For this I plan to use the Microsoft sync framework. And I was able to get it to work to keep all rows (or updated rows) in sync in some of the tables mentioned.
However, my dbA contains a lot of data and it grows as well, so I don't want clients to have a full copy of the dbA in their local databases. Instead, I just want them to have some parts of the database (some rows that depend on some expression, for example from table A just select rows where moduleId = 4)
So, initially I thought that filters seem to work for me. Specifically parameter-based filters, where I can specify a parameter for the filter. However, after some research, it turns out that when you create a filter based on parameters, you are actually just creating a template and then creating different scopes from it.
I cannot describe exactly what my application does, but you can think of it almost as a versioning system, and the parameter will represent which version I want to select. So you can see that this will become more areas.
So in the end, what are my options?
source to share
Perhaps you can use the SQL function on the filter parameter to make this change?
Something like
serverTemplate.Tables["tableA"].AddFilterColumn("ModuleId");
serverTemplate.Tables["tableA"].FilterClause = "[side].[ModuleId] = sf_GetCurrentModule()";
You can create a SQL function sf_GetCurrentModule () to return the ModuleId for synchronization. If the lines to be synchronized vary from client to client, you at least need a synchronization scope for each client, which is not a problem, just use a unique name for the scope and maybe pass the ID from client to function:
serverTemplate.Tables["tableA"].FilterClause = string.Format("[side].[ModuleId] = sf_GetCurrentModule({0})", ClientId);
This way you can get the ModuleId back to sync depending on the client.
Hope it helps.
Travis
source to share