Written by: Scott Davis
3/15/2009 3:08 PM

It’s hard to think of an application that doesn’t do something with data.  So when your windows mobile application needs a data store more extensible than a text file, you’ll probably turn to SQLCE.

However, don’t think you can use SQLCE the same way you use a desktop or server version.  I expect that it is probably true that SQL CE is thread safe and supports multiple connections, as Microsoft claims.  You would therefore expect you can use the database the same as any other database you’ve used in the past. However, don’t forget you are running on the window mobile platform, where resources are scarce.  As hardware improves and memory increases, you may very well never experience an issue following the same programming paradigm you have always followed.  But if you are a user conscientious developer, you will want to consider those poor folks that are still using a windows mobile 5 Smartphone, with low ram and processor power.

If you write your application to open a connection to the database, execute a query, then close and dispose the connection, you may very well run into an intermediate PInvoke error.  This error is coming from the interop that is connecting .Net to the native interface for SQLCE.  If your experience is as mine was, you will almost always see this error when attempting to open a new connection to the database, after your application has been running for a while, or if you try to open and close several connections in succession.  Running multi-threaded access to the database may make the situation worse.

The answer for me was to open a single connection to the database, keep it open for the entire life of the application, and use a mutex to control access to the database, so threads would have to wait their turn to get to the database. 
 

If you are anything like me, opening a connection and not closing and disposing of it, is counter to the nature of being a good developer.  Freeing resources at every opportunity, especially if you think the mobile environment is one that really could use those resources back, is the intuitive thing to do.  Unfortunately it appears that the very effort of freeing those resources could result in a backlog of garbage collection that actually keeps the resource in play much longer than you expected, potentially creating an issue (PInvoke error).  So keeping one connection open suddenly looks better than the alternative.
 

I implemented a static DataController class through which all data in the SQLCE database is accessed.  I then implemented an Initialization method that establishes the database connection and stores it in a private class variable.  Finally, I built a TearDown routine, which is called when the application terminates, that closes and disposes of the connection.  (You could also implement a singleton pattern and use the constructor and dispose methods to do this, if you want to go to the extra work of building a Singleton).  Along with these methods are a couple helper methods to create and destroy the command and resultset objects.  Finally, pay close attention to the Lock statement, without which, all the rest would be not much help.
 

The sample code below creates the core of my data access layer model for Windows Mobile Development.   Feel free to leverage what I’ve learned in your own applications. 
 

using System; using System.Linq; using System.Collections.Generic; using System.Text; using System.IO; using System.Data.SqlServerCe; namespace MobileDemoApp { internal static class DataController { private const string DATABASE_FILE_NAME = "myDB.sdf"; private static SqlCeConnection _conn = null; private static object _databaseLock = new object(); #region legal stuff /* This code is provided as a courtesy of www.IgnitionPointSolutions.com, and it may be freely used and distributed THE INFORMATION AND CODE PROVIDED IS PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. */ #endregion #region Intialize and Dispose Methods internal static void Initialize() { //don't do anything if connection has already been setup if (_conn == null) { string fullPath = Path.GetDirectoryName( System.Reflection.Assembly .GetExecutingAssembly().GetName().CodeBase) + "\\" + DATABASE_FILE_NAME; FileInfo fo = new FileInfo(fullPath); if (!fo.Exists) { throw new IOException("The database file could not be found: " + fullPath); } else if (fo.Attributes == FileAttributes.ReadOnly) { throw new IOException("The database file is readonly: " + fullPath); } _conn = new SqlCeConnection(); _conn.ConnectionString = "Data Source =" + fullPath; int retry = 0; bool success = false; while (!success) //try to open more than once, you never know on Mobile { try { _conn.Open(); success = true; } catch (Exception exc) { if (retry++ <= 6) //retry several times { //give the DB a chance to get a good state, //sleep a little longer each time System.Threading.Thread.Sleep(retry * 2000); } else { new IOException("Failed to open database: " + fullPath, exc); break; } } } } } internal static void TearDown() { //Clean up the static DB Connection try { if (_conn.State != System.Data.ConnectionState.Open) { _conn.Close(); } _conn.Dispose(); } catch (Exception e) { } _conn = null; } #endregion #region Helper Methods private static SqlCeCommand GetCommand(string commandText) { //create a command and put the connection on it if (_conn == null) { throw new InvalidOperationException("Database connection was not initialized"); } return new SqlCeCommand(commandText, _conn); } private static void CleanUp(SqlCeCommand cmd, SqlCeResultSet rs) { //Close and dipose of objects properly (as best you can) if (rs != null) { try { rs.Close(); } catch (Exception e) { } //ignore error try { rs.Dispose(); } catch (Exception e) { } //ignore error rs = null; } if (cmd != null) { try { cmd.Dispose(); } catch (Exception e) { } //ignore error cmd = null; } } #endregion #region Data Access Methods internal static List<string> GetCustomerNames() { SqlCeCommand cmd = null; SqlCeResultSet rs = null; List<string> result = new List<string>(); //use this dummy object as a mutex to ensure //no one else gets access to the connection //all database call must be wrapped with a //lock on the same object lock (_databaseLock) { try { cmd = GetCommand("Select * from Customers where Active = 1"); rs = cmd.ExecuteResultSet(ResultSetOptions.None); while (rs.Read()) { if (!rs.IsDBNull(rs.GetOrdinal("CustomerName"))) { result.Add(rs.GetString(rs.GetOrdinal("CustomerName"))); } } } catch (Exception e) { //TODO: Log error throw; } finally { CleanUp(cmd, rs); } return result; } } #endregion } }

Trackback Print

Location: Blogs Scott's Technology Blog