I'm proposing that you just set your own DeleteCommand in the Data Adapter
as in the sample code below. The CommandBuilder doesn't do anything magical
(in fact we almost didn't ship it). It's merely a helper class so that in
some cases you can avoid creating commands by hand. In fact you'll get
better perf if you hand craft your Insert, Update, and Delete commands.
//Create and connect to DB
string dbFile = @"\temp.sdf";
string cnString = "Data Source = " + dbFile;
if(File.Exists(dbFile))
File.Delete(dbFile);
SqlCeEngine en = new SqlCeEngine(cnString);
en.CreateDatabase();
en.Dispose();
SqlCeConnection cn = new SqlCeConnection(cnString);
cn.Open();
//Create Test Schema
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "Create Table t1 (col1 int, col2 nvarchar(20), col3
uniqueidentifier DEFAULT newid(), primary key (col1,col2));";
cmd.ExecuteNonQuery();
cmd.CommandText = "Insert into t1 (col1,col2) values (5,'Hello');";
cmd.ExecuteNonQuery();
cmd.CommandText = "Insert into t1 (col1,col2) values (5,'Hello2');";
cmd.ExecuteNonQuery();
cmd.Dispose();
//Setup DataAdapter
SqlCeDataAdapter da = new SqlCeDataAdapter("Select * from t1",cn);
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
//Create custom delete command
da.DeleteCommand = new SqlCeCommand("DELETE FROM t1 WHERE col1 = ? and col2
= ?",cn);
SqlCeParameter p1 = new SqlCeParameter("@param1",SqlDbType.Int);
p1.SourceVersion = DataRowVersion.Original;
p1.SourceColumn = "col1";
SqlCeParameter p2 = new SqlCeParameter("@param2",SqlDbType.NVarChar);
p2.SourceVersion = DataRowVersion.Original;
p2.SourceColumn = "col2";
da.DeleteCommand.Parameters.Add(p1);
da.DeleteCommand.Parameters.Add(p2);
//Now that the DataAdapter commands are populated the DA can be used
normally
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].Rows[0].Delete();
da.Update(ds);
--
This posting is provided "AS IS" with no warranties, and confers no rights.
braed_dyad said:
mmmm .. not sure .
I'm updating SQL CE data after it's been modified (add/change/delete)
within a datagrid ... I remove the row from the datagrid using:
myCurrencymaster.removeAt(currencymaster.position)
and add rows using the datatable's rows.add method ..
the data adaptor's .udpate handles all the the grid add, changes and
deletes ..so I can test the dataset's 'haschanges.deleted' property and
know that some rows have been deleted but I assume to execute my own delete
I will have to trap and save the row's key values at the 'removeAt' stage ?
or is there a way of reading thru the datatable rows and tested for deleted
rows?
also I still want to execute the command builders' .update command to let
it handle the adds/modifies .. but will it also try to execute the deletes
because the haschanges.deleted is set?? I think the haschanged. properties
are 'read only' so I wont' be able to 'manually' turn off the .deleted will
I ??
