カスタム ページングを適用したデータを並べ替える (VB)
前のチュートリアルでは、Web ページにデータを表示するときにカスタム ページングを実装する方法を学習しました。 このチュートリアルでは、前の例を拡張して、カスタム ページングの並べ替えをサポートする方法について説明します。
はじめに
既定のページングと比較して、カスタム ページングでは、データ経由のページングのパフォーマンスを数桁向上させることができます。これにより、大量のデータをページングするときに、カスタム ページングが事実上のページング実装の選択肢になります。 ただし、特にミックスに並べ替えを追加する場合は、カスタム ページングの実装は既定のページングの実装より複雑になります。 このチュートリアルでは、前のチュートリアルの例を拡張して、 と を並べ替える カスタム ページングをサポートします。
Note
このチュートリアルは前のチュートリアルに基づいて構築されているため、開始する前に、前のチュートリアルの Web ページ (EfficientPaging.aspx) から <asp:Content> 要素内の宣言構文をコピーし、SortParameter.aspx ページ内の <asp:Content> 要素の間に貼り付けます。 ある 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/ELSET-SQL ステートメントを使用して、実行するクエリを決定します。 CASEステートメントを使用して、@sortExpression 個の入力パラメーターに基づいて動的なORDER BY式を提供します。詳細については、T-SQLCASEステートメントのクエリ結果の動的な並べ替えへの使用に関するページを参照してください。- 適切なクエリをストアド プロシージャ内の文字列として作成し、
sp_executesql個のシステム ストアド プロシージャを使用して動的クエリを実行します。
これらの回避策にはそれぞれ、いくつかの欠点があります。 最初のオプションは可能性のある並べ替え式ごとにクエリを作成する必要があるため、他の 2 つのオプションほど保守しやすいものではありません。 したがって、後で新しい並べ替え可能なフィールドを GridView に追加する場合は、ストアド プロシージャに戻って更新する必要もあります。 2 番目のアプローチには、文字列以外のデータベース列で並べ替えた場合にパフォーマンスの問題が発生し、1 つ目と同じ保守しやすさの問題が発生する微妙な点があります。 また、3 番目の選択肢は動的 SQL を使用するもので、攻撃者が選択した入力パラメーター値を渡してストアド プロシージャを実行できる場合、SQL インジェクション攻撃のリスクが生じます。
これらのアプローチはどれも完璧ではありませんが、3 つの中で 3 番目の選択肢を最もお勧めできます。 動的 SQL を使用することで、他の 2 つにはないレベルの柔軟性が提供されます。 さらに、SQL インジェクション攻撃は、攻撃者が選択した入力パラメーターを渡してストアド プロシージャを実行できる場合にのみ悪用できます。 DAL はパラメーター化されたクエリを使用するため、ADO.NET はアーキテクチャ経由でデータベースに送信されるパラメーターを保護します。つまり、SQL インジェクション攻撃の脆弱性は、攻撃者がストアド プロシージャを直接実行できる場合にのみ存在します。
この機能を実装するには、Northwind データベースに GetProductsPagedAndSorted という名前の新しいストアド プロシージャを作成します。 このストアド プロシージャは、次の 3 つの入力パラメーターを受け入れる必要があります: @sortExpression (nvarchar(100 型の入力パラメーター) は、結果を並べ替える方法を指定し、OVER 句の ORDER BY テキストの直後に挿入されます。@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 クエリは、[製品] テーブルからすべての行を取得するために使用した以前のクエリとは若干異なることに注意してください。 前の例では、サブクエリを使用して、各製品に関連するカテゴリ名とサプライヤー名を取得しました。 TableAdapter では、このようなクエリに関連する挿入、更新、および削除メソッドを自動的に作成できないため、この決定は、データ アクセス層の作成に関するチュートリアルに戻り、JOIN を使用する代わりに行われました。 ただし、GetProductsPagedAndSorted ストアド プロシージャでは、カテゴリ名またはサプライヤー名で並べ替える結果に JOIN を使用する必要があります。
この動的クエリは、静的クエリ部分と、@sortExpression、@startRowIndex、および @maximumRows パラメーターを連結することによって構築されます。 @startRowIndex と @maximumRows は整数パラメーターであるため、正しく連結するには、nvarchars に変換する必要があります。 この動的 SQL クエリが構築されると、sp_executesql を介して実行されます。
@sortExpression、@startRowIndex、および @maximumRows パラメーターの値を変えて、このストアド プロシージャをテストしてみましょう。 サーバー エクスプローラーから、ストアド プロシージャ名を右クリックし、[実行] を選択します。 これにより、[ストアド プロシージャの実行] ダイアログ ボックスが表示され、入力パラメーターを入力できます (図 1 参照)。 カテゴリ名で結果を並べ替えるには、@sortExpression パラメーター値に CategoryName を使用します。サプライヤーの会社名で並べ替えるには、CompanyName を使用します。 必要なパラメーター値を入力したら [OK] をクリックします。 結果が出力ウィンドウに表示されます。 図 2 は、UnitPrice を降順に並べ替えたときに 11 から 20 にランク付けされた製品を返す場合の結果を示しています。
図 1: さまざまな値を試すストアド プロシージャの 3 つの入力パラメーター
図 2: 出力ウィンドウに表示されたストアド プロシージャの結果 (クリックするとフルサイズの画像が表示されます)
Note
OVER 句で指定した ORDER BY 列で結果をランク付けする場合、SQL Server は結果を並べ替える必要があります。 この操作は、結果が並べ替えられている列にクラスター化インデックスがある場合、またはカバリング インデックスがある場合は短時間でできますが、それ以外の場合はコストが高くなります。 十分に大きなクエリのパフォーマンスを向上させるには、結果を並べ替える列に非クラスター化インデックスを追加することを検討してください。 詳細については、SQL Server 2005 のランク付け関数とパフォーマンスに関するページを参照してください。
手順 2: データ アクセス層とビジネス ロジック層の拡張
GetProductsPagedAndSorted ストアド プロシージャが作成されたので、次の手順では、アプリケーション アーキテクチャを通じてそのストアド プロシージャを実行する手段を提供します。 これには、DAL と BLL の両方に適切なメソッドを追加する必要があります。 まずは DAL にメソッドを追加しましょう。 Northwind.xsd 型指定されたデータセットを開き、ProductsTableAdapter を右クリックし、コンテキスト メニューから [クエリの追加] オプションを選択します。 前のチュートリアルで行ったように、この場合は既存のストアド プロシージャ (GetProductsPagedAndSorted) を使用するように、この新しい DAL メソッドを構成します。 まず、新しい TableAdapter メソッドで既存のストアド プロシージャを使用することを示します。
図 3: 既存のストアド プロシージャの使用の選択
使用するストアド プロシージャを指定するには、次の画面のドロップダウン リストから GetProductsPagedAndSorted ストアド プロシージャを選択します。
図 4: GetProductsPagedAndSorted ストアド プロシージャの使用
このストアド プロシージャは、その結果としてレコードのセットを返します。次の画面は、表形式のデータを返すことを示しています。
図 5: 表形式データを返すことを示すストアド プロシージャ
最後に、[DataTable にデータを格納する] パターンと [DataTable を返す] パターンの両方を使用する DAL メソッドを作成し、メソッドにそれぞれFillPagedAndSorted と GetProductsPagedAndSorted という名前を付けます。
図 6: メソッド名の選択
DAL を拡張したので、BLL に切り替える準備ができました。 ProductsBLL クラス ファイルを開き、新しいメソッド (GetProductsPagedAndSorted) を追加します。 このメソッドは、3 つの入力パラメーター (sortExpression、startRowIndex、maximumRows) を受け入れる必要があり、次に示すように、単純に DAL の GetProductsPagedAndSorted メソッドを呼び出す必要があります。
<System.ComponentModel.DataObjectMethodAttribute( _
System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsPagedAndSorted(ByVal sortExpression As String, _
ByVal startRowIndex As Integer, ByVal maximumRows As Integer) _
As Northwind.ProductsDataTable
Return Adapter.GetProductsPagedAndSorted(sortExpression, startRowIndex, maximumRows)
End Function
手順 3: SortExpression パラメーターを渡すための ObjectDataSource の構成
GetProductsPagedAndSorted ストアド プロシージャを利用するメソッドを含めるように DAL と BLL 拡張したので、あとは新しい BLL メソッドを使用するために SortParameter.aspx ページで ObjectDataSource を構成し、ユーザーが結果の並べ替えを要求した列に基づいて SortExpression パラメーターを渡すだけです。
まず、ObjectDataSource の SelectMethod を GetProductsPaged から GetProductsPagedAndSorted に変更します。 これは、データソースの構成ウィザード、プロパティ ウィンドウ、または宣言構文を通じて直接実行できます。 次に、ObjectDataSource の SortParameterName プロパティ に値を指定する必要があります。 このプロパティが設定されている場合、ObjectDataSource は GridView の SortExpression プロパティを SelectMethod に渡そうとします。 特に、ObjectDataSource は、SortParameterName プロパティの値と同じ名前の入力パラメーターを検索します。 BLL の 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>
Note
前のチュートリアルと同様に、ObjectDataSource の SelectParameters コレクションに sortExpression、startRowIndex、または maximumRows 入力パラメーターが含まれてないことを確認します。
GridView で並べ替えを有効にするには、GridView のスマート タグの [並べ替えを有効にする] チェックボックスをオンにするだけで、GridView の AllowSorting プロパティが true に設定され、各列のヘッダー テキストが LinkButton としてレンダリングされます。 エンド ユーザーがいずれかのヘッダーの LinkButtons をクリックすると、ポストバックが発生し、次の手順が実行されます。
- GridView は、その
SortExpressionプロパティをヘッダー リンクがクリックされたフィールドのSortExpressionの値に更新します。 - ObjectDataSource は BLL の
GetProductsPagedAndSortedメソッドを呼び出し、GridView のSortExpressionプロパティをメソッドのsortExpression入力パラメーターの値として渡します (適切なstartRowIndexおよびmaximumRows入力パラメーター値も一緒に渡します)。 - BLL は DAL の
GetProductsPagedAndSortedメソッドを呼び出します - DAL は
GetProductsPagedAndSortedストアド プロシージャを実行し、@sortExpressionパラメーターを渡します (@startRowIndexおよび@maximumRows入力パラメーター値も一緒に渡します)。 - ストアド プロシージャは、データの適切なサブセットを BLL に返し、BLL は、それを ObjectDataSource に返します。このデータは GridView にバインドされ、HTML にレンダリングされ、エンド ユーザーに送信されます。
図 7 は、UnitPrice が昇順で並べ替えられた場合の結果の最初のページを示しています。
図 7: UnitPrice で並べ替えられた結果 (クリックするとフルサイズの画像が表示されます)
現在の実装では、結果を製品名、カテゴリ名、ユニットあたりの数量、単価で正しく並べ替えることができますが、サプライヤー名で結果を並べ替えようとすると、ランタイム例外が発生します (図 8 参照)。
図 8: サプライヤーの結果ごとに結果を並べ替えようとした場合に発生する次のランタイム例外
この例外は、GridView の SupplierName BoundField の SortExpression が SupplierName に設定されているために発生します。 しかし、Suppliers テーブルのサプライヤーの名前は、実際にはこの列名に SupplierName という別名を付けた CompanyName が呼び出されます。 ただし、ROW_NUMBER() 関数で使用される OVER 句はエイリアスを使用することはできず、実際の列名を使用する必要があります。 そのため、SupplierName BoundField の SortExpression を SupplierName から CompanyName に変更します (図 9 参照)。 図 10 に示すように、この変更後、結果はサプライヤーごとに並べ替えることができます。
図 9: SupplierName BoundField の SortExpression の CompanyName への変更
図 10: サプライヤーごとに並べ替えられるようになった結果 (クリックするとフルサイズの画像が表示されます)
まとめ
前のチュートリアルで確認したカスタム ページングの実装では、デザイン時に結果を並べ替える順序を指定する必要がありました。 つまり、実装したカスタム ページングの実装では、並べ替え機能を同時に提供することはできませんでした。 このチュートリアルでは、最初のストアド プロシージャを拡張して、結果を並べ替えることができるように @sortExpression 入力パラメーターを含めることで、この制限を克服しました。
このストアド プロシージャを作成し、DAL と BLL で新しいメソッドを作成した後、ObjectDataSource を構成して GridView の現在の SortExpression プロパティを BLL SelectMethod に渡すことで、並べ替えとカスタム ページングの両方を提供する GridView を実装できました。
プログラミングに満足!
著者について
7 冊の ASP/ASP.NET 書籍の著者であり、4GuysFromRolla.com の創設者である Scott Mitchell は、1998 年から Microsoft Web テクノロジを扱っています。 Scott は、独立したコンサルタント、トレーナー、ライターとして働いています。 彼の最新の本は サムズは24時間で2.0 ASP.NET 自分自身を教えています。 にアクセスするか、ブログを使用して にアクセスmitchell@4GuysFromRolla.comできます。これは でhttp://ScottOnWriting.NET見つけることができます。
特別な感謝
このチュートリアル シリーズは、多くの役に立つ校閲者によってレビューされました。 このチュートリアルのリード レビュー担当者は、Carlos Santos でした。 今後の MSDN の記事を確認することに関心がありますか? その場合は、 にmitchell@4GuysFromRolla.com行をドロップしてください。