カスタム ページングを適用したデータを並べ替える (C#)
前のチュートリアルでは、Web ページにデータを表示するときにカスタム ページングを実装する方法について説明しました。 このチュートリアルでは、前の例を拡張して、カスタム ページングの並べ替えのサポートを含める方法について説明します。
はじめに
カスタム ページングは、既定のページングと比較して、データをページングするパフォーマンスを数桁向上させることができます。これにより、大量のデータをページングするときに、カスタム ページングが事実上のページング実装の選択肢になります。 ただし、特にミックスに並べ替えを追加する場合は、カスタム ページングの実装は既定のページングを実装するよりも複雑です。 このチュートリアルでは、前の例から例を拡張して、並べ替え と カスタム ページングのサポートを含めます。
注意
このチュートリアルは前のチュートリアルに基づいているため、開始する前に、前のチュートリアルの Web ページ (EfficientPaging.aspx
) から 要素内<asp:Content>
の宣言構文をコピーし、ページ内の 要素の間<asp:Content>
にSortParameter.aspx
貼り付けます。 1 つの ASP.NET ページの機能を別のページにレプリケートする方法の詳細については、「 Editing and Inserting Interfaces 」チュートリアルへの検証コントロールの追加に関するチュートリアルの手順 1 を参照してください。
手順 1: カスタム ページング手法を再調整する
カスタム ページングを適切に機能させるには、Start Row Index パラメーターと Maximum Rows パラメーターを使用して、レコードの特定のサブセットを効率的に取得できる手法を実装する必要があります。 この目的を達成するために使用できる手法がいくつかあります。 前のチュートリアルでは、Microsoft SQL Server 2005 の新しいROW_NUMBER()
ランク付け関数を使用してこれを実現する方法について説明しました。 つまり、ランク付け関数は、 ROW_NUMBER()
指定された並べ替え順序でランク付けされたクエリによって返される各行に行番号を割り当てます。 レコードの適切なサブセットは、番号付き結果の特定のセクションを返すことによって取得されます。 次のクエリは、この手法を使用して、 で並べ替えられた結果をアルファベット順にランク付けするときに、11 から 20 の番号が付いた製品を返す方法を ProductName
示しています。
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
作成する必要がありますが、次のいずれかの回避策を使用します。
- 使用できる並べ替え式ごとにハードコーディングされたクエリを記述します。次に、T-SQL ステートメントを使用
IF/ELSE
して、実行するクエリを決定します。 - n 個の
CASE
入力パラメーターに基づいて動的ORDER BY
な式を指定するには、 ステートメントを@sortExpressio
使用します。詳細については 、T-SQLCASE
ステートメント の「クエリ結果を動的に並べ替えるために使用する」セクションを参照してください。 - ストアド プロシージャで適切なクエリを文字列として作成し、システム ストアド プロシージャを
sp_executesql
使用して動的クエリを実行します。
これらの回避策にはそれぞれ、いくつかの欠点があります。 最初のオプションは、可能な並べ替え式ごとにクエリを作成する必要があるため、他の 2 つのオプションほど保守可能ではありません。 したがって、後で新しい並べ替え可能なフィールドを GridView に追加する場合は、ストアド プロシージャに戻って更新する必要もあります。 2 番目の方法には、文字列以外のデータベース列による並べ替え時にパフォーマンスの問題が発生し、最初と同じ保守性の問題が発生する微妙な点もあります。 また、動的 SQL を使用する 3 番目の選択肢では、攻撃者が選択した入力パラメーター値を渡してストアド プロシージャを実行できる場合、SQL インジェクション攻撃のリスクが生じます。
これらのアプローチはどれも完璧ではありませんが、私は3番目の選択肢が3つの中で最高だと思います。 動的 SQL を使用すると、他の 2 つでは使用できないレベルの柔軟性が提供されます。 さらに、SQL インジェクション攻撃は、攻撃者が選択した入力パラメーターを渡してストアド プロシージャを実行できる場合にのみ悪用されます。 DAL ではパラメーター化されたクエリが使用されるため、ADO.NET はアーキテクチャを介してデータベースに送信されるパラメーターを保護します。つまり、SQL インジェクション攻撃の脆弱性は、攻撃者がストアド プロシージャを直接実行できる場合にのみ存在します。
この機能を実装するには、 という名前 GetProductsPagedAndSorted
の Northwind データベースに新しいストアド プロシージャを作成します。 このストアド プロシージャでは、@sortExpression
結果の並べ替え方法を指定し、句@maximumRows
@startRowIndex
内のテキストの直後ORDER BY
に挿入する 3 つの入力パラメーター (型nvarchar(100
) と、前のチュートリアルでOVER
調べたストアド プロシージャと同じ 2 つの整数入力パラメーターGetProductsPaged
を受け取る必要があります。 次のスクリプトを 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 テーブルからすべての行を取得するために使用した以前のクエリとは若干異なります。 前の例では、サブクエリを使用して、各製品に関連付けられているカテゴリ s と仕入先の名前を取得しました。 この決定は 、データ アクセス層の作成 に関するチュートリアルに戻り、 を使用 JOIN
する代わりに行われました。TableAdapter では、このようなクエリに関連する挿入、更新、および削除の各メソッドを自動的に作成できないためです。 ただし、ストアド プロシージャでは GetProductsPagedAndSorted
、カテゴリ名または仕入先名で並べ替える結果に s を使用 JOIN
する必要があります。
この動的クエリは、静的クエリ部分と 、@startRowIndex
、および @maximumRows
パラメーターを@sortExpression
連結することによって構築されます。 と @maximumRows
は整数パラメーターであるため@startRowIndex
、正しく連結するには nvarchars に変換する必要があります。 この動的 SQL クエリが構築されると、 を使用して sp_executesql
実行されます。
、、および @maximumRows
パラメーターに対して異なる値を指定して、このストアド プロシージャを@sortExpression
@startRowIndex
テストします。 [サーバー] エクスプローラーで、ストアド プロシージャ名を右クリックし、[実行] を選択します。 これにより、[ストアド プロシージャの実行] ダイアログ ボックスが表示され、入力パラメーターを入力できます (図 1 を参照)。 カテゴリ名で結果を並べ替えるには、パラメーター値に CategoryName を @sortExpression
使用します。仕入先の会社名で並べ替えるには、CompanyName を使用します。 パラメーター値を指定したら、[OK] をクリックします。 結果が [出力] ウィンドウに表示されます。 図 2 は、 で降順に並べ替えた場合に 11 から 20 位の製品を返す場合の UnitPrice
結果を示しています。
図 1: ストアド プロシージャの 3 つの入力パラメーターに対してさまざまな値を試す
図 2: ストアド プロシージャの結果が [出力] ウィンドウに表示されます (フルサイズの画像を表示する場合はクリックします)
注意
句でOVER
指定したORDER BY
列で結果をランク付けするときは、SQL Server結果を並べ替える必要があります。 これは、結果が並べ替えられている列にクラスター化インデックスがある場合、またはカバーするインデックスがある場合に簡単に行えますが、それ以外の場合はコストが高くなります。 十分に大きなクエリのパフォーマンスを向上させるには、結果の並べ替えの基になる列に非クラスター化インデックスを追加することを検討してください。 詳細については、「SQL Server 2005 のランク付け関数とパフォーマンス」を参照してください。
手順 2: データ アクセス層とビジネス ロジック 層の拡張
ストアド プロシージャを GetProductsPagedAndSorted
作成したら、次の手順として、アプリケーション アーキテクチャを使用してそのストアド プロシージャを実行する手段を提供します。 これには、DAL と BLL の両方に適切なメソッドを追加する必要があります。 まず、DAL にメソッドを追加します。 型指定された DataSet を Northwind.xsd
開き、 を ProductsTableAdapter
右クリックし、コンテキスト メニューから [クエリの追加] オプションを選択します。 前のチュートリアルで行ったように、既存のストアド プロシージャ ( GetProductsPagedAndSorted
この場合は ) を使用するように、この新しい DAL メソッドを構成します。 まず、新しい TableAdapter メソッドで既存のストアド プロシージャを使用することを示します。
図 3: 既存のストアド プロシージャの使用を選択する
使用するストアド プロシージャを指定するには、次の GetProductsPagedAndSorted
画面のドロップダウン リストからストアド プロシージャを選択します。
図 4: GetProductsPagedAndSorted ストアド プロシージャを使用する
このストアド プロシージャは、結果としてレコードのセットを返します。次の画面では、表形式のデータが返されることを示します。
図 5: ストアド プロシージャから表形式データが返されることを示す
最後に、Fill a DataTable パターンと Return a DataTable パターンの両方を使用する DAL メソッドを作成し、それぞれ メソッド FillPagedAndSorted
と GetProductsPagedAndSorted
という名前を付けます。
図 6: メソッド名の選択
DAL を拡張したので、BLL に切り替える準備ができました。 クラス ファイルを ProductsBLL
開き、新しいメソッド を追加します GetProductsPagedAndSorted
。 このメソッドは、3 つの入力パラメーター sortExpression
、、 startRowIndex
および maximumRows
を受け入れる必要があります。これは、次のように 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 をクリックすると、ポストバックが続き、次の手順が発生します。
- GridView は、ヘッダー リンクがクリックされたフィールドの
SortExpression
の値にプロパティを更新SortExpression
します - ObjectDataSource は BLL s
GetProductsPagedAndSorted
メソッドを呼び出し、GridView sSortExpression
プロパティをメソッドのsortExpression
入力パラメーターの値として渡します (適切なstartRowIndex
パラメーター値とmaximumRows
入力パラメーター値と共に)。 - BLL は DAL s
GetProductsPagedAndSorted
メソッドを呼び出します - DAL はストアド プロシージャを
GetProductsPagedAndSorted
実行し、 パラメーター (@sortExpression
および 入力パラメーターの値と@maximumRows
共に) を@startRowIndex
渡します。 - ストアド プロシージャは、データの適切なサブセットを BLL に返し、それを ObjectDataSource に返します。その後、このデータは GridView にバインドされ、HTML にレンダリングされ、エンド ユーザーに送信されます
図 7 は、 で昇順に並べ替えられた場合の結果の最初のページを UnitPrice
示しています。
図 7: 結果は UnitPrice で並べ替えられます (クリックするとフルサイズの画像が表示されます)
現在の実装では、製品名、カテゴリ名、単位あたりの数量、単価で結果を正しく並べ替えることができますが、仕入先名で結果を並べ替えようとすると、実行時例外が発生します (図 8 を参照)。
図 8: 次の実行時例外でサプライヤーの結果で結果を並べ替えようとする
この例外は、GridView の SupplierName
BoundField の が に設定されているためSortExpression
に発生しますSupplierName
。 ただし、テーブル内Suppliers
の仕入先の名前は、実際には、この列名の別名としてSupplierName
呼び出CompanyName
されています。 ただし、 OVER
関数で ROW_NUMBER()
使用される 句はエイリアスを使用できず、実際の列名を使用する必要があります。 そのため、BoundFields SortExpression
を SupplierName
SupplierName から CompanyName に変更します (図 9 を参照)。 図 10 に示すように、この変更後、結果は仕入先によって並べ替えることができます。
図 9: SupplierName BoundField の SortExpression を CompanyName に変更する
図 10: 結果を仕入先別に並べ替えられるようになりました (クリックするとフルサイズの画像が表示されます)
まとめ
前のチュートリアルで確認したカスタム ページングの実装では、結果の並べ替え順序をデザイン時に指定する必要がありました。 つまり、実装したカスタム ページング実装では、並べ替え機能を同時に提供できませんでした。 このチュートリアルでは、ストアド プロシージャを最初から拡張して、結果を並べ替えることができる入力パラメーターを含 @sortExpression
めることで、この制限を克服しました。
このストアド プロシージャを作成し、DAL と BLL で新しいメソッドを作成した後、GridView の現在 SortExpression
のプロパティを BLL SelectMethod
に渡すように ObjectDataSource を構成することで、並べ替えとカスタム ページングの両方を提供する GridView を実装できました。
幸せなプログラミング!
著者について
7 冊の ASP/ASP.NET 書籍の著者であり、 4GuysFromRolla.com の創設者である Scott Mitchell は、1998 年から Microsoft Web テクノロジと協力しています。 Scott は独立したコンサルタント、トレーナー、ライターとして働いています。 彼の最新の本は サムズ・ティーチ・自分自身 ASP.NET 24時間で2.0です。 にアクセスmitchell@4GuysFromRolla.comすることも、ブログを介して アクセスすることもできます。これは でhttp://ScottOnWriting.NET確認できます。
特別な感謝
このチュートリアル シリーズは、多くの役立つ校閲者によってレビューされました。 このチュートリアルのリード レビュー担当者は Carlos Santos でした。 今後の MSDN 記事の確認に関心がありますか? その場合は、 に行mitchell@4GuysFromRolla.comをドロップしてください。
フィードバック
https://aka.ms/ContentUserFeedback」を参照してください。
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「フィードバックの送信と表示