Found a bug in SqlHelper's UpdateDataset() method
UPDATE: This is for version 2.0. I just did a few searches on the GotDotNet workspace and saw than another fellow had reported the same thing in their forum. But there were no replies.
UPDATE 2: I couldn't get into their Bug Tracker coz of some Passport error before. But I can now see that the problem with the UpdateDataset() method is on the list. I guess it will be fixed in version 3.
After a couple of hours of debugging I finally found the bug. It wasn't in my code, it was in the UpdateDataset() method of SqlHelper!
I had a strongly typed DataSet with 2 tables in it (with a relation between them) that I wanted to add to the database. It was an order (in one table) with several order details (in another table). Problem was the order details were never written to the database!
So, first I called on SqlHelper.UpdateDataset() for the order data, then I did the same with the order details, but the problem was that the UpdateDataset() method calls dataSet.AcceptChanges() after the dataAdapter.Update(), and this changes EVERY datarow and datatable in the whole dataset! The result of that was that my order details were never written to the database, because they were flagged in the RowState as "unchanged". The correct thing (I guess) would be to accept changes on the affected table only as the code at the bottom of this post shows.
This is the original code with the error in it:
Public Overloads Shared Sub UpdateDataset(ByVal insertCommand As SqlCommand, ByVal deleteCommand As SqlCommand, ByVal updateCommand As SqlCommand, ByVal dataSet As DataSet, ByVal tableName As String)
If (insertCommand Is Nothing) Then Throw New ArgumentNullException("insertCommand")
If (deleteCommand Is Nothing) Then Throw New ArgumentNullException("deleteCommand")
If (updateCommand Is Nothing) Then Throw New ArgumentNullException("updateCommand")
If (dataSet Is Nothing) Then Throw New ArgumentNullException("dataSet")
If (tableName Is Nothing OrElse tableName.Length = 0) Then Throw New ArgumentNullException("tableName")
' Create a SqlDataAdapter, and dispose of it after we are done
Dim dataAdapter As New SqlDataAdapter
Try
' Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand
dataAdapter.InsertCommand = insertCommand
dataAdapter.DeleteCommand = deleteCommand
' Update the dataset changes in the data source
dataAdapter.Update(dataSet, tableName)
' Commit all the changes made to the DataSet
' This is a buggy one, it resets other tables RowState to "unchanged" /Johan
dataSet.AcceptChanges()
Finally
If (Not dataAdapter Is Nothing) Then dataAdapter.Dispose()
End Try
End Sub
And this is how it should look:
Public Overloads Shared Sub UpdateDataset(ByVal insertCommand As SqlCommand, ByVal deleteCommand As SqlCommand, ByVal updateCommand As SqlCommand, ByVal dataSet As DataSet, ByVal tableName As String)
If (insertCommand Is Nothing) Then Throw New ArgumentNullException("insertCommand")
If (deleteCommand Is Nothing) Then Throw New ArgumentNullException("deleteCommand")
If (updateCommand Is Nothing) Then Throw New ArgumentNullException("updateCommand")
If (dataSet Is Nothing) Then Throw New ArgumentNullException("dataSet")
If (tableName Is Nothing OrElse tableName.Length = 0) Then Throw New ArgumentNullException("tableName")
' Create a SqlDataAdapter, and dispose of it after we are done
Dim dataAdapter As New SqlDataAdapter
Try
' Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand
dataAdapter.InsertCommand = insertCommand
dataAdapter.DeleteCommand = deleteCommand
' Update the dataset changes in the data source
dataAdapter.Update(dataSet, tableName)
' Commit all the changes made to the DataSet
' Commit changes to THIS TABLE ONLY! /Johan
dataSet.Tables(tableName).AcceptChanges()
Finally
If (Not dataAdapter Is Nothing) Then dataAdapter.Dispose()
End Try
End Sub
It's late, maybe I'm wrong, but things work way better now. Am I wrong? I'll try to notify the devs about this.