VB でアクセス操作 Ⅲ VB 2005 5 プログラムの概要 前回に引き続き 此処では コードに依るデータベースの操作方法を学ぶ コネクションオブジェクトを用いてデータベースと接続し 表形式でデータを表示するデータグリッドビューコントロールにレコードデータを自動的に表示する手法を学ぶ 既に学んだ様にコントロールを連結する事に依り コードレスでデータベース操作が可能で有るが 此処では 着目して居る行 ( カレントレコード ) の指定と 個々の列データ ( フィールドデータ ) の取得に付いて学ぶ 亦 高度なデータベース操作の下準備と仕て ADO.NET オブジェクトをコードで操作する事とデータベース操作の基礎と成る SQL に慣れ親しむ事を目的とする 今回の課題項目 ADO.NET に依るデータベース接続 ( コネクションオブジェクト ) ADO.NET に依るデータベース接続 (ConnectionString プロパティ Open メソッド ) ADO.NET に依るデータベース操作 ( データアダプタオブジェクト ) ADO.NET に依るデータベース操作 (SelectCommand CommandText プロパティ ) ADO.NET に依るデータベース操作 (InsertCommand CommandText プロパティ ) ADO.NET に依るデータベース操作 (UpdateCommand CommandText プロパティ ) ADO.NET に依るデータベース操作 (DeleteCommand CommandText プロパティ ) ADO.NET に依るデータベース操作 (Fill メソッド Update メソッド ) ADO.NET に依るデータセット操作 ( データセットオブジェクト ) ADO.NET に依るテーブルデータ操作 (Tables コレクション Clear メソッド ) ADO.NET に依るレコードデータ操作 (Rows コレクション Count プロパティ ) ADO.NET に依るフィールドデータ操作 (Item コレクション ToString メソッド ) 今回の重点項目 ADO.NET に依るデータベース接続 ( コネクションオブジェクト ) ADO.NET に依るデータベース操作 ( データアダプタオブジェクト ) ADO.NET に依るデータセット操作 ( データセットオブジェクト ) SQL 文 (SELECT 文 : レコードを検索 追加 変更 削除する文 ) -1-
オブジェクト プロパティ一覧 コマンドボタン 1 コマンドボタン 2 コマンドボタン 3 ラジオボタン 1 ラジオボタン 2 コマンドボタン 4 コントロールの種類 プロパティ プロパティの設定値 コマンドボタン1 Name btninsert Text 追加 コマンドボタン2 Name btnupdate Text 変更 コマンドボタン3 Name btndelete Text 削除 コマンドボタン4 Name btnupdatedb Text 更新 ラジオボタン1 Name radimmediately Text 直ちにデータベースを更新 ラジオボタン2 Name radcollectively Text 一括してデータベースを更新 上記以外のコントロールに付いては 前回 VB でアクセス操作 Ⅱ と同じで有る 既存のフォームを再利用するには 下記の方法が有る 1. プロジェクトを保存したトップフォルダ毎コピーする方法此の場合 フォルダ名を変更する ( トップフォルダの名前を変更しても問題は発生しない ) 2 フォーム上の総てのコントロールを選択して新しいフォームにコピーする方法此の場合 フォームのプロパティは 設定し直す必要が有る 3. 既存のフォームを新しいプロジェクトに追加する方法此の場合 フォーム関係のファイルを新しいプロジェクトにコピーして置く事が望ましい 上記の孰れかを利用して 前回のフォームを今回のプロジェクトに使用すると 開発効率を向上させる事が出来る -2-
プログラムリスト ' 名前空間のインポート Imports System.Data Imports System.Data.OleDb Public Class access ' 同一クラス内でグローバルな変数の宣言 Private Cn As OleDbConnection Private Ds As DataSet 記述を簡略化する為に 名前空間をインポートして居る 此れに依り 名前空間以下のクラスは 名前空間を省略して記述出来る Private DaB Private DaC Private DaW Private DaT Private Tn As OleDbDataAdapter As OleDbDataAdapter As OleDbDataAdapter As OleDbDataAdapter As String = "BOOK" 此処で宣言した変数は 同じクラス内の総てのプロシージャ ( メソッド ) で値の参照と設定を行う事が出来る ' フォームが読み込まれた時の処理 Private Sub access_load( ByVal sender As System.Object, ByVal e As System.EventArgs ) _ Handles MyBase.Load 此処で宣言した変数は宣言したサ Dim SQL As String = "" ブプロシージャ内でしか値の参照と設定を行う事が出来ない ' データベースへの接続情報の設定 Cn = New OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb" ) ' 本テーブル用のデータアダプタの生成とクエリの設定データアダプタのコマンドを生成 DaB = New OleDbDataAdapter( ) する自作関数を呼び出して居る SQL = "SELECT * FROM 本 " DaB.SelectCommand = CreateDataAdapterCommand( SQL, "S" ) SQL = "INSERT INTO 本 ( タイトル, 価格, 購入日, カテゴリ ID, 著者 ID) VALUES(?,?,?,?,?)" DaB.InsertCommand = CreateDataAdapterCommand( SQL, "I" ) SQL = "UPDATE 本 SET タイトル =?, 価格 =?, 購入日 =?, カテゴリ ID=?, 著者 ID=? WHERE ID=?" DaB.UpdateCommand = CreateDataAdapterCommand( SQL, "U" ) SQL = "DELETE FROM 本 WHERE ID=?" DaB.DeleteCommand = CreateDataAdapterCommand(SQL, "D") ' 本テーブル以外のデータアダプタの生成と選択クエリの設定 DaC = New OleDbDataAdapter( "SELECT * FROM カテゴリ ORDER BY ID", Cn ) DaW = New OleDbDataAdapter( "SELECT * FROM 著者 ORDER BY ID", Cn ) DaT = New OleDbDataAdapter( "SELECT * FROM カテゴリ WHERE ID=0", Cn ) ' データセットの生成 Ds = New DataSet ' SQL の発行と取得したデータのデータセットへの格納 DaB.Fill( Ds, "BOOK" ) DaC.Fill( Ds, "CATEGORY" ) DaW.Fill( Ds, "WRITER" ) DaT.Fill( Ds, "TEMP" ) ' データセット内のデータソースをデータグリッドビューに連結 dgvbook.datasource = Ds dgvbook.datamember = "BOOK" ' コンボボックス ( カテゴリ 著者 ) の設定 Call CboAddItem() 各テーブルから総てのフィールドのデータを抽出する SQL 文を作成する ORDER BY 節は抽出データの並び順を WHERE 節は 抽出条件を設定する イベントプロシージャに其処で処理される総てのコードを記述するのでは無く 処理ブロック毎にサブルーチン化する事は 開発効率や保守性を高める効果が有る -3-
' フォームが閉じられ様と仕た時の処理 Private Sub access_formclosing( ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.FormClosingEventArgs ) Handles MyBase.FormClosing ' 各インスタンスの破棄 Ds.Dispose( ) : DaB.Dispose( ) : DaC.Dispose( ) : DaW.Dispose( ) : DaT.Dispose( ) Cn.Close( ) : Cn.Dispose( ) End ' データグリッドビューの行ヘッダがクリックされた時の処理 Private Sub dgvbook_rowheadermouseclick( ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs ) _ Handles dgvbook.rowheadermouseclick Dim S As String = "" ' 着目行の各フィールド値をテキストボックスに表示フィールドデータが ヌルやオブ Dim N As Integer = dgvbook.currentrow.index ジェクトの場合が有る為 VB で取り扱う事の出来る文字列に変更 If N < Ds.Tables( "BOOK" ).Rows.Count Then する為に ToString を使用する ' データセットから取得 txtid.text = Ds.Tables( "BOOK" ).Rows( N ).Item( "ID" ).ToString txttitle.text = Ds.Tables( "BOOK" ).Rows( N ).Item( " タイトル " ).ToString txtprice.text = Ds.Tables( "BOOK" ).Rows( N ).Item( " 価格 " ).ToString dtpdate.value = Ds.Tables( "BOOK" ).Rows( N ).Item( " 購入日 " ).ToString txtcate.text = Ds.Tables( "BOOK" ).Rows( N ).Item( " カテゴリ ID" ).ToString txtwriter.text = Ds.Tables( "BOOK" ).Rows( N ).Item( " 著者 ID" ).ToString ' コンボボックス ( カテゴリ ) の設定 Ds.Tables( "TEMP" ).Clear( ) S = Ds.Tables( "BOOK" ).Rows( N ).Item( " カテゴリ ID" ).ToString DaT.SelectCommand.CommandText = "SELECT カテゴリ名 FROM カテゴリ WHERE ID=" & S DaT.Fill( Ds, "TEMP" ) S = Ds.Tables( "TEMP" ).Rows( 0 ).Item( " カテゴリ名 " ).ToString For I As Integer = 0 To ( cbocate.items.count - 1 ) If cbocate.items.item( I ) = S Then cbocate.selectedindex = I Exit For Next ' コンボボックス ( 著者 ) の設定 Ds.Tables( "TEMP" ).Clear( ) S = Ds.Tables( "BOOK" ).Rows( N ).Item( " 著者 ID" ).ToString DaT.SelectCommand.CommandText = "SELECT 著者名 FROM 著者 WHERE ID=" & S DaT.Fill( Ds, "TEMP" ) S = Ds.Tables( "TEMP" ).Rows( 0 ).Item( " 著者名 " ).ToString For I As Integer = 0 To ( cbowriter.items.count - 1 ) If cbowriter.items.item( I ) = S Then cbowriter.selectedindex = I Exit For Next Else txtid.text = "": txttitle.text = "" txtprice.text = "": dtpdate.text = "" txtcate.text = "": txtwriter.text = "" -4- Close メソッドは 関連するシステムリソースを開放する丈で メモリから削除するには Dispose する必要が有る 此処で宣言した変数は宣言したサブプロシージャ内でしか値の参照と設定を行う事が出来ない For 文等のブロック内で宣言した変数は 宣言したブロック内でしか値の参照と設定を行う事が出来ない ( 変数 I が相当 ) 記号アンパサント (&) は 文字列を結合する演算子で有る コンボボックスの項目をコードで選択するには SelectedIndex に選択する項目のインデックスを設定する テキストボックス内に記述されたテキストを消去するには ヌルストリング ( 二重引用符の中に何も記述しない文字列 ) を設定する
' コンボボックス ( カテゴリ ) の選択項目が変化した時の処理 Private Sub cbocate_selectedindexchanged( ByVal sender As System.Object, _ ByVal e As System.EventArgs ) _ Handles cbocate.selectedindexchanged For Each D As DataRow In Ds.Tables( "CATEGORY" ).Rows If D.Item(" カテゴリ名 ").ToString = cbocate.items.item( cbocate.selectedindex ) Then txtcate.text = D.Item( "ID" ).ToString Exit For Next ' コンボボックス ( 著者 ) の選択項目が変化した時の処理 Private Sub cbowriter_selectedindexchanged( ByVal sender As System.Object, _ ByVal e As System.EventArgs ) _ Handles cbowriter.selectedindexchanged For Each D As DataRow In Ds.Tables( "WRITER" ).Rows If D.Item(" 著者名 ").ToString = cbowriter.items.item( cbowriter.selectedindex ) Then txtwriter.text = D.Item( "ID" ).ToString Exit For Next ' ラジオボタン ( 両方 ) がクリックされた時の処理 Private Sub RadioButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles radimmediately.click, radcollectively.click ハンドルを追加して居る Dim R As RadioButton = DirectCast( sender, RadioButton ) For Each ~ Next は コレクションから総ての要素を 1 個宛取り出すループ構文で有る 此処では データセット内の CATEGORY テーブルから各レコード ( 行 ) を取り出して居る For Each ~ Next は コレクションから総ての要素を 1 個宛取り出すループ構文で有る 此処では データセット内の WRITER テーブルから各レコード ( 行 ) を取り出して居る If R.Name = "radimmediately" Then btnupdatedb.enabled = False Else btnupdatedb.enabled = True 複数のコントロールで同じイベントプロシージャを共有する時 イベント発生源のコントロールを特定するには sender 引数を使用する ' ボタン ( 追加 ) がクリックされた時の処理 Private Sub btninsert_click(byval sender As System.Object, ByVal e As System.EventArgs) _ Handles btninsert.click Dim D As DataRow = Ds.Tables( "BOOK" ).NewRow 新規にレコード ( 行 ) を生成して DataRow 型の変数に割り当てる D.Item( " タイトル " ) = txttitle.text D.Item( " 価格 " ) = txtprice.text D.Item( " 購入日 " ) = dtpdate.value D.Item( " カテゴリ ID" ) = txtcate.text D.Item( " 著者 ID" ) = txtwriter.text Ds.Tables( "BOOK" ).Rows.Add( D ) Call UpdateDatabase( ) DataRow の各フィールド ( 列 ) 名は 特に変更しない限り 元と成るデータベースのフィールド名と同じで有る 新規に作成した DataRow を データセット内の所定のテーブルの Rows コレクションに追加する -5-
' ボタン ( 変更 ) がクリックされた時の処理 Private Sub btnupdate_click( ByVal sender As System.Object, _ ByVal e As System.EventArgs ) _ Handles btnupdate.click Dim N As Integer = GetRowIndex( txtid.text ) ID より Rows コレクション内での位置 ( インデックス ) を取得する自作関数を呼び出し 其の戻り値を変数に代入する If Not N < 0 Then Dim D As DataRow = Ds.Tables( "BOOK" ).Rows( N ) D.BeginEdit( ) D.Item( " タイトル " ) = txttitle.text D.Item( " 価格 " ) = txtprice.text D.Item( " 購入日 " ) = dtpdate.value D.Item( " カテゴリ ID" ) = txtcate.text D.Item( " 著者 ID" ) = txtwriter.text D.EndEdit( ) Call UpdateDatabase( ) Else MsgBox( " 更新出来ません!", MsgBoxStyle.Critical, " 警告 " ) ID の一致するレコード ( 行 ) を取得して DataRow 型の変数に割り当てる 既存のレコードを編集する前には BeginEdit メソッドを呼び出す 既存のレコードを編集した後には EndEdit メソッドを呼び出す ID と一致するレコード ( 行 ) が無い場合の処理 ' ボタン ( 削除 ) がクリックされた時の処理 Private Sub btndelete_click( ByVal sender As System.Object, _ ByVal e As System.EventArgs ) _ Handles btndelete.click IDより Rows コレクション内での位置 ( インデックス ) を取得する自作関数を呼び出し 其の戻り値 Dim N As Integer = GetRowIndex( txtid.text ) を変数に代入する If Not N < 0 Then Dim D As DataRow = Ds.Tables( "BOOK" ).Rows( N ) D.Delete( ) Call UpdateDatabase( ) Else MsgBox( " 削除出来ません!", MsgBoxStyle.Critical, " 警告 " ) ID の一致するレコード ( 行 ) を取得して DataRow 型の変数に割り当てる レコード ( 行 ) を削除するには Delete メソッドを呼び出す ID と一致するレコード ( 行 ) が無い場合の処理 ' ボタン ( 更新 ) がクリックされた時の処理 Private Sub btnupdatedb_click( ByVal sender As System.Object, _ ByVal e As System.EventArgs ) _ Handles btnupdatedb.click DaB.Update( Ds, "BOOK" ) データセット内の変更をデータベースに反映させる ( 書き戻す ) には データアダプタの Update メソッドを呼び出す ' DataRow 内で ID の一致する行のインデックスを取得するジェネラルプロシージャ Private Function GetRowIndex( ByVal S As String ) As Integer For I As Integer = 0 To ( Ds.Tables( "BOOK" ).Rows.Count - 1 ) If Ds.Tables( "BOOK" ).Rows( I ).Item( "ID" ).ToString = S Then Return I Next Return -1 End Function -6- Return は 呼出元へ制御を戻すステートメントで有る 猶 引数で指定した値は 戻り値と成る
' データベースを更新するジェネラルプロシージャ Private Sub UpdateDatabase( ) If radimmediately.checked Then ' Cn.Open( ) DaB.Update( Ds, "BOOK" ) Ds.Tables( "BOOK" ).Clear( ) DaB.Fill( Ds, "BOOK" ) ' Cn.Close( ) ラジオボタンの状況に依りデータベースを更新するジェネラルプロシージャで 同じコードを複数の場所に記述する無駄を無くす丈でなく 保守性に優れる データベースを更新した後 データベースより再読込して居る 此れは レコードの追加時に オートナンバー型の ID を データベースより取得する為で有る ' コンボボックスにアイテムを追加するジェネラルプロシージャ Count は 個数を表すプロパティ Private Sub CboAddItem( ) で有り 1 スタートで有る ' カテゴリの設定 cbocate.items.clear( ) For I As Integer = 0 To ( Ds.Tables( "CATEGORY" ).Rows.Count - 1 ) cbocate.items.add( Ds.Tables( "CATEGORY" ).Rows( I ).Item( " カテゴリ名 " ).ToString ) Next ' 著者の設定 cbowriter.items.clear( ) For I As Integer = 0 To ( Ds.Tables( "WRITER" ).Rows.Count - 1 ) cbowriter.items.add( Ds.Tables( "WRITER" ).Rows( I ).Item( " 著者名 " ).ToString ) Next ' データアダプタのコマンドを設定するジェネラルプロシージャ Private Function CreateDataAdapterCommand(ByVal SQL As String, ByVal S As String ) _ As OleDbCommand Dim Cm As New OleDbCommand( SQL, Cn ) Dim Pc As OleDbParameterCollection = Cm.Parameters Rows の引数は インデックスを表すプロパティで有り 0 スタートで有る ' パラメータの設定 ' パラメータは SQL の? の順番に合わせる必要が有る If S = "I" Or S = "U" Then Pc.Add( " タイトル ", OleDbType.VarWChar, 50, " タイトル " ) Pc.Add( " 価格 ", OleDbType.Integer, 4, " 価格 " ) Pc.Add( " 購入日 ", OleDbType.DBTimeStamp, 8, " 購入日 " ) Pc.Add( " カテゴリ ID", OleDbType.Integer, 4, " カテゴリ ID" ) Pc.Add( " 著者 ID", OleDbType.Integer, 4, " 著者 ID" ) If S = "U" Or S = "D" Then Pc.Add( "ID", OleDbType.Integer, 4, "ID" ) Return Cm End Function End Class パラメータは SQL 文の中に記述された? の順番通りに追加して行かなければ成らない SQL 文に記述されたフィールド名に依り自動的に判別される訳ではない! 此の関数 ( ファンクションプロシージャ ) では OleDbCommand オブジェクトを戻り値と仕て返す 此の様に スカラー値丈でなく オブジェクトを戻り値とする事も出来る -7-
OleDbDataAdapter クラス ( 再掲 ) データソースとデータセットの間を仲介するクラス データセットへのデータの格納とデータソースの更新に使用される一連のデータコマンドとデータベース接続を表す DataAdapter は DataSet とデータソースとの間でデータの取得と保存を行う為の 仲介の役割を果たす DataAdapter は 其の為に DataSet 内のデータをデータソース内のデータと一致する様に変更する Fill メソッドと データソース内のデータを DataSet 内のデータと一致する様に変更する Update メソッドが用意されて居る OLE DB をサポートするデータソース (Access 等 ) に接続する場合は 関連付けられた OleDbCommand オブジェクトや OleDbConnection オブジェクトと共に OleDbDataAdapter を使用する事に依り 全体的なパフォーマンスを向上させる事が出来る データベースの種類 OLE DB データソース SQL Server データベース ODBC データソース Oracle データベース 最適化されたクラス OleDbCommand OleDbConnection OleDbDataAdapter SqlCommand SqlConnection SqlDataAdapter OdbcCommand OdbcConnection OdbcDataAdapter OracleCommand OracleConnection OracleDataAdapter DataAdapter のインスタンスを作成すると 読み書き可能プロパティが初期値に設定される 此等の初期値の一覧に付いては DataAdapter コンストラクタの記述を参照され度い OleDbDataAdapter クラスのコンストラクタ OleDbDataAdapter オブジェクトを生成するメソッド Dim 変数 As New OleDbDataAdapter( ) OleDbDataAdapter クラスの新しいインスタンスを初期化する (4 種のオーバーロードが有る ) 引数を指定しないコンストラクタでは 選択クエリ (SelectCommand プロパティ ) やアクションクエリ (Insert Command プロパティ Delete Command プロパティ Update Command プロパティ ) は設定されない 亦 接続も確立されない 此のコンストラクタは データアダプタを手動で設定する時に使用する データソースからデータを取得する場合は SelectCommand オブジェクトを 亦 データソースを更新する場合は InsertCommand オブジェクト DeleteCommand オブジェクト Updateommand オブジェクトの各オブジェクトも設定する必要が有る 此等は OleDbCommand オブジェクトを使用して設定する事が出来る 猶 OleDbDataAdapter のインスタンスを作成すると 下記の様に読み取り / 書き込みプロパティが初期値に設定される プロパティ MissingMappingAction MissingSchemaAction 初期値 MissingMappingAction.Passthrough MissingSchemaAction.Add -8-
OleDbDataAdapter クラスの Update メソッド DataSet からデータをデータソースに書き戻すメソッド Object.Update( データセット名, テーブル名 ) DataSet 名と DataTable 名を使用して DataSet 内で挿入 更新 削除の孰れかの操作が行われた行に対して INSERT UPDATE DELETE ステートメントを個別に呼び出す (7 種のオーバーロードが有る ) 引数のデータセット名には データソースの更新に使用する DataSet を指定する 亦 引数のテーブル名には テーブルマップに使用するソーステーブルの名前を指定する 亦 戻り値には DataSet で正常に追加や更新された行数が返される 此れには 行を返さないステートメントの影響を受ける行は含まれない コマンドが行を返さない場合は DataSet にテーブルは追加されない ( 此の場合 例外は発生しない ) アプリケーションが Update メソッドを呼び出すと DbDataAdapter は RowState プロパティを調べ DataSet に設定されて居るインデックスの順序に基づいて 要求された INSERT UPDATE DELETE ステートメントを各行に対して反復的に実行する 例えば Update が呼び出された時に DataTable 内の行の順序に応じて DELETE ステートメントを実行し INSERT ステートメントを実行してから 別の DELETE ステートメントを実行する事等が有る 此等のステートメントは バッチ処理と仕て実行されるのではない点に注意を要する 各行は 個別に更新される UPDATE の前に INSERT を実行する等 ステートメントの種類のシーケンスを制御する必要が有る場合は アプリケーションから GetChanges メソッドを呼び出す事が出来る INSERT UPDATE DELETE ステートメントが指定されて居ない場合 Update メソッドでは 例外が発生する 但し.NET Framework データプロバイダの SelectCommand プロパティを設定した場合は 単一テーブルを更新する SQL ステートメントを自動的に生成する SqlCommandBuilder や OleDbCommandBuilder オブジェクトを作成する事が出来る 此れに依り 追加の SQL ステートメントが CommandBuilder に依って自動的に生成される 此の生成ロジックでは DataSet 内にキー列情報が存在して居る必要が有る Update メソッドは 大文字と小文字の違いを除いて名前が一致する DataTable オブジェクトが DataSet 内に複数含まれる場合に 其等を区別する 此の様なテーブルが DataSet 内に複数存在する場合 Update は 大文字と小文字を区別して比較を実行し 対応するテーブルを見付ける 正確に一致するテーブルが存在しない場合は 例外が発生する 此の動作を表すコード例を 下記に示す Dim Ds As DataSet = new DataSet( ) Ds.Tables.Add("aaa") Ds.Tables.Add("AAA") Adapter.Update( Ds, "aaa" ) ' 既に DataSet 内に存在する "aaa" を更新する Adapter.Update( Ds, "Aaa" ) ' 例外が発生する 猶 Update を呼び出した時に 大文字と小文字の違いを除いて名前が一致する DataTable が DataSet 内に 1 個丈しか含まれて居ない場合は 其の DataTable が更新される 此の場合は 比較の時に大文字と小文字が区別されない 此処から -9-
OleDbDataAdapter クラスの SelectCommand プロパティ ( 再掲 ) 選択クエリの取得と設定を行うプロパティ Object.SelectCommand = OleDbCommand データソース内のレコードを選択する為の SQL ステートメント 又は ストアドプロシージャの取得と設定を行う 設定値の OleDbCommand には Fill 処理中に DataSet に格納するレコードをデータソースから選択する為の OleDbCommand オブジェクトを指定する 作成済みの OleDbCommand に SelectCommand が割り当てられた場合 OleDbCommand のクローンは作成されない SelectCommand に依り 作成済みの OleDbCommand オブジェクトへの参照が維持される OleDbDataAdapter クラスの InsertCommand プロパティ 挿入クエリの取得と設定を行うプロパティ Object.InsertCommand = OleDbCommand データソースに新しいレコードを挿入する為の SQL ステートメント 又は ストアドプロシージャの取得と設定を行う 設定値の OleDbCommand には Update 処理中に DataSet 内の新しい行に対応するデータソース内のレコードを挿入する為の OleDbCommand オブジェクトを指定する 作成済みの OleDbCommand に InsertCommand が割り当てられた場合 OleDbCommand のクローンは作成されない InsertCommand に依り 作成済みの OleDbCommand オブジェクトへの参照が維持される OleDbDataAdapter クラスの UpdateCommand プロパティ 更新クエリの取得と設定を行うプロパティ Object.UpdateCommand = OleDbCommand データソース内のレコードを更新する為の SQL ステートメント 又は ストアドプロシージャの取得と設定を行う 設定値の OleDbCommand には Update 処理中に DataSet 内の変更行に対応するデータソース内のレコードを更新する為の OleDbCommand オブジェクトを指定する 作成済みの OleDbCommand に UpdateCommand が割り当てられた場合 OleDbCommand のクローンは作成されない UpdateCommand に依り 作成済みの OleDbCommand オブジェクトへの参照が維持される -10-
OleDbDataAdapter クラスの DeleteCommand プロパティ 削除クエリの取得と設定を行うプロパティ Object.DeleteCommand = OleDbCommand データセットからレコードを削除する為の SQL ステートメント 又は ストアドプロシージャの取得と設定を行う 設定値の OleDbCommand には Update 処理中に DataSet 内の削除行に対応するデータソース内のレコードを削除する為の OleDbCommand オブジェクトを指定する 作成済みの OleDbCommand に DeleteCommand が割り当てられた場合 OleDbCommand のクローンは作成されない DeleteCommand に依り 作成済みの OleDbCommand オブジェクトへの参照が維持される 各 Command プロパティの共通事項 Update 処理中に 此等のプロパティが設定されて居らず DataSet に主キー情報が存在する場合 SelectCommand プロパティを設定し OleDbCommandBuilder を使用して居る時は InsertCommand UpdateCommand DeleteCommand が自動的に生成される 続いて 設定して居ない追加のコマンドが OleDbCommandBuilder に依り生成される 此の生成ロジックでは DataSet 内にキー列情報が存在して居る必要が有る OleDbCommand クラスの CommandText プロパティ ( 再掲 抜粋 ) クエリの取得と設定を行うプロパティ Object.CommandText = クエリ データソースで実行する SQL ステートメントやストアドプロシージャの取得と設定を行う 引数のクエリには 実行する SQL ステートメント 又は ストアドプロシージャを指定する CommandType プロパティの値に依り CommandType プロパティに設定する文字列の種類を 下記に示す CommandType プロパティ StoredProcedure TableDirect Text 設定文字列ストアドプロシージャ名テーブル名 SQL ステートメント OLE DB.NET プロバイダでは OleDbCommand が呼び出す SQL ステートメントやストアドプロシージャにパラメータを渡す為の名前付きパラメータは利用出来ない 此の場合は 疑問符プレースホルダを使用する 次に例を示す SELECT * FROM 本 WHERE ID =? 疑問符プレースホルダを使用する場合 パラメータの疑問符プレースホルダの位置と OleDbParameter オブジェクトを OleDbParameterCollection に追加する順序とを 正しく対応させる必要が有る 現在の接続で実行 又は フェッチ操作が実行中の場合は CommandText プロパティは設定出来ない -11-
データアダプタをコードで設定する方法 データアダプタをコードで設定する手順は 下記の通りで有る 1. 有効な接続を確立する (OleDbConnection) 2. データアダプタのインスタンスを生成する (OleDbDataAdapter) 3. 有効な接続と SQL 文を使用してコマンドのインスタンスを生成する (OleDbCommand) 4. パラメータコレクションのインスタンスを生成して コマンドオブジェクトの Parameters プロパティを設定する (OleDbParameterCollection) 5. パラメータコレクションに SQL 文の疑問符プレースフォルダの順番に従い パラメータを追加する (OleDbParameterCollection の Add メソッド ) 6. データアダプタのコマンドプロパティにコマンドオブジェクトを設定する 手動でデータアダプタの UpdateCommand プロパティを設定するコード例を 下記に示す ' クエリと接続文字列の設定 Dim SQL, CNS As String SQL = "UPDATE 本 SET タイトル =?, 価格 =?, 購入日 =?, カテゴリ ID=?, 著者 ID=? WHERE ID=?" CNS = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb" ' 各オブジェクトのインスタンス生成 Dim Cn As OleDbConnection = New OleDbConnection(CNS) Dim Da As OleDbDataAdapter = New OleDbDataAdapter( ) Dim Cm As New OleDbCommand(SQL, Cn) Dim Pc As OleDbParameterCollection = Cm.Parameters ' パラメータの設定 ( パラメータは SQL の? の順番に合わせる必要が有る ) Pc.Add(" タイトル ", OleDbType.VarWChar, 50, " タイトル ") Pc.Add(" 価格 ", OleDbType.Integer, 4, " 価格 ") Pc.Add(" 購入日 ", OleDbType.DBTimeStamp, 8, " 購入日 ") Pc.Add(" カテゴリ ID", OleDbType.Integer, 4, " カテゴリ ID") Pc.Add(" 著者 ID", OleDbType.Integer, 4, " 著者 ID") Pc.Add("ID", OleDbType.Integer, 4, "ID") ' データアダプタの UpdateCommand プロパティ設定 Da.UpdateCommand = Cm ' 1 ' 2 ' 3 ' 4 ' 5 ' 5 ' 5 ' 5 ' 5 ' 5 ' 6 疑問符プレースホルダを使用する場合 パラメータの疑問符プレースホルダの位置と OleDbParameter オブジェクト (Add メソッドの引数に パラメータの名前 データ型 列の長さ 及び ソースの列名を指定して生成 ) を OleDbParameterCollection に追加する順序とを 正しく対応させる必要が有る 即ち SQL 文に含まれる疑問符プレースフォルダに対応するフィールド要素 (OleDbParameter オブジェクト ) を 左から順に パラメータコレクション (OleDbParameterCollection オブジェクト ) に追加 (Add メソッド ) しなければ成らない 順番が正しく対応して居ないと 違うフィールドに値が設定されたり データ型が違うと謂う例外が発生したりする SQL Server 7.0 以降で使用する SqlCommand では 疑問符プレースホルダはサポートされて居らず 名前付きパラメータ ( 例 :WHERE ID = @ID) を使用する為 追加する順番は問題では無い -12-
OleDbParameterCollection クラスの Add メソッド パラメータコレクションにパラメータを追加するメソッド Object.Add( パラメータ名, データ型, 列長, ソース列名 ) パラメータの名前 データ型 列の長さ 及び ソースの列名を指定して OleDbParameter を OleDbParameterCollection に追加する (6 種のオーバーロードが有る ) 引数のパラメータ名には パラメータの名前を String 型で指定する 引数のデータ型には 当該フィールドのデータ型を OleDbType 列挙体の値で指定する 引数の列長には 当該フィールドの列の長さを Integer 型で指定する 引数のソース列名には 当該フィールドのソース列の名前を String 型で指定する 各パラメータコレクションで指定するデータ型の一覧を 下記に示す.NET Framework System.Data.DbType SqlDbType OleDbType OdbcType OracleType bool Boolean Bit Boolean Bit Byte byte Byte TinyInt UnsignedTinyInt TinyInt Byte byte[] Binary VarBinary. 2 VarBinary Binary Raw char 1 Char Char Byte DateTime DateTime DateTime DBTimeStamp DateTime DateTime Decimal Decimal Decimal Decimal Numeric Number double Double Float Double Double Double float Single Real Single Real Float Guid Guid UniqueIdentifier Guid UniqueIdentifier Raw Int16 Int16 SmallInt SmallInt SmallInt Int16 Int32 Int32 Int Int Int Int32 Int64 Int64 BitInt BigInt BigInt Number object Object Variant Variant 1 Blob string String NVarChar 3 VarWChar NVarChar NVarChar TimeSpan Time 1 DBTime Time DateTime UInt16 UInt16 1 UnsignedSmallInt Int UInt16 UInt32 UInt32 1 UnsignedInt BigInt UInt32 UInt64 UInt64 1 UnsignedBigInt Numeric Number AnsiString VarChar VarChar VarChar VarChar AnsiStringFixedLength Char Char Char Char Currency Money Currency 1 Number Date 1 DBDate Date DateTime SByte 1 TinyInt 1 SByte StringFixedLength NChar WChar NChar NChar Time 1 DBTime Time DateTime VarNumeric 1 VarNumeric 1 Number 1 サポートされて居ない 2 バイト配列が VarBinary の最大サイズ (8000 バイト ) より大きい場合は 此の暗黙の変換はエラーに成る 8000 バイトを超えるバイト配列の場合は 明示的に SqlDbType を設定する 3 文字列が NVarChar の最大サイズ (4000 文字 ) より大きい場合 此の暗黙の変換はエラーに成る 4000 文字を超える文字列の場合は 明示的に SqlDbType を設定する -13-
DataSet オブジェクト 2 レコードの追加 レコードを追加するには DataRow オブジェクトを作成し 其れを DataTable オブジェクトの Rows コレクションに追加する 例 : レコードを追加する Dim Dr As DataRow = Ds.Tables("BOOK").NewRow Dr.Item(" タイトル ") = " 速習 Visual Basic 2005" Dr.Item(" 価格 ") = 3500 Ds.Tables("BOOK").Rows.Add(Dr) レコードを追加しても 追加したレコードが直ちにデータベースに反映される訳では無く Update メソッドを呼び出した時に DataSet オブジェクトよりデータベースに書き戻される レコードの更新 レコードを更新するには DataRow オブジェクトを直接書き換える 例 : レコードを更新する ( 最初のレコードの価格フィールドを変更 ) Dim Dr As DataRow = Ds.Tables("BOOK").Rows(0) Dr.BeginEdit( ) Dr.Item(" 価格 ") = 3500 Dr.EndEdit( ) Item プロパティを設定すると 其の時点で レコードが編集モードと成るが 何の時点で編集を終了するのかが解り難く成る為 変更前に 明示的に BeginEdit メソッドを呼び出して編集モードに入り 変更後に EndEdit メソッドを呼び出して編集モードを抜ける様にする 猶 EndEdit メソッドを呼び出す前に CancelEdit メソッドを呼び出すと BeginEdit メソッドを呼び出す前の値に戻す事が出来る レコードを更新しても 更新したレコードが直ちにデータベースに反映される訳では無く Update メソッドを呼び出した時に DataSet オブジェクトよりデータベースに書き戻される レコードの削除 レコードを削除するには DataRow オブジェクトの Delete メソッドを呼び出す 例 : レコードを削除する ( 最初のレコードを削除 ) Dim Dr As DataRow = Ds.Tables("BOOK").Rows(0) Dr.Delete ( ) レコードを削除しても 削除したレコードが直ちにデータベースに反映される訳では無く Update メソッドを呼び出した時に DataSet オブジェクトよりデータベースに書き戻される -14-
データベースの更新 DataTable オブジェクト内のレコードに対する変更 ( 追加 更新 削除 ) は Update メソッドを呼び出した時点で データベースに書き戻される 例 : データセットをデータベースに書き戻す DaB.Update(Ds) データベースに書き戻す対象と成る DataRow オブジェクトは DataRow オブジェクトの RowState プロパティに依り 判断される RowState プロパティは 下記の DataRowState 列挙体の値が設定されて居る 値 Added Deleted Modified Unchanged Detached 意味追加されたレコード削除されたレコード更新されたレコード変更されて居ないレコード何の DataTable オブジェクトの Rows コレクションにも属さないレコード DataRow オブジェクトの RowState プロパティに依り 実行されるクエリが決定される DataRowState.Added の場合 InsertCommand プロパティに設定したクエリを実行 DataRowState.Modified の場合 UpdateCommand プロパティに設定したクエリを実行 DataRowState.Deleted の場合 DeleteCommand プロパティに設定したクエリを実行 OleDbDataAdapter のコマンドを OleDbCommandBuilder を使用して生成 下記の様に OleDbCommandBuilder を使用して OleDbDataAdapter の InsertCommand UpdateCommand DeleteCommand の各コマンドを自動生成する事も出来る ' 接続文字列の設定 Dim CNS As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb" ' 各オブジェクトのインスタンス生成 Dim Cn As OleDbConnection = New OleDbConnection(CNS) Dim Da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM 本 ", Cn) Dim Bd As OleDbCommandBuilder = New OleDbCommandBuilder(Da) 自動生成されたコマンドをイミディエイトウィンドウに出力して確認するには 下記の様にする Debug.WriteLine(Bd.GetInsertCommand( ).CommandText) Debug.WriteLine(Bd.GetUpdateCommand( ).CommandText) Debug.WriteLine(Bd.GetDeleteCommand( ).CommandText) 亦 コマンドテキストの変更するには 下記の様にする Bd.GetInsertCommand( ).CommandText = "INSERT INTO " Bd.GetUpdateCommand( ).CommandText = "UPDATE " Bd.GetDeleteCommand( ).CommandText = "DELETE FROM " 此の方法だと SelectCommand を変更すると RefreshSchema メソッドを呼び出す必要が有る 亦 必ず主キーが設定されて居なければ成らない -15-