New to threading

[Ron Weiner]

I am developing a PPC 2003 application (VB.Net - CF 1.1) that will be mostly
connected to a corporate Sql Server via WiFi. The application uses a local
SqlCE database to store and process all of the data it captures.

Original specifications were for the application to be able to copy all of
its captured data back the corporate Sql Server on demand. All of this has
been implemented and works without error.

Now the customer would like to be able to capture this data to the Sql
Server in near real time. If the network and server are available the whole
process takes only a second or two to run. In that I can never be really
sure that the device will be with in range of a network access point, or
(for that matter) that the server will up and running, I have a problem with
the device becoming non responsive (for something like 15-20 seconds) to
user actions when attempting to make a connection that ultimately will fail.

In an attempt to solve this problem, I tried starting the data transfer
routine to its own thread. Smooth sailing whether or not the network is
available. The device is always responsive to user input, BUT apparently
SqlCE is a "Single Trick Pony" application. I now occasionally get errors
when the main thread is attempting to access the SqlCe data base at the same
time as my background data transfer thread. This problem appears to be
database wide as I will get the error making the connection, not accessing
data in a table locked by the other process.

I am sure I am not the first person to get himself into this situation, and
was hoping that someone has had similar experience and can direct me towards
a solution. Any advice on how to architect this scenario would be most
sincerely appreciated.

 

[Ginny Caughey [MVP]]

Ron,

You are certainly not the first person to realize that multiple simultaneous
connections to the database could be useful, and SQL Mobile implements that
feature. Unfortunately as you found out, SQL CE does not. It sounds like it
would be worthwhile to investigate upgrading to VS 2005 Standard or above
and CF 2.0/SQL Mobile. The only real reason not to would be if you need to
support legacy devices that can't run CF 2.0.

 

[Paul G. Tobey [eMVP]]

Yes, that's the way it works. Trying to mix SQLCE commands coming from
multiple threads at the same time might not a good idea. You might consider
having the database I/O always initiated from the background thread,
triggered, perhaps, by a queue of command objects that you can add from the
UI thread when you need something database-related done. This is a very
general but fairly complicated solution.

Paul T.

 

[Ron Weiner]

It's a shame that SqlCe Database works like that. In my case I would not
have to worry about the data getting clobbered by competing threads, so I
think I am going to take Ginny's advise to heart, and do an upgrade. I
suspect this would be far easier and less costly than attempting the work
around you described. Thanks for your help.

 

[Ron Weiner]

Do I understand correctly, that if I do the upgrade to VS 2005 and port my
project to CF 2.0 that I will get a newer/better CE Sql engine (Sql Mobile)
that natively supports multiple connections to the same database? I sorta
control the hardware that my application will be used with (Symbol MC50) and
as near as I can tell this device should be able to use CF 2.0 and Sql
Mobile.

 

[Ginny Caughey [MVP]]

Ron,

You understand correctly.

 

[Markus Humm]

It's a shame that SqlCe Database works like that. In my case I would not
have to worry about the data getting clobbered by competing threads, so I
think I am going to take Ginny's advise to heart, and do an upgrade. I
suspect this would be far easier and less costly than attempting the work
around you described. Thanks for your help.

Another thing would be to use critical sections around the database
accesses, so only one of them could run at any given time. Okay, that
costs performance at least a little bit (depending on what you do excactly).

Greetings

Markus