How to: Filter Data for Database Synchronization (SQL Server)
This topic shows you how to create filters that can be used with Sync Framework to synchronize SQL Server, SQL Azure, and SQL Server Compact databases. The examples in this topic focus on the following Sync Framework classes and members:
PopulateFromTemplate(String, String)
For more information about how to run the sample code, see "Example Applications in the How to Topics" in Synchronizing SQL Server and SQL Server Compact.
Understanding Filters
A filter is used to control the items that are enumerated by the source provider in a synchronization session. When items are enumerated and sent to the destination provider, the source provider includes an item only when the item passes the filter. A filter is typically based on the value of one or more fields, so that a row is passed by the filter only when its filter fields meet the filter requirements.
Sync Framework enables you to create two types of filters: static filters and parameter-based filters. Static filters are defined as part of a synchronization scope and define the values that the filter fields must contain in order to be passed by the filter. Static filters are encoded in the stored procedure that is used by the source database to enumerate changes for the scope. After a static filter has been defined it cannot be changed. For more information and an example of how to use a static filter, see How to: Execute Database Synchronization (SQL Server). Parameter-based filters are defined by a filter clause and a set of parameters that map to table columns in the synchronization scope. A parameter-based filter is defined in two stages. The first stage defines the filter clause and the parameters and establishes the description of the scope associated with the filter. In this stage, the filter and scope are in a template format only. The second stage sets the parameter values for the filter and creates the synchronization scope from the template. The scope created in this stage is the scope that a destination provider uses to synchronize with the source database. The source database for parameter-based filtering can be either a SQL Server or SQL Azure database, and the destination database can be a SQL Server, SQL Azure, or SQL Server Compact database.
In a typical scenario for filtering, a database administrator or application developer defines a parameter-based filter and readies the server database for filtered synchronization. He can also optionally create a simple tool, such as a web-based subscription tool, that uses Sync Framework objects to let users specify their filter parameter values and subscribe their client databases for synchronization. By creating a subscription tool, the database administrator does not have to be involved in creating filters for individual users. Instead, users use the tool to specify the parameter values that are appropriate for them, and subscribe to synchronization on an as-needed basis.
An example process for setting up filtered synchronization is as follows:
You have a database of customer information that contains columns for the type of customer and the state where the customer is located.
You define a filter template that is based on two filter parameters: customer type and state. You specify that a row passes the filter only when its customer type and state values equal the parameter values.
A particular salesperson wants to synchronize her client database with your server. She requests the creation of a filtered scope with parameter values for retail customers in Washington, and synchronizes.
She receives just the customer data that she wants, reducing both network traffic and the amount of memory used on her client database.
Another salesperson requests a filtered scope that passes wholesale customers in Delaware, and synchronizes. He receives just the customer data he wants.
Be aware that parameter-based filters are appropriate only when items will not move into or out of the filter scope, such as when the value of a field changes so that an item that was previously passed by the filter is no longer passed by the filter. Changes of this kind will not be correctly propagated throughout the synchronization community. For example, a filter is defined based on a column for salesperson name. Salesperson A creates a scope filtered on his name and synchronizes data to his client database. His manager reassigns one of his customers to salesperson B. When salesperson A synchronizes again with the database, the reassigned customer will still appear in his client database with out-of-date data, instead of being removed.
Creating a Parameter-based Filter
Parameter-based filters are created in two steps. First, filter and scope templates are defined. Then, a filtered scope is created that has specific values for the filter parameters. This two-step process has the following advantages:
Easy to set up. A filter template is defined one time. Creating a filter template is the only action that requires permission to create stored procedures in the database server.
Easy to subscribe. Clients specify parameter values to create and subscribe to filtered scopes on an as-needed basis. This step requires only permission to insert rows in synchronization tables in the database server.
**Easy to maintain.**Even when several parameters are combined and lots of filtered scopes are created, maintenance is simple because a single, parameter-based procedure is used to enumerate changes.
Defining a Filter Template
The first step to creating a parameter-based filter is to define a filter template that can later be used to create filtered scopes. A filter template is stored in the source database and requires creation of synchronization tables and stored procedures. Therefore appropriate permissions are needed in the source database.
A filter template is defined together with a definition of a synchronization scope. You define a filter template for a table in the scope as follows:
Add a filter column to a SqlSyncTableProvisioning object in the synchronization scope by using AddFilterColumn(String). This adds the filter column to the tracking table that tracks changes for the base table.
Define one or more filter parameters by adding SqlParameter objects to the FilterParameters collection of the SqlSyncTableProvisioning object. This adds the specified parameters to the argument list of the stored procedure that enumerates changes during synchronization.
Add a filter clause that defines the relationship between parameter values and column values by setting the FilterClause property of the SqlSyncTableProvisioning object. The filter clause is a WHERE clause without the WHERE keyword. The [side] alias is an alias for the tracking table. The parameters match the parameters specified in the FilterParameters collection. At this point you are only defining the relationship between the filter parameters and columns. The actual values for the parameters will be specified later, when the filtered scope is created.
The filter and scope templates are then applied to the source database by using the Apply method of the SqlSyncScopeProvisioning object, at which point the appropriate synchronization tables and stored procedures are created.
In the filter clause, the aliases [base] and [side] are defined by Sync Framework. [base] refers to the base name for the table and [side] refers to the change-tracking table. For example, the Customer table is filtered based on the CustomerType column. By default, [base] is an alias for [Customer] and [side] is an alias for [Customer_tracking]. Because the CustomerType column exists in both the base and tracking tables, references to it must be qualified in the filter clause; otherwise, the column is ambiguous and an error will occur. You can also use the actual table names instead of the [base] and [side] aliases, such as [Customer_tracking].[CustomerType] = @customertype.
The following example defines a filter template and applies it to the source database:
Creating a Filtered Scope
Before a client can use a filter to synchronize with the server, the client must first define specific values for the filter parameters. To do this, the client first populates a SqlSyncScopeProvisioning object from the filter template on the server, and names the filtered scope, by calling PopulateFromTemplate(String, String). The client then defines the filter parameter values by setting the value properties of the FilterParameters collection members in the SqlSyncTableProvisioning object. Finally, the client applies the filtered scope to the server by calling the Apply method of the SqlSyncScopeProvisioning object. Applying a filtered scope to the server database adds rows to synchronization tables, requiring only permission to insert rows to these tables.
After the filtered scope has been specified on the server database, you provision a client database by calling GetDescriptionForScope to get the scope description for the named scope, loading the scope description into a SqlSyncScopeProvisioning object, and applying the scope description to the client database by calling Apply.
The code to define filter parameter values, apply the newly specified filter to the server database, and provision the client database can be easily encapsulated in a separate tool that gathers filter parameter values from a user and subscribes the user's client database for filtered synchronization.
The following example defines a parameter value for a filter, applies it to the server database, and provisions the client database with the filtered scope to ready it for synchronization:
Synchronizing a Client by Using a Filtered Scope
After the filtered scope has been defined and the client database is provisioned, the client can be synchronized by creating SqlSyncProvider objects for the filtered scope in the client and server databases, associating the providers with a SyncOrchestrator object, and by calling the Synchronize method.
The following example performs a filtered synchronization of two databases:
Example
The following example includes the code examples that are described earlier and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.