What is best SQLCeConnection strategy?

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

hi
I would like to know whats better
To declare a SQLCeConnection variable as a Static member of
a Class containing functions to MakeConnection and
CloseConnection, or should I just declare it as a Public
member of the above mentioned class containing Connection
code.

And, if better is use a static SQLCeConnection should i use open a
connection once or open before a execute an query and close after it?


Thanks
 
I don't believe there is a simple answer to this question.

A single static connection may be the answer, but if your application
is multi-threaded and could have active connections to the database
from more than 1 thread you may have problems.

Someone already mentioned that closing the "last open connection" will/
may compact the database, but it also shuts down some db process(es).
Similar to shutting down the sql server services on a server or
desktop. Opening the first connection will start these process(es)

This is important to know because it has a huge impact on performance.

You'll find that opening and closing a single connection for each
query/transaction performs poorly due to the compacting and shutting
down of processes.

However, if you create a single sqlceconnection, open it, keep it
open, and never use it, for the lifetime of your application. You
will find that the opening and closing of subsequent sqlceconnections
performs very well and you may hardly notice the overhead of closing
and opening a connection. (watch out for the garbage collection when
disposing of many sqlceconnections)

Also keep in mind that an open connection locks the database file. So
if you need/want to be able to copy/remove that sdf file without
stopping your app, you'll need to close all the connections.

Knowing this will give you more flexibility in your approach. Good
luck.
 
A single static connection may be the answer, but if your application
is multi-threaded and could have active connections to the database
from more than 1 thread you may have problems.
Click to expand...

This is a good point. Each thread should have its own connection. Good
reply.
 
Back
Top