バックナンバー【第59号】

 第59号(2007.12.12発行)
======================================================================
        ★★ -- VB.NETデータベースプログラミング奮闘記 -- ★★
            （VB 2005 Express ＆ SQL Server 2005 Express）
----------------------------------------------------------------------
いつもご購読ありがとうございます。ADO.NETの基本的なプログラミングを中
心に掲載します。今後ともよろしくお願い申し上げます。

すぐ使えるADO.NET --> http://park5.wakwak.com/‾weblab/
======================================================================
                  ■■ 社員テーブルに追加する 1 ■■

社員コード、社員氏名、カナ名、部署コードを入力して、追加ボタンをクリッ
クすると、入力したデータのエラーチェックをして、エラーが無ければ、社員
テーブルに1レコード追加します。接続文字列は、app.configに登録しておき
ます。

【sampleデータベースの社員テーブル】
----------------------------------------------------------------------
key 　列　名　　　データ型　　　　nullを許容　　備　考
----------------------------------------------------------------------
○　社員コード　　nchar(5)       許容しない　英数字のみ
−　社員氏名　　　nvarchar(20)   許容しない
−　社員カナ　　　nvarchar(40)   許容しない
−　部署コード　　nchar(4)       許容しない
−　更新日時　　　datetime       許容しない
----------------------------------------------------------------------

【sampleデータベースの部署テーブル】
----------------------------------------------------------------------
key 　列　名　　　データ型　　　　nullを許容　　備　考
----------------------------------------------------------------------
○　部署コード　　nchar(4)       許容しない　英数字のみ
−　部署名　　　　nvarchar(20)   許容しない
−　更新日時　　　datetime       許容しない
----------------------------------------------------------------------

コンボボックスのDropDownStyleプロパティは、DropDownListを選択して、テ
キストボックス部分を入力できないように設定します。
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃　社員テーブル追加　　　　　　　　　　　　　　　　＿□×┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃　　Label1　　　　TxtShainCode
┃┌―――――┐　┌―――――┐
┃│社員コード│　│　　　　　│
┃└―――――┘　└―――――┘
┃　　Label2　　　　TxtShainShimei
┃┌―――――┐　┌――――――――┐
┃│社員氏名　│　│　　　　　　　　│
┃└―――――┘　└――――――――┘
┃　　Label3　　　　TxtShainKana
┃┌―――――┐　┌――――――――――――――┐
┃│社員カナ　│　│　　　　　　　　　　　　　　│
┃└―――――┘　└――――――――――――――┘
┃　　Label4　　　　CboBusho
┃┌―――――┐　┌――――――――┐
┃│部署コード│　│　　　　　　│▼│
┃└―――――┘　└――――――――┘
┃　BtnInsert　　　　BtnClear
┃┌――――┐　　┌―――――┐
┃│　追加　│　　│　クリア　│
┃└――――┘　　└―――――┘
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

【追加ボタンクリック処理】
よくあるパターンは、エラーチェック、画面制御、データベースアクセスなど、
すべての処理をここにコーディングしてしまうことです。追加ボタンクリック
処理は、できる限り画面制御処理に専念します。

そのために、入力データのエラーチェックは、社員データクラスに、レコード
追加処理や検索処理は、データベースアクセスクラスに任せます。

【フォームロード処理】
この処理では、部署名をコンボボックスに表示して、選択した部署名に対応す
る部署コードを取得できるようにします。

----------------------------------------------------------------------
           ■■ 全レコードを取得するGetAllDataメソッド ■■

部署名をコンボボックスに表示するために、基本クラスClsSampleDBIO から派
生した ClsBushoTableIOクラスを作成して、部署テーブルの全レコードを取得
する GetAllDataメソッドを追加します。

さらに、GetKeyDataメソッドと同様に、基本クラスにもGetAllDataメソッドを
追加して、データベースアクセス処理をコーディングします。

ClsBushoTableIOクラスの GetAllDataメソッドは、引数を３つ指定できます。
最初の引数は必須で、他の２つは任意とします。したがって、先頭にOptional
キーワードをつけて、

 Optional ByVal SortKey As String = Nothing

とします。

GetAllDataメソッドを、ClsBushoTableIOクラスに追加します。
ClsBushoTableIO.vbファイルを新規に追加してください。

--【プログラムソースリスト（ファイル名：ClsBushoTableIO.vb）】--------
Imports System.Configuration   '参照の追加でSystem.Configurationを追加
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions

Public Class ClsBushoTableIO
  Inherits ClsSampleDBIO

  '*******************************************************************
  ' 機能：コンストラクタ
  ' 引数：なし
  '******1*********2*********3*********4*********5**********6*********
  Sub New()

    MyBase.New()     '必ず最初に基本クラスのコンストラクタを呼び出す

  End Sub

  '*******************************************************************
  ' 機能：部署テーブルから引数にした列名の全レコードを取得する
  ' 引数：必須 ByVal 部署テーブルの取得する列名（String型配列）
  ' 引数：任意 ByVal 整列する列名（String型）
  ' 引数：任意 ByVal 昇順ならTrue、降順ならFalse（Boolean型）
  ' 戻値：部署テーブルの全レコード（DataTble型）
  '******1*********2*********3*********4*********5**********6*********
  Public Overloads _
  Function GetAllData _
           (ByVal Columns() As String, _
            Optional ByVal SortKey As String = Nothing, _
            Optional ByVal Sorted As Boolean = True _
           ) As DataTable

    Dim retDt As New DataTable()                '戻値

    Try
      '引数Nothing(未設定)・空白チェック
      If Columns.Length = 0 Then
        Throw New myDBIOException("エラー：引数Columnsが未設定")
      End If

      '部署テーブルから引数列名の全レコードを取得する
      retDt = MyBase.GetAllData _
      ("部署テーブル", Columns, SortKey, Sorted)

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

    Catch ex As Exception
      Throw New Exception("例外発生：" & ex.ToString)

    End Try

    '◆戻値を設定してリターン
    Return retDt

  End Function

End Class
----------------------------------------------------------------------

基本クラスClsSampleDBIOの GetAllDataメソッドでは、引数のテーブル名と列
名のエラーチェックをします。

コネクションオブジェクトの GetSchemaメソッドを呼出すと、テーブル名や列
名が、メソッドの戻り値 DataTable型として取得できます。テーブル名を取得
する場合には

  dtTableName = con.GetSchema("Tables")

とします。同様にして、部署テーブルの列名も取得できます。第２引数でテー
ブル名を指定して列名を取得します。

  dtColumns = con.GetSchema _
  ("Columns", New String() {Nothing, Nothing, "部署テーブル", Nothing})

GetSchemaメソッドで取得した列名とテーブル名を、引数で指定した値をチェッ
クしながら、

SELECT 列名(0), .... 列名(N) FROM 部署テーブル ORDER BY 列名 ASC

のようなSQL文を作成していきます。そして、完成したSQL文を使って、テーブ
ルから全レコードを取得します。

基本クラスの GetAllDataメソッドを、ClsSampleDBIOクラスに追加します。

--【プログラムソースリスト（ファイル名：ClsSampleDBIO.vb）】----------
  '*******************************************************************
  ' 機能：引数に指定したテーブルの全レコードを取得する
  ' 引数：必須 ByVal テーブル名（String型）
  ' 引数：必須 ByVal 取得する列名（String型配列）
  ' 引数：任意 ByVal 整列する列名（String型）
  ' 引数：任意 ByVal 昇順ならTrue、降順ならFalse（Boolean型）
  ' 戻値：引数に指定したテーブルの全レコード（DataTble型）
  '******1*********2*********3*********4*********5**********6*********
  Public Function GetAllData _
                  (ByVal TableName As String, _
                   ByVal Columns() As String, _
                   Optional ByVal SortKey As String = Nothing, _
                   Optional ByVal Sorted As Boolean = True _
                  ) As DataTable

    Dim retDt As New DataTable()                '戻値

    Try
      Using con As New SqlConnection
        Using cmd As New SqlCommand

          '----------< 引数が未設定だったら例外をスロー >----------
          If TableName = Nothing Then
            Throw New myDBIOException("引数エラー：TableNameが未設定")
          End If

          If Columns.Length = 0 Then
            Throw New myDBIOException("引数エラー：Columnsが未設定")
          End If

          '----------< テーブル名取得、列名取得 >----------
          Dim dtTableName As DataTable
          Dim dtColumns As DataTable

          '接続関連プロパティ設定
          Me.SetConnection(con, cmd)

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

          'テーブル名を取得
          dtTableName = con.GetSchema("Tables")

          '列名を取得
          dtColumns = con.GetSchema _
          ("Columns", New String() {Nothing, Nothing, TableName, Nothing})

          'ＤＢ接続を閉じる
          con.Close()

          '----------< ＳＱＬ文の生成 >----------
          Dim existFlg As Boolean = False

          cmd.Parameters.Clear()
          cmd.CommandText = "SELECT "

          '列名の設定
          If Columns(0) = "*" Then
            '全列名指定の場合
             cmd.CommandText &= Columns(0) & " "

          Else
            '特定列名指定の場合
            For i As Integer = 0 To Columns.Length - 1
              '列名存在チェック
              For j As Integer = 0 To dtColumns.Rows.Count - 1
                If Columns(i) = dtColumns.Rows(j)("COLUMN_NAME").ToString Then
                  existFlg = True
                  Exit For
                End If
              Next j

              '列名が存在すれば、SQL文に連結
              If existFlg = True Then
                cmd.CommandText &= Columns(i)
                '最後の列名以外「,」を連結
                If i < Columns.Length - 1 Then
                  cmd.CommandText &= ","
                End If
                existFlg = False

              Else
                '列名が存在しなければ、例外をスロー
                Throw New myDBIOException _
                ("エラー：" & Columns(i) & "の列名は存在しない")
              End If

            Next i

          End If

          'テーブル名の設定
          For i As Integer = 0 To dtTableName.Rows.Count - 1
            'テーブル名存在チェック
            If TableName = dtColumns.Rows(i)("TABLE_NAME").ToString Then
              existFlg = True
              Exit For
            End If
          Next i

          'テーブル名が存在すれば、テーブル名をSQL文に連結
          If existFlg = True Then
            cmd.CommandText &= " FROM " & TableName
          Else
            'テーブル名が存在しなければ、例外をスロー
            Throw New myDBIOException _
            ("エラー：" & TableName & "は存在しない")
          End If

          '整列の設定
          If Not SortKey = Nothing Then
            For i As Integer = 0 To Columns.Length - 1
              '整列キーの列名存在チェック
              If SortKey = dtColumns.Rows(i)("COLUMN_NAME").ToString Then
                existFlg = True
                Exit For
              End If
            Next i

            '整列キーの列名が存在すれば、列名をSQL文に連結
            If existFlg = True Then
              cmd.CommandText &= " ORDER BY " & SortKey
              '整列キーの昇順または降順をSQL文に連結
              If Sorted = True Then
                cmd.CommandText &= " ASC"
              Else
                cmd.CommandText &= " DESC"
              End If
            Else
              '整列キーの列名が存在しなければ、例外をスロー
              Throw New myDBIOException _
              ("エラー：" & SortKey & "の列名は存在しない")
            End If

          End If

          '----------< テーブルから全レコード取得 >----------
          Dim da As New SqlDataAdapter
          da.SelectCommand = cmd
          da.Fill(retDt)

        End Using

      End Using

    Catch ex As Exception
      '例外が発生したら、例外をスロー
      Throw New Exception("例外発生：" & ex.ToString)

    End Try

    '----------< ◆戻値を設定してリターン >----------
    Return retDt

  End Function

----------------------------------------------------------------------
次号では、フォームロード処理や追加ボタンクリック処理などを作成します。
入力データエラーチェッククラスについても触れていきます。
======================================================================
VB.NET データベースプログラミング奮闘記
  発行者：ウェブ実験室([email protected])
          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」ホームページに掲載されてい
る会社名・製品名等は、各社の登録商標または商標です。
======================================================================