Export (0) Print
Expand All

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.

Namespace:  Microsoft.Synchronization.Data.SqlServer
Assembly:  Microsoft.Synchronization.Data.SqlServer (in Microsoft.Synchronization.Data.SqlServer.dll)

public string FilterClause { get; set; }

Property Value

Type: System.String
The SQL WHERE clause (without the WHERE keyword) that is used to filter the result set from the base table.

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()


Show:
© 2015 Microsoft