フリーレイアウトグリッドで業務アプリケーションを作成する（AD）

フリーレイアウトグリッドを使った業務アプリケーションの作成

第1回導入準備から検索画面作成まで

瀬戸遥[著]

2008/08/22 11:00

ダウンロードサンプルファイル（C#） (268.1 KB)
ダウンロードサンプルファイル（VB.NET） (186.0 KB)

ダミーのビューを作成するSQL文をVBAで生成する

　「ダミーのビューを作成するSQL文」を生成するVBAのコードを、ExcelのVisual Basic Editorで作成します。

　このマクロは、ワークシートのセル「B8:IV65536」の間に入力されたデータから、指定された名称のダミーのビューを作成するSQL文を生成します。

　カラム名が空白になれば、それ以降の列は出力されません。カラム名を入力した列には、必ず型を設定してください。B列のデータが空白になれば、それ以降は出力されません。

　日付型は YYYY/MM/DD のフォーマットにする必要があります。

Private Sub CommandButton1_Click()
Dim intCol As Integer       'データエリアの列カウンタ
Dim intRow As Integer       'データエリアの行カウンタ
Dim arrCol() As String      'カラム名の一覧を取得
Dim strDelimiter As String  '列の区切り（カンマまたは空白）
Dim strCell As String       'データの内容
Dim strColData As String    'SQLの1カラム分の記述
Dim i  As Integer           'カウンタ

Dim strSQL As String        '出力するSQL

Dim dob As DataObject       'クリップボードを利用

    ' 定型のSQLを記述（既に存在すれば削除する）
    strSQL = "IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID('"
    strSQL = strSQL & Me.Range("ビュー名").Text & "'))"
    strSQL = strSQL & vbCrLf & "DROP VIEW " & Me.Range("ビュー名").Text
    strSQL = strSQL & vbCrLf & "GO"
    strSQL = strSQL & vbCrLf
    strSQL = strSQL & vbCrLf & "CREATE VIEW " & Me.Range("ビュー名").Text & " AS "

    ' カラム名の一覧を取得する（カラム名が入力済みのモノのみ対象とする）
    intCol = 0
    Do Until Me.Range("カラム名").Offset(0, intCol).Text = ""
        ReDim Preserve arrCol(intCol)
        arrCol(intCol) = Me.Range("カラム名").Offset(0, intCol).Text
        intCol = intCol + 1
    Loop

    ' シートの1列目が入力されているときデータがあると判断してSQLを生成する。
    i = 0
    Do Until Me.Range("データ").Offset(intRow, 0).Text = ""
        ' シートのデータ用領域の1行目でないとき、UNION ALL を追加する。
        If intRow > 0 Then
            strSQL = strSQL & vbCrLf & "    UNION ALL "
        End If

        strSQL = strSQL & vbCrLf & "    SELECT"

        ' カラムのデータからダミーの SQL を生成する。
        For i = 0 To intCol - 1
            If i = 0 Then
                strDelimiter = " "
            Else
                strDelimiter = ", "
            End If

            strCell = Me.Range("データ").Offset(intRow, i).Text

            '指定された型に変換する。
            Select Case Me.Range("型").Offset(0, i).Text
            Case "文字型"
                strColData = strDelimiter & "'" & strCell & "'"
            Case "日付型"
                If IsDate(strCell) Then
                    strColData = strDelimiter & "CAST('" _
                            & StrConv(strCell, vbNarrow) _
                            & "' AS datetime)"
                Else
                    strColData = strDelimiter & "NULL"
                End If
            Case "数値型"
                If IsNumeric(strCell) Then
                    strColData = strDelimiter & StrConv(strCell, vbNarrow)
                Else
                    strColData = strDelimiter & "0"
                End If
            End Select

            strSQL = strSQL & strColData & " AS " & arrCol(i)

        Next i

        intRow = intRow + 1
    Loop

    strSQL = strSQL & vbCrLf & "GO"

    'クリップボードに出力する。
    Set dob = New DataObject
    dob.Clear
    dob.SetText (strSQL)
    dob.PutInClipboard

    MsgBox "クリップボードにコピーしました。"
End Sub

　このマクロを実行すると、次のビュー作成のSQL文を生成します。

ビュー作成のSQL文

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID('[dbo].[vi得意先マスタ]'))
DROP VIEW [dbo].[vi得意先マスタ]
GO

CREATE VIEW [dbo].[vi得意先マスタ] AS
    SELECT 100001 AS 得意先ID, '鈴木　太郎' AS 得意先名, '999-1111' AS 郵便番号, '東京都新宿区南東新宿1-2-3' AS 住所, '001-0111-0000' AS 電話番号, '800-0001-1002' AS FAX
    UNION ALL
    SELECT 100002 AS 得意先ID, '佐藤　三郎' AS 得意先名, '999-1112' AS 郵便番号, '大阪府大阪市北区南東梅田9-8-7' AS 住所, '001-0121-0001' AS 電話番号, '800-0001-1013' AS FAX
    UNION ALL
    SELECT 100003 AS 得意先ID, '中村　花子' AS 得意先名, '999-1113' AS 郵便番号, '愛知県名古屋市中村区南東中村町2-3-4' AS 住所, '001-0111-0002' AS 電話番号, '800-0001-1024' AS FAX
    UNION ALL
    SELECT 100004 AS 得意先ID, '山本　太郎' AS 得意先名, '999-1114' AS 郵便番号, '福岡県福岡市中央区南東天神8-7-6' AS 住所, '001-0121-0002' AS 電話番号, '800-0001-1035' AS FAX
    UNION ALL
    SELECT 100005 AS 得意先ID, '田中　三郎' AS 得意先名, '999-1115' AS 郵便番号, '北海道札幌市中央区南東3-4-5' AS 住所, '001-0111-0003' AS 電話番号, '800-0001-1046' AS FAX
    UNION ALL
    SELECT 100006 AS 得意先ID, '渡辺　花子' AS 得意先名, '999-1116' AS 郵便番号, '宮城県仙台市青葉区南東国分町7-6-5' AS 住所, '001-0121-0003' AS 電話番号, '800-0001-1057' AS FAX
    UNION ALL
    SELECT 100007 AS 得意先ID, '高橋　太郎' AS 得意先名, '999-1117' AS 郵便番号, '東京都新宿区南東新宿1-2-3' AS 住所, '001-0111-0004' AS 電話番号, '800-0001-1068' AS FAX
    UNION ALL
    SELECT 100008 AS 得意先ID, '斉藤　三郎' AS 得意先名, '999-1118' AS 郵便番号, '大阪府大阪市北区南東梅田9-8-7' AS 住所, '001-0121-0004' AS 電話番号, '800-0001-1079' AS FAX
    UNION ALL
    SELECT 100009 AS 得意先ID, '高田　花子' AS 得意先名, '999-1119' AS 郵便番号, '愛知県名古屋市中村区南東中村町2-3-4' AS 住所, '001-0111-0005' AS 電話番号, '800-0001-1090' AS FAX
    UNION ALL
    SELECT 100010 AS 得意先ID, '川口　太郎' AS 得意先名, '999-1120' AS 郵便番号, '福岡県福岡市中央区南東天神8-7-6' AS 住所, '001-0121-0005' AS 電話番号, '800-0001-1101' AS FAX
    UNION ALL
    SELECT 100011 AS 得意先ID, '武田　三郎' AS 得意先名, '999-1121' AS 郵便番号, '北海道札幌市中央区南東3-4-5' AS 住所, '001-0111-0006' AS 電話番号, '800-0001-1112' AS FAX
    UNION ALL
    SELECT 100012 AS 得意先ID, '遠藤　花子' AS 得意先名, '999-1122' AS 郵便番号, '宮城県仙台市青葉区南東国分町7-6-5' AS 住所, '001-0121-0006' AS 電話番号, '800-0001-1123' AS FAX
    UNION ALL
    SELECT 100013 AS 得意先ID, '北村　太郎' AS 得意先名, '999-1123' AS 郵便番号, '東京都新宿区南東新宿1-2-3' AS 住所, '001-0111-0007' AS 電話番号, '800-0001-1134' AS FAX
    UNION ALL
    SELECT 100014 AS 得意先ID, '石川　三郎' AS 得意先名, '999-1124' AS 郵便番号, '大阪府大阪市北区南東梅田9-8-7' AS 住所, '001-0121-0007' AS 電話番号, '800-0001-1145' AS FAX
    UNION ALL
    SELECT 100015 AS 得意先ID, '西田　花子' AS 得意先名, '999-1125' AS 郵便番号, '愛知県名古屋市中村区南東中村町2-3-4' AS 住所, '001-0111-0008' AS 電話番号, '800-0001-1156' AS FAX
    UNION ALL
    SELECT 100016 AS 得意先ID, '佐藤　花子' AS 得意先名, '999-1112' AS 郵便番号, '福岡県福岡市中央区南東天神8-7-6' AS 住所, '001-0121-0001' AS 電話番号, '800-0001-1013' AS FAX
    UNION ALL
    SELECT 100017 AS 得意先ID, '中村　三郎' AS 得意先名, '999-1113' AS 郵便番号, '北海道札幌市中央区南東3-4-5' AS 住所, '001-0111-0002' AS 電話番号, '800-0001-1024' AS FAX
    UNION ALL
    SELECT 100018 AS 得意先ID, '山本　太郎' AS 得意先名, '999-1114' AS 郵便番号, '宮城県仙台市青葉区南東国分町7-6-5' AS 住所, '001-0121-0002' AS 電話番号, '800-0001-1035' AS FAX
    UNION ALL
    SELECT 100019 AS 得意先ID, '田中　花子' AS 得意先名, '999-1115' AS 郵便番号, '東京都新宿区南東新宿1-2-3' AS 住所, '001-0111-0003' AS 電話番号, '800-0001-1046' AS FAX
    UNION ALL
    SELECT 100020 AS 得意先ID, '渡辺　三郎' AS 得意先名, '999-1116' AS 郵便番号, '大阪府大阪市北区南東梅田9-8-7' AS 住所, '001-0121-0003' AS 電話番号, '800-0001-1057' AS FAX
    UNION ALL
    SELECT 100021 AS 得意先ID, '高橋　太郎' AS 得意先名, '999-1117' AS 郵便番号, '愛知県名古屋市中村区南東中村町2-3-4' AS 住所, '001-0111-0004' AS 電話番号, '800-0001-1068' AS FAX
    UNION ALL
    SELECT 100022 AS 得意先ID, '斉藤　花子' AS 得意先名, '999-1118' AS 郵便番号, '福岡県福岡市中央区南東天神8-7-6' AS 住所, '001-0121-0004' AS 電話番号, '800-0001-1079' AS FAX
    UNION ALL
    SELECT 100023 AS 得意先ID, '高田　三郎' AS 得意先名, '999-1119' AS 郵便番号, '北海道札幌市中央区南東3-4-5' AS 住所, '001-0111-0005' AS 電話番号, '800-0001-1090' AS FAX
    UNION ALL
    SELECT 100024 AS 得意先ID, '川口　太郎' AS 得意先名, '999-1120' AS 郵便番号, '宮城県仙台市青葉区南東国分町7-6-5' AS 住所, '001-0121-0005' AS 電話番号, '800-0001-1101' AS FAX
    UNION ALL
    SELECT 100025 AS 得意先ID, '武田　花子' AS 得意先名, '999-1121' AS 郵便番号, '東京都新宿区南東新宿1-2-3' AS 住所, '001-0111-0006' AS 電話番号, '800-0001-1112' AS FAX
    UNION ALL
    SELECT 100026 AS 得意先ID, '遠藤　三郎' AS 得意先名, '999-1122' AS 郵便番号, '大阪府大阪市北区南東梅田9-8-7' AS 住所, '001-0121-0006' AS 電話番号, '800-0001-1123' AS FAX
    UNION ALL
    SELECT 100027 AS 得意先ID, '北村　太郎' AS 得意先名, '999-1123' AS 郵便番号, '愛知県名古屋市中村区南東中村町2-3-4' AS 住所, '001-0111-0007' AS 電話番号, '800-0001-1134' AS FAX
    UNION ALL
    SELECT 100028 AS 得意先ID, '石川　花子' AS 得意先名, '999-1124' AS 郵便番号, '福岡県福岡市中央区南東天神8-7-6' AS 住所, '001-0121-0007' AS 電話番号, '800-0001-1145' AS FAX
    UNION ALL
    SELECT 100029 AS 得意先ID, '西田　三郎' AS 得意先名, '999-1125' AS 郵便番号, '北海道札幌市中央区南東3-4-5' AS 住所, '001-0111-0008' AS 電話番号, '800-0001-1156' AS FAX
GO

メモ

　このソースを実際に使うときには、C#の場合はSettings.settingsで、VBの場合は［プロジェクト］-［プロパティ］-［設定（VB）］で接続先を変える必要があります。

　また、現場で使うときには、DB接続・命名法などに個別のポリシーがあるはずですので、そのポリシーに従ってください。

検索ボタンの処理

　フォームに配置した検索ボタンでは、検索条件と完全一致で検索するのか、部分一致で検索するのか、2通りの検索を使い分けるようにします。これは、チェックボックスのチェックで判断し、クエリのオプションを切り替えるようにしています。

VB.NET

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim opt As Integer = 0

        If Me.チェックボックス.Checked Then
            opt = 1
        End If

        Pr得意先検索TableAdapter.Fill(CzsampleDataSet.pr得意先検索, _
                テキストボックス.Text, opt)

    End Sub

private void btnSearch_Click(object sender, EventArgs e)
        {
            int opt = 0;

            if (チェックボックス.Checked)
            {
                opt = 1;
            }

            pr得意先検索TableAdapter.Fill(czsampleDataSet.pr得意先検索,
                テキストボックス.Text, opt);
        }

　顧客の田中課長にレビューしたところ、次の要望がでました。

名称欄と住所欄を広くしてほしい。
横スクロールしないように2段で表示して欲しい

　山下課長は、フリーレイアウトグリッドの機能を使ってレイアウトの修正方法を説明し、レイアウトは結合テスト期間にエンドユーザーに決めてもらうという方向に話をまとめました。

　山下課長からすれば、ユーザーにデザイン作業を代わってもらったことになるのですが、田中課長は逆に喜んでいます。

　結果的に、他の画面についてもレイアウトに関する打合せ工数を大幅に削減できますので、お互いにとってよい結果につながりました。

まとめ

　今回は、導入準備から検索画面作成までを行いました。フリーレイアウトグリッドの便利な機能や優れている点については、今後の連載の中で詳しく紹介していきます。

　次回は、入力画面の作成について説明します。

コラム

　ストアドプロシージャは速いとよく言われます。これは事実ともウソとも言えます。

　そもそも、ストアドプロシージャとは何でしょうか？

　ストアドプロシージャとは、直訳するとStored（保存された）Procedure（手続き）です。つまり、手順が保存されているため、実行計画も計画済みのものが保存されています。ですから、実行時にアクセスパスを検討する必要がなくなるため速くなります。

　また、サンプルコードの※1と※2を比べると、実行時に転送されるSQL文の量も圧倒的に※1のストアドプロシージャの方が少なくなりますから、その分速くなります。

　とはいえ、現状のハードウェア・ネットワーク環境であれば、影響は微々たるものでしょう。それどころか、※1と※2を実際に比べると、逆にストアドプロシージャの方が遅くなる可能性が高いです。

　その理由は、ストアドプロシージャはルールベースでアクセスパスを1つに固定してしまうため、顧客名にインデックスがあって前方一致で検索したとしても、インデックスを使わないアクセスパスを保存してしまう可能性が高いわけです。データ件数によっては非常に大きな差が出るでしょう。

　これを防ぐには、ストアドプロシージャの中で動的に SQL を組み立てる必要があります。

この記事は参考になりましたか？

印刷用を表示

フリーレイアウトグリッドで業務アプリケーションを作成する連載記事一覧: フリーレイアウトグリッドを使った　業務アプリケーションの作成 2 - 受注画面の作成

フリーレイアウトグリッドを使った業務アプリケーションの作成

この記事の著者: 瀬戸遥（セトハルカ）

8ビットコンピュータの時代からBASICを使い、C言語を独習で学びWindows 3.1のフリーソフトを作成、NiftyServeのフォーラムなどで配布。Excel VBAとVisual Basic関連の解説書を中心に現在まで40冊以上の書籍を出版。近著に、「ExcelユーザーのためのAccess再...

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この著者の最近の執筆記事