VB実用③　アクセス操作Ⅰ

プログラムの概要

此処では、コントロールを使用しないで、コード に依るデータベースへの接続とデータの取得の 方法を学ぶ。 コントロールに依るデータベース接続は、簡単に データベースを操作する事が出来ると謂う利点 が有り、小規模な運用で有れば、充分な能力を発 揮する。 併し、中規模、大規模に成り、複雑な操作を必要 とする様に成れば、力不足に成る可能性が高く成 る。然う成れば、矢張り、コードに依りデータベ ースを操作する事が必要と成る。 其の為、両者を、必要に応じて、使い分ける事が 出来る様に、孰れにも精通して置く事が、開発効 率を高め、良質のプログラムを作成する上で、必 要な事で有る。

VB でアクセス操作Ⅰ

VB 2005 ③ □ 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 文：レコードを検索する文）

■ オブジェクト･プロパティ一覧 ■ コントロールの種類 プロパティ プロパティの設定値 フォーム Name access Text 本棚データベース ラベル１ Name lblItem Text 列見出し ラベル２ Name lblField Text フィールドデータ リストボックス１ Name lstItem リストボックス２ Name lstField フレーム Name grpTable Text 表示テーブル ラジオボタン１ Name radBook Text 本 Checked True ラジオボタン２ Name radCate Text カテゴリ ラジオボタン３ Name radWriter Text 著者 データベースの用意 此処では、現時点で最も普及して居るデータベースで有るACCESS データベースを使用する事にす る。ACCESS データベース（MDB ファイル）で有れば、何でも良いが、此処では、テスト用に用 意したdata.mdb を使用する。猶、此のファイルを、デバッグ時には bin フォルダの Debug フォル ダに、亦、実行時にはexe ファイルと同じフォルダに、事前に格納して置く。 ラジオボタン１ フレーム リストボックス１ リストボックス２ ラベル１ ラベル２ ラジオボタン３ ラジオボタン２

■ プログラムリスト ■ ' 名前空間のインポート

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"

' フォームが読み込まれた時の処理

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 ) End Sub ' フォームが閉じられ様と仕た時の処理

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 End Sub 此処で宣言した変数は同じクラス 内の総てのサブプロシージャで値 の参照と設定を行う事が出来る。 クラスレベルでグローバルな変数 Tn には、対象と成るテーブル名を 初期値と仕て設定して居る。 記述を簡略化する為に、名前空間 をインポートして居る。此れに依 り、名前空間以下のクラスは、名 前空間を省略して記述出来る。 使用済みのオブジェクトは、必要 が無く成った時点で、必ず、速や かに閉じて、メモリから開放して 置く様に心掛ける。 Connection オブジェクトは、必要 が無く成った時点で、必ず、速や かに閉じる様に心掛ける。 Call ステートメントは、ジェネラ ルプロシージャ（メソッド）を呼 び出す制御構文で有る。省略する 事も出来るが、自作のメソッドに は、必ず付ける様に仕て居る。 New キーワードは、宣言ステート メントか代入ステートメントで使 用し、オブジェクトの新しいイン スタンスを生成する

' リストボックス（列見出し）の選択項目が変化した時の処理

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

End Sub

' ラジオボタンがクリックされた時の処理

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.Text

Case "本" Tn = "BOOK" Case "カテゴリ" Tn = "CATEGORY" Case "著者" Tn = "WRITER" End Select ' ジェネラルプロシージャの呼出 Call DispItemList( Tn ) End Sub ' 列見出しを表示するジェネラルプロシージャ

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 ) Next End Sub End Class 此処で宣言した変数は、宣言した サブプロシージャ内でしか値の参 照と設定を行う事が出来ない。 １つの式の値を複数の値と比較す る条件が多数有る場合は、Select Case 制御構造を使うと、効率的で 読み易いコードを記述出来る。 複数の箇所で同じコードを記述す る場合は、ジェネラルプロシージ ャ（メソッド）に纏める方が、保 守面でも優れて居る。 ToString メソッドは、データがヌ ルの場合、VB で取り扱う事の出 来るヌルストリングに変更する。 此処で宣言した変数は、宣言した ブロック内でしか値の参照と設定 を行う事が出来ない。 For～Next は、指定した回数、ブ ロック内の処理を繰り返すループ 処理と呼ばれる構文で有る。

上記のFor 文は、For Each 文を使用して、下記の様に記述する事も出来る。 For Each C As DataColumn In Ds.Tables(T).Columns

lstItem.Items.Add(C.ColumnName) Next

此の様に、コードの記述方法は、必ずしも一通りでは無い。各自の習熟度に合わせた記述法を使用 すれば良い。意図した通りの動きをすれば、先ずは正しいコードで有る。

■ プロバイダファクトリの利用 ■ 前記では、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 As DbConnection Private Ds 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( ) 固有プロバイダの種類と接続文字 列は、此の様に、定数で宣言して 置くと、データベースを変更して も、此の部分丈を書き直せば良い 事に成る。

' 接続のオープン Cn.ConnectionString = CONNECTION_STRING Cn.Open( ) ' データアダプタの生成 DaB = Fa.CreateDataAdapter( ) DaB.SelectCommand = Fa.CreateCommand( )

DaB.SelectCommand.CommandText = "SELECT * FROM 本" DaB.SelectCommand.Connection = Cn

DaC = Fa.CreateDataAdapter( )

DaC.SelectCommand = Fa.CreateCommand( )

DaC.SelectCommand.CommandText = "SELECT * FROM カテゴリ" DaC.SelectCommand.Connection = Cn

DaW = Fa.CreateDataAdapter( )

DaW.SelectCommand = Fa.CreateCommand( )

DaW.SelectCommand.CommandText = "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 Sub … End Class 定数PROVIDER_NAME には、データベースの種類に応じて、下記の名前空間を指定する。 SQL Server 7.0 以降の場合は、"System.Data.SqlClient" Oracle 8.1.7 以降の場合は、"System.Data.OracleClient" ODBC 接続の場合は、"System.Data.Odbc"

DbProviderFactories クラスの GetFactory メソッドの引数に、上記の定数 PROVIDER_NAME を指定 して、プロバイダファクトリ（DbProviderFactory）を生成する。 コネクション（DbConnection）は、プロバイダファクトリ（DbProviderFactory）の CreateConnection メソッドを用いて生成し、亦、データアダプタ（DbDataAdapter）は、CreateDataAdapter メソッド を用いて生成する。 猶、SQL は、プロバイダ固有の SQL を使用せずに、汎用的な SQL を用いる様に心掛けて置けば、異 種データベースに移行する際、コードの変更を最小限に抑える事が出来る。

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

■ .NET データプロバイダ

.NET データプロバイダは、データベースへの接続や、コマンドを実行してデータの取得や更新を行う 為のコンポーネントの集合で、コネクション、データアダプタ、コマンド、データリーダから構成され る。.NET Framework に含まれる.NET Framework データプロバイダを次の表に示す。

データプロバイダ 説明

Data Provider for SQL Server Microsoft SQL Server 7.0 以降を使用したデータソースに対応。 System.Data.SqlClient 名前空間を使用する。

Data Provider for OLE DB OLE DB を使用したデータソース（Access 等）に対応。 System.Data.OleDb 名前空間を使用する。

Data Provider for ODBC ODBC を使用したデータソース（Access、MySQL 等）に対応。 System.Data.Odbc 名前空間を使用する。

Data Provider for Oracle Oracle 8.1.7 以降を使用したデータソースに対応。 System.Data.OracleClient 名前空間を使用する。

コネクション

特定のデータベースへの接続を確立するコンポーネントで有る。ADO.NET では、Connection オブジ ェクトを使用して、接続文字列に必要な認証情報を指定する事に依り、特定のデータソースに接続す る。.NET Framework に含まれて居る各.NET Framework データプロバイダは、Connection オブジェ クトを持って居る。何のConnection オブジェクトを使用するかは、データソースの種類に依り異なる。 データアダプタ データベースとデータセットを連結するコンポーネントで、下記の機能を有する。 ・データベースから取得したデータをデータセットに格納する。 ・データセットの内容でデータベースを更新する。 亦、データアダプタは、上記の機能を果たす為に、下記の4 個のコマンドを有する。 ・選択コマンド：必要なデータを抽出する（SelectCommand）。 ・追加コマンド：レコードを追加する（InsertCommand）。 ・削除コマンド：レコードを削除する（DeleteCommand）。 ・更新コマンド：レコードを更新する（UpdateCommand）。 データアダプタは、上記のコマンドを実行する時、自動的にデータベースに接続し、処理を終了した後 は、接続を解除する。其の為、データベースへの接続と解除を手動で行う必要は無い。 コマンド データベースに対して実行するコマンドを保持するコンポーネントで有る。 データリーダ コマンドを実行した結果、データベースから、読込専用、前方スクロール可能な形で読み込んだデータ で有る。 猶、Windows アプリケーションを開発する場合は、主にデータアダプタとコネクションを使用し、コ マンドとデータリーダは、Web アプリケーションの開発に利用する。

■ データセット データセットは、アプリケーションが使用するデータベースを、メモリ上に展開した物で有る。データ ベースと同じ様に、データセットには、複数のテーブルを定義する事が出来る。亦、主キーの様なテー ブル属性やリレーションシップを設定して、テーブル間の関連付けを定義する事が出来る。アプリケー ションの実行時には、定義したテーブルにデータを保持する事が出来る。 データセットは、接続するデータソースから完全に独立して居る。其の為、下図の様に、種類の異なる データベースから取得したデータを、同じデータセットに保持する事も可能で有る。亦、データセット 内のデータは、データベースの種類に係らず、同じ手法でアクセスする事が出来る。 データセット内のデータは、アプリケーション実行中、メモリに常駐する。レコードの編集や、必要な データの抽出等の作業は、総てデータセット上で行う。其の為、レコード操作を行うと、データセット の内容と元のデータベースの内容は、異なる物に成る。此の両者の同期を取る為には、データアダプタ が保持して居るデータベース更新用のコマンド（Update メソッド）を使用する。此のコマンドを実行 すると、再びデータベースに接続して、データを更新し、処理を終了した後は、接続を解除する。 通常の多階層の実装でDataSet を作成・変更し、次に元のデータを更新する手順を、下記に示す。 １．DataAdapter の Fill メソッドを使用して、DataSet 内に DataTable を作成し、各テーブルにデー

タソースのデータを格納する。

２．DataRow オブジェクトを追加、更新、又は、削除して、個別の DataTable オブジェクト内のデー タを変更する。

３．GetChanges メソッドを呼び出して、データへの変更丈を格納する 2 個目の DataSet を作成する。 ４．此の2 個目の DataSet を引数と仕て渡して、DataAdapter の Update メソッドを呼び出す。 ５．Merge を呼び出して、2 つ目の DataSet に格納された変更を最初のデータセットにマージする。 ６．DataSet で AcceptChanges を呼び出す。変更をキャンセルするには、RejectChanges を呼び出す。

Access データベース Oracle データベース SQL Server データベース データセット

SQL（Structured Query Language：構造化照会言語）とは 直訳すると難しく成るが、プログラミング知識の無いオペレーターが簡単にデータを操作出来る様に編 み出された言語で有る。国際規格（ISO）にも定められて居る為、ORACLE でも SQLServer でも基本 は同じで有る。４つの構文を覚える事で、様々な、データーベースのデータを扱う事が可能に成る。 SQL の４大構文 下記の構文をSQL の４大構文と謂う。 SELECT 文 ：データを選択する文 DELETE 文 ：データを削除する文 INSERT 文 ：データを追加する文 UPDATE 文 ：データの内容を変更する文 SELECT 文の解説 データを探す構文 SELECTΔフィールド名ΔFROMΔテーブル名ΔWHEREΔ検索条件 例文１：SELECTΔ*ΔfromΔ本; 解 説：『＊』（アスタリスク）は、フィールド総てを表す。 本と謂う名前のテーブルに在る全件数のレコードを検索し、総てのフィールドのデータを取得する 例文２：SELECTΔタイトル,価格ΔfromΔ本; 解 説：フィールドを複数指定する場合は『タイトル,価格』の様に『，』（コンマ）で区切る。 本と謂う名前のテーブルに在る全件数のレコードを検索し、タイトル、価格フィールドのデータを取得する 例文３：SELECTΔ*ΔfromΔ本ΔWHEREΔ価格=2500; 解 説：『＊』（アスタリスク）は、フィールド総てを表す。 本と謂う名前のテーブルに在る価格フィールドが2500 のレコードを検索し、総てのフィールドのデータを取得する 例文４：SELECTΔ*ΔfromΔ本ΔWHEREΔタイトルΔLIKEΔ’%VB%’; 解 説：SQL 文内で文字列を指定する場合は『’』（シングルクオーテージョン）で区切る。 本と謂う名前のテーブルに在るタイトルフィールドに’VB’という文字列が含まれるレコードを検索し、総てのフィールド のデータを取得する

例文５：SELECTΔ本.タイトル,本.価格,著者.著者名ΔfromΔ本,著者ΔWHEREΔ本.著者 ID=著者 ID;

解 説：複数テーブルを関連付けた SELECT 文を記述する事が出来る。使用するテーブル はFrom 句の後ろに 2 つ目以降『，』(コンマ)で区切り、指定する。亦、フィールド 指定は必ず『テーブル名.フィールド名』とする。理由は、異なるテーブルで、同じ フィールド名を使用して居るケースも有るから。 本テーブルの著者ID フィールドと著者テーブルの ID フィールドを関連付け、本.タイトル、本.価格、著者.著者名フィ ールドのデータを取得する △は半角スペースを意味する（以下、総ての構文で同じ）。 ACCESS で SQL 文を使用する場合は、文の末尾にセミコロン（ ; ）を付ける。 機 能 書 式 解 説

DELETE 文の解説 データを削除する構文 DELETEΔFROMΔテーブル名ΔWHEREΔ条件 例文１：DELETEΔFROMΔ本ΔWHEREΔ価格=2500; 解 説：SELECT 文と似て居るが、DELETE 文の場合はフィールド名を指定し無い。 本と謂う名前のテーブルの価格が2500 と謂うデータレコードを削除する 例文２：DELETEΔFROMΔ本; 解 説：条件を指定し無いと、総てのデータレコードが削除される。 本と謂う名前のテーブルのデータレコードを総て削除する INSERT 文の解説 データを追加する構文 INSERTΔINTOΔテーブル名(フィールド名,フィールド名)ΔVALUES(追加する数値データ,’追加する文字列データ’) 例文１：INSERTΔINTOΔ本ΔVALUES(9,’VB ファン’,4000,’2000/5/5’,4,4); 解 説：例えば『VB ファン』の様に、文字列型のデータを追加する場合は、『’』（シングル クオーテーション）で区切る。亦、フィールドとフィールドを区切る文字は『，』（コ ンマ）を使用する。

本テーブルにID:9、タイトル:’VB ファン’、価格:4000、購入日:’2000/5/5’、カテゴリ ID:4、著者 ID:4 のレコードを追加

例文２：INSERTΔINTOΔ本(タイトル,価格,購入日,カテゴリ ID, 著者 ID)ΔVALUES(’VB ファン’,4000,’2000/5/5’,4,4); 解 説：特定のフィールドにデータを挿入し無い場合は、省略出来る。上記の例では、ID フィールドを省略して居る。実行した後、データを見ると、ID フィールドには『０』 が代入されて居る。猶、文字列型のフィールドの場合は空白と成る。此の状態を 『NULL』と謂う。亦、オートナンバー型フィールドの場合は自動で番号が割り振 られる。 本テーブルに、タイトル:’VB ファン’、価格:4000、購入日:’2000/5/5’、カテゴリ ID:4、著者 ID:4 のレコードを追加する UPDATE 文の解説 データ内容を変更する構文 UPDATE△テーブル名ΔSETΔフィールド名=変更する数値データ,フィールド名=’変更する文字列データ’ΔWHEREΔ変更するデータの条件 例文１：UPDATEΔ本ΔSETΔ価格=8000,購入日=’2000/6/5’ΔWHEREΔタイトル=’VB ファン’ ; 解 説：総てのデータを変更したい場合は WHERE は必要無いが、特定のレコードを変更 したい場合は指定する。 本テーブルのタイトルフィールドが’VB ファン’と謂うレコードの価格を 8000 に、購入日を’2000/6/5’に変更する SQL 文で文字列を記述する場合は、シングルクオーテーション（ ' ）で囲む。 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説