Performing a delete on a DataSet and writing the DataSet back tothe DB

[Markus Palme]

Hi !

Following situation: I have a DataSet containing all the data of the
table 'appointments'.

Now, I want to delete a row in the DataSet AND save it back to the DB.
I tried this:

//
OleDbCommand command = new OleDbCommand("DELETE FROM appointments WHERE
id = @ID");
OleDbConnection connection = connectToDB();
command.Parameters.Add("@ID", appointment.ID);
command.Connection = connection;
command.ExecuteNonQuery();
\\


This deletes the data in the database. But it stays in the dataset. But
I don't want to load the DataSet again and again. So I tried the
following approach:

//
adapter = new OleDbDataAdapter("SELECT id FROM appointments", connection);
cb = new OleDbCommandBuilder(adapter);
string filter = "ID = '" + appointment.ID + "'";
foreach(DataRow row in dataSet.Tables["appointments"].Select(filter))
{
eventDataSet.Tables["appointments"].Rows.Remove(row);
}
adapter.Update(eventDataSet, "appointments");
\\

This deletes the rows in the dataset, but they remain in the database.
So, how can I do both in one step ? Or is there a general mistake in my
approach of keeping the dataset all the time ?

Thanks in advance
Markus

 

[Miha Markic]

Hi Markus,

OleDbConnection connection = connectToDB();
OleDbCommand command = new OleDbCommand("DELETE FROM appointments WHERE id =
@ID");
command.Parameters.Add("@ID", typeof(System.Int32)); // I presume
that typeof id column is int32
command.Connection = connection;

adapter = new OleDbDataAdapter("SELECT id FROM appointments", connection);
// select is not important in update
adapter.DeleteCommand = command ;
connection.Open();
try
{
adapter.Update(eventDataSet);
}
finally
{
connection.Close();
}

This should do the job assuming I didn't put a typo in there.
If you want also insert/update capability you might create respective
commands and assign them to adapter.InsertCommand/UpdateCommand.
You might also want to embed the Update operation within
BeginTransaction/EndTransaction.