SqlSyncTableProvisioning::FilterParameters Property
Gets the list of filter parameters that are used to control which items are enumerated.
Assembly: Microsoft.Synchronization.Data.SqlServer (in Microsoft.Synchronization.Data.SqlServer.dll)
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.
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()