Page 1
Page 2
To demonstrate each method, you will have to create an eVB project and add
the ADOCE and SQL CE references to it. You might also want to include the reference
to the ADOCE DDL control for schema changes.
Then add the following public variables:
Public RemoteDB As SSCE.RemoteDataAccess
Public Repl As SSCE.Replication
Public LocalCAT As ADOXCE.Catalog
SQL Pass Through
SQL pass through is the probably the most customizable
method of using synchronization, however it will most likely require the most amount
of code. You can execute stored procedures to upload data, and they will work on
any SQL Server version from 6.5 to 2000. This method is perfect for write only
scenarios, and can also be used to manage the database with system stored procedures.
To demonstrate, add a stored procedure to the pubs database as follows:
CREATE PROCEDURE NewJob(
@job_desc varchar(50),
@min_lvl tinyint,
@max_lvl tinyint)
AS
insert into jobs(job_desc, min_lvl, max_lvl)
values (@job_desc, @min_lvl, @max_lvl)
GO
Then add a button named 'PassThrough' to your project
with the following code in the click event of the button, replacing the server,
virtual directory, user names and passwords as appropriate.
'Create the SQL CE Object
Set RemoteDB = CreateObject("SSCE.RemoteDataAccess.1.0")
'Set the IIS server and authentication to connect through
RemoteDB.InternetURL = "http://<Server Name>/<SQLCE Virtual Directory
Name>/sscesa10.dll"
RemoteDB.InternetLogin = ""
RemoteDB.InternetPassword = ""
'Execute the stored proc
RemoteDB.SubmitSQL "exec NewJob 'asdf',10,10", "Provider=sqloledb;Initial
Catalog=Pubs;Data Source=<Server Name>;UID=sa;password="
This event will add a new job to the job table with the
description of 'asdf' and the min and max values of 10.
Remote Data Access
RDA uses a push and pull method that is similar to early versions
of replication. You can code your application to automatically
pull down tables or select data into a local table. If the table
does not exist, it will be created. However, it will not create
defaults, indexes, constraints, etc. These must be created manually
using the ADOCE DDL object. Remote Data access will also work
with SQL Server versions 6.5 to 2000. If the statement returns
no records, or returns an unsupported datatype, an error will
fire with no explanation.
To demo this, first, we must create the local database.
Add a command button to the form of your project named 'CreateDB'
and add the following code to the click event.
'Instantiate the DDL object
Set LocalCAT = CreateObject("ADOXCE.Catalog.3.1")
'Create the database 'pubs' in the root directory
LocalCAT.Create "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data Source=Pubs.sdf"
'Clean up
Set LocalCAT = Nothing
Then, add the following code to a new button named 'RDA',
replacing the server, virtual directory, user names and passwords as appropriate.
'Create the SQL CE Object
Set RemoteDB = CreateObject("SSCE.RemoteDataAccess.1.0")
'Set the Local Database
RemoteDB.LocalConnectionString = "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data
Source=Pubs.sdf"
'Set the IIS server and authentication to connect through
RemoteDB.InternetURL = "http://<Server Name>/<SQLCE Virtual Directory
Name>/sscesa10.dll"
RemoteDB.InternetLogin = ""
RemoteDB.InternetPassword = ""
'Pull the select statement
RemoteDB.Pull "authors", "SELECT * FROM authors;", "Provider=sqloledb;Initial
Catalog=Pubs;Data Source=<Server Name>;UID=sa;password=", TRACKINGOFF
The 'TRACKING' option has two valid values. If it is set
to 'TRACKINGOFF', SQL CE does not track any changes. If it is set to 'TRACKINGON',
it will track any changes on that table on the device, and an optional parameter
is available to specify an error table to store any errors concerning the table.
Similarly, a push command can be executed to push the data back to the server.
Replication
Replication can fulfill most of the read/write scenarios
by itself, however it does seem to be more difficult to configure. Snapshot
publications fulfill the read only scenario, and are great for when the subscriber
(device) is in a rarely connected model. Like Snapshot publications, transactional
publications are also read only, but are for models that need to be connected
intermittently or frequently. Transactional publications are updated incrementally,
whereas the Subscriber model fully replaces the data on the device.
Merge replication fulfills the read-write model and supports all connection
models, and all conflicts are resolved on the publisher. Replication with SQL
Server CE Edition is only supported with SQL Server 2000.
Summary
Architecting a mobile solution with SQL Server
CE Edition requires careful planning and design. The connection states and read-write
scenarios required must be well thought out in order to manage replication effectively.
Previous Page