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

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

完成ソースコード

リスト1　完成版のストアドプロシージャ

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[LookupEmployees]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LookupEmployees2]
go
CREATE PROCEDURE [dbo].[LookupEmployees2]
    @LastName varchar(50)=null, @FirstName varchar(50)=null,
    @Address varchar(50)=null,  @City varchar(50)=null,
    @State varchar(2)=null,     @Zip varchar(50)=null,
    @StartRowIndex int, @MaxRows int,
    @AlphaChar varchar(1)=null,   @SortCol varchar(20)=null
AS
BEGIN
SET NOCOUNT ON
DECLARE @lPaging bit
IF  @AlphaChar is null
SET @lPaging = 0
else
SET @lPaging = 1
WITH CustListTemp AS
   (SELECT CustomerID, LastName, FirstName, Address, City, State,
           Zip, ROW_NUMBER() OVER (ORDER BY
CASE @SortCol
WHEN 'LASTNAME' THEN LastName + Firstname
WHEN 'ADDRESS' THEN Address
WHEN 'CITY'  THEN City + LastName + Firstname
WHEN 'STATE' THEN STATE + LastName + Firstname
WHEN 'ZIP'  THEN ZIP + LastName + Firstname
ELSE LastName + Firstname END) AS RowNum
FROM Customers
WHERE LastName LIKE '%'+COALESCE(@LastName,LastName)+'%' AND
   FirstName LIKE '%'+COALESCE(@FirstName,FirstName)+'%' AND
   Address LIKE '%'+COALESCE(@Address,Address)+'%' AND
   City LIKE '%'+COALESCE(@City,City)+'%' AND
   State LIKE '%'+COALESCE(@State,State)+'%' AND
   Zip LIKE '%'+COALESCE(@Zip,Zip)+'%'   )
SELECT TOP (@MaxRows) CustomerID, LastName, FirstName, Address,
   City, State, Zip, RowNum
FROM
   ( SELECT CustListTemp.*,
      (SELECT COUNT(*) from CustListTemp) AS RecCount
       FROM CustListTemp )CustList
       WHERE
       CASE
         WHEN @lPaging = 1 AND @SortCol= 'LASTNAME'  AND
            SUBSTRING(LastName,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 1 AND @SortCol= 'ADDRESS' AND
            SUBSTRING(Address,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 1 AND  @SortCol= 'CITY'  AND
            SUBSTRING(City,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 1 AND  @SortCol= 'STATE' AND
            SUBSTRING(State,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 1 AND  @SortCol= 'ZIP'  AND
            SUBSTRING(Zip,1,1) >= RTRIM(@AlphaChar) THEN 1
         WHEN @lPaging = 0 AND
            RowNum BETWEEN
               ( CASE @StartRowIndex WHEN -1 THEN
               ( RecCount ) - @MaxRows ELSE  @StartRowIndex END )
            AND
               ( CASE @StartRowIndex WHEN -1 then
               ( RecCount )- @MaxRows ELSE @StartRowIndex END) +
                   @MaxRows
            THEN 1 ELSE 0 END = 1
         END
GO

リスト2　カスタムDAL

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace daCustomer
{
   public class daCustomer : SimpleDataAccess.SimpleDataAccess
   {
      public dsCustomer GetCustomers(string FirstName,
         string LastName, string Address, string City,
         string State, string Zip, int StartRowIndex,
         int MaxRows, string AlphaChar, string SortCol)
      {
         if (AlphaChar == "")
            AlphaChar = null;
         List<SqlParameter> oSQLParms = new
            List<SqlParameter>();
         oSQLParms.Add(new SqlParameter("@LastName",
            LastName.Length > 0 ? LastName : null));
         oSQLParms.Add(new SqlParameter("@FirstName",
            FirstName.Length > 0 ? FirstName : null));
         oSQLParms.Add(new SqlParameter("@Address",
            Address.Length > 0 ? Address : null));
         oSQLParms.Add(new SqlParameter("@City",
            City.Length > 0 ? City : null));
         oSQLParms.Add(new SqlParameter("@State",
            State.Length > 0 ? State : null));
         oSQLParms.Add(new SqlParameter("@Zip",
            Zip.Length > 0 ? Zip : null));
         oSQLParms.Add(new SqlParameter("@startRowIndex",
            StartRowIndex));
         oSQLParms.Add(new SqlParameter("@MaxRows",  MaxRows));
         oSQLParms.Add(new SqlParameter("@alphachar", AlphaChar));
         oSQLParms.Add(new SqlParameter("@SortCol",SortCol ));
         dsCustomer odsCustomer = new dsCustomer();
         this.RetrieveDataIntoTypedDs(odsCustomer,
            "[dbo].[LookupEmployees]", oSQLParms);
         return odsCustomer;
      }
   }
}

リスト3　ページの分離コード

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
      if (!this.IsPostBack)
      {
         Session["CriteriaSet"] = false;
         string[] alphabet = new string[] { " ", "A", "B", "C",
            "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P",
            "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "0", "1", "2",
            "3", "4", "5", "6", "7", "8", "9" };
         for (int i = 0; i < alphabet.Length; i++)
            this.cboAlphaIndex.Items.Add(alphabet[i].Trim());
         this.InitializeVars();
      }
   }
   protected void btnRetrieve_Click(object sender, EventArgs e)
   {
      this.GetData();
   }
   private void SetInfo(daCustomer.dsCustomer odsCustomer)
   {
      int nResultCount = odsCustomer.dtCustomer.Rows.Count;
      if (nResultCount > 0)
      {
         Session["CurrentFirstRow"] = odsCustomer.dtCustomer[0].RowNum;
         Session["CurrentLastRow"] = odsCustomer.dtCustomer[
            nResultCount - 1].RowNum;
      }
      this.grdResults.Caption = "Number of matching records: "
         + nResultCount.ToString().Trim();
   }
   private void GetData()
   {
      string FirstName = this.txtFirstName.Text.ToString().Trim();
      string LastName = this.txtLastName.Text.ToString().Trim();
      string Address = this.txtAddress.Text.ToString().Trim();
      string City = this.txtCity.Text.ToString().Trim();
      string State = this.txtState.Text.ToString().Trim();
      string Zip = this.txtZip.Text.ToString().Trim();
      string AlphaChar = this.cboAlphaIndex.Text.ToString().Trim();
      int StartRowIndex = Convert.ToInt32(Session["StartRowIndex"]);
      int MaxRows = Convert.ToInt32(Session["MaxRows"]);
      string SortCol = Convert.ToString(Session["SortCol"]);
      daCustomer.daCustomer odaCustomer = new
         daCustomer.daCustomer();
      daCustomer.dsCustomer odsCustomer =
         odaCustomer.GetCustomers(FirstName, LastName,
         Address, City, State, Zip, StartRowIndex, MaxRows,
         AlphaChar, SortCol);
      this.SetInfo(odsCustomer);
      this.grdResults.DataSource = odsCustomer;
      this.grdResults.DataBind();
   }
   private void InitializeVars()
   {
      Session["startRowIndex"] = 0;
      Session["AlphaChar"] = null;
      Session["CurrentFirstRow"] = 0;
      Session["CurrentLastRow"] = 0;
      Session["MaxRows"] = 15;
      Session["SortCol"] = "LASTNAME";
   }
   protected void btnFirst_Click(object sender,
      ImageClickEventArgs e)
   {
      this.NavBegin();
      this.GetData();
   }
   protected void btnPrev_Click(object sender, ImageClickEventArgs e)
   {
      this.NavPrevious();
      this.GetData();
   }
   protected void btnNext_Click(object sender, ImageClickEventArgs e)
   {
      this.NavNext();
      this.GetData();
   }
   protected void btnLast_Click(object sender, ImageClickEventArgs e)
   {
      this.NavEnd();
      this.GetData();
   }
   private void NavBegin()
   {
      // set the startrowindex to zero, and make sure we're
      // not specifying a letter
      Session["startRowIndex"] = 0;
      Session["AlphaChar"] = null;
      this.cboAlphaIndex.SelectedIndex = 0;
   }
   private void NavPrevious()
   {
      // set the startrowindex to the row number for the first
      // record in the current page, minus 1, and minus maxrows
      // so if we're looking at rows 200-249, and we go back one
      // page, the new start row index would be 200-1-50, or
      // 149....and we'd get back 149-199
      Session["startRowIndex"] = (int)Session["CurrentFirstRow"] -
         (int)Session["MaxRows"];
      Session["AlphaChar"] = null;
      this.cboAlphaIndex.SelectedIndex = 0;
   }
   private void NavNext()
   {
      // startrow index becomes the value of the last row  [the
      // stored proc does a 'greater than']
      Session["startRowIndex"] = (int)Session["CurrentLastRow"] + 1;
      Session["AlphaChar"] = null;
      this.cboAlphaIndex.SelectedIndex = 0;
   }
   private void NavEnd()
   {
      // -1 is the 'magic number', it tells the stored proc to
      // just grab everything from rowcount-maxrows, to rowcount
      Session["startRowIndex"] = -1;
      Session["AlphaChar"] = null;
      this.cboAlphaIndex.SelectedIndex = 0;
   }
   protected void cboAlphaIndex_SelectedIndexChanged(
      object sender, EventArgs e)
   {
      this.GetData();
   }
   protected void grdResults_Sorting(object sender,
      GridViewSortEventArgs e)
   {
      Session["SortCol"] = e.SortExpression.ToString().Trim();
      this.GetData();
   }
   protected void grdResults_SelectedIndexChanged
                                (object sender, EventArgs e)
   {
      int nCustomerID = (int)this.grdResults.SelectedDataKey.Values[0];
      Response.Redirect("CustomerPage.aspx?CUSTID=" +
         nCustomerID.ToString().Trim());
   }
}

リスト4　XML文字列をテーブル変数に変換するT-SQL 2005 UDF

-- Table-valued UDF to convert an XML string
-- to a table-valued UDF
-- Useful if you have an XML string of user-selections,
-- and want to convert them to a Table variable that
-- you can use in subsequent JOIN statements
-- Uses the new XML data type
CREATE FUNCTION [dbo].[XMLtoTable]
   (@XMLString XML )
RETURNS
   @tPKList TABLE  ( IntPK int )   -- returns table variable
AS
BEGIN
INSERT INTO @tPKList
SELECT Tbl.col.value('.','int') as IntPK
FROM   @XMLString.nodes('//IDpk' ) Tbl(col)
-- use nodes() method to shred XML data into relational
-- data.  Incoming XML must have a numeric column
-- with the name IDpk
RETURN
END

リスト5　結果セットをカスタムオブジェクトに出力するジェネリックDALメソッド

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace SimpleDataAccess
{
   public class SimpleDataAccess
   {
      public void RetrieveIntoCollection<T>(
         List<T> oCollection , string cStoredProc,
         List<SqlParameter> oParmList, Type oCollectionType)
      {
         SqlConnection oSqlConn = this.GetConnection();
         SqlCommand oCmd = new SqlCommand(cStoredProc, oSqlConn);
         oCmd.CommandType = CommandType.StoredProcedure;
         foreach (SqlParameter oParm in oParmList)
            oCmd.Parameters.Add(oParm);
         oSqlConn.Open();
         SqlDataReader oDR = oCmd.ExecuteReader();
         while(oDR.Read())  {
            T oItem = (T)Activator.CreateInstance(oCollectionType);
            // get all the properties of the class
            PropertyInfo[] oCollectionProps =
               ((Type) oItem.GetType()).GetProperties();
            for (int n=0; n<oCollectionProps.Length; n++)  {
               string cPropName = oCollectionProps[n].Name;
               oCollectionProps[n].SetValue(oItem, oDR[cPropName], null);
            }
            oCollection.Add(oItem);
         }
         oSqlConn.Close();
      }
      public SqlConnection GetConnection()
      {
         SqlConnectionStringBuilder oStringBuilder = new
            SqlConnectionStringBuilder();
         oStringBuilder.UserID = "sa";
         oStringBuilder.Password = "";
         oStringBuilder.InitialCatalog = "NewCustomer";
         oStringBuilder.DataSource = "KCI890";
         return new SqlConnection(oStringBuilder.ConnectionString);
      }
   }
}

付録： コード集： 匿名メソッドによるカスタムリストのソート

　私はどちらかと言えばDataSet派ですが、.NETジェネリックのListクラスの有用さ、特にC# 2.0の新しい匿名メソッドと組み合わせた場合の威力は認めざるを得ません。この付録では、カスタムリストコレクションのソートとフィルタを行ういくつかのコードを紹介します。

　例えば、LocationID、Customer ID、およびAmount Dueの各フィールドから成るレコードのリストがあるものとします。Amount Dueが10000より大きいという条件で、Locations 1とLocations 2についてフィルタを実行します。また、その結果を、Location内でAmount Dueの降順にソートします。ADO.NETを使うと、次に示すように、DataViewでこれを実現できます。

DataView dv = new DataView(dt);
dv.RowFilter =
   "LocationID in (1,2) AND AmountDue > 10000";
dv.Sort = "LocationID, AmountDue DESC";

　「DataSet対カスタムコレクション」という議論の中で、DataSetの支持者は、カスタムコレクションで同じ機能を実現するには複雑なコードを記述しなければならないと主張します（Visual Studio 2005より前の時点では、私も確かにこのような主張をしていました）。

　しかし、Visual Studio 2005が提供する2つの新しい機能を組み合わせれば、上記のADO.NETコードに十分対抗できます。第一に、新しいListクラスには、ソートとフィルタのメソッドが用意されています（SortメソッドとFindAllメソッドを使用）。ソート/フィルタのカスタムメソッドを作成し、メソッドの名前を、Sort/FindAllメソッドのデリゲートパラメータとして指定します。

　第二に、C# 2.0では匿名メソッドを使ってデリゲートの代わりにロジックを配置できます。つまり、個別のカスタムメソッドを作成するのではなく、本来ならデリゲートが生じる位置に、インラインでコードを設定できます。いくつかのコードサンプルを紹介しましょう。DataSetの代わりに、CustomerRecという名前のカスタムリストの例を使用します。これは、LocationIDおよびAmountDueというプロパティを持ちます。

　コードでは、リストのFindAllメソッド内に匿名メソッドを挿入し、LocationIDが1に等しい顧客のフィルタリストを作成します。次に、フィルタリストをAmountDueの降順にソートします。

　Sortメソッドのデリゲートパラメータが2つのパラメータを受け取ることに注目してください。これは、ソート比較を構成する各オブジェクトインスタンスにそれぞれ対応します。匿名メソッドは、リスト内の各アイテムに対して実行されます。実行のたびに、コードは2つの入力値を比較し、.NETのCompareToメソッドを使って2つの値のうち大きい方の値を返します。昇順でソートを呼び出した場合は、最初のパラメータと2番目のパラメータを比較しますが、この例では降順で呼び出しているので、パラメータの使い方が反転します。

// anonymous method to filter on Location = 1
List<CustomerRec> oFilteredCustomers =
   oCustomerRecs.FindAll(
   (delegate(CustomerRec oRec)  {
   return (oRec.LocationID == 1 );})
   );

// anonymous method to sort on amount due DESC
// by reversing the incoming parameters
oFilteredCustomers.Sort(
   delegate(CustomerRec oRec1, CustomerRec oRec2)
   { return oRec2.AmountDue.CompareTo
      (oRec1.AmountDue); });

　ORとANDの組み合わせなど、もっと複雑なインラインコードを含めることができます。次のコードサンプルでは、Location 1または2、かつAmount Dueが10000より大きいという条件でデータをフィルタするADO.NETサンプルのロジックを再現します。

// anonymous method to filter on
// either Location 1 or 2, AND amount due GT 10000
List<CustomerRec> oFilteredCustomers =
   oCustomerRecs.FindAll((delegate(CustomerRec oRec)  {
   return (
      (oRec.LocationID == 1 || oRec.LocationID == 2)
      && oRec.AmountDue > 10000);
   }));

　最後のコードサンプルは、Location内でAmountの降順でフィルタリストをソートする匿名メソッドを示しています。デリゲートは、各入力比較に対応する2つのパラメータを受け取ります。Locationが等しい場合は、2番目のパラメータのAmount Dueを1番目のパラメータに対して比較します。Locationが等しくない場合は、1番目のパラメータのLocationIDを2番目のパラメータに対して比較します。

// Now sort on amount due DESC, within Location
// To do so, check the two incoming locations 1st
// If they are equal, reverse the order of two
// incoming parameters, and compare the amount due
// [just like above]
// If they AREN'T equal, compare the two locations
oFilteredCustomers.Sort(
   delegate(CustomerRec oRec1, CustomerRec oRec2)
   { return oRec1.LocationID == oRec2.LocationID ?
       oRec2.AmountDue.CompareTo(oRec1.AmountDue):
       oRec1.LocationID.CompareTo(oRec2.LocationID);
   });

　結局のところ、開発者は多少コードを記述する必要はありますが、新しいListクラスを使うことで、高度なソートとフィルタの機能を実装できるようになりました。加えて、匿名メソッドを実装できるようになったことで、ADO.NET構文を超えるカスタムフィルタを作成することが可能になりました（ADO.NETではカスタムフィルタメソッドのフックはサポートされていません）。

　この記事で紹介したソースコード全体は、私のWebサイトに掲載されています。詳細については、私のブログを参照してください。

最後に

　記事、論文、コードなどを投稿した後に、よいアイディアを思いついたことはありませんか。私は、後になっていろいろと思いつくのが得意です。幸いなことに、そういう場合はブログが重宝します。私がこれまで発表した記事の補足ヒントや注意に関しては、私のブログを参照してください。場合によっては、お楽しみが見つかるかもしれません。