次の方法で共有

カスタム ページングを適用したデータを並べ替える (C#)

スコット・ミッチェル著

PDF をダウンロードする

前のチュートリアルでは、Web ページにデータを表示するときにカスタム ページングを実装する方法を学習しました。 このチュートリアルでは、前の例を拡張して、カスタム ページングの並べ替えのサポートを含める方法について説明します。

イントロダクション

カスタム ページングは、既定のページングと比較して、データを介したページングのパフォーマンスを数桁向上させることができます。これにより、大量のデータをページングするときに、事実上のページング実装の選択肢がカスタム ページングになります。 ただし、特にミックスに並べ替えを追加する場合は、カスタム ページングの実装が既定のページングを実装するよりも複雑です。 このチュートリアルでは、前の例の例を拡張して、並べ替え と カスタム ページングのサポートを含めます。

注

このチュートリアルは前のチュートリアルに基づいているため、開始する前に、前のチュートリアルの Web ページ (<asp:Content>) のEfficientPaging.aspx要素内の宣言構文をコピーし、<asp:Content> ページの SortParameter.aspx 要素の間に貼り付けます。 1 つの ASP.NET ページの機能を別のページにレプリケートする方法の詳細については、「 インターフェイスの編集と挿入 」チュートリアルへの検証コントロールの追加に関するチュートリアルの手順 1 を参照してください。

手順 1: カスタム ページング手法を再検討する

カスタム ページングを適切に機能させるには、開始行インデックスパラメーターと最大行数パラメーターを指定して、レコードの特定のサブセットを効率的に取得できる手法を実装する必要があります。 この目的を達成するために使用できる手法がいくつかあります。 前のチュートリアルでは、Microsoft SQL Server 2005 の新しい ROW_NUMBER() ランク付け関数を使用してこれを実現する方法について説明しました。 つまり、 ROW_NUMBER() ランク付け関数は、指定された並べ替え順序でランク付けされたクエリによって返される各行に行番号を割り当てます。 レコードの適切なサブセットは、番号付き結果の特定のセクションを返すことによって取得されます。 次のクエリは、結果を ProductNameでアルファベット順に順位付けするときに、この手法を使用して 11 から 20 の番号が付いた製品を返す方法を示しています。

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
        (ORDER BY ProductName) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

この手法は、特定の並べ替え順序 (この場合はアルファベット順に並べ替えられたProductName ) を使用したページングに適していますが、クエリを変更して、別の並べ替え式で並べ替えられた結果を表示する必要があります。 理想的には、上記のクエリを書き直して、 OVER 句で次のようにパラメーターを使用できます。

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
        (ORDER BY @sortExpression) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

残念ながら、パラメーター化された ORDER BY 句は使用できません。 代わりに、 @sortExpression 入力パラメーターを受け取るストアド プロシージャを作成する必要がありますが、次のいずれかの回避策を使用します。

  • 使用できる並べ替え式ごとにハードコーディングされたクエリを記述します。次に、 IF/ELSE T-SQL ステートメントを使用して、実行するクエリを決定します。
  • CASE ステートメントを使用して、ORDER BY n 個の入力パラメーターに基づいて動的な@sortExpressio式を指定します。詳細については、T-SQL CASE ステートメントの「クエリ結果の動的な並べ替えに使用する」セクションを参照してください。
  • ストアド プロシージャ内の文字列として適切なクエリを作成し、sp_executesql システム ストアド プロシージャを使用して動的クエリを実行します。

これらの回避策にはそれぞれ、いくつかの欠点があります。 最初のオプションは、可能な並べ替え式ごとにクエリを作成する必要があるため、他の 2 つのオプションほど保守可能ではありません。 したがって、後で新しい並べ替え可能なフィールドを GridView に追加する場合は、ストアド プロシージャに戻って更新する必要もあります。 2 番目の方法には、文字列以外のデータベース列による並べ替え時のパフォーマンスの問題が生じ、最初と同じ保守性の問題が発生する、いくつかの機微があります。 また、動的 SQL を使用する 3 番目の選択肢では、攻撃者が選択した入力パラメーター値を渡してストアド プロシージャを実行できる場合、SQL インジェクション攻撃のリスクが生じます。

これらのアプローチはどれも完璧ではありませんが、私は3番目の選択肢が3つの中で最高だと思います。 動的 SQL を使用すると、他の 2 つでは使用できないレベルの柔軟性が提供されます。 さらに、SQL インジェクション攻撃は、攻撃者が選択した入力パラメーターを渡してストアド プロシージャを実行できる場合にのみ悪用できます。 DAL はパラメーター化されたクエリを使用するため、ADO.NET はアーキテクチャを介してデータベースに送信されるパラメーターを保護します。つまり、SQL インジェクション攻撃の脆弱性は、攻撃者がストアド プロシージャを直接実行できる場合にのみ存在します。

この機能を実装するには、 GetProductsPagedAndSortedという名前の Northwind データベースに新しいストアド プロシージャを作成します。 このストアド プロシージャは、3 つの入力パラメーターを受け入れる必要があります。@sortExpression、nvarchar(100) 型の入力パラメーターです。このパラメーターは、結果の並べ替え方法を指定し、ORDER BY句のOVER テキストの直後に挿入します。@startRowIndexと@maximumRowsは、前のチュートリアルで調べたGetProductsPagedストアド プロシージャの同じ 2 つの整数入力パラメーターです。 次のスクリプトを使用して、 GetProductsPagedAndSorted ストアド プロシージャを作成します。

CREATE PROCEDURE dbo.GetProductsPagedAndSorted
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int
)
AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
    SET @sortExpression = 'ProductID'
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
            UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
            CategoryName, SupplierName
            FROM (SELECT ProductID, ProductName, p.SupplierID, p.CategoryID,
                    QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
                    ReorderLevel, Discontinued,
                  c.CategoryName, s.CompanyName AS SupplierName,
                   ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowRank
            FROM Products AS p
                    INNER JOIN Categories AS c ON
                        c.CategoryID = p.CategoryID
                    INNER JOIN Suppliers AS s ON
                        s.SupplierID = p.SupplierID) AS ProductsWithRowNumbers
            WHERE     RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) + ')'
-- Execute the SQL query
EXEC sp_executesql @sql

ストアド プロシージャは、 @sortExpression パラメーターの値が指定されていることを確認することから始まります。 見つからない場合、結果は ProductIDでランク付けされます。 次に、動的 SQL クエリが構築されます。 ここでの動的 SQL クエリは、Products テーブルからすべての行を取得するために使用した以前のクエリとは若干異なります。 前の例では、サブクエリを使用して、関連する各製品のカテゴリと仕入先の名前を取得しました。 この決定は 、データ アクセス層の作成 に関するチュートリアルに戻り、 JOIN を使用する代わりに行われました。TableAdapter では、このようなクエリに関連する挿入、更新、および削除のメソッドを自動的に作成できないためです。 ただし、 GetProductsPagedAndSorted ストアド プロシージャでは、カテゴリ名または仕入先名で並べ替える結果に JOIN s を使用する必要があります。

この動的クエリは、静的クエリ部分と、 @sortExpression、 @startRowIndex、および @maximumRows パラメーターを連結することによって構築されます。 @startRowIndexと@maximumRowsは整数パラメーターであるため、正しく連結するには nvarchars に変換する必要があります。 この動的 SQL クエリが構築されると、 sp_executesqlを介して実行されます。

このストアド プロシージャを、 @sortExpression、 @startRowIndex、および @maximumRows パラメーターに対して異なる値でテストします。 サーバー エクスプローラーでストアド プロシージャ名を右クリックし、[実行] を選択します。 これにより、[ストアド プロシージャの実行] ダイアログ ボックスが表示され、入力パラメーターを入力できます (図 1 を参照)。 カテゴリ名で結果を並べ替えるには、 @sortExpression パラメーター値に CategoryName を使用します。仕入先の会社名で並べ替えるには、CompanyName を使用します。 パラメーター値を指定したら、[OK] をクリックします。 結果が [出力] ウィンドウに表示されます。 図 2 は、 UnitPrice で降順に並べ替えたときに、11 から 20 の順位の製品を返した場合の結果を示しています。

ストアド プロシージャの 3 つの入力パラメーターに対して異なる値を試す

図 1: ストアド プロシージャの 3 つの入力パラメーターに対してさまざまな値を試す

ストアド プロシージャの結果が出力ウィンドウに表示される

図 2: ストアド プロシージャの結果が出力ウィンドウに表示されます (フルサイズの画像を表示する をクリックします)。

注

ORDER BY句で指定したOVER列で結果をランク付けする場合、SQL Server は結果を並べ替える必要があります。 これは、結果が並べ替えられている列にクラスター化インデックスがある場合、またはカバーインデックスがある場合に簡単な操作ですが、それ以外の場合はコストが高くなります。 十分に大きなクエリのパフォーマンスを向上させるには、結果の順序付けに使用する列に非クラスター化インデックスを追加することを検討してください。 詳細については、「 SQL Server 2005 のランク付け関数とパフォーマンス 」を参照してください。

手順 2: データ アクセス層とビジネス ロジック レイヤーの拡張

GetProductsPagedAndSorted ストアド プロシージャを作成したら、次の手順では、アプリケーション アーキテクチャを使用してそのストアド プロシージャを実行する手段を提供します。 これには、DAL と BLL の両方に適切なメソッドを追加する必要があります。 DAL にメソッドを追加することから始めましょう。 Northwind.xsd型指定されたデータセットを開き、ProductsTableAdapterを右クリックし、コンテキスト メニューから [クエリの追加] オプションを選択します。 前のチュートリアルで行ったように、既存のストアド プロシージャ (この場合は GetProductsPagedAndSorted) を使用するように、この新しい DAL メソッドを構成します。 まず、新しい TableAdapter メソッドで既存のストアド プロシージャを使用することを示します。

既存のストアド プロシージャの使用を選択する

図 3: 既存のストアド プロシージャの使用を選択する

使用するストアド プロシージャを指定するには、次の画面のドロップダウン リストから GetProductsPagedAndSorted ストアド プロシージャを選択します。

GetProductsPagedAndSorted ストアドプロシージャを利用する

図 4: ストアドプロシージャ GetProductsPagedAndSorted を利用する

このストアド プロシージャは、結果としてレコードのセットを返します。次の画面では、表形式のデータが返されることを示します。

ストアド プロシージャから表形式データが返されることを示す

図 5: ストアド プロシージャから表形式データが返されることを示す

最後に、Fill a DataTable パターンと Return a DataTable パターンの両方を使用する DAL メソッドを作成し、メソッドにそれぞれ FillPagedAndSorted と GetProductsPagedAndSortedの名前を付ける必要があります。

メソッド名の選択

図 6: メソッド名の選択

DAL を拡張したので、BLL に切り替える準備ができました。 ProductsBLL クラス ファイルを開き、新しいメソッド (GetProductsPagedAndSorted) を追加します。 このメソッドは、 sortExpression、 startRowIndex、 maximumRows の 3 つの入力パラメーターを受け入れる必要があり、次のように DAL の GetProductsPagedAndSorted メソッドを呼び出す必要があります。

[System.ComponentModel.DataObjectMethodAttribute(
    System.ComponentModel.DataObjectMethodType.Select, false)]
public Northwind.ProductsDataTable GetProductsPagedAndSorted(
    string sortExpression, int startRowIndex, int maximumRows)
{
    return Adapter.GetProductsPagedAndSorted
        (sortExpression, startRowIndex, maximumRows);
}

手順 3: SortExpression パラメーターを渡すように ObjectDataSource を構成する

DAL と BLL に、 GetProductsPagedAndSorted ストアド プロシージャを利用するメソッドが含まれるよう拡張した後、残っているのは、新しい BLL メソッドを使用するように SortParameter.aspx ページで ObjectDataSource を構成し、ユーザーが結果の並べ替えを要求した列に基づいて SortExpression パラメーターを渡すことです。

まず、ObjectDataSource の SelectMethod を GetProductsPaged から GetProductsPagedAndSorted に変更します。 これは、データ ソースの構成ウィザード、プロパティ ウィンドウ、または宣言構文を使用して直接実行できます。 次に、ObjectDataSource の SortParameterName プロパティの値を指定する必要があります。 このプロパティが設定されている場合、ObjectDataSource は GridView の SortExpression プロパティを SelectMethodに渡そうとします。 特に、ObjectDataSource は、名前が SortParameterName プロパティの値と等しい入力パラメーターを検索します。 BLL s GetProductsPagedAndSorted メソッドには sortExpression という名前の並べ替え式入力パラメーターがあるため、ObjectDataSource の SortExpression プロパティを sortExpression に設定します。

これら 2 つの変更を行った後、ObjectDataSource の宣言構文は次のようになります。

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
    SelectMethod="GetProductsPagedAndSorted" EnablePaging="True"
    SelectCountMethod="TotalNumberOfProducts" SortParameterName="sortExpression">
</asp:ObjectDataSource>

注

前のチュートリアルと同様に、ObjectDataSource の SelectParameters コレクションに sortExpression、startRowIndex、または maximumRows 入力パラメーターが含 まれていない ことを確認します。

GridView で並べ替えを有効にするには、GridView のスマート タグの [並べ替えを有効にする] チェック ボックスをオンにします。このチェック ボックスをオンにすると、GridView の AllowSorting プロパティが true に設定され、各列のヘッダー テキストが LinkButton としてレンダリングされます。 エンド ユーザーがいずれかのヘッダー LinkButtons をクリックすると、ポストバックが発生し、次の手順が発生します。

  1. GridView は、ヘッダーリンクがクリックされたフィールドのSortExpressionの値に、そのプロパティを更新します。
  2. ObjectDataSource は BLL s GetProductsPagedAndSorted メソッドを呼び出し、GridView の SortExpression プロパティをメソッドの sortExpression 入力パラメーターの値として渡します (適切な startRowIndex と maximumRows 入力パラメーターの値と共に)。
  3. BLL は DAL s GetProductsPagedAndSorted メソッドを呼び出します
  4. DAL は、GetProductsPagedAndSorted ストアド プロシージャを実行し、(@sortExpressionおよび@startRowIndex入力パラメーター値と共に) @maximumRows パラメーターを渡します。
  5. ストアド プロシージャは、データの適切なサブセットを BLL に返し、それを ObjectDataSource に返します。このデータは GridView にバインドされ、HTML にレンダリングされ、エンド ユーザーに送信されます

図 7 は、 UnitPrice で昇順に並べ替えられた場合の結果の最初のページを示しています。

結果は UnitPrice で並べ替えられます

図 7: 結果は UnitPrice で並べ替えられます (フルサイズの画像を表示する をクリックします)。

現在の実装では、製品名、カテゴリ名、ユニットあたりの数量、単価で結果を正しく並べ替えることができますが、サプライヤー名で結果を並べ替えようとすると、実行時例外が発生します (図 8 を参照)。

次のランタイム例外でサプライヤーの結果で結果を並べ替えようとしています

図 8: 次の実行時例外でサプライヤーの結果で結果を並べ替えようとしている

この例外は、GridView SortExpression BoundField のSupplierNameが SupplierName に設定されているために発生します。 ただし、Suppliers テーブル内の仕入先名は実際にはCompanyNameと呼ばれていますが、この列名にSupplierNameというエイリアスを付けました。 ただし、OVER関数で使用されるROW_NUMBER()句はエイリアスを使用できず、実際の列名を使用する必要があります。 そのため、 SupplierName BoundField の SortExpression を SupplierName から CompanyName に変更します (図 9 を参照)。 図 10 に示すように、この変更後、結果は仕入先で並べ替えることができます。

SupplierName BoundField s SortExpression を CompanyName に変更する

図 9: SupplierName BoundField の SortExpression を CompanyName に変更する

結果を仕入先別に並べ替えることができるようになりました

図 10: 結果を仕入先別に並べ替えられるようになりました (フルサイズの画像を表示する をクリックします)。

概要

前のチュートリアルで調べたカスタム ページング実装では、デザイン時に結果を並べ替える順序を指定する必要がありました。 つまり、実装したカスタム ページング実装では、並べ替え機能を同時に提供できませんでした。 このチュートリアルでは、ストアド プロシージャを最初から拡張して、結果を並べ替えることができる @sortExpression 入力パラメーターを含めることで、この制限を克服しました。

このストアド プロシージャを作成し、DAL と BLL で新しいメソッドを作成した後、並べ替えとカスタム ページングの両方を提供する GridView を実装できました。これには、GridView の現在の SortExpression プロパティを BLL SelectMethodに渡すように ObjectDataSource を構成します。

プログラミングに満足!

著者について

7 冊の ASP/ASP.NET 書籍の著者であり、4GuysFromRolla.com の創設者である Scott Mitchell は、1998 年から Microsoft Web テクノロジを使用しています。 Scott は、独立したコンサルタント、トレーナー、ライターとして働いています。 彼の最新の本は サムズ・ティーチ・セルフ ASP.NET 24時間で2.0です。 彼には mitchell@4GuysFromRolla.comで連絡できます。

特別な感謝

このチュートリアル シリーズは、多くの役に立つ校閲者によってレビューされました。 このチュートリアルのリード レビュー担当者は、Carlos Santos でした。 今後の MSDN の記事を確認することに関心がありますか? その場合は、mitchell@4GuysFromRolla.comにメッセージを送ってください。

前へ次へ