【VB.NET】トランザクション処理を行うには？

訪問して頂きありがとうございます。まさふくろーです。

この記事では、トランザクション処理を行う方法をご紹介します。

トランザクション処理を行う
関連記事

トランザクション処理を行う

トランザクションを使用すると、データベース更新中にエラーが発生した場合、データの整合性を保持することができます。
トランザクションを使用する手順は以下になります。

トランザクションの開始

OleDbConnectionクラスをインスタンス化します。
書式は、以下になります。

New OleDbConnection()

OleDbConnectionのBeginTransactionメソッドで、トランザクションを開始します。
BeginTransactionメソッドの戻り値は、OleDbTransaction型です。
書式は、以下になります。

インスタンス化したOleDbConnection.BeginTransaction()

実行するSQLにトランザクションを適用する

OleDbCommandクラスをインスタンス化します。
書式は、以下になります。

New OleDbCommand()

OleDbCommandのTransactionプロパティで、トランザクションを適用します。
書式は、以下になります。

インスタンス化したOleDbCommand.Transaction = インスタンス化したOleDbConnection.BeginTransaction()

トランザクションを終了する

トランザクションを終了するには、OleDbTransactionクラスのCommitまたはRollbackメソッドを実行します。
Commitメソッドは、更新した内容をデータベースに反映し、Rollbackメソッドは、更新前の状態に戻します。
書式は、以下になります。

OleDbTransaction.Commit()

OleDbTransaction.Rollback()

SQL Serverに接続する場合は、SqlConnectionクラス、SqlCommandクラスでも可能です。
トランザクションの開始

New SqlConnection()

インスタンス化したSqlConnection.BeginTransaction()
実行するSQLにトランザクションを適用する

New SqlCommand()

インスタンス化したSqlCommand.Transaction = インスタンス化したSqlConnection.BeginTransaction()
トランザクションを終了する

SqlTransaction.Commit()

SqlTransaction.Rollback()

サンプルプログラム

OleDbConnectionクラスを使用する

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim prov As String = "SQLOLEDB"
        Dim serverName As String = "TEST-PC\SQLEXPRESS"
        Dim dataBase As String = "AdventureWorksLT2017"
        Dim userid As String = "test"
        Dim pwd As String = "test"
        Dim oleDbTran As OleDb.OleDbTransaction

        Using conn As New OleDb.OleDbConnection()

            conn.ConnectionString =
                    " Provider = " & prov &
                    ";Data Source = " & serverName &
                    ";Initial Catalog = " & dataBase &
                    ";User ID = " & userid &
                    ";Password =" & pwd

            conn.Open()

            oleDbTran = conn.BeginTransaction()

            Try

                Using cmd As New OleDb.OleDbCommand()

                    cmd.Connection = conn

                    cmd.Transaction = oleDbTran

                    cmd.CommandText =
                        "UPDATE SalesLT.SalesOrderHeader   " & ControlChars.NewLine &
                        "SET                               " & ControlChars.NewLine &
                        "       Status = ?                 " & ControlChars.NewLine &
                        "      ,SubTotal = ?               " & ControlChars.NewLine &
                        "      ,ModifiedDate = ?           " & ControlChars.NewLine &
                        "WHERE                             " & ControlChars.NewLine &
                        "       SalesOrderID = ?           " & ControlChars.NewLine

                    cmd.Parameters.Add("@Status", OleDb.OleDbType.Integer).Value = 6
                    cmd.Parameters.Add("@SubTotal", OleDb.OleDbType.Currency).Value = 1070.694
                    cmd.Parameters.Add("@ModifiedDate", OleDb.OleDbType.Date).Value = Now
                    cmd.Parameters.Add("@SalesOrderID", OleDb.OleDbType.Integer).Value = 71774

                    cmd.ExecuteNonQuery()

                    cmd.Parameters.Clear()

                    cmd.CommandText =
                        "UPDATE SalesLT.SalesOrderDetail   " & ControlChars.NewLine &
                        "SET                               " & ControlChars.NewLine &
                        "       OrderQty = ?                 " & ControlChars.NewLine &
                        "      ,ModifiedDate = ?           " & ControlChars.NewLine &
                        "WHERE                             " & ControlChars.NewLine &
                        "       SalesOrderID = ?           " & ControlChars.NewLine &
                        "AND                               " & ControlChars.NewLine &
                        "       SalesOrderDetailID = ?     " & ControlChars.NewLine

                    cmd.Parameters.Add("@OrderQty", OleDb.OleDbType.Integer).Value = 2
                    cmd.Parameters.Add("@ModifiedDate", OleDb.OleDbType.Date).Value = Now
                    cmd.Parameters.Add("@SalesOrderID", OleDb.OleDbType.Integer).Value = 71774
                    cmd.Parameters.Add("@SalesOrderDetailID", OleDb.OleDbType.Integer).Value = 110562

                    cmd.ExecuteNonQuery()

                    oleDbTran.Commit()

                End Using

                Label1.Text = "SalesOrderHeader、SalesOrderDetailテーブルのレコードを更新しました。"

            Catch ex As Exception

                oleDbTran.Rollback()

                Label1.Text = Err.Description

            End Try

        End Using

    End Sub
End Class

Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim prov As String = "SQLOLEDB"

Dim serverName As String = "TEST-PC\SQLEXPRESS"

Dim dataBase As String = "AdventureWorksLT2017"

Dim userid As String = "test"

Dim pwd As String = "test"

Dim oleDbTran As OleDb.OleDbTransaction

Using conn As New OleDb.OleDbConnection()

conn.ConnectionString =

" Provider = " & prov &

";Data Source = " & serverName &

";Initial Catalog = " & dataBase &

";User ID = " & userid &

";Password =" & pwd

conn.Open()

oleDbTran = conn.BeginTransaction()

Try

Using cmd As New OleDb.OleDbCommand()

cmd.Connection = conn

cmd.Transaction = oleDbTran

cmd.CommandText =

"UPDATE SalesLT.SalesOrderHeader " & ControlChars.NewLine &

"SET " & ControlChars.NewLine &

" Status = ? " & ControlChars.NewLine &

" ,SubTotal = ? " & ControlChars.NewLine &

" ,ModifiedDate = ? " & ControlChars.NewLine &

"WHERE " & ControlChars.NewLine &

" SalesOrderID = ? " & ControlChars.NewLine

cmd.Parameters.Add("@Status", OleDb.OleDbType.Integer).Value = 6

cmd.Parameters.Add("@SubTotal", OleDb.OleDbType.Currency).Value = 1070.694

cmd.Parameters.Add("@ModifiedDate", OleDb.OleDbType.Date).Value = Now

cmd.Parameters.Add("@SalesOrderID", OleDb.OleDbType.Integer).Value = 71774

cmd.ExecuteNonQuery()

cmd.Parameters.Clear()

cmd.CommandText =

"UPDATE SalesLT.SalesOrderDetail " & ControlChars.NewLine &

"SET " & ControlChars.NewLine &

" OrderQty = ? " & ControlChars.NewLine &

" ,ModifiedDate = ? " & ControlChars.NewLine &

"WHERE " & ControlChars.NewLine &

" SalesOrderID = ? " & ControlChars.NewLine &

"AND " & ControlChars.NewLine &

" SalesOrderDetailID = ? " & ControlChars.NewLine

cmd.Parameters.Add("@OrderQty", OleDb.OleDbType.Integer).Value = 2

cmd.Parameters.Add("@ModifiedDate", OleDb.OleDbType.Date).Value = Now

cmd.Parameters.Add("@SalesOrderID", OleDb.OleDbType.Integer).Value = 71774

cmd.Parameters.Add("@SalesOrderDetailID", OleDb.OleDbType.Integer).Value = 110562

cmd.ExecuteNonQuery()

oleDbTran.Commit()

End Using

Label1.Text = "SalesOrderHeader、SalesOrderDetailテーブルのレコードを更新しました。"

Catch ex As Exception

oleDbTran.Rollback()

Label1.Text = Err.Description

End Try

End Using

End Sub

End Class

2	「複数テーブルのレコードを更新」ボタンクリック時に以下処理を行う。
4～9	接続先データベースの情報を、変数に代入。
11	OleDbConnectionクラスのインスタンス化。（Usingステートメントでリソース開放を自動化）
13～18	ConnectionStringプロパティにデータベース情報を設定。
20	データベースに接続。
22	トランザクションを開始し、OleDbTransaction型の変数「oleDbTran」に値を代入。
26	OleDbCommandクラスのインスタンス化。（Usingステートメントでリソース開放を自動化）
28	SQL文を実行するために、13～18行目で設定したデータベース情報と同じ情報をConnection プロパティに設定。
30	実行するSQL文にトランザクションを適用する。
32～39	レコードを更新するSQL文をCommandTextプロパティに設定。
32～39	35～39行目の「?」マークは、以下41～44行目で設定するパラメータを使用する際に記述します。
41	「SalesOrderHeader」テーブルの「Status」列に、値「6」を設定する。
42	「SalesOrderHeader」テーブルの「SubTotal」列に、値「1070.694」を設定する。
43	「SalesOrderHeader」テーブルの「ModifiedDate」列に、値「現在日」を設定する。
44	「SalesOrderHeader」テーブルの「SalesOrderID」列に、値「71774」を設定する。
46	SQL文を実行する。
48	41～44行目で設定したパラメータを初期化する。
50～58	レコードを更新するSQL文をCommandTextプロパティに設定。
50～58	53～58行目の「?」マークは、以下60～63行目で設定するパラメータを使用する際に記述します。
60	「SalesOrderDetail」テーブルの「OrderQty」列に、値「2」を設定する。
61	「SalesOrderDetail」テーブルの「ModifiedDate」列に、値「現在日」を設定する。
62	「SalesOrderDetail」テーブルの「SalesOrderID」列に、値「71774」を設定する。
63	「SalesOrderDetail」テーブルの「SalesOrderDetailID」列に、値「110562」を設定する。
65	SQL文を実行する。
67	更新処理が正常に終了したら、更新結果をすべてのテーブルに反映する。
71	処理が正常に終了したら、正常終了のメッセージをラベルに表示。
73	処理の実行中にエラーが発生した場合、
75	すべてのテーブルに対して、更新前のデータに戻す。
77	エラー内容を取得し、ラベルに表示する。

SqlConnectionクラスを使用する

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim serverName As String = "TEST-PC\SQLEXPRESS"
        Dim dataBase As String = "AdventureWorksLT2017"
        Dim userid As String = "test"
        Dim pwd As String = "test"
        Dim sqlTran As SqlClient.SqlTransaction

        Using conn As New SqlClient.SqlConnection()

            conn.ConnectionString =
                " Data Source = " & serverName &
                ";Initial Catalog = " & dataBase &
                ";User ID = " & userid &
                ";Password =" & pwd

            conn.Open()

            sqlTran = conn.BeginTransaction()

            Try

                Using cmd As New SqlClient.SqlCommand()

                    cmd.Connection = conn

                    cmd.Transaction = sqlTran

                    cmd.CommandText =
                        "UPDATE SalesLT.SalesOrderHeader        " & ControlChars.NewLine &
                        "SET                                    " & ControlChars.NewLine &
                        "       Status = @Status                " & ControlChars.NewLine &
                        "      ,SubTotal = @SubTotal            " & ControlChars.NewLine &
                        "      ,ModifiedDate = @ModifiedDate    " & ControlChars.NewLine &
                        "WHERE                                  " & ControlChars.NewLine &
                        "       SalesOrderID = @SalesOrderID    " & ControlChars.NewLine

                    cmd.Parameters.Add("@Status", SqlDbType.Int).Value = 6
                    cmd.Parameters.Add("@SubTotal", SqlDbType.Money).Value = 1070.694
                    cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = Now
                    cmd.Parameters.Add("@SalesOrderID", SqlDbType.Int).Value = 71774

                    cmd.ExecuteNonQuery()

                    cmd.Parameters.Clear()

                    cmd.CommandText =
                        "UPDATE SalesLT.SalesOrderDetail                    " & ControlChars.NewLine &
                        "SET                                                " & ControlChars.NewLine &
                        "       OrderQty = @OrderQty                        " & ControlChars.NewLine &
                        "      ,ModifiedDate = @ModifiedDate                " & ControlChars.NewLine &
                        "WHERE                                              " & ControlChars.NewLine &
                        "       SalesOrderID = @SalesOrderID                " & ControlChars.NewLine &
                        "AND                                                " & ControlChars.NewLine &
                        "       SalesOrderDetailID = @SalesOrderDetailID    " & ControlChars.NewLine

                    cmd.Parameters.Add("@OrderQty", SqlDbType.Int).Value = 2
                    cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = Now
                    cmd.Parameters.Add("@SalesOrderID", SqlDbType.Int).Value = 71774
                    cmd.Parameters.Add("@SalesOrderDetailID", SqlDbType.Int).Value = 110562

                    cmd.ExecuteNonQuery()

                    sqlTran.Commit()

                End Using

                Label1.Text = "SalesOrderHeader、SalesOrderDetailテーブルのレコードを更新しました。"

            Catch ex As Exception

                sqlTran.Rollback()

                Label1.Text = Err.Description

            End Try

        End Using

    End Sub
End Class

Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim serverName As String = "TEST-PC\SQLEXPRESS"

Dim dataBase As String = "AdventureWorksLT2017"

Dim userid As String = "test"

Dim pwd As String = "test"

Dim sqlTran As SqlClient.SqlTransaction

Using conn As New SqlClient.SqlConnection()

conn.ConnectionString =

" Data Source = " & serverName &

";Initial Catalog = " & dataBase &

";User ID = " & userid &

";Password =" & pwd

conn.Open()

sqlTran = conn.BeginTransaction()

Try

Using cmd As New SqlClient.SqlCommand()

cmd.Connection = conn

cmd.Transaction = sqlTran

cmd.CommandText =

"UPDATE SalesLT.SalesOrderHeader " & ControlChars.NewLine &

"SET " & ControlChars.NewLine &

" Status = @Status " & ControlChars.NewLine &

" ,SubTotal = @SubTotal " & ControlChars.NewLine &

" ,ModifiedDate = @ModifiedDate " & ControlChars.NewLine &

"WHERE " & ControlChars.NewLine &

" SalesOrderID = @SalesOrderID " & ControlChars.NewLine

cmd.Parameters.Add("@Status", SqlDbType.Int).Value = 6

cmd.Parameters.Add("@SubTotal", SqlDbType.Money).Value = 1070.694

cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = Now

cmd.Parameters.Add("@SalesOrderID", SqlDbType.Int).Value = 71774

cmd.ExecuteNonQuery()

cmd.Parameters.Clear()

cmd.CommandText =

"UPDATE SalesLT.SalesOrderDetail " & ControlChars.NewLine &

"SET " & ControlChars.NewLine &

" OrderQty = @OrderQty " & ControlChars.NewLine &

" ,ModifiedDate = @ModifiedDate " & ControlChars.NewLine &

"WHERE " & ControlChars.NewLine &

" SalesOrderID = @SalesOrderID " & ControlChars.NewLine &

"AND " & ControlChars.NewLine &

" SalesOrderDetailID = @SalesOrderDetailID " & ControlChars.NewLine

cmd.Parameters.Add("@OrderQty", SqlDbType.Int).Value = 2

cmd.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = Now

cmd.Parameters.Add("@SalesOrderID", SqlDbType.Int).Value = 71774

cmd.Parameters.Add("@SalesOrderDetailID", SqlDbType.Int).Value = 110562

cmd.ExecuteNonQuery()

sqlTran.Commit()

End Using

Label1.Text = "SalesOrderHeader、SalesOrderDetailテーブルのレコードを更新しました。"

Catch ex As Exception

sqlTran.Rollback()

Label1.Text = Err.Description

End Try

End Using

End Sub

End Class

2	「複数テーブルのレコードを更新」ボタンクリック時に以下処理を行う。
4～8	接続先データベースの情報を、変数に代入。
10	SqlConnectionクラスのインスタンス化。（Usingステートメントでリソース開放を自動化）
12～16	ConnectionStringプロパティにデータベース情報を設定。
18	データベースに接続。
20	トランザクションを開始し、SqlTransaction型の変数「sqlTran」に値を代入。
24	SqlCommandクラスのインスタンス化。（Usingステートメントでリソース開放を自動化）
26	SQL文を実行するために、12～16行目で設定したデータベース情報と同じ情報をConnection プロパティに設定。
28	実行するSQL文にトランザクションを適用する。
30～37	レコードを更新するSQL文をCommandTextプロパティに設定。
30～37	33～37行目の「@」マークは、以下39～42行目で設定するパラメータを使用する際に記述します。
39	「SalesOrderHeader」テーブルの「Status」列に、値「6」を設定する。
40	「SalesOrderHeader」テーブルの「SubTotal」列に、値「1070.694」を設定する。
41	「SalesOrderHeader」テーブルの「ModifiedDate」列に、値「現在日」を設定する。
42	「SalesOrderHeader」テーブルの「SalesOrderID」列に、値「71774」を設定する。
44	SQL文を実行する。
46	39～42行目で設定したパラメータを初期化する。
48～56	レコードを更新するSQL文をCommandTextプロパティに設定。
48～56	51～56行目の「@」マークは、以下58～61行目で設定するパラメータを使用する際に記述します。
58	「SalesOrderDetail」テーブルの「OrderQty」列に、値「2」を設定する。
59	「SalesOrderDetail」テーブルの「ModifiedDate」列に、値「現在日」を設定する。
60	「SalesOrderDetail」テーブルの「SalesOrderID」列に、値「71774」を設定する。
61	「SalesOrderDetail」テーブルの「SalesOrderDetailID」列に、値「110562」を設定する。
63	SQL文を実行する。
65	更新処理が正常に終了したら、更新結果をすべてのテーブルに反映する。
69	処理が正常に終了したら、正常終了のメッセージをラベルに表示。
71	処理の実行中にエラーが発生した場合、
73	すべてのテーブルに対して、更新前のデータに戻す。
75	エラー内容を取得し、ラベルに表示する。