Binaryworld - ExecuteBatch - Executing a batch of OleDb commands ... [ VB.net -> ADO.net ]

' Execute a batch of OleDb commands.
' Parameters:
' - The list of statements separated by ; chars. Use the ? char as a
' placeholder for a parameter.
' - The array of OleParameter objects that will replace the ? chars
' - an optional Boolean that specifies whether the transaction will be rolled
' back if there is an exception.
' If False, and an exception is thrown while executing the third statement in
' the list, the first 2 statement successfully executed will be committed
' anyway.
'
' Note: requires requires Imports System.Data.OleDb
'
' Example: execute an INSERT, a DELETE and an UPDATE
' Dim connString As String = "Provider=SQLOLEDB.1;Data Source=.;User
' ID=sa;Password=;Initial Catalog=MyTestDb"
' Dim sql As String = "INSERT INTO Authors (AuthorID, AuthorName) VALUES (?,
' ?); " & '    "DELETE FROM Authors WHERE AuthorID = ?; " & '    "UPDATE
' Authors SET AuthorName = ? WHERE AuthorID = ? "
' Dim params() As OleDbParameter = { '    New OleDbParameter("NewID", 3),
' '    New OleDbParameter("NewName", "Mark"), '    New OleDbParameter
' ("DeleteID", 1), '    New OleDbParameter("UpdateName", "Tony"),
' '    New OleDbParameter("UpdateID", 2)}
'
' Try
'    Dim numAffected As Integer = ExecuteBatch(connString, sql, params)
'    MessageBox.Show(numAffected & " affected records.")
' Catch ex As Exception
'    MessageBox.Show(ex.Message)
' End Try

Function ExecuteBatch(ByVal connString As String, ByVal sql As String, _
  ByVal params() As OleDbParameter, Optional ByVal rollback As Boolean = True) _
  As Integer
  Dim i, start, affected As Integer
  Dim cn As New OleDbConnection(connString)
  Dim tran As OleDbTransaction
  Dim cmd As OleDbCommand

  ' split the input list of statements in an array of statements
  Dim statements() As String = sql.Split(New Char() {";"c})
  Dim statement As String

  Try
    cn.Open()
    tran = cn.BeginTransaction()
    ' for each statement, append the right number of parameters from the
    ' input array
    For Each statement In statements
      cmd = New OleDbCommand(statement, cn)
      cmd.Transaction = tran
      ' attach the params. Count the ? occurrences to know how many
      ' params must the attached
      Dim numParams As Integer = CountOccurrences(statement, "\?")
      For i = start To start + numParams - 1
        cmd.Parameters.Add(params(i))
      Next
      start = start + numParams
      ' execute the command and add the returned integer to a local
      ' variable that stores the total number of affected records
      affected += cmd.ExecuteNonQuery()
    Next
    'commit the transaction
    tran.Commit()
    Return affected
  Catch e As Exception
    ' in case of exception, rollback the transaction is so is required by
    ' the user
    If rollback Then tran.Rollback()
    ' re-trown the exception so that it's handled by the caller
    Throw e
  Finally
    If Not rollback Then tran.Commit()
    cn.Close()
  End Try
End Function