Keeping SqlCeConnection Open and Thread Safety

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 people 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've blogged about this once before here.

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." 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.".

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("Connection already open.");
_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.