Page 1
Page 2
Requirement #2 - Allow User to Create New Endpoints and
Links
An "endpoint" of a trip is the departure or arrival
location. This is normally a neighborhood, landmark etc. that can be shared
with other users. It is not normally a fixed address.
A "link" is one of the paths or modes used to get from the departure
to the arrival location. A link might be "Highway #1", "bus",
or "walking".
A route contains two endpoints and one or more links.
Two (or more) routes can have the same endpoints but use different links. This
allows the user to compare travel times via different routes (possibly made
by different commuters) to the same destination. This is the real power of this
application.
Users need to be able to create new endpoints and new links. These endpoints
and links are shared by all users and exist on all local SQL tables.
For the remainder of this section we will consider only the "EndPoints"
table.
The "Add Endpoint" dialog. When the user enters
a new endpoint and clicks "Add" a connection will be established with
the back end server. The new record will be inserted and the entire table will
be resent to the device.
For this requirement, the record should only be created
on the back end SQL Server and the table should use an auto-incrementing identity
field. This identity value will then be used in subsequent trip records for
the departure or arrival location ID.
To achieve this, the user needs to create the new
endpoint record on the back end SQL Server. The new endpoint cannot be created
on the local SQL Server because two users might create the same location. Additionally,
if we were to perform the INSERT on the PocketPC the identity field would be
incremented there and we would not be able to push the record back to the server.
SQL Server/RDA will not let us push tables with identity fields.
I created a simple stored procedure on the back end
SQL Server database that accepts the new location string. The stored procedure
does a rudimentary check (using LIKE) to make sure there are no obvious duplicates.
In a higher volume application these inserts might need more sophisticated checking
procedures, possibly including manual review.
The stored procedure is executed by connecting the
eVB application to the back end SQL Server and then using the RDA SubmitSQL
method to execute the stored procedure. The stored procedure inserts a new record
in the Endpoints table (with a new unique identity). Once the new record has
been created, eVB code drops the local SQL Server CE Endpoints table and uses
the RDA Pull method to pull the entire Endpoints table (with the new location)
back to the device. Over a wireless dial up connection this is surprisingly
fast.
The following code is run when the "Add" button
is clicked:
Private Sub cmdAddNewRoute_Click()
On Error Resume Next
Dim SQL As String
Dim NextRoute As Integer
Dim strError As String
Dim bRes As Boolean
cn.Close
'Tell RDA about IIS Server.
Set rda = CreateObject("SSCE.RemoteDataAccess.1.0")
rda.LocalConnectionString = strSQLEProvider & frmMain.txtLocalDatabase.Text
rda.InternetURL = frmMain.cmbInternetURL.Text
'Build the connectiuon string for the remote server.
strRemoteConn = strSQLProvider & _
";data source=" + frmMain.txtServerName.Text & _
";Initial Catalog=" + frmMain.txtServerDatabase.Text & _
";user id=" + frmMain.txtDBUserId.Text & _
";password=" + frmMain.txtDBPassword.Text & ";"
SQL = "EXEC CreateRoute 1,'" & txtRouteDescription.Text &
"'"
Err.Clear
rda.SubmitSQL SQL, strRemoteConn
If Err.Number <> 0 Then
MsgBox Err.Description
cn.Close
Exit Sub
End If
'The new record is on the server. Drop the local table and then re-pull
cn.Open strSQLEProvider & frmMain.txtLocalDatabase.Text
cn.Execute "DROP table Routes"
cn.Close
rda.Pull "Routes", "SELECT RouteID, MetroAreaID, Description
FROM EndPoints WHERE MetroAreaID=1", strRemoteConn, TRACKINGON
'Refresh the dropdown combos
bRes = LoadCombos(strError, frmMain.txtLocalDatabase.Text)
If bRes = False Then
MsgBox strError
End If
End Sub
The above process runs in 5-10 seconds over a 14.4 dial
up connection for 30+ records.
Summary
eVB with SQL Server and SQL Server CE provide a compelling
combination. Achieve wireless connectivity and a whole class of applications
become feasible. Microsoft has indeed given us some cool new Lego bricks to
play with. Careful partitioning of the application can result in a very nice
hybrid solution that leverages the strength of each platform. The code on each
platform is simpler to develop and maintain and allows the delivery of substantial
functionality.
The SQL Server CE RDA object provides easy to use
functionality in the push, pull and SubmitSQL methods and the UniqueIdentifier
data type provides effortless yet crucial uniqueness support.
It is hard not to get excited when your eVB app connects
for the first time to a backend SQL Server, the same tingle I had when my first
VB 1.0 app compiled!
Author Biography
David Bailey is a Professional Engineer from Vancouver
BC, Canada.
David owns and runs Ideal Engineering, specializing in PocketPC, Web and client
server development with Microsoft tools. My first contract job for a local client
many years ago was to rewrite a program called Traf-o-data, written by none
other than Bill himself. The rewrite went fine but Bill has done better since.
Previous Page