Efficiently Using ADO.NET - IV

Undertsanding how Updates go bad

by William Ryan

I'm going to assume that you know basically how DataAdapters work and how to fill a dataset with them.  So what happens after you fill your dataset.  Typicall, you start making changes to that data.  You might modify  a value in a row for instance.  You might delete   a row. You might  add   a row.  And at some point, you call the Update method of the dataadapter and if all works well, the changes in the dataset are committed to your database.

We know that if you use the visual tools, when you configure a dataadapter, you choose a connection, then choose a table(s), pick your fields and hit Next.  At this point, you'll be told what succeeded - the possibilities are Update Command Succeeded, Insert Command Succeeded , Delete Command Succeeded and Select Command Succeeded.  As long as you have a valid Key on your table and aren't using any joins, this works 99% of the time.

Now you'll typically fill a dataset, modify the data and then submit an update.  Very simple.  But how does calling DataAdapter.Update cause the update to happen.  To understand this you need to take a look at the logic that's generated:

A Typical Snippet looks something like this:

this.sqlUpdateCommand1.CommandText = "UPDATE Employees SET LastName = @LastName, FirstName = @FirstName, Title = @Title" +", TitleOfCourtesy = @TitleOfCourtesy, BirthDate = @BirthDate, HireDate = @HireDa" +"te, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode" +    ", Country = @Country, HomePhone = @HomePhone, Extension = @Extension, Photo = @P" +"hoto, Notes = @Notes,

The Paramater declarations look something like this:

this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 20, "LastName")); this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar, 10, "FirstName")); this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Title", System.Data.SqlDbType.NVarChar, 30, "Title")); this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@TitleOfCourtesy", System.Data.SqlDbType.NVarChar, 25, "TitleOfCourtesy")); this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@BirthDate", System.Data.SqlDbType.DateTime, 8, "BirthDate"));

You'll notice that the last parameter in teh declaraion looks like a field name. Well, it is. This is the field that the Paramater maps back to.

So, when you call dataAdapter.Update, it first checks to see if the dataSet.HasChanges meaning the .HasChanges property evaluates to true.  If this doesn't evaluate to true, call DataAdapter.Update all year and nothing is going to happen.  Want Proof?

private void cn_StateChange(object sender, System.Data.StateChangeEventArgs e) {     MessageBox.Show("Connection State is Currently: " + e.CurrentState.ToString()); } private void btnProve_Click(object sender, System.EventArgs e) {     ds = new DataSet();     da.Fill(ds, "TestTable");    //Just filled table, rowstate is Unchanged for all rows   //Now, we'll prove that if the DataSet HasChanges == false   //Calling update all year does NOTHING!   //Keep your eye on the StateChanged Event Handler..it'll only   //fire once when it opens the connection for fill, and once   //more when it closes it.  Never during the loop    Debug.Assert(!ds.HasChanges(), "Changes are present");    for(int x = 0; x < 1000; x++)<BR>    {        da.Update(ds.Tables[0]);    } }

I added an event handler for the Connection's StateChanged Event.  When fill is called, the dataadapter opens up the connection, calls the SELECT command then closes the connection. We proved this because the MessageBox shows twice, once indicating Open and once Indicating closed.  This is exactly what we would expect. Now, we verify that the dataSet doesn't have any changes... the Debug.Assert(!ds.HasChanges()) succeeds.

Now, this means we have NO changes to the dataset which should be obvious b/c all we did was fill it.  To prove that calling update on a table without changes does nothing, I stuck it in the middle of a loop that executes 1000 times.  There's a myth that every time you call Update, the DataAdapter loops through all of the records and tries to send them back to the db.  This is silly.  If ADO.NET architecture were built like this, it would offset any possible benefit of the disconnected model..

So, what happens in our loop?  Nothing, You won't see one MessageBox.  That's b/c as soon as it's determined that HasChanges is false, nothing will occur.  You could make this number 1000000 and the results will be the same.  That should be enough to prove it to any cynic.

As such, if you aren't getting the results you expect (database being updated), then the first thing to do is verify HasChanges.

Another thing we see often is people calling AcceptChanges right before the update. This is very bad.  If you do this, HasChanges will be false and your update will never work.  Since Update calls AcceptChanges, there's no need to call it on your own.  Acceptchanges first deletes the rows marked Deleted, then sets the rowstates of the previously modified rows to UnChanged.

----This is a perfect seque into another topic. What happens when it does have changes?

Well, when you call update , it goes row by row and examines the rowstate. If a row is Added, it takes the values of the rows, maps them to the parameters specified in the Insert command (which are the values of that row alone) and calls executeNonQuery.  If it sees a row deleted, it does the same, it just calls the delete command.  It its Modified, then it calls the Update Command in the same manner. So you can see, rowstate is everything.    So, if your rowstate is Added, but it already exists in the database, what will happen?  If there's a key, you'll get a dupliate key violation.  If not, you'll add a second copy of that row.

Now, at the end of the Update, it calls AcceptChanges on the Dataset.  This removes the deleted rows.  Many people don't believe this so I'm going to prove it.  However, let me draw one big distinction.  If you call Remove instead of Delete, the Row is immediately taken of the dataset/datatable.  The  rowcount will immediately change.  However, if you delete 10 rows, the rowcount will be the same as before you deleted them until you call update or until you call AcceptChanges or RejectChanges.

Examine the code below, this should settle any disputes on this subject:

private void btnTestDelete_Click(object sender, System.EventArgs e) {     ds = new DataSet();     da.Fill(ds, "TestTable");    int i = ds.Tables[0].Rows.Count;//9    Debug.Write("Number of Rows Before 'Delete'" + i.ToString());    for(int x = 0; x < 5; x++)<BR>    {        ds.Tables[0].Rows[x].Delete();    }    //Provet they are the same by Asserting i == Current Row Count    Debug.Assert(i == ds.Tables[0].Rows.Count, "Counts Don't Match");    //Assertion Passes, proving Delete doesn't affect RowCount , Yet    ds.Tables[0].AcceptChanges();    //After AcceptChanges - or da.Update, Deleted Rows are actually   //deleted - Update calls AcceptChanges    Debug.Assert(i ==ds.Tables[0].Rows.Count, "Counts Don't match");    //Fails now b/c AcceptChanges Removed the rows    i = ds.Tables[0].Rows.Count;//Reset i    ds.Tables[0].Rows.Remove(ds.Tables[0].Rows[0]);    //Remove actually gets rid of the row, the assertion will fail   //now    Debug.Assert(i == ds.Tables[0].Rows.Count); }

Notice that the row counts are the same after we call delete.  It's only when we call AcceptChanges or DataAdapter.Fill (which calls AcceptChanges) that the rowcount changes.  But with Remove, it's gone immediately.  So if it's gone immediately, what happens on the DB Side?  NOTHING!  Why?  Because the Adapter is looping through the datatable, and doesn't find that row so it keeps on moving.

You could delete every row in a datatable and call update and all of the rows would be deleted from the db (provided the Delete command was legit).  But if you call Remove on all of them , the adapter has nothing to do.  HasChanges will be false so no updating will go on.

If you run through this, you'll realize that things work very methodically.  There's no magic involved and there's nothing "black box" about it.   It's all about rowstate!

Considering it takes one line of code to fill a properly configured table and one line to submit all the changes back to the db, and considering how much code this would otherwise take, you realize how important this is to learn.