VB.NET ODP.NETでトランザクションを実行する

ODP.NETを使用してSQLを実行する際にトランザクションを実行する方法を紹介します。

以下はTABLE_AとTABLE_Bを更新している処理です。

この処理にトランザクションを実行して、エラー時は全てロールバックできるようにします。

（以下はわかりやすくするため１つのファンクションで実行していますがSQLごとにファンクション化する方法を記事の最後で紹介しています）

Imports Oracle.DataAccess.Client 'ファイルの先頭に追加

Public Sub SelectData(id As String)

    Dim ds As New DataSet

    Using conn As OracleConnection = New OracleConnection(接続文字列)

        'コネクションOpen
        conn.Open()

        Dim sql As StringBuilder

        'TABLE_Aを更新
        sql = New StringBuilder
        sql.AppendLine(" UPDATE TABLE_A ")
        sql.AppendLine(" SET ")
        sql.AppendLine("    T1.COL1 = :COL1 ")

        Using cmd As New OracleCommand(sql.ToString(), conn)
            cmd.BindByName = True
            cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
            
            cmd.ExecuteNonQuery()
        End Using

        'TABLE_Bを更新
        sql = New StringBuilder
        sql.AppendLine(" UPDATE TABLE_B")
        sql.AppendLine(" SET ")
        sql.AppendLine("    T1.COL1 = :COL1 ")

        Using cmd As New OracleCommand(sql.ToString(), conn)
            cmd.BindByName = True
            cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
            
            cmd.ExecuteNonQuery()
        End Using

    End Using

End Sub

以下のように、トランザクションとして実行したいSQLの処理をUsingで囲みます。

また、処理の最後にコミットを実行する処理を追加しています。

エラーが発生した場合はコミットが実行されないため、トランザクションはロールバックされます。（ロールバック処理を明示的に記載する必要はありません）

Imports Oracle.DataAccess.Client 'ファイルの先頭に追加

Public Sub SelectData(id As String)

    Dim ds As New DataSet

    Using conn As OracleConnection = New OracleConnection(接続文字列)

        'コネクションOpen
        conn.Open()

        Using trans As OracleTransaction = conn.BeginTransaction() 'トランザクション開始

            Dim sql As StringBuilder

            'TABLE_Aを更新
            sql = New StringBuilder
            sql.AppendLine(" UPDATE TABLE_A ")
            sql.AppendLine(" SET ")
            sql.AppendLine("    T1.COL1 = :COL1 ")

            Using cmd As New OracleCommand(sql.ToString(), conn)
                cmd.BindByName = True
                cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
                
                cmd.ExecuteNonQuery()
            End Using

            'TABLE_Bを更新
            sql = New StringBuilder
            sql.AppendLine(" UPDATE TABLE_B")
            sql.AppendLine(" SET ")
            sql.AppendLine("    T1.COL1 = :COL1 ")

            Using cmd As New OracleCommand(sql.ToString(), conn)
                cmd.BindByName = True
                cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
                
                cmd.ExecuteNonQuery()
            End Using

            trans.Commit() 'コミット
        End Using
    End Using

End Sub

排他制御など、処理結果によって明示的にロールバックをしたい場合は以下のようにします。

If result = True Then
    '正常終了時はコミット
    trans.Commit()
Else
    'エラー時はロールバック
    trans.Rollback()
End If

SQLごとにファンクション化

長いSQL文を組み立てる必要がある場合があると、ファンクションが長くなりがちですので、実行するSQLごとにファンクションを作成して、処理を見やすくします。

Imports Oracle.DataAccess.Client 'ファイルの先頭に追加

Public Sub SelectData(id As String)

    Dim ds As New DataSet

    Using conn As OracleConnection = New OracleConnection(接続文字列)

        'コネクションOpen
        conn.Open()

        Using trans As OracleTransaction = conn.BeginTransaction() 'トランザクション開始

            Dim sql As StringBuilder

            'TABLE_Aを更新
            UpdateTABLE_A(conn, id)

            'TABLE_Bを更新
            UpdateTABLE_B(conn, id)

            trans.Commit() 'コミット
        End Using
    End Using

End Sub

Private Sub UpdateTABLE_A(ByVal conn As OracleConnection, id As String)

    Dim sql As StringBuilder

    'TABLE_Aを更新
    sql = New StringBuilder
    sql.AppendLine(" UPDATE TABLE_A ")
    sql.AppendLine(" SET ")
    sql.AppendLine("    T1.COL1 = :COL1 ")

    Using cmd As New OracleCommand(sql.ToString(), conn)
        cmd.BindByName = True
        cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
        
        cmd.ExecuteNonQuery()
    End Using
End Sub

Private Sub UpdateTABLE_B(ByVal conn As OracleConnection, id As String)

    Dim sql As StringBuilder

    'TABLE_Bを更新
    sql = New StringBuilder
    sql.AppendLine(" UPDATE TABLE_B ")
    sql.AppendLine(" SET ")
    sql.AppendLine("    T1.COL1 = :COL1 ")

    Using cmd As New OracleCommand(sql.ToString(), conn)
        cmd.BindByName = True
        cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
        
        cmd.ExecuteNonQuery()
    End Using
End Sub