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