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 TryFunction 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 TryEnd Function