目次＞第3章 サンプルアプリケーション構築 > 3.4 SQL文管理

# 3.4 SQL文管理

## 1 概要

本節では、Server Frameworkが提供するSQL文管理機能を用いて、SQL文を外部ファイルで管理する方法を学習する。
SQL文管理機能を用いることでSQL文を外部ファイルで一元管理できるようになり、画面のロジックに一切影響を与えずSQL文をチューニングすることが可能となる。詳細は、機能説明書『WC-02 SQL文管理機能』を参照のこと。
本サンプルでは、比較的使用頻度が高いと思われるTableAdapter内でSQL文を発行するパターンにおいて、どのようにSQL文管理機能が使用されるかを説明する。

### 1.1 動作イメージ

図1.1-1 動作イメージ

- 対象画面
  - 商品選択画面（SelectItemForm.aspx）
- 処理概要
  1. （クライアント）商品選択画面でカテゴリ選択ボタンを押下し、クリックイベントハンドラを実行する。
  2. （サーバ）クリックイベントハンドラから、ビジネスロジックを実行する。ビジネスロジックには画面で選択したカテゴリのIDを渡す。
  3. （サーバ）ビジネスロジックからTableAdapterを実行する。
  4. （サーバ）SQL文管理機能を用いて、SQL文を取得する。
  5. （サーバ）SQL文を発行し、Itemテーブルから指定されたカテゴリの商品情報を取得する。
  6. （クライアント）Itemテーブルから取得した商品情報を画面に表示する。

## 2 作業手順

- 作成イメージ
  1. データベースの確認
  2. データセットの確認
  3. SQL設定ファイルの設定
  4. TableAdapterの実装
  5. ビジネスロジックの作成
  6. ビジネスロジック設定ファイルの設定
  7. 画面の確認
  8. メニュー画面のイベントの実装

### 2.1 データベースの確認

本節では、データベースにアクセスして画面に表示する情報を取得する。
本節では、ブランクプロジェクトに最初から含まれている「App_Data\Database.mdf」をデータベースとして用いる。
「App_Data\Database.mdf」にはItemテーブルとCategoryテーブルが用意されおり、それぞれにデータが登録されている。

図2.1-1 データベース（Database.mdf）

#### 2.1.1 Itemテーブル

Itemテーブルのスキーマと登録データを以下に記す。

図2.1.1-1 Itemテーブル

図2.1.1-2 Itemテーブルに登録されているデータ

#### 2.1.2 Categoryテーブル

Categoryテーブルのスキーマと登録データを以下に記す。

図2.1.2-1 Categoryテーブル

図2.1.2-2 Categoryテーブルに登録されているデータ

### 2.2 データセットの確認

#### 2.2.1 商品情報取得用データセット（ビジネスロジック出力データセット）の確認

本節では、データベースのItemテーブルに登録された商品情報を取得するためのデータセットとして、ブランクプロジェクトに最初から含まれている「Dto\ItemDataSet.xsd」を利用する。本データセットは、ビジネスロジックの出力データセットとしても使用される。本データセットにはDataTable「Item」とそのTableAdapterが定義されている。
図2.2.1-1 商品情報取得用データセットの確認

表2.2.1-1 作成するデータセットのテーブル、カラム、データ型の一覧

DataTable]名 | Column(列)名 | DataType(型)
Item |  |
| ItemId | System.String
| ItemName | System.String

#### 2.2.2 ビジネスロジック入力データセットの確認

本節では、画面で選択されたカテゴリのIDを格納するデータセットとして、ブランクプロジェクトに最初から含まれている「SelectItemParamDataSet.xsd」を利用する。

図2.2.2-1 ビジネスロジック入力データセットの確認

表2.2.2-1 作成するデータセットのテーブル、カラム、データ型の一覧

DataTable名 | Column(列)名 | DataType(型)
SelectItemParam |  |
| CategoryId | System.String

### 2.3 SQL設定ファイルの設定

ポイント！

SQL文管理機能がSQL文を管理するために使用するSQL設定ファイル（Config\SqlConfiguration.config）に、商品情報を取得するSQL文の定義を追加する。
SQLIDには"selectItemByCategoryId"を指定する。
CDATAセクション内では改行などが自由に行えるので、SQL文を自由に整形できるメリットがある。
SQL文をSQL設定ファイルで管理することで、ソースコードを修正することなくSQL文の修正やチューニングが可能となる。

SqlConfiguration.config

    <?xml version="1.0" encoding="utf-8" ?>
    <!--SQL文設定ファイルの設定-->
    <sqlConfiguration xmlns="http://www.terasoluna.jp/schema/SqlSchema.xsd">

      <!-- カテゴリIDをもとに商品情報を取得する -->
      <sql name="selectItemByCategoryId">
        <![CDATA[
        SELECT * FROM Item WHERE CategoryId = @categoryId
        ]]>
      </sql>

    </sqlConfiguration>

### 2.4 TableAdapterの実装

ポイント！

TableAdapterクラスで、SQL文管理機能を用いて取得したSQL文を発行し、データベースから取得した商品情報を返却する処理を記述する。

図3.4-9 TableAdapterの概念図

#### 2.4.1 ItemDataSet.csの作成

ソリューションエクスプローラのItemDataSet.xsdの上で右クリック→コードの表示を選択し、ItemDataSet.csを新規作成する。
図3.4-10 ItemDataSet.xsd.csの作成

#### 2.4.2 TableAdapterのpartialクラスの作成

ItemDataSet.csを編集し、TableAdapterのpartialクラスを作成する。

ItemDataSet.cs

    namespace TutorialServerWeb.Dto
    {
        partial class ItemDataSet
        {
        }
    }

    namespace TutorialServerWeb.Dto.ItemDataSetTableAdapters
    {
        /// <summary>
        /// ItemDataSetのTableAdapter。
        /// </summary>
        public partial class ItemTableAdapter
        {

        }
    }

#### 2.4.3 TableAdapterにメソッドを追加する

TableAdapterにSelectCategoryメソッドを追加する。

ItemDataSet.cs

    namespace TutorialServerWeb.Dto.ItemDataSetTableAdapters
    {
        /// <summary>
        /// ItemDataSetのTableAdapter。
        /// </summary>
        public partial class ItemTableAdapter
        {
            /// <summary>
            /// 商品情報の一覧を取得する。
            /// </summary>
            /// <param name="categoryId">カテゴリID</param>
            /// <returns>商品情報テーブル</returns>
            [DataObjectMethod(DataObjectMethodType.Select)]
            public ItemDataSet.ItemDataTable SelectCategory(string categoryId)
            {
                // SQL文管理機能を用いて、外部ファイルからSQL文を取得する
                SqlCommand sqlCommand = new SqlCommand(SqlConfiguration.GetSql("selectItemByCategoryId"), this.Connection);

                sqlCommand.Parameters.AddWithValue("@categoryId", categoryId);

                SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);

                ItemDataSet.ItemDataTable dataTable = new ItemDataSet.ItemDataTable();

                // SQLを発行し、データベースから取得した商品情報をDataTableに格納する
                adapter.Fill(dataTable);

                return dataTable;
            }

        }
    }

SelectCategoryメソッドでは、Server Frameworkが提供するSqlConfigurationクラスのGetSqlメソッドを実行し、SQL設定ファイルからSQL文を取得する。
GetSqlメソッドの引数には、3. SQL設定ファイルの設定で定義したSQLID"selectItemByCategoryId"を設定する。
引数のカテゴリIDはSQL文のwhere句の条件として使用する。
データベースから取得した商品情報はDataTableに格納し、返却する。

### 2.5 ビジネスロジックの作成

ポイント！

4. TableAdapterの実装で作成したTableAdapterを呼び出し、商品情報の取得を行うビジネスロジックを新規作成する。ビジネスロジッククラスには、IBLogicインタフェースを実装させること。 画面の初期表示時および"すべて"のカテゴリが選択された場合は全ての商品情報を取得し、それ以外の場合は画面で選択されたカテゴリに属する商品情報を取得する。

図2.5-1 ビジネスロジックの概念図

#### 2.5.1 ビジネスロジックの作成

ビジネスロジック(BLogic\SelectItemBLogic.cs)を作成する。
図2.5.1-1 ビジネスロジックの作成

#### 2.5.2 ビジネスロジックの実装

IBLogicインターフェイスで定義しているExecuteメソッドに、TableAdapterを用いて商品情報を取得する処理を実装する。

SelectItemBLogic.cs

    using System;
    using System.Configuration;
    using System.Data;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using TERASOLUNA.Fw.Common.BLogic;
    using TutorialServerWeb.Dto;
    using TutorialServerWeb.Dto.ItemDataSetTableAdapters;

    namespace TutorialServerWeb.BLogic
    {
        /// <summary>
        /// 商品情報を取得するビジネスロジック。
        /// </summary>
        public class SelectItemBLogic : IBLogic
        {
            /// <summary>
            /// 商品情報を取得する。
            /// </summary>
            /// <param name="paramData">ビジネスロジック入力クラス。</param>
            /// <returns>ビジネスロジック出力クラス。</returns>
            public BLogicResult Execute(BLogicParam param)
            {
                // 引数に格納されたカテゴリIDを取得する
                SelectItemParamDataSet paramDataSet = (SelectItemParamDataSet)param.ParamData;
                string selectedCategoryId = paramDataSet.SelectItemParam[0].CategoryId;

                // TableAdapterを生成する
                ItemTableAdapter adapter = new ItemTableAdapter();
                ItemDataSet.ItemDataTable dataTable = new ItemDataSet.ItemDataTable();

                if ("all".Equals(selectedCategoryId))
                {
                    // TableAdapterを用いて、すべての商品情報を取得する
                    dataTable = adapter.GetData();
                }
                else
                {
                    // TableAdapterを用いて、画面で入力されたカテゴリIDの商品情報を取得する
                    dataTable = adapter.SelectCategory(selectedCategoryId);
                }

                // ビジネスロジック出力データセットを生成する
                ItemDataSet itemDataSet = new ItemDataSet();

                // TableAdapterの実行結果のDataTableを、ビジネスロジック出力データセットにマージする
                itemDataSet.Merge(dataTable);

                BLogicResult result = new BLogicResult(BLogicResult.SUCCESS, itemDataSet);

                return result;
            }
        }
    }

引数に格納されたカテゴリIDが"all"ならば、ItemTableAdapterが標準で提供するGetDataメソッドを呼び出し、すべての商品情報を取得する。カテゴリID"all"は、画面の初期表示時および"すべて"のカテゴリが選択された場合に設定される。
カテゴリIDが"all"以外なら、ItemTableAdapterに追加したSelectCategoryメソッドを呼び出し、カテゴリIDに属する商品情報を取得する。
ItemTableAdapterの実行結果のDataTableは、ビジネスロジック出力データセットにマージして返却する。

### 2.6 ビジネスロジック設定ファイルの設定

ビジネスロジック設定ファイル(Config\BLogicConfiguration.config)に、5. ビジネスロジックの作成で作成したビジネスロジックの定義を追加する。
ビジネスロジック名（name属性）には"selectItem"を、type属性には商品情報を選択するビジネスロジック（SelectItemBLogicクラス）のアセンブリ修飾名を設定する。

      <!-- 商品情報選択ビジネスロジッククラスの定義 -->
      <blogic name="selectItem" type="TutorialServerWeb.BLogic.SelectItemBLogic, TutorialServerWeb" />

完成後のビジネスロジック設定ファイルを以下に記す。

BLogicConfiguration.config

    <?xml version="1.0" encoding="utf-8"?>
    <!--ビジネスロジック設定ファイルの設定-->
    <blogicConfiguration xmlns="http://www.terasoluna.jp/schema/BLogicSchema.xsd">
      <!-- 掛け算ビジネスロジッククラスの定義 -->
      <blogic name="calc" type="TutorialServerWeb.BLogic.CalcBLogic, TutorialServerWeb" />
      <!-- 商品情報選択ビジネスロジッククラスの定義 -->
      <blogic name="selectItem" type="TutorialServerWeb.BLogic.SelectItemBLogic, TutorialServerWeb" />
    </blogicConfiguration>

### 2.7 画面の確認

本節では、ブランクプロジェクトに最初から含まれている商品選択画面「Form/SelectItemForm.aspx」を利用する。
画面に配置されたコントロールのプロパティやDropDownListのデータバインドも設定済みである。
ただしGridViewにはデータバインドが行われていないので、初期状態では何も表示されない。
データベースのItemテーブルから取得した商品情報をGridViewに表示する処理を実装するのが、本節の演習内容となる。なおItemテーブルの情報は、TableAdapterおよびSQL文管理機能を用いて取得する。

#### 2.7.1 商品選択画面の仕様

SelectItemForm.aspxに配置されたコントロールと、そのプロパティについての説明を以下に記す。

図2.7.1-1 配置したコントロール

表2.7.1-1 配置したコントロールのプロパティ設定

番号 | コントロール | IDプロパティ | Textプロパティ | Visibleプロパティ | ForeColorプロパティ | DataSourceIDプロパティ | DataTextFieldプロパティ | DataValueFieldプロパティ | AppendDataBoundItemsプロパティ
① | DropDownList | DropDownListCategory |  |  |  | SqlDataSourceItem | CategoryName | CategoryId | True
② | Button | ButtonCategory | カテゴリ選択 |  |  |  |  |  |
③ | SqlDataSource | SqlDataSourceItem |  |  |  |  |  |  |
④ | Label | LabelSelectError] | 最低１つは商品を選択してください。 | false | Red |  |  |  |
⑤ | GridView | GridViewItem | 計算 |  |  |  |  |  |
⑥ | Button | ButtonConfirm | 商品確認画面へ |  |  |  |  |  |
⑦ | Button | ButtonMenu | メニュー画面に戻る |  |  |  |  |  |

##### 2.7.1.1 DropDownListについて

画面に配置されたDropDownListは、データベースのCategoryテーブルに登録されたデータを表示する。 ただし、項目「すべて」はDropDownListのListItemとして登録している。
図2.7.1.1-1 DropDownListの項目

DropDownListはSqlDataSourceを用いてデータベースからカテゴリ情報を取得している。
SqlDataSourceはデータベースに対して以下のSQL文を発行する。

    SELECT * FROM [Category]

#### 2.7.2 商品情報選択画面のイベントの実装確認

「カテゴリ選択」ボタンのクリックイベントから、ビジネスロジックを実行し、実行結果を画面のGridViewに反映する処理の確認を行う。

図2.7.2-1 商品情報選択画面イベントの概念図

##### 2.7.2.1 「カテゴリ選択」ボタンのクリックイベントの実装確認

「カテゴリ選択」ボタンのクリックイベントから、商品情報取得ビジネスロジックを実行する。DropDownListで選択されたカテゴリに属する商品情報を取得する。

SelectItemForm.aspx.cs

    /// <summary>
    /// 画面で選択されたカテゴリに属する商品の情報を取得する。
    /// </summary>
    /// <param name="sender">イベントのソース。</param>
    /// <param name="e">イベントデータを格納している<see cref="EventArgs"/>。</param>
    protected void ButtonCategory_Click(object sender, EventArgs e)
    {
        ・・・省略・・・

        // 画面で選択されたカテゴリIDをもとに、ビジネスロジックを実行し、実行結果の商品情報をGridViewにバインドする。
        UpdateGridViewItem(DropDownListCategory.SelectedValue);

        ・・・省略・・・
    }

    /// <summary>
    /// 引数のカテゴリIDをもとにビジネスロジックを実行し、実行結果の商品情報をGridViewにバインドする。
    /// </summary>
    /// <param name="selectedCategoryId">カテゴリID</param>
    private void UpdateGridViewItem(string selectedCategoryId)
    {
        // ビジネスロジックの生成
        IBLogic blogic = BLogicFactory.CreateBLogic("selectItem");

        // ビジネスロジック入力データセットへのカテゴリIDの設定
        BLogicParam param = new BLogicParam();
        SelectItemParamDataSet paramDataSet = new SelectItemParamDataSet();
        paramDataSet.SelectItemParam.AddSelectItemParamRow(selectedCategoryId);

        param.ParamData = paramDataSet;

        // ビジネスロジックの実行
        BLogicResult result = blogic.Execute(param);

        if(BLogicResult.SUCCESS.Equals(result.ResultString)){
            ItemDataSet itemDataSet = (ItemDataSet)result.ResultData;

            // GridViewにデータベースから取得した商品情報をバインドする
            GridViewItem.DataSource = itemDataSet.Item;
            GridViewItem.DataBind();
        }
    }

「カテゴリ選択」ボタンのクリックイベントからUpdateGridViewItemメソッドを実行する。UpdateGridViewItemメソッドにロジックを外出ししているのは、Loadイベントでもこのロジックを使用するからである。
UpdateGridViewItemメソッドでは、まずBLogicFactoryクラスのCreateBLogicメソッドを実行し、商品情報ビジネスロジック（SelectItemBLogicクラス）を生成する。CreateBLogicメソッドの引数には6. ビジネスロジック設定ファイルの設定で設定したビジネスロジックID"selectItem"を設定する。
画面で選択されたカテゴリIDをビジネスロジック入力データセットに格納し、ビジネスロジック生成機能によって生成されたビジネスロジックを実行する。
ビジネスロジックの実行に成功した場合、ビジネスロジックの実行結果として返却される商品情報が格納されたDataTableを、GridViewにバインドしている。
ビジネスロジック生成機能の詳細は、機能説明書『CM-04 ビジネスロジック生成機能』を参照のこと。

##### 2.7.2.2 Loadイベントの実装確認

Loadイベントでは、画面の初期表示時に商品情報取得ビジネスロジックを実行し、画面に全ての商品情報を表示させる。

    /// <summary>
    /// 初期化イベント。
    /// </summary>
    /// <param name="sender">イベントのソース。</param>
    /// <param name="e">イベントデータを格納している<see cref="EventArgs"/>。</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        ・・・省略・・・

        if (!IsPostBack)
        {
            // 画面の初期表示時にカテゴリID"all"をもとに、ビジネスロジックを実行し、実行結果の商品情報をGridViewにバインドする。
            UpdateGridViewItem("all");

            ・・・省略・・・
        }
    }

7.2.1 カテゴリ選択ボタンのクリックイベントの実装確認で確認したUpdateGridViewItemメソッドを実行し、ビジネスロジックの実行結果として返却される商品情報が格納されたDataTableをGridViewにバインドしている。全ての商品情報を表示させるため、UpdateGridViewItemメソッドの引数にカテゴリID"all"を設定している。

### 2.8 メニュー画面のイベントの実装

#### 2.8.1 「商品選択画面」ボタンのクリックイベント

メニュー画面（MenuForm.aspx）に配置した「商品選択画面」ボタンのクリックイベントを追加する。
図2.8.1-1 「商品選択画面」ボタンのクリックイベントの追加

#### 2.8.2 「商品選択画面」ボタンのクリックイベントの実装

クリックイベントに「商品選択画面」に遷移するロジックを実装する。 IDには"SelectItemForm"を指定する。

MenuForm.aspx.cs

    /// <summary>
    /// 商品選択画面に遷移する。
    /// </summary>
    /// <param name="sender">イベントのソース。</param>
    /// <param name="e">イベントデータを格納している<see cref="EventArgs"/>。</param>
    protected void ButtonItemSelect_Click(object sender, EventArgs e)
    {
        WebUtils.Transit("SelectItemForm");
    }

## 3 動作確認

### 3.1 商品選択画面に遷移できることの確認

1. サーバアプリケーションを起動する(TutorialServerWebプロジェクトの発行)。
2. ブラウザのアドレス欄のURLに「http://localhost/tutorialServerWeb/Form/LogonForm.aspx」を入力し、移動ボタンをクリックし、正常にログオン画面が表示されることを確認する。
3. ログオン画面でユーザID "tera"、パスワード "soluna" と入力し、ログオンボタンを押下する。
4. メニュー画面で「商品選択画面」ボタンをクリックし、商品選択画面に遷移し、GridViewにItemテーブルに登録されたすべてのデータが表示されることを確認する。

図3.1-1 商品選択画面に遷移できることの確認

### 3.2 カテゴリに属する商品情報が表示されることの確認

1. 「カテゴリ選択」ボタンをクリックし、GridViewにカテゴリに属する商品が表示されることを確認する。

図3.2-1 カテゴリに属する商品情報が表示されることの確認

次節：3.5 セッション管理