Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Gets the list of filter parameters that are used to control which items are enumerated.
Namespace: Microsoft.Synchronization.Data.SqlServer
Assembly: Microsoft.Synchronization.Data.SqlServer (in Microsoft.Synchronization.Data.SqlServer.dll)
Syntax
'Declaration
Public ReadOnly Property FilterParameters As SqlSyncSqlParameterCollection
Get
'Usage
Dim instance As SqlSyncTableProvisioning
Dim value As SqlSyncSqlParameterCollection
value = instance.FilterParameters
public SqlSyncSqlParameterCollection FilterParameters { get; }
public:
property SqlSyncSqlParameterCollection^ FilterParameters {
SqlSyncSqlParameterCollection^ get ();
}
member FilterParameters : SqlSyncSqlParameterCollection
function get FilterParameters () : SqlSyncSqlParameterCollection
Property Value
Type: Microsoft.Synchronization.Data.SqlServer.SqlSyncSqlParameterCollection
The list of filter parameters that are used to control which items are enumerated.
Remarks
A filter parameter contains the name of a parameter and its value. Filter parameters are in the argument list of the select_changes stored procedure that is used for enumerating changes. A filter parameter is added by name when a filter template is created, and the value of each parameter is specified when a filtered scope is created from the filter template.
Examples
The following example shows how to create a filter template that filters based on the CustomerType column of the table. The filter parameter name is @customertype. At this point, only the parameter name is specified because a template is being created. The parameter value is specified when the filtered scope is created.
// 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()
The following example shows how to create a filtered scope that includes only retail customers. The @customertype filter parameter value is specified as "Retail".
// Create a synchronization scope for retail customers.
// This action adds rows to synchronization tables but does not create new tables or stored procedures, reducing
// the permissions needed on the server.
SqlSyncScopeProvisioning serverProvRetail = new SqlSyncScopeProvisioning(serverConn);
serverProvRetail.ObjectSchema = "Sync";
serverProvRetail.PopulateFromTemplate("RetailCustomers", "customertype_template");
serverProvRetail.Tables["Sales.Customer"].FilterParameters["@customertype"].Value = "Retail";
serverProvRetail.UserComment = "Customer data includes only retail customers.";
serverProvRetail.Apply();
// Provision the existing database SyncSamplesDb_SqlPeer2 based on filtered scope
// information that is retrieved from the server.
DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("RetailCustomers", null, "Sync", serverConn);
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlConn, clientSqlDesc);
clientSqlConfig.ObjectSchema = "Sync";
clientSqlConfig.Apply();
' Create a synchronization scope for retail customers.
' This action adds rows to synchronization tables but does not create new tables or stored procedures, reducing
' the permissions needed on the server.
Dim serverProvRetail As New SqlSyncScopeProvisioning(serverConn)
serverProvRetail.ObjectSchema = "Sync"
serverProvRetail.PopulateFromTemplate("RetailCustomers", "customertype_template")
serverProvRetail.Tables("Sales.Customer").FilterParameters("@customertype").Value = "Retail"
serverProvRetail.UserComment = "Customer data includes only retail customers."
serverProvRetail.Apply()
' Provision the existing database SyncSamplesDb_SqlPeer2 based on filtered scope
' information that is retrieved from the server.
Dim clientSqlDesc As DbSyncScopeDescription = SqlSyncDescriptionBuilder.GetDescriptionForScope("RetailCustomers", Nothing, "Sync", serverConn)
Dim clientSqlConfig As New SqlSyncScopeProvisioning(clientSqlConn, clientSqlDesc)
clientSqlConfig.ObjectSchema = "Sync"
clientSqlConfig.Apply()
See Also
Reference
SqlSyncTableProvisioning Class
Microsoft.Synchronization.Data.SqlServer Namespace
Other Resources
How to: Filter Data for Database Synchronization (SQL Server)