SQLCE 2.0: Calling Engine.Dispose() after every query?

[kmsuzuki]

After making several hundred queries against a SQL CE 2.0 database
(through NetCF/ADO.NET), I begin getting a SqlCeException: "Not enough
storage is available to complete this operation."

Microsoft speaks to this situation in the following hotfix:
http://support.microsoft.com/?kbid=827837

When I contacted them to receive the hotfixed ssce20.dll, they
described the problem as SqlCeDataReader and SqlCeDataAdapter not
releasing their memory resources after they went out of scope. In
addition to using the hotfixed binary, they also advised me to call
SqlCeEngine.Dispose() after every query to force SQL CE to release
resources, as shown in the "finally" block of the code below.

I have a couple of questions about this:

(1)
Will this cause a lot of performance overhead for me, especially if my
application makes frequent queries using the following code?

(2)
Can a cache an instance of SqlCeEngine and call Dispose() on that
cached instance repeatedly, so I can avoid having to instantiate a new
SqlCeEngine each time?


SqlCeConnection conn = null;

try
{
conn = new SqlCeConnection(connectionString);
dbCmd.Connection = conn;
dbCmd.Connection.Open();
dbCmd.ExecuteReader(CommandBehavior.CloseConnection);

<use the reader>
}
finally
{
dbCmd.Connection.Close();

// Add the following code to release resources?
SqlCeEngine engine = new SqlCeEngine(connectionString);
engine.Dispose();
}

 

[Guest]

I doubt they requested you call Dispose on the engine. You need to call
Dispose ont he Reader objects when you are done with them so their finalizer
runs without waiting for a GC.

-Chris

 

[Fredrik Ree]

Receiving the same error message and searching for a solution, I came
across this helpful article on Microsoft Support regarding an issue with
SqlCeDataAdapter :

http://support.microsoft.com/kb/824462/

You need to dispose the SqlCeDataAdapter object and its command objects,
not the engine.

/Fredrik