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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS