Merging Datasets and Updating

After about 4 days of frustration I finally found out how to get this working.

I have a table in a database that I am looking to move to the same table in another database. This seems like an ideal solution for datasets and it is. If you follow the MSDN documentation I found it doesn't work. I have been pulling my hair out and researching datasets for 4 days now trying to figure out why it wouldn't work. When you view the dataset after performing the merge all the data is there and when you perform the update it doesn't error out or throw an exception, however, it didn't work.

What was comforting through this learning experience is that I was no the only one running into this issue. I found hundreds of posts from 2004 to present about this very same issue but not one solution, until today. Some of the seasoned .NET programmers probably already know why, but for those that are still learning I will tell you why.

When you create an adapter, any adapter, and fill your dataset, all rows will be marked as unchanged. This sounds reasonable since you haven't changed anything yet but when you are migrating data it will not work for you. The reason it doesn't work is when you call the update method on your adapter to move it to the new location the method is going to look for rows that are added, deleted, or changed. It is going to completely ignore the rows marked as unchanged. This I understood, what I couldn't figure out was how to mark those rows as changed without manually iterating through each row and making some change.

Enter William Ryan and his great article on "DataSet.Merge and Transferring Data". The way to get this to work is very simple, after you have set up your adapter and before you fill your dataset you need to set the AcceptChangesDuringFill property to false. The reason you set it to false is to get each row in the dataset to be marked as added. Then when you update it all the rows will be added.

I hope this helps some one out and big thanks to William for a great write up.