SqlDataAdapter, DataSets and deleting rows

[Christoph]

I'm delving into using ADO.NET for the first time. In fact, I've never
done any database work using C# at all so all of this is new to me.
Please bear that in mind if I am asking stupid questions.

In reading the documentation for the "Update()" method of the
SqlDataAdapter class, I see that it "Calls the respective INSERT, UPDATE,
or DELETE statements for each inserted, updated or deleted rows in the
specified array of DataRow objects". I've got the INSERT and UPDATE
parts working great but I'm having problems with the DELETE part. It
doesn't
matter how many rows I delete from the DataSet, when I call the Update()
method, those rows are not deleted. Why? How does the Update() method
know which rows have been deleted so that it knows which rows would need
to be deleted from the database?

Here's the sample code I'm using. Please note that this is just a test
method
created so that I can just see it working. This is by no means meant to be
efficient or do anything meaningful.

private void doUpdateSQL() {

DataRow oDR;

try {

SqlConnection myConn = getSqlConnection();

SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = new SqlCommand("SELECT * FROM
Employees", myConn);
SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);

myConn.Open();

DataSet ds = new DataSet();
myDataAdapter.Fill(ds, "Employees");

// Removing last record

ds.Tables["Employees"].Rows.Remove(ds.Tables["Employees"].Rows[ds.Tables["Em
ployees"].Rows.Count-1]);

myDataAdapter.Update(ds);
ds.AcceptChanges();

//code to modify data in DataSet here
//String SearchID = "1";
oDR = ds.Tables["Employees"].Rows[0]; //.Find(SearchID);

oDR.BeginEdit();
oDR["FirstName"] = "Anne";
oDR["LastName"] = "Davolio";
oDR.EndEdit();

//Without the SqlCommandBuilder this line would fail
myDataAdapter.Update(ds, "Employees");

DataRow newDR = ds.Tables["Employees"].NewRow();
newDR.BeginEdit();
newDR["FirstName"] = "New";
newDR["LastName"] = "User";
newDR.EndEdit();

ds.Tables["Employees"].Rows.Add(newDR);

myDataAdapter.Update(ds, "Employees" );

myConn.Close();

} catch( Exception oErr ) {
tbSQLOutput.Text += "Error:\r\n" + oErr.Message;

}
}

Any pointers to what I might be missing or misunderstanding would
be very much appreciated!!

thnx,
Christoph

 

[Jon Skeet [C# MVP]]

I'm delving into using ADO.NET for the first time. In fact, I've never
done any database work using C# at all so all of this is new to me.
Please bear that in mind if I am asking stupid questions.

In reading the documentation for the "Update()" method of the
SqlDataAdapter class, I see that it "Calls the respective INSERT, UPDATE,
or DELETE statements for each inserted, updated or deleted rows in the
specified array of DataRow objects". I've got the INSERT and UPDATE
parts working great but I'm having problems with the DELETE part. It
doesn't
matter how many rows I delete from the DataSet, when I call the Update()
method, those rows are not deleted. Why? How does the Update() method
know which rows have been deleted so that it knows which rows would need
to be deleted from the database?

Because the rows which have been deleted are still meant to be in the
DataTable, just with a state of Deleted.

The problem you've got is that you're calling Remove on the
DataRowCollection rather than just calling Delete on the row itself.
You also shouldn't call AcceptChanges yourself - the adapter will do
that.

 

[Christoph]

Because the rows which have been deleted are still meant to be in the

DataTable, just with a state of Deleted.

When I query the table, those rows are still being returned as part of the
result set. Should I see that they have a different state?
Also, does this mean that if I want them physically deleted (such that they
do not get returned as part of the result set), I'll need to manually delete
them myself with a DELETE query?

The problem you've got is that you're calling Remove on the
DataRowCollection rather than just calling Delete on the row itself.

Only because I wasn't aware of that method. Shouldn't both methods to
basically the same thing?

You also shouldn't call AcceptChanges yourself - the adapter will do
that.

Noted. Thanks.

Chris

 

[Jon Skeet [C# MVP]]

When I query the table, those rows are still being returned as part of the
result set. Should I see that they have a different state?

You need to be very careful with terminology here - note that I said
they're meant to be in the DataTable, not in the original database
table. What did you mean when you wrote "When I query the table"?

Also, does this mean that if I want them physically deleted (such that they
do not get returned as part of the result set), I'll need to manually delete
them myself with a DELETE query?


Only because I wasn't aware of that method. Shouldn't both methods to
basically the same thing?

No. Remove removes it from the DataTable completely; Delete marks it
for deletion so the DataAdapter knows to execute a delete command.