C# Oracleでデータ更新する(insert/update/delete)

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

目次

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

insertのサンプル

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

using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;

class Test1
{
    static void Main()
    {
        string sql = "insert into employee(id,name,romaji) VALUES(:ID,:name,:romaji)";
        try
        {
            using (OracleConnection conn = new OracleConnection())
            {
                conn.ConnectionString =
                    "User ID=hr; Password=hr; Data Source=localhost/XE";
                conn.Open();
                using (OracleTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        using (OracleCommand cmd = new OracleCommand(sql, conn))
                        {
                            cmd.BindByName = true;
                            cmd.Parameters.Add(new OracleParameter(":ID", OracleDbType.Int32,
                                5, ParameterDirection.Input));
                            cmd.Parameters.Add(new OracleParameter(":name", OracleDbType.Varchar2,
                                "高橋", ParameterDirection.Input));
                            cmd.Parameters.Add(new OracleParameter(":romaji", OracleDbType.Varchar2,
                                "takahashi", ParameterDirection.Input));

                            cmd.ExecuteNonQuery();

                            transaction.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine(ex.ToString());
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message.ToString());
        }
    }
}

ドライバをimportする

Imports Oracle.ManagedDataAccess.Client

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

バインド変数

string sql = "insert into employee(id,name,romaji) VALUES(:ID,:name,:romaji)";

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

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

    using (OracleConnection conn = new OracleConnection())
    {
         conn.ConnectionString =
            "User ID=hr; Password=hr; Data Source=localhost/XE";

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

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

       conn.Open();
       using (OracleTransaction transaction = conn.BeginTransaction())

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

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

           try
            {
                 using (OracleCommand cmd = new OracleCommand(sql, conn))
                 {
                       cmd.BindByName = true;

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

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

  cmd.Parameters.Add(new OracleParameter(":ID", OracleDbType.Int32,
         5, ParameterDirection.Input));
   cmd.Parameters.Add(new OracleParameter(":name", OracleDbType.Varchar2,
         "高橋", ParameterDirection.Input));
   cmd.Parameters.Add(new OracleParameter(":romaji", OracleDbType.Varchar2,
         "takahashi", ParameterDirection.Input));

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

SQLの実行とコミット

                    cmd.ExecuteNonQuery();

                    transaction.Commit();

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

例外のCatchとロールバック

                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine(ex.Message);

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

updateの場合

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

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

deleteの場合

= "delete from employee where id = :ID"

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

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

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

	using (OracleCommand cmd = new OracleCommand(sql, conn))
	{
		cmd.BindByName = true;
		cmd.Parameters.Add(new OracleParameter(":ID", OracleDbType.Int32,
			5, ParameterDirection.Input));
		cmd.Parameters.Add(new OracleParameter(":name", OracleDbType.Varchar2,
			"高橋", ParameterDirection.Input));
		cmd.Parameters.Add(new OracleParameter(":romaji", OracleDbType.Varchar2,
			"takahashi", ParameterDirection.Input));

		cmd.ExecuteNonQuery();

		cmd.Parameters.Clear();

		cmd.Parameters.Add(new OracleParameter(":ID", OracleDbType.Int32,
			6, ParameterDirection.Input));
		cmd.Parameters.Add(new OracleParameter(":name", OracleDbType.Varchar2,
			"武田", ParameterDirection.Input));
		cmd.Parameters.Add(new OracleParameter(":romaji", OracleDbType.Varchar2,
			"takeda", ParameterDirection.Input));

		cmd.ExecuteNonQuery();

		transaction.Commit();
	}

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

Oracle.ManagedDataAccessを取得する

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

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

 

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

 

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

関連の記事

C# Oracleに接続してselectするサンプル

△上に戻る