Propriedade FilterClause

Artigo
08/18/2010

Obtém ou define a cláusula SQL WHERE (sem a palavra-chave WHERE) usada para filtrar o conjunto de resultados da tabela base.

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

Sintaxe

'Declaração
Public Property FilterClause As String
    Get
    Set
'Uso
Dim instance As SqlSyncTableProvisioning
Dim value As String

value = instance.FilterClause

instance.FilterClause = value

public string FilterClause { get; set; }

public:
property String^ FilterClause {
    String^ get ();
    void set (String^ value);
}

member FilterClause : string with get, set

function get FilterClause () : String
function set FilterClause (value : String)

Valor da propriedade

Tipo: System. . :: . .String
A cláusula SQL WHERE (sem a palavra-chave WHERE) usada para filtrar o conjunto de resultados da tabela base.

Comentários

A cláusula de filtro é uma cláusula WHERE sem a palavra-chave WHERE. Ela inclui um alias de [side] para a tabela de controle de alterações do Sync Framework. Por exemplo, se você desejar filtrar uma tabela de forma que sejam retornados apenas os resultados de clientes na Califórnia, a cláusula de filtro seria semelhante a: [side].[SalesTerritory] = 'CA'. Use AddFilterColumn para especificar as colunas incluídas nessa cláusula de filtro.

A cláusula de filtro também pode incluir parâmetros para filtragem baseada em parâmetros. Por exemplo, para filtrar com base no tipo de cliente, a cláusula de filtro poderia ser assim:[side].[CustomerType] = @customertype

Os aliases [base] e [side] são definidos pelo Sync Framework. [base] se refere ao nome base da tabela e [side] se refere à tabela de controle de alterações. Por exemplo, a tabela Customer é filtrada com base na coluna CustomerType. Por padrão, [base] é um alias para [Customer] e [side] é um alias para [Customer_tracking]. Como a coluna CustomerType existe tanto na tabela base quanto na tabela de controle, as referências a ela devem ser qualificadas na cláusula de filtro; do contrário, a coluna é ambígua e haverá um erro. Também é possível usar os nomes de tabela reais dos aliases [base] e [side], como [Customer_tracking].[CustomerType] = @customertype.

Exemplos

O exemplo a seguir mostra como criar um modelo de filtro que filtre com base na coluna CustomerType da tabela usando um parâmetro.

// 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.UserDescription = "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.UserDescription = "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()