データベース操作プログラム Ⅰ VB 2005 8 プログラムの概要 データベースプログラムを手軽に作成する方法としては Access のフォーム機能を用いる事が 先ず考えられます ウィザードも充実しており 操作を覚えれば簡単に作成する事が出来ます 其れでは VB でデータベースプログラムを作るメリットは何んでしょうか 1 つは Access のフォーム機能に比べ コントロールが充実している事 より自由なデザインが可能です もう 1 つ重要な点は データベースソフト を選ばない事です 小規模な運用であれば Access は十分な能力を発揮しますが 中規模 大規模になってくると 力不足に成る可能性が高く成ります VB でソフトを作成して置けば Access では力不足だ と感じた時でも 今迄作成した内容を少し変更するだけで 他のデータベースソフトへ乗り換える事が可能です 逆に Access のフォーム機能で作成した場合は今迄の作業の殆どを 1 から作り直す必要が有るでしょう 此処では データベースプログラムを作成する上で不可欠な SQL 文 を主に学びます 今回の課題項目 コントロールの利用 (GroupBox RadioButton DataGridView) プロパティの値の取得と設定 (Height Width Tag DataSource 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 ファイルと同じフォルダに 格納して置く事 Public Class bookshelf ' フォームレベルでグローバルな変数の宣言 Private Cn As ADODB.Connection Private Rs As ADODB.Recordset Private Ds As DataSet Private Da As OleDb.OleDbDataAdapter 此等のオブジェクト変数は インスタンスを生成してからでないと使用する事は出来ない ' フォームが読み込まれた時の処理 Private Sub bookshelf_load(byval sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load ' コネクションとレコードセットのインスタンス生成 Cn = New ADODB.Connection Rs = New ADODB.Recordset オブジェクト変数のインスタンスを生成するには New キーワードを用いて行う事が出来る ' コネクションの確立 ( データベースへの接続 ) Cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb;" Cn.Open() ' 選択クエリに依るレコードセットの取得 Rs.Open("SELECT 本.*, 著者. 著者名 FROM 本, 著者 WHERE 本. 著者 ID= 著者.ID;", Cn) ' データグリッドへの連結 Da = New OleDb.OleDbDataAdapter() Ds = New DataSet() Da.Fill(Ds, Rs, "book") dgvbookshelf.datasource = Ds dgvbookshelf.datamember = "book" ' ラジオボタンの設定 radbook.checked = True AddHandler radbook.click, AddressOf radbook_click AddHandler radcategory.click, AddressOf radbook_click AddHandler radwriter.click, AddressOf radbook_click End Sub DataGridView の Datasource プロパティと DataMember プロパティをセットする事で DataSet のデータをバインドする (Recordset とは直接バインド出来ない ) ' フォームのサイズが変更された時の処理 Private Sub bookshelf_resize(byval sender As Object, ByVal e As System.EventArgs) _ Handles Me.Resize ' サイズの最小限度 If Me.Width < 300 Then Me.Width = 300 If Me.Height < 300 Then Me.Height = 300 End Sub フォームサイズを縮小する時の下限を設定して居る 値は 作成したフォームに応じて 適宜変更して使用する -6-
' フォームが閉じられ様と仕た時の処理 Private Sub bookshelf_formclosing( ByVal sender As Object, _ ByVal e As System.Windows.Forms.FormClosingEventArgs ) Handles Me.FormClosing ' コネクションとレコードセットのインスタンス開放 Rs = Nothing Cn = Nothing End Sub インスタンスをメモリから開放する場合 Dispose メソッドが無い時は Nothing を代入する ' ラジオボタン ( 本 カテゴリ 著者 ) がクリックされた時の処理 Private Sub radbook_click(byval sender As System.Object, ByVal e As System.EventArgs) Dim R As RadioButton = DirectCast(sender, RadioButton) Call DispData(Integer.Parse(R.Tag.ToString())) End Sub ' データを表示するジェネラルプロシージャ Private Sub DispData(ByVal N As Integer) Dim SQL As String = "" Dim Tbl As String = "" Select Case N Case 0 SQL = "SELECT 本.*, 著者. 著者名 FROM 本, 著者 WHERE 本. 著者 ID= 著者.ID;" Tbl = "book" Case 1 SQL = "SELECT * FROM カテゴリ ;" Tbl = "category" Case 2 SQL = "SELECT * FROM 著者 ;" Tbl = "writer" End Select ' レコードセットのクローズ If Rs.State = 1 Then Rs.Close() End If ' adstateclosed = 0 ' adstateopen = 1 ' オブジェクトが閉じて居る事を示す ' オブジェクトが開いて居る事を示す 複数のイベントハンドラを設定したイベントプロシージャで Tag をオブジェクトの判別に利用 各ラジオボタンに応じて 選択クエリ (SQL 文 ) を生成する 開かれて居るレコードセットに対して Open メソッドを実行するとエラーが発生するので 一旦 レコードセットを Close メソッドを用いて閉じる ' レコードセットの更新 Rs.Open(SQL, Cn) ' データグリッドへの連結 dgvbookshelf.datasource = Nothing dgvbookshelf.datamember = Nothing Ds.Clear() Da.Fill(Ds, Rs, Tbl) dgvbookshelf.datasource = Ds dgvbookshelf.datamember = Tbl End Sub End Class 引数に選択クエリと有効なコネクションを指定してレコードセットを開くと 指定した選択クエリに応じたデータがレコードセットに格納される COM オブジェクトの参照カウントのデクリメントは System.Runtime.InteropServices.Marshal クラスに有る ReleaseComObject メソッドを使用が 必ずしも完全では無い -7-
ACCESS で SQL 文を使用する ACCESS では或る条件の元に 視覚的に データを検索したり 追加したり 変更したり 削除する機能 クエリ機能 が存在する 非常に便利な機能だが 命令文而巳で データを操作出来る言語が存在する 其れが SQL で有る ACCESS 而巳使用するので有れば 此の様な 小難しく 古臭い言語を覚える必要は無い 何故なら SQL の進化系が ACCESS の様な視覚的にデータを操作する機能だからで有る 併し 未だに全世界で使われ続けて居る理由が有る ORACLE や SQLServer 等と謂う名前を聞いた事が有ると思う 共にデータベースソフトだが 然うした類の数多くは SQL を採用して居る 即ち SQL を覚えれば ACCESS 以外のデータベースソフトのデータを操作する事が出来ると謂う事で有る 亦 ACCESS も例外で無く SQL を直接入力し 実行出来る機能が有る 此処では其の機能の使用法を説明する 1. data.mdb を開き オブジェクト 内の クエリ をクリックすると 以下の様な画面に成るので デザインビューでクエリを作成する をダブルクリックする 2. テーブルの表示 ダイアログボックスが表示されるので 本 を追加後 閉じる ボタンをクリックして ダイアログボックスを閉じる -8-
3. フィールドに 本 テーブルの * をドラッグドロップ後 メニューバーの 表示 をクリックして ドロップダウンメニューから SQL ビュー (Q) を選択する 4. 上記の操作で デザインビュー で作成した選択クエリが SQL 文と成り SQL ビュー に表示される ツールバー上の ( 実行ボタン ) をクリックすると 指定したクエリーの結果が データシートビュー に表示される 此れで SQL を勉強する準備が完了した 先程の操作からも解る様に デザインビュー で操作した内容と SQL ビュー の内容は 大方連動して居る 今迄に ACCESS のクエリ機能で作成した SQL が何の様に記述されて居るのか確認するのも効果的な勉強と謂えるが 今回は 1 文宛 直接入力する事で何の様な SQL が有るかと謂う事を学ぶ事にする 以下に記述した SQL を実際に入力し 結果を確認して欲しい -9-
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 フィールドを関連付け 本. タイトル 本. 価格 著者. 著者名フィ ールドのデータを取得する -10-
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 文で文字列を記述する場合は ( シングルクオーテーション ) で囲む -11-