Challenging question

  • Thread starter Thread starter Calvin Lai
  • Start date Start date
C

Calvin Lai

Hi all,

I have an unsolvable otherwise cumbersome problem. Currently I am using
SqlCe on my Handheld w/ some data downloaded onto it from Sql Server. The
update/insert/delete commands are then issued onto the Handheld database.
Now with all data are changed/modified on the SqlCe, I created a WebService
which takes a DataSet argument and intended to update the changes made from
the handheld back on the consolidated Sql Server.

Here comes the problem: How do I be able to generate a DataSet on the
handheld such that it contains the modified contents since it last
downloaded from the server? I see no way of tracking this unless I use some
more tables to store all the history. On the other hand, I suspect Microsoft
should be smart enough to provide this feature on SqlCe since it is intended
for remote use.

Does anyone know how to solve this problem? Thanks!

Calvin
 
Calvin:

If I understand the problem correctly, you're asking how to keep the PDA
data in sync with what's changed on the Server since you last synced up? If
that's the question, then currently there isn't a solution (there are work
arounds but no inherent support solutions), not even on the desktop.
Remember that CE is intended to be used with Replication. You can certainly
use it as a stand alone db, but if you are using a Web Service, then I can't
imagine why you are using SQL Ce, it's just another layer of fat.

If you have a Web Service, just pull the data over from the web service and
use the DataSet as your datasource, There's no real difference b/c you
don't bind controls to CE, rahter you bind them to data returned to a
dataset from CE.. You can also serialize the dataset as soon as you pull it
over and periodically thereafter using DataSet.WriteXML(@"\filename.xml").
When the changes are ready to by submitted, just send them via the web
service. From your post, it doesn't sound like you are using replication so
I think there's All upside and no downside (even if not, the upside
outweighs the downside) to taking CE out of the equation [Mind you that I'm
a big fan of SQL Ce and not in any way saying it shouldn't be used. It's
just that if you are using a web service for data access and aren't using
replication, I don't see any benefit].

However, your real problem is how to tell what's changed? Well, ADo.NET
until v 2.0 doesn't have any native support for this. You can set up
notification services but this is totally not worth it on CE. You could
write all updates to a MessageQuue via triggers and poll the queue for
changes. This too, is a bit complex but we'll have a MessageQueue library
at www.opennetcf.org shortly and the new CF will have queuing built in and
ADO.NET 2.0 will have notifications built in.

Since neither of those is viable except in extreme situations, I'd recommend
adding a SELECT statement to each update statement and use Output
parameters. Bill Vaughn has a good example of doing this at
www.betav.com ->Articles -> MSDN.
Either that, or run a background thread with low priority that polls the db
for changes (and if you decide to use Queues like I mention above, this is
essentially what you'd do) and refreshes your dataset.

HTH,

Bill
 
Hi Will,

Thanks for your insight. It *could* be implemented on a DataSet object
instead of using SqlCe at all, if my data is going to be update
periodically. However, this isn't the case. The data on the handheld may run
for as long as a couple of days, through which the data are frequently
modified on the handheld. Hence, serializing the whole dataset into xml for
each write/modify will be expensive I think. And since there isn't always a
connection provided, I can't really use WebService to access database
directly over the internet for the handhelds as well. Base on these
constraints, is there any good solution you can think of? Thank you very
much for your help.


Calvin


William Ryan eMVP said:
Calvin:

If I understand the problem correctly, you're asking how to keep the PDA
data in sync with what's changed on the Server since you last synced up? If
that's the question, then currently there isn't a solution (there are work
arounds but no inherent support solutions), not even on the desktop.
Remember that CE is intended to be used with Replication. You can certainly
use it as a stand alone db, but if you are using a Web Service, then I can't
imagine why you are using SQL Ce, it's just another layer of fat.

If you have a Web Service, just pull the data over from the web service and
use the DataSet as your datasource, There's no real difference b/c you
don't bind controls to CE, rahter you bind them to data returned to a
dataset from CE.. You can also serialize the dataset as soon as you pull it
over and periodically thereafter using DataSet.WriteXML(@"\filename.xml").
When the changes are ready to by submitted, just send them via the web
service. From your post, it doesn't sound like you are using replication so
I think there's All upside and no downside (even if not, the upside
outweighs the downside) to taking CE out of the equation [Mind you that I'm
a big fan of SQL Ce and not in any way saying it shouldn't be used. It's
just that if you are using a web service for data access and aren't using
replication, I don't see any benefit].

However, your real problem is how to tell what's changed? Well, ADo.NET
until v 2.0 doesn't have any native support for this. You can set up
notification services but this is totally not worth it on CE. You could
write all updates to a MessageQuue via triggers and poll the queue for
changes. This too, is a bit complex but we'll have a MessageQueue library
at www.opennetcf.org shortly and the new CF will have queuing built in and
ADO.NET 2.0 will have notifications built in.

Since neither of those is viable except in extreme situations, I'd recommend
adding a SELECT statement to each update statement and use Output
parameters. Bill Vaughn has a good example of doing this at
www.betav.com ->Articles -> MSDN.
Either that, or run a background thread with low priority that polls the db
for changes (and if you decide to use Queues like I mention above, this is
essentially what you'd do) and refreshes your dataset.

HTH,

Bill
Calvin Lai said:
Hi all,

I have an unsolvable otherwise cumbersome problem. Currently I am using
SqlCe on my Handheld w/ some data downloaded onto it from Sql Server. The
update/insert/delete commands are then issued onto the Handheld database.
Now with all data are changed/modified on the SqlCe, I created a WebService
which takes a DataSet argument and intended to update the changes made from
the handheld back on the consolidated Sql Server.

Here comes the problem: How do I be able to generate a DataSet on the
handheld such that it contains the modified contents since it last
downloaded from the server? I see no way of tracking this unless I use some
more tables to store all the history. On the other hand, I suspect Microsoft
should be smart enough to provide this feature on SqlCe since it is intended
for remote use.

Does anyone know how to solve this problem? Thanks!

Calvin
Click to expand...
Click to expand...
 
Back
Top