SqlServerCE - Two connections to database

Question

Post reply

SqlServerCE - Two connections to database

Miguel Ferreira

SSC Rookie

Points: 33
More actions
October 3, 2005 at 12:26 pm

#87955

Hello,

I have a problems regarding database connections.

I am developing and application that run a Thread this threah executes and update to some records in the database.

When the thread runs and a cursor to the database is already opened...it returns a SqlCeException, because it can´t handle two cursors connected at the same time.

Is this true?

This is the way i open my connection:

SqlCeConnection ssceconn = new SqlCeConnection(Data Source = \\Program Files\\TrackAndTrace\\TrackTraceDB.sdf);

ssceconn.Open();

....

ssceconn.Close();

In my application,i open and close a connection everytime i need to do something in the database, but if the thread runs the update at a time that a cursor is alreay open, it blows.

How can i solve this problem? I need two connections to the Database at the same time.

Is it possible? How can i do it?

Best regards

Miguel Ferreira

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Mendes SSC-Addicted Points: 402 More actions · Answer 1

Hi there.

Well... I got to say one thing. In fact SqlServerCE isn't in any way a common database engine. It only performs simple tasks - the versions I've tried were all deceptive in some way. I realy don't know if it doesn't allow concurrent cursors but, it is probable that it realy doesn't support that simple feature - probably because it would need a lot of processing/memory amount to get it done.

I've been developing for Windows Mobile(CE) powered devices for some time now and the best way I've found to treat data - of any kind - was to develop a custom database system. This way I can do several concurrent updates or whatever I want, or need to.

My suggestion is that, depending on your app needs, you should consider on using custom data engines that fit your needs. The time you spent on the development will surely prove worthy when knowing you won't have that sort of problems - and if you have some, you can solve it. The problem with this is that you won't(!) have a backend team ensuring that your engine will stay up to date and those other replication or synchronization features the common SQL engines provide by default - unless you realy want to go that far - in my opinion it won't prove worthy - this one.

All of this to say that SqlServerCe engine - till today, and in my simple opinion, hasn't prove it's value so - for now - it's better to use other data engine systems - including your own custom engine.

I know that isn't very helpful in this case but, think about it - you never know!

My Best regards

Miguel Ferreira SSC Rookie Points: 33 More actions · Answer 2

Miguel Ferreira

SSC Rookie

Points: 33

October 4, 2005 at 4:35 am

#594771

If i open a connection, can i perform an update and a simple query at the same time (using the same connection) in two diferent threads, one performing the update and the other one performing the select?

Luis Mendes SSC-Addicted Points: 402 More actions · Answer 3

Well, has I've said before, you won't be able to do that sort of things using the sqlserverce engine! This is, in that sort of operations you should consider the lock procedures the engine uses to maintain data stability. You commonly have, even in the powerfull version of SQLServer, a sort of pessimistic lock control - this means that when you are updating a table you won't be able to see that particular table's info until the update has finished - it doesn't depend on the thread but only on the engine. In this case I believe they've maintained the pessimistic lock approach - it means you can't do what you want to but, check the new updates on SQLServer 2005 and the respective CE engine - you'll be surprised but, even then I can't say that it will be possible using the CE SQL engine! It raises some problems as you can imagine.

Hope it helps. Remember, nowadays databases are evolving to use new algorithm approaches in order to ensure both data stability and data availability - when succeded you will be able to do just what you want - maybe then have some more problems to solve but, this one is in progress.

Regards

Miguel Ferreira SSC Rookie Points: 33 More actions · Answer 4

I already tryed the lock aproach in c#:

Example:

public SqlCeConnection getConnection()

   lock(ssceconn)

    while (!available)

    Monitor.Wait(ssceconn);

if (ssceconn.State.Equals(ConnectionState.Closed))

throw new SqlCeException();

available = false;

Monitor.Pulse(ssceconn);

return ssceconn;

public void releaseConnection ()

lock(ssceconn)

available = true;

Monitor.Pulse(ssceconn);

But in C# .Net Compact Framework the System.Threading.Monitor doens´t have all the methods!!

Wait and pulse is missing...so the code above will not work. I dont know what to do...

If those methods existed...i could manage the connection access.

Do you know why System.Threading.Monitor doesn´t have all the methodsin compact framework? Do you ever used it?

Luis Mendes SSC-Addicted Points: 402 More actions · Answer 5

I also use the .NET Compact Framework and yes, it is very far from the full one. It is understandable why this has to be like this. Handheld/Smart Devices aren't really prepared for these kind of procedure handling - it is also understandable, at least by now anyway!

I really advise you to consider and try a different approach on what you want to do. This is, if you know there isn't a way to do your stuff through any of the common approches, reconsider and develop your own. If you simplify things and, mainly, if you centralize your database access through a kind of a singleton object that handles the job: one in - several outs > simple. This way you can monitor/debug everything on a different plane - like a new abstraction layer above the SQLServerCE connection - where you can provide that sort of procedure handling.

But, if you want to go through what doesn't exist you'll be stuck until someone remembers to change(if it is possible) the core sqlserverce engine - monitoring the job won't be an anwser really.

Try and create that abstraction layer and redirect every database call to it. In that abstraction layer build a process stack - to handle each request - and probably you'll need a kind of cache to ensure data availability while locking tables for updates.

If you explain your case better: Mainly what is the purpose of it; Maybe I can give you some extended help.

I believe that if you don't find it you gotta do it yourself

My best regards