VB2005コントロール::DataTable

vb2005コントロール::DataTable
DataReaderの覚え書き
- DataReaderでレコード数を取得する
VB2005関連リンク

メモ書き
※mdbからDataSet利用で大量のデータ処理をしようとしているが、兎角問題が多く調べたところ以下のような特徴があるらしい。データベース利用の処理は、基本を抑えていないちょっとやっかいである。
2007.10.3追記

DataReader: mdb接続での順次処理用。大量の帳票出力処理などに適する。
SQLで抽出後、順次シーケンシャル処理となるので汎用性はない。
数○年前COBOL(パソコンではない)でSQL文を何気なく使っていたが、思うにこれに相当する機能を利用していたのだと思う。ただ、更新処理などはデータベースというより普通のファイル処理としてSQLなど使用せずに読み書きできていた気がする。今回のＰＣ用データベース利用ではmdb(その他のデータベースでも)レコードのアクセス自体がSQL文を介するといったところが異なるところなのだろう。(システムのファイルシステム自体がデータベース利用前提であったため。)
ゆえに、DataSetのようにメモリ内へ配列のように読み込んで利用するという利用方法は当時なかった。
単純明快で昔は良かったが、パソコンでは、データベース自体がアプリケーションであり、システム(OS標準システム)ではないということがキーポイントかも？
(さらに、RDB機能自体がシステム標準だったのでプログラムから何気な～くリレーションが使えてたような・・・。でも、主テーブル（マスタファイル）の更新は出来たが、リレーションしたレコードは読み取り（表示）専用という制限もあった（と思う）。)

格言：１０年一昔！それ以上は石器時代か？

DataSet: mdb非接続で、メモリ内へデータテーブルを読み込み、メモリ上のテーブル操作の後更新、追加、削除などの処理をmdbファイルへ反映する。大量のテーブルデータを一度に読み込むと、メモリがいくらあっても足りなくなる？ようなので注意。
また、SQL文で読み込んだメモリ内データは、DataSet用のコマンド文（プロパティ？メソッド？）を使用し再抽出、並べ替えなどが可能らしい。当然SQL文でmdbからデータ再読込などしないで再利用が可能らしい・・・。
汎用性は高いが、データ量が多い場合、よく考えて使わないと”スワップ”や”フリーズ”の嵐が起きて使ってらんない状態になる。

※上記から、帳票出力にはDataRederを使うのがよいらしいのだが・・・いまさら、めんどい・・・(T_T)

vb2005コントロール::DataTable

この記事は書きかけです。

予備知識のようなもの
=========
関連機能・用語、類似機能
※個人的見解的解釈（覚え書き）なので、誤っているかも知れません。主にMSDNライブラリより抜粋しています。

配列

DataGridView: データのメモリ内キャッシュを表します。
多くの種類のデータをバインド（データベースなど）または、バインドなしでデータを直接取扱場合など表形式で表示、編集などを行うコントロール。VB2005からDataGridに代わる新しいコントロールとして追加。

コレクション

DataSet: データソースと独立しプログラムに固有のデータや複数データソースのデータを含めることができる。但しDataSetに行われた更新・追加・削除などをデータソースに反映するのは「DataAdapter」の更新の操作はによって制御する。
DataTable: メモリ内データの 1 つのテーブルを表します

DataSetとDataTableの違い？
※2007.10.3追記 DataSet の下に DataTableが位置する。

DataSet

テーブル、リレーションシップ、および制約のコレクションで構成されます。ADO.NET では、DataSet 内のテーブルを表すために DataTable オブジェクトを使用します。DataTable は、1 つのメモリ内のリレーショナルデータテーブルを表します。このテーブルのデータは、そのデータが存在する .NET ベースアプリケーションのローカルデータですが、DataAdapter を使用して Microsoft SQL Server などのデータソースから読み込むこともできます。詳細については、「DataAdapter からの DataSet の読み込み」を参照してください。
※リレーションとか、テーブル情報とかを登録しておくもの？実体（データ）のないmdbの定義（リレーション／テーブルリストとか）ファイルってことでしょうか？

DataTable クラス

.NET Framework クラスライブラリ内の System.Data 名前空間のメンバです。DataTable は、単独でも DataSet のメンバとしても作成および使用できます。また、DataTable オブジェクトは、DataView などの他の .NET Framework オブジェクトからも使用できます。DataSet 内のテーブルのコレクションには、DataSet オブジェクトの Tables プロパティを使用してアクセスします。
※要するにメモリ上に作られた単一テーブルのクローンってことかな？…かつリレーションも・・・？（お調べ中）

※MSDNライブラリ（ヘルプ）より引用・・・てか、丸写し。・・・意味わかんないのでこまった。

DataAdapter
- Dim adapter As New OleDbDataAdapter(sqlcmd)などのように定義
  - adapter.Fill([DataSet/DataTable])：SelectCommand の結果を使用して DataSet を設定
- SelectCommand　：データソースからデータを取得
- InsertCommand　：データソースへデータを追加
- UpdateCommand　：データソースのデータを更新
- DeleteCommand　：データソースのデータを削除

.Dispose()
DataRow
.Rows()()

▲上へ

DataReaderの覚え書き

このページは散文的、支離滅裂な個人的メモストックです。
内容について、まったく根拠がないので正誤の保証は致しかねます。
※…っていうか、このＷｉｋｉ自体がそんなメモの集合体ですけど。

MSDNサンプルから・・・もうちょっと解り易く直した版。
※直したあとの動作確認なし。

'▼DBへの接続準備
Dim conn As New OleDbConnection( _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & data_source )
Dim sqlcmd As OleDbCommand = conn.CreateCommand

'▼table取得コマンド(SqlCommandText)
sqlcmd.CommandText = "SELECT * FROM TableName ;"

'▼DB接続(ファイルオープン)：mdbへ直接接続したまま処理を実行。
conn.Open()
Dim reader As OleDbDataReader = sqlcmd.ExecuteReader()

'▼シーケンシャルアクセス
Dim nextResult As Boolean = True
Do Until Not nextResult
  '// 列名の取得と表示
  Console.WriteLine( _
          reader.GetName(0) & vbTab & _
          reader.GetName(1) _
  )
  Do While reader.Read()
    '// 順次レコードを処理（Sequential Read）
    Console.WriteLine( _ 
            reader.GetString(0) & vbTab & _
            reader.GetString(1) _
    )
    Application.DoEvents()
  Loop
  nextResult = reader.NextResult()
Loop

'▼終了処理
reader.Close()
conn.Close()

MsgBox("終了しました")

※読出しデータが多い場合、Application.DoEvents()などでメッセージキューを適度に処理しないと多分途中でハングする。
※vbTabで"Tab"挿入できるのは今回の収穫。
※reader.GetString()のほか、テーブルデータに合わせて型指定する必要がある。例：reader.GetInt32()とか…
※DataTableReader.GetInt32メソッドは、DataTableReader.GetIntInteger()としても動きそうだが、実際はエラーになるので注意。
※Reader.GetString(*) の *は、0から始まるレコードのColumnNoを相対的に指定する。Reader.GetString("ColumnName")とするとエラー。
※また、SQL文で SELECT * FROM TableName ; とするなら良いが、抽出項目を指定した場合の相対値には注意。つまんない事で悩んでしまった。
※Reader("ColumnName")なら正常動作確認済み。(2007.10.13追記)
※Reader.GetString(*)・Reader.GetInt32(*)、Reader("ColumnName")の使い分けは作成者の好みで使い分けと言う事になります。

▲上へ

DataReaderでレコード数を取得する

経緯：DataSetを利用して100M超のDB処理組んでたら、やっぱりスワップ多発で使い物にならない。「ならば…」としぶしぶDataReaderで組みなおしたが、DataSetで簡単に取得可能な抽出レコード数を取得するプロパティやメソッドが存在しないことに気付く。

…　／(-。-)＼こまった　…

調べたところ解決できそうなことが解ったので追記。2007/10/7

DataReaderでも処理対象となるレコードを抽出するのはSQL文(sqlCommand)である。
DataSetでは簡単に取得可能な処理対象レコード数たが、DataReaderでは相当する関数やコマンドがない。

しかし、SQL文のSELECT COUNT(*)[集計関数]で問題解決可能なようである。

φ(。。* )メモシテオコウ

SELECT COUNT(*) FROM T_table WHERE Field = value ;

※SQL文で抽出条件を指定、レコード数を集計関数で返してもらう。

'▼DBへの接続(DataSet,DataReader共通)
Dim conn As New OleDbConnection( _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\AccessDB\data_source.mdb" )
Dim sqlcmd As OleDbCommand = conn.CreateCommand

'▼table取得コマンド(SqlCommandText)
sqlcmd.CommandText = "SELECT COUNT(*) " & _
                     "FROM Table_Name " & _
                     "WHERE Field = value ;"

'▼DB接続(ファイルオープン)：mdbへ直接接続したまま処理を実行。
conn.Open()
Dim reader As OleDbDataReader = sqlcmd.ExecuteReader()

'▼SQL集計関数 COUNT(*) の値を取得::１レコード・１フィールドで値が返る
Do While reader.Read()
  Console.WriteLine(vbTab & "{0}" , reader.GetInt32(0))
Loop

'▼終了処理
reader.Close()
conn.Close()

※動作未確認。
※~~reader.Read() は、続きのレコード監視用(通常True、NextRecなしでFalseと思う)なので Do ～ Loop 含めて不要かも？何セ確実に１行、一列しか返さないので。~~
-> ないと取得エラーになるので修正。
※レコード数を取得するだけなのにこんなにプログラムの行を費やす・・・関数(function)で組み直さないと見通しわるそう。

▲上へ

■関数版Ver1
DataReadCount(DataSource,tableName,condition) As Integer
(つうことで関数版のメモ。動作保証なしバージョン…いゃ、保証なんか一切しないし…出来ないし…する気皆無だし…)

''' <summary>抽出レコード数取得関数Ver.1</summary>
''' <param name="dataSource">接続mdbフルパス</param>
''' <param name="tableName">抽出対象テーブル名</param>
''' <param name="condition">抽出条件</param>
''' <returns>抽出レコード数</returns>
''' <remarks>
''' DataReader使用時のSQL抽出レコード数を取得する関数
''' </remarks>
Private Function DataReadCount(ByVal dataSource As String, _
    ByVal tableName As String,ByVal condition As String) As Integer
  Dim conn As New OleDbConnection( _ 
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dataSource & ";")
  Dim sqlcmd As OleDbCommand = conn.CreateCommand
  sqlcmd.CommandText = "SELECT COUNT(*) " & _
                       "FROM " & tableName & " " & _
                       "WHERE " & condition & " ;"
  conn.Open()
  Dim reader As OleDbDataReader = sqlcmd.ExecuteReader()
  Dim retValue As Integer
  Do While reader.Read()
    retValue = reader.GetInt32(0)
  Loop
  reader.Close()
  conn.Close()
  Return retValue
End Function

※コメント省略・注)動作未確認：”机上の空論”って奴です。
※その後：SQL文をテキストボックスで取得し、そこからtableNameとconditionを切り出す必要があることに気付く。まったく厄介である。sqlcmdを引数に SELECT句～ FROM の間を COUNT(*) で置き換えたので問題無いか確認中。->問題あるのでVer2を作成しました。

▲上へ

■関数版Ver2(Ver1は汎用性皆無なので機能追加)
DataReadCount(DataSource,sqlText) As Integer

''' <summary>抽出レコード数取得関数Ver.2</summary>
''' <param name="dataSource">接続mdbフルパス</param>
''' <param name="sqlText">SQL文をそのまま受け取る</param>
''' <returns>抽出レコード数</returns>
''' <remarks>
''' DataReader使用時のSQL抽出レコード数を取得する関数Ver.2
'''   1.FROM句は大文字のみ対応です。
'''   2.SELECT句以外の入力は想定していません。
'''   3.複雑な条件やSQL書式で正常に動作するかは未確認。
'''   Ver1との違い：引数(SQL全文受取)と受取SQL文加工を追加。
''' 2007.10.12 動作chk:定数へ式代入でエラー → 変数へ修正
''' 2007.10.13 動作chk:ORDER BY以降強制削除追加。在るとエラー
''' </remarks>
Private Function DataReadCount( _
                 ByVal dataSource As String, _
                 ByVal sqlText As String) As Integer
  '▼sqlText を COUNT(*) 集計関数仕様へ修正
  Dim selectLen As Integer = Len("SELECT") + 1
  Dim fromPos As Integer = sqlText.IndexOf("FROM")
  Dim delLen As Integer = fromPos - selectLen
  '//SELECT ～ FROM 内を削除
  sqlText = sqlText.Remove(selectLen, delLen)
  '//SELECT ～ FROM 内へ COUNT(*)を挿入
  '//～FROMまで削除し、先頭から追加挿入するのが楽なのに気付く
  '//しかし後でなにやってるかわかりづらいので「これでよし！」
  sqlText = sqlText.Insert(selectLen, "COUNT(*) ")
  '//2007.10.13追加：ORDER BY以降を削除
  sqlText = Mid(sqlText, 1, sqlText.IndexOf("ORDER BY")) & " ;"
  '▼DBからレコード数を取得する
  Dim conn As New OleDbConnection( _
                  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & dataSource & ";")
  Dim sqlcmd As OleDbCommand = conn.CreateCommand
  sqlcmd.CommandText = sqlText
  conn.Open()
  Dim reader As OleDbDataReader = sqlcmd.ExecuteReader()
  Dim retValue As Integer
  Do While reader.Read()
    retValue = reader.GetInt32(0)
  Loop
  reader.Close()
  conn.Close()
  '▼レコード数を返す（戻り値）
  Return retValue
End Function

※実働確認の後、また手直ししてるのでエラー出すかも？
※例えば抽出条件でグルーピングしている場合とか…SELECT～FROM内を置き換えるのでSQL側でエラー出すとか、グループ毎に複数レコードを返すとか…
実行確認結果

ORDER BY … など適切に削除しないと実行時エラー(2007.10.13修正)

※SQL文は大文字のみ対応なので注意。

▲上へ

■SQL集計関数には以下のようなものがある。

関数	説明
SUM(field)	合計
MIN(field)	最小値
MAX(field)	最大値
AVG(field)	平均例えばサブクエリ条件で ==================== SELECT クラス, 出席番号, 氏名 FROM T_期末テスト WHERE 点数 > (SELECT AVG(点数) FROM T_期末テスト) ; ==================== 等、データベースでほぼ完結した答えを返すのが凄い。
COUNT(field) COUNT(*)	フィールド内の有効値をカウントレコード数をカウント
GROUPING(field)	集計行の判定 ==================== SELECT 学年, クラス, 氏名, SUM(得点), GROUPING(クラス) FROM T_期末テスト GROUP BY ROLLUP(学年, クラス); ==================== といった感じで"学年","クラス"単位の得点合計の集計行を表示する。関連： SUM(field) , GROUP BY , ROLLUP(field1,field2):小計field指定 , HAVING:関数条件指定 HAVING GROUPING(field)>0 で集計行のみ表示など… ※Access / SQLServer利用可否不明
STDDEV(field) STDIV(field)	標準偏差 ※Access / SQL Server は Stdiv
VARIANCE(field) VAR(field)	分散 ※Access / SQL Server は Var