SQL Mobile

I had a question recently about memory issues with the SqlCeConnection class used in conjunction with SQL Mobile. In this customers app it appeared the class was leaking resources that were not being collected by the GC. So after just a few minutes of execution their managed application ran out of available memory and failed to create connections.

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. Putting in the calls to Dispose() fixed their problem but left them with the question, why? 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. For a complex app, ideally the SqlCeConnection instance would be placed in a singleton wrapper class that manages access to the database.

While I'm on the subject...

If you are working with SQL CE 2.0 or SQL Mobile this is a really useful doc for getting the right approach to perf tuning. Most of its common sense but useful to have in a concatenated form: https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ssceqpop.mspx

Another doc I find useful that covers getting data onto a SQL Mobile database: https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5mobile.asp

Marcus

Comments

• Anonymous
  June 27, 2005
  Hi marcpe,
  I am getting multiple copies of your blog on my newsreader [Mozilla Thunderbird version 1.0.2 (20050317)].
  
  One is coming from
  https://blogs.msdn.com:443/marcpe/archive/2005/06/21/431074.aspx
  
  and other from
  http://blogs.msdn.com/marcpe/archive/2005/06/21/431074.aspx
  
  To be exact, each post is coming 12 times, 6 times from frist source, 6 times from second source.
  
  I have subscribed to this blog using the following link.
  https://blogs.msdn.com/marcpe/rss.aspx
  
  There is no such problem with other blogs where I have subscribed. This problem started today only.

• Anonymous
  June 27, 2005
  I have checked everything I can and all appears well, including the RSS feed which looks well formed and contains only one copy of each item.
  
  Is anyone else seeing this problem?
  
  Marcus

• Anonymous
  June 30, 2005
  "For a complex app, ideally the SqlCeConnection instance would be placed in a singleton wrapper class that manages access to the database."
  
  at the cost of connection pooling =(

• Anonymous
  July 03, 2005
  There is no connection pool used for local database access for SQL Mobile. For RDA and replication the ISAPI extension will make use of connection pooling based upon the connection string used.
  
  Marcus

• Anonymous
  January 07, 2007
  Marcus, In my preliminary research on the subject, I have not found a lot of links on the web (directly from Microsoft or third-party) that talk about how one should properly handle a SqlCeConnection from multiple threads. In the blog post above, you suggest that a "multithreaded app" should provide a wrapper class around the SqlCeConnection. I'm trying to envision what this would look like. I could imagine one approach would be to completely hide the underlying SqlCeConnection inside the wrapper and provide common database-related functionality such as OpenDatabase(), CloseDatabase(), Insert(..), Update(...), Select(...), etc. However, this seems relatively fickle. The nice thing about the SqlCeCommand interface is that it's incredibly flexible. The wrapper interface, as I defined it above, seems to dramatically reduce this flexibility. Have you thought of a clean way to wrap a SqlCeConnection such that one could still use SqlCeCommand objects? An entirely different approach, which I have seen suggested, is to create a SqlCeConnection for each thread in the system. This would work well, I suppose, if those threads are long lived (e.g., to mitigate the cost of creating and destroying the connection). Thoughts? I realize you made this original post ~1.5 years ago, but I thought I'd post a comment regardless. I posted a rundown of this issue at http://csharponphone.blogspot.com/search?q=SqlCeConnection.

• Anonymous
  June 13, 2007
  I was unable to find much documentation on the web, but just to note that you must also call Dispose (or use the using pattern) with the SQLCeCommand object when running on Windows CE devices.  Surprisingly the same code runs perfectly without the Dispose on the Windows XP platform. see http://support.microsoft.com/?scid=kb%3Ben-us%3B824462&x=4&y=15

• Anonymous
  March 24, 2008
  If you're an avid PS3 gamer, then you know the importance of saving your progress for future game play. Pls, help me!

• Anonymous
  June 18, 2009
  We are using sql ce 3.5 and have implemented a sql connection pool that stores the connections for threads when a thread requests a connection to a database.  If the connection is no longer is in use for a period of time we dispose the command and connection to the database.  Our monitor thread is triggered by a timer that goes off incrementally that checks the state of each of the threads connections and cleans up any connections if they meet the criteria.  For some reason this implementations bleeds memory and eventually with enough connections will run out of virtual memory for a process.  As a test in our freeConnection routine I moved the connection dispose and command dispose to be done on each of the freeConnection called (same code that is executed in the timer call) and the resourced seem to get freed up correctly.  The only difference I can see is that the timer calling the monitor code is on a separate thread then the one owning the connection so I was wondering if this has an impact on the actually disposing of the connections and commands in the underlying implementation of SQL CE. Thanks Jake