Binaryworld - UpdateDataAdapter - Update a data source through a DataAdapter ... [ VB.net -> ADO.net ]

' update a data source through a DataAdapter
'
' DA is the OleDbDataAdapter or SqlDataAdapter object
' DT is the DataTable to be updated
' if USETRANSACTIONS is True, all rows are updated, or none
'
' returns the number of affected records
' or -1 if the update failed

Function UpdateDataAdapter(ByVal da As System.Data.IDbDataAdapter, _
  ByVal dt As DataTable, ByVal useTransactions As Boolean) As Integer
  Dim tr As System.Data.IDbTransaction
  Dim affectedRows As Integer

  ' we need a reference to a concrete DbDataAdapter object
  Dim dda As System.Data.Common.DbDataAdapter = DirectCast(da, _
    System.Data.Common.DbDataAdapter)

  If useTransactions Then
    ' get the connection object related to the DataAdapter
    Dim cn As System.Data.IDbConnection = da.SelectCommand.Connection
    ' open a transaction on that connection
    tr = da.SelectCommand.Connection.BeginTransaction()
    ' enroll all commands in that transaction
    da.DeleteCommand.Transaction = tr
    da.InsertCommand.Transaction = tr
    da.UpdateCommand.Transaction = tr
    ' we need an exception if an update conflict occurs
    dda.ContinueUpdateOnError = False
  Else
    ' otherwise just ignore conflicts
    dda.ContinueUpdateOnError = True
  End If

  Try
    ' perform the update
    affectedRows = dda.Update(dt)
    ' if we get here, we can commit the transaction (if there is one)
    If Not (tr Is Nothing) Then tr.Commit()
  Catch ex As Exception
    ' in this case we must rollback the transaction
    ' (if there is one) and swallow the exception
    If Not (tr Is Nothing) Then tr.Rollback()
    affectedRows = -1
  End Try

  ' return the number of affected rows
  Return affectedRows
End Function