データベース操作プログラム Ⅰ C# 2005 8 プログラムの概要 データベースプログラムを手軽に作成する方法としては Access のフォーム機能を用いる事が 先ず考えられます ウィザードも充実しており 操作を覚えれば簡単に作成する事が出来ます 其れでは VB でデータベースプログラムを作るメリットは何んでしょうか 1 つは Access のフォーム機能に比べ コントロールが充実している事 より自由なデザインが可能です もう 1 つ重要な点は データベースソフト を選ばない事です 小規模な運用であれば Access は十分な能力を発揮しますが 中規模 大規模になってくると 力不足に成る可能性が高く成ります VB でソフトを作成して置けば Access では力不足だ と感じた時でも 今迄作成した内容を少し変更するだけで 他のデータベースソフトへ乗り換える事が可能です 逆に Access のフォーム機能で作成した場合は今迄の作業の殆どを 1 から作り直す必要が有るでしょう 此処では データベースプログラムを作成する上で不可欠な SQL 文 を主に学びます 今回の課題項目 コントロールの利用 (GroupBox RadioButton AxDataGrid) プロパティの値の取得と設定 (Height Width Tag DataMember) イベントハンドラの利用 (Handles Load Resize Click) ADODB のオブジェクト (Connection Recordset) ADODB のプロパティとメソッド (CursorLocation ConnectionString Open Close) AxDataGrid の利用 ( データバインディング DataSource) 今回の重点項目 フォームサイズ変更に伴うオブジェクトのサイズ変更 (Anchor) データベースとの接続 (ADODB.Connection ADODB.Recordset) データベースとの連結 (DataGrid:DataSource) SQL 文 (SELECT 文 : レコードを検索する文 ) 今回の応用項目 様々な SQL 文を試してみる -1-
データベース (MDB ファイル ) の格納場所 Windows7 等の 64bit 環境では bin フォルダ内の Debug フォルダに格納した MDB ファイルにアクセスると Microsoft.Jet.OLEDB.4.0 プロバイダを認識する事が出来ない 其処で メニューの ビルド から 構成マネージャー を表示させ アクティブソリューションプラットフォームを Any CPU から x86 に変更する アクティブソリューションプラットフォームの選択肢に x86 が無ければ 其の下のプルダウンリストの 新規作成 で x86 を作成する 猶 下図の様に ビルド メニューの中に 構成マネージャー が表示されて居ない場合は メニューの ツール オプション の左側にあるリストから プロジェクトおよびソリューション を選び ビルド構成の詳細を表示 にチェックを入れる -2-
プラットフォームを x86 に変更した場合 bin フォルダの中に x86 と謂う名前のフォルダが自動作成され 其の中のデータベース (mdb ファイル ) が有効に成る 接続文字列は 従来の儘 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= データベース名 " で良く 変更する必要は無い 猶 Application.StartupPath は 此の x86 フォルダに成る 参考 :C: Windows SysWOW64 フォルダの中に msjet40.dll と msjetoledb40.dll は存在する 参考 データを簡単に表示する為に 従来は ADODB.Recordset とバインディングの出来る Microsoft DataGrid Control 6.0 を使用して居たが 64bit 環境では Visual Basic 6.0 をインストールして居ない環境では C: Windows SysWOW64 に OCX ファイルを格納し ツールボックスのアイテム選択で 参照しても 正しくインストールされない ( 一部機能の使用不可 ) 従って 此処では 実際にデータの表示に頻繁に使用する DataGridView コントロールを使用して居る 併し 此のコントロールは ADODB.Recordset とデータバインディングする事は出来ないので DataSet オブジェクトを介して居る 其の為 処理が少々繁雑に成って居るが 細かい事に気を奪われず 如何に仕て データベースと接続し データの遣り取りを行うかと謂うプリミティブな処理に留意して欲しい -3-
ADODB の参照設定 Visual Studio では.NET 以降 ADO.NET が追加され 非接続型処理が可能と成ったが 従来の接続型の ADO(ADODB) も使用する事が出来 選択肢が増えた リアルタイム処理を行う接続型は データベース操作の基本と成る物で有るので 此処では ADODB を扱う事にする Visual Studio 2005 でも 従来同様 ADODB を使用するには プログラムをコンパイル 又は ビルトする際に必要と成る参照設定を行う必要が有る Visual Studio 2005 で ADODB の参照設定を行うには 先ず メニューバーの プロジェクト で 参照の追加 をクリックして ダイアログを開く ダイアログの COM タブで Microsoft ActiveX Data Object x.x Library と謂う名前のコンポーネントを選択して OK ボタンをクリックすれば 参照の追加が完了する (x.x は バージョンを示す ) -4-
オブジェクト プロパティ一覧 ラジオボタン 1 ラジオボタン 2 グループボックス ラジオボタン 3 DataGridView コントロールの種類 プロパティ プロパティの設定値 フォーム Name bookshelf Text 本棚 Font MS 明朝 標準 9 StartPosition CenterScreen グループボックス Name grptable Text 表示テーブル ラジオボタン1 Name radbook Text 本 Checked True Tag 0 ラジオボタン2 Name radcategory Text カテゴリ Tag 1 ラジオボタン3 Name radwriter Text 著者 Tag 2 DataGridView Name dgvbookshelf Anchor Top, Bottom, Left, Right DataGridView( データグリッドビュー ) データを表形式で表示するコントロール DataGridView コントロールに表示されるデータは 通常データソースから DataGridViewRow オブジェクトに格納される 簡易的に表示するには DataSet オブジェクトとバインド ( 連結 ) する事も出来る -5-
プログラムリスト データファイル data.mdb は 製作中は 当該プロジェクトの bin/x86/debug フォルダに 亦 コンパイル後の実行中は EXE ファイルと同じフォルダに 格納して置く事 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; namespace bookshelf public partial class bookshelf : Form ADODB.Connection cn; ADODB.Recordset rs; DataSet ds; System.Data.OleDb.OleDbDataAdapter da; public bookshelf( ) InitializeComponent( ); // フォームが読み込まれた時の処理 private void bookshelf_load( object sender, EventArgs e ) // コネクションとレコードセットのインスタンス生成 cn = new ADODB.Connection( ); rs = new ADODB.Recordset( ); ネームスペース ( 名前空間 ) の使用を宣言して居る 此の部分は エディタが 自動的に記述して下れる 此処で private 宣言した変数は同じフォームモジュール内の総てのプロシージャで値の参照と設定を行う事が出来る 此等のオブジェクト変数は インスタンスを生成してからでないと使用する事は出来ない クラス名と同じ名前のメソッドは コンストラクタと呼ばれ クラスのインスタンスを生成し 初期化するメソッドで有る オブジェクト変数のインスタンスを生成するには new キーワードを用いて行う事が出来る // コネクションの確立 ( データベースへの接続 ) cn.cursorlocation = ADODB.CursorLocationEnum.adUseClient; cn.open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb;", "", "", 0 ); // 選択クエリに依るレコードセットの取得 rs.open( "SELECT 本.*, 著者. 著者名 FROM 本, 著者 WHERE 本. 著者 ID= 著者.ID;", cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1 ); // データグリッドビューへの連結 da = new System.Data.OleDb.OleDbDataAdapter(); ds = new DataSet(); da.fill(ds, rs, "book"); dgvbookshelf.datasource = ds; dgvbookshelf.datamember = "book"; DataGridView の Datasource プロパティと DataMember プロパティをセットする事で DataSet のデータをバインドする (Recordset とは直接バインド出来ない ) -6-
// フォームが閉じられ様と仕た時の処理 private void bookshelf_formclosing( object sender, FormClosingEventArgs e ) // コネクションとレコードセットのクローズ rs.close( ); cn.close( ); コネクションやレコードセットは 使用後 必ず閉じて置く // コネクションとレコードセットのインスタンス開放 rs = null; cn = null; インスタンスをメモリから開放する場合 Dispose メソッドが無い時は null を代入する // フォームのサイズが変更された時の処理 private void bookshelf_resize( object sender, EventArgs e ) // サイズの最小限度フォームサイズを縮小する時の下限を設定して居る 値は 作成し if ( this.width < 300 ) this.width = 300; たフォームに応じて 適宜変更し if ( this.height < 300 ) this.height = 300; て使用する // ラジオボタン ( 本 カテゴリ 著者 ) がクリックされた時の処理 private void radbook_click( object sender, EventArgs e ) RadioButton r = ( RadioButton ) sender; int n = System.Convert.ToInt32( r.tag ); string sql = ""; switch ( n ) case 0: 複数のイベントハンドラを設定したイベントプロシージャで Tag をオブジェクトの判別に利用 各ラジオボタンに応じて 選択クエリ (SQL 文 ) を生成する sql = "SELECT 本.*, 著者. 著者名 FROM 本, 著者 WHERE 本. 著者 ID= 著者.ID;"; break; case 1: sql = "SELECT * FROM カテゴリ ;"; break; case 2: sql = "SELECT * FROM 著者 ;"; break; // レコードセットの更新 rs.open( sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1 ); // データグリッドビューへの連結 da = new System.Data.OleDb.OleDbDataAdapter(); ds = new DataSet(); da.fill(ds, rs, "book"); dgvbookshelf.datasource = ds; dgvbookshelf.datamember = "book"; 引数に選択クエリと有効なコネクションを指定してレコードセットを開くと 指定した選択クエリに応じたデータがレコードセットに格納される COM オブジェクトの参照カウントのデクリメントは System.Runtime.InteropServices.Marshal クラスに有る ReleaseComObject メソッドを使用するが 必ずしも完全では無い -7-
データの連結 此処では データベースの操作の基本に専念する為 取得データのデータグリッドビューへの表示は簡易的な方法として DataGridView の Datasource プロパティと DataMember プロパティをセットする事で DataSet のデータをバインドして居る ( 猶 Recordset とは直接バインド出来ない為 Recordset で得たデータを DataSet に充填して居る ) データの値に依り表示方法を変える等を行う場合は フィールド値を 1 個宛セルに書き込む 下記に Recordset で取得したフィールド値を DataGridView のセルに書き込む例を示す 猶 DataSet に充填しなければ Recordset は接続された儘に成るので 再使用する時には 閉じる必要が有る // データグリッドへの連結 dgvbookshelf.rows.clear(); dgvbookshelf.columns.clear(); for (int i = 0; i < rs.fields.count; i++) dgvbookshelf.columns.add("col" + i.tostring(), rs.fields[i].name); while (rs.eof == false) DataGridViewRow r = new DataGridViewRow(); r.createcells(dgvbookshelf); for (int i = 0; i < rs.fields.count; i++) r.cells[i].value = rs.fields[i].value.tostring(); dgvbookshelf.rows.add(r); rs.movenext(); DataSet に格納されたフィールド値を DataGridView のセルに書き込む時は 下記の様に成る da = new System.Data.OleDb.OleDbDataAdapter(); ds = new DataSet(); da.fill(ds, rs, "book"); for (int i = 0; i < ds.tables["book"].rows.count; i++) DataGridViewRow r = new DataGridViewRow(); r.createcells(dgvbookshelf); for (int j = 0; j < ds.tables["book"].rows[i].itemarray.length; j++) r.cells[j].value = ds.tables["book"].rows[i].itemarray[j].tostring(); dgvbookshelf.rows.add(r); 此の部分は 下記の様に記述する事も出来る ds.tables["book"].rows[i][j].tostring(); -8-
ACCESS で SQL 文を使用する ACCESS では或る条件の元に 視覚的に データを検索したり 追加したり 変更したり 削除する機能 クエリ機能 が存在する 非常に便利な機能だが 命令文而巳で データを操作出来る言語が存在する 其れが SQL で有る ACCESS 而巳使用するので有れば 此の様な 小難しく 古臭い言語を覚える必要は無い 何故なら SQL の進化系が ACCESS の様な視覚的にデータを操作する機能だからで有る 併し 未だに全世界で使われ続けて居る理由が有る ORACLE や SQLServer 等と謂う名前を聞いた事が有ると思う 共にデータベースソフトだが 然うした類の数多くは SQL を採用して居る 即ち SQL を覚えれば ACCESS 以外のデータベースソフトのデータを操作する事が出来ると謂う事で有る 亦 ACCESS も例外で無く SQL を直接入力し 実行出来る機能が有る 此処では其の機能の使用法を説明する 1. data.mdb を開き オブジェクト 内の クエリ をクリックすると 以下の様な画面に成るので デザインビューでクエリを作成する をダブルクリックする 2. テーブルの表示 ダイアログボックスが表示されるので 本 を追加後 閉じる ボタンをクリックして ダイアログボックスを閉じる -9-
3. フィールドに 本 テーブルの * をドラッグドロップ後 メニューバーの 表示 をクリックして ドロップダウンメニューから SQL ビュー (Q) を選択する 4. 上記の操作で デザインビュー で作成した選択クエリが SQL 文と成り SQL ビュー に表示される ツールバー上の ( 実行ボタン ) をクリックすると 指定したクエリーの結果が データシートビュー に表示される 此れで SQL を勉強する準備が完了した 先程の操作からも解る様に デザインビュー で操作した内容と SQL ビュー の内容は 大方連動して居る 今迄に ACCESS のクエリ機能で作成した SQL が何の様に記述されて居るのか確認するのも効果的な勉強と謂えるが 今回は 1 文宛 直接入力する事で何の様な SQL が有るかと謂う事を学ぶ事にする 以下に記述した SQL を実際に入力し 結果を確認して欲しい -10-
SQL(Structured Query Language: 構造化照会言語 ) とは 直訳すると難しく成るが プログラミング知識の無いオペレーターが簡単にデータを操作出来る様に編み出された言語で有る 国際規格 (ISO) にも定められて居る為 ORACLE でも SQLServer でも基本は同じで有る 4 つの構文を覚える事で 様々な データーベースのデータを扱う事が可能に成る SQL の 4 大構文 下記の構文を SQL の 4 大構文と謂う SELECT 文 : データを探す文 DELETE 文 : データを削除する文 INSERT 文 : データを追加する文 UPDATE 文 : データの内容を変更する文 SELECT 文の解説 は半角スペースを意味する ( 以下 総ての構文で同じ ) ACCESS で SQL 文を使用する場合は 文の末尾に ; ( セミコロン ) をつける 機能 書式 解説 データを探す構文 SELECTΔ フィールド名 ΔFROMΔ テーブル名 ΔWHEREΔ 検索条件 例文 1:SELECTΔ*ΔfromΔ 本 ; 解説 : * ( アスタリスク ) は フィールド総てを表す 本と謂う名前のテーブルに在る全件数のレコードを検索し 総てのフィールドのデータを取得する 例文 2:SELECTΔ タイトル, 価格 ΔfromΔ 本 ; 解説 : フィールドを複数指定する場合は タイトル, 価格 の様に, ( コンマ ) で区切る 本と謂う名前のテーブルに在る全件数のレコードを検索し タイトル 価格フィールドのデータを取得する 例文 3:SELECTΔ*ΔfromΔ 本 ΔWHEREΔ 価格 =2500; 解説 : * ( アスタリスク ) は フィールド総てを表す 本と謂う名前のテーブルに在る価格フィールドが 2500 のレコードを検索し 総てのフィールドのデータを取得する 例文 4:SELECTΔ*ΔfromΔ 本 ΔWHEREΔ タイトル ΔLIKEΔ %VB% ; 解説 :SQL 文内で文字列を指定する場合は ( シングルクオーテージョン ) で区切る 本と謂う名前のテーブルに在るタイトルフィールドに VB という文字列が含まれるレコードを検索し 総てのフィールド のデータを取得する 例文 5:SELECTΔ 本. タイトル, 本. 価格, 著者. 著者名 ΔfromΔ 本, 著者 ΔWHEREΔ 本. 著者 ID= 著者 ID; 解説 : 複数テーブルを関連付けた SELECT 文を記述する事が出来る 使用するテーブルは From 句の後ろに 2 つ目以降, ( コンマ ) で区切り 指定する 亦 フィールド指定は必ず テーブル名. フィールド名 とする 理由は 異なるテーブルで 同じフィールド名を使用して居るケースも有るから 本テーブルの著者 ID フィールドと著者テーブルの ID フィールドを関連付け 本. タイトル 本. 価格 著者. 著者名フィ ールドのデータを取得する -11-
DELETE 文の解説 機能 書式 解説 データを削除する構文 DELETEΔFROMΔ テーブル名 ΔWHEREΔ 条件 例文 1:DELETEΔFROMΔ 本 ΔWHEREΔ 価格 =2500; 解説 :SELECT 文と似て居るが DELETE 文の場合はフィールド名を指定し無い 本と謂う名前のテーブルの価格が 2500 と謂うデータレコードを削除する 例文 2:DELETEΔFROMΔ 本 ; 解説 : 条件を指定し無いと 総てのデータレコードが削除される 本という名前のテーブルのデータレコードすべてを削除する INSERT 文の解説 機能 データを追加する構文 書式 INSERTΔINTOΔ テーブル名 ( フィールド名, フィールド名 )ΔVALUES( 追加する数値データ, 追加する文字列データ ) 解説 例文 1:INSERTΔINTOΔ 本 ΔVALUES(9, VB ファン,4000, 2000/5/5,4,4); 解説 : 例えば VB ファン の様に 文字列型のデータを追加する場合は ( シングルクオーテーション ) で区切る 亦 フィールドとフィールドを区切る文字は, ( コンマ ) を使用する 本テーブルに ID:9 タイトル : VB ファン 価格 :4000 購入日 : 2000/5/5 カテゴリ ID:4 著者 ID:4 のレコードを追加 例文 2:INSERTΔINTOΔ 本 ( タイトル, 価格, 購入日, カテゴリ ID, 著者 ID)ΔVALUES( VB ファン,4000, 2000/5/5,4,4); 解説 : 特定のフィールドにデータを挿入し無い場合は 省略出来る 上記の例では ID フィールドを省略して居る 実行した後 データを見ると ID フィールドには 0 が代入されて居る 猶 文字列型のフィールドの場合は空白と成る 此の状態を NULL と謂う 亦 オートナンバー型フィールドの場合は自動で番号が割り振られる 本テーブルに タイトル : VB ファン 価格 :4000 購入日 : 2000/5/5 カテゴリ ID:4 著者 ID:4 のレコードを追加する UPDATE 文の解説 機能 データ内容を変更する構文 書式 UPDATE テーブル名 ΔSETΔ フィールド名 = 変更する数値データ, フィールド名 = 変更する文字列データ ΔWHEREΔ 変更するデータの条件 解説 例文 1:UPDATEΔ 本 ΔSETΔ 価格 =8000, 購入日 = 2000/6/5 ΔWHEREΔ タイトル = VB ファン ; 解説 : 総てのデータを変更したい場合は WHERE は必要無いが 特定のレコードを変更したい場合は指定する 本テーブルのタイトルフィールドが VB ファン と謂うレコードの価格を 8000 に 購入日を 2000/6/5 に変更する SQL 文で文字列を記述する場合は ( シングルクオーテーション ) で囲む -12-
recordset.open Source, ActiveConnection, CursorType, LockType, Options Source には SQL ステートメント テーブル名 ストアドプロシージャ等を指定し 省略する事が出来る ActiveConnection には 有効な Connection オブジェクトを指定し 省略する事が出来る CursorType には Recordset を開く際にプロバイダが使うカーソルタイプを決める為の値を 下記の CursorTypeEnum 定数で指定し 省略する事が出来る 定数 説明 adopenforwardonly 前方スクロールタイプカーソルを開く ( 規定値 ) adopenkeyset キーセットカーソルを開く adopendynamic 動的カーソルを開く adopenstatic 静的カーソルを開く LockType には Recordset を開く際にプロバイダが使うロック ( 同時実行制御 ) の種類を決める為の値を 下記の LockTypeEnum 定数で指定し 省略する事が出来る 定数説明 adlockreadonly 読み取り専用 ( 規定値 データの変更は出来ない ) adlockpessimistic レコード毎の排他的ロック ( 通常 編集の際にプロバイダがデータソースでレコードをロックする事に依り 確実にレコードを編集出来る為に必要な最小限のロックを使用する ) adlockoptimistic レコード毎の共有的ロック (Update メソッドを呼び出した場合而巳 プロバイダが共有的ロックを使用してレコードをロックする ) adlockbatchoptimistic 共有的バッチ更新 ( 即時更新モードに対して バッチ更新モードの場合に必要で有る ) Options には Source 引数が Command オブジェクト以外の設定値を表す場合 又は 以前に保存していたファイルから Recordset を復元する場合に プロバイダが引数を評価する方法を示す為の値を 下記の定数で指定し 省略する事が出来る adcmdtext adcmdtable 定数値説明 1 プロバイダがSource をコマンド文字列に依る定義として評価する 2 ADO がSource に名前を付けられたテーブルから総ての行を返すSQL クエリーを生成する adcmdtabledirect 512 プロバイダがSource に名前を付けられたテーブルから総ての行を返す adcmdstoredproc 4 プロバイダがSource をストアドプロシージャとして評価する adcmdunknown Source 引数に指定されたコマンドタイプが不明で有る adcmdfile 256 Source 引数に指名されたファイルから保存されたRecordset を復元する adasyncexecute 16 Source 引数を非同期実行する adasyncfetch 32 CacheSize プロパティに指定された初期量の残りの行を非同期でフェッチ する -13-
connection.open ConnectionString, UserID, Password, OpenOptions ConnectionString には 接続情報を含む文字列を指定し 省略する事が出来る UserID には 接続を確立する際に必要なユーザー名を表す文字列を指定し 省略する事が出来る Password には 接続を確立する際に必要なパスワードを表す文字列を指定し 省略する事が出来る OpenOptions には OpenOptionEnum 値を指定し 省略する事が出来る adasyncconnect に設定すると 接続を非同期で開く 接続が使用可能に成った時 ConnectComplete イベントが発生する 定数 値 説明 adasyncconnect 16 接続を非同期で開く adconnectunspecified 無設定で開く 行を返さないコマンド文字列の場合の構文は下記の通りで有る connection.execute CommandText, RecordsAffected, Options 行を返すコマンド文字列の場合の構文は下記の通りで有る Set recordset = connection.execute (CommandText, RecordsAffected, Options) CommandText には 実行する SQL ステートメント テーブル名 ストアドプロシージャ 又は プロバイダ固有のテキストを含む文字列型の値を指定し 必ず指定する RecordsAffected には プロバイダが返す操作の結果を反映するレコード数を長整数型の変数で指定し 省略する事が出来る Options には プロバイダが CommandText 引数を評価する方法を示す長整数型の値を 下記の定数で指定し 省略する事が出来る 定数 値 説明 adcmdtext 1 CommandText をコマンド文字列に依る定義と仕て評価する事を指定 adcmdtable 2 ADO が CommandText に名前を付けられたテーブルから総ての行を返す SQL クエリを生成する事を指定 adcmdtabledirect 512 プロバイダが CommandText に名前を付けられたテーブルから総ての行を返す事を指定 adcmdstoredproc adcmdunknown adasyncexecute adasyncfetch 4 プロバイダが CommandText をストアドプロシージャと仕て評価する事を指定 CommandText のコマンドタイプが不明で有る事を指定 16 コマンドを非同期実行する事を指定 32 CacheSize プロパティに指定された初期量の残りの行を非同期でフェッチする様に指定 -14-