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

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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 = "";	//  Make the connection to the SQL Server CE data source	SqlCeConnection conn = new SqlCeConnection("Data Source=<completePath of SDF file>");	//  Create the SqlCeDataAdapter object	sqlCeDataAdapter da = new SqlCeDataAdapter();	//  Create the DataSet object	DataSet ds = new DataSet();	try	{		sqlstring = "select name from mytable where name = ?";		// 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("name", System.Data.SqlDbType.NVarChar, 30));		da.SelectCommand.Parameters["name"].Value = name;		//  Populate the DataSet object		da.Fill(ds,"name");	}	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.
REFERENCES
For more information about using the SqlCeDataAdapter object to retrieve data from a SQL Server CE database, click the following article number to view the article in the Microsoft Knowledge Base:
813731 How to retrieve data from a SQL Server CE 2.0 database or from a SQL Server 2005 Compact Edition database and save the data in an XML document
Properties

Article ID: 824462 - Last Review: 11/14/2007 17:39:41 - Revision: 3.5

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

  • kbquery kbdatabase kbprb kbdataadapter kbappdev kbinfo kbcodesnippet KB824462
Feedback