VB.NET Oracleでデータ更新する(insert/update/delete)

VB.NETでOracleでデータ更新するサンプルと説明です。
(確認環境:Microsoft Visual Studio Community 2019,Oracle Express Edition 11g)

目次

SQLのサンプル	insertのサンプル
説明	ドライバをimportする
	バインド変数
	OracleConnectionクラスのインスタンスを生成
	接続とトランザクションの開始
	OracleCommandクラスのインスタンスを生成
	OracleCommandクラスのParametersプロパティに値をセット
	SQLの実行とコミット
	例外のCatchとロールバック
	updateの場合
	deleteの場合
サンプル	更新のsqlを複数回実行する例
ドライバ	Oracle.ManagedDataAccessを取得する

insertのサンプル

Oracle11gに接続してインサートを実行するサンプルです。

Imports Oracle.ManagedDataAccess.Client

Module Module1
  Sub Main()

    Dim Sql As String _
    = "insert into employee (id,name,romaji) VALUES (:ID,:name,:romaji)"

    Using Conn As OracleConnection = New OracleConnection()

      Conn.ConnectionString =
          "User Id=hr;Password=hr;Data Source=localhost/XE;"
      Try
        Conn.Open()

        Using transaction As OracleTransaction = Conn.BeginTransaction()

          Try
            Using cmd As OracleCommand = New OracleCommand(Sql)

              cmd.Connection = Conn
              cmd.CommandType = CommandType.Text
              cmd.BindByName = True

              cmd.Parameters.Add(New OracleParameter(
                  "ID", OracleDbType.Int32)).Value = 5
              cmd.Parameters.Add(New OracleParameter(
                   "name", OracleDbType.Varchar2)).Value = "高橋"
              cmd.Parameters.Add(New OracleParameter(
                   "romaji", OracleDbType.Varchar2)).Value = "takahashi"

              cmd.ExecuteNonQuery()

              transaction.Commit()

            End Using
          Catch ex As Exception
            transaction.Rollback()
            Console.WriteLine(ex.Message)
          End Try
        End Using
      Catch ex As Exception
        Console.WriteLine(ex.Message)
      End Try
    End Using
  End Sub
End Module

ドライバをimportする

Imports Oracle.ManagedDataAccess.Client

1行目は、Oracleのドライバをインポートしています。Oracleで操作をするのに必要です。

バインド変数

    Dim Sql As String _
    = "insert into employee (id,name,romaji) VALUES (:ID,:name,:romaji)"

8行目は、SQLのinsert文です。valuesにあるコロン(:)がついている項目はバインド変数です。パラメータで値を設定します。バインド変数は、プレースホルダとも呼ばれます。
入力される不正な文字を抑止できるのでSQLインジェクション対策になります。
固定文字列のSQLの部分がプリコンパイルされるので実行速度が上がります。

OracleConnectionクラスのインスタンスを生成

    Using Conn As OracleConnection = New OracleConnection()

      Conn.ConnectionString =
          "User Id=hr;Password=hr;Data Source=localhost/XE;"

9行目は、OracleConnectionのインスタンスを生成しています。Usingなので自動でcloseします。
11,12行目は、インスタンスにOracleへの接続情報をセットしています。XEはデータベース名です。

接続とトランザクションの開始

        Conn.Open()

        Using transaction As OracleTransaction = Conn.BeginTransaction()

14行目は、openメソッドでOracleへ接続しています。
16行目は、OracleConnectionクラスのBeginTransactionメソッドでトランザクションを開始します。ここもUsingなので自動でcloseします。
変数の型はOracleTransactionクラスです。

OracleCommandクラスのインスタンスを生成

          Try
            Using cmd As OracleCommand = New OracleCommand(Sql)

              cmd.Connection = Conn
              cmd.CommandType = CommandType.Text
              cmd.BindByName = True

18行目のTryは、例外があったときに後述するCatchに処理が移動します。
19行目は、OracleCommandクラスのインスタンスを生成しています。コンストラクタの引数に上記のinsert文のSQLを指定しています。
21～23行目は、OracleCommandクラスのプロパティに値をセットしています。
重要なのは23行目のBindByNameプロパティです。これを指定しないとバインド名での紐付けがされません。これを記述しない場合は、SQL文のバインド変数の並び順と後述するOracleParameterへの設定の順序を同じにします。
例として25,26行目の記述を31行目以降に移動させるとSQL文の:IDをOracleParameterのnameでみてしまうので以下のエラーメッセージがでて登録されません。
{"ORA-01722: 数値が無効です。"}

OracleCommandクラスのParametersプロパティに値をセット

              cmd.Parameters.Add(New OracleParameter(
                  "ID", OracleDbType.Int32)).Value = 5
              cmd.Parameters.Add(New OracleParameter(
                   "name", OracleDbType.Varchar2)).Value = "高橋"
              cmd.Parameters.Add(New OracleParameter(
                   "romaji", OracleDbType.Varchar2)).Value = "takahashi"

25行目からは、OracleCommandクラスのParametersプロパティに値をセットしています。OracleParameterの引数の"ID"や”name"がSQL文のバインド変数と紐づきます。26行目は、Oracleのテーブルのデータ型はnumber型なのでOracleDbTypeは、Int32にしています。
28,30行目は、Oracleのテーブルのデータ型はVARCHAR2型なのでVarchar2にしています。

SQLの実行とコミット

              cmd.ExecuteNonQuery()

              transaction.Commit()

32行目は、OracleCommandクラスのExecuteNonQueryメソッドでSQLを実行します。
34行目は、OracleでのSQLの処理が成功した場合、OracleTransactionクラスのCommitメソッドで確定されます。
ここでエラーがあった場合は、34行目は実行されず、後述のCatchに処理が移動します。

例外のCatchとロールバック

          Catch ex As Exception
            transaction.Rollback()
            Console.WriteLine(ex.Message)
          End Try

37行目のCatchは18行目のtry以降の実行でエラーが発生したときに処理が行われます。
エラーをキャッチするとも言われます。
Exceptionはクラスでexは変数です。
38行目は、OracleTransactionクラスのRollbackメソッドでデータをロールバックします。更新しません。例えばinsert文を2つ発行したとして一つは成功しもう一つは失敗した場合、2つともデータ更新はしません。
39行目は、ExceptionクラスのMessageプロパティにエラーメッセージが入ります。

updateの場合

= "update employee Set name = :name ,romaji = :romaji where id = :ID"

updeteを行う場合は、18行目のSQL文を変更します。
cmd.Parametersには、キーと更新したい項目を指定します。

deleteの場合

= "delete from employee where id = :ID"

deleteを行う場合は、18行目のSQL文を変更します。
cmd.Parametersには、キーを指定します。

更新のsqlを複数回実行する例

更新のsqlを複数回実行する場合は、OracleParameterCollectionクラスのClearメソッドを使用します。

	Using cmd As OracleCommand = New OracleCommand(Sql)

		cmd.Connection = Conn
		cmd.CommandType = CommandType.Text
		cmd.BindByName = True

		cmd.Parameters.Add(New OracleParameter(
			"ID", OracleDbType.Int32)).Value = 5
		cmd.Parameters.Add(New OracleParameter(
			 "name", OracleDbType.Varchar2)).Value = "高橋"
		cmd.Parameters.Add(New OracleParameter(
			 "romaji", OracleDbType.Varchar2)).Value = "takahashi"

		cmd.ExecuteNonQuery()

		cmd.Parameters.Clear()

		cmd.Parameters.Add(New OracleParameter(
			"ID", OracleDbType.Int32)).Value = 6
		cmd.Parameters.Add(New OracleParameter(
			"name", OracleDbType.Varchar2)).Value = "武田"
		cmd.Parameters.Add(New OracleParameter(
			"romaji", OracleDbType.Varchar2)).Value = "takeda"

		cmd.ExecuteNonQuery()

		transaction.Commit()
	End Using

32行目は、ExecuteNonQueryメソッドで1回目の更新処理を行います。
34行目は、Clearメソッドでパラメータをクリアします。
43行目は、2回目の更新処理を行います。
45行目は、上記2つの更新処理が成功した場合にコミットします。

Oracle.ManagedDataAccessを取得する

Oracle.ManagedDataAccessは、Oracle公式のドライバです。
(Oracle Data Provider for .NET(ODP.NET),Managed Driverです)
この他にOracle Clientをインストールする必要はありません。

１．Visual Studioの「ツール」→「NuGetパッケージマネージャー」→「ソリューションのNuGetパッケージのい管理」をクリックします。

 

２．「参照」タブをクリックし検索入力欄に「Oracle.ManagedDataAccess」と入力します。
表示されたOracle.ManagedDataAccessをクリックし、プロジェクトのチェックを入れ、インストールボタンを押します。

 

３．インストールが完了すると参照配下にOracle.ManagedDataAccessが追加されます。

関連の記事

VB.NET Oracleに接続してselectするサンプル