' 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