バックナンバー：VB.NETデータベースプログラミング

 第36号(2006.1.11発行)
======================================================================
           ★★ VB.NETデータベースプログラミング奮闘記 ★★
----------------------------------------------------------------------
いつもご購読ありがとうございます。本年もよろしくお願い申し上げます。

すぐ使えるADO.NET --> サンプルプログラム満載
                      http://park5.wakwak.com/‾weblab/
======================================================================
 ■■ VB.NETワンポイント：コネクションオブジェクトStateプロパティ ■■

コネクションオブジェクトのStateプロパティを調べると、データベースの接
続状態を知ることができます。次の例では、閉じる時は、念のためStateプロ
パティでデータベースが開いていることを確認して、開いている場合のみ閉じ
るようにしています。Closeメソッドは、Finallyブロックで実行します。

  'コネクション
  Dim mConn As OleDbConnection

  Try
    'ＤＢ接続文字列の設定
    mConn = New OleDbConnection()
    mConn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
    & Application.StartupPath & "¥sample.mdb"

    '接続状態チェック(まだ閉じています)
    MessageBox.Show(mConn.State.ToString, "DB接続状態１")

    mConn.Open()

    '接続状態チェック(開きました)
    MessageBox.Show(mConn.State.ToString, "DB接続状態２")

  Catch oExcept As Exception
    Throw New Exception("例外発生" + oExcept.ToString)

  Finally
    'ＤＢ接続を閉じる
    If Not mConn Is Nothing Then
      If mConn.State = ConnectionState.Open Then
        mConn.Close()
      End If
    End If

  End Try

----------------------------------------------------------------------
     ■■ データベースアクセスクラスの汎用メソッドについて 5 ■■

今回は、テキスト型の複数フィールドで主キーを構成するテーブルからキー値
のレコードを取得する汎用的なメソッドを作成します。前号のgetKeyRecordメ
ソッドと引数が異なりますので、同じメソッド名にします。

◆引数のNothing(未設定)チェック
引数のテーブル名、引数の主キーフィールド配列、引数の主キー値配列が未設
定の場合、myDBIOException例外をスローします。

◆テーブル名チェック
前号とまったく同様で、引数のテーブル名がmTableNameListになければ、
エラーとして、myDBIOException例外をスローします。

◆テーブルのキーフィールド名の取得
前号とまったく同様で、コネクションオブジェクトのGetOleDbSchemaTableメ
ソッドを使って、キーフィールド名を取得します。

◆主キーフィールド数チェック
配列引数に設定されたキーフィールドの数と、テーブルの主キーフィールド数
が一致するか調べます。不一致の場合には、myDBIOException例外をスローし
ます。引数に設定されたキーフィールドの数は、parKeyfield.Length で、テー
ブルの主キーフィールドの数は、前項のGetOleDbSchemaTableメソッドの戻り
値であるデータテーブルオブジェクトのRows.Countでわかります。

  If Not parKeyfield.Length = schemaTable.Rows.Count Then
    Throw New myDBIOException( _
    "引数の主キーフィールド数がテーブル主キーフィールド数と不一致")
  End If

◆主キー値数チェック
配列引数に設定されたキー値の数と、テーブルの主キーフィールド数が一致す
るか調べます。不一致の場合には、myDBIOException例外をスローします。
引数に設定されたキー値の数は、parKeyValue.Length で、テーブル
の主キーフィールドの数は、データテーブルオブジェクトのRows.Countでわか
ります。

  If Not parKeyValue.Length = schemaTable.Rows.Count Then
    Throw New myDBIOException( _
    "引数の主キー値数がテーブルの主キーフィールド数と不一致")
  End If

◆主キーフィールド名保持
GetOleDbSchemaTableメソッドで取得したテーブルの主キーフィールド名は、
いったんコレクションオブジェクトに保持し、配列引数に設定された主キー
フィールド名のチェックに使用します。

  For i = 0 To schemaTable.Rows.Count - 1
    keyList.Add(schemaTable.Rows(i).Item(3).ToString)
  Next i

●の項目については、主キーフィールドの数だけ繰り返し処理をします。

●テーブルのキーフィールド名チェック
配列引数に設定された主キーフィールド名が、コレクションオブジェクト
keyListになければ、myDBIOException例外をスローします。
ただし、ここでは、引数に同一キーフィールド名の存在チェックは省略してい
ます。

●キー値のチェック
キー値のチェックは、まず前後の空白を除去します。そして正規表現Regexク
ラスのIsMatchメソッドを使って、数字、英大文字、英小文字、"_"、"-"以外
の文字が含まれていればエラーとして、myDBIOException例外をスローします。

●SQL文の構築
WHERE句を構築します。配列引数が、(N+1)要素の場合には、

 @key0 = parKeyfield(0)の値 AND _
 @key1 = parKeyfield(1)の値 AND _
   ......
 @keyN = parKeyfield(N)の値

ように文字列を0から順に主キーフィールドがなくなるまで、連結していきま
す。たとえば、配列引数が２要素として、parKeyfield(0)がoyaYubinNo、
parKeyfield(1)がkoYubinNoの場合には、

 @key0 = oyaYubinNo AND @key1 = koYubinNo

となります。

最後の主キーフィールドの後には、ANDは不要のため、
  If Not i = schemaTable.Rows.Count - 1 Then
    wherePhrase += " AND "
  End If
により、最後の主キーフィールドの後にANDを連結しないようにしています。

●コマンドパラメータに値を設定
@key0、@key1 … を生成するたびに、コマンドパラメータに追加して、
parKeyfield(i)の値を設定します。

◆レコードの取得
データアダプタのFillメソッドを呼び出します。取得したレコードは
DataTable型になります。この型をgetKeyRecordメソッドを戻り値に指定すれ
ば、引数に指定したテーブルの主キー値のレコードを受け取ることができます。

次号では、テーブルの全レコードを取得するgetAllRecordメソッドをclsDBIOク
ラスに追加します。

注意：getKeyRecordメソッドの配列の引数名には、最後に"s"を追加して
parKeyfields と parKeyValues にしました。

--【プログラムソースリスト】------------------------------------------
Option Explicit On

Imports System.Data.OleDb
Imports System.Text.RegularExpressions

'----------------- << データベースアクセスクラス >> -----------------
Public Class clsDBIO

  '--------------------< メンバ：変数 >--------------------
  'コネクション
  Private mConn As OleDbConnection
  'コマンド
  Private mCommand As OleDbCommand
  'テーブル名コレクション
  Private mTableNameList As New Collection()

  '*******************************************************************
  ' 機能：コンストラクタ
  ' 引数：なし
  ' 履歴：2005.12.16 追加したFinallyブロックにmConn.Close()を移動
  '*******************************************************************
  Public Sub New()

    Try
      'ＤＢ接続文字列の設定
      mConn = New OleDbConnection()
      mConn.ConnectionString = _
      "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
      & Application.StartupPath & "¥sample.mdb"

      'コネクションオブジェクトの設定
      mCommand = New OleDbCommand()
      mCommand.Connection = mConn

      '********** << テーブル名を取得 >> **********
      Dim schemaTable As DataTable

      mConn.Open()

      schemaTable = mConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                    New Object() {Nothing, Nothing, Nothing, "TABLE"})

      'メンバmTableNameListにテーブル名をセット
      Dim i As Integer
      For i = 0 To schemaTable.Rows.Count - 1
        mTableNameList.Add(schemaTable.Rows(i)("TABLE_NAME").ToString)
      Next i

    Catch oExcept As Exception
      Throw New Exception _
      ("clsDBIOのコンストラクタで例外発生" + oExcept.ToString)

    Finally
      'ＤＢ接続を閉じる
      If Not mConn Is Nothing Then
        If mConn.State = ConnectionState.Open Then
          mConn.Close()
        End If
      End If

    End Try

  End Sub

  '*******************************************************************
  ' 機能：キー値のレコードの有無を調べるメソッド
  ' 引数：テーブル名、キー値
  ' 戻値：ある-->True、ない-->False
  ' 備考：キー値は英数字 "_" "-" で構成されていること
  '*******************************************************************
  Public Function existKeyRecord( _
    ByVal parTableName As String, ByVal parKeyValue As String) As Boolean

    '第33号を参照願います。

  End Function

  '*******************************************************************
  ' 機能：複数キー値のレコードの有無を調べるメソッド
  ' 引数：テーブル名、キーフィールド名配列、キー値配列
  ' 戻値：ある-->True、ない-->False
  ' 備考：キー値は英数字のみで構成されている
  '*******************************************************************
  Public Function existKeyRecord(ByVal parTableName As String, _
                                 ByVal parKeyfield() As String, _
                                 ByVal parKeyValue() As String) As Boolean

    '第34号を参照願います。

  End Function

  '*******************************************************************
  ' 機能：引数にテーブルと主キー値を指定してレコードを取得するメソッド
  ' 引数：String型 テーブル名
  ' 引数：String型 主キー値
  ' 戻値：DataTable型 取得したレコード
  '*******************************************************************
  Public Function getKeyRecord(ByVal parTableName As String, _
                               ByVal parKeyValue As String) As DataTable

    '第35号を参照願います。

  End Function

  '*******************************************************************
  ' 機能：複数キー値のレコードを取得するメソッド
  ' 引数：String型：テーブル名
  ' 引数：String型：主キーフィールド名配列
  ' 引数：String型：主キーフィールドに対応するキー値配列
  ' 戻値：DataTable型：取得したレコード情報
  ' 備考：キー値は英数字のみで構成されている
  '*******************************************************************
  Public Function getKeyRecord(ByVal parTableName As String, _
                               ByVal parKeyfields() As String, _
                               ByVal parKeyValues() As String) As DataTable

    Dim retDataTable As New DataTable()                 'リターン値
    Dim keyList As New Collection()
    Dim i As Integer

    Try

      '引数チェック
      If parTableName = Nothing Then
        Throw New myDBIOException("引数テーブル名が未設定")
      End If
      If parKeyfields Is Nothing Then
        Throw New myDBIOException("引数主キーフィールドが未設定")
      End If
      If parKeyValues Is Nothing Then
        Throw New myDBIOException("引数主キー値が未設定")
      End If

      'テーブル名チェック
      Dim tblName As String
      Dim existFlg As Boolean

      For Each tblName In mTableNameList
        If tblName = parTableName Then
          existFlg = True
          Exit For
        End If
      Next tblName

      If Not existFlg = True Then
        Throw New myDBIOException("テーブル名不正")
      End If

      'ＤＢ接続を開く
      mConn.Open()

      '主キーを取得
      Dim schemaTable As DataTable
      schemaTable = mConn.GetOleDbSchemaTable( _
                    OleDbSchemaGuid.Primary_Keys, _
                    New Object() {Nothing, Nothing, parTableName})

      '主キーフィールド数チェック
      If Not parKeyfields.Length = schemaTable.Rows.Count Then
        Throw New myDBIOException( _
        "引数の主キーフィールド数がテーブル主キーフィールド数と不一致")
      End If

      '主キー値数チェック
      If Not parKeyValues.Length = schemaTable.Rows.Count Then
        Throw New myDBIOException( _
        "引数の主キー値数がテーブルの主キーフィールド数と不一致")
      End If

      For i = 0 To schemaTable.Rows.Count - 1
        keyList.Add(schemaTable.Rows(i).Item(3).ToString)
      Next i

      Dim wherePhrase As String
      Dim paraStr As String
      Dim keyName As String

      mCommand.Parameters.Clear()

      For i = 0 To schemaTable.Rows.Count - 1

        existFlg = False
        For Each keyName In keyList
          If keyName = parKeyfields(i) Then
            existFlg = True
            Exit For
          End If
        Next keyName

        If Not existFlg = True Then
          Throw New myDBIOException( _
          (i + 1).ToString + "番目のキーフィールド名不正")
        End If

        'キー値チェック(注：キー値は英数字と_と-で構成されている場合)
        parKeyValues(i) = parKeyValues(i).Trim()
        If Not Regex.IsMatch(parKeyValues(i), "^[0-9a-zA-Z_¥-]+$") Then
          '英数字以外はエラー
          Throw New myDBIOException( _
          "エラー：" + (i + 1).ToString + "番目のキー値が正しくありません")
        End If

        paraStr = "@key" + i.ToString
        wherePhrase += " " + parKeyfields(i) + "=" + paraStr
        'WHERE句をANDで区切る
        If Not i = schemaTable.Rows.Count - 1 Then
          wherePhrase += " AND "
        End If

        '***** ＳＱＬ文の引数設定 *****
        mCommand.Parameters.Add( _
        New OleDbParameter(paraStr, OleDbType.Char))

        '----------< コマンドパラメータに値を設定 >----------
        mCommand.Parameters(paraStr).Value = parKeyValues(i)

      Next i

      'ＳＱＬ文設定
      mCommand.CommandText = _
      "SELECT * FROM " + parTableName + " WHERE" + wherePhrase

      'テーブルからレコード取得
      Dim dataAdapter As New OleDbDataAdapter()

      dataAdapter.SelectCommand = mCommand
      dataAdapter.Fill(retDataTable)

    Catch oExcept As myDBIOException
      Throw New myDBIOException(oExcept.Message)

    Catch oExcept As Exception
      Throw New Exception _
      ("clsDBIOのgetKeyRecordで例外発生" + oExcept.ToString)

    Finally
      'ＤＢ接続を閉じる
      If Not mConn Is Nothing Then
        If mConn.State = ConnectionState.Open Then
          mConn.Close()
        End If
      End If

    End Try

    '◆リターン
    Return retDataTable

  End Function

End Class

'--------------- << 独自エラーメッセージ用例外クラス >> --------------
Public Class myDBIOException
  Inherits ApplicationException

  Public Sub New(ByVal errorMessage As String)
    MyBase.New(errorMessage)
  End Sub

End Class

----------------------------------------------------------------------
              ■■ 次号予告 第37号(2月8日発行予定) ■■
1. VB.NETワンポイント
2. データベースアクセスクラスの汎用メソッドについて 6
======================================================================
VB.NET データベースプログラミング奮闘記
  発行者：ウェブ実験室(-----@-----)
          http://park5.wakwak.com/‾weblab/
----------------------------------------------------------------------
このメールマガジン(マガジンID: 0000128094)は、
インターネットの本屋さん『まぐまぐ』から配信されています。
  http://www.mag2.com/

【購読中止の方法】購読の中止は次のホームページからお願い致します。
  http://park5.wakwak.com/‾weblab/
  http://www.mag2.com/m/0000128094.htm
----------------------------------------------------------------------
このメールマガジン及び、「すぐ使えるADO.NET」ホームページで公開してい
るソースプログラム・データの利用により生じた損害等については、発行者は
一切責任を負いません。
ソースプログラムの再利用は自由です。著作権は発行者が所有します。
このメールマガジン及び、「すぐ使えるADO.NET」ホームページに掲載されて
いる会社名・製品名等は、各社の登録商標または商標です。
======================================================================