Step by Step: Program Microsoft SQL Server CE 2.0 Merge Replication Using .NET Compact Framework

Article
10/26/2006

Microsoft Corporation

February 2005

Applies to:
   Microsoft Visual Basic .NET
   Microsoft .NET Compact Framework
   Microsoft SQL Server
   Microsoft SQL Server CE
   Microsoft Visual Studio .NET 2003
   Windows Mobile-based Pocket PCs
   Microsoft Windows Powered Pocket PC 2002
   Microsoft ActiveSync 3.5 or later

Summary: In this exercise, you will use the .NET Compact Framework and SQL Server CE to configure merge replication, build an application user interface, subscribe to a SQL Server publication, modify and synchronize data, and resolve conflicts. (50 printed pages)

Download SQL ServerCE Replication .NET.exe from the Microsoft Download Center.

Introduction
Part 1: Setting Up and Configuring Your Computer
Part 2: Configuring Merge Replication
Part 3: Configuring IIS for SQL Server CE 2.0 Merge Replication
Part 4: Building the Application User Interface
Part 5: Subscribing to a Publication
Part 6: Modifying and Synchronizing Data
Part 7: Examining How Conflicts are Resolved
Summary

To complete this lab, you will need:

Microsoft Windows XP Professional
Microsoft Visual Studio .NET 2003 (this includes support for developing applications for the .NET Compact Framework and for SQL Server CE version 2.0)
SQL Server 2000 Developer Edition
SQL Server 2000 SP3 or later
SQL Server CE 2.0 Server Tools for SQL Server 2000 SP3

To test your application in a Pocket PC environment, you will need either a Pocket PC device that is connected to your development computer and is synchronized by means of Microsoft ActiveSync version 3.5 or later or a Pocket PC emulator that is included with the Pocket PC 2002 SDK or Visual Studio .NET 2003. If you decide to use a Pocket PC device, you must ensure that your device and desktop computer can communicate over your network connection by using HTTP (hypertext transfer protocol). Steps to check that the HTTP connection is configured correctly are included in Part 5: Subscribing to a Publication.

Introduction

In this exercise, you will learn how to build a Microsoft .NET Compact Framework application in Microsoft Visual Basic .NET to access replicated data from a Microsoft SQL Server database to a SQL Server CE database on a mobile device. The seven parts of this exercise will take you through the processes of:

Setting up and configuring your computer
Configuring merge replication
Configuring IIS for SQL Server CE 2.0 merge replication
Building the application user interface
Subscribing to a publication
Modifying and synchronizing data
Examining how conflicts are resolved

You will learn how to build an application to run on a mobile device to use information from a central SQL Server database, and to update the local changes to the main database at appropriate times.

For this exercise to work on your computer, be sure to follow the procedures in the Setup and Configuration section of this exercise.

Part 1: Setting Up and Configuring Your Computer

For this exercise, you may change the computer name, IP address, and SQL Server name to your own settings, but then you must remember to change the relevant sections during the exercise. The instructions throughout the exercise assume that your desktop computer is called TWEEDLEDEE; the IP address of your desktop computer is 192.168.1.30; and the SQL Server instance is called TWEEDLEDEE\TWEEDLEDEE.

In this part of the exercise, you will perform the following tasks:

Install and configure Windows XP Professional
Install Visual Studio .NET 2003
Install and configure SQL Server 2000 Developer Edition
Install SQL Server 2000 Service Pack 3 or later
Install SQL Server CE 2.0 Server Tools
Install and configure ActiveSync
Prepare for the exercise

To install and configure Windows XP Professional

Perform a default installation of Microsoft Windows XP Professional, specifying the computer's name as TWEEDLEDEE.
After installation is complete, use the Add or Remove Programs option in Control Panel to install Internet Information Services.

To install Visual Studio .NET 2003

Perform a default installation of Visual Studio .NET 2003.
Add a shortcut to Visual Studio .NET 2003 to your desktop.

To install and configure SQL Server 2000 Developer Edition

Perform a default installation of SQL Server, except for the following options:
- On the Installation Definition page, select Server and Client Tools.
- On the Instance Name page, clear the Default option
- On the Instance Name page in the Instance Name box, type TWEEDLEDEE.
- On the Service Account page in the Service Settings section, select Use a Domain User Account, and then type the details for your computer Administrator account.
- On the Authentication Mode page, select Mixed Mode. Type a suitable password for the sa account. It is not recommended to select the Blank Password option.
Add a shortcut for the SQL Server Enterprise Manager to your desktop.

To install SQL Server 2000 Service Pack 3 or later

Download the latest service pack for SQL Server 2000. Service Pack 3 or later is required to protect against virus infections, such as the "Slammer" worm.
Follow the instructions included in the service pack to apply the update to your SQL Server 2000 installation.

To install SQL Server CE 2.0 Server Tools

Download the latest update of the SQL Server CE 2.0 Server Tools. If you have installed a later service pack for SQL Server 2000 rather than Service Pack 3, check the Web site for the update to the SQL Server CE 2.0 Server Tools to match the service pack you have installed.
Double-click the download file sqlce20sql2ksp3 to start the installation.
Accept all of the default options for the installation.
When the product is installed, do not run the SQL Server CE Connectivity Wizard.
Reboot the computer if prompted.

To install and configure ActiveSync

Download the latest version of ActiveSync.
Perform a default installation of ActiveSync.
If you are planning to use a Pocket PC device for testing (and not a Pocket PC emulator), then you must:
- Ensure that your Pocket PC is connected to your desktop computer.
- Start ActiveSync on the desktop computer. The Get Connected Wizard will appear. Set up a partnership with the Pocket PC by using the default synchronization settings.

To prepare for the exercise

Before starting the exercise, copy the exercise files supplied into a folder called C:\Labs on the desktop computer.

Part 2: Configuring Merge Replication

In part one of this exercise, you installed support for replication from SQL Server 2000 that is running on the desktop computer to SQL Server CE that is running on a mobile device. The objective of this part of the exercise is to create a network share on a folder to hold the replication snapshot files, and then create a publication comprising data from the products table in the Northwind database.

In this part of the exercise, you will perform the following tasks:

Configure the database login
Create and share the Snapshot folder
Create the publication
Grant access to the publication

Some illustrations in this part of the exercise are thumbnails. You can click the thumbnails for larger images.

To configure the database login

Start SQL Server Enterprise Manager.

Note Throughout this exercise, the desktop computer is called TWEEDLEDEE, and the local SQL Server instance is called TWEEDLEDEE\TWEEDLEDEE. If you have chosen to use different names for your computer, ensure you use the appropriate names for your environment.
Connect to TWEEDLEDEE\TWEEDLEDEE in the SQL Server group.
Create a new login account for TWEEDLEDEE\IUSR_TWEEDLEDEE (the anonymous Internet access account) by configuring the following options on the General tab, as shown in the following figure:
- Select Windows Authentication.
- Specify the Domain as TWEEDLEDEE.
- Under Security access, select Grant access.
- Under Defaults, set the Database to Northwind.
On the Database Access tab, grant the user IUSR_TWEEDLEDE access to the Northwind database, as shown in the following illustration.
Click OK.

To create and share the Snapshot folder

Open Windows Explorer, and then navigate to the C:\Labs folder.
Create a new folder in C:\Labs called Snapshot.
Right-click the Snapshot folder, and then select Sharing and Security from the menu. The Sharing tab in the Snapshot Properties dialog box appears, as shown in the following illustration.
Select Share this folder.
In the Share name box, type NorthwindSnapshot.
Click Permissions.
Ensure that Everyone is listed under Group or user names, and ensure that this group has Change and Read permissions, as shown in the following illustration.
Click OK, and then click OK again to close the Snapshot Properties dialog box.

To create the publication

Start SQL Server Enterprise Manager (by using the icon on the desktop).
Expand Microsoft SQL Servers, and then expand SQL Server Group to display the local SQL Server instance.
Expand the local SQL Server instance (TWEEDLEDEE\TWEEDLEDEE), and then select Replication.
From the Action menu, point to New, and then select Publication. The Create Publication Wizard appears, as shown in the following illustration.
Click Next.
On the Select Distributor page, select Make TWEEDLEDEE\TWEEDLEDEE its own Distributor; SQL Server will create a distribution database and a log, and then click Next, as shown in the following illustration.
On the Configure SQL Server Agent page, select Yes, configure the SQL Server Agent service to start automatically, and then click Next, as shown in the following illustration.
On the Specify Snapshot Folder page, type the path to the network share you created at the beginning of this exercise, which is \\TWEEDLEDEE\NorthwindSnapshot, and then click Next, as shown in the following illustration.
On the Choose Publication Database page, select the Northwind database, and then click Next, as shown in the following illustration.
On the Select Publication Type page, select Merge publication, and then click Next, as shown in the following illustration.
On the Specify Subscriber Types page, select Devices running SQL Server CE, and then clear the other check boxes, as shown in the following illustration. Click Next.
On the Specify Articles page, select Show adjacent to the Tables object type. Select the Products object in the list on the right side, as shown in the following illustration. Click Next.
On the Article Issues page, accept the default options by clicking Next, as shown in the following illustration.
On the Select Publication Name and Description page, in the Publication name box, type NorthwindProducts, as shown in the following illustration. Click Next.
On the Customize the Properties of the Publication page, select No, create the publication as specified, as shown in the following illustration. Click Next.
On the Completing the Create Publication Wizard page, click Finish, as shown in the following illustration. The new publication will be created (a window will appear displaying the progress of the various tasks being performed).
After the publication has been created, a dialog box may appear warning you that the Products table contains references to other tables that are not part of the publication. If this dialog box appears, click Close to acknowledge the warning. Another dialog box may appear informing you that the Replication Monitor has been added to the console tree inside SQL Server Enterprise Manager. Click Close to acknowledge this dialog box.

To grant access to the publication

In the SQL Server Enterprise Manager console tree, expand Replication Monitor, expand Agents, and then select Merge Agents, as shown in the following illustration. This page will display all the active merge agents involved in replicating data to subscribers. This page should be empty because no subscribers have been configured.

The application running on the mobile device will access SQL Server as an anonymous Internet user by means of HTTP. You have granted permission to the TWEEDLEDEE\IUSR_TWEEDLEDEE account to use the Northwind database, but you must grant this account access to the NorthwindProducts publication.
In the SQL Server Enterprise Manager console tree, expand TWEEDLEDEE\TWEEDLEDEE, expand Replication, and then expand Publications.
Select the NorthwindProducts.
On the Action menu, choose Properties. The Publication Properties dialog box appears, as shown in the following illustration.
Click the Publication Access List tab. Click Add to add the user TWEEDLEDEE\IUSR_TWEEDLEDEE to the list, as shown in the following illustration. Click OK.

Part 3: Configuring IIS for SQL Server CE 2.0 Merge Replication

In this part of the exercise, you will configure Internet Information Services (IIS) to support access from mobile clients running SQL Server CE 2.0 for merge replication.

Some illustrations in this part of the exercise are thumbnails. You can click the thumbnails for larger images.

To configure IIS to support access from mobile clients

Click Start, All Programs, Microsoft SQL Server CE 2.0, and then click Configure Connectivity Support in IIS.
Double-click Create a Virtual Directory, as shown in the following illustration.

The SQL Server CE Virtual Directory Creation wizard displays. It summarizes the steps it will take you through. Prior to SQL Server CE 2.0, users had to manually perform these configuration steps.
On the Welcome page, click Next, as shown in the following illustration.
On the Virtual Directory Alias and Content Folder page, type Northwind as the alias, and then type C:\Labs\NorthwindTempFiles as the folder and location for temporary files (the wizard will create this directory for you, and copy in the required files), as shown in following illustration. Click Next.
When the wizard asks you if it should create the directory, click Yes.
When the wizard asks you if it should copy and register the SQL Server CE Agent, click Yes, as shown in the following illustration.
On the Virtual Directory Authentication page, select Anonymous access. Click Edit, and then specify the username of IUSR_TWEEDLEDEE.
On the NTFS Access Permissions: Computer running SQL Server page, select One or more applications requiring SQL Server merge replication.
On the NTFS Access Permissions: Snapshot folder page, type the name of the network share you set up in the previous exercise: \\TWEEDLEDEE\NorthwindSnapshot. Click OK to accept the changes.

Part 4: Building the Application User Interface

You will create the mobile application and lay out the user interface.

In this part of the exercise, you will perform the following tasks:

Create the application
Create the user interface
Set the startup object

Some illustrations in this part of the exercise are thumbnails. You can click the thumbnails for larger images.

To create the application

Start Visual Studio .NET.
On the File menu, click New Project. The New Project dialog box appears, as shown in the following illustration.
Under Project Types, select Visual Basic Projects.
Under Templates, select Smart Device Application.
In the Name box, type Mobile Products.
In the Location box, type C:\Labs, and then click OK.
After the Smart Device Application Wizard appears, select Pocket PC as the platform, and Windows Application as the project type, as shown in the following illustration. Click OK.

A default form called Form1 will appear in the Designer window, containing a MainMenu control called MainMenu1.

To create the user interface

In the Properties window (if the Properties window is not displayed, on the View menu, click Properties Window), change the Text property of Form1 to NextFin136, and then change the Name property to ProductForm.
In the Solution Explorer (if the Solution Explorer window is not displayed, on the View menu, click Solution Explorer), select Form1.vb.
In the Properties window, change the File Name to ProductForm.vb, as shown in the following illustration.
Using the Toolbox (if the Toolbox is not displayed, on the View menu, click Toolbox), click and drag six Button controls, three Label controls, and a NumericUpDown control onto ProductForm.
Select each control one by one, and set its properties by using the values specified in the following table.

Control	Property	Value
Button1	Name Enabled Location Size Text	CreateButton True 8, 48 112, 20 Create Database
Button2	Name Enabled Location Size Text	SynchronizeButton True 8, 80 112, 20 Synchronize
Button3	Name Enabled Location Size Text	UpdateButton False 144, 80 56, 20 Update
Button4	Name Enabled Location Size Text	FetchButton True 8, 184 48, 20 Fetch
Button5	Name Enabled Location Size Text	NextButton False 72, 184 48, 20 Next
Button6	Name Enabled Location Size Text	FinishButton False 136, 184 48, 20 Finish
Label1	Font Location Size Text	Arial, 18pt, style=Bold, Italic 0, 8 232, 32 Northwind Traders
Label2	Name Location Size Text	ProductName 8, 120 216, 20 leave blank
Label3	Location Size Text	8, 152 88, 20 In Stock:
NumericUpDown1	Name Location Maximum Minimum Size Value	InStock 104,152 5000 0 64, 20 0

The completed form should appear as shown in the following illustration.

To set the startup object

In the Solution Explorer, select MobileProducts.
On the Project menu, click Properties to display the MobileProducts Property Pages dialog box, as shown in the following illustration.
Under Common Properties, select General.
Under Startup object, select ProductForm, and then click OK.
On the Build menu, click Build MobileProducts. The project should compile without any errors.

Part 5: Subscribing to a Publication

In the MobileProducts application, when the user clicks Create Database, the application creates a local SQL Server CE database on the device, subscribes to the NorthwindProducts publication, and synchronizes with the SQL Server database on the desktop computer. In this part of the exercise, you will write the Visual Basic .NET code that achieves these actions, and you will examine the database that is created.

You will perform the following tasks in this part of the exercise:

Reference the SQLServerCE assembly
Write the code that subscribes to the publication
Build and test the code
View the replicated database
View the merge agent in SQL Server Enterprise Manager

Some illustrations in this part of the exercise are thumbnails. You can click the thumbnails for larger images.

To reference the SQLServerCE assembly

Return to the MobileProducts application you started building in part four of this exercise.
On the Project menu, choose Add Reference.
In the Add Reference dialog box, double-click the System.Data.SqlServerCe assembly, as shown in the following illustration. This assembly contains the classes and types needed to access a SQL Server CE database. Click OK.

To write the code that subscribes to the publication

On the View menu, click Code display the code for the application.
Add the following Imports statements at the top of the file above the definition of the ProductForm class. Only type the code shown in bold — the other code is for your reference. You will be referring to objects in these namespaces when you write the Visual Basic .NET code for the application. (If you do not want to type this code manually, you can copy and paste it from the file C:\Labs\CodeFiles\Imports.txt.)
```
Imports System.IO
Imports System.Net
Imports System.Data
Imports System.Data.SqlServerCe

Public Class ProductForm
    Inherits System.Windows.Forms.Form
...
```

Add the following constants and variables to the ProductForm class before the CreateButton variable. These items will be used when connecting to the SQL Server database on the desktop computer. Only type the code shown in bold. (This code is available in the file C:\Labs\CodeFiles\Variables.txt.)

Note If your desktop computer has an IP address different to the recommended address (192.168.1.30), be sure to change the appropriate line of code. If your computer name or SQL Server name is not TWEEDLEDEE, make the appropriate changes to the code.

...
Public Class ProductForm
    Inherits System.Windows.Forms.Form

    ' Replication parameters
    Private Const INTERNETURL As String = _ 
            "https://192.168.1.30/Northwind/sscesa20.dll"
    Private Const PUBLISHERSERVER As String = _
            "TWEEDLEDEE"
    Private Const PUBLISHERDATABASE As String = _
            "Northwind"
    Private Const PUBLISHERSECURITYMODE As SecurityType =_
            SecurityType.NTAuthentication
    Private Const PUBLICATION As String = _ 
            "NorthwindProducts"
    Private SUBSCRIBER As String = Dns.GetHostName()
    Private Const LOCALDATABASE As String = _
            "\My Documents\products.sdf"
    Private Const CONNECTIONSTRING As String = _
            "data source=" & LOCALDATABASE

    Friend WithEvents CreateButton As _ 
                System.Windows.Forms.Button
    ...

Add the following subroutine to the end of the ProductForm class but immediately before the End Class statement. Only type the code shown in bold. (This code is available in the file C:\Labs\CodeFiles\ReplicateData.txt.)
```
...
    Private Sub ReplicateData()
        Dim replicator As New SqlCeReplication()
        replicator.InternetUrl = INTERNETURL
        replicator.Publisher = PUBLISHERSERVER
        replicator.PublisherDatabase = PUBLISHERDATABASE
        replicator.PublisherSecurityMode = _ 
                    PUBLISHERSECURITYMODE
        replicator.Publication = PUBLICATION
        replicator.Subscriber = SUBSCRIBER
        replicator.SubscriberConnectionString = _
                    CONNECTIONSTRING
        replicator.Synchronize()
    End Sub
End Class
```
The SqlCeReplication class contains methods and properties that a mobile application can use to subscribe to a SQL Server publication. The ReplicateData method you have just created instantiates a SqlceReplication object and sets its properties to subscribe to the NorthwindProducts publication you defined in part four. These properties are described in the following table. After the properties have been set, the Synchronize method copies the data from the publication to a local database on the mobile device.

Property	Description
InternetURL	SQL Server CE communicates with a SQL Server Agent (sscesa20.dll) running on the desktop computer by means of HTTP. IIS on the desktop computer has been configured to accept SQL Server CE requests and forward them to SQL Server by using the SQL Server Agent. The InternetURL property specifies the URL that has been configured for this purpose. In this exercise, the value is https://192.168.1.30 /Northwind/sscesa20.dll. If your desktop computer is using a different IP address, use that address instead of 192.168.1.30.
Publisher	The Publisher property identifies the instance of SQL Server publishing the publication in the form machineName\SQLServerInstanceName. In this exercise, the value is TWEEDLEDEE\TWEEDLEDEE.
PublisherDatabase	The PublisherDatabase property identifies the database containing the publication. In this exercise, the value is Northwind.
PublisherSecurityMode	The PublisherSecurityMode property determines how the subscribing application is authenticated by the publishing server. SQL Server supports database authentication (when an explicit PublisherLogin and PublisherPassword must be supplied), and NT authentication, which uses the integrated security features of the operating system and SQL Server. In this exercise, SQL Server has been configured to use Windows authentication. The mobile application will connect to the SQL Server through IIS as the anonymous Internet user (IUSR_TWEEDLEDEE). You granted access to this user to the database and publication in part three.
Publication	The Publication property is the name of the publication you created in part three, which was NorthwindProducts.
Subscriber	The Subscriber property identifies the subscribing device or computer. It is conventional to use the name of the device or computer. This exercise uses the value returned by the Dns.GetHostName method.
SubscriberConnectionString	The SubscriberConnectionString property specifies the connection string for accessing the local SQL Server CE database. When the subscription is synchronized with the publication, a copy of the published data will be taken and stored in the database specified. In this exercise, the local SQL Server CE database is called products.sdf, which is in the My Documents folder. (SQL Server CE databases are conventionally given the file extension .sdf)

On the View menu, click Designer.
Double-click the Create Database button. This action will create an event method called CreateButton_Click. The view will switch back to the Code view.

Add the following code to the CreateButton_Click method. (This code is available in the file C:\Labs\CodeFiles\CreateButton_Click.txt.)

Private Sub CreateButton_Click(_
                            ByVal sender As System.Object, _
                            ByVal e As System.EventArgs) _
                            Handles CreateButton.Click
    Cursor.Current = Cursors.WaitCursor
    Try
        Dim engine As SqlCeEngine = _
                        New SqlCeEngine(CONNECTIONSTRING)
        If File.Exists(LOCALDATABASE) Then
            File.Delete(LOCALDATABASE)
        End If
        engine.CreateDatabase()
        ReplicateData()
        Cursor.Current = Cursors.Default
        MessageBox.Show("Replica database created", _
                            "Replicated", _ 
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.None, _ 
                            MessageBoxDefaultButton.Button1)

    Catch ex As SqlCeException
        Cursor.Current = Cursors.Default
        Dim errSQL As SqlCeError
        For Each errSQL In ex.Errors
            MessageBox.Show(errSQL.Message, _
                            "Error subscribing to database", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Exclamation, _
                            MessageBoxDefaultButton.Button1)
        Next
    End Try
End Sub

The CreateButton_Click method creates a local database called "\My Documents\Products.sdf" by using the CreateDatabase method of the SqlCeEngine class (the code deletes the database if it already exists). After the database has been created, the ReplicateData method populates it with data from the NorthwindProducts publication.

The Catch block handles any errors that occur. If a database exception is reported, the SqlCeException object that is thrown contains a list of errors in its Errors collection. (There may be several errors; often the last one reported is the most significant and is the cause of the others.) The Catch block iterates through the Errors collection displaying the message associated with each one in turn.

To build and test the code

On the Build menu, click Build MobileProducts. The project should compile without any errors. (You should go back and check your typing if this is not the case.)
Before proceeding any further, ensure that the Pocket PC is currently connected to the desktop computer. On the Pocket PC, tap Start, tap Programs, and then tap ActiveSync. If the Pocket PC is not currently connected, tap the Sync button.

Note If you are using a Pocket PC and not an emulator, ensure that the device is connected by ActiveSync to your desktop computer.
To verify that the Pocket PC or Pocket PC emulator can communicate with the desktop computer by means of HTTP, use Internet Explorer to browse to https://192.168.1.30/northwind/sscesa20.dll on the Pocket PC (or substitute the IP address of your desktop computer if it's not 192.168.1.30). If the message SQL Server CE Server Agent appears, then the communication has been successful and the SQL Server Agent is correctly configured.
In Visual Studio .NET 2003 on the desktop computer on the Debug menu, click Start to deploy and run the MobileProducts application on the mobile device.
Deploy the application to the Pocket PC Device, as shown in the following illustration.
When the application starts running on the device, tap Create Database. The cursor will change while the application creates the local database and subscribes to the NorthwindProducts subscription, which copies the data to the local database. When the operation is completed, you will see a message box, as shown in the following illustration. (The images show the program running on the Pocket PC emulator. The real Pocket PC should be very similar.)
Tap OK to acknowledge the message.
In Visual Studio .NET 2003, click Debug, and then click Stop Debugging. This closes the application running on the Pocket PC. (None of the other buttons in the application will do anything yet).

Note Do not use the Close button on the menu bar of the application to close the application. On Pocket PC devices, the Close button minimizes the application to the background — it does not close the application. When you use Visual Studio .NET 2003 to stop debugging, the application closes.

To view the replicated database

On the Pocket PC (or emulator), open File Explorer by tapping Start, Programs, and then tapping File Explorer).
Examine the contents of the My Documents folder. You will find the Products database, as shown in the following illustration.
On the Pocket PC, tap Start, and then tap SQLCE Query to start the SQL Server CE Query Analyzer tool. You can use this tool to examine SQL Server CE databases held on the Pocket PC. To open the Products database, click the storage icon, as shown in the following illustration.
When Connect to SQL Server CE dialog box appears, click the ellipsis adjacent to the Path box, as shown in the following illustration.
When a list shows the databases on the Pocket PC appears, tap the Products database, as shown in the following illustration.
When the Path box in the Connect to SQL Server CE dialog box contains the file name of the Products database, tap Connect to open the database, as shown in the following illustration.
In the Query Analyzer, expand the Databases folder. You will now see the Products database.
Expand the \My Documents\products.sdf database to see the Tables folder.
Expand the Tables folder to see all of the tables in the database.
Expand the Products table to display the Columns and Indexes folders.
Expand the Columns folder to display all of the columns in the Products table.
Select the Products table, and then tap the green Execute icon, as shown in the following illustration.
Browse the data in the grid that appears and displays the data in the Products table, as shown in the following illustration.
On the Tools menu, tap Exit to close the Query Analyzer when you have finished browsing the data.

Note It is important to close the Products database and exit Query Analyzer because SQL Server CE databases do not support multiple simultaneous connections, and you will not be able to perform the next exercises.

To view the merge agent in SQL Server Enterprise Manager

On the desktop computer, return to SQL Server Enterprise Manager.
Expand Replication Monitor, and then expand Agents.
Select Merge Agents, as shown in the following illustration. A new agent has been created to support the subscription made by the Pocket PC device.

Part 6: Modifying and Synchronizing Data

In the previous part of this exercise, you created a replica of the Products table in a local database on the Pocket PC. In this part of the exercise, you will add code to the MobileProducts application to allow the user to modify the number of each product item in stock, and then propagate these changes back to the SQL Server database that is running on the desktop computer.

You will perform the following tasks in this part of the exercise:

Retrieve data from the database
Update the database
Synchronize the database
Test the code
Verify the changes are synchronized to the desktop computer
Synchronize changes from the desktop computer to the mobile application

Some illustrations in this part of the exercise are thumbnails. You can click the thumbnails for larger images.

To retrieve data from the database

In Visual Studio .NET 2003, open the MobileProducts application.
On the View menu, click Code to display the code for the application.
After the CreateButton_Click subroutine and before the end of the ProductForm class, add the following variable definitions. These variables will be used when fetching data from the database and displaying it in the form. (This code is available in the file C:\Labs\CodeFiles\DataVariables.txt.)
```
' Variables used when fetching and displaying data
    Private ceConn As SqlCeConnection = Nothing
    Private ceReader As SqlCeDataReader = Nothing
    Private productId As Int32 
End Class
```
On the View menu, click Designer, and then double-click the Fetch button. This action creates an event method called FetchButton_Click and switches back to code view.

Add the following code to the FetchButton_Click method. (This code is available in the file C:\Labs\CodeFiles\FetchButton_Click.txt.)

Private Sub FetchButton_Click(
                            ByVal sender As System.Object, _
                            ByVal e As System.EventArgs) _ 
                            Handles FetchButton.Click

        Cursor.Current = Cursors.WaitCursor
        Try
            ceConn = New SqlCeConnection(CONNECTIONSTRING)
            ceConn.Open()
            Dim ceCmd As SqlCeCommand = New SqlCeCommand(_
                    "SELECT ProductID, ProductName, "_
                    & "UnitsInStock FROM Products " _
                    & "ORDER BY ProductID", ceConn)

            ceReader = ceCmd.ExecuteReader(_
                        CommandBehavior.CloseConnection)

                If ceReader.Read() Then
                productId = ceReader.GetInt32(0)
                ProductName.Text = ceReader.GetString(1)
                InStock.Value = ceReader.GetInt16(2)
                FetchButton.Enabled = False
                CreateButton.Enabled = False
                SynchronizeButton.Enabled = False
                NextButton.Enabled = True
                FinishButton.Enabled = True
                UpdateButton.Enabled = True
                Cursor.Current = Cursors.Default
            Else
                Cursor.Current = Cursors.Default
                MessageBox.Show("No products found", _
                            "No Products", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Exclamation, _
                            MessageBoxDefaultButton.Button1)
            End If

        Catch ex As SqlCeException
            Cursor.Current = Cursors.Default
            Dim errSQL As SqlCeError
            For Each errSQL In ex.Errors
                MessageBox.Show(errSQL.Message, _
                        "Error fetching data", _
                        MessageBoxButtons.OK, _
                        MessageBoxIcon.Exclamation, _
                        MessageBoxDefaultButton.Button1)
            Next
        End Try
    End Sub

The FetchButton_Click subroutine opens a connection to the local SQL Server CE database holding the replicated data. The subroutine then creates a SqlCeCommand to retrieve the ProductID, ProductName, and UnitsInStock columns from the Products table. The subroutine opens a SqlCeDataReader over the result set generated by this command and fetches the first row.

The product name is displayed in the ProductName label, and the number of units in stock is placed in the InStock NumericUpDown control. The subroutine also enables and disables the various buttons on the form, which controls the functionality that the user can invoke while the data reader is open and connected to the database.

If the result set is empty (the Read method of the SqlCeDataReader class returns False if there is no more data available), the subroutine displays a message and does not enable/disable the various buttons on the form. If an exception occurs, the Catch handler uses the technique shown earlier to display all the associated errors.

On the View menu, click Designer, and then double-click the Next button. This action creates an event method called NextButton_Click and switches back to code view.

Add the following code to the NextButton_Click method. (This code is available in the file C:\Labs\CodeFiles\NextButton_Click.txt.)

Private Sub NextButton_Click( _
                            ByVal sender As System.Object, _
                            ByVal e As System.EventArgs) _
                            Handles NextButton.Click

        Cursor.Current = Cursors.WaitCursor
        Try
            If ceReader.Read() Then
                productId = ceReader.GetInt32(0)
                ProductName.Text = ceReader.GetString(1)
                InStock.Value = ceReader.GetInt16(2)
                Cursor.Current = Cursors.Default
            Else
                Cursor.Current = Cursors.Default
                MessageBox.Show( _
                            "No more products available", _
                            "No More Products", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Exclamation, _
                            MessageBoxDefaultButton.Button1)
            End If

        Catch ex As SqlCeException
            Cursor.Current = Cursors.Default
            Dim errSQL As SqlCeError
            For Each errSQL In ex.Errors
                MessageBox.Show(errSQL.Message, _
                            "Error fetching data", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Exclamation, _
                            MessageBoxDefaultButton.Button1)
            Next
        End Try
    End Sub

The Next button is enabled by the FetchButton_Click method. The purpose of the Next button is to advance the data reader opened in the FetchButton_Click subroutine to the next row and display the data on the form. The NextButton_Click subroutine reads the next row from the data reader. If there is another row, the results are displayed; if not, a message is displayed.

To update the database

On the View menu, click Designer, and then double-click the Update button. This creates an event method called UpdateButton_Click and switches back to code view.

Add the following code to the UpdateButton_Click method. (This code is available in the file C:\Labs\CodeFiles\UpdateButton_Click.txt.)

Private Sub UpdateButton_Click( _
                        ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) _
                        Handles UpdateButton.Click

        Cursor.Current = Cursors.WaitCursor
        Try
            Dim ceCmd As SqlCeCommand = _
                    New SqlCeCommand(_
                    "UPDATE Products SET UnitsInStock = " & _
                    InStock.Text & " WHERE ProductID = " & _
                    productId, ceConn)
            ceCmd.ExecuteNonQuery()
            Cursor.Current = Cursors.Default
            MessageBox.Show("Database updated", _
                            "Updated", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.None, _
                            MessageBoxDefaultButton.Button1)

        Catch ex As SqlCeException
            Cursor.Current = Cursors.Default
            Dim errSQL As SqlCeError
            For Each errSQL In ex.Errors
                MessageBox.Show(errSQL.Message, _
                        "Error updating local database", _
                        MessageBoxButtons.OK, _
                        MessageBoxIcon.Exclamation, _
                        MessageBoxDefaultButton.Button1)
            Next
        End Try
    End Sub

The user can change the quantity in stock for the currently displayed product. Clicking the Update button causes the change to be recorded in the local SQL Server CE database. The UpdateButton_Click subroutine executes an SQL UPDATE statement (by using the same connection used to fetch the data) to modify the row in the database.

On the View menu, click Designer, and then double-click the Finish button. This action creates an event method called FinishButton_Click and switches back to code view.

Add the following code to the FinishButton_Click method. (This code is available in the file C:\Labs\CodeFiles\FinishButton_Click.txt.)

Private Sub FinishButton_Click( _
                        ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) _
                        Handles FinishButton.Click

        Cursor.Current = Cursors.WaitCursor
        Try
            ProductName.Text = ""
            InStock.Value = 0
            ceReader.Close()
            FetchButton.Enabled = True
            CreateButton.Enabled = True
            SynchronizeButton.Enabled = True
            NextButton.Enabled = False
            FinishButton.Enabled = False
            UpdateButton.Enabled = False
            Cursor.Current = Cursors.Default
            MessageBox.Show( _
                        "Database connection closed", _
                        "Closed", _
                        MessageBoxButtons.OK, _
                        MessageBoxIcon.None, _
                        MessageBoxDefaultButton.Button1)

        Catch ex As SqlCeException
            Cursor.Current = Cursors.Default
            Dim errSQL As SqlCeError
            For Each errSQL In ex.Errors
                MessageBox.Show(errSQL.Message, _
                            "Error closing connection", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Exclamation, _
                            MessageBoxDefaultButton.Button1)
            Next
        End Try
    End Sub

Users click the Finish button after they have finished browsing products and updating stock levels. The FinishButton_Click method simply re-enables and disables buttons on the form as appropriate, clears the data displayed on the form, and closes the connection to the local SQL Server CE database.

To synchronize the databases

On the View menu, click Designer, and then double-click the Synchronize button. This action creates an event method called SynchronizeButton_Click and switches back to code view.

Add the following code to the SynchronizeButton_Click method. (This code is available in the file C:\Labs\CodeFiles\SynchronizeButton_Click.txt.)

Private Sub SynchronizeButton_Click( _
                        ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) _
                        Handles SynchronizeButton.Click

        Cursor.Current = Cursors.WaitCursor
        Try
            ReplicateData()
            Cursor.Current = Cursors.Default
            MessageBox.Show("Databases synchronized", _
                            "Synchronized", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.None, _
                            MessageBoxDefaultButton.Button1)

        Catch ex As SqlCeException
            Cursor.Current = Cursors.Default
            Dim errSQL As SqlCeError
            For Each errSQL In ex.Errors
                MessageBox.Show(errSQL.Message, _
                "Error synchronizing with server database", _
                MessageBoxButtons.OK, _
                MessageBoxIcon.Exclamation, _
                MessageBoxDefaultButton.Button1)
            Next
        End Try
    End Sub

Users click the Synchronize button to synchronize the local SQL Server CE database with the database located on the desktop computer. Any changes made locally are sent back to the desktop computer, and any changes made to the desktop computer's database since the last synchronization are propagated back to the SQL Server CE database. The SynchronizeButton_Click achieves this action just by calling the ReplicateData method you previously wrote (the same method was used when creating the initial subscription).

Note A completed version of the application is available in the C:\Labs\CompletedMobileProducts folder on the desktop computer.

To test the code

On the Build menu, click Build MobileProducts. The application should compile without any errors.
On the Debug menu, click Start to deploy and run the application on the Pocket PC.
When the Products In Stock form appears on the Pocket PC, tap Fetch. (You don't need to use Create Database because the database should still exist on the Pocket PC. If you do, you will create a second subscription to the same publication.)
When you see the first product (Chai) appear in addition to the volume in stock, make a note of this value (39). You will also notice that the Next, Update, and Finish buttons have been enabled.
Tap Next to move to the second product (Chang).
Change the volume In Stock to 50, and then tap Update. You will see a message indicating that the local database has been updated, as shown in the following illustration.
Click OK to acknowledge the message.
Tap Next and move to the next row (Aniseed Syrup). Change the volume In Stock to 0, and then tap Update.
Tap Finish. A message appears indicating that the database connection has been closed. The Create Database, Synchronize, and Fetch buttons are re-enabled.
To send the changes back to the SQL Server database running on the desktop computer, tap Synchronize. After a short delay, a message indicates that the databases have been successfully synchronized, as shown in the following illustration.

To verify the changes are synchronized to the desktop computer

Keep the mobile application running from the previous task, and then return to SQL Server Enterprise Manager that is open on the desktop computer.
Under the Replication Monitor folder, expand Agents, and then select Merge Agents.
On the Action menu, choose Refresh to update the display.
Scroll the Merge Agents details pane to the right to display the Last Action column. You will see a message indicating that the most recent merge operation performed two updates, as shown in the following illustration.
On the Tools menu, choose SQL Query Analyzer.

In the Query window, type the following SQL statement.

SELECT ProductID, ProductName, UnitsInStock
FROM Northwind..Products
ORDER BY ProductID

Press F5 to run the query. The results are displayed, as shown in the following illustration. Note that the values for UnitsInStock for the second and third products have been changed to reflect the values that you specified when running the mobile application.

To synchronize changes from the desktop computer to the mobile application

Overwrite the SELECT statement in the Query window with the following UPDATE statement. This action increases the volume in stock of every product.
```
UPDATE Northwind..Products
SET UnitsInStock = UnitsInStock + 10
```
Press F5 to perform the update. (It modifies 77 rows.)
Return to the mobile application that is running on the Pocket PC. Tap the Synchronize button.
When synchronization has competed, tap Fetch, and then note the volume in stock of the first product (Chai). Previously it was 39, but now displays 49 because it was updated in the SQL Server database on the desktop computer, as shown in the following illustration.

Note that you can also push changes from the desktop computer to the Pocket PC device by manually starting the merge agent from SQL Server Enterprise Manager. This method only works when the Pocket PC is connected to the network though.

Part 7: Examining How Conflicts are Resolved

In the real world, there could be any number of mobile devices subscribing to the same publication. The applications running on these devices could change any row in the local database and subsequently attempt to synchronize with the desktop SQL Server database. In this environment, it is possible that at some point two copies of the same data will be modified with different values. SQL Server merge replication detects this situation automatically and handles it.

In this part of the exercise, you will perform the following tasks:

Create a conflict
View the conflict and its details

Some illustrations in this part of the exercise are thumbnails. You can click the thumbnails for larger images.

To create a conflict

On the Pocket PC, for Chai (the product currently displayed), change the volume In Stock to 10, and then tap Update.
On the desktop computer, return to the SQL Query Analyzer.
In the Query window, type the following UPDATE statement, and then press F5 to run the application. This statement changes the volume in stock of Chai to 100. (You will see a message indicating that only one row was affected.)
```
UPDATE Northwind..Products
SET UnitsInStock = 100
WHERE ProductID = 1
```
On the Pocket PC, tap Finish, and then tap Synchronize.
When the synchronization has completed, a message appears. Tap OK to acknowledge the message.
Tap Fetch to retrieve and display the first row from the Products table (Chai). The volume in stock will be 100 and not 10.

To view the conflict and its details

On the desktop computer, return to the SQL Server Enterprise Manager that is running.
Select the Merge Agents folder (under Replication Monitor and Agents).
On the Action menu, click Refresh to update the display again.
Scroll the Merge Agent details pane to the right to display the Publisher Conflicts column. The value in this column indicates that one conflict was detected and resolved during the most recent merge operation, as shown in the following illustration.
In SQL Server Enterprise Manager, expand Databases.
Expand the Northwind folder.
Expand Publications, and then select the NorthwindProducts publication.
On the Action menu, click View Conflicts. The Microsoft Replication Conflict Viewer dialog box appears, as shown in the following illustration.
Click the View button to display the details of the conflict and how it was resolved, as shown in the following illustration.

By default, the Conflict Resolver keeps the most recent update. You can override the result by clicking Resolve With This Data.
Click Resolve With This Data to select the change made by the mobile application running on the Pocket PC.
Click Close to close the Microsoft Replication Conflict Viewer.
Return to the Pocket PC displaying the volume in stock for Chai. Tap Finish, and then tap Synchronize again.
Tap Fetch. This time the volume in stock will be displayed as 10.
Tap Finish, and then use Visual Studio .NET 2003 to finish debugging and thus close the mobile application.

Summary

In this exercise, you performed the following:

Set up and configured your computer
Configured merge replication
Configured IIS for SQL Server CE 2.0 merge replication
Built the application user interface
Subscribed to a publication
Modified and synchronized data
Examined how conflicts are resolved

You can find more information at the following:

Step by Step: Program Microsoft SQL Server CE 2.0 Merge Replication Using .NET Compact Framework

Contents

To complete this lab, you will need:

Introduction

Part 1: Setting Up and Configuring Your Computer

Part 2: Configuring Merge Replication

Part 3: Configuring IIS for SQL Server CE 2.0 Merge Replication

Part 4: Building the Application User Interface

Part 5: Subscribing to a Publication

Part 6: Modifying and Synchronizing Data

Part 7: Examining How Conflicts are Resolved

Summary

Additional resources