VB実用⑤　アクセス操作Ⅲ

Size: px

Start display at page:

Download "VB実用⑤　アクセス操作Ⅲ"

ゆみかかみいしづ
5 years ago
Views:

VB でアクセス操作 Ⅲ VB 2005 5 プログラムの概要前回に引き続き此処ではコードに依るデータベースの操作方法を学ぶコネクションオブジェクトを用いてデータベースと接続し表形式でデータを表示するデータグリッドビューコントロールにレコードデータを自動的に表示する手法を学ぶ既に学んだ様にコントロールを連結する事に依りコードレスでデータベース操作が可能で有るが此処では

NET に依るデータベース接続 (ConnectionString プロパティ Open メソッド ) ADO.NET に依るデータベース操作 ( データアダプタオブジェクト ) ADO.NET に依るデータベース操作 (SelectCommand CommandText プロパティ ) ADO.

1 VB でアクセス操作 Ⅲ VB プログラムの概要前回に引き続き此処ではコードに依るデータベースの操作方法を学ぶコネクションオブジェクトを用いてデータベースと接続し表形式でデータを表示するデータグリッドビューコントロールにレコードデータを自動的に表示する手法を学ぶ既に学んだ様にコントロールを連結する事に依りコードレスでデータベース操作が可能で有るが此処では着目して居る行 ( カレントレコード ) の指定と個々の列データ ( フィールドデータ ) の取得に付いて学ぶ亦高度なデータベース操作の下準備と仕て 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 削除

2 オブジェクトプロパティ一覧コマンドボタン 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-

3 プログラムリスト ' 名前空間のインポート 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-

4 ' フォームが閉じられ様と仕た時の処理 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 に選択する項目のインデックスを設定するテキストボックス内に記述されたテキストを消去するにはヌルストリング ( 二重引用符の中に何も記述しない文字列 ) を設定する

5 ' コンボボックス ( カテゴリ ) の選択項目が変化した時の処理 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-

6 ' ボタン ( 変更 ) がクリックされた時の処理 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 は呼出元へ制御を戻すステートメントで有る猶引数で指定した値は戻り値と成る

7 ' データベースを更新するジェネラルプロシージャ 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-

8 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-

9 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-

10 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-

11 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-

12 データアダプタをコードで設定する方法データアダプタをコードで設定する手順は下記の通りで有る 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 を使用する為追加する順番は問題では無い -12-

13 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-

14 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-

15 データベースの更新 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-

VB実用③　アクセス操作Ⅰ

VB実用③　アクセス操作Ⅰ VB でアクセス操作 Ⅰ VB 2005 3 プログラムの概要此処ではコントロールを使用しないでコードに依るデータベースへの接続とデータの取得の方法を学ぶコントロールに依るデータベース接続は簡単にデータベースを操作する事が出来ると謂う利点が有り小規模な運用で有れば充分な能力を発揮する併し中規模大規模に成り複雑な操作を必要とする様に成れば力不足に成る可能性が高く成る然う成れば

VB実用⑤ アクセス操作Ⅲ

VB実用⑤　アクセス操作Ⅲ