ExecuteBatch - Executing a batch of OleDb commands

' 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