２− ７　MｙSQLテーブルの追加・更新をする（その ３）

　コマンドを利用したデータ処理概要MySQLテーブルの追加・更新も既に２回 行っているが、今回はまとめとしてデータファイルからデータを読み込み、その内容でテーブルを更新する処理を行ってみる。今までは画面からの更新を説明したが、 実際問題として必要なのは　テキストファイルを用いたテーブルの更新だと想像する。参照系においては　画面からの処理の必要性は理解できるが、更新系の場合に　一件づつ画面から更新する事はそう多くはないのではないかと思うからである。
　今までの処理の延長なので　今回特別な画面は準備せず、メニューから処理を起動するようにしている。テキストファイルからのデータ読み込みはStreamReaderを利用する。 注意すべきはStreamReaderのコード体系の既定値がUTF8である点で　元のテキストファイルもUTF8形式で作成されている必要があるという部分だ。元のテキストファイルをSJISで作成し、エンコードをASCIIにして読み込む事も可能である。多分、このやり方でデータベースがSQLサーバーならうまく動作するのだろうが、相手はMySQLなので　SJISのデータを使用する避けなければならない。ファイルはUTF８にて作成するのが無難だろう。（なお、言わずもがなな事であるが、StreamReaderを利用する場合は　Imports system.ioを最初に指定しておく必要がある）。メニューのイメージ

１．コマンドを利用して　テキストフアィルのデータを追加・更新
　上に処理の概念を記載しておきましたが、ここで行う処理は　テキストファイルの行を一行づつ読み込み、店コードのデータがMySQLのテーブルに存在するかどうか確認。対象の店が存在するなら　テーブルを更新し　店が存在していなかったら追加するというシンプルな内容だ。これを　テキストファイルの行が無くなるまで繰り返すわけだ。
　処理自体は前回行ったコマンドによるデータの追加・更新をテキストの各行分行うので　コードはほとんど変更せずに使う事が可能である。

Private Sub コマンドテストCToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles コマンドテストCToolStripMenuItem.Click
Dim sqlCont As New MySql.Data.MySqlClient.MySqlConnection 'SQL接続
Dim sqlCMDS As MySql.Data.MySqlClient.MySqlCommand 'コマンド
Dim sqlCMDU As MySql.Data.MySqlClient.MySqlCommand 'コマンド
Dim sqlCMDI As MySql.Data.MySqlClient.MySqlCommand 'コマンド
Dim sqlDr As MySql.Data.MySqlClient.MySqlDataReader 'データリーダー
Dim MyconString As String = "server=localhost;user id=hanbai;password=password;database=tanpin;"
Dim I As Long, J1 As Long, J2 As Long 'カウンター
Dim IIT As DateTime, IIE As DateTime
Dim Iifile As StreamReader '入力ファイル
Dim Iinstr As String, Iidata() As String
'
IIT = Now()
'接続文字列を設定
sqlCont.ConnectionString = MyconString
Iifile = File.OpenText("d:\nettest\sqlget\ﾃｽﾄ用.txt")
Try
'MAK Select command
sqlCMDS = sqlCont.CreateCommand
sqlCMDS.CommandText = "select tencode, tenname from tentbl where tencode = ?tencode"
Dim MySParm = sqlCMDS.Parameters.AddWithValue("?tencode", MySqlDbType.Int16)
'Mak Update command
sqlCMDU = sqlCont.CreateCommand
sqlCMDU.CommandText = "UPDATE tentbl SET tenname = ?tenname " & _
"WHERE tencode = ?tencode"
sqlCMDU.Parameters.Add(New MySqlParameter("?tencode", MySqlDbType.Int16))
sqlCMDU.Parameters.Add(New MySqlParameter("?tenname", MySqlDbType.VarString))
'Mak Insert command
sqlCMDI = sqlCont.CreateCommand
sqlCMDI.CommandText = "INSERT INTO tentbl ( tencode, tenname) VALUES ( ?tencode, ?tenname)"
sqlCMDI.Parameters.Add(New MySqlParameter("?tencode", MySqlDbType.Int16))
sqlCMDI.Parameters.Add(New MySqlParameter("?tenname", MySqlDbType.VarString))
'ここからデータ読み込み
While Iifile.Peek > -1
Iinstr = Iifile.ReadLine
Iidata = Split(Iinstr, ",")
Try
'接続を作成して
sqlCont.Open()
'パラメータ値を指定して
MySParm.Value = Iidata(0)
'検索結果を確認する。
sqlDr = sqlCMDS.ExecuteReader
I = 0
While (sqlDr.Read())
I = I + 1
End While
sqlCont.Close()
'接続を作成して
sqlCont.Open()
Select Case I
Case 0
sqlCMDI.Parameters("?tencode").Value = Iidata(0)
sqlCMDI.Parameters("?tenname").Value = Iidata(1)
'内容を追加します。
sqlCMDI.ExecuteNonQuery()
J1 = J1 + 1
Case Else
sqlCMDU.Parameters("?tencode").Value = Iidata(0)
sqlCMDU.Parameters("?tenname").Value = Iidata(1)
'内容を更新します。
sqlCMDU.ExecuteNonQuery()
J2 = J2 + 1
End Select
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
sqlCont.Close()
End Try
End While
'データ読み込みここまで
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
Iifile.Close()
End Try
'
IIE = Now
Dim interval As TimeSpan = IIE.Subtract(IIT)
MessageBox.Show(interval.Milliseconds & " 追加= " & Str(J1) & "件更新=" & Str(J2) & "件")
End Sub使用したテキストファイル

　行っている内容についての説明は前回の内容を参照してもらえば良い。違いは今までテキストボックスに入力した値に従い更新・追加を行っていたのが　入力元がテキストファイルになった点だけである。なお　参考の為にどの程度時間がかかったかを調べるコードを追加してある。私の使用しているテスト環境はペン３のWindows2000のPCでメモリー２５６Mというかなり非力なハードであるが、２３行のレコードに対して４件追加、４件更新で実施した場合に　３７０ミリ秒要している。この必要時間は毎回変わってしまうので正確ではないおよその目安である。なお、終了せず、同じコードを実行した場合は　ほぼ毎回４０ミリ秒かかっている。この時は全件更新となるが　時間が毎回同じ程度というのは興味深い。

２．DataSetを利用して　テキストフアィルのデータを追加・更新
　続いて、DataAdapterを作成しておき、テキストファイルからデータを読み込みDataSetを作成し　この内容に従ってコマンドを自動作成させてテーブルを更新する処理を行う。
　簡単に　前回行った処理をそのまま利用し　上に示したように入力元をテキストファイルの行データにするように書き換えたコードの核心部分は以下のようになる。

'ここからデータ読み込み
While Iifile.Peek > -1
Iinstr = Iifile.ReadLine
Iidata = Split(Iinstr, ",")
Try
'パラメータ値を指定して
MyParm.Value = Iidata(0)
'検索結果を確認する。
sqlDA.Fill(resultDS, "tentbl")
'Dataset内のテーブル"tentbl"の件数を確認
I = resultDS.Tables("tentbl").Rows.Count
Select Case I
Case 0
oDR = resultDS.Tables("tentbl").NewRow()
oDR.BeginEdit()
oDR("tencode") = Iidata(0)
oDR("tenname") = Iidata(1)
oDR.EndEdit()
resultDS.Tables("tentbl").Rows.Add(oDR)
J1 = J1 + 1
Case Else
'テーブル"tentbl"の先頭値に対する更新処理
resultDS.Tables("tentbl").Rows(0).Item("tenname") = Iidata(1)
J2 = J2 + 1
End Select
Try
'Update文の自動実行のためにCommandBuilderオブジェクトを作成する
sqlCmdBldr = New MySqlCommandBuilder(sqlDA)
'DataSetに対する更新結果をDBに反映
sqlDA.Update(resultDS, "tentbl")
'MessageBox.Show("レコードは更新されました")
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
End While

　一見して全く問題がないように見える。テキストファイルを読み込み　その内容に従ってデータセットを作成し　その件数を確認。対象レコードがなければデータセットに行を追加、あれば更新。最後にこのデータセットからテーブルを更新するコマンドを作成して　テーブルを更新。でも、このコードでは追加も更新も行われなかった。何故か？
　どうも．NETのDataAdapterを使う場合、データがキャッシュされてメモリーに残ってしまうようだ。従って、テキストファイルを読み込む度に「DataSet作成−DataSet更新−Command作成−Updateの実行」を行なおうとしても駄目なのである。画面から１件処理して終わり、また１件処理して終わり、のような画面からの更新の処理と　一気にデータを更新してしまう処理が同じコードでは動作しない。ここは、根本的に直す必要がありそうだ。

索引つきのDataSetを用いる３．DataSetを利用して　 テキストフアィルのデータを追加・更新２
　今度は、考え方を変えて、初めに全データを含むDataSetを作成し（右の図ではDataAdapterとなっているがこれは誤り）このDataSetに対して追加・更新を行い　テキストファイルのデータが無くなるまで続けたら　DataSetをコマンドを作成してテーブルに戻してやるようにするようにした。この場合に必要のは、DataSetに索引を作ることだ。やり方は簡単で、Fillコマンドを実行する前にDataAdapterに対してMissingSchemaActionを行うのである。Microsoftの説明では「スキーマ情報をDataSetに追加する」となっている。具体的なコードは以下のようになる。

Private Sub 自動生成ﾃｽﾄJToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles 自動生成ﾃｽﾄJToolStripMenuItem.Click
Dim sqlDA As MySql.Data.MySqlClient.MySqlDataAdapter 'データアダプタ作成
Dim sqlCmdBldr As MySqlCommandBuilder 'SQLコマンド作成
Dim resultDS As DataSet 'データセットオブジェクト
Dim oDR As DataRow 'データオブジェクト
Dim MyconString As String 'MySQL接続文字列
Dim J1 As Long, J2 As Long 'カウンター
Dim IIT As DateTime, IIE As DateTime
Dim Iifile As StreamReader '入力ファイル
Dim Iinstr As String, Iidata() As String
'
IIT = Now()
'DataAdapterオブジェクトの作成
sqlDA = New MySql.Data.MySqlClient.MySqlDataAdapter
'DataSetオブジェクトの作成
resultDS = New DataSet()
'MySQL接続文字列
MyconString = "server=localhost;user id=hanbai;password=password;database=tanpin;"
Iifile = File.OpenText("d:\nettest\sqlget\ﾃｽﾄ用.txt")
Try
'接続を作成します
sqlDA.SelectCommand = New MySqlCommand()
sqlDA.SelectCommand.CommandType = CommandType.Text
sqlDA.SelectCommand.CommandText = "select * from tentbl"
sqlDA.SelectCommand.Connection = New MySqlConnection(MyconString)
'スキーマ情報をDatasetに追加する
sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
'検索結果を確認する。
sqlDA.Fill(resultDS, "tentbl")
While Iifile.Peek > -1
Iinstr = Iifile.ReadLine
Iidata = Split(Iinstr, ",")
Try
oDR = resultDS.Tables("tentbl").Rows.Find(Iidata(0))
If oDR Is Nothing Then
oDR = resultDS.Tables("tentbl").NewRow()
oDR("tencode") = Iidata(0)
oDR("tenname") = Iidata(1)
resultDS.Tables("tentbl").Rows.Add(oDR)
J1 = J1 + 1
Else
'テーブル"tentbl"の先頭値に対する更新処理
oDR("tenname") = Iidata(1)
J2 = J2 + 1
End If
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
End While
'データ処理がおわったら元テーブルを更新
Try
'Update文の自動実行のためにCommandBuilderオブジェクトを作成する
sqlCmdBldr = New MySqlCommandBuilder(sqlDA)
'DataSetに対する更新結果をDBに反映
sqlDA.Update(resultDS, "tentbl")
'MessageBox.Show("レコードは更新されました")
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
Iifile.Close()
End Try
'
IIE = Now
Dim interval As TimeSpan = IIE.Subtract(IIT)
MessageBox.Show(interval.Milliseconds & " 追加= " & Str(J1) & "件更新=" & Str(J2) & "件")
End Sub

　DataAdapterのMissingSchemaActionに MissingSchemaAction.AddWithKeyを指定する。このコードを指定してあるとFillコマンドによってDatasetを作成した際に　DataSetに索引が作成されるのでFindによって店コードに対するDatarowを取得できる。この内容を更新してやるのだ。このようにしてDataSetとテキストファイル間でデータ処理を行ってから　内容を元のMySQLテーブルに戻してやるようになる。コード自体は１件処理するのとそう変わりは無いがこのコードのような形でDataSetを作成する方法が簡単に実現できるので　こういった処理には使えそうである。
　なお、上のコマンドによる処理同様時間を計測すると、３８０ミリ秒かかっている。これを終了させずに何度も続けて行うと要する時間は１０ミリ秒から９０ミリ秒と毎回違った値が出た。続けて行うという事は　全件更新となるわけだが、前にも書いたようにDataAdapterやDataSetの内容がキャッシュされている事が原因で時間が不定なのかもしれない。

４．DataSetを利用。データ追加・更新は直接コマンドで行う。
　次に、上の内容と同様、作成したDataSetからFindによってDataRowを作成。この内容によって作成してあったコマンドから直接MySQLのテーブルを更新してしまおうという処理を作成してテストしてみた。つまり、１番と３番を混合させてような形だ。コードは以下のようにしている。

Private Sub 共用テストOToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles 共用テストOToolStripMenuItem.Click
Dim sqlDA As MySql.Data.MySqlClient.MySqlDataAdapter 'データアダプタ作成
Dim resultDS As DataSet 'データセットオブジェクト
Dim sqlCont As New MySql.Data.MySqlClient.MySqlConnection 'SQL接続
Dim sqlCMDU As MySql.Data.MySqlClient.MySqlCommand 'コマンド
Dim sqlCMDI As MySql.Data.MySqlClient.MySqlCommand 'コマンド
Dim oDR As DataRow 'データオブジェクト
'DataAdapterオブジェクトの作成
sqlDA = New MySql.Data.MySqlClient.MySqlDataAdapter
'DataSetオブジェクトの作成
resultDS = New DataSet()
'MySQL接続文字列
Dim MyconString As String = "server=localhost;user id=hanbai;password=password;database=tanpin;"
Dim J1 As Long, J2 As Long 'カウンター
Dim IIT As DateTime, IIE As DateTime
Dim Iifile As StreamReader '入力ファイル
Dim Iinstr As String, Iidata() As String
'
IIT = Now()
'接続文字列を設定
sqlCont.ConnectionString = MyconString
Iifile = File.OpenText("d:\nettest\sqlget\ﾃｽﾄ用.txt")
Try
'Mak Update command
sqlCMDU = sqlCont.CreateCommand
sqlCMDU.CommandText = "UPDATE tentbl SET tenname = ?tenname " & _
"WHERE tencode = ?tencode"
sqlCMDU.Parameters.Add(New MySqlParameter("?tencode", MySqlDbType.Int16))
sqlCMDU.Parameters.Add(New MySqlParameter("?tenname", MySqlDbType.VarString))
'Mak Insert command
sqlCMDI = sqlCont.CreateCommand
sqlCMDI.CommandText = "INSERT INTO tentbl ( tencode, tenname) VALUES ( ?tencode, ?tenname)"
sqlCMDI.Parameters.Add(New MySqlParameter("?tencode", MySqlDbType.Int16))
sqlCMDI.Parameters.Add(New MySqlParameter("?tenname", MySqlDbType.VarString))
'接続を作成します
sqlDA.SelectCommand = New MySqlCommand()
sqlDA.SelectCommand.CommandType = CommandType.Text
sqlDA.SelectCommand.CommandText = "select * from tentbl"
sqlDA.SelectCommand.Connection = New MySqlConnection(MyconString)
'スキーマ情報をDatasetに追加する
sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
'検索結果を確認する。
sqlDA.Fill(resultDS, "tentbl")
'ここからデータ読み込み
While Iifile.Peek > -1
Iinstr = Iifile.ReadLine
Iidata = Split(Iinstr, ",")
Try
oDR = resultDS.Tables("tentbl").Rows.Find(Iidata(0))
If oDR Is Nothing Then
'接続を作成して
sqlCont.Open()
sqlCMDI.Parameters("?tencode").Value = Iidata(0)
sqlCMDI.Parameters("?tenname").Value = Iidata(1)
'内容を追加します。
sqlCMDI.ExecuteNonQuery()
J1 = J1 + 1
Else
'接続を作成して
sqlCont.Open()
'内容を更新します。
sqlCMDU.Parameters("?tencode").Value = Iidata(0)
sqlCMDU.Parameters("?tenname").Value = Iidata(1)
sqlCMDU.ExecuteNonQuery()
J2 = J2 + 1
End If
sqlCont.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
End Try
End While
'データ読み込みここまで
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
Iifile.Close()
End Try
'
IIE = Now
Dim interval As TimeSpan = IIE.Subtract(IIT)
MessageBox.Show(interval.Milliseconds & " 追加= " & Str(J1) & "件更新=" & Str(J2) & "件")
End Sub

　内容は今までの説明を読んでいただければわかるだろう。この場合の速度は３２０ミリ秒で　比較的早いように感じられる。終了せずに続けた場合には８０ミリ秒から２０ミリ秒の値が出たが、次第に所要時間は２０ミリ秒に近づいている。

　所要時間については　タイミングによってかなり違いがあるように感じられるので、断定は出来ないが、元のMySQLのデータ、テキストファイルの件数共に　より多くのデータがある状態で何度か繰り返せば　正確な値に近い物が得られるように思う。（例えば、以前に提示したマスターに対して２０００件程度のテキストファイルを使用するようなものを準備できれば）。
　ただ、どうなのかと思うのは初めに全データをDataSetに取り込む部分で、件数が多くなれば多くなるだけ　この処理は時間がかかるようになるだろう。ある程度Selectできれば違うだろうが　使用するテキストファイルの内容が不定であればこれも難しいだろう。逆に、テキストファイルから新しいDataTableを作成しておき、この内容を使用してMySQLのテーブルからDataSetを作成して更新するような方法を取る必要があるのではないだろうか？私は　不勉強でどのようなコードで実現できるかわからないが　今後こういった点についても調べてゆきたいものだ。

プログラムの内容を上のように書くと、インデントされていない為に読みづらくなってしまいます。この為、内容をPDF化したものをつけてあります。必要ならこちらを参照してみて下さい。PDFファイル。ちなみに、このファイルはVisualBasic 2005 Expressの内容をOpenOffice.Org2.4のWriterに貼り付けし　PDFにエクスポートしたものです。テキストコピー可の形で作成していますので、そのまま使用することも可能です。

「市民の目」トップページへ戻る