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

  • [アーティクル]

作成者: Scott Mitchell

PDF のダウンロード

前のチュートリアルでは、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-SQL CASE ステートメント の「クエリ結果の動的な並べ替えに使用する」セクションを参照してください。
  • ストアド プロシージャ内の文字列として適切なクエリを作成し、システム ストアド プロシージャを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 テーブルからすべての行を取得するために使用した以前のクエリとは若干異なります。 前の例では、サブクエリを使用して、関連する各製品のカテゴリとサプライヤーの名前を取得しました。 この決定は 、データ アクセス層の作成 に関するチュートリアルに戻り、TableAdapter でこのようなクエリに関連する挿入、更新、および削除のメソッドを自動的に作成できないため、 を使用 JOIN する代わりに行われました。 ただし、ストアド プロシージャでは GetProductsPagedAndSorted 、結果をカテゴリ名または仕入先名で並べ替えるために は、 を使用 JOIN する必要があります。

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

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

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

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

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

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

注意

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

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

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

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

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

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

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 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 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 の プロパティが にtrue設定され、各列のAllowSortingヘッダー テキストが LinkButton としてレンダリングされます。 エンド ユーザーがいずれかのヘッダー LinkButtons をクリックすると、ポストバックが続き、次の手順が実行されます。

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

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

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

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

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

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

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

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

SupplierName BoundField s SortExpression を CompanyName に変更する

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

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

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

まとめ

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

このストアド プロシージャを作成し、DAL と BLL で新しいメソッドを作成した後、GridView の現在 SortExpression のプロパティを BLL SelectMethodに渡すように ObjectDataSource を構成することで、並べ替えとカスタム ページングの両方を提供する 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行をドロップしてください。

前へ次へ