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

View products that this article applies to.

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.

Back to the top

RESOLUTION

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

Back to the top

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.

Back to the top

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 (http://support.microsoft.com/kb/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

Back to the top