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

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 SQL Server 2005 Mobile 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.