Walkthrough: Provisioning a Client with Filtered Scope

  • Article

In this walkthrough you will create a console application, which provisions the SQL Server compact database SyncCompactDB 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 Server Compact database

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

  1. In Solution Explorer, right-click Solution ‘SyncSQLServerAndSQLCompact’, 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 System.Data.SqlServerCe, Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer, Microsoft.Synchronization.Data.SqlServerCe 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 System.Data.SqlServerCe;

using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;

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

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

    Important

    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 SyncDB server database. 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 SyncCompactDB compact 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 Server Compact 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 compact database. The SqlCeSyncScopeProvisioning class represents the provisioning of a SQL Server Compact 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 compact database.

      Note

      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 SQL Server Compact [My Computer\...\SyncCompactDB], and click Refresh.

  17. Expand SQL Server Compact [My Computer\...\SyncCompactDB] node, expand Tables, and you should see Orders and Orders_tracking 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 System.Data.SqlServerCe;

using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;

namespace ProvisionFilteredScopeClient
{
    class Program
    {
        static void Main(string[] args)
        {
            //create connection to the compact DB
            SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf'");

            //create connection to the server DB
            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 CE provisioning object
            SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);

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

See Also

Concepts

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