VB実用③　アクセス操作Ⅰ

Transcription

1 VB でアクセス操作 Ⅰ VB プログラムの概要 此処では コントロールを使用しないで コードに依るデータベースへの接続とデータの取得の方法を学ぶ コントロールに依るデータベース接続は 簡単にデータベースを操作する事が出来ると謂う利点が有り 小規模な運用で有れば 充分な能力を発揮する 併し 中規模 大規模に成り 複雑な操作を必要とする様に成れば 力不足に成る可能性が高く成る 然う成れば 矢張り コードに依りデータベースを操作する事が必要と成る 其の為 両者を 必要に応じて 使い分ける事が出来る様に 孰れにも精通して置く事が 開発効率を高め 良質のプログラムを作成する上で 必要な事で有る 今回の課題項目 ADO.NET に依るデータベース接続 ( コネクションオブジェクト ) ADO.NET に依るデータベース接続 (ConnectionString プロパティ Open メソッド ) ADO.NET に依るデータベース操作 ( データアダプタオブジェクト Fill メソッド ) ADO.NET に依るテーブルデータ操作 (Tables コレクション Count プロパティ ) ADO.NET に依るレコードデータ操作 (Rows コレクション Count プロパティ ) ADO.NET に依るフィールドデータ操作 (Columns コレクション Count プロパティ ) ADO.NET に依るフィールドデータ操作 (ColumnName プロパティ Caption プロパティ ) ADO.NET に依るフィールドデータ操作 (Item コレクション ToString メソッド ) コネクションの切断 ( コネクションオブジェクト Close メソッド ) オブジェクト変数の開放 ( オブジェクト.Dispose) プログラムの強制終了 (End ステートメント ) 今回の重点項目 ADO.NET に依るデータベース接続 ( コネクションオブジェクト ) ADO.NET に依るデータベース接続 (ConnectionString プロパティ Open メソッド ) ADO.NET に依るデータベース操作 ( データアダプタオブジェクト Fill メソッド ) SQL 文 (SELECT 文 : レコードを検索する文 ) -1-

2 オブジェクト プロパティ一覧 ラベル 1 ラベル 2 リストボックス 1 リストボックス 2 フレーム ラジオボタン 1 ラジオボタン 2 ラジオボタン 3 コントロールの種類 プロパティ プロパティの設定値 フォーム Name access 本棚データベース ラベル1 Name lblitem 列見出し ラベル2 Name lblfield フィールドデータ リストボックス1 Name lstitem リストボックス2 Name lstfield フレーム Name grptable 表示テーブル ラジオボタン1 Name radbook 本 Checked True ラジオボタン2 Name radcate カテゴリ ラジオボタン3 Name radwriter 著者 データベースの用意此処では 現時点で最も普及して居るデータベースで有る ACCESS データベースを使用する事にする ACCESS データベース (MDB ファイル ) で有れば 何でも良いが 此処では テスト用に用意した data.mdb を使用する 猶 此のファイルを デバッグ時には bin フォルダの Debug フォルダに 亦 実行時には exe ファイルと同じフォルダに 事前に格納して置く -2-

3 プログラムリスト ' 名前空間のインポート Imports System.Data Imports System.Data.OleDb Public Class access ' 同一クラス内でグローバルな変数の宣言 Private Cn As OleDbConnection Private Ds As DataSet Private DaB As OleDbDataAdapter Private DaC As OleDbDataAdapter Private DaW As OleDbDataAdapter Private Tn As String = "BOOK" 記述を簡略化する為に 名前空間をインポートして居る 此れに依り 名前空間以下のクラスは 名前空間を省略して記述出来る 此処で宣言した変数は同じクラス内の総てのサブプロシージャで値の参照と設定を行う事が出来る クラスレベルでグローバルな変数 Tn には 対象と成るテーブル名を初期値と仕て設定して居る ' フォームが読み込まれた時の処理 Private Sub access_load( ByVal sender As Object, ByVal e As System.EventArgs ) _ Handles Me.Load ' データベースへの接続情報の設定 Cn = New OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb" ) ' データアダプタの生成 DaB = New OleDbDataAdapter( "SELECT * FROM 本 ", Cn ) DaC = New OleDbDataAdapter( "SELECT * FROM カテゴリ ", Cn ) DaW = New OleDbDataAdapter( "SELECT * FROM 著者 ", Cn ) ' データセットの生成 Ds = New DataSet ' SQL の発行と取得したデータのデータセットへの格納 DaB.Fill( Ds, "BOOK" ) DaC.Fill( Ds, "CATEGORY" ) DaW.Fill( Ds, "WRITER" ) ' データセットよりフィールド名をリストボックスに追加 Call DispItemList( Tn ) ' フォームが閉じられ様と仕た時の処理 Private Sub access_formclosing( ByVal sender As Object, _ ByVal e As System.Windows.Forms.FormClosingEventArgs ) _ Handles Me.FormClosing ' オブジェクト型変数の破棄 Ds.Dispose( ) DaB.Dispose( ) DaC.Dispose( ) DaW.Dispose( ) Cn.Close( ) Cn.Dispose( ) End New キーワードは 宣言ステートメントか代入ステートメントで使用し オブジェクトの新しいインスタンスを生成する Call ステートメントは ジェネラルプロシージャ ( メソッド ) を呼び出す制御構文で有る 省略する事も出来るが 自作のメソッドには 必ず付ける様に仕て居る 使用済みのオブジェクトは 必要が無く成った時点で 必ず 速やかに閉じて メモリから開放して置く様に心掛ける Connection オブジェクトは 必要が無く成った時点で 必ず 速やかに閉じる様に心掛ける -3-

4 ' リストボックス ( 列見出し ) の選択項目が変化した時の処理 Private Sub lstitem_selectedindexchanged( ByVal sender As System.Object, _ ByVal e As System.EventArgs ) _ Handles lstitem.selectedindexchanged 此処で宣言した変数は 宣言したサブプロシージャ内でしか値の参 Dim S As String = lstitem.items( lstitem.selectedindex ) 照と設定を行う事が出来ない lstfield.items.clear( ) For I As Integer = 0 To ( Ds.Tables( Tn ).Rows.Count - 1 ) lstfield.items.add( Ds.Tables( Tn ).Rows( I ).Item( S ).ToString) Next ToString メソッドは データがヌルの場合 VB で取り扱う事の出 来るヌルストリングに変更する ' ラジオボタンがクリックされた時の処理 Private Sub radbook_click( ByVal sender As Object, ByVal e As System.EventArgs ) _ Handles radbook.click, radwriter.click, radcate.click Dim R As RadioButton = CType( sender, RadioButton ) ' ラジオボタンに依り使用するテーブルの設定 Select Case R. Case " 本 " Tn = "BOOK" Case " カテゴリ " Tn = "CATEGORY" Case " 著者 " Tn = "WRITER" End Select ' ジェネラルプロシージャの呼出 Call DispItemList( Tn ) 1つの式の値を複数の値と比較する条件が多数有る場合は Select Case 制御構造を使うと 効率的で読み易いコードを記述出来る 複数の箇所で同じコードを記述する場合は ジェネラルプロシージャ ( メソッド ) に纏める方が 保守面でも優れて居る 此処で宣言した変数は 宣言した ' 列見出しを表示するジェネラルプロシージャブロック内でしか値の参照と設定 Private Sub DispItemList( ByVal T As String ) を行う事が出来ない lstitem.items.clear( ) lstfield.items.clear( ) For I As Integer = 0 To ( Ds.Tables( T ).Columns.Count - 1 ) lstitem.items.add( Ds.Tables( T ).Columns( I ).ColumnName ) ' 下記でも同じ結果を得る事が出来る ' lstitem.items.add( Ds.Tables( T ).Columns( I ).Caption ) For~Next は 指定した回数 ブ Next ロック内の処理を繰り返すループ 処理と呼ばれる構文で有る End Class 上記の For 文は For Each 文を使用して 下記の様に記述する事も出来る For Each C As DataColumn In Ds.Tables(T).Columns lstitem.items.add(c.columnname) Next 此の様に コードの記述方法は 必ずしも一通りでは無い 各自の習熟度に合わせた記述法を使用すれば良い 意図した通りの動きをすれば 先ずは正しいコードで有る -4-

5 プロバイダファクトリの利用 前記では ACCESS データベースを操作する為に プロバイダに OleDbConnection オブジェクトと OleDbDataAdapter オブジェクトの固有プロバイダを使用して居る 固有プロバイダでは 接続するデータベースの種類に依り 使用するコネクションやデータアダプタを使い分ける必要が有る データベースの種類と使用するコネクションやデータアダプタを 下記に示す データプロバイダ コネクション データアダプタ Data Provider for SQL Server SqlConnection SqlDatAdapter Data Provider for OLE DB OleDbConnection OleDbDataAdapter Data Provider for ODBC OdbcConnection OdbcDataAdapter Data Provider for Oracle OracleConnection OracleDataAdapter 此れでは 同じデータベースを使用する限りには 問題が無いが データベースの種類を変更 ( 例えば ACCESS から SQL Server に移行 ) すると コネクションやデータアダプタの種類を総て変更する必要が有る 其処で.NET Framework version 2.0 では System.Data.Common 名前空間に 基本クラスで有る汎用的なコネクション (DbConnection) やデータアダプタ (DbDataAdapter) 等が 新しく追加された 此等の汎用的なコネクションやデータアダプタは プロバイダファクトリ (DbProviderFactory) に依り 固有プロバイダのインスタンス化を行う 下記に 此等を用いて 前記のコードを書き直した 特定のデータプロバイダに依存しない汎用的なプログラミング例を示す Imports System.Data Imports System.Data.Common Public Class access Private Const PROVIDER_NAME As String = "System.Data.OleDb" Private Const CONNECTION_STRING As String = _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb" Private Cn Private Ds As DbConnection As DataSet Private DaB As DbDataAdapter Private DaC As DbDataAdapter Private DaW As DbDataAdapter 固有プロバイダの種類と接続文字列は 此の様に 定数で宣言して置くと データベースを変更しても 此の部分丈を書き直せば良い事に成る Private Tn As String = "BOOK" ' フォームが読み込まれた時の処理 Private Sub access_load( ByVal sender As Object, ByVal e As System.EventArgs ) _ Handles Me.Load ' プロバイダファクトリの生成 Dim Fa As DbProviderFactory = DbProviderFactories.GetFactory(PROVIDER_NAME) ' 固有プロバイダの生成 Cn = Fa.CreateConnection( ) -5-

6 ' 接続のオープン Cn.ConnectionString = CONNECTION_STRING Cn.Open( ) ' データアダプタの生成 DaB = Fa.CreateDataAdapter( ) DaB.SelectCommand = Fa.CreateCommand( ) DaB.SelectCommand.Command = "SELECT * FROM 本 " DaB.SelectCommand.Connection = Cn DaC = Fa.CreateDataAdapter( ) DaC.SelectCommand = Fa.CreateCommand( ) DaC.SelectCommand.Command = "SELECT * FROM カテゴリ " DaC.SelectCommand.Connection = Cn DaW = Fa.CreateDataAdapter( ) DaW.SelectCommand = Fa.CreateCommand( ) DaW.SelectCommand.Command = "SELECT * FROM 著者 " DaW.SelectCommand.Connection = Cn ' データセットの生成 Ds = New DataSet ' SQL の発行と取得したデータのデータセットへの格納 DaB.Fill( Ds, "BOOK" ) DaC.Fill( Ds, "CATEGORY" ) DaW.Fill( Ds, "WRITER" ) ' データセットよりフィールド名をリストボックスに追加 Call DispItemList( Tn ) End Class 定数 PROVIDER_NAME には データベースの種類に応じて 下記の名前空間を指定する SQL Server 7.0 以降の場合は "System.Data.SqlClient" Oracle 以降の場合は "System.Data.OracleClient" ODBC 接続の場合は "System.Data.Odbc" DbProviderFactories クラスの GetFactory メソッドの引数に 上記の定数 PROVIDER_NAME を指定して プロバイダファクトリ (DbProviderFactory) を生成する コネクション (DbConnection) は プロバイダファクトリ (DbProviderFactory) の CreateConnection メソッドを用いて生成し 亦 データアダプタ (DbDataAdapter) は CreateDataAdapter メソッドを用いて生成する 猶 SQL は プロバイダ固有の SQL を使用せずに 汎用的な SQL を用いる様に心掛けて置けば 異種データベースに移行する際 コードの変更を最小限に抑える事が出来る -6-

7 データベース操作の概要 ADO.NET で主要なコンポーネントは データセットとデータプロバイダ ( コネクションとデータアダプタ等の総称 ) で有る データセットは メモリ上に展開したデータベースで有り データプロバイダは 元のデータベースとデータセットとの橋渡しを行う データベースにアクセスして取得したデータをフォームに表示する時のイメージを 下図に示す 上記の図の内 特に頻繁に使用されるコンポーネントと 各コンポーネントの繋がりを 下図に示す 猶 下図のコンポーネントを理解すれば 基本的なデータベース操作は 粗自在に行う事が出来る 特に Windows アプリケーションを開発する時 データアクセスの中心に成るコンポーネントは データアダプタとデータセットで有る 此等は ウィザードを利用して簡単に作成する事が出来る データベースの接続に使用する接続文字列や データを取得したり更新したりする為の SQL ステートメントを手動で作成する必要は殆ど無い -7-

8 .NET データプロバイダ.NET データプロバイダは データベースへの接続や コマンドを実行してデータの取得や更新を行う為のコンポーネントの集合で コネクション データアダプタ コマンド データリーダから構成される.NET Framework に含まれる.NET Framework データプロバイダを次の表に示す データプロバイダ Data Provider for SQL Server Data Provider for OLE DB Data Provider for ODBC Data Provider for Oracle 説明 Microsoft SQL Server 7.0 以降を使用したデータソースに対応 System.Data.SqlClient 名前空間を使用する OLE DB を使用したデータソース (Access 等 ) に対応 System.Data.OleDb 名前空間を使用する ODBC を使用したデータソース (Access MySQL 等 ) に対応 System.Data.Odbc 名前空間を使用する Oracle 以降を使用したデータソースに対応 System.Data.OracleClient 名前空間を使用する コネクション 特定のデータベースへの接続を確立するコンポーネントで有る ADO.NET では Connection オブジェクトを使用して 接続文字列に必要な認証情報を指定する事に依り 特定のデータソースに接続する.NET Framework に含まれて居る各.NET Framework データプロバイダは Connection オブジェクトを持って居る 何の Connection オブジェクトを使用するかは データソースの種類に依り異なる データアダプタ データベースとデータセットを連結するコンポーネントで 下記の機能を有する データベースから取得したデータをデータセットに格納する データセットの内容でデータベースを更新する 亦 データアダプタは 上記の機能を果たす為に 下記の 4 個のコマンドを有する 選択コマンド : 必要なデータを抽出する (SelectCommand) 追加コマンド : レコードを追加する (InsertCommand) 削除コマンド : レコードを削除する (DeleteCommand) 更新コマンド : レコードを更新する (UpdateCommand) データアダプタは 上記のコマンドを実行する時 自動的にデータベースに接続し 処理を終了した後は 接続を解除する 其の為 データベースへの接続と解除を手動で行う必要は無い コマンド データベースに対して実行するコマンドを保持するコンポーネントで有る データリーダ コマンドを実行した結果 データベースから 読込専用 前方スクロール可能な形で読み込んだデータで有る 猶 Windows アプリケーションを開発する場合は 主にデータアダプタとコネクションを使用し コマンドとデータリーダは Web アプリケーションの開発に利用する -8-

9 データセット データセットは アプリケーションが使用するデータベースを メモリ上に展開した物で有る データベースと同じ様に データセットには 複数のテーブルを定義する事が出来る 亦 主キーの様なテーブル属性やリレーションシップを設定して テーブル間の関連付けを定義する事が出来る アプリケーションの実行時には 定義したテーブルにデータを保持する事が出来る データセットは 接続するデータソースから完全に独立して居る 其の為 下図の様に 種類の異なるデータベースから取得したデータを 同じデータセットに保持する事も可能で有る 亦 データセット内のデータは データベースの種類に係らず 同じ手法でアクセスする事が出来る Access データベース Oracle データベース SQL Server データベース データセット データセット内のデータは アプリケーション実行中 メモリに常駐する レコードの編集や 必要なデータの抽出等の作業は 総てデータセット上で行う 其の為 レコード操作を行うと データセットの内容と元のデータベースの内容は 異なる物に成る 此の両者の同期を取る為には データアダプタが保持して居るデータベース更新用のコマンド (Update メソッド ) を使用する 此のコマンドを実行すると 再びデータベースに接続して データを更新し 処理を終了した後は 接続を解除する 通常の多階層の実装で DataSet を作成 変更し 次に元のデータを更新する手順を 下記に示す 1.DataAdapter の Fill メソッドを使用して DataSet 内に DataTable を作成し 各テーブルにデータソースのデータを格納する 2.DataRow オブジェクトを追加 更新 又は 削除して 個別の DataTable オブジェクト内のデータを変更する 3.GetChanges メソッドを呼び出して データへの変更丈を格納する 2 個目の DataSet を作成する 4. 此の 2 個目の DataSet を引数と仕て渡して DataAdapter の Update メソッドを呼び出す 5.Merge を呼び出して 2 つ目の DataSet に格納された変更を最初のデータセットにマージする 6.DataSet で AcceptChanges を呼び出す 変更をキャンセルするには RejectChanges を呼び出す -9-

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 フィールドを関連付け 本. タイトル 本. 価格 著者. 著者名フィ ールドのデータを取得する -10-

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 文で文字列を記述する場合は シングルクオーテーション ( ' ) で囲む -11-