need advice - first mobile app

[Jason Shohet]

We have people that do mailing runs around NYC, and need to be able to
retrieve and input data from our central Oracle database, on a mobile (I
guess lets assume CE) device. They have records of their runs, and then
check off fields when they are done.

What happens when the device can't connect to the internet? Can they input
data, and then have it cached, and then synch it back up with IIS and our
Oracle database when the device comes back online? How would this work.

If the answer is no, it seems I'm totally sc**wed.
Would the 2nd best thing be to have some local db on the device (ie, Access,
Sybase SQL Anywhere? suggestions?), and a local WindowsCE app on the device
connects to our main Oracle db, pulls down records for the run into a
dataset, and saves that dataset to Access on the device. Then the user does
his run, updates the local Access db, and then when he comes back into the
office, the application takes the information from the local db on the
device, and updates the main records in the Oracle database. This seems a
horrible way to go....

Thanks for any suggestions
Jason Shohet

 

[Ed Kaim [MSFT]]

Yes, you can store data offline. See http://www.microsoft.com/sql/ce.
Synchronizing is 1 line of code, if I remember correctly.

 

[Jason Shohet]

So you're saying that one would have a sql-CE db on his local device. And
somehow that can upload to a central Oracle 9i database in our main office.

But they are disparate dbms's. How is one supposed to speak to the other.
Even their datatypes don't match....

 

[Ed Kaim [MSFT]]

You can use a SQL Server bridge.

 

[Jason Shohet]

Is there a way to avoid having a second db on the windowsCE device and just
use the main central db?
BEST SCENARIO:
If the device cannot connect, there's some software that can cache the
dataset on the device, record any changes, and then when it gets back into
range and the guy hits 'update', it will make all the necessary deletes,
updates etc.

Dreaming here?

 

[Valentin]

You can implement this without using SQL CE - use DataSet.

Create on the server side a Web service that handles DataSets,
create on the device an .NET Compact Framework aplication that
comunicates with the webservice. If the connection is not available,
save the DataSet on the device.

 

[Jason Shohet]

Valentin, thank you for the advice -- let me see if I get this straight.

You're talking about a windows or asp.net app on the CE device. I would
guess it has to be a windows app. Because when the CE device is out of
range & can't connect to the web server, it would have to persist the
dataset somewhere from web page to web page. If there's no sessions (no web
server), how to keep that dataset alive?
So maybe windowsForms is the only way to do this. And it would call a
webservice on the web server when it updates. But the dataset is in a
windowsForm app -- so it won't die, as the user updates records in the
dataset.

PS: I don't have to worry about concurrency either -- its fine if the user
connects an hour later and synchs back up. But how to update the database
from all the changes in the dataset? There could be many. CommandBuilder
object?

TY again
Jason Shohet

 

[Jason Shohet]

Also if I'm correct that you're talking about a windowsforms app --
what happens if I have a dataset in the app on the CE device, (no db
locally), but the user can't get on the LAN or connect to the internet to
call the webservice... And he's tired wants to go home, and decides to close
the app ?
Is there a way to save that dataset with multiple relational tables in it,
onto the windowsCE device? So that the next day when he/ she comes to the
office and synchs up to the LAN, or connects online, the dataset from
yesterday will be able to update the database via the ws's or whatever ?

 

[Ed Kaim [MSFT]]

Web services are probably the best way to avoid having a local database (and
cache the data offline as XML). You'll have to do the reconnect/resync work
by hand.

 

[new.microsoft.com]

I highly recommend looking at using the enterprise version of AvantGo for
something like this. You basically build a regular web app. AvantGo caches
form submits until you get around to synchronizing. And synch can be done
via wifi, docking cradle, cell phone...

Plus it's cross platform and will run Palm pilots and blackberries too with
little or no changes.

The downside with AvantGo is cost per seat is kind of high. And if you need
a great deal of local intelligence in the app (searching data, dynamic
screens based on results entered on prior screens, very specific data entry
controls ...) the compact framework starts to be more compelling.

But you could easily build an app that downloads each person's route, then
in the route screen info such as maps, directions, details. And then check
boxes/drop downs to indicate delivery status. You can even capture
signatures. And on webserver side all you do is build a straight web app
that would even work for people fully connected to the server.

I wish Microsoft had bought AvantGo instead of Sybase. It would have been a
great fit.

-Bill
bill at billwolf dot com

 

[Jason Shohet]

wow AvantGo sound exactly what we need, spectacular. Yea I don't know if
this org. would go for the licenses... I'm going to check.
If I don't use avantGo, and I have a windows app, lets say it goes like
this:

1. delivery guy goes to the main office. Starts up his WindowsCE device,
starts a winforms app on it, connects to the LAN, retrieves his route into a
dataset (via a webservice lets say) within the winforms app.

2. He unplugs from the LAN, goes about his route. He makes changes into
that dataset.

3. At end of the day he reconnects to the LAN and the dataset changes
update the main database (via CommandBuilder ADO technique perhaps).

The problem with this is -- what happens if he decides to shut down the
application for an hour. Where does that dataset go. Can a dataset be
persisted to a "c drive" on the CE device (do they even have a c drive?)
then retrieved the next time the app starts ? This is what I'm
struggling with.

 

[new.microsoft.com]

If you store the data to XML it won't go anywhere when they powerdown. I'm
looking into using XML cause my app will be offline maybe 5-10% of the time.

But in your case you might want to build for the offline scenario since the
messengers are on the go. So you might want to rely on SQL CE replication.
Then you're building a regular WinCE app going against a local data. You
might have a button or just hook into active sync to do the replication when
they're connected.

BTW, a huge feature you could add would be a way to deal with changes to the
schedule while the guy's already out on his route. AvantGo has a way cool
way to deal with this - especially on a Blackberry where you get an email
that both updates the locally cached pages and takes you right to it.

bill at billwolf dot coom

 

[Valentin]

Yes, a DataSet can be saved and loaded from a file(using ReadXML and
WriteXML methods). A CE Device has a file system like a desktop
computer, the only difference is there is no c: drive, you use
absolute path like this:
"\dir1\dir2..." not like this "c:\dir1\dir2.."

Regards,
Valentin Iliescu