Full Title: "How to update a dataset with related tables and identity columns from a Windows Forms application by using a Web service in Visual Basic .NET"

Based on the MSDN article 310350 (How to update parent-child data with an Identity column from a Windows Forms application by using a Web service in Visual Basic .NET, http://support.microsoft.com/kb/310350)

I wrote this piece of code to extend Microsoft’s example of a simple parent-child relationship. The function UpdateDataSet can accept as input an array of tables and their corresponding data adapters, perform all requested CRUD operations and return the dataset with rows having 'as expected' row state and version for merging back to client's datasets.

It is important that the data adapters and the data tables will be passed to UpdateDataSet function with a specific order, because when relations exists in the dataset and in database, the CRUD operations must be applied in the correct order. The order expected here is from the children to parent tables. For example, when we have two tables, ParentTable and ChildTable, the dataTables array must contain then in this order ChildTable, ParentTable with the same order for their data adapters in dataAdapters array.

The philosophy behind the code is this:

Situation:

1. We have a data access layer (DAL) hidden behind a web services layer and a client that communicates with web services using datasets
2. The datasets contain multiple tables with relations
3. The client requests from the web services to get such a dataset filled up with data
4. The client does multiple insert, update and delete actions on the dataset that currently holds
5. The client uses GetChanges to get all changed rows and sends those changed rows (as a dataset) back to the web services layer to update the database
6. The DAL updates the database and sends back to the client (through web services) the updated rows
7. The client gets the database updated rows and merges it back to the original dataset

Problem:

When the data adapters in the DAL do an update on dataset, they force an AcceptChanges on all rows of all tables. When a row was inserted in the dataset from the client side, the dataset detects identity columns an assign them virtual identity values. These values usually are identical with the actual identity that the database will assign, except two cases:

1. When a table in the client's dataset is completely empty, the dataset counter for the identity columns starts from 0
2. When another user inserts a record to the same table he gets the next available ID but the dataset on the first client thinks that this ID is still available.

So, when the data adapters finish updating the tables, they return to the client a dataset with "UnChanged" status and different IDs from what the client's dataset currently holds. The result is duplicate rows in the client's dataset with row state "UnChanged", with some of them not really exist in database.

Status:

Microsoft says that this behaviour is by design. And indeed, if you access the database directly from the client and your performance needs does not require using the GetChanges method, the data adapters will correctly update your dataset to reflect the actual database data. But when GetChanges is necessary to reduce your bandwidth consumption, you have a problem.

Solution:

The main idea is to manually handle the row states and versions of all rows, after they finish processing of data adapters as follows:

Row Versions:

1. All original row versions must exactly much the rows that the client currently holds, so that the Merge method on the client correctly understands witch rows to update in the original dataset.
2. All current row versions must exactly much the actual database rows, so that after the Merge and AcceptChanges on the client the dataset data will reflect the actual data in the database

Row States:

1. When a row is inserted we'll need to manually set the row version as previously described and maintain the Added row state of the row
2. When a row is deleted we'll need just to maintain the Deleted row state so that final Merge and AcceptChanges methods will remove the row from the dataset
3. When a row is updated we don't want to change anything, the Merge operation will correctly update the row in the client's dataset

Points of interest:

Error Handling

In order to manually process the row states and versions of rows while the data adapters are updating them, we use the RowUpdated event of each data adapter. Microsoft's example starts with direct examination of the StatementType of each event (to see what the actual action on the row was). That's not correct because when an error occurs (e.g. there is a foreign key violation with a table not included in the dataset) the status set on the event arguments UpdateStatus.SkipCurrentRow will completely hide the actual exception. So I choose to examine the Errors parameter and do nothing if an error has occurred, so that I get a proper exception in those cases.

Order of insert, update, delete

Microsoft chooses to process the row actions in this order: delete, modify, insert. That is very convenient because: a. When rows are deleted, their row state does not change, b. When the rows are modified, their row state does not change, c. When the rows are inserted, we manually set their row state to modified so that the client understands that these rows were modified compared to the rows that currently holds. At this point, we don't mind to set the row state to modified, because we already know that the modification of rows is already complete. But this processing order is not correct for all cases:

There are two cases that uniquely specify the order of actions that must be performed:

1. Deletion of records must occur before inserts
   Why: If a table has a column with unique values, and in the dataset you delete a row and then reinsert it with the same value for the unique column, then the new row cannot be inserted without deleting first the old row.
2. Modification of existing records must occur after inserts
   Why: If in the dataset you insert a new row in a parent table and then update a record from a child table to refer to the newly inserted parent row, you cannot update the child row in the database if the parent row not yet exists.

Conclusion:

The only possible order of actions (generally speaking, if we want to allow all types of actions when updating the dataset) is:

1. Delete (starting with children tables first, continuing up to the parent tables)
2. Insert (starting with the parent tables first, continuing down to the children tables)
3. Update (starting with the parent tables first, continuing down to the children tables)

But, when the order is Delete, Insert, Update, a new problem arises. Let’s examine what happens again:

a. When rows are deleted, their row state does not change, b. When the rows are inserted, we manually set their row state to modified so that the client understands that these rows were modified compared to the rows that currently holds, c. When we try to modify the rows, we won't know which rows have to actually be updated in the database and which rows have row state Modified because we manually set the so in the previous step.

So, before processing the rows in the correct order, we have to store the original action intension so we won't mess up by looking row states. For this purpose I use the custom TableRows object with three arrays to store initially inserted, updated and deleted rows.

Transactions

The whole process must be executed in a transaction scope so that we can roll back the changes if an error occurs. I've put two versions of UpdateDataSet method, what with a transaction parameter so that the process can participate in an existing transaction and one without a transaction parameter that creates and uses its own transaction.

For any comments, suggestions, bugs, ideas etc. please post here or contact me directly at [email protected]

Chris Georgakopoulos

 
Imports System.Data.SqlClient
 
Public Module DataAdapterHelper
 
'Author: [email protected]
 
Private Function GetDeletedRows(ByVal dataTable As DataTable) As DataRow()
 
'Returns an array of data rows with all the deleted rows contained in
 
'dataTable
 
Dim DeletedRows() As DataRow
 
If dataTable Is Nothing Then Return DeletedRows
 
DeletedRows = dataTable.Select(String.Empty, String.Empty, DataViewRowState.Deleted)
 
If DeletedRows.Length = 0 OrElse Not (DeletedRows(0) Is Nothing) Then Return DeletedRows
 
Dim RowCounter As Integer = 0
 
For Each Row As DataRow In dataTable.Rows
 
If Row.RowState = DataRowState.Deleted Then
 
DeletedRows(RowCounter) = Row
 
RowCounter += 1
 
End If
 
Next
 
Return DeletedRows
 
End Function
 
Private Sub RowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)
 
'Preserves the expected versions of rows, so that merge operations can occur
 
'without duplicate rows
 
'If the update operation has an error, then we do not interfere so that
 
'a proper exception will be thrown.
 
If e.Errors Is Nothing Then
 
'If we have an insert operation on a row, we need to return a row
 
'that has the following:
 
' a. The values that the client dataset auto generated on primary key
 
' columns as original state values
 
' b. the values that was generated by the database for the primary key
 
' columns as current state values
 
' c. RowState = Modified
 
If e.StatementType = StatementType.Insert Then
 
'Don't allow the AcceptChanges to occur on this row.
 
e.Status = UpdateStatus.SkipCurrentRow
 
'Holds the number of primary key column in the row table.
 
Dim NumberOfPrimaryKeys As Integer = e.Row.Table.PrimaryKey.Length
 
'Holds the actual primary key values that were returned from the database.
 
Dim CurrentKeys(NumberOfPrimaryKeys) As Integer
 
'Holds the initial primary key values as passed in the source dataset.
 
Dim OriginalKeys(NumberOfPrimaryKeys) As Integer
 
'Holds the original read-only state of the primary key columns, so that
 
'we can set it back after we manually update the primary key values.
 
Dim ReadOnlyKeys(NumberOfPrimaryKeys) As Boolean
 
'We get the actual primary key values that were returned from the database
 
'These values are in the Current DataRowVersion of each row.
 
For KeyCounter As Integer = 0 To NumberOfPrimaryKeys - 1
 
CurrentKeys(KeyCounter) = CType(e.Row(e.Row.Table.PrimaryKey(KeyCounter), DataRowVersion.Current), Integer)
 
Next
 
'We get the primary key values as they passed from the source dataset.
 
'These values are stored in the Original DataRowVersion of each row.
 
For KeyCounter As Integer = 0 To NumberOfPrimaryKeys - 1
 
OriginalKeys(KeyCounter) = CType(e.Row(e.Row.Table.PrimaryKey(KeyCounter), DataRowVersion.Original), Integer)
 
Next
 
'We get the original read only state for each primary key column. We'll
 
'change this to readonly = false just to be sure that we cat set the values
 
'in each column and then we'll set it back to were it was.
 
For KeyCounter As Integer = 0 To NumberOfPrimaryKeys - 1
 
ReadOnlyKeys(KeyCounter) = e.Row.Table.PrimaryKey(KeyCounter).ReadOnly
 
Next
 
'We make all primary key columns non readonly
 
For KeyCounter As Integer = 0 To NumberOfPrimaryKeys - 1
 
e.Row.Table.PrimaryKey(KeyCounter).ReadOnly = False
 
Next
 
' This is where you get a correct original value key stored in the row
 
For KeyCounter As Integer = 0 To NumberOfPrimaryKeys - 1
 
e.Row(e.Row.Table.PrimaryKey(KeyCounter)) = OriginalKeys(KeyCounter)
 
Next
 
'We force accepting changes so that when we set the actual value of each
 
'primary key column in the next step, the original values will be preserved
 
'and the row state goes to Modified.
 
e.Row.AcceptChanges()
 
'Now store the actual primary key value back into the primary key column.
 
For KeyCounter As Integer = 0 To NumberOfPrimaryKeys - 1
 
e.Row(e.Row.Table.PrimaryKey(KeyCounter)) = CurrentKeys(KeyCounter)
 
Next
 
'We set back the proper readonly state of each primary key column
 
For KeyCounter As Integer = 0 To NumberOfPrimaryKeys - 1
 
e.Row.Table.PrimaryKey(KeyCounter).ReadOnly = ReadOnlyKeys(KeyCounter)
 
Next
 
End If
 
'If we have a delete operation, we only need to preserve the deleted row state
 
'of the row, so we just prevent the adapter from doing an accept changes on
 
'deleted rows
 
If e.StatementType = StatementType.Delete Then e.Status = UpdateStatus.SkipCurrentRow
 
End If
 
End Sub
 
Public Function UpdateDataSet(ByVal dataAdapters() As SqlDataAdapter, ByVal dataTables() As DataTable, ByVal transaction As SqlTransaction) As DataSet
 
'Execute all CRUD operations on the rows contained in dataTables, using 
 
'the data adapters contained in dataAdapters.
 
'It is extremely important that the data adapters and the data tables
 
'will be passed here with a specific order because when relations exists
 
'in the dataset and in database, the CRUD operations must be applied
 
'in the correct order.
 
'The order expected here is from the children to parent tables. For example,
 
'when we have two tables, ParentTable and ChildTable, the dataTables
 
'array must contain then in this order ChildTable, ParentTable with the
 
'same order for their data adapters in dataAdapters array.
 
'The whole operation must be executed in a transactional scope so that we can undone
 
'partial operations if an error occurs.
 
If transaction Is Nothing Then Throw New Exception("No transaction supplied")
 
'Input parameter checks
 
If dataAdapters Is Nothing Then Throw New Exception("No dataAdapters supplied")
 
If dataTables Is Nothing Then Throw New Exception("No dataTables supplied")
 
If dataAdapters.Length = 0 Then Throw New Exception("No dataAdapters supplied")
 
If dataAdapters.Length <> dataTables.Length Then Throw New Exception("Number of dataAdapters does not much number of dataTables")
 
'Holds the number of the tables or data adapters
 
Dim NumberOfObjects As Integer = dataAdapters.Length
 
'Check that all tables supplied belong to the same dataset
 
Dim DataSet As DataSet = dataTables(0).DataSet
 
For ObjectCounter As Integer = 0 To NumberOfObjects - 1
 
If Not dataTables(ObjectCounter).DataSet Is DataSet Then
 
Throw New Exception("Found tables that belong to different dataSets")
 
End If
 
Next
 
Try
 
'Set all commands object to use the existing transaction and connection
 
For ObjectCounter As Integer = 0 To NumberOfObjects - 1
 
dataAdapters(ObjectCounter).UpdateCommand.Connection = transaction.Connection
 
dataAdapters(ObjectCounter).UpdateCommand.Transaction = transaction
 
dataAdapters(ObjectCounter).InsertCommand.Connection = transaction.Connection
 
dataAdapters(ObjectCounter).InsertCommand.Transaction = transaction
 
dataAdapters(ObjectCounter).DeleteCommand.Connection = transaction.Connection
 
dataAdapters(ObjectCounter).DeleteCommand.Transaction = transaction
 
Next
 
'This array will hold the initialy desired action for each row
 
Dim TableRows(NumberOfObjects) As TableRows
 
'Initialize the TableRows object for all tables
 
For ObjectCounter As Integer = 0 To NumberOfObjects - 1
 
TableRows(ObjectCounter) = New TableRows
 
Next
 
'Store the rows that need to be inserted to the database
 
For ObjectCounter As Integer = 0 To NumberOfObjects - 1
 
TableRows(ObjectCounter).InsertedRows = dataTables(ObjectCounter).Select("", "", DataViewRowState.Added)
 
Next
 
'Store the rows that need to be updated to the database
 
For ObjectCounter As Integer = 0 To NumberOfObjects - 1
 
TableRows(ObjectCounter).UpdatedRows = dataTables(ObjectCounter).Select("", "", DataViewRowState.ModifiedCurrent)
 
Next
 
'Store the rows that need to be deleted to the database
 
For ObjectCounter As Integer = 0 To NumberOfObjects - 1
 
TableRows(ObjectCounter).DeletedRows = GetDeletedRows(dataTables(ObjectCounter))
 
Next
 
'Add handlers to row update events to handle properly the modification of
 
'rows in insert and delete operations.
 
For ObjectCounter As Integer = 0 To NumberOfObjects - 1
 
AddHandler dataAdapters(ObjectCounter).RowUpdated, AddressOf RowUpdated
 
Next
 
'Now we are ready to actualy update the database. The actions must be taken in
 
'this order: delete, insert, update.
 
'See: http://blogs.gr/equilibrium/archive/2005/07/16/Order_of_Insert_Update_Delete_with_DataSet_Updates.aspx
 
'Process deleted rows (starting from children tables to parent tables)
 
For ObjectCounter As Integer = NumberOfObjects - 1 To 0 Step -1
 
dataAdapters(ObjectCounter).Update(TableRows(ObjectCounter).DeletedRows)
 
Next
 
'Close constrains of the dataset because we will manualy handle the changes in identity columns
 
dataTables(0).DataSet.EnforceConstraints = False
 
'Process inserted rows (from parent tables to children tables) 
 
For ObjectCounter As Integer = NumberOfObjects - 1 To 0 Step -1
 
dataAdapters(ObjectCounter).Update(TableRows(ObjectCounter).InsertedRows)
 
Next
 
'Open dataset constrains
 
dataTables(0).DataSet.EnforceConstraints = True
 
'Process modified rows (from parent tables to children tables) 
 
For ObjectCounter As Integer = NumberOfObjects - 1 To 0 Step -1
 
dataAdapters(ObjectCounter).Update(TableRows(ObjectCounter).UpdatedRows)
 
Next
 
Catch ex As Exception
 
Throw New Exception("Failed to update DataSet over transaction", ex)
 
End Try
 
Return DataSet
 
End Function
 
Public Function UpdateDataSet(ByVal dataAdapters() As SqlDataAdapter, ByVal dataTables() As DataTable, ByVal connection As SqlConnection) As DataSet
 
'Calls the UpdateDataSet function by first creating a transaction based
 
'on an existing connection.
 
Dim CloseConnectionOnReturn As Boolean
 
If connection.State = ConnectionState.Open Then
 
CloseConnectionOnReturn = False
 
Else
 
CloseConnectionOnReturn = True
 
connection.Open()
 
End If
 
Dim Transaction As SqlClient.SqlTransaction
 
Transaction = connection.BeginTransaction
 
Try
 
UpdateDataSet = UpdateDataSet(dataAdapters, dataTables, Transaction)
 
Transaction.Commit()
 
Catch ex As Exception
 
Transaction.Rollback()
 
Throw New Exception("Failed to update DataSet", ex)
 
Finally
 
If CloseConnectionOnReturn AndAlso connection.State = ConnectionState.Open Then
 
connection.Close()
 
End If
 
End Try
 
End Function
 
End Module
 
Public Class TableRows
 
'This class will hold the initialy inserted, updated and deleted rows of each table.
 
'Because we manualy control the row states of all table rows, we can rely on row states
 
'so we have to store here the originaly desired action on each row.
 
Public InsertedRows As DataRow()
 
Public UpdatedRows As DataRow()
 
Public DeletedRows As DataRow()
 
End Class
 

Βρήκα κάτι που νομίζω ότι είναι λάθος στον αρχικό κώδικα της Microsoft:


The correct order should be Insert, Update, Delete. Please consider the following case:

On the client:
1. A record is added in the parent table
2. A record from the child table is updated so that it relates to the record of the previous step.

When you call UpdateData, the record from the child table (step 2) is updated to refer to a record from the parent table that do not yet exists is the database because the insert operation follows the update.

So the code should be:

daOrd2.Update(ds.Tables("orders").Select("", "", DataViewRowState.Added))
ds.EnforceConstraints = False
daDetails.Update(ds.Tables("details").Select("", "", DataViewRowState.Added))
ds.EnforceConstraints = True

daOrd2.Update(ds.Tables("orders").Select("", "", DataViewRowState.ModifiedCurrent))
daDetails.Update(ds.Tables("details").Select("", "", DataViewRowState.ModifiedCurrent ))

daDetails.Update(GetDeletedRows(ds.Tables("details")))
daOrd2.Update(GetDeletedRows(ds.Tables("orders")))

Οπότε και στον δικό μου κώδικα πρέπει να αλλάξει αντίστοιχα η σειρά. Μόλις το τεστάρω λίγο ακόμα και ο Γιώργος μου ανοίξει το upload θα ανεβάσω τις σχετικές τροποποιήσεις.

Η διόρθωση αυτή έχει πλέον ενσωματωθεί στο αρχικό post

Έκανα τις απαραίτητες διορθώσεις στον κώδικα, επέκτεινα το documentation, μίκρυνα τον τίτλο για να μην φωνάζουν μερικοί μερικοί, έβαλα attached τον κώδικα.

Ίσως ο κώδικας αυτός θα έπρεπει να μπεί κάπου στο Data Access Application Block μέσα στο Enterpsise Library... Θα το ψάξω με τους τυπάδες που το φτιάχνουν...