Microsoft KB Archive/824462

= SqlCeCommand objects are not automatically disposed if you use a SqlCeDataAdapter object =

Article ID: 824462

Article Last Modified on 11/14/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition 2.0
 * Microsoft SQL Server 2005 Compact Edition

-



SYMPTOMS
If you use the SqlCeDataAdapter object to populate a DataSet object, and you do not explicitly call the Dispose method for all the associated SqlCeCommand instances, you may receive the following error message:

Error Code: 8007000E

Message: Not enough storage is available to complete this operation.

Note The type of SqlCeCommand instances may be select, insert, update, or delete.



RESOLUTION
To resolve this problem, explicitly call the Dispose method for the SqlCeCommand instances when you use SqlCeCommand instances with a SqlCeDataAdapter object.



MORE INFORMATION
The following code sample shows how to populate a DataSet object with rows from a Microsoft SQL Server 2000 Windows CE Edition or Microsoft SQL Server 2005 Compact Edition database table by using a SelectCommand instance with the SqlCeDataAdapter object: public static DataSet LoadData {   string sqlstring = &quot;&quot;;

// Make the connection to the SQL Server CE data source SqlCeConnection conn = new SqlCeConnection(&quot;Data Source=&quot;);

// Create the SqlCeDataAdapter object sqlCeDataAdapter da = new SqlCeDataAdapter;

// Create the DataSet object DataSet ds = new DataSet;

try {       sqlstring = &quot;select name from mytable where name = ?&quot;;

// Create the SelectCommand instance to run a select query da.SelectCommand = new SqlCeCommand;

// Set SelectCommand object properties da.SelectCommand.Connection = conn; da.SelectCommand.CommandText = sqlstring; da.SelectCommand.Parameters.Add(new SqlCeParameter(&quot;name&quot;, System.Data.SqlDbType.NVarChar, 30)); da.SelectCommand.Parameters[&quot;name&quot;].Value = name;

// Populate the DataSet object da.Fill(ds,&quot;name&quot;); }

catch (SqlCeException sqlx) {           ShowErrors(sqlx); }

catch (Exception x)       { MessageBox.Show(x.Message.ToString); }

finally {       //  Explicitly dispose the SelectCommand instance da.SelectCommand.Dispose;

da.Dispose; }

return ds; } Note The SelectCommand instance is disposed by explicitly calling the Dispose method in the Finally block.

The following code sample provides a generic method that can be used to clean up all the command objects that are associated with the SqlCeDataAdapter object, such as SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand objects: public System.Data.IDbDataAdapter DisposeAdapter (System.Data.IDbDataAdapter dbAdapter) {   if (dbAdapter is SqlCeDataAdapter) {       // Create the SqlCeCommand object and assign the SelectCommand object SqlCeCommand cmd = dbAdapter.SelectCommand;

// Dispose the SqlCeCommand object if (cmd != null) {                   cmd.Dispose; }

cmd = dbAdapter.InsertCommand; if (cmd != null) {                   cmd.Dispose; }

cmd = dbAdapter.UpdateCommand; if (cmd != null) {                   cmd.Dispose; }

cmd = dbAdapter.DeleteCommand; if (cmd != null) {                   cmd.Dispose; }   }

return null; } Note The generic method in this sample accepts an object of type IDbDataAdapter as the input parameter, disposes the command objects that are associated with the IDbDataAdapter object, and then returns the resulting IDbDataAdapter object.

