はじめに
業務システム開発では、開発するアプリケーションの種類を大きく分けて次のように分類することができます。
- 検索系
- 登録系
- 一覧系
- 帳票（伝票・単票）系
- 帳票（一覧）系
- バッチ系
そこで、この連載では検索系・登録系のアプリケーション開発について、実際の開発現場に沿った形で、開発の手順からコーディングまでのテクニックを解説していきます。
具体的には、データベースサーバのストアドプロシージャと、得意先検索画面、受注入力画面 を表示するアプリケーションの開発過程を紹介します。
アプリケーションの作成には、アクセスの帳票フォームのように1明細複数行表示を簡単に実現できる、ジーワンシステムのフリーレイアウトグリッドを利用します。
対象読者
- Visual Basic 2005/2008またはVisual C# 2005/2008を使ってプログラムを作ったことのある人。
- 社内での立場がプログラマに指示を出す人、顧客にシステム提案をする人、基本的なSQLが理解できる人。
必要な環境
Visual Basic 2005/2008またはVisual C# 2005/2008を使ってプログラムが作れる環境があること。また、SQL Serverがインストールされている必要があります。
なお、Visual Basic 2005 Express Edition、Visual C# 2005 Express Edition、SQL Server 2005 Express Editionでも使用可能です。
コントロールのインストール
はじめてフリーレイアウトグリッドを使用する方は、プロジェクトにフリーレイアウトグリッドをインストールする必要があります。
インストーラは、株式会社ジーワンシステムのWebページからダウンロードできますので、ここからダウンロードしてインストールしてください。
ファイルはZIP形式で圧縮されており、解凍すると「Setup2008.8.8.msi」というWindowsインストーラ パッケージがありますので、これを起動します。後は、インストーラの指示に従ってインストールしてください。
有償のコントロールですが、プロダクトキーを入力せずにインストールすることで、トライアル版として使用できます。制限事項などの詳細については、インストーラに同梱されているリリースノートを参照ください。
コントロールの追加
フリーレイアウトグリッドをインストールしたら、ツールボックスに専用のタブを作成し、フリーレイアウトグリッドコンポーネントを追加します。追加するコントロールは、「.NET Frameworkコンポーネント」の「G1FreeGrid」コントロールです。
GUIのデザイン
今回は、とりあえずアプリケーションの外観だけを作成します。
フリーレイアウトグリッドとVS標準のコントロール、データベースコントロールを使用します。
フリーレイアウトグリッドの設定
フリーレイアウトグリッドをフォームに配置したら、「G1FreeGrid」タスクトレイの［データソースの選択］で、利用するデータベースを設定します。
このデータベースのバインド作業は、YouTubeに作業手順の動画がありますので、詳細はこちらを参考にするとよいでしょう。
そして、［データソースからレイアウトを設定］を選択すると、自動的にデータソースに基づいて列数や列見出し（ヘッダー）を設定してくれます。
開発中のシステム－販売管理システム
本記事では、ある開発現場でフリーレイアウトグリッドを使ったアプリケーション開発を行っていく、というストーリーで解説を進めていきます。
- 【顧客側】情報システム課 課長 田中 様
- 【開発者側】システム開発課 課長 山下、新入社員 上田
ストーリーの背景
現時点では、とりあえずの要件定義と概要設計を請け負った。田中課長は早い段階で動くものが見たいとのご要望があり、第一回の打合せで販売管理システムで一番よく利用する 「得意先検索画面」のモックアップをお持ちすることになった。
山下課長のポリシー
システム開発をする上で、仕様変更は避けられないものです。仕様変更が起きたとき一番被害が大きいもの、そして、後工程の開発工数に影響するものは何でしょう。それはテーブル設計です。
では、一番被害の大きな仕様変更を起きないようにするにはどうすればよいでしょうか。
それは、他の仕様が固まってからテーブル設計をすることです。つまり、テーブル設計を極力後回しにすることで、被害を最小限に抑えることができます。
そのために、山下課長はストアドプロシージャを利用し、テーブルを作らないでモックアップを作る方法をとることにしました。
ストアドプロシージャは、データベースシステムのベンダー依存が非常に強いため導入を嫌うプロジェクトも多いのですが、実際の開発現場でデータベースシステムが途中から変更されることはほとんどなく、むしろホスト言語（VB6 → C# 、Ruby → Java）などの変更の方がはるかに多くなっています。そのため、ストアドプロシージャにしている方が変更のための作業工数が少なくてすみ、与えられたデータベースの能力を一杯まで使うことができる、という考えが主流になっています。
今回は、数あるRDBMSの中で、最も工数削減が可能なSQL Serverで提案できましたので、トレーニングを兼ねて開発メンバーに新人の上田君をアサインしました。
SQLServer のストアドプロシージャについて
Oralce や PostgreSQL などのデータベースシステムでは、テーブル型のデータを返すプロシージャを作るときは、Typeを作らなければいけません。
しかし、SQL Server は型を宣言する必要がありません。これにより、他のデータベースのストアドプロシージャに比べ非常に変更に強くなります。
--
-- 得意先を検索します。
-- 引数：@得意先名
--
CREATE PROCEDURE pr得意先検索
(@得意先名 varchar(100))
AS
SET NOCOUNT ON
-- 名称に一致する得意先を抽出し、結果のレコードを返します。
SELECT
得意先ID,
得意先名,
郵便番号,
住所,
電話番号,
FAX
FROM
得意先マスタ
WHERE
得意先名 LIKE @得意先名
RETURN
GO
実行時は、
exec pr得意先検索('山田%') ※1
とすれば、これは
SELECT
得意先ID,
得意先名,
郵便番号,
住所,
電話番号,
FAX
FROM
得意先マスタ
WHERE
得意先名 LIKE '山田%' ※2
と等価のSQLとして実行できます。
無償のSQL Server 2005 Express Editionが利用できるため、初期段階から顧客環境にテスト導入でき、モックアップでのデモを行うのにも非常に適しています。具体的な内容は、今後の連載を通してお伝えしていきたいと思います。
さて、山下課長は以上のように上田君に説明しました。
上田君は、ストアドプロシージャが難しいものだと感じる前に、書き方が違う程度という印象を持つことができました。
また山下課長は、データについては以下のような SQL を、新人教育で習ったExcel VBAを使ったマクロで生成し、テーブルの代わりにストアドプロシージャを利用するように上田君に指示しました。
CREATE VIEW vi得意先マスタ AS SELECT 100001 AS 得意先ID, '得意先1' AS 得意先名 UNION SELECT 100002 AS 得意先ID, '得意先2' AS 得意先名 UNION SELECT 100003 AS 得意先ID, '得意先3' AS 得意先名 UNION SELECT 100004 AS 得意先ID, '得意先4' AS 得意先名 GO
実行画面の作成
山下課長は、新人の上田君にラフな図で次のような得意先検索画面の作成を指示しました。
上田君は、指示通り、ビュー・ストアドプロシージャ・画面の順で作り始めました。
-- 得意先を検索します。
-- 引数：@得意先名 検索する内容
-- @検索オプション 1のとき一部一致
ALTER PROCEDURE [dbo].[pr得意先検索]
(
@得意先名 varchar(100)
, @検索オプション int
)
AS
SET NOCOUNT ON
-- 名称に一致する得意先を抽出し、結果のレコードを返します。
SELECT
得意先ID,
得意先名,
郵便番号,
住所,
電話番号,
FAX
FROM
dbo.vi得意先マスタ
WHERE
得意先名 LIKE dbo.fnFinedName(@得意先名, @検索オプション)
RETURN
GO
ダミーのビューを作成する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文を生成します。
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通りの検索を使い分けるようにします。これは、チェックボックスのチェックで判断し、クエリのオプションを切り替えるようにしています。
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 を組み立てる必要があります。
