Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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