Web Services

Re: Update a dataset with related tables and identity columns by using a Web service

Χρήστος Γεωργακόπουλος — Sat, 16 Jul 2005 19:31:59 GMT

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

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

Re: 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

KelMan — Wed, 13 Jul 2005 03:40:27 GMT

Επίσης, μην βάζετε τεράστιους τίτλους στα posts γιατί "σπρώχνουν" τα κουμπιά και τα links δεξιά, το ένα κάτω από το άλλο...

Re: 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

George J. Capnias — Wed, 13 Jul 2005 03:14:39 GMT

Έκανα ένα γενικό καθάρισμα στα δικαιώματα των forums σήμερα. Όταν θα πάς να κάνεις post μήνυμα θα πρέπει να έχεις το δικαίωμα να βάλεις και ένα attachment.

Προσοχή: Για όλους πάει αυτό, αν θέλετε να βάλετε ένα "συννημένοt" σε ένα μύνημα, επιλέγετε το αρχείο και μετά κατευθείαν post, όχι preview, για να μπορέσει να το κάνει επιτυχημένα. Είναι ένα bug του forum.

George J.

Re: 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

Χρήστος Γεωργακόπουλος — Tue, 12 Jul 2005 21:27:12 GMT

Βρήκα κάτι που νομίζω ότι είναι λάθος στον αρχικό κώδικα της 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

Re: Update a dataset with related tables and identity columns by using a Web service

Χρήστος Γεωργακόπουλος — Tue, 12 Jul 2005 14:53:09 GMT

~~Το κλασσικό... upload δεν βλέπω πουθενά. Anyway, το έβαλα online μέχρι να δεις τι γίνεται με το upload:~~

Ο κώδικας είναι πλέον attached στο αρχικό post

Re: 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

George J. Capnias — Tue, 12 Jul 2005 09:17:04 GMT

Χρήστο,

Μπράβο πολύ καλή προσπάθεια!

Προσπάθησα να κάνω copy & paste τον κώδικα στο Visual Studio IDE αλλά δεν τα κατάφερα. Έχω αλλάξει τα δικαιώματα στο forum, και αν δεν σου κάνει κόπο, μπορείς να κάνεις upload είτε το project, ή μόνο το εν λόγω .vb αρχείο, ώστε να μπορούν όλοι να πάρουν τον κώδικα.

George J.

Update a dataset with related tables and identity columns by using a Web service

Χρήστος Γεωργακόπουλος — Tue, 12 Jul 2005 03:32:56 GMT

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:

We have a data access layer (DAL) hidden behind a web services layer and a client that communicates with web services using datasets
The datasets contain multiple tables with relations
The client requests from the web services to get such a dataset filled up with data
The client does multiple insert, update and delete actions on the dataset that currently holds
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
The DAL updates the database and sends back to the client (through web services) the updated rows
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:

When a table in the client's dataset is completely empty, the dataset counter for the identity columns starts from 0
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:

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.
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:

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
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
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:

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.
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:

Delete (starting with children tables first, continuing up to the parent tables)
Insert (starting with the parent tables first, continuing down to the children tables)
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 cgeorgakopoulos@gmail.com

Chris Georgakopoulos

 
 Imports System.Data.SqlClient
 Public Module DataAdapterHelper
 'Author: cgeorgakopoulos@gmail.com
 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