DataSet.Merge and Transferring Data Using ADO.NET to move data from one table to another by William Ryan

Just about every week on the newsgroups I see a post that looks something like this:

"Please help.  I have a table and I want to transfer the data into another table.  I have my dataadapters configured, I merge the to datasets into one, then call update but nothing happens"

It's almost comical at this point.  Well, it's very simple to fix.  To understand what's happening let me go through a few things.  First, when a DataAdapter, be it a SqlDataAdapter, OleDbDataAdapter or whatever else, fills a DataSet or a DataTable, its default setting for AcceptChangesDuringFill property set to true.  

To understand why this is significant, you need to understand what DataSet's AcceptChanges method does. As you know, a Dataset is a collection of one or more DataTables.  A Datatable is a collection of DataRow objects and DataColumn objects.  DataSets and DataTables are locally cached data holders which your program manipulates.  When you delete a row in a DataTable, it isn't really deleted like it is in a DataBase.  If you fire a SQL statement like DELETE SomeRecord FROM Sometable where SomeColumn = 'Whatever', every row where SomeColumn equals Whatever is deleted.  If you referenced a DataRow in a DataTable and Deleted it, all that happens is that its RowState is changed to Deleted.  This way, when you call the Update method of the dataadapter, it knows that this row should be used in conjunction with its DeleteCommand.  Many newcomers mistakenly use Remove interchangeably with Delete and find out that they are very different.  This shows the difference:

Dim ds As New DataSet Dim dt As New DataTable    ds.Tables.Add(dt)    dt.Rows.Remove(SomeRow)    ds.HasChanges() 'false    dt.Rows(0).Delete()    ds.HasChanges() 'True

The difference here is that Deleting a Row simply marks its Rowstate to Deleted whereas Remove takes it out of the Rows Collection.

When you change a value in a Row, the RowState for that row is changed to Modified.  When you call Update on the DataAdapter, everything with a RowState of Modified is used in conjunction with the UpdateCommand (provided you have one) to update existing records.  Similiarly, if you add a Row to the collection, it adds the row to the Rows collection and then changes its RowState to Added:

Dim dro As DataRow = dt.NewRow dro(0) = "whatever" dro(1) = "somethign else" dt.Rows.Add(dro) Debug.WriteLine(dt.Rows(0).RowState)  ' DataRowState.Added

So, when you call update, if it sees a Row with a Rowstate of Added, then it uses the InsertCommand (if provided) to insert the row into the database.  If none of these things occured, then the RowState will be Unchanged.

Now, many people are aware of the Merge method of the DataSet, DataTable, DataRow and they mistakenly believe that Merging two datasets will cause the merged datasets rows to have a RowState of Added.  Then they try to use a DataAdapter to add these rows and nothing happens.  Let me stop here for a second.  If you test your DataSet with the HasChanges method and it returns false, you can call Update against it until the cows come home and nothing is going to happen to the database.  Only if there are changes registered and only if there is a valid Insert/Delete/Update command is anything on the back end going to happen.  There are no exceptions.

This is a common source of confusion because people will merge a dataset, test to see if it has changes, find out it doesn't, call update anyway and somehow expect changes to happen.  They won't.  This myth that Merge somehow changes rowstate is as pervasive as it is incorrect.  

There's one more thing I'd like to mention before I finally show you how to move tables in just 5 lines of code (you can actually do it in two provided you have your dataadapters configured correctly).  You can call the AcceptChanges method manually as well as the RejectChanges method. If you call RejectChanges, you basically undo everything you have done to your Datatable/Dataset.  Now, RejectChanges simply undoes all PENDING changes, so if you removed a Row, it doesn't have a pending change and this won't be added back.  However, everything marked Added, Modified or Deleted will go back to it's original state (which means added rows won't be there any more).  However, AcceptChanges has a similar effect.  It basically commits all the pending changes, so deleted rows are actually removed, and the rowstate of everything else is set to unchanged. Finally, this will set the current state of the datarow to be the "original" value so the DiffGram will show no changes.  So, if you made a bunch of changes, then right before you called DataAdapter.Update, you called either DataTable.AcceptChanges or DataTable.RejectChanges (DataSets and DataRows all have the same method and I'm using them interchangeably), you'd never see the changes reflected in your database.  Another common mistake is to call AcceptChanges before calling Update.  Now, when you call DataAdapter.Update, it automatically calls AcceptChanges by default so if the update was successful, your backend DB and local DataSet will match each other (well, in most cases, there are some exceptions but I don't want to get into that right now b/c they are rare).

So, what does any of this have to do with moving data from one Table to another?  Simple.  By Default, the DataAdapter has AcceptChangesDuringFill set to true.  So when  you fill a DataTable for instance, and immediately check to see if it has changes, it won't.  Since Merge doesn't add new rows and the rowstate of these rows is Unchanged, you can merge all you want and Update will never submit any of your changes.  However, by setting the AcceptChangesDuringFill property to false, the RowState of Every Row will now be Added!  So, if you used the same dataadapter and called update, it would try to insert every record back into the table it came from.  This would probably blow up because you have keys on your tables (and if you don't you should be unemployed).  However, if you wanted to move that data to another table or a different database, all you'd need is a dataadapter configured to point to that other database table.  The schemas would need to match to the extent of the original select query (or whatever your INSERT command decided to submit).

Anyway, I have a Table called Employees which is filled by daSource.  I have an empty table with the same schema in another database called Employees_Destination.  Employees has 203 rows in it, Employees_Destination has 0.  All I do is set the AcceptChangesDuringFill property of daSource set to false, fill a Dataset with it, take the same dataset and call update with daDestination on it.  Since you can set AcceptChangesDuringFill in the properties window, all it takes is a call to daSource.Fill(dataSet, "SomeTable") and then a call to daDestination(dataSet, "SomeTable") and we will have moved all of the data to the second table.

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load        daSource.AcceptChangesDuringFill = false End Sub Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click   MessageBox.Show("DataSet currently has Changes: " & ds.HasChanges.ToString) 'True   Dim i As Integer = daDestination.Update(ds, "TransferData") '203 records   Label2.Text = "Transferred Records: " & i.ToString 'i = 203 End Sub Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadData.Click     ds.HasChanges() 'false     daSource.Fill(ds, "TransferData")     MessageBox.Show("Source Table has " & ds.Tables(0).Rows.Count.ToString & " rows") '203     ds.HasChanges() 'true     Label1.Text = "Source Table: " & ds.Tables(0).Rows.Count.ToString End Sub

You'll see with the comments what's happening. First we click btnLoad and we load our dataset (which is declared at the module level).  Then we call btnImport.  Since ds.HasChanges and daDestination has valid Update/Insert/Delete commands, it inserts each row that was loaded.  If we didn't have AcceptChangesDuringFill  set to false when we filled the dataset, then the update would do nothing.  Also, you can set this in the properties window, I did it in Form2_Load just to make it clear that's what I was doing.  If you do this in the designer, and take out the code I inserted for UI effects (HasChanges, MessageBoxes, Label1.Text)  it takes 2, yes 2 lines of code to transfer this data.

I know a lot of people who won't use Update or DataAdapters, but you have to admit, this would be a WHOLE lot harder doing any other way through code.  Now, I will say that ADO.NET is not meant to be a data transfer tool.  This is a very slow and inefficient way to transfer data if you are going from server to server. However, it's common that you may be transferring this to an XML file or non-Client/Server DB and if nothing else, it's a good way to illustrate the way ADO.NET works.