| Visual Basic データベース講座 |
|
|
データをまとめて取得して表形式で表示する方法を説明します。さらに、変更点をデータベースに反映させる方法も説明します。
| 概要 ・DataTableを使用すると表形式で複数のデータをまとめて保持できる。 ・DataTableにデータを読み込むにはOleDbDataAdapterのFillメソッドを使用する。 ・DataTableの個々のレコードはバージョン管理されており、値を変更した後でも変更前の内容を取得することができる。 ・DataTableへの変更点をデータベースに反映させるには個々のレコードの値を基にSQL文を使用する。 |
前回は効率性の観点は無視してとにかくデータベースの読み書きが自由にできるようになるべく説明をしました。今回はこれに引き続いて効率性を考慮に入れていきます。
前回の方法では読み込む場合も書き込む場合も対象の値をピンポイントで指定していた点が最も効率が悪いと言えます。１回の処理で１つの値だけを対象にするようなまれなケースであれば前回の方法でもよいのですが、たいていの場合はある程度まとまったデータを扱うことになります。
今回はDataTableクラスを使用してまとまりとしてデータを扱う方法を説明します。そして、今回の説明を読んでいただければDataTableにはそれ以上のメリットがあることもお分かりいただけるでしょう。実際のところDataTableはVBにおけるデータベースプログラミングで中心となる最重要のクラスなのです。
前回紹介したプログラムをもう一度眺めてみます。
| 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) |
■リスト１：[T_目マスタ]テーブルから、目ID=2のレコードの[説明]を抽出する。
プログラムを実行するためにはプログラムの一番先頭にImports System.Data.OleDbと記述する必要がある点も忘れないでください。
このプログラムは[T_目マスタ]テーブルから値を１つだけ取得するプログラムです。「値が１つだけ」というのはレコードと列が指定されていることから保障されます。
ところで、SQLのSELECT文ではレコードを指定しないことも可能で、レコードを指定しない場合このプログラムは次のように書き換えられます。
| 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_目マスタ" Dim Value As String Cn.Open() Value = SQLCm.ExecuteScalar Cn.Close() MsgBox(Value) |
■リスト２：T_目マスタのすべてのレコードの[説明]を取得しようとするが文字列型で受けるので１つしか受け取れない。
こうすると、列は指定されていますがレコードは指定されていない状態になりますので、[T_目マスタ]テーブルの[説明]列のすべての値が選択対象となります。
それでもExecuteScalarメソッドは一番最初の値だけを処理するので結果として表示される値は１つだけです。複数の値をまとまりとして処理するにはExecuteScalarメソッドとは別の方法でデータを取得する必要があります。
次のようになります。
| '▼データ取得 Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand Dim Adapter As New OleDbDataAdapter(SQLCm) Dim Table As New DataTable SQLCm.CommandText = "SELECT 説明 FROM T_目マスタ" Adapter.Fill(Table) '▼値の表示 Dim Value As String '先頭の値を表示 Value = Table.Rows(0)("説明") MsgBox(Value) '20番目の値を表示 Value = Table.Rows(19)("説明") MsgBox(Value) '▼後処理 Table.Dispose() Adapter.Dispose() SQLCm.Dispose() Cn.Dispose() |
■リスト３：T_目マスタのすべてのレコードの[説明]を取得する。
今までのプログラムとは大分異なります。一番注目してほしいのはデータをまとまりとして保持するためにDataTableを使用している点です。普通の文字列型の変数では値を１つしか受け取れませんが、DataTableは「表」のようなイメージのクラスで、複数の値をまとめて取り扱うことができます。まさにデータベースからデータのまとまりを取得して保持するのに最適なクラスです。
DataTableをデータで満たすにはOleDbDataAdapterクラスのFillメソッドを使用します。Fillメソッドを使用したときにどのようなデータを取得するのかは前回と同様OleDbCommandクラスのCommandTextにSQL文の形式で指定します。
MsgBoxでは複数の 値を同時に表示するのが面倒なので、この例では値が正しく取得できている確認するために２つのMsgBoxを使用しました。
小さな違いとしてFillメソッドを使用する場合には接続を開いたり閉じたりしなくていい点にもちょっとだけ注目してください。ExecuteScalarやExecuteNonQueryの際には自分で接続を開いたり閉じたりしていましたがFillはこういった処理を自動でやってくれます。
このままでは値の確認に不便なので、値の一覧を見られるようにDataGridViewコントロールを使用しましょう。ツールボックスからDataGridViewを選択してフォームに貼り付けてください。VB.NET2002, VB.NET2003ではDataGridViewがないので代わりにDataGridを使用し、以下の説明では適宜DataGridViewをDataGridに読み替えてください。ただし、DataGridではサポートされていない機能もありますのでその場合は悪しからずご了承ください。
DataGridViewを配置したら上記のプログラムの値の表示部分を改造して次のようにします。
| '▼データ取得 Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand Dim Adapter As New OleDbDataAdapter(SQLCm) Dim Table As New DataTable SQLCm.CommandText = "SELECT 説明 FROM T_目マスタ" Adapter.Fill(Table) '▼値の表示 DataGridView1.DataSource = Table '▼後処理 Table.Dispose() Adapter.Dispose() SQLCm.Dispose() Cn.Dispose() |
■リスト４：T_目マスタのすべてのレコードの[説明]を取得・表示する。
これで実行すると今度はフォーム上のDataGridViewに取得したデータの一覧が表示されます。この一覧は修正・追加・削除が可能ですがいくら変更してもDataTableが保持している値が変更されるだけで実際のデータベースの値が変更されるわけではありません。
このことは初心者のうちはよく誤解している人がいるので注意してください。DataTableが記録しているレコードはすべてクライアントのメモリ上にあるだけで、いくらいじってもこのメモリの内容が書き換わるだけです。取得元のデータベースを更新するには更新用のプログラムを別に書く必要があります。そのプログラムは後半に紹介します。
ところで、 折角一覧表示しているのに[説明]列だけではさびしいのでSQLのSELECT文を改造してもっといろいろな列を同時に取得できるようにしましょう。いくつか例を示しますのでSQL文の練習のつもりで実際に実行してみてください。
次のように改造すると[目名]と[説明]の両方を取得できます。
SELECT 目名, 説明 FROM T_目マスタ |
■SQL１：T_目マスタの[目名]と[説明]を取得する。
次の用例では[目名]と[読み]と[説明]を取得できます。
SELECT 目名, 読み, 説明 FROM T_目マスタ |
■SQL２：T_目マスタの[目名]と[読み]と[説明]を取得する。
このようにSELECT文ではカンマでつなげることによって必要な列を複数記述することができます。列名はSELECT節に記述します。
次の例では[T_目マスタ]テーブルのすべての列を取得できます。
| SELECT * FROM T_目マスタ |
■SQL３：T_目マスタのすべてを取得する。
記号「*」(読み方：* = アスタリスク)を使用すると「すべての列」という意味になります。これは何かと重宝します。ここからの説明ではSQL文はこの最後のSELECT * FROM T_目マスタを使用していることを前提にします。
■画像１：すべて取得したときの画面
ここまで実際にやってみるとわかると思いますが、DataTableクラスとは結局のところテーブルそのものなのです。データベースからデータをまとめて持ってきてクライアントのメモリ上に新しいテーブルとしてデータを展開したものがDataTableなのです。
したがってDataTableから特定の値を抽出するためにはやはりDataTable内の行と列を指定します。これらもクラス化されていて行を表しているのはDataRowクラス、列を表しているのはDataColumnクラスです。特に重要なのは行をあらわすDataRowクラスです。DataTableの 実体は複数のDataRowで構成されていると考えることもできます。DataColumnクラスの方はそれほど出番は多くありません。
個々のDataRowにアクセスするにはDataTableのRowsプロパティを使用します。RowsプロパティはDataRowのコレクションになっていて任意のDataRowにアクセスすることができます。たとえば、３番目のDataRowを取得するには次のように書きます。
| Dim Row
As DataRow Row = Table.Rows(2) |
■リスト５：３番目のレコードを取得する。
そして、この行の[目名]の値を取得するには、次のように書きます。
| Dim Row
As DataRow Dim Value As String Row = Table.Rows(2) Value = Row("目名") |
■リスト６：３番目のレコードの[目名]を取得する。
ここで列はDataColumnクラスではなく文字列で指定しているのでDataColumnクラスの出番はDataRowクラスほど多くないわけです。
上記の例は次のように短く書くこともでき、この書き方のほうが広く使われているようです。
| Dim Value
As
String Value = Table.Rows(2)("目名") |
■リスト７：３番目のレコードの[目名]を取得する。
どの書き方でもループを回すことによってすべての列を処理できるという点は重要です。
列名も文字列ではなく番号でしていることもできます。次の例では3行目の5列目の値を取得します。
| Dim Value
As
String Value = Table.Rows(2)(4) |
■リスト８：列を数字で指定する。
行の数はDataTable.Rows.Countで取得できます。列の数はDataTalbe.Column.Countで取得できますし、DataTable.RowsもDataTable.Columnもそれぞれ行と列をあらわすコレクションですから簡単にすべてのデータを対象にしたループを記述することができます。
利用例として取得した内容をCSVファイルに書き込むプロうグラムを紹介します。この例ではレコードははFor Eachで、列はForでループさせています。
| Dim
Writer As New
IO.StreamWriter("C:\DataTableTest.csv") Dim LineText As String For Each Row As DataRow In Table.Rows LineText = "" For i As Integer = 0 To Table.Columns.Count - 1 If Len(LineText) > 0 Then LineText &= "," End If LineText &= Row(i) Next Writer.WriteLine(LineText) Next Writer.Close() Writer.Dispose() |
■リスト９：DataTableの内容をCSV形式で出力する。
ファイルに文字を書き込むプログラムならどの場合でも当てはまることですが、文字コードを指定しなかった場合VBではUTF-8でファイルに書き込みます。ShiftJISなどで書き込みたい場合はStreamWriterのコンストラクタで文字コードを指定してください。たとえば、次のようになります。
| Dim Writer As New IO.StreamWriter("C:\DataTableTest.csv", False, System.Text.Encoding.GetEncoding("Shift-Jis")) |
■リスト９−１．ShiftJISで書き込む場合
なお、対応していない文字コードをメモ帳など開くと文字化けして表示されます。新しいWindowsではUTF-8が標準で使用されています。
CSVではなくxml形式で出力するだけならWriteXmlメソッドがはじめから用意されているので簡単にできます。
| Table.TableName =
"T_目マスタ" Table.WriteXml("C:\DataTableTest.xml") |
■リスト１０：DataTableの内容をXML形式で出力する。
WriteXmlメソッドを使用するには事前にDataTableにテーブル名を設定する必要があります。テーブル名はコンストラクタで指定できるのですが、ここではプロパティで指定する例を紹介しました。
なお、一度DataGridViewにセットした後では次のようにしてDataGridViewからDataTableを取得することができるので、DataTable型の変数に広い適用範囲を持たせて使いまわす必要はありません。
| Dim Table
As DataTable Table = DirectCast(DataGridView1.DataSource, DataTable) |
■リスト１１：DataGridViewのDataSourceからDataTableを取得する。
このようにしてDataGridViewに表示されている内容を自在に制御することもまったく簡単にできるわけです。
今度はDataTableを使ったデータベースの更新を説明しましょう。DataTable内の値を書き換えること 自体は特に説明は必要ないでしょう。連結されたDataGridViewを使ってユーザーの操作で値を書き換えることもできますし、プログラムを使って書き換えることもできます。
次の例はDataTableの12行目の説明を書き換えます。ここではDataTableはDataGridViewから取得しています。
| Dim Table
As DataTable
= DirectCast(DataGridView1.DataSource,
DataTable) Dim Row As DataRow Row = Table.Rows(11) Row("説明") = "イワダヌキ, ハイラックス" |
■リスト１２：１２行目のレコードの値を書き換える。データベースは更新されない。
もちろん、この例は次のように簡略化して書くこともできます。
| Dim Table
As DataTable
= DirectCast(DataGridView1.DataSource,
DataTable) Table.Rows(11)("説明") = "イワダヌキ, ハイラックス" |
■リスト１３：１２行目のレコードの値を書き換える。データベースは更新されない。
さらに簡略化して１行で書くこともできます。実際に何行で書くかは好みと可読性を考慮して決めてください。私はここでは各クラスの働きがわかるようにあまり簡略化しないで書いていますが、実際にプログラムするときにはもっと簡単に書いています。
DataTableから行を削除するにはDeleteメソッドを使用します。
| Dim Table
As DataTable
= DirectCast(DataGridView1.DataSource,
DataTable) Dim Row As DataRow Row = Table.Rows(11) Row.Delete() |
■リスト１４：１２番目のレコードを削除する。データベースは更新されない。
DataTable.Rows.Removeメソッドも日本語上は「行を削除する」機能がありますが、上述のDeleteメソッドとは明らかに働きが異なります。すぐ後で説明しますがDeleteメソッドは実際には「削除された」というしるしをつけるだけでDataRowクラス自体は消滅しないのですが、DataTable.Rows.RemoveメソッドのほうはDataRowクラス自体を消してしまいます。データベース更新のための削除にはDeleteの方が都合がよいです。
レコードの新規追加は少し手順が必要です。まず、テーブルと同じ構造をもつDataRowを作成します。この段階ではこの新しいDataRowは独立していてどのテーブルにも属していません。２つ目のステップでこのDataRowをDataTableに含めます。
次のようになります。
| Dim Table
As DataTable
= DirectCast(DataGridView1.DataSource,
DataTable) Dim Row As DataRow '同じ構造をもつ独立したレコードを作成 Row = Table.NewRow 'レコードに値を設定 Row("目ID") = 900 Row("目名") = "獣脚目" Row("読み") = "ジュウキャクモク" Row("綱ID") = "4" '爬虫類 Row("説明") = "ティラノサウルス, アロサウルス, ディノニクス" 'レコードをDataTbleに含める Table.Rows.Add(Row) |
■リスト１５：新規レコードを追加する。データベースは更新されない。
これでDataTableに対するレコードの修正、削除、新規追加ができるようになりました。どれもごく簡単にできることが理解いただけたでしょう。しかし、先にも説明したようにDataTableに対する変更はあくまでもクライアントのメモリ上の変更に過ぎず、DataTableを変更したからと言ってそのもととなっているデータベースのテーブルの内容が変更されるということは一切ありません。
次の節ではDataTableの変更をデータベースに反映させる方法を説明します。
レコードをあらわすDataRowクラスは常に最初の状態を保存していますし、そのレコードに対してどのような編集が加えられたかも記録しています。ですから、削除されたレコードは削除するようにデータベースに指示できますし、更新したレコードは更新するように指示することもできるわけです。この仕組みを順を追ってみていきましょう。
まず、レコードを編集した後で編集前の値を取得し、そのレコードが編集されたことを示す例を紹介します。この例の半分は今まででてきた例を組み合わせたものですが全体としては示唆に富みます。
| Private
Sub Form1_Load(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs) Handles
MyBase.Load '▼データ取得 Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand Dim Adapter As New OleDbDataAdapter(SQLCm) Dim Table As New DataTable SQLCm.CommandText = "SELECT * FROM T_目マスタ" Adapter.Fill(Table) '▼値の表示 DataGridView1.DataSource = Table '▼後処理 Table.Dispose() Adapter.Dispose() SQLCm.Dispose() Cn.Dispose() End Sub |
|
Private
Sub
Button1_Click(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs) Handles
Button1.Click 'レコードを編集する Dim Table As DataTable = DirectCast(DataGridView1.DataSource, DataTable) Dim Row As DataRow Row = Table.Rows(11) Row("説明") = "イワダヌキ, ハイラックス" End Sub |
|
Private
Sub
Button2_Click(ByVal
sender As
System.Object, ByVal
e As
System.EventArgs) Handles
Button2.Click Dim Table As DataTable = DirectCast(DataGridView1.DataSource, DataTable) Dim Row As DataRow = Table.Rows(11) Dim OldValue As String '編集前の値 Dim NewValue As String '編集後の値 'レコードが編集されているか判定する If Row.RowState <> DataRowState.Modified Then MsgBox("このレコードは編集されていません。") Return End If OldValue = Row("説明", DataRowVersion.Original) NewValue = Row("説明") MsgBox("編集前：" & OldValue & vbNewLine & "編集後：" & NewValue) End Sub |
■リスト１６：変更したレコードの変更前の値と編集後の値を取得・表示する
フォームの表示時にはすでに[T_目マスタ]テーブルの値が表示されています。この状態でButton2をクリックすると「レコードは編集されていません。」と表示されます。この例では説明を簡単にするために常に１２番目のレコードだけを対象にしている点にも注意してください。１２番目のレコードは標準の状態では「岩狸目(イワダヌキ)」です。
DataGridViewを直接編集して岩狸目の説明を何か別のものに変更してからButton2をクリックすると今度は編集前の値と編集後の値が対比して表示 されます。Button1を利用してプログラムから値を変更した後でButton2をクリックしても同様です。
このプログラムには２つのポイントがあります。まず、レコードに対してどのような編集が行われたか、または行われていないかを示すDataRow.RowStateプロパティです。RowStateプロパティの値は次のどれかでありレコードに対する編集状態を取得することができます。
| 値 | 読み方 | 意味 |
| Added | アディド | 新規追加されたレコード |
| Deleted | デリーテッド | 削除されたレコード |
| Detached | デタッチド | テーブルに属していないレコード |
| Modified | モディファイド | 修正されたレコード |
| Unchanged | アンチェンジド | 変更されていないレコード |
上記の例ではIf Row.RowState <> DataRowState.Modified Thenというコードでレコードが編集されているかされていないかを識別しています。もちろんこの識別方法は不十分で、これでは新規追加されたレコードや削除されたレコードを検出できません。
もう１つのポイントはDataRowのバージョン管理機能 です。編集前の値と編集後の値の両方を取得できるのはこの機能によるものです。
普通に Value = Row("説明") と記述した場合は現在の値が取得できるだけですが、第２引数でバージョンを指定することにより編集前の値などを取得できます。なお、Row("説明")という書き方はRow.Item("説明")の省略形なのでこの機能のヘルプを探すときはItemプロパティを見てみてください。
指定できるバージョンは次のとおりです。
| 値 | 読み方 | 意味 |
| Current | カレント | 現在の値 |
| Default | デフォルト | 通常はCurrentと同じ。RowStateがDetachedの場合はProposedと同じ。 |
| Original | オリジナル | 編集前の値 |
| Proposed | プロポーズドゥッ | 編集中の値 |
バージョンの指定を省略した場合はDefaultが指定されたものとみなされます。
これらのバージョンの値はいつでも使用できるわけではありません。たとえば、削除されたレコードに対してCurrentやDefaultを取得しようとするとバージョンが存在しないため例外VersionNotFoundExceptionが発生します。Proposedはもっと限定的なシーンでのみ利用可能です。DataRowのHasVersionメソッドを使用すると 特定のバージョンが存在するか調べることができます。
修正・新規追加などの状態を一旦確定させるためにDataTableにはAcceptChangesメソッドも用意されています。AcceptChangesを呼び出すと、それぞれのレコードに加えられた変更はそのまま温存しますが、すべてのDataRowのRowStateプロパティはUnchangedになり、その時の値がOriginalバージョンの値になります。特定のレコードだけUnchangedにするためにDataRowにもAcceptChangesメソッドが用意されています。
AcceptChangesが各RowStateに及ぼす影響を表にまとめておきます。
| AcceptChanges前 | AcceptChanges後 |
| Added | Unchanged |
| Deleted | (削除される) |
| Modified | Unchanged |
データベースへの更新後に引き続き編集を行う可能性がある場合などは更新時点でAcceptChangesメソッドを呼び出しておくことで以降の更新も最初の更新と同じロジックで処理することができるようになります。もし、AccesptChangesメソッドを使用しないとしたら1回目の更新対象となったレコードは2回目の更新対象からは省くという処理が必要になりかなり大変です。もっともこれは一度の呼び出しで何度も更新する可能性がある場合の話で、更新後にすぐに画面を閉じたり、更新後にすぐに新たなDataTableを取得するなどの仕様であれば特に気にする必要はありません。
メモ：AcceptChangesが実際に役に立つシーンはリスト１８の後で紹介します。
さて、レコードが編集されたかされていないか、レコードの編集前の値は何か、これら２点が取得できるようになったとしてDataTableの内容をデータベースに書き込むのに何の役に立つのでしょうか。
データベースの更新を行うときは前回も登場したようにSQL文を使用します。修正を意味するUPDATE、新規追加を意味するINSERT、削除を意味するDELETEです。
これで気がつかれたと思いますが、RowStateプロパティに応じて実行するSQL文を分岐させるわけです。
先ほどから登場している１２番目のレコードである「岩狸目」だけに限定するとプログラムは次のようになります。岩狸目を修正・削除してこのコードを実行するとデータベースに修正・削除の内容が反映されるので実行の際には注意してください。
| Dim
Table As DataTable =
DirectCast(DataGridView1.DataSource, DataTable) Dim Row As DataRow Dim SQL As String = "" Row = Table.Rows(11) '●SQL文の生成 Select Case Row.RowState Case DataRowState.Added '▼新規追加されたレコードの場合 SQL = "INSERT INTO T_目マスタ VALUES (" SQL &= Row("目ID") & ", " '目ID SQL &= "'" & Row("目名") & "', " '目名 SQL &= "'" & Row("読み") & "', " '読み SQL &= Row("綱ID") & ", " '綱ID SQL &= "'" & Row("説明") & "' " '説明 SQL &= ")" Case DataRowState.Deleted '▼削除されたレコードの場合 SQL = "DELETE FROM T_目マスタ WHERE " SQL &= " 目ID = " & Row("目ID", DataRowVersion.Original) Case DataRowState.Modified '▼修正されたレコードの場合 SQL = "UPDATE T_目マスタ SET " SQL &= " 目ID = " & Row("目ID") & ", " SQL &= " 目名 = '" & Row("目名") & "', " SQL &= " 読み = '" & Row("読み") & "', " SQL &= " 綱ID = " & Row("綱ID") & ", " SQL &= " 説明 = '" & Row("説明") & "' " SQL &= " WHERE " SQL &= " 目ID = " & Row("目ID", DataRowVersion.Original) Case Else Return End Select '●更新実行 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 = SQL Cn.Open() SQLCm.ExecuteNonQuery() Cn.Close() Table.Dispose() SQLCm.Dispose() Cn.Dispose() |
■リスト１７：１２番目のレコードへの修正・削除をデータベースに反映させる。
「岩狸目」はすでに存在するので新規追加というのはあり得ないのですが参考のために載せておきました。これで、DataGridViewを使って岩狸目を修正、削除してこのコードを実行すると実際にデータベースにその内容が反映されます。
| 注意 - DELETEとUPDATE SQL文ではWHERE句を使って対象のレコードを指定します。WHERE句を省略した場合はそのテーブル内にある全レコードが対象となります。 そのためWHERE句を間違ったり指定しないでDELETE文を実行するとすべてのレコードが削除されてしまう場合があります。UPDATE文でもすべてのレコードが更新されてしまう場合があり元のデータを復元できないという点ではDELETEしたのと変わりありません。 DELETEとUPDATEを使用する際は常にWHERE句の条件に気をつけて意図しないレコードまで対象に含まないように注意してください。 |
岩狸目だけでなくすべてのレコードを対象にしたい場合は上記のコードをループ内に入れます。次のようになります。
| Dim
Cn As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand Dim Table As DataTable = DirectCast(DataGridView1.DataSource, DataTable) For Each Row As DataRow In Table.Rows Dim SQL As String = "" '●SQL文の生成 Select Case Row.RowState Case DataRowState.Added '▼新規追加されたレコードの場合 SQL = "INSERT INTO T_目マスタ VALUES (" SQL &= Row("目ID") & ", " '目ID SQL &= "'" & Row("目名") & "', " '目名 SQL &= "'" & Row("読み") & "', " '読み SQL &= Row("綱ID") & ", " '綱ID SQL &= "'" & Row("説明") & "' " '説明 SQL &= ")" Case DataRowState.Deleted '▼削除されたレコードの場合 SQL = "DELETE FROM T_目マスタ WHERE " SQL &= " 目ID = " & Row("目ID", DataRowVersion.Original) Case DataRowState.Modified '▼修正されたレコードの場合 SQL = "UPDATE T_目マスタ SET " SQL &= " 目ID = " & Row("目ID") & ", " SQL &= " 目名 = '" & Row("目名") & "', " SQL &= " 読み = '" & Row("読み") & "', " SQL &= " 綱ID = " & Row("綱ID") & ", " SQL &= " 説明 = '" & Row("説明") & "' " SQL &= " WHERE " SQL &= " 目ID = " & Row("目ID", DataRowVersion.Original) Case Else Continue For End Select '●更新実行 SQLCm.CommandText = Sql Cn.Open() SQLCm.ExecuteNonQuery() Cn.Close() Next Table.Dispose() SQLCm.Dispose() Cn.Dispose() |
■リスト１８：すべてのレコードへの変更点をデータベースに反映させる。
このコードをFormClosingイベントなどに記述した場合は問題ないのですが、ButtonのClickイベントなどに書いた場合には１つ考慮すべきことがあります。新しくレコードを追加した状態でデータベースへの更新を複数回実行するとエラーが発生するのです。
エラーの原因はレコードのキーの重複です。つまり、[T_目マスタ]テーブルでは同じ目IDのレコードは１つしか登録できないので、2つ以上登録しようとするとエラーになるのです。
たとえば新しく目IDが1000のレコードを追加したとしましょう。一度目の更新ではこのレコードはうまくデータベースに登録されます。目ID=1000のデータがはじめは存在しないからです。
次に、画面を閉じないでそのまま目IDが1001のレコードを追加したとします。そして、もう一度更新を実行すると今度はエラーになってしまいます。2回目の更新では目ID=1000のレコードと目ID=1001のレコードの両方を追加しようとするからです。1001の方は問題ないのですが、1000の方は先ほど自分で登録しているのでエラーになります。
1回目の更新直前の状態
|
2回目の更新直前の状態
|
||||||||||||||
| →更新処理では目ID = 1000のレコードを新規追加しようとする。 | →更新処理では目ID = 1000のレコードと目ID = 1001のレコードを新規追加しようとする。目ID = 1000のレコードは1回目の更新で追加されているので目IDの重複でエラーになる。 |
このケースで、なぜ2回目も目ID=1000のレコードを登録しようとするのかはRowStateを考えればすぐにわかります。目ID=1000のレコードのRowStateがAddedだからです。1回目にデータベースに登録したからと言って自動的にRowStateがAddedからUnchangedに変わることはありません。ですから自分でプログラムして登録が完了したレコードのRowStateをUnchangedに戻す必要があります。
2回目の更新直前のあるべき状態
|
||||||||
| →1回目の更新が完了した時点で目ID = 1000のレコードのRowStateはUnchangesになるべきである。 |
と言ってもRowStateプロパティは読み取り専用なので直接値を変更することはできません。RowStateをUnchangedに戻すにはDataRowのAcceptChangesメソッドを使用するか、DataTableのAcceptChangesメソッドを使用します。DataRowのAcceptChangesメソッドとはそのレコードのRowStateをUnchangedに戻ります。DataTableのAcceptChangesはそのDataTableに属するすべてのレコードのRowStateをUnchangedに戻します。
たいていの場合すべてのレコードをUnchangedにするだけで用は足ります。上述の例では最後の方にあるTable.Disposeの1行つ上にTable.AcceptChangesと記述すればこの問題は回避できます。
| 失われた岩狸を求めて...
- レコードの並び順は不定である 上の例をいろいろと試していると興味深い現象に遭遇する場合があります。たとえば、はじめは１２番目に表示されている岩狸目ですが、いったんこれを削除してみます。
■画像２：岩狸目は１２番目にある。Deleteキーを押してこれを削除する。 そして、一番下に新しく岩狸を登録しなおしましょう。
■画像３：岩狸目を最後の行に追加。 これでいったんプログラムを終了させて、もう一度プログラムを起動すると追加したはずの岩狸が１２番目に表示されていないではないですか。一番下にもありません。どこへ行ったのかと探してみると変な所にまぎれています。
■画像４：再度プログラムを実行させると、岩狸目は奇天烈なところに…。 この現象は同じように操作しても発生しない場合もあります。 すべてのレコードは一見したところ目ID順にならんでいますが、実はそうではないということがこの現象からわかります。しかも目IDはこのテーブルの唯一のキーなのにその順番に並んでいないのです。このことはある程度データベースプログラムの経験がある人でも見落としている場合があります。 並び順を指定しなかった場合取得したレコードは予測不可能な順番で並んでいるということを覚えておいてください。 なお、並び順を指定する場合はSQL文でORDER BYを使用します。たとえば、次のとおりです。 SELECT * FROM T_目マスタ ORDER BY 目ID SQL Serverの場合は並び順を指定しなかった場合はクラスタードインデックス(つまり主キー)の順に自動的に整列します。つまり、このあたりの仕様はデータベースによって異なります。そういったことをこまごまと気にするのは大変なので順番が重要な場合はORDER BYを指定する癖をつけておきましょう。 |
これでDataTableの内容を自由自在にデータベースに反映できるようになりましたが、ちょっとコードの量が多いと感じられたのではないでしょうか。楽をする方法は色々とありますが、基本的には上記のコードはちゃんと理解していつでも自分で書けるくらいになっておかないとこの先苦労することになるかもしれません。
とは言え、楽をする方法も説明しておきましょう。その方法とはOleDbDataAdapterを使用することです。OleDbDataAdapterはDataTableを取得する際に使用していますが、実は逆の流れ、つまりDataTableの内容をデータベースに書き込むときにも使用できるのです。
OleDbDataAdapterを使用すると、次のようにしてデータベースを更新することができます。
|
Imports System.Data.OleDb Public Class Form1 Dim Adapter As OleDbDataAdapter Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load '▼データ取得 Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database\Animals.mdb") Dim SQLCm As OleDbCommand = Cn.CreateCommand Adapter = New OleDbDataAdapter(SQLCm) Dim Builder As New OleDbCommandBuilder(Adapter) Dim Table As New DataTable SQLCm.CommandText = "SELECT * FROM T_目マスタ" Adapter.Fill(Table) '▼値の表示 DataGridView1.DataSource = Table '▼後処理 Table.Dispose() End Sub |
|
Private Sub Button1_Click(ByVal
sender As System.Object,
ByVal e As
System.EventArgs) Handles Button1.Click Adapter.Update(DataGridView1.DataSource) End Sub End Class |
■リスト１９：OleDbDataAdapterを利用して簡単にデータベースを更新する。
はじめのデータを取得するところが少し変わっていますが、データを更新するところがなんとたったの１行になっています。これは驚くべきことです。
これを見てOleDbDataAdapter.Updateを使えば簡単にデータベースを更新できるのかと思ったらそれはちょっと違います。
仕組みを簡単に説明しましょう。OleDbDataAdapterはデータを取得するときに使用するSQLをSelectCommandプロパティに記録していて、Fillメソッドが呼び出された時にはこのSQL文が動作します。同様にUpdateメソッドが発行された時には修正用のUpdateCommandプロパティ、新規追加用のInsertCommandプロパティ、削除用のDeleteCommandプロパティにあらかじめセットしておいたSQLが自動的に作動します。ここでいうSQLは文字列のSQL文ではなくOleDbCommandクラスのことです。
自分でループ処理をしなくても全レコードを処理してくれる点はありがたいのですが結局のところそれぞれのシーンに応じたSQLをあらかじめセットしておく必要があるのです。
しかし、上述のプログラムではどこでもそれらしいSQL文をセットしているところが見当たりません。OleDbDataAdapterクラスのコンストラクタに指定しているSQLCmはSelectCommandの値となりますが、UpdateCommand等の指定にはなりません。これはどういうことかというと今回はSelectCommandに設定しているSQLが"SELECT * FROM T_目マスタ"というかなりシンプルなSQL文であるために、残りのSQLは自動生成可能なのです。
SQLを自動生成するのはOleDbCommandBuilderクラスで、このクラスはOleDbDataAdapterクラスと関連付けておくとあとは自動的にSQLを生成してくれます。関連付けているのはDim Builder As New OleDbCommandBuilder(Adapter)の部分で、これだけ書いておけばあとは余計なメソッドを呼び出したりプロパティを設定しなくても各SQL文が自動的に生成されます。
この自動生成機能が広範囲にわたっていれば結構楽ができるのですが、SelectCommandが一定以上に複雑な場合自動生成機能が利用できず、結局自分でSQL文を書くことになります。
それに実際にデータベースアプリケーションを作りだすと機械的に更新すればよいというわけではない場面にはたくさん出会うことになるでしょう。だからどのような場合にも対応できるように自分でループを回してSQL文を実行する方法を使いこなせるようにしておくべきなのです。
