Replication is programmed through the SSCE.Replicationobject’s methods and properties. This object is created using the progid “SSCE.Replication.1.0”. A reference to the library “Microsoft SQL Server Control 1.0” should be added to the project using the eVB Project Add References menu com- mand. All the code shown in this section can be found in the project \Examples\PocketPC\Replication\Replication.ebp or \Examples \HPC2000\Replication\Replication.ebpon the CDROM. The code to use replication is the same for Pocket PC and HPC 2000. All the code in this application is located in frmReplication.ebf.
You should ensure that the “SQL Server Agent” service is running before attempting to use merge replication.
178
Chapter 4•
SQL Server CE Remote Data Access and ReplicationConfiguring the publication access list
Figure 4.21
The following stages are used for programming replication:
● Initialize: Used to create an SSCE database and initialize a subscription
to a publication.
● Synchronize: Connects to the publication and downloads /uploads data
changes. This is called periodically by an application to keep data changes up to date.
● Reinitialize: Used to reinitialize a subscription when changes are made
to the articles on the SQL Server.
● Drop: Used to remove a subscription to a publication and optionally
delete the database. 04-P1914 8/24/2001 11:26 AM Page 178
INITIALIZING A SUBSCRIPTION
Initializing a subscription involves setting replication properties to specify de- tails on the publication and to create a local database to store the SSCE copies of the articles. Listing 4.8 shows the function ConfigureReplicationOb ject.This creates a Replication object and sets the necessary properties to specify the publication to subscribe to.
Listing 4.8 Setting Replication Properties
Private Sub ConfigureReplicationObject(oRep _ As SSCE.Replication, sServer)
Set oRep = CreateObject("SSCE.Replication.1.0") oRep.InternetURL = "http://" & _
sServer & "/sscepubs/sscesa10.dll" ' TODO: Change this to the name of
' your Publication server
' This will probably be the same as your SQL ' Server you are connecting to
oRep.Publisher = "MALAHIDE" oRep.PublisherDatabase = "pubs" oRep.Publication = "pubs"
oRep.PublisherSecurityMode = NT_AUTHENTICATION ' Use these properties for non-NT Authentication ' access 'oRep.PublisherLogin = "sa" 'oRep.PublisherPassword = "" oRep.SubscriberConnectionString = _ "data source=\PubsRepl.sdf" oRep.Subscriber = "Pubs #1" End Sub
The properties used in Listing 4.8 are
● InternetURL: The URL of the IIS server, virtual directory, and sscesa10.dllused for replication.
● Publisher: The Publisher SQL Server name. In many cases this will be the same name as the SQL Server containing the published database. This should be a server name and not an IP address.
● PublisherDatabase: The name of the SQL Server database being published.
● Publication: The name of the publication. In this case the Pub lisherDatabaseand Publicationare the same, but there could be
180
Chapter 4•
SQL Server CE Remote Data Access and Replicationseveral different publications (e.g., “pubs1” or “pubs2”) on the same pub- lisher database (e.g., “pubs”).
● PublisherSecurityMode: Specifies the type of login access to the SQL Server. The default is DB_AUTHENTICATION, which specifies that an SQL Server login /password will be supplied using the PublisherLogin and PublisherPassword properties. NT_AUTHENTICATION specifies trusted security, and a login and password do not need to be specified.
● PublisherLogin: An SQL Server login for DB_AUTHENTICATION.
● PublisherPassword: A password for DB_AUTHENTICATION.
● SubscriberConnectionString: The connection string specifying the local SSCE database used for the subscription. Note that you do not need to specify a provider because SSCE is assumed.
● Subscriber: The name by which the subscriber will be known by the publisher.
The function CreateSubscription(see Listing 4.9) checks if subscrip- tion database \PubsRepl.sdf exists, and if it does, deletes it. The function CreateSubscription then calls ConfigureReplicationObject to ini- tialize the properties in the Replication object. Once this is done, the Replication methods Initialize, Run, and Terminate are called, and then the contents of the “Authors” table are displayed in a grid. This subscribes to the publication and then performs a merge replication resulting in the data in the articles being downloaded to the SSCE database. The function Create Subscriptionis called through the Subscription Create menu command.
Listing 4.9 Creating a subscription
Private Sub CreateSubscription(sServer) Dim oRep As SSCE.Replication
Call ConfigureReplicationObject(oRep, sServer) On Error Resume Next
If FileSystem1.Dir("\PubsRepl.sdf") <> "" Then FileSystem1.Kill "\PubsRepl.sdf"
If Err.Number <> 0 Then
DisplayError "Deleting database: " Exit Sub
End If End If
oRep.AddSubscription CREATE_DATABASE If Err.Number <> 0 Then
Call ReportEngineErrors("AddSubscription:", oRep) Exit Sub
End If
oRep.Initialize 04-P1914 8/24/2001 11:26 AM Page 180
If Err.Number <> 0 Then
Call ReportEngineErrors("Initialize:", oRep) Exit Sub
End If oRep.Run
If Err.Number <> 0 Then
Call ReportEngineErrors("Run:", oRep) Else oRep.Terminate Call ShowAuthors MsgBox "Initialized" End If End Sub
The following Replicationobject methods are used in Listing 4.9:
● AddSubscription: Adds a new anonymous subscription to an SQL Server publication. The CREATE_DATABASEoption causes the method to create the SSCE database, while the option EXISTING_DATABASE as- sumes that the SSCE database already exists.
● Initialize: Prepares the Replication object for synchronization.
● Run: Invokes the merge replication, resulting in a two-way transfer of data.
● Terminate: Completes the merge replication process.
The function CreateSubscription uses the method ReportEngine Errors— this is the same function used in the RDA examples for reporting er- rors. Creating the subscription adds three fields to the “Authors” table in the SSCE database: Rowguid, S_generation, and S_rowlineage. The code to display the “Authors” table specifies a list of fields to display in the SELECT statement rather than “*” to avoid displaying these fields — they are binary and cannot be displayed in a grid.
SYNCHRONIZING A SUBSCRIPTION
The Pocket PC or HPC 2000 application is responsible for connecting to the SQL Server distributor and synchronizing the replication on a regular basis. This could be whenever the user dials into a network using RAS (Remote Ac- cess Server) or on a periodic basis for devices that are permanently connected to the network. The code in Listing 4.10 calls the Replication object’s Ini tialize, Run, and Terminatemethods to perform synchronization — these are the same calls made in Listing 4.9.
Listing 4.10 Synchronizing replication
Private Sub Synchronize(sServer) Dim oRep As SSCE.Replication
182
Chapter 4•
SQL Server CE Remote Data Access and ReplicationCall ConfigureReplicationObject(oRep, sServer) On Error Resume Next
oRep.Initialize
If Err.Number <> 0 Then
Call ReportEngineErrors("Initialize:", oRep) Exit Sub
End If oRep.Run
If Err.Number <> 0 Then
Call ReportEngineErrors("Run:", oRep) Else oRep.Terminate Call ShowAuthors MsgBox "Synchronized" End If End Sub
The Subscription Synchronize menu in the sample application syn- chronizes the subscription and redisplays the list of authors. Changes made in the SSCE copy of the Authorstable will be copied up to the SQL Server copy, and changes made in the SQL Server copy of the Authorstable will be cop- ied down on the SSCE database. Note that this is two-way replication, whereas RDA only sends changes made on the SSCE table up to SQL Server. The entire contents of the table must be copied down from the SQL Server table using a Pull.
The PublisherChangesproperty contains the count of the total num- ber of publisher changes applied to the subscriber after synchronization takes place. Likewise, the SubscriberChanges property returns the number of changes made at the publisher by the subscriber.
REINITIALIZING A SYNCHRONIZATION
You can reinitialize a subscription through the Replicationobject’s Reini tializationmethod. This marks the subscription as requiring a refresh, and the next time the Initialize, Run, and Terminatemethods are called, the subscription information will be downloaded again from the SQL Server pub- lication. This might need to be done if the articles in the publication have been changed and the changed articles need to be used in the SSCE database. DROPPING A SYNCHRONIZATION
An SSCE application can remove itself from the list of anonymous subscribers to an SQL Server publication by calling the Replication object’s DropSub scriptionmethod (see Listing 4.11). Note that the only Replicationprop- erty that needs to be assigned a value is SubscriberConnectionString. The DropSubscription method can be passed the DROP_DATABASE con- stant to request that the SSCE database be deleted. The option LEAVE_DATA 04-P1914 8/24/2001 11:26 AM Page 182
BASEwill leave the database in place after the call is made. The Synchronize Drop menu command will drop the subscription in the sample application.
Listing 4.11 Dropping a replication
Private Sub DropSubscription(gc_Server) Dim oRep As SSCE.Replication
Set oRep = CreateObject("SSCE.Replication.1.0") On Error Resume Next
oRep.SubscriberConnectionString = _ "data source=\PubsRepl.sdf" oRep.DropSubscription DROP_DATABASE If Err.Number <> 0 Then
Call ReportEngineErrors("Drop:", oRep) Else
MsgBox "Subscription dropped" End If
End Sub