ASP.NETでのデータベース検索ページの作成に役立つ13のヒント

C# 2.0、.NETジェネリック、ストアドプロシージャを組み合わせた手法

ヒント12： 可変個数の選択の許可

　最善の努力を払ったとしても、ユーザーは新たな要望を抱えて舞い戻ってきます。人数情報のフィルタに加えて、ルックアップテーブルから顧客の会計情報（支払日、延滞30日など）をフィルタすることも要求されます。エンドユーザーは、1つのステータス、複数のステータス、またはすべてのステータスを選択する可能性があります。ステータスコードは、Customerテーブルに存在します。

　これは、問題への取り組みのプロセスを繰り返す良い練習です。この練習は、可変個数の選択を処理する再利用可能な手法も示しています。

• Webページを変更して、複数の選択に対応できるデータバインドコントロールを含めます。開発者によっては上級のサードパーティコントロールを選ぶかもしれませんが、この例では単純なASP.NET 2.0 CheckedListBox Webコントロールを使います。
• 選択した値のCheckedListBoxコントロールをXML文字列に読み取るコードを作成します。
• データアクセスクラスを変更して、このXML文字列をパラメータとしてストアドプロシージャに渡します。
• 最後に、ストアドプロシージャを変更してXMLパラメータを処理します。

　上のタスクは、ユーザーインターフェイスで始まり、データベースで終わっているため、その順序で変更を進めようと思いがちです。しかし、バックエンドから始めて、フロントエンドに向かって作業する方が、わずかながら効率的です。設計は外側から内側に向かって行い、開発は内側から外側に向かって行います。

　最初に、XMLパラメータをストアドプロシージャに追加します。XML文字列は再び、ユーザーが選択するステータスの列を格納します。SQL Server 2005は新しいXMLデータ型をサポートするため、このプロセスは非常に簡単に行われます。Customerテーブルへのジョインでは、ストアドプロシージャはXML文字列をテーブル変数に変換します。

　SQL Server 2005より前のバージョンでは、開発者はしばしば、sp_xml_preparedocumentシステムストアドプロシージャとOPENXML関数を使って、XML選択をSQLテーブルに変換していました。この方法は今でも有効ですが、2つの問題を抱えています。第一に、メモリ負荷が大きくなります。ストアドプロシージャsp_xml_preparedocumentは、COM XMLドキュメントオブジェクトモデルへのメモリポインタを返すため、大規模なXMLドキュメント上でOPENXMLを使うとサーバに負担がかかります。第二に、sp_xml_preparedocumentをT-SQLユーザー定義関数の内部で使用することはできません。そのため、このタスク用の汎用的で再利用可能なSQL機能を開発することは困難です。

　SQL Server 2005の新しいXML機能が反映されているのは、XQueryと呼ばれる一般的な機能と、nodes()という特別なメソッドです。これらを使うと、XML文字列から特定のデータをテーブルに切り出すことができます。この機能は使用するリソースが少なくて済み、1行のコードでタスクを実現できます。

　リスト4には、XMLtoTableと呼ばれるT-SQL 2005テーブル値UDFが含まれています。このUDFは、XML文字列をパラメータとして受け取り、IDpk列内のXML文字列に格納されている整数キーを含むテーブル変数を返します。XML文字列内の選択列の名前を可変にするには、UDFを変更して、列の名前をパラメータとして渡します。

　リスト4内のコードでは、nodes()メソッドを使って、XML文字列のIDpkノードを先頭にクエリを実行し、結果をテーブル/（列）形式でエイリアスに格納します。そこから、SELECT文はvalue関数を使ってIDpkの値を整数の結果セットに読み込みます。

INSERT INTO @tPKList
SELECT Tbl.col.value('.','int') as IntPK
FROM   @XMLString.nodes('//IDpk' ) Tbl(col)

　このUDFを、Customersテーブルに対するメインクエリに組み込むことができます。

-- uses the new parameter XMLString
-- that contains the list of statuses
SELECT <column list>
FROM Customers
   JOIN [dbo].[XMLtoTable](@XMLString) StatusList
ON StatusList.IntPK = customers.statusfk

　データベースの処理は終わりました。この後の処理はずっと簡単です。リスト2内のデータアクセスクラスを変更して、新しいパラメータを含めます。

// code also adds XMLStatuses as a parameter
// to GetCustomers in Listing 4
oSQLParms.Add(
new SqlParameter("@XMLString", XMLStatuses));

　あと一息です。最後のステップでは、CheckedListBoxコントロールを割り当て、それを読み取ることによって、Webページを処理します。

　Webページには、コントロールを割り当てるコードが必要です。デモ上の目的で、コードはDataTableを手動で読み込みます。実際には、顧客ステータスコードのバックエンドデータベースを読み取るデータクラスから得られます。

DataTable dtStatus = new DataTable();
dtStatus.Columns.Add("StatusPK", typeof(Int32));
dtStatus.Columns.Add("Descript", typeof(String));
dtStatus.Rows.Add(1, "Up to Date");
dtStatus.Rows.Add(2, "Overdue 30 days");
dtStatus.Rows.Add(3, "Overdue 60 days");
dtStatus.Rows.Add(4, "Overdue 90 days");
dtStatus.Rows.Add(5, "Overdue 120 days");
dtStatus.Rows.Add(6, "Account suspended");
// Set the data binding, and the text/value fields
this.chkStatusList.DataSource = dtStatus;
this.chkStatusList.DataTextField = "descript";
this.chkStatusList.DataValueField = "statuspk";
this.chkStatusList.DataBind();

　最後に、ユーザーが選択したアイテムのCheckedListBoxを読み取り、XML文字列を返すコードを作成する必要があります。次のコードでは、一時DataSetを作成し、コントロール内のアイテムのコレクションを通じて読み取りを行い、選択されたアイテムを一時DataSetに挿入します。次に、GetXMLメソッドを使って、選択アイテムが含まれる一時DataSetのXML表現を返します。

private string GetStatuses()
{
   DataTable dtSelected = new DataTable();
   dtSelected.Columns.Add("IDpk", typeof(Int32));
   foreach (ListItem oItem in this.chkStatusList.Items)
      if (oItem.Selected == true)
         dtSelected.Rows.Add(Convert.ToInt32(oItem.Value));
   DataSet ds = new DataSet();
   ds.Tables.Add(dtSelected);
   return ds.GetXml();
}

ヒント13： カスタムオブジェクトに出力するようにDALを変更

　ヒント6で述べたように、2006年9月/10月号の『CoDe Magazine』に掲載された「Baker's Dozen」の記事では、TableAdapterまたはDataSetのMerge関数を使わずに、.NETジェネリックを利用して、型指定されたDataSetをストアドプロシージャから直接読み込むコードを紹介しました。

　カスタムコレクションに「均等な時間」を与えるために、.NETジェネリックを使った基本メソッドをデータアクセス層に追加しました。メソッドReadIntoCollection（リスト5を参照）はストアドプロシージャを実行し、結果を直接カスタムコレクションに出力します。例えば、前のヒントの例で、型指定されたDataSetではなく、カスタムコレクションを使うものとします。

List<SqlParameter> oSQLParms = new List<SqlParameter>();
oSQLParms.Add(new SqlParameter("@LastName", LastName));
List<CustomerClass> oCustomers = new List<CustomerClass>();
// pass an instance of the list, SP name/parms,
// and a type reference to the class
this.ReadIntoCollection(oCustomers, "[dbo].[LookupEmployees]",
   oSQLParms, typeof(CustomerClass));

　このメソッドの内部では何が起きているのでしょうか。

　はじめに、メソッド内のパラメータを見てみましょう。呼び出し元の関数が、CustomerClassアイテムリストのインスタンスを基本メソッドに渡す際、基本メソッドは、この特定のクラスについて何も知りません。ここでは、パラメータ定義で.NETジェネリックを使うことができます。先頭のパラメータとしてList oCollectionを指定することで、任意の種類の有効なListを渡すことができます。2番目のパラメータと3番目のパラメータ（ストアドプロシージャ名およびSQLパラメータのリスト）は、ヒント6と同じです。最後のパラメータは、クラス自体、つまりCustomerClassへの型参照です。リストを読み込むときにクラスのプロパティを反復するため、基本メソッドにはこのパラメータが必要です。

public void ReadIntoCollection<T>List<T> oCollection,
   string cStoredProc, List<SqlParameter> oParmList,
   Type oCollectionType)

　次に、基本メソッドは接続を開き、ストアドプロシージャのコマンドオブジェクトを定義します。そして、SQLパラメータを再び確立します。これは、DataSetメソッドと非常によく似ています。

SqlConnection oSqlConn = this.GetConnection();
SqlCommand oCmd = new SqlCommand(cStoredProc, oSqlConn);
oCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter oParm in oParmList)
   oCmd.Parameters.Add(oParm);

　今回は、.NET DataAdapterのFillメソッドを使うのではなく、接続を開き、DataReaderを実行します。リーダーを反復してカスタムリストを読み込むことが目的です。

oSqlConn.Open();
SqlDataReader oDR = oCmd.ExecuteReader();

　次の一連のステップでは、リーダーを通じてTypeパラメータを使ってクラスのインスタンスを作成し、クラスのすべてのプロパティを決定します。リーダーからクラスインスタンスへそのプロパティ名の実際の値を読み取り、リストを作成します。ずいぶんと複雑そうです。まるで昔の、「大きな象を食べるにはどうすればいい？」「一口ずつ食べていけばいい」という話を思い出します。

　まずこのコードでは、リーダーオブジェクトを使ってループを設定し、クラスタイプのインスタンスを作成します。パラメータが.NETジェネリックを利用してクラスパラメータを定義しているため、コードではTプレースホルダを使ってクラスのインスタンスを指定できます。このコードをデバッガで調べると、oItemがCustomerClass型のクラスであることが分かります。

while(oDR.Read())  {
   T oItem = (T)Activator.CreateInstance(
      oCollectionType);

　次に、コードは、クラスのすべてのプロパティを検出するのに.NETリフレクションを多少使う必要があります。oItemのすべてのプロパティを、GetPropertiesを使ってPropertyInfo型の配列に読み込みます。配列oCollectionPropsを呼び出します。

// get all the properties of the class
PropertyInfo[] oCollectionProps = (
   (Type) oItem.GetType()).GetProperties();

　クラスのプロパティの配列ができたため（oCollectionProps）、その配列を反復処理し、プロパティの名前を取得し、リフレクションメソッドSetValueを使って、DataReaderからoItem内の特定のプロパティの値を設定できます。

for (int n=0; n<oCollectionProps.Length; n++)
{
   string cPropName = CollectionProps[n].Name;
   oCollectionProps[n].SetValue
      (oItem, oDR[cPropName], null);
}
// Add the item to the collection
oCollection.Add(oItem);
// Now get the next row in the DataReader

　よく理解できない場合は、まずジェネリックを使わないコードを考えてみてから、ジェネリックを使用するコードと比較してみましょう。

   while(oDR.Read())  {
      CustomerClass oCustomer = new CustomerClass();
      // no need to loop through properties, we
      // know what they are
      oCustomerClass.FirstName = oDR["FirstName"];
      oCustomerClass.LastName = oDR["LastNName"];
   }
   oCollection.Add(oItem);
}