ＶＢ実用①　データベースⅠ

VB でデータベース Ⅰ VB 2005 1 プログラムの概要 データベースプログラムを手軽に作成する方法と仕ては Access のフォーム機能を用いる事が 先ず考えられる ウィザードも充実して居り 操作を覚えれば簡単に作成する事が出来る 其れでは VB でデータベースプログラムを作るメリットは何かと謂えば先ず Access のフォーム機能に比べ コントロールが充実して居り より自由なデザインが可能と謂う事と 更に重要な点と仕て データベースソフトを選ばない事で有る 小規模な運用で有れば Access は十分な能力を発揮するが 中規模 大規模に成ると 力不足に成る可能性が高く成る VB でプログラムを作成して置けば 其の様な場合 少し変更で 他のデータベースソフト (SQL Server 等 ) へ移行する事が可能で有る 此処では データベースプログラム作成の基本に付いて学ぶ 今回の課題項目 データベースの概要 ( データベースとは データを使用した作業 ) データベース処理オブジェクト (ADO.NET ADO DAO) SQL Server データベースの作成 ( テーブルデザイナ テーブル キー データの追加 ) SQL Server データベースに接続 ( データソース構成ウィザード 接続の追加 接続文字列 ) SQL Server データベースの操作 (DataSet BindingSource TableAdapter BindingNavigator) 今回の重点項目 データベース処理オブジェクト (ADO.NET ADO DAO) SQL Server データベースの作成 ( テーブルデザイナ テーブル キー データの追加 ) SQL Server データベースに接続 ( データソース構成ウィザード 接続の追加 接続文字列 ) SQL Server データベースの操作 (DataSet BindingSource TableAdapter BindingNavigator) 今回の応用項目 様々な SQL 文を試してみる 1

VB データベース データベースとは データベースの概要 : データの格納とアクセス データベースは データの集合で有り プログラムから独立したファイルに格納される データベースに格納されるデータは テキスト 数値 画像等 様々な種類が有る 亦 様々なプログラムが同じデータベースに接続して データベース内のデータを表示 編集する事が出来る 通常 データベースは 1 つ以上のテーブルに分かれて居る テーブルは 関連するレコードの集合で有る 例えば 購入した書籍に関するデータを保持したデータベースを使用する場合 書籍を表すテーブル カテゴリを表すテーブル 著者を表すテーブル等を使用する 各テーブルは 列 ( フィールド カラム ) と行 ( レコード ロウ ) のグリッドと仕て構成される 列は レコード内のデータのカテゴリを表し 行は 個別のレコードを表す 例えば 下図では 各書籍を表す個別の行 及び 購入した書籍を表す列が 価格や購入日等と共に本テーブルに表示される データを使用した作業の開始 プログラムからデータベース内のデータにアクセスするには 先ず アクセスするデータベースを用意する必要が有る Visual Basic を使用すると 独自のデータベースの作成や 他で作成されたデータベースの利用が簡単に出来る データベースを準備した後 DataSet オブジェクトを使用してデータベースをプログラムに関連付け データバインディング機能を使用して フォーム上のフィールドやコントロールにデータを関連付ける TextBox コントロール等 プログラム内のフィールドがデータベーステーブルの列にバインドされて居る場合 テキストボックス内に着目列のデータを表示し テキストボックスの内容を変更してデータベースに保存したり 新しいレコードのデータを入力してデータベースに追加する事が出来る 2

データベース処理オブジェクトの分類 従来からのデータベース処理オブジェクト Visual Baisc 6.0 では DAO(Data Access Objects) と ADO(ActiveX Data Objects) と謂う 2 種のデータベース処理用オブジェクトが提供されて居た DAO(Data Access Objects) DAO は Jet ワークスペース ODBC Direct ワークスペースをサポートするオブジェクトで 従来の VB の各バージョンでサポートされて居る DAO は Data コントロールを介してアクセスする ADO(ActiveX Data Objects) ADO は OLE DB をサポートし データベース間の差異を吸収した汎用的データベースオブジェクトで有る ADO は ADODC コントロールを介してアクセスする 新たに追加されたデータベース処理オブジェクト Visual Basic.NET では 従来の ADO を従来の方法で使用する事も出来るが 通常 データベースにアクセスするには ADO を.NET Framework 用に進化させた ADO.NET を使用する ADO と ADO.NET の根本的な相違点は ADO が接続型で有るのに対して ADO.NET が非接続型で有る事で有る ADO では データベースに常時接続した状態で使用するのが基本で有る 其の為 常に最新のデータを取得し 更新する事も可能で有るが 実際には データの転送時以外は データベースに接続して居る必要が無い場合が多い 亦 データベースへの接続を維持する事で システムリソースを消費し パフォーマンスが低下すると謂う負の側面も有る ADO.NET では データセットに依り データベースと粗同じ物をメモリ上に保持する事が出来るので データベースに接続する必要が有るのは データセットにデータを取得する時と データセットで変更した内容をデータベースに転送する時丈で有る 其れ以外の時はデータベースの接続を解除し データセットを利用してデータベース操作を行う ADO.NET には ADO では提供されて居た ADODC の様な便利なコンポーネントは無いが データソース構成ウィザード等のウィザードが充実して居るので 基本的な構成は 殆ど GUI で作成する事が出来る 猶 ADO.NET は Visual Basic.NET 及び Visual Basic.NET 2003 では SqlConnection SqlDataAdapter DataSet DataView DataGrid を使用して操作して居たが Visual Basic 2005 では 此等を使用する事も可能だが DataConnection DataGridView を使用しての操作が 新たに追加され 選択肢が増えた ADO と ADO.NET の使い分け 新規にアプリケーションを開発する時は ADO.NET を利用すると 豊富なツールを利用して 効率良くアプリケーションを開発する事が出来る ADO.NET では データベースと常時接続する必要が無い為 システムの負荷を軽減する事が出来る 併し 非接続型故に 大量のレコードの即時更新や 常に最新の情報が必要な場合には 不適格で有り 此の様な場合は 常時接続型の ADO を利用すると良い.NET Framework でも ADO は サポートされて居る 3

SQL Server データベースの作成 概要 Visual Basic Express Edition では Microsoft SQL Server データベースと Microsoft Access データベースの 2 種類のデータベースにアクセス出来るので 此処では SQL Server データベースを使用する方法に付いて説明する 前提条件 Visual Basic Express Edition を使用して SQL Server データベースを作成してアクセスするには SQL Server Express Edition もインストールする必要が有る 既定では Visual Basic Express Edition のインストール時に SQL Server Express Edition もインストールされるが インストールしない様に選択をした場合 事前にインストールを実行して置く必要が有る データベースの作成 先ず database1 と謂う名前の新しい Windows フォームプロジェクトを作成する ( プロジェクト名は 各自の目的に適合した任意の名前を付けても良い ) 統合開発環境の プロジェクト メニューより 新しい項目の追加 をクリックして 新しい項目の追加 ダイアログボックスを表示する ソリューションエクスプローラで プロジェクト名を右クリックし ポップアップメニューで 追加 新しい項目 と進んでも ダイアログボックスを表示する事が出来る 4

表示された 新しい項目の追加 ダイアログボックスで SQL データベース を選択し ファイル名 ボックスに data.mdf と入力して 追加 ボタンをクリックする ( ファイル名は 各自の目的に適合した任意の名前を付けても良い ) マシンパワーの低いコンピュータでは 下記のダイアログボックスが表示される前に タイムアウトが発生する事が有るが 其の場合は 前図に戻るので 再度 追加 ボタンをクリックする 猶 マシンパワーの低いコンピュータでは ダイアログボックスが表示される迄に 結構な時間を要するので 途中で強制終了しない様にする 表示された データソース構成ウィザード で キャンセル をクリックする 以上で data.mdf と謂う新しいデータベースがプロジェクトに追加され ソリューションエクスプローラに表示される 上記の様に Visual Basic 2005 では SQL Server データベースの作成は簡単で有る 併し 此の時点では 未だ データベースには データが含まれて居ない為 役に立たない 次の手順と仕て データベースにテーブルを追加する必要が有る テーブルとは 表形式で管理する情報の事を謂い データベースに於いては 個々の情報を格納するフィールド ( カラムとも謂う ) から成る 1 件分の情報を格納するレコード ( ロウとも謂う ) の集合で有る 従って データベースとは 多数のデータを一定の構造で保持する物で有り 通常 特定のデータ型のフィールドから成るレコードを基本的なアクセス単位とし 複数のレコードで構成され フィールドの値やレコードの検索 並替 再結合等の一連の操作が出来る様に仕た物で 多数のデータを無秩序に集めた物は データベースとは謂わない 5

テーブルの追加 統合開発環境に サーバーエクスプローラ タブが表示されて居ない場合は 表示 メニューより サーバーエクスプローラ をクリックして サーバーエクスプローラ タブを表示する 既定では 左端のツールボックスと重なる様に表示される 下図の様に サーバーエクスプローラ を表示して data.mdf ノードの先頭の正符号をクリックして 当該ノードを展開し テーブル ノードを選択した後 データ メニューの 新規追加 をポイントし テーブル をクリックする テーブルノードを右クリックし ポップアップメニューで 新しいテーブルの追加 をクリックしても良い 以上で 下図の様な テーブルデザイナ ウィンドウが表示されるので プロパティ ウィンドウで オブジェクト名 を選択し 本 と入力する ( 此れがテーブル名と成る ) 6

下図の テーブルデザイナ ウィンドウで 下記の様に フィールド定義を行う 更に 前述の手順で 2 個のテーブルを追加し 下記の様に カテゴリ テーブルと 著者 テーブルの夫々れのフィールド定義を行う 猶 此等の定義は 対象とするテーブルノードを右クリックして表示されるポップアップメニューで テーブル定義を開く をクリックする事に依り 何時でも編集する事が出来る 7

キーの追加 キー列は 主キー (Primary Key) とも呼ばれ キー列に指定した列 ( フィールド カラム ) の値は 其のテーブル内で一意と成り テーブル内の行 ( レコード ロウ ) を区別 ( 特定 ) する為の物で有る 即ち キー列に指定した列の値は テーブル内の同じ列に 1 個しか存在しない 同じ値の 2 個目の行を入力するとすると エラーが発生する キー列を設定するには テーブルデザイナ ウィンドウに キー列を設定するテーブルを表示し キー列を設定する列を選択した後 テーブルデザイナ メニューの 主キーの設定 をクリックする 猶 テーブルデザイナ ウィンドウで項目を右クリックし ポップアップメニューで テーブルデータの表示 をクリックしても良い オートナンバー型のフィールドを設定するには 設定する列を選択した後 テーブルデザイナ メニューの IDENTITY の指定 で (Is Identity) 欄を はい に変更する 本 カテゴリ 著者の各テーブルの ID フィールドに対して IDENTITY を設定する Microsoft SQL Server では Is Identity を はい に設定すると IDENTITY インクリメント ( 増分値 ) と IDENTITY シード ( 初期値 ) が 自動的に設定され オートインクリメントフィールドと成る 8

データの追加 最終的には プログラムからデータを操作する事を目的とするが テーブルデザイナ ウィンドウでも データの入力 編集 削除を行う事が出来る 此処では 初期データを入力する事にする テーブルデザイナ ウィンドウでデータの入力を行うには データ メニューで テーブルデータの表示 をクリックする テーブルノードを右クリックし ポップアップメニューで テーブルデータの表示 をクリックしても良い 下図の様な テーブルデザイナ ウィンドウが表示されるので 各フィールドに 下記のデータを入力する テーブル : 本 ID タイトル 価格 購入日 カテゴリ ID 著者 ID 1 VB テクニック集 3500 2001/02/10 4 1 2 EXCEL テクニック集 2500 2000/09/10 2 3 3 WORD テクニック集 2500 2000/09/10 1 3 4 ACCESS テクニック集 3000 2000/10/20 3 4 5 実用 VB 2000 2001/01/09 4 1 6 実用 EXCEL 1300 2000/09/10 2 2 7 実用 WORD 1300 2000/08/09 1 2 8 実用 ACCESS 1500 2000/10/03 3 4 9 VB ファン 4000 2000/05/05 4 4 テーブル : カテゴリ ID カテゴリ名 トータル 1 ワード 0 2 エクセル 0 3 アクセス 0 4 VB 0 各テーブルの ID フィールドはオートナンバー型なので 入力する必要は無い テーブル : 著者 ID 著者名 フリガナ 1 赤塚昭二 アカツカショウジ 2 伊川憲明 イカワノリアキ 3 宇田健史 ウダケンジ 4 遠藤裕子 エンドウユウコ 9

SQL Server データベースの操作 データベースに接続 此処では 前項で作成したデータベースに接続する為の database2 と謂う名前の新しい Windows フォームプロジェクトを作成する ( プロジェクト名は 各自の目的に適合した任意の名前を付けても良い ) ソリューションエクスプローラの データソース タブをクリックして データソース ウィンドウで 新しいデータソースの追加 をクリックすると データソース構成ウィザードが起動する 既に 何等かのデータソースが設定されて居る場合は 其のデータセットが表示されて居り 上図の様に 新しいデータソースの追加 リンクが表示されないので 其の場合は 上図のツールバーの左端の 新しいデータソースの追加 アイコンをクリックする 下図の データソース構成ウィザード の データソースの種類を選択 で データベース が選択されて居る事を確認して 次へ ボタンをクリックする 通常 データベース が 既定で選択されて居るが 選択されて居ない場合は データベース アイコンをクリックして 選択する 10

下図の データソース構成ウィザード の データ接続の選択 で 新しい接続 ボタンをクリックすると 新しい接続 ダイアログボックスが表示される 下図の 接続の追加 ダイアログボックスで データソース が Microsoft SQL Server データベースファイル (SqlClient) でない場合は 変更 ボタンをクリックし データソースの変更 ダイアログボックスで Microsoft SQL Server データベースファイル を選択して OK ボタンをクリックする 11

元の 接続の追加 ダイアログボックスに戻るので 今度は データベースファイル名 入力ボックスの左側に有る 参照 ボタンをクリックする 前項で作成したデータベースは 前項のプロジェクトのルートフォルダに作成されて居る 其の儘で使用しても良いが 適当な場所にフォルダを作成し 其の中に mdf ファイルをコピーして使用する様にする事を推奨する 下図の SQL Server データベースファイルの選択 ダイアログボックスで data.mdf データベースを保存した場所に移動し 当該ファイルを選択して 開く ボタンをクリックする Microsoft SQL Server データベースファイルが表示されない場合は ファイルの種類 が mdf ファイルに成って居るか確認する 12

再び 元の 接続の追加 ダイアログボックスに戻るので 接続の確認 ボタンをクリックして 正しく接続されるか何うか確認する テスト接続に成功しました と謂うダイアログボックスが表示されゝば OK ボタンをクリックする 亦 元の 接続の追加 ダイアログボックスでも OK ボタンをクリックして ダイアログボックスを閉じて データソース構成ウィザード に戻る 接続の確認を行う事は 必須ではないが テスト接続に成功しない場合は プログラムの実行でも 接続の確立に成功する筈が無いので 必ず接続の確認を行う事を推奨する Windows 認証 ( 規定値 ) を選択した場合 個別のユーザー ID とパスワードは Windows のネットワークセキュリティプロセスから取得する為 ユーザーが再入力する必要が無い 一方 SQL Server 認証を選択した場合 サーバーエクスプローラから接続を開く時に ユーザー名とパスワードの入力を求める様にログオン要件を設定する事に成る 13

データソース構成ウィザード に戻ると 次へ ボタンをクリックする データファイルがプロジェクトと同じ場所に無い場合は データファイルをプロジェクトにコピーするか何うか確認するメッセージが表示されるので いいえ をクリックする いいえ を選択した場合 此処で いいえ を選択した場合 リモートデータベースに接続する時と同じ様に接続が作成され 現在の場所に有る既存のデータベースファイルに接続する事に成る 此の場合 アプリケーションの開発中 ( 開発環境内で実行時 ) に データに対して行われた変更は 既存のデータベースに反映される はい を選択した場合 アプリケーションの開発中のデータに対する変更を 元のデータベースに反映させ度くない場合には はい を選択する 此の場合 データベースのコピーが プロジェクトのルートフォルダに作成されて プロジェクトに追加され プロジェクト内のデータベースを指す様に接続が変更される ( サーバーエクスプローラの既存のデータ接続も プロジェクト内のデータベースファイルを指す様に変更される ) 猶 此の場合は 既定の設定では プロジェクトをビルド デバッグ 実行する度に データベースファイルがルートプロジェクトフォルダから出力 (bin) フォルダにコピーされ アプリケーションの開発中のデータに対する変更は 此の bin フォルダのデータベースに反映される 14

下図の データソース構成ウィザード の 接続文字列をアプリケーション構成ファイルに保存する では 次の名前で接続を保存する チェックボックスをオンに仕て 次へ ボタンをクリックする 接続を保存する名前は 任意に変更しても良い 此の値は プロジェクトのプロパティの 設定 タブで確認する事が出来る 亦 コードで Dim S As String = My.Settings.dataConnectionString の様に参照する事も出来る 下図の データソース構成ウィザード の データベースオブジェクトの選択 では テーブル ノードを展開して 使用するテーブルのチェックボックスをオンにし 完了 ボタンをクリックする 以上で データソース ウィンドウに 今作成したデータセットオブジェクトが追加される 亦 データファイルがプロジェクトと同じ場所に無い場合で データファイルをプロジェクトにコピーするか何うか確認するメッセージに はい と仕た場合には ローカルデータベースファイルがプロジェクトに追加されて居る 15

データの表示 此処では ローカルデータベース内のデータを表示する為の基本的なユーザーインターフェイスを作成する方法を説明する データベースからデータを取得し 其のデータをユーザーインターフェイスに表示する処理の背後に有るプログラミングは 非常に複雑で有るが Visual Basic では 必要なデータオブジェクトが自動的に作成 構成される為 ユーザーに必要な操作は オブジェクトの選択と配置而巳で有る データベースウィンドウでデータセットノードを展開し 表示するテーブルノードを フォームにドラッグアンドドロップする 上記の操作で 下図の様に データ表示に必要なコントロールやコンポーネントが 自動的にフォームに追加される 猶 DataGridView コントロールの Dock プロパティを Fill に設定すると グリッドがフォーム全体に拡張され フォームのリサイズと共に 大きさが変化する様に成る 16

プログラムの実行 メニューバーの デバッグ から デバッグ開始 をクリックすると 下図の様な実行画面が表示される 上部の BindingNavigator オブジェクト ( ツールバー ) で レコードの移動やデータの追加 削除 変更を行う事が出来る 特にコードを記述する事無く データを表示し 亦 レコードの移動やデータの追加 削除 変更を行う事が出来るが 此れは フォームの Load イベントと BindingNavigatorSaveItem オブジェクトの Click イベントに自動的にコードが記述される為で有る Public Class database2 Private Sub 本 BindingNavigatorSaveItem_Click( ByVal sender As System.Object, _ ByVal e As System.EventArgs ) Handles 本 BindingNavigatorSaveItem.Click Me.Validate( ) Me. 本 BindingSource.EndEdit( ) Me. 本 TableAdapter.Update( Me.dataDataSet. 本 ) End Sub Private Sub database2_load( ByVal sender As System.Object, _ ByVal e As System.EventArgs ) Handles MyBase.Load Me. 本 TableAdapter.Fill( Me.dataDataSet. 本 ) End Sub End Class 注意 Visual Basic 2005 Professional Edition では ビルトエラー ( 型 ' クラス名. データセット名 ' が定義されて居ません 等 ) が発生する事が有る 此の場合 エラー一覧 で 当該エラーをダブルクリックすると クラス名.Designer.vb の該当部分 ( 青色の波下線部 ) が表示される 此の部分の クラス名. の部分を削除すると エラーは回避する事が出来る 此の不具合は Service Pack 1 を当てゝも 解消されて居ない 猶 Visual Basic 2005 Express Edition では 此のビルトエラーは 発生しない 17

VB 2005 1 VB 2005 実用 Ⅰ ユーティリティ noa 自動生成されるクエリ ウィザードに依り自動的に生成されたクエリを確認するには DataSet オブジェクトを選択し 右上に表示される三角マーク ( : スマートタググリフ ) をクリックすると タスク一覧のアクションウィンドウが表示されるので 其の中から データセットデザイナで編集 を選択する 既定で画面中央に データセットデザイナ ウィンドウが表示されるので クエリを確認する TableAdapter オブジェクトを選択すると プロパティ ウィンドウで各クエリを確認する事が出来る 自動生成される各クエリ (CommandText プロパティ ) は 下記の通りで有る 本 TableAdapter の自動生成クエリ DeleteCommand( 削除クエリ ) DELETE FROM [dbo].[ 本 ] WHERE (([ID] = @Original_ID) AND ([ タイトル ] = @Original_ タイトル ) AND ([ 価格 ] = @Original_ 価格 ) AND ([ 購入日 ] = @Original_ 購入日 ) AND ([ カテゴリ ID] = @Original_ カテゴリ ID) AND ([ 著者 ID] = @Original_ 著者 ID)) InsertCommand( 追加クエリ ) INSERT INTO [dbo].[ 本 ] ([ タイトル ], [ 価格 ], [ 購入日 ], [ カテゴリ ID], [ 著者 ID]) VALUES (@ タイトル, @ 価格, @ 購入日, @ カテゴリ ID, @ 著者 ID); SELECT ID, タイトル, 価格, 購入日, カテゴリ ID, 著者 ID FROM 本 WHERE (ID = SCOPE_IDENTITY()) SelectCommand( 選択クエリ ) SELECT ID, タイトル, 価格, 購入日, カテゴリ ID, 著者 ID FROM dbo. 本 UpdateCommand( 更新クエリ ) UPDATE [dbo].[ 本 ] SET [ タイトル ] = @ タイトル, [ 価格 ] = @ 価格, [ 購入日 ] = @ 購入日, [ カテゴリ ID] = @ カテゴリ ID, [ 著者 ID] = @ 著者 ID WHERE (([ID] = @Original_ID) AND ([ タイトル ] = @Original_ タイトル ) AND ([ 価格 ] = @Original_ 価格 ) AND ([ 購入日 ] = @Original_ 購入日 ) AND ([ カテゴリ ID] = @Original_ カテゴリ ID) AND ([ 著者 ID] = @Original_ 著者 ID)); SELECT ID, タイトル, 価格, 購入日, カテゴリ ID, 著者 ID FROM 本 WHERE (ID = @ID) 18

SQL Server の起動 Visual Basic 2005 Professional Edition や Express edition をインストールで SQL Server もインストールすると SQL Server は サービスに登録され Windows 起動時に SQL Server が起動する様に設定される 併し 何等かの理由に依り SQL Server が停止して居る場合は SQL Server データベースに接続して使用する事は出来ない 其の場合には SQL Server を手動で起動する必要が有る SQL Server の起動と停止 SQL Server を使用する場合は 事前にインストールされた SQL Server を起動して置く 起動するには コントロールパネル 管理ツール サービス と進み SQL Server(SQLEXPRESS) を右クリックして表示されたコンテキストメニューから 開始 を選択する 状態 欄に 開始 と表示されゝば 起動して居る 猶 SQL Server を自動起動し度い場合は プロパティシートから スタートアップの種類 に 自動 を選択すると 起動時に SQL Server が起動される様に成る 猶 設定を変更するには 管理者権限でログインする必要が有る 管理者権限以外でログインした場合は 設定を確認する事は出来るが 設定を変更する事は出来ない 19