Walkthrough: Provisioning a Client Database with Filtered Scope

In this walkthrough you will create a console application, which provisions the SQL Express database SyncExpressDB with artifacts related to the OrdersScope-NC sync scope. Basically, the provisioning process prepares the client database for synchronization of OrdersScope-NC scope related data with the server database. For in-depth details about provisioning clients, see Provisioning the Clients and for details about filtered scopes, see How to: Filter Data for Database Synchronization (SQL Server).

To provision SQL Express database

The following list contains steps to create a console application using Visual Studio to provision the SQL Express database SyncExpressDB with artifacts related to sync scope OrdersScope-NC.

1. In Solution Explorer, right-click Solution ‘SyncSQLServerAndSQLExpress’, point to Add, and click New Project.

2. Select Visual C# from Project Types, and select Console Application from Templates.

3. Type ProvisionFilteredScopeClient for project name.

4. Click OK to close the New Project dialog box.

5. In Solution Explorer window, right-click ProvisionFilteredScopeClient, and then click Add Reference.

6. Select Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer, and click OK to close the Add Reference dialog box.

7. 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;
   

8. Add the following statement to the Main method to create a SQL connection to the express database.

9. Add the following statement to the Main method to create an SQL connection to the server database.

   警告

   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.

10. Add the following statement to the Main method to get the description of the OrdersScope-NC scope from the SQL Server. This statement invokes the GetDescriptionForScope(String, SqlCeConnection) method on the SqlSyncDescriptionBuilder class to retrieve description of the OrdersScope-NC from server.

    The DbSyncScopeDescription class is used to specify the name of the sync scope and the list of tables to be synchronized. This information can be specified explicitly or it can be obtained by querying the database using the GetDescriptionForScope(String, String, SqlConnection) method. In this walkthrough, you will use the GetDescriptionForScope(String, String, SqlConnection) method of the SqlSyncDescriptionBuilder class to retrieve the description of the scope from the server.

11. Add the following statements to provision the SyncExpressDB database with sync related artifacts. This code creates a SqlCeSyncScopeProvisioning object, sets the flag to create the Orders table on the client database, and runs the provisioning process to add scope related artifacts to the client. The high level steps for provisioning a SQL Express database with sync scope related artifacts are:

    1. Create an instance of the SqlCeSyncScopeProvisioning class based on the DbSyncScopeDescription obtained in the previous step and a connection to the SQL Express database. The SqlCeSyncScopeProvisioning class represents the provisioning of a SQL Express database for a particular scope that is represented by a DbSyncScopeDescription object.

    2. Invoke the Apply method on SqlCeSyncScopeProvisioning object to start the provisioning process, which creates the change-tracking infrastructure in the SyncExpressDB database.

       注意

       You do not need to invoke the SetCreateTableDefault(DbSyncCreationOption) method explicitly with DbSyncCreationOption value as CreateOrUseExisting because the CreateOrUseExisting is the default value that sync framework uses. The Products table is automatically created in the compact database during the provisioning process. The SetCreateTableDefault(DbSyncCreationOption) method is used to specify whether to create base tables when a scope is configured.

12. In Solution Explorer, right-click ProvisionFilteredScopeClient, and click Build.

13. In Solution Explorer, right-click ProvisionFilteredScopeClient again, and click Set as Startup Project.

14. Press Ctrl+F5 to execute the program.

15. Press ENTER to close the command prompt window.

16. In SQL Server Management Studio, right-click .\SQLEXPRESS, and click Refresh.

17. Expand .\SQLEXPRESS node, expand Databases, expand SyncExpressDB, expand Tables, and you should see the tables created by the provisioning process. There are other objects such as triggers and stored procedures created by the provisioning process.

18. Keep Visual Studio and SQL Server Management Studio open.

Complete Code Example

using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace ProvisionFilteredScopeClient
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection clientConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=SyncExpressDB; Trusted_Connection=Yes");

            SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");

            // get description for the OrdersScope-NC scope from the SyncDB server database
            DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("OrdersScope-NC", serverConn);

            // create a provisioning object 
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);

            // start the provisioning process
            clientProvision.Apply();
        }
    }
}

請參閱

概念

How to: Filter Data for Database Synchronization (SQL Server)