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