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