Re: Keeping SqlCeConnection Open and Thread Safety

Jon,

I don't remember specifically advising using separate connections on
separate threads for SqlCe, but it would certainly safest if there's any
doubt. Perhaps Marcus's approach is fine, I just don't know. Certainly
opening the connection is expensive, but if you've only got, say, two
threads running in your app - perhaps one for work and one for synchronizing
or some such, then you're only taking the hit twice in the lifetime of your
app. I don't recommend opening and closing connections as needed on mobile
devices for performance reasons. In fact I guess my desktop apps fall into
Dave's 1% category because they also typically use a single connection
during the lifetime of the app or thread.

--
Ginny Caughey
Device Application Development MVP


"jonfroehlich" <jonfroehlich@xxxxxxxxx> wrote in message
news:1168569014.096416.39000@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I've done quite a bit of searching online and it's fairly clear that
there is a bit of confusion about how one should use the
SqlCeConnection object, particularly with regards to two interrelated
issues: (1) whether to keep the SqlCeConnection open and (2) whether
the SqlCeConnection is thread safe. For example, some advocate for
keeping one static SqlCeConnection open and shared for the lifetime of
an application. However, if the application is multithreaded, can we
assume that we can safely share the connection object?

I think some of this confusion stems from the differences between the
desktop version of SQL Server (SqlConnection and SQL Server Mobile
(SqlCeConnection). For example, in a MSDN forum post, Dave Hayden (a
.NET/C# MS MVP) suggests that "For 99% of all applications, the best
practice is to open and dispose of database connections right when you
need them and not to leave them open for the duration of the
application. Open the database connection as late as possible and
close/dispose of it as soon as possible."
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=526164&SiteID=1).
If, like Dave recommends, we always open a local connection within our
method calls (and do not access the SQL connection as a state object),
we don't really have to worry about the thread safety of the SQL
connection object.

Although this may be good advice for accessing SQL Server from the
desktop, it contradicts the advice given by Microsoft developer Marcus
Perryman in his SQL Mobile post on his blog, "The SqlCeConnection class
implements the IDisposable interface because it allocates a number of
unmanaged resources, and therefore the code must call Dispose() on the
object before it goes out of scope to ensure these resources are
cleaned up in a timely manner. Creating and destroying SQL Mobile
database connections is an expensive task and so the SqlCeConnection is
designed to be a long lived, shared instance across the lifetime of the
application."
(http://blogs.msdn.com/marcpe/archive/2005/06/21/431074.aspx).

Thus, it appears that Dave and Marcus contradict each other. However,
Dave's advice was (I believe) given under the assumption that the
person was using SQL Server and not SQL Server Mobile. So, it looks
like the correct usage of the SqlCeConnection object is to reduce the
amount of creating and destroying SQL Mobile database connections (the
reasoning behind this is that SQL Server Mobile supposedly does not
have connection pooling, see Marcus Perry link above).

So, then what about thread safety? The MSDN documentation is clear.
Most objects within the SQL Mobile namespace are not threadsafe. For
example, the SqlCeConnection documentation states, "Any public static
members of this type are thread safe. Any instance members are not
guaranteed to be thread safe." Thus, instance methods like
BeginTransaction(), CreateCommand(), etc. are not thread safe. I'm not
sure what the detriment/exception would be if this was not followed,
but MSDN is clear, SqlCeConnection was not meant to be shared across
threads.

OK, so what if you have a multithreaded application? It seems like
there are two prevailing methods: (1) Marcus Perry suggests that "for a
complex app, ideally the SqlCeConnection instance would be placed in a
singleton wrapper class that manages access to the database." or (2)
Ginny Caughey (MS MVP) offers the other approach, using a separate
SqlCeConnection object for each thread. For the first approach, I would
imagine the simplest wrapper possible may look something like the
following (I will implement it as a static class rather than a
singleton):

static class SqlWrapper
{

private static SqlCeConnection _sqlCeConnection;
private static object _objLock = new object();

static void Open(string connectionString)
{
lock (_objLock)
{
if (_sqlCeConnection != null)
throw new InvalidOperationException("Already opened");
_sqlCeConnection = new SqlCeConnection(connectionString);
}
}

static void Close()
{
lock (_objLock)
{
if (_sqlCeConnection != null)
{
_sqlCeConnection.Dispose();
_sqlCeConnection = null;
}
}
}

static int ExecuteNonQuery(SqlCeCommand sqlCommand)
{
lock (_objLock)
{
sqlCommand.Connection = _sqlCeConnection;
return sqlCommand.ExecuteNonQuery();
}
}

static SqlCeDataReader ExecuteReader(SqlCeCommand sqlCommand)
{
lock (_objLock)
{
sqlCommand.Connection = _sqlCeConnection;
return sqlCommand.ExecuteReader();
}
}
}

Does this seem right to people? In this class, we do not expose the
underlying SqlCeConnection object, thus commands must be passed in via
the wrapper interface to be executed (as they require a reference to a
SqlCeConnection).

For the second approach, one connection per thread, the difficulty
isn't necessarily in opening 1 connection per thread but knowing when
to close those connections. In other words, a SqlCeConnectionManager
class might manage access to SqlCeConnections and might serve as a
SqlCeConnection factory (with, perhaps, an underlying connection pool).
The factory may look at which thread is active (Thread.CurrentThread),
check to see if a connection has been allocated for that thread (create
one if not) and return the SqlCeConnection. However, the onus would be
on the threads themselves to close their connections before exiting
(this seems messy to me). I suppose one could spawn a monitoring thread
that looked as the status of Threads and, once dead, would either close
the respective SqlCeConnection or return the connection the connection
pool. However, .NET CF 2.0 does not support the instance property
IsAlive so it's not clear how one could do this.

I would imagine that a multithreaded application that launches many
short-lived threads that need database access should probably go with
the SQL wrapper solution as the overhead of opening/closing connections
would introduce a performance hit (even with connection pooling).

An interesting side note: I created a test app on the mobile phone
which launched 50 threads on a shared SqlCeConnection and proceeded to
read/write random bits of data. No exception was thrown. Of course, the
lack of error does not mean that it will always work.



.

Relevant Pages