Walkthrough: Defining Filtered Scope and Provisioning Server
It is often useful to create a scope that includes a subset of rows in a table based on a filter value instead of including all rows in the table. In this walkthrough you will create a scope that includes orders that originate in the state NC.
In Walkthrough: Defining Scope and Provisioning a Server walkthrough, you created a console application that defined the ProductsScope scope and provisioned the server database SyncDB with scope related artifacts. The ProductsScope was defined with no filter associated with it. In this walkthrough you will create a console application that defines a filtered scope and provisions the server database SyncDB with scope related artifacts.
A scope is a set of tables that are synchronized as a single unit. Some or all of these tables can be filtered. A scope with one or more tables that have filters specified is called filtered scope. In this walkthrough you will define a filtered scope named OrdersScope-NC that contains orders stored in the Orders table with the value of OriginState column in the table set to NC.
The tables in a sync scope can already exist in the database or they can be described by using the Sync Framework object model and then be generated at runtime when the underlying store is synchronized. In this walkthrough, you will use the Orders table that already exists in the server database.
The provisioning of a database involves adding sync scope related artifacts such as tracking tables, triggers, and stored procedures to the database. These artifacts are used by the synchronization process at runtime. Optionally, the base table is also added to the database as specified in the previous paragraph.
For in-depth technical details about provisioning filtered scopes, see How to: Filter Data for Database Synchronization (SQL Server).
To define a filtered scope and provision server
In Solution Explorer, Right-click Solution ‘SyncSQLServerAndSQLCompact’, point to Add, and click New Project.
Select Visual C# from Project Types, and select Console Application from Templates.
Type ProvisionServerWithFilteredScope for project name.
Click OK to close the New Project dialog box.
In Solution Explorer window, right-click ProvisionServerWithFilteredScope, and then click Add Reference.
Select Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer, and click OK to close the Add Reference dialog box.
Add the following using statements to the beginning of the Program.cs file after the existing using statements.
using System.Data.SqlClient; using Microsoft.Synchronization.Data; using Microsoft.Synchronization.Data.SqlServer;Add the following statement to the Main method to create an SQL connection to the server database.
Importante
In the above statement, replace the server name with your server’s instance name, if you are not using the default instance. For example: if your SQL Server instance is called MYSQLINSTANCE, replace (local) with .\MYSQLINSTANCE.
Add the following code to the Main method to create a sync scope based on the Orders table in the SyncDB database. This code creates a sync scope, gets the description of Orders table from the SyncDB server database, and adds the table description to the sync scope definition. The following list contains detailed steps to provision a server database with artifacts related to a filtered sync scope.
Create an instance of the DbSyncScopeDescription class. The DbSyncScopeDescription class is used to specify the name of the sync scope and the list of tables to be synchronized. The tables are specified using the DbSyncTableDescription class.
Create an instance of the DbSyncTableDescription class based on the schema of Orders table retrieved from the SyncDB server database. The DbSyncTableDescription class is used to specify the name of the table, columns of the table to be synchronized, data types of the table, and other information that is required for the sync. This information can be specified explicitly or it can be obtained by querying the database using the GetDescriptionForTable(String, SqlConnection) method.
In this walkthrough, you will use the GetDescriptionForTable(String, SqlConnection) method of the SqlSyncDescriptionBuilder class to retrieve the description of the table.
Add the DbSyncTableDescription object to Tables collection of the DbSyncScopeDescription object using the Add method.
Add the following code to the Main method to provision SyncDB server database with sync related artifacts. This code creates a SqlSyncScopeProvisioning object, sets the creation of table flag to Skip because the table already exists on the server, sets the OriginState column as the filter column with value set to NC, and starts the provisioning process.
Create an instance of the SqlSyncScopeProvisioning class based on the DbSyncScopeDescription object and a connection to the server database. The SqlSyncScopeProvisioning class represents the provisioning of a SQL Server database for a particular scope that is represented by a DbSyncScopeDescription object.
Invoke the SetCreateTableDefault(DbSyncCreationOption) method by specifying the DbSyncCreationOption value as Skip because the Orders table already exists in the server database. The SetCreateTableDefault(DbSyncCreationOption) method is used to specify whether to create base tables when a scope is configured.
Specify the OriginState column of the Orders table as the filter column and “OriginState = ‘NC’” as the filter clause for the table. The AddFilterColumn(String) method allows you to add a column that is used in the filter clause specified by the FilterClause property. The filter column is added to the tracking table that tracks changes made to the base table. The filter clause is a WHERE clause without the WHERE keyword. The [side] alias is an alias for the tracking table. You invoke these members on the SqlSyncTableProvisioning object associated with the Orders table.
Invoke the Apply method on SqlSyncScopeProvisioning object to start the provisioning process, which creates the change-tracking infrastructure in the server database.
In Solution Explorer, right-click ProvisionServerWithFilteredScope, and click Build.
In Solution Explorer, right-click ProvisionServerWithFilteredScope, and click Set as Startup Project.
Press Ctrl+F5 to execute the program.
Press ENTER to close the command prompt window.
In SQL Server Management Studio, expand SyncDB node, expand Tables, and you should see the following Orders_Tracking table created by the provisioning process. There are other objects such as triggers and stored procedures created by the provisioning process.
Keep Visual Studio open to perform the next walkthrough.
Complete Code Example
using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
namespace ProvisionServerWithFilteredScope
{
class Program
{
static void Main(string[] args)
{
//create connection to the server DB
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// define the OrdersScope-NC filtered scope
// this scope filters records in the Orders table with OriginState set to NC"
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope-NC");
// get the description of the Orders table and add it to the scope
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
scopeDesc.Tables.Add(tableDesc);
// create server provisioning object
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// no need to create the Orders table since it already exists,
// so use the Skip parameter
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// set the filter column on the Orders table to OriginState
serverProvision.Tables["Orders"].AddFilterColumn("OriginState");
// set the filter value to NC
serverProvision.Tables["Orders"].FilterClause = "[side].[OriginState] = 'NC'";
// start the provisioning process
serverProvision.Apply();
}
}
}
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
namespace ProvisionServerWithFilteredScope
{
class Program
{
static void Main(string[] args)
{
//create connection to the server DB
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// define the OrdersScope-NC parameterized scope
// this scope filers records in the Orders table with OriginState set to NC
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope-NC");
// gets the description of the Orders table and add it to the scope
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
scopeDesc.Tables.Add(tableDesc);
// create server provisioning object
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// no need to create the Orders table since it already exists,
// so use the Skip parameter
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// set the filter column on the Orders table to OriginState
serverProvision.Tables["Orders"].AddFilterColumn("OriginState");
// set the filter value to NC
serverProvision.Tables["Orders"].FilterClause = "[side].[OriginState] = 'NC'";
// start the provisioning process
serverProvision.Apply();
}
}
}
Vea también
Conceptos
How to: Filter Data for Database Synchronization (SQL Server)