データベースとADO.NETの機能を適切に活用する（2/3）

連載：実践で役立つ業務アプリ開発のヒント

第1回データベースとADO.NETの機能を適切に活用する

えムナウ（児玉宏之）
（Microsoft MVP Visual Developer - Visual C# JAN 2005 - DEC 2007）
2007/06/08

●開発ヒント2：ストアド・プロシージャの作成

　ストアド・プロシージャはどうしても利用しなければならない技術というわけではない。だが、SQL Serverの速度と効率性を高めるうえでは欠かせない技術である。従って筆者はできるだけこれを利用することをお勧めする。

　なお、VS 2005におけるストアド・プロシージャ活用の基礎について学ぶには「連載：Visual Studio 2005でいってみようDBプログラミング第8回　Let's Master ストアド・プロシージャ！（前編）」および「同（後編）」をご一読いただきたい。以下ではストアド・プロシージャを理解しているものとして話を進める。

　VS 2005でテーブルアダプタを構成する際には、SELECT／INSERT／UPDATE／DELETE命令を実行するストアド・プロシージャを選択可能である。そこで、先ほどのユーザーViewに対するこれらのストアド・プロシージャを作成し、そこからテーブルアダプタを構成することにしよう（テーブルアダプタについては「連載：Visual Studio 2005によるWindowsデータベース・プログラミング　第3回テーブルアダプタの基礎」を参照してほしい）。

　まずはストアド・プロシージャを作成する。これには、［サーバーエクスプローラ］に表示されたストアド・プロシージャの項目を右クリックして［新しいストアドプロシージャの追加］をクリックすればよい。

　続いて、先ほどのユーザーViewに対してSELECT／INSERT／UPDATE／DELETE命令を発行するストアド・プロシージャを、以下のような内容で作成する。なおストアド・プロシージャの名前は、本稿ではSELECT／INSERT／UPDATE／DELETE命令の順に「ユーザー一覧」「ユーザー追加」「ユーザー更新」「ユーザー削除」とした。

CREATE PROCEDURE dbo.ユーザー一覧

AS

  SET NOCOUNT ON

  SELECT ID,

    姓, 名, ユーザー名, パスワード,

    電子メールアドレス, Webサイト,

    郵便番号, 都道府県, 市町村, 番地, 部屋番号,

    電話番号, 携帯電話番号, FAX番号,

    都道府県名

  FROM ユーザーView

  RETURN

ユーザーViewに対してSELECT命令を発行するストアド・プロシージャ「ユーザー一覧」

CREATE PROCEDURE dbo.ユーザー追加

  @姓 nvarchar(50), @名 nvarchar(50),

  @ユーザー名 nvarchar(50), @パスワード nvarchar(50),

  @電子メールアドレス nvarchar(50), @Webサイト nvarchar(50),

  @郵便番号 nvarchar(50), @都道府県 nvarchar(50),

  @市町村 nvarchar(50), @番地 nvarchar(50), @部屋番号 nvarchar(50),

  @電話番号 nvarchar(50), @携帯電話番号 nvarchar(50),

  @FAX番号 nvarchar(50)

AS

  SET NOCOUNT ON

  INSERT INTO ユーザー

    (姓, 名, ユーザー名, パスワード,

    電子メールアドレス, Webサイト,

    郵便番号, 都道府県, 市町村, 番地, 部屋番号,

    電話番号, 携帯電話番号, FAX番号)

  VALUES

    (@姓, @名, @ユーザー名, @パスワード,

    @電子メールアドレス, @Webサイト,

    @郵便番号, @都道府県, @市町村, @番地, @部屋番号,

    @電話番号, @携帯電話番号, @FAX番号)

  SELECT ID,

    姓, 名, ユーザー名, パスワード,

    電子メールアドレス, Webサイト,

    郵便番号, 都道府県, 市町村, 番地, 部屋番号,

    電話番号, 携帯電話番号, FAX番号,

    都道府県名

  FROM ユーザーView

  WHERE ID=@@IDENTITY

  RETURN

ユーザーViewに対してINSERT命令を発行するストアド・プロシージャ「ユーザー追加」

CREATE PROCEDURE dbo.ユーザー更新

  @ID int,

  @姓 nvarchar(50), @名 nvarchar(50),

  @ユーザー名 nvarchar(50), @パスワード nvarchar(50),

  @電子メールアドレス nvarchar(50), @Webサイト nvarchar(50),

  @郵便番号 nvarchar(50), @都道府県 nvarchar(50),

  @市町村 nvarchar(50), @番地 nvarchar(50), @部屋番号 nvarchar(50),

  @電話番号 nvarchar(50), @携帯電話番号 nvarchar(50),

  @FAX番号 nvarchar(50)

AS

  SET NOCOUNT ON

  UPDATE ユーザー

  SET

    姓= @姓, 名= @名,

    ユーザー名= @ユーザー名, パスワード= @パスワード,

    電子メールアドレス= @電子メールアドレス,

    Webサイト= @Webサイト,

    郵便番号= @郵便番号, 都道府県= @都道府県,

    市町村= @市町村, 番地= @番地, 部屋番号= @部屋番号,

    電話番号= @電話番号, 携帯電話番号= @携帯電話番号,

    FAX番号= @FAX番号

  WHERE (ID = @ID)

  RETURN

ユーザーViewに対してUPDATE命令を発行するストアド・プロシージャ「ユーザー更新」

CREATE PROCEDURE dbo.ユーザー削除

@ID int

AS

  SET NOCOUNT ON

  DELETE FROM ユーザー

  WHERE (ID = @ID)

  RETURN

ユーザーViewに対してDELETE命令を発行するストアド・プロシージャ「ユーザー削除」

　本稿のストアド・プロシージャを作成するためのSQLスクリプトは、以下のリンクからダウンロードできる。

SQLスクリプト（createstoredproc.sql）

　以上のストアド・プロシージャを作成すると、［サーバーエクスプローラ］の内容は次のようになる。

［サーバーエクスプローラ］に表示されたストアド・プロシージャ

　次に、作成したストアド・プロシージャでテーブルアダプタを構成する。

　具体的には、まずプロジェクトに「データセット（.xsdファイル）」を追加する（本稿では「ユーザーDataSet.xsd」というファイル名にした）。編集画面にデータセット・デザイナが表示されるので、その上で右クリックして［追加］－「TableAdapter」を選択する。［TableAdapter 構成ウィザード］が表示されるので、適切なデータベース（本稿では「Sample」）を選択して、［既存のストアドプロシージャを使用］を選び、以下の画面のように、先ほど作成したストアド・プロシージャをそれぞれ設定すればよい。これにより、ユーザー・テーブルの一覧（表示）／追加／更新／削除の一連の動作が可能になる。

［TableAdapter 構成ウィザード］におけるストアド・プロシージャの設定

4つの各コンボボックスで適切なストアド・プロシージャを選択し、［次へ］ボタンをクリックする。次のページで、（本稿の例では［DataTable を返す］のチェックを外して）［完了］ボタンをクリックする。

　なお、「Visual Studio 2005によるWindowsデータベース・プログラミング」では、［サーバーエクスプローラ］からテーブルをドラッグ＆ドロップしてデータテーブルを作成しているが、その場合はストアド・プロシージャではなくテーブルアダプタ内に文字列でSELECT／INSERT／UPDATE／DELETE命令の各SQL文が作成される。

　ここでは、追加されたデータセットの名前は「ユーザー」に、テーブルアダプタの名前は「ユーザーTableAdapter」とした（※ファイル名は前述したように「ユーザーDataSet.xsd」）。名前の変更は、データセット・デザイナでタイトル部分をクリックすれば行える。

　さらに、必要であればテーブルアダプタにクエリを追加する。本稿では次回以降の説明で使えるように、IDからユーザーを1件取得する「ユーザー取得」のためのFillByKeysメソッドと、姓／名／ユーザー名の入力されたものからユーザーを検索する「ユーザー検索」のためのFillBySearchConditionsメソッドをクエリとして追加した。

　実際にこれらのクエリを追加するには、データセット・デザイナに表示された「ユーザーTableAdapter」を右クリックして［クエリの追加］を実行する。これにより［TableAdapter クエリの構成ウィザード］が表示されるので、まず［新しいストアドプロシージャの作成］を、次に［複数行を返す SELECT］を選び、適切なSQL文を入力する。最後に、［新しいストアドプロシージャの名前］を入力し（「ユーザー取得」や「ユーザー検索」）、さらに［メソッド名］を入力し（「FillByKeys」や「FillBySearchConditions」）、最後に［DataTable を返す］のチェックを外して［完了］ボタンをクリックすればよい。それぞれのSQL文は以下のとおりだ。

SELECT ID,

  姓, 名, ユーザー名, パスワード, 電子メールアドレス, Webサイト,

  郵便番号, 都道府県, 市町村, 番地, 部屋番号,

  電話番号, 携帯電話番号, FAX番号, 都道府県名

FROM ユーザーView

WHERE ID = @ID

「ユーザー取得」FillByKeysメソッドのSQL文

SELECT ID,

  姓, 名, ユーザー名, パスワード, 電子メールアドレス, Webサイト,

  郵便番号, 都道府県, 市町村, 番地, 部屋番号,

  電話番号, 携帯電話番号, FAX番号, 都道府県名

FROM ユーザーView

WHERE (@姓 IS NULL OR @姓= '' OR 姓 LIKE '%' + @姓+ '%')

  AND (@名 IS NULL OR @名= '' OR 名 LIKE '%' + @名+ '%')

  AND (@ユーザー名 IS NULL OR @ユーザー名= '' OR

       ユーザー名 LIKE '%' + @ユーザー名+ '%')

「ユーザー検索」FillBySearchConditionsメソッドのSQL文

　以上の作業により、Sampleデータベースに「ユーザー取得」と「ユーザー検索」というストアド・プロシージャが新規に追加される。

INDEX
	実践で役立つ業務アプリ開発のヒント
	第1回データベースとADO.NETの機能を適切に活用する
	1．開発ヒント1：画面や印刷イメージに合致するビューの作成
	2．開発ヒント2：ストアド・プロシージャの作成
	3．開発ヒント3：テーブルアダプタでのメソッドの作成

　「実践で役立つ業務アプリ開発のヒント」

Insider.NET フォーラム新着記事

第2回　簡潔なコーディングのために （2017/7/26）
　ラムダ式で記述できるメンバの増加、throw式、out変数、タプルなど、C# 7には以前よりもコードを簡潔に記述できるような機能が導入されている
第1回　Visual Studio Codeデバッグの基礎知識 （2017/7/21）
　Node.jsプログラムをデバッグしながら、Visual Studio Codeに統合されているデバッグ機能の基本の「キ」をマスターしよう
第1回　明瞭なコーディングのために （2017/7/19）
　C# 7で追加された新機能の中から、「数値リテラル構文の改善」と「ローカル関数」を紹介する。これらは分かりやすいコードを記述するのに使える
Presentation Translator （2017/7/18）
　Presentation TranslatorはPowerPoint用のアドイン。プレゼンテーション時の字幕の付加や、多言語での質疑応答、スライドの翻訳を行える