SqlSyncTableProvisioning::FilterClause Property
Gets or sets the SQL WHERE clause (without the WHERE keyword) that is used to filter the result set from the base table.
Assembly: Microsoft.Synchronization.Data.SqlServer (in Microsoft.Synchronization.Data.SqlServer.dll)
The filter clause is a WHERE clause without the WHERE keyword. It includes an alias of [side] for the Sync Framework change-tracking table. For example, if you want to filter a table so that only results for customers in California are returned, the filter clause would be similar to the following: [side].[SalesTerritory] = 'CA'. Use AddFilterColumn to specify the columns that are included in this filter clause.
The filter clause can also include parameters for parameter-based filtering. For example, to filter based on the customer type the filter clause might look like this: [side].[CustomerType] = @customertype
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 shows how to create a filter template that filters based on the CustomerType column of the table by using a parameter.
// Create a scope named "customertype_template", and add two tables to the scope. // GetDescriptionForTable gets the schema of each table, so that tracking // tables and triggers can be created for that table. DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("customertype_template"); // Set a friendly description of the template. scopeDesc.UserComment = "Template for Customer and CustomerContact tables. Customer data is filtered by CustomerType parameter."; // Definition for tables. DbSyncTableDescription customerDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn); scopeDesc.Tables.Add(customerDescription); DbSyncTableDescription customerContactDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", serverConn); scopeDesc.Tables.Add(customerContactDescription); // Create a provisioning object for "customertype_template" that can be used to create a template // from which filtered synchronization scopes can be created. We specify that // all synchronization-related objects should be created in a // database schema named "Sync". If you specify a schema, it must already exist // in the database. SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template); serverTemplate.ObjectSchema = "Sync"; // Specify the column in the Customer table to use for filtering data, // and the filtering clause to use against the tracking table. // "[side]" is an alias for the tracking table. // The CustomerType column that defines the filter is set up as a parameter in this template. // An actual customer type will be specified when the synchronization scope is created. serverTemplate.Tables["Sales.Customer"].AddFilterColumn("CustomerType"); serverTemplate.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = @customertype"; SqlParameter param = new SqlParameter("@customertype", SqlDbType.NVarChar, 100); serverTemplate.Tables["Sales.Customer"].FilterParameters.Add(param); // Create the "customertype_template" template in the database. // This action creates tables and stored procedures in the database, so appropriate database permissions are needed. serverTemplate.Apply();
' Create a scope named "customertype_template", and add two tables to the scope. ' GetDescriptionForTable gets the schema of each table, so that tracking ' tables and triggers can be created for that table. Dim scopeDesc As New DbSyncScopeDescription("customertype_template") ' Set a friendly description of the template. scopeDesc.UserComment = "Template for Customer and CustomerContact tables. Customer data is filtered by CustomerType parameter." ' Definition for tables. Dim customerDescription As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn) scopeDesc.Tables.Add(customerDescription) Dim customerContactDescription As DbSyncTableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.CustomerContact", serverConn) scopeDesc.Tables.Add(customerContactDescription) ' Create a provisioning object for "customertype_template" that can be used to create a template ' from which filtered synchronization scopes can be created. We specify that ' all synchronization-related objects should be created in a ' database schema named "Sync". If you specify a schema, it must already exist ' in the database. Dim serverTemplate As New SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template) serverTemplate.ObjectSchema = "Sync" ' Specify the column in the Customer table to use for filtering data, ' and the filtering clause to use against the tracking table. ' "[side]" is an alias for the tracking table. ' The CustomerType column that defines the filter is set up as a parameter in this template. ' An actual customer type will be specified when the synchronization scope is created. serverTemplate.Tables("Sales.Customer").AddFilterColumn("CustomerType") serverTemplate.Tables("Sales.Customer").FilterClause = "[side].[CustomerType] = @customertype" Dim param As New SqlParameter("@customertype", SqlDbType.NVarChar, 100) serverTemplate.Tables("Sales.Customer").FilterParameters.Add(param) ' Create the "customertype_template" template in the database. ' This action creates tables and stored procedures in the database, so appropriate permissions are needed. serverTemplate.Apply()