How to: Filter Rows and Columns
This topic describes how to filter rows and columns in tables that are synchronized. The examples in this topic focus on the following Sync Framework types:
For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Client and Server Synchronization Tasks.
Understanding Filtering
Frequently, the client database requires only a subset of the data that is available at the server. Sync Framework lets you specify which rows and columns a client requires, whether you construct synchronization commands manually or by using the SqlSyncAdapterBuilder. By filtering data, you can accomplish the following:
Reduce the amount of data sent over the network.
Reduce the amount of storage space that is required at the client.
Provide custom data partitions based on individual client requirements.
Avoid or reduce conflicts if clients are updating data, because different data partitions can be sent to different clients. (No two clients will be updating the same data values.)
Filters can be based on a single table, or they can reference multiple tables by using JOIN clauses or multiple SELECT statements. Basing filters on multiple tables enables a user to download a partition of data, such as a salesperson that requires only the data for her customers and all the customers' orders. Filtering across multiple tables provides flexibility. However, you should keep filters as simple as possible and test performance as the number of tables increases. You should also index the columns on which filters are based.
Importante
Do not rely on filtering for security. The ability to filter data from the server based on a client or user ID is not a security feature. In other words, this approach cannot be used to prevent one client from reading data that belongs to another client. This type of filtering is useful only for partitioning data and reducing the amount of data that is brought down to the client database.
Sync Services does not provide automatic partition management. This has the following consequences:
If you update a row and change the value of a column that was used in filtering, the row is not automatically deleted from those clients whose partition included that row. Consider an application that downloads customer data based on postal code to a salesperson. If a customer moves its offices into a new postal code area, data for that customer is not removed from the salesperson who originally had it. If that functionality is required, you could develop a system that enables Sync Services to download that update as a delete.
There is no mechanism to prevent an application from inserting data at the client that is outside of that client's partition. You could add constraints at the client to disallow out-of-partition inserts and updates.
Filters Based on Non-Key Columns
In SQL Server change tracking and in some custom tracking systems, only the primary key is retained for deleted rows. If a filter is based only on the primary key, the query that you specify for the SelectIncrementalDeletesCommand property can identify the correct subset of rows and download them to the client. If the filter is based on columns outside the primary key, the query fails because it references columns that no longer exist for deleted rows. To address this issue, consider using one of the following approaches:
Include all filtering columns in the primary key. Put the additional columns at the end of the key so that you do not affect the selectivity of the key.
Only filter inserts and updates. Superfluous deletes will be downloaded to the client, but they will be ignored.
Perform logical deletes on the server. Instead of deleting the row, use an ON DELETE trigger to update a column that flags the row as deleted or archived. The change is then sent to the client as an update.
Overload the context column in SQL Server change tracking (SYS_CHANGE_CONTEXT) with additional values that can be used to filter data. This is probably the best option in terms of performance, but it might be the most complex because you have to parse this column.
Example
The example code in this topic shows you how to filter data for the Customer, OrderHeader, and OrderDetail tables from the Sync Services sample database. The Customer table is filtered so that only rows that have a value of Brenda Diaz for the SalesPerson column are downloaded. The filter is then extended to the other two tables. The example shows how to filter data by using the SqlSyncAdapterBuilder and by creating synchronization commands manually. For an overview of synchronization commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.
Using the SqlSyncAdapterBuilder
This section describes the API that is used in filtering if you create commands by using SqlSyncAdapterBuilder. This section provides code examples that point out the key parts of the API, and then provides a complete code example.
Key Parts of the API
The following code example creates a filter parameter that is used in the filter clause for all three tables.
The following code example specifies which columns to download for the Customer table.
The following code example specifies which rows to download for the Customer table. You can hardcode a value for SalesPerson. However, it is more common to use a parameter that has a value that can change, as shown in the example. The parameter from the first code example is used.
The following code example extends the filter from the Customer table to the OrderHeader table. In this case, a SELECT statement is used in the filter clause. For the manual commands, a JOIN clause is used because it provides more control over how to specify commands.
The following code example specifies a value for the @SalesPerson parameter in a class that derives from SyncAgent. In an application, this value might come from a login ID or other user input.
Complete Code Example
The following complete code 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. When you run the sample, pay attention to the information returned by the SyncStatistics: a subset of rows is downloaded in both the initial and later synchronizations.
Using Manual Commands
This section describes the API that is used in filtering if you create the commands manually. This section provides code examples that point out the key parts of the API, and then provides a complete code example.
Key Parts of the API
The following code example specifies which inserted columns and rows to download for the Customer table. You can hardcode a value for SalesPerson. However, it is more common to use a parameter that has a value that can change, as shown in the example. The example passes the filter parameter together with the other parameters that are required to download incremental inserts.
The following code example extends the filter from the Customer table to the OrderHeader table. In this case, a JOIN clause is used to define the relationship between the two tables.
The following code example specifies a value for the @SalesPerson parameter in a class that derives from SyncAgent. In an application, this value might come from a login ID or other user input.
Complete Code Example
The following complete code 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. When you run the sample, pay attention to the information returned by the SyncStatistics: a subset of rows is downloaded in both the initial and later synchronizations.