Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB7
Expertise: Intermediate
Mar 24, 2003



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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

        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
            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()
        'commit the transaction
        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
        If Not rollback Then tran.Commit()
    End Try
End Function
Marco Bellinaso
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date