This sample shows how to use SQL Server Compact 3.5 as a Subscriber in a merge replication topology for delivering data to mobile users. It also demonstrates the programmability features of replication in SQL Server Compact 3.5. The sample is a Windows Forms-based application that uses standard Microsoft data access technologies and merge replication to enable a salesperson to maintain local sales data and synchronize periodically with the home office.
Language
Prerequisites
Before running this sample, make sure that the following software is installed on the development computer:
-
Microsoft .NET Compact Framework 2.0 or a later version.
-
Microsoft .NET Framework version 2.0 SDK or Visual Studio 2008 or a later version.
-
SQL Server 2005 or a later version.
-
Microsoft ActiveSync 4.0 or a later version.
-
SQL Server samples. These samples are included with SQL Server and are also available at the SQL Server Developer Center. For more information, see Installing Samples.
You must also have either a mobile device or a mobile device emulator (included with Visual Studio 2008) with at least 15 MB of free memory on which to deploy and run the sample.
To initialize the subscription and download sales order data to the device, you must have a SQL Server Publisher configured to run the Sales Orders Sample for Merge Replication.
Required Permissions
The permissions that are required to configure and run the Sales Orders sample are based on replication security requirements, and are as follows:
-
To run InstallSalesOrdersSample.bat at the Publisher, the Windows account must be a member of the sysadmin fixed server role at the Publisher. This batch file configures replication, creates the AdvWorksSalesOrders publication, and generates the initial snapshot.
-
To initialize the subscription and synchronize data with the Publisher, the Windows account that is used to connect to the Web server must meet the following requirements:
-
Have a valid login at the Publisher associated with a user in the publication database.
-
Have a valid login at the Distributor associated with a user in the distribution database (or the guest user).
-
Be a member of the publication access list (PAL) for the AdvWorksSalesOrders publication.
.gif) Note: |
|---|
|
When you use an anonymous connection to the Web server, the IIS anonymous Windows account is used to connect to the Publisher. |
-
To connect to the Web server by using Secure Sockets Layer (SSL) encryption, you must have a copy of the root certificate of the server certificate used to help secure the Web server installed as a root certificate on the device. If the Web server is configured to require client certificates, you must also have a valid certificate installed as a personal certificate on the device.
Building and Configuring the Sample
To configure the replication topology and build and deploy the sample project:
-
At the Publisher, run the publication installation script from the Sales Orders sample.
-
Build the Sales Orders sample and deploy the business logic handler assembly on the Web server.
-
Run the Configure Web Synchronization wizard to configure the virtual directory.
-
Build and deploy the sample to a device or device emulator using Visual Studio 2008.
To configure the Publisher, and build and deploy the sample project
-
On a computer that is running SQL Server 2005 or a later version, execute the installation script from the Sales Orders Sample for Merge Replication to create the AdvWorksSalesOrders publication at the Publisher.
-
At the command prompt, navigate to the SQL Server samples installation directory. The default is C:\Program Files\Microsoft SQL Server\90\Samples.
-
Execute the following at a .NET Framework or Visual Studio 2008 command prompt:
sn -k SampleKey.snk
| Note: |
|---|
|
You can skip this step if this key was previously created for use with another sample. |
-
Navigate to the installation location for the Sales Orders sample solution in Microsoft Visual C#. The default is C:\Program Files\Microsoft SQL Server\90\Samples\Replication\Merge\SalesOrders\CS.
-
Open the SalesOrders.sln solution file in Visual Studio 2008.
-
Build the project.
-
Place a copy of the BusinessLogic.dll assembly in the C:\Program Files\Microsoft SQL Server\90\COM directory on the Web server.
-
Run the Configure Web synchronization Wizard to create a virtual directory name "SalesOrdersMobile." For information about how to run this wizard, follow the steps in the How to: Configure Connectivity with the Configure Web Synchronization Wizard topic in SQL Server Compact 3.5 Books Online.
| Note: |
|---|
|
When client certificates are required to connect to the Web server, you must have a valid personal certificate installed on the device. |
-
If using an anonymous connection to the Web server, create a login at the Publisher for the Windows account used for anonymous access, create a user in the AdventureWorks database for this login, and add this user to the PAL for the AdvWorksSalesOrders publication. For more information, see How to: Manage Logins in the Publication Access List (SQL Server Management Studio).
-
Navigate to the installation location for the Sales Orders Mobile sample solution in Microsoft Visual C#. The default is C:\Program Files\Microsoft SQL Server\90\Samples\Replication\Merge\SalesOrdersMobile\CS.
-
Open the SalesOrdersMobile.sln solution file in Visual Studio 2008.
-
Connect the device or device emulator and start ActiveSync.
-
Build and deploy the project to the device.
Running the Sample
To run the Sales Orders Mobile sample and synchronize data
-
Cradle the device and ensure that ActiveSync is running.
-
To help secure the connection to the Web server by using SSL: Export the root certificate of the server certificate that was used to help secure the SalesOrdersMobile virtual directory (in DER encoded binary X.509 format), copy it to the device, and tap to install it as a root certificate on the device. When client certificates are required to connect to the Web server, you must also have a valid personal certificate installed on the device.
.gif) Security Note: |
|---|
|
If you use Basic Authentication to connect to the Web server, you should use SSL to encrypt the connection to the Web server. This protects the Windows credentials. |
-
On the device, start Internet Explorer and browse to one of the following URLs:
-
http://webserver/SalesOrdersMobile/sqlcesa35.dll?diag - when using anonymous access to the Web server.
-
https://webserver/SalesOrdersMobile/sqlcesa35.dll?diag - when connecting to the Web server using SSL and specifying a Windows login and password to use for Basic Authentication.
-
On the device or device emulator, start the SalesOrdersMobile program. When run for the first time, the program creates the mobile subscription database (\My Documents\AWMobile.sdf).
-
On the Settings page, enter the name of the Publisher and the URL of the Web server in one of the following formats:
-
http://webserver/SalesOrdersMobile/sqlcesa35.dll. This URL is used when the Connect anonymously to server box is checked.
-
https://webserver/SalesOrdersMobile/sqlcesa35.dll. This URL is used when the Connect anonymously to server box is unchecked. You must also specify a Windows login and strong password in the Windows login and Password fields, where these values are the credentials that correspond to a Windows login at the Publisher and Distributor.
-
Tap Save to save these settings.
-
On the Synchronize page, verify the value in the Hostname field, select Full (recommended) for Sync type, and then tap Synchronize. The device connects to the specified Web server to create, initialize, and download subscription data for the application.
To update sales order data and synchronize with the Publisher
-
With the subscription initialized, on the File menu, tap View Orders, and select a customer from the Customers list.
-
To update an existing order, tap on an existing order in the Orders table, and then do one of the following:
-
To update an existing item, double-tap an item in the Order Items grid, change the Quantity value for the selected item, and then tap Save.
-
To add a new item to the order, tap Add New, select the product from the Category and Product lists, set the order quantity in the Quantity field, and then tap Save.
-
To delete an existing item from an order, select the item in the Order Items grid, and then tap Delete.
-
Tap Synchronize from the Tools menu, select either Full (recommended) or Upload only from the Sync type list, and then tap Synchronize.
See Also