UpdateDataAdapter - Update a data source through a DataAdapter

' 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