Walkthrough: Provisioning a SQL Compact Client
In this walkthrough you will create a SQL Server compact database named SyncCompactDB, and create a console application that provisions the compact database with ProductsScope related artifacts. The provision process prepares the client database for synchronization with the server. In the previous walkthrough Walkthrough: Defining Scope and Provisioning a Server, you provisioned the server database with sync related artifacts already. For in-depth technical details about provisioning, see Provisioning the Clients and Provisioning for Synchronization (SQL Server).
To create a SQL Server compact database
The following list contains steps to create the SyncCompactDB compact database, which will be used as a client for the SyncDB server database.
In SQL Server Management Studio, click File menu, and click Connect Object Explorer.
In the Connect to Server dialog box, select SQL Server Compact Edition for Server type.
Click down-arrow next to Database file, and click New Database.
In the Create New SQL Server Compact Database dialog box, type C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf for Database file name, and click OK.
Click Yes on the message box you see about the blank password.
Click Connect on the Connect to Server dialog box
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 ProductsScope.
In Visual Studio, 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 ProvisionClient for project name.
Click OK to close the New Project dialog box.
In Solution Explorer window, right-click ProvisionClient, and click Add Reference.
Select Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer, Microsoft.Synchronization.Data.SqlServerCe and click OK to close the Add Reference dialog box.
Repeat previous two steps to add a reference to System.Data.SqlServerCe assembly.
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;Add the following statement to the Main method to create a SQL connection to the compact database.
Add the following statement to the Main method to create a 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.
Add the following statement to the Main method to get the description of the ProductsScope scope from the SQL Server. This statement invokes the GetDescriptionForScope(String, SqlCeConnection) method on the SqlSyncDescriptionBuilder class to retrieve description of the ProductsScope 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.
Add the following statements to provision the SQL Server compact database with the ProductsScope related artifacts. The high level steps for provisioning a SQL Server Compact database with sync scope related artifacts are:
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.
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.
Note
The SqlCeSyncScopeProvisioning class is used to provision SQL Server Compact databases and the SqlCeSyncScopeProvisioning class is used to provision compact (SQL Server Compact) databases.
In Solution Explorer, right-click ProvisionClient, and click Build.
In Solution Explorer, right-click ProvisionClient again, and click Set as Startup Project.
Warning
If you do not set the current project as a startup project and press Ctrl+F5 again, the ProvisionServer application is executed again, and you get an error message similar to the following: “scope that already exists in the server database”.
Press Ctrl+F5 to execute the program.
Press ENTER to close the command prompt window.
In SQL Server Management Studio, expand SQL Server Compact [My Computer\...\SyncCompactDB] node, expand Tables, and you should see the following additional tables created by the provisioning process: Products_Tracking, schema_info, scope_config, and scope_info. There are other objects such as triggers and stored procedures created by the provisioning process.
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 ProvisionClient
{
class Program
{
static void Main(string[] args)
{
// create a connection to the SyncCompactDB database
SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf'");
// create a connection to the SyncDB server database
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// get the description of ProductsScope from the SyncDB server database
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);
// create CE provisioning object based on the ProductsScope
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
// starts the provisioning process
clientProvision.Apply();
}
}
}