OTN Japan - はじめてのODP.NETアプリケーション開発：第2回

はじめてのODP.NETアプリケーション開発

第2回：ADO.NET とデータベース操作

日本オラクルサポートサービス本部
システム製品サポート2部ツールグループ
八坂和男

◆ はじめに

Oracle Data Provider for .NET(ODP.NET) はADO.NET インターフェースのOracleによる実装です。今回はOracle データベース操作を行う上で必要なADO.NET の概要とODP.NET での使用方法について、具体的なサンプルを交えご紹介します。
前回のソースを変更し、以下の目次にあるデータベース操作の基本を説明します。

◆ ADO.NET インターフェースの概要

ADO.NET はOracle Provider for OLE DB などで使用したActiveX Data Objects（ADO) の.NET クラスライブラリに対応した実装を提供しています。ADO.NET は.NET Framework のClassLibrary を基盤とした構成であるのに対してADO はCOM (OLE)を基盤とした旧来の手法です。このようにまったく異なるアプローチで構成されていますが、使用方法や考え方の多くは近いものがありますので、ADO をご存知の方はその違いに気をつけて実装すれば問題ないでしょう。

ODP.NET のクラスはADO.NET で提供されているインターフェースを実装し、Oracle の独自の拡張を加えています。ADO.NET のインターフェースはメソッドとプロパティのセットであり、基本的な定義と考えることができます。各インターフェースを実装したものがクラスで、各クラスではメソッドやプロパティの具体的な処理を実装しています。そのため、同じインターフェースを実装しているOLEDB.NET やODBC.NETのクラスにも同様なメソッドやプロパティがあり、基本的な動作は大変似通っています。したがって、ODP.NET の拡張点さえ意識すれば、OLE DB やMicrosoft SQL Server 用のADO.NET を使用したサンプルでも若干の変更を加えるだけでそのまま動作させることができます。

ODP.NET の各基本クラス名とそのインターフェース、さらにOLE DB .NET のクラス名は以下のとおりです。このように、同じインターフェースを実装した各クラス名は、非常に似た名称になっています。

ADO.NET インターフェースクラス定義

機能	インターフェース	ODP.NET クラス	OLE DB.NET クラス
Connection	IDbConnection	OracleConnection	OleDbConnection
Command	IDbCommand	OracleCommand	OleDbCommand
DataAdaper	DbDataAdapter, IDbDataAdapter	OracleDataAdapter	OleDbDataAdapter
DataReader	IDataReader, IDataRecord	OracleDataReader	OleDbDataReader
CommandBuilder	Component	OracleCommandBuilder	OleDbCommandBuilder

次に示すのは各クラスの関連イメージ図です。
各クラスについての詳細説明はそれぞれのリファレンスに譲り、実際に動作するサンプルを元に具体的な使用方法をご紹介します。

◆ 接続・切断とエラーハンドリング

接続にはOracleConnetion を使用します。すでに第一回のサンプルでも接続を実行できていますが、エラー・ハンドリングを行っていませんでした。そのためエラーが発生した際Visual Studio のインストールされている環境では以下のデバッガの選択画面が表示され、「いいえ」を選ぶと「ハンドルされていない例外」としてエラーが出力されていました。

Visual Studio 2003 環境でのエラー時のダイアログ画面

正しくエラーハンドリングを行うには以下のようにtry…catch 構文を使用して記述します。

static void Main()
   {
     try
         {
           Example example = new Example();
                   example.Connect();
                   example.Close();
         }
     catch(Exception ex)
          {
            Console.WriteLine(ex.Message);
          }
   }

さらにORA エラーの番号だけ取得してそのエラー番号をもとに何らかの処理を記述するといった場合には、OraceException クラスを使用することで上記と同様にエラーメッセージを取得したり、ORA エラーの番号だけを取得するといったことが可能です。

catch (OracleException oraerr)
     {
        Console.WriteLine(oraerr.Message);
        Console.WriteLine(oraerr.Number);
     }

最初に示したException ではORA のエラー番号だけを取得することはできませんので、そのような場合にはOracleException を使用し、Oracle 以外の実行時のエラーを含めすべて取得したい場合にはExeception を使用します。

切断時の記述は以下のとおりDispose() メソッドを実行します。Close() メソッドだけでは明示的に接続が解放されず、.NET Framework の共通言語ランタイム(CLR)によるガベージコレクタによってクラスが開放されることではじめて切断される動作になります。安全なプログラムを作成するという観点から明示的にDispose するべきです。

void Close()
  {
    con.Close();
    con.Dispose();
  }

◆ 接続型と非接続型でのデータ取得

ADO.NET では従来のADO と同様にデータベースに接続したまま直接処理する接続型の処理方法と、データベースとの接続を一旦切った状態で処理可能な非接続型の処理方法を提供しています。前者はOracleDataReader やOracleCommand オブジェクトを介してデータベースのデータを接続したまま直接扱います。後者は取得したデータをDataSet というクライアントのメモリ上に取られたデータキャッシュに格納し、そのキャッシュされたデータを操作します。

●接続型
はじめて登場するクラスがたくさん出てきますが、各クラスの概要はリファレンスを参照してください。ここでは具体的な使用方法を説明します。
まず、OracleCommand にSQL 文と接続オブジェクトcon(OracleConnection)を設定します。OracleCommand クラスのExecuteReader() メソッドを実行し、その結果をOracleReader クラスで取得、その後OracleDataReader で取得した中身に対してRead メソッドを実行し値を全行出力しています。OracleDataReader クラスは読み取り専用の高速なアクセスを提供しますので、このような単純な読込処理に向いています。しかし、後方参照(前の行にさかのぼって参照すること)ができません。このようにOracleDataReader はRead only かつForward only のクラスです。また、一度のReadメソッドの実行で読み込まれるデータは1行だけです。そのため、以下のように全行取得する際にはループ処理が必要になります。この一連の処理の間、常にデータソースと接続されたまま処理します。

void Reader()
    {
        string strSQL = "select ename from emp";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        OracleDataReader myReader = myCmd.ExecuteReader();
        Console.WriteLine("Read from OracleDataReader:");
        while(myReader.Read())
        {
            Console.WriteLine(myReader.GetString(0));
        }
            myReader.Close();
            myReader.Dispose();
    }

●非接続型
このサンプルでは接続型のサンプルとは違い、OracleDataAdapterのFill メソッドを実行し、用意したDataSet へデータを格納しています。この例では1つのテーブルだけがDataSet に格納されており、Table[0] が実際に取得したEmp 表になります。DataRow[0] はSELECT 文で指定した選択リストの最初のカラムを表しますので、DataRow dr[0]はename列を表します。dr[1]を指定すればempno 列の値が取得できます。
このサンプルでは値を取得しただけですが、Fill メソッドが実行されてDataSet に値が入るとその時点でデータベースの内容とクライアントのDataSet のデータに直接の関連は無くなり、それぞれの変更はお互いに反映されません。そのため、この処理の後にデータベースのデータに変更があってもそのままではDataSet には反映されませんし、DataSet の値の変更についても同じです。また、物理的な接続自体も切断していてかまいません。OracleDataReader でのRead メソッド時の動作と違い、Fill メソッド実行時にすでにデータベースから全行分の値が取得されています。

void ReadDataSet()
    {
        string strSQL = "select ename,empno from emp";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        OracleDataAdapter   myDa   = new OracleDataAdapter(myCmd);
        Console.WriteLine("Read from DataSet:");
        DataSet myDs = new DataSet();
        myDa.Fill(myDs, "emp"); 
        foreach(DataRow dr in myDs.Tables[0].Rows)
        {
            Console.WriteLine(dr[0]);
     }

◆ 値の挿入、削除および更新

Insert やDeleteおよびUpdate といった結果セットを返さないSQLはOracleCommandオブジェクトにSQL 文を設定し、ExecuteNonQuery メソッドを実行します。

void delData()
    {
        string strSQL = "delete from emp where empno=9999";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        myCmd.ExecuteNonQuery();
    }

上記のDelete 文はWhere 句でEmpno の値9999 のデータを削除していますが、最初からこのデータは存在しないので、このSQL 文の結果は0行です。これは操作対象となる行が存在せず、0行を処理したという結果が返されていますのでエラーは返りません。

そこで、OracleCommand のExecuteNonQuery の返値には実際に処理した行数が返りますのでそちらを確認するコードを追加します。以下の例では1行以上処理されていれば成功とみなしています。

int res = myCmd.ExecuteNonQuery();
        if (res >= 1)
            Console.WriteLine("Delete Success");
        else
            Console.WriteLine("No Data to Delete");

Insert 文も同様に実行できます。こちらはあらかじめ上記のチェックを実装済みでInsert 文のため1行処理されれば成功としています。

void insData()
    {
        string strSQL = "Insert into emp(empno,ename,deptno) values(9999,'ODP.NET',10)";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        int res = myCmd.ExecuteNonQuery();
        if (res == 1)
              Console.WriteLine("Insert Success");
        else
              Console.WriteLine("Insert failed");
     }

しかし、上記の例では2回このプログラムを実行すると、エラーとして「ORA-00001: 一意制約(SCOTT.PK_EMP)に反しています」が発生しますので、実際には”Insert failed ” の表示は確認できません。このような場合はInsert時に直接try …catch 文でエラーを取得します。次のトランザクション処理でまとめてサンプルを示します。

◆ トランザクション処理

ODP.NETでのSQL操作はすべてデフォルトではAutoCommit です。そこで、トランザクション処理を実行する際はOracleTransaction クラスを使い設定します。トランザクションの開始時にBeginTransaction()を実行し、Commit 時にはCommti() メソッドを、Rollback 時にはRollback() メソッドを実行します。

void insData()
    {
        string strSQL = "Insert into emp(empno,ename,deptno) values(9999,'ODP.NET',10)";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        OracleTransaction txn = con.BeginTransaction();
            try
            {
                int res = myCmd.ExecuteNonQuery();
                if (res == 1)
                {
                    txn.Commit();
                    Console.WriteLine("Insert Success and Commited.");
                }
            }
            catch(Exception e)
            {
                txn.Rollback();
                Console.WriteLine("No record was inserted ." + e.Message);
            }
    }

◆ まとめ

以上でデータベースへの一通りの処理が出来るようになりました。
前回のExample.cs をExample2.cs として今回の手順をすべて実行したものが以下のソースになります。
DataSet から取得したデータの表示方法として、一部変更を加えていますのでコメントを参考にしてください。

/* Example2.cs
   Create make file as follows(as of your Oracle_Hom\bin);
    /r:system.dll,system.data.dll,c:\oracle\ora920\bin\Oracle.DataAccess.dll
   csc @make Example2.cs
*/ 
using System;
using System.Data;
using Oracle.DataAccess.Client;
class Example
{
    OracleConnection con;
    void Connect()
    {
        con = new OracleConnection();
        con.ConnectionString = "User Id=scott;Password=tiger;Data Source=ExampleDB";
        con.Open();
        Console.WriteLine("Connected to Oracle");
        Console.WriteLine(con.ServerVersion);
    }
    void Close()
    {
        con.Close();
        con.Dispose();
    }
    void Reader()
    {
        string strSQL = "select ename from emp";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        OracleDataReader myReader = myCmd.ExecuteReader();
        Console.WriteLine("Read from OracleDataReader:");
        while(myReader.Read())
        {
            Console.WriteLine(myReader.GetString(0));
        }
            myReader.Close();
            myReader.Dispose();
    }
    void ReadDataSet()
    {
        string strSQL = "select ename,empno from emp";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        OracleDataAdapter   myDa   = new OracleDataAdapter(myCmd);
        Console.WriteLine("Read from DataSet:");
        DataSet myDs = new DataSet();
        myDa.Fill(myDs, "emp"); 
/* ---DataSetの1列目のみ表示---
        foreach(DataRow dr in myDs.Tables[0].Rows)
        {
            Console.WriteLine(dr[0]);
        }
*/
/* ----全列の値を取得して整形して表示---*/
        foreach (DataRow dr in myDs.Tables[0].Rows) 
        { 
            for (int i = 0; i<myDs.Tables[0].Columns.Count; i++)
            {
                Console.Write("{0,10}", dr[i]);
            }
            Console.Write("\n");
        }
    }
    void delData()
    {
        string strSQL = "delete from emp where empno=9999";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        int res = myCmd.ExecuteNonQuery();
        if (res >= 1)
            Console.WriteLine("Delete Success");
        else
            Console.WriteLine("No Data to Delete");
    }
    void insData()
    {
        string strSQL = "Insert into emp(empno,ename,deptno) values(9999,'ODP.NET',10)";
        OracleCommand myCmd = new OracleCommand(strSQL, con);
        OracleTransaction txn = con.BeginTransaction();
            try
            {
                int res = myCmd.ExecuteNonQuery();
                if (res == 1)
                {
                    txn.Commit();
                    Console.WriteLine("Insert Success and Commited.");
                }
            }
            catch(Exception e)
            {
                txn.Rollback();
                Console.WriteLine("No record was inserted ." + e.Message);
            }
    }
    static void Main()
    {
        try
        {
            Example example = new Example();
            example.Connect();
            example.Reader();
            example.insData();
            example.delData();
            example.ReadDataSet();
            example.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

コンパイル方法は以下のとおりです。

	（1）	コマンド・プロンプトにてvsvars32 を実行し環境設定
	（2）	以下の1行を記入したテキスト・ファイルをmake として拡張子なしで作成 /r:system.dll,system.data.dll,c:\oracle\ora920\bin\Oracle.DataAccess.dll
	（3）	csc @make Example2.cs でコンパイルします。
	（4）	Example2.exe を実行

今回はここまでです。
次回はADO.NET を使用したデータベース操作の基本のうち、PL/SQL の処理とOracleDataAdapter とOracleCommandBuilder によるデータの取得からデータの書き戻しまでをGUI 上で行うサンプルをご紹介します。