Development | .NET Compact Framework

Using replication on a SQL Server Database
Written by Pete Vickers  [author's bio]  [read 15060 times]
Edited by Derek

      

Page 1  Page 2  Page 3 

Introduction

Merge replication is ideally suited to portable devices because it allows data to be updated autonomously on the portable device and the server. The data can later be merged when the device is connected to an instance of SQL Server. Server CE synchronizes with SQL Server by establishing an HTTP connection to the SQL Server Publisher through Microsoft Internet Information Services (IIS).

First of all, it is necessary to set up your server for merge replication. There is an excellent MSDN article by Microsoft, ‘Step by Step: Program Microsoft SQL Server CE 2.0 Merge Replication Using .NET Compact Framework’ at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/eff_arch_sql_servr_ce_rep.asp

Use Part1, Part2 and Part3 of the above article. This will ensure that the ‘northwind’ database is set up for merge replication, and that IIS is also setup. Select all the tables for replication though, instead of just products.

To check that everything is set up correctly, run Internet Explorer, and in the address field enter http://(server_ip)/Northwind/sscesa20.dll as in Figure 7.

Figure 7 – Confirmation that IIS is set up correctly from the server

If the page displays as above, then you have set up correctly. If not, please check your settings.

With your device in the cradle, ensure that the connections are set to ‘My Work Network’. This can be verified by Start>Settings>Connections>Advanced>Select Networks, as in Figure 8.

Figure 8 – Network settings

When you have done this, start Pocket Internet Explorer, and in the address field enter http://(server_ip)/Northwind/sscesa20.dll as in Figure 9.

Figure 9 – Confirmation that IIS is set up correctly from the Pocket PC

If the page displays as above, then you have set up correctly. If not, please check your settings.

Accessing local SQL Server databases and Replication use the System.Data.SqlServerCe namespace. To add the references we need, in Visual Studio, click on Project > Add Reference, and add the following references
System.Data.SqlServerCe
System.Data.Common

In our code, we then have to import the namespaces.

Imports System.Net

Imports System.Data

Imports System.Data.SqlServerCe

Imports System.Io

Imports System.Drawing

Then we have to define the fields we need for replication and accessing the local database.

Private interneturl As String = "http://192.168.1.25/Northwind/sscesa20.dll" 'Replace with your IP

Private publisher_server As String = "delldesktop" 'Replace with the name of your server

Private publisher_database As String = "northwind"

Private publisher_secmode As SecurityType = SecurityType.NTAuthentication

Private publication As String = "northwind"

Private subscriber As String = Dns.GetHostName() 'This PPC

Private local_database As String = "\My Documents\northwind.sdf"

Private connection_string As String = "data source=" & local_database

Private strSql As String

Private SqlCmd As SqlCeCommand

Private sqlCeConn As SqlCeConnection

In the form load procedure, we open the local ‘northwind’ database, if it exists, using the function Open_Local_Database.

Private Sub Open_Local_Database()

If System.IO.File.Exists(local_database) Then

sqlCeConn = New SqlCeConnection

sqlCeConn.ConnectionString = connection_string

Try

sqlCeConn.Open()

Catch Ex As SqlServerCe.SqlCeException

DisplaySQLCeErrors(Ex, "Open " & connection_string)

End Try

End If

End Sub

When we first run, this database will not be present, but we are going to create it using replication. Our screen originally consists of 1 visible button, Connect & Sync. The code behind Connect & Sync sets up our replication parameters, creates the local database if it doesn’t already exist, and populates it.

Next Page 

Back to .NET Compact Framework | [Article Index]