| Visual Basic データベース講座 |
|
|
2006.11.25
データを取得、変更、追加、削除するプログラムを紹介します。その中でテーブルの構造やSQLについて説明します。
| 概要 ・データを指定するには、データベースとテーブルと行と列の４つの要素を使用する。 ・データベースを指定するには接続を使用する。 ・SQLを使うとデータベースに関するさまざまな制御が行える。 |
今回は実際にプログラムでデータを読み書きする方法を説明します。データを読み書きするにはまず対象のデータをどのように特定するのか知っていなければなりません。つまり、「どのデータを読み書きするのか」の「どの」の指定の方法です。
標準的なデータベースではデータを指定するために次の４つの要素を使用します。
これらについて１つずつ見ていきましょう。
まず、データベースですがこれは今回はC:\Database\Animals.mdbのことです。前回はデータベースエクスプローラ(またはサーバーエクスプローラ)を使ってAnimals.mdbに接続しましたが、そのときにいくつかの設定を行いました。プログラムでデータベースに接続するためにはこれらの設定を文字列で指定します。この文字列のことを接続文字列と呼びます。今回使用する接続文字列は以下のとおりです。
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\Animals.mdb
この接続文字列さえ正しければどのようなデータベースにも接続できます。逆に接続文字列がわからなければどのデータベースにも接続できません。接続文字列を知るにはどうしたらよいでしょうか？MSDNライブラリに記載されている内容を熟読すれば接続文字列を作成できるようになりますが、そうしなくても実は簡単に接続文字列を取得する方法があります。
データベースエクスプローラ(またはサーバーエクスプローラ)を開くと前回の設定が残っています。もし前回の設定を行っていないならばちょっともどって前回説明している手順でデータベースに接続してください。
ここで、Animals.mdbの接続をクリックすると、プロパティウィンドウにいくつかの情報が表示されます。その中にはちゃんと「接続文字列」という情報もありますから、ここの文字列をコピーしてもってくればいいわけです。
■画像１：プロパティウィンドウに表示されている接続文字列。
具体的にこの接続文字列を使ってデータベースに接続するプログラムは次のとおりです。
Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\Animals.mdb") |
■リスト１：データベースへの接続
この他にコードの冒頭にImports System.Data.OleDbと記述してください。
このプログラムは接続クラスを作成しているだけなので実行しても何も起きません。
この例でCnは「接続」をあらわすOleDbConnectionクラスのインスタンスです。今までボタンをあらわすButtonクラスや、文字列をあらわすStringクラスなど を扱ってきたと思いますが、「接続」そのものも具象化されてクラスになっています。
「接続」のような形のない概念がクラスになっているのではじめのうちは戸惑うかもしれませんね。
なお、この講座ではAccessのMDBを使用しているので接続をあらわすクラスがOleDbConnectionクラスになっていますが、もし、SQL Serverをデータベースとして使うのならばSQLConnectionクラス、OracleならばOracleConnectionクラスを使用することになります。
このようにデータベースの種類によって使用するクラスが異なりますが、これらのクラスは基本的な使用方法はすべて同じですのでどれか１つの使い方を覚えれば他にも通用します。
| メモ - 種類別データベースへの接続 各種類のデータベースの制御に使う名前空間と接続用のクラスをまとめておきます。
■表１：データベースごとの名前空間の違い 接続以外でもデータベースアクセスに使うさまざまなクラスが種類別にわかれていますが、どれも基本的な使い方は同じです。 この表に挙げた以外にもサードパーティ製のクラスが存在する場合もあります。たとえば、Oralceへの接続にはOracle社が.NET用に開発したOracle Data Provider for .NET(通称ODP.NET)が提供されています。 |
次に行と列の指定方法について説明します。
説明のためにAnimals.mdbの[T_目マスタ]テーブルの中を少し抜粋します。次のようになっています。
| 目ID | 目名 | 読み | 綱ID | 説明 |
| 1 | 霊長目 | レイチョウモク | 1 | ゴリラ、サル、チンパンジー、オランウータン |
| 2 | 翼手目 | ヨクシュモク | 1 | コウモリ |
| 3 | 皮翼目 | ヒヨクモク | 1 | ヒヨケザル |
| 4 | 食虫目 | ショクチュウモク | 1 | モグラ、ジネズミ、ハリネズミ |
| 5 | 齧歯目 | ゲッシモク | 1 | ウサギ、ネズミ、リス、ムササビ、モモンガ |
■表２：[T_目マスタ]テーブルの抜粋
ここでは先頭から５行のデータを抜粋しています。このような行のことをデータベースの世界では「レコード」と呼びますので覚えておいてください。
メモ帳やExcelや普通の人間の感覚で言うと「行」というのは上から何行目とか下から何行目とかいうもののことですが、データベースでは表示方法の指定によってどのような順序で表示するかいくらでも変更できますから上から何行目、下から何行目という指定方法は使用しません。
その代わりにレコードごとの固有の値を使用します。[T_目マスタ]テーブルでは「目ID」がこの固有の値です。どのレコードもそれぞれ異なった目IDを持っています。VBのデータベースエクスプローラ(またはサーバーエクスプローラ)を使って一番下の行にデータを追加してみるとわかりますが、同じ目IDを持ったデータは追加できません。しかし、同じ目名のデータは追加できます。
このようにレコードごとに固有の値のことを「キー」と呼びます。[T_目マスタ]テーブルのキーは目IDです。キーはテーブルの設定で変更することができます。Animals.mdbではあらかじめ私がキーを設定しておきました。みなさんはキーの設定を変更することもできますが、今後の説明に支障が生じますからとりあえずそのままにしておいてください。
このキーを使えばどのような順番に並んでいようとも目的の行を特定することができます。たとえば、翼手目の説明である「コウモリ」は目ID=2のレコードの[説明]列の値と表現することができます。
| メモ - MDBではサポートされていない機能 データベースがMDBではなく、SQL Serverの場合はサーバーエクスプローラを使ってテーブルを新規作成したり、列やキーの設定を変更することができます。MDBではこれらの機能はサポートされていないのでテーブル自体の変更を行うにはAccessなど別のツールを使用するか自分でプログラムする必要があります。 なお、SQL Server Express Editionに対して何ができるかは未確認です。少なくともMDBと同等の操作はできると思いますがそれ以上どこまでサポートされているかわかりません。 |
まとめると、テーブルでは横方向に表現されるものを行・レコードと呼び、縦方向に表現されるものを列と呼びます。
■画像２：行と列
列は「カラム」、「フィールド」、「項目」などと呼ぶ時もあります。
ではいよいよプログラムを記述してデータベースの値を取得してみましょう。
上記の「コウモリ」を取得するプログラムは次のようになります。
| Dim Cn
As
New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand SQLCm.CommandText = "SELECT 説明 FROM T_目マスタ WHERE 目ID = 2" Dim Value As String Cn.Open() Value = SQLCm.ExecuteScalar Cn.Close() MsgBox(Value) |
■リスト２：データベースから値を読み込む
このプログラムの意味は説明がなくてはわからないかもしれませんが、データベース、テーブル、行、列の４つの要素をどこで指定しているかは見ただけでわかっていただけますよね。これであなたはデータベースから好きな値を取得できるようになりました。
それでは、プログラムの意味も説明しましょう。
前回までにも説明しましたがデータベースの実際の操作はすべてデータベースエンジンが行います。プログラマはこのデータベースエンジンに対して命令するわけで、この命令の手段としてSQL(読み方：SQL = エスキューエル)という構文を使用します。
プログラムの中に登場する以下の部分がこのSQLです。
SELECT 説明 FROM T_目マスタ WHERE 目ID = 2 |
このSQLの意味は「[T_目マスタ]テーブルから目ID=2のレコードの[説明]を取得しなさい」という意味です。
SQLではさまざまな構文や命令、関数が用意されておりデータベースプログラミングではSQLの理解が必須です。この講座でもSQLについての説明もしていくことになるでしょう。
プログラム中に登場するOleDbCommandクラス(読み方：OleDbCommand = オーエルイーディービーコマンド)はSQL文をデータベースエンジンに伝えたり、その設定や結果を仲介する機能があります。
実際に上記のSQL文を実行しているのはこのクラスのExecuteScalarメソッド(読み方：ExecuteScalar = エグゼキュート スカラー)です。
ExecuteScalarメソッドはCommandTextプロパティ(読み方：CommandText = コマンドテキスト)にセットされたSQL文を実行して結果を受け取るメソッドです。
OleDbCommandクラスにはこの他にもSQLを実行する別のメソッドがいくつか用意されていますが、それらは登場する都度説明することにします。
注意していただきたいのは実際にExecuteScalarメソッドを使用してデータベースにSQL文を伝える前後で接続を開いて、閉じている点です。ExecuteScalarメソッドを使用するにはデータベースへの接続が開いている必要があるのです。
接続の制御はOleDbConnectionクラスが担当していますから、このクラスのOpenメソッド(読み方：Open = オープン)で接続を開き、Closeメソッドで接続を閉じます。データベースは同時接続数が増えるとパフォーマンスが低下しますし、ライセンス上の制限からも同時接続数が増えることは好ましくありません。そのため、接続は必要な時だけ開いておいて必要がなくなったらすぐに閉じるようにします。
今度は値を書き込んでみます。さきほどの「コウモリ」のところを、「コウモリ、オオコウモリ」に変更してみましょう。
次のようになります。
| Dim Cn
As
New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand SQLCm.CommandText = "UPDATE T_目マスタ SET 説明= 'コウモリ, オオコウモリ' WHERE 目ID = 2" Cn.Open() SQLCm.ExecuteNonQuery() Cn.Close() MsgBox("変更しました。") |
■リスト３：データベースの値を変更する
さきほどの値を取得するプログラムとかなり似ています。こちらではSQL文を発行するメソッドがExecuteScalarではなくExecuteNonQuery(読み方：ExecuteNonQuery = エグゼキュートノンクエリー)になっています。この２つのメソッドにはほとんど違いはありません。ExecuteScalarメソッドの方は結果として値を返すのに対して、ExecuteNonQueryは値を返さないというのが唯一の違いです。
プログラムの中で特に目を引く違いはSQL文です。このようにデータベースプログラミングではSQL文が非常に大きな役割を果たしています。今回のSQL文は次のようになっています。
UPDATE T_目マスタ SET 説明= 'コウモリ, オオコウモリ' WHERE 目ID = 2 |
このSQL文の意味は「[T_目マスタ]テーブルで、目ID=2のレコードの説明を『コウモリ, オオコウモリ』にせよ」という意味です。
博士のワンポイントレッスン
|
上記の例は既に存在するレコードの値を書き換えるものでした。新しくレコードを追加するには次のようにします。
| Dim Cn
As
New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand SQLCm.CommandText = "INSERT INTO T_目マスタ VALUES (999, 'テスト', 'てすと', 1, 'これはテストです。')" Cn.Open() SQLCm.ExecuteNonQuery() Cn.Close() MsgBox("追加しました。") |
■リスト４：レコードを追加する
SQL分以外の部分が値を書き換える例と全く同じであるという点は注目に値します。
このコードを実行すると次のレコードが追加されます。
| 目ID | 目名 | 読み | 綱ID | 説明 |
| 999 | テスト | てすと | 1 | これはテストです。 |
なお、このコードを２回実行すると例外OleDbException(読み方：OleDBException = オーエルイーディービー エクセプション)が発生します。これは[目ID]=999であるレコードを２重に登録しようとしたためです。
■画像３：キーの重複エラー
前述したように[目ID]は[T_目マスタ]テーブルにおいてはレコードを識別するためのキーになっていますので、同じ値のレコードを追加できないわけです。キーの値を998などに変更すれば問題なく登録できます。
最後のレコードの削除方法も説明しておきます。これも上述の例でSQL文を変えるだけでできます。
| Dim Cn
As
New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand SQLCm.CommandText = "DELETE FROM T_目マスタ WHERE 目ID = 999 " Cn.Open() SQLCm.ExecuteNonQuery() Cn.Close() MsgBox("削除しました。") |
■リスト５：レコードを削除する
今回はレコードの取得、値の変更、新規追加、削除の方法を説明しました。この４つを組み合わせればほとんどのデータベースアプリケーションが作成できるはずです。
ただし、今回は基本的な事項の説明を目的としているために効率性に関してはまったく考慮していません。たとえば、今回紹介した方法でレコードの値を取得することを考えると、１つの値だけ取得してくるというのであれば大きな問題はありませんが、表を表示するように大量の値を取得するとなるとかなり非効率になります。
また、今回紹介したサンプルをご覧いただくとデータベースアプリケーションの中でSQL文が果たす役割の大きさがよくわかります。
SQLは奥が深く、SQLだけを解説している書籍なども売っているほどです。この講座でも基本的なSQL文を今後説明していくことになりますがとてもすべてを説明しきることはできないでしょう。
SQL文の中でも基本と呼ばれているのは次の４つのコマンドです。
| コマンド | 読み方 | 機能 |
| SELECT | セレクト | 値の取得 |
| UPDATE | アップデート | 値の変更 |
| INSERT | インサート | レコードの追加 |
| DELETE | デリート | レコードの削除 |
■表３：最も基本的で最も応用力のある４つのコマンド
今回すでにこの４つのコマンドが登場しており、この４つだけで通常必要なSQLの知識のほとんどをカバーすることができます。その代りこの４つにはいろいろな機能がありすべてを知ることは容易ではありません。私もすべてを知っているとは到底言えません。
また、SQL文はデータベースエンジンの種類によって異なります。基本的な部分では共通しているのですが各データベースエンジンによっていろいろと拡張されており、使用できるコマンドの種類や関数はもとより構文自体が異なることもあります。このあたりはデータベースを勉強する者の悩みの種です。
