ExecuteBatch – Executing a batch of OleDb commands

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:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing