Řazení dat s uživatelsky definovaným stránkováním (VB)

od Scotta Mitchella

Stáhnout PDF

V předchozím kurzu jsme zjistili, jak implementovat vlastní stránkování při prezentování dat na webové stránce. V tomto kurzu si ukážeme, jak rozšířit předchozí příklad tak, aby zahrnoval podporu řazení vlastních stránkování.

Úvod

Ve srovnání s výchozím způsobem stránkování může vlastní stránkování výrazně zlepšit výkon při stránkování dat, což z něj činí preferovanou volbu pro stránkování velkých objemů dat. Implementace vlastního stránkování je složitější než implementace výchozího stránkování, zvláště když se přidává třídění. V tomto kurzu rozšíříme příklad z předchozího kurzu tak, že zahrne podporu řazení a vlastního stránkování.

Poznámka:

Vzhledem k tomu, že tento kurz vychází z předchozího kurzu, před zahájením chvilku zkopírujte deklarativní syntaxi v elementu <asp:Content> z předchozí webové stránky kurzu (EfficientPaging.aspx) a vložte ji mezi <asp:Content> prvek na SortParameter.aspx stránce. Podrobnější diskuzi o replikaci funkčnosti jedné ASP.NET stránky na jinou najdete, když se vrátíte ke kroku 1 tutoriálu Přidání ověřovacích ovládacích prvků do rozhraní pro úpravy a vkládání.

Krok 1: Přezkoumání vlastní techniky stránkování

Aby vlastní stránkování fungovalo správně, musíme implementovat nějakou techniku, která efektivně vezme konkrétní podmnožinu záznamů s ohledem na parametry Index počátečního řádku a Maximální počet řádků. Existuje několik technik, které lze použít k dosažení tohoto cíle. V předchozím kurzu jsme se podívali na to, jak toho dosáhnout pomocí nové ROW_NUMBER() funkce řazení Microsoft SQL Server 2005. Stručně řečeno, ROW_NUMBER() funkce řazení přiřadí každému řádku vrácené dotazem seřazeným podle zadaného pořadí řazení číslo řádku. Příslušná podmnožina záznamů se pak získá vrácením konkrétní části očíslovaných výsledků. Následující dotaz ukazuje, jak pomocí této techniky vrátit tyto produkty očíslované 11 až 20 při řazení výsledků seřazených abecedně podle 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

Tato technika funguje dobře pro stránkování pomocí konkrétního pořadí řazení (ProductName seřazené abecedně, v tomto případě), ale dotaz je potřeba upravit tak, aby zobrazoval výsledky seřazené podle jiného výrazu řazení. V ideálním případě by výše uvedený dotaz mohl být přepsán tak, aby používal parametr v OVER klauzuli, například takto:

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

Parametrizované ORDER BY klauzule bohužel nejsou povolené. Místo toho musíme vytvořit uloženou proceduru @sortExpression , která přijímá vstupní parametr, ale používá jedno z následujících alternativních řešení:

• Pište pevně zakódované dotazy pro každý výraz řazení, který lze použít; pak pomocí IF/ELSE příkazů T-SQL určete, který dotaz se má provést.
• CASE Příkaz použijte k poskytnutí dynamických ORDER BY výrazů založených na @sortExpressio n vstupním parametru. Další informace najdete v části Použité k dynamickému řazení výsledků dotazu v příkazech T-SQLCASE.
• Vytvořte příslušný dotaz jako řetězec v uložené proceduře a pak pomocí sp_executesql systémové uložené procedury spusťte dynamický dotaz.

Každé z těchto alternativních řešení má určité nevýhody. První možnost není tak udržovatelná jako ostatní dvě, protože vyžaduje, abyste vytvořili dotaz pro každý možný výraz řazení. Proto pokud se později rozhodnete přidat nová, seřazená pole do GridView, budete se také muset vrátit a aktualizovat uloženou proceduru. Druhý přístup má některé jemnosti, které přinášejí obavy o výkon při řazení podle databázových sloupců, jež nejsou řetězci, a také trpí stejnými problémy s udržovatelností jako první. A třetí volba, která používá dynamické SQL, představuje riziko útoku prostřednictvím injektáže SQL, pokud útočník dokáže spustit uloženou proceduru, která předává hodnoty vstupních parametrů podle svého výběru.

I když žádný z těchto přístupů není dokonalý, myslím, že třetí možnost je nejlepší ze tří. Díky použití dynamického SQL nabízí úroveň flexibility, kterou ty další dva nemají. Útok prostřednictvím injektáže SQL je navíc možné zneužít pouze v případě, že útočník dokáže spustit uloženou proceduru, která předává vstupní parametry podle svého výběru. Vzhledem k tomu, že DAL používá parametrizované dotazy, ADO.NET bude chránit tyto parametry odeslané do databáze prostřednictvím architektury, což znamená, že zranitelnost vůči útoku SQL injektáží existuje pouze v případě, že útočník může přímo spustit uloženou proceduru.

Chcete-li tuto funkci implementovat, vytvořte novou uloženou proceduru v databázi Northwind s názvem GetProductsPagedAndSorted. Tato uložená procedura by měla přijímat tři vstupní parametry: @sortExpressionvstupní parametr typu nvarchar(100), který určuje, jak se mají výsledky řadit a vkládat přímo za ORDER BY text v OVER klauzuli, a @startRowIndex a @maximumRowsstejné dva celočíselné vstupní parametry z GetProductsPaged uložené procedury vyšetřované v předchozím kurzu. Pomocí následujícího skriptu vytvořte uloženou proceduru 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

Uložená procedura začíná tím, že zajistí, aby byla zadána hodnota parametru @sortExpression . Pokud chybí, výsledky jsou seřazeny podle ProductID. Dále se sestaví dynamický dotaz SQL. Všimněte si, že dynamický dotaz SQL se zde mírně liší od předchozích dotazů použitých k načtení všech řádků z tabulky Products. V předchozích příkladech jsme pomocí poddotazů získali názvy jednotlivých produktů přidružených k kategoriím a dodavatelům. Toto rozhodnutí bylo učiněno v kurzu Vytvoření vrstvy přístupu k datům a bylo provedeno namísto použití JOIN, protože TableAdapter nemůže automaticky vytvořit spojené metody vložení, aktualizace a odstranění pro takové dotazy. Uložená procedura GetProductsPagedAndSorted však musí používat JOIN, aby byly výsledky seřazeny podle kategorií nebo názvů dodavatelů.

Tento dynamický dotaz se sestaví zřetězením částí statických dotazů a parametrů @sortExpression, @startRowIndex a @maximumRows. Protože @startRowIndex a @maximumRows jsou celočíselné parametry, musí být převedeny na nvarchars, aby byly správně zřetězeny. Po vytvoření tohoto dynamického dotazu SQL je vykonán prostřednictvím sp_executesql.

Chvíli otestujte tuto uloženou proceduru s různými hodnotami pro @sortExpression, @startRowIndexa @maximumRows parametry. V Průzkumníku serveru klikněte pravým tlačítkem myši na název uložené procedury a zvolte Spustit. Tím se zobrazí dialogové okno Spustit uloženou proceduru, do kterého můžete zadat vstupní parametry (viz obrázek 1). Pokud chcete výsledky seřadit podle názvu kategorie, použijte pro hodnotu parametru @sortExpression NázevKategorie. Pokud chcete řadit podle názvu společnosti dodavatele, použijte Název společnosti. Po zadání hodnot parametrů klikněte na tlačítko OK. Výsledky se zobrazí v okně Výstup. Obrázek 2 ukazuje výsledky při vracení produktů seřazených od 11 do 20 při řazení v UnitPrice sestupném pořadí.

Obrázek 1: Zkuste použít jiné hodnoty pro tři vstupní parametry uložené procedury

Obrázek 2: Výsledky uložených procedur jsou zobrazeny v okně výstupu (kliknutím zobrazíte obrázek s plnou velikostí).

Poznámka:

Při řazení výsledků podle zadaného ORDER BY sloupce v OVER klauzuli musí SQL Server výsledky seřadit. Jedná se o rychlou operaci, pokud se ve sloupci nachází clusterovaný index, podle kterého se výsledky řazuje, nebo pokud existuje krytý index, ale může být nákladnější jinak. Pokud chcete zvýšit výkon dostatečně velkých dotazů, zvažte přidání neskupovaného indexu pro sloupec, podle kterého jsou výsledky seřazeny. Další podrobnosti najdete v tématu Funkce řazení a výkon v SQL Serveru 2005 .

Krok 2: Rozšíření přístupu k datům a vrstev obchodní logiky

Když je GetProductsPagedAndSorted vytvořená uložená procedura, naším dalším krokem je poskytnout způsob, jak tuto uloženou proceduru provést prostřednictvím architektury aplikace. To zahrnuje přidání vhodné metody do DAL i BLL. Začněme přidáním metody do DAL. Otevřete Northwind.xsd Datovou sadu typu, klikněte pravým tlačítkem myši na ProductsTableAdapter a v místní nabídce zvolte možnost Přidat dotaz. Stejně jako v předchozím kurzu chceme tuto novou metodu DAL nakonfigurovat tak, aby používala existující uloženou proceduru – GetProductsPagedAndSortedv tomto případě. Začněte tím, že indikujete, že chcete, aby nová metoda TableAdapter používala existující uloženou proceduru.

Obrázek 3: Volba použití existující uložené procedury

Pokud chcete zadat uloženou proceduru, která se má použít, vyberte uloženou GetProductsPagedAndSorted proceduru z rozevíracího seznamu na další obrazovce.

Obrázek 4: Použití uložené procedury GetProductsPagedAndSorted

Tato uložená procedura vrátí sadu záznamů jako výsledky, takže na další obrazovce označuje, že vrací tabulková data.

Obrázek 5: Indikuje, že uložená procedura vrací tabulková data

Nakonec vytvořte metody DAL, které používají vzory Fill a DataTable a Return a DataTable, pojmenování metod FillPagedAndSorted a GetProductsPagedAndSortedv uvedeném pořadí.

Obrázek 6: Volba názvů metod

Teď, když jsme rozšířili DAL, jsme připraveni se obrátit na BLL. ProductsBLL Otevřete soubor třídy a přidejte novou metodu . GetProductsPagedAndSorted Tato metoda musí přijmout tři vstupní parametry sortExpression, startRowIndex a maximumRows a jednoduše zavolat na metodu DAL s GetProductsPagedAndSorted, například takto:

<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

Krok 3: Konfigurace ObjectDataSource pro předání parametru SortExpression

Vzhledem k tomu, že DAL a BLL byly rozšířeny o metody, které využívají uložené GetProductsPagedAndSorted procedury, zbývá už jen nakonfigurovat ObjectDataSource na SortParameter.aspx stránce, aby používal novou metodu BLL, a předat parametr na základě sloupce, podle kterého uživatel požádal o seřazení výsledků.

Začněte změnou ObjectDataSource z SelectMethodGetProductsPaged na GetProductsPagedAndSorted. Můžete to provést pomocí průvodce konfigurací zdroje dat, z okna Vlastnosti nebo přímo prostřednictvím deklarativní syntaxe. Dále musíme zadat hodnotu pro Vlastnost ObjectDataSourceSortParameterName. Pokud je tato vlastnost nastavena, ObjectDataSource se pokusí předat vlastnost SortExpression z GridView do SelectMethod. Konkrétně ObjectDataSource hledá vstupní parametr, jehož název je roven hodnotě SortParameterName vlastnosti. Vzhledem k tomu, že metoda BLL s GetProductsPagedAndSorted má vstupní parametr pro výraz řazení nazvaný sortExpression, nastavte vlastnost ObjectDataSource s SortExpression na sortExpression.

Po provedení těchto dvou změn by deklarativní syntaxe ObjectDataSource měla vypadat nějak takto:

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

Poznámka:

Stejně jako v předchozím kurzu se ujistěte, že ObjectDataSource nezahrnuje sortExpression, startRowIndex nebo maximumRows vstupní parametry v kolekci SelectParameters.

Pokud chcete povolit řazení v GridView, jednoduše zaškrtněte políčko Povolit řazení v inteligentní značce GridView s, která nastaví GridView s AllowSorting vlastnost a způsobí, že true se text záhlaví pro každý sloupec vykresluje jako LinkButton. Když koncový uživatel klikne na jednu z hlaviček tlačítek odkazů, proběhne postback a následující kroky se odehrají:

1. GridView aktualizuje svou SortExpression vlastnost na hodnotu SortExpression pole, jehož odkaz na záhlaví byl kliknut
2. ObjectDataSource vyvolá metodu BLL s GetProductsPagedAndSorted, předáním vlastnosti GridView s SortExpression jako hodnoty pro vstupní parametr metody sortExpression (spolu s odpovídajícími vstupními hodnotami parametrů startRowIndex a maximumRows).
3. BLL vyvolá metodu DAL s GetProductsPagedAndSorted .
4. Dal spustí uloženou proceduru GetProductsPagedAndSorted@sortExpression a předá parametr (spolu s hodnotami vstupních @startRowIndex@maximumRows parametrů).
5. Uložená procedura vrátí příslušnou podmnožinu dat do BLL, která je vrátí do ObjectDataSource; tato data jsou pak svázána s GridView, vykreslena do HTML a odeslána koncovému uživateli.

Obrázek 7 ukazuje první stránku výsledků při řazení podle UnitPrice vzestupného pořadí.

Obrázek 7: Výsledky jsou seřazené podle jednotkové ceny (kliknutím zobrazíte obrázek s plnou velikostí)

I když aktuální implementace dokáže správně seřadit výsledky podle názvu produktu, názvu kategorie, množství na jednotku a jednotkové ceny, pokus o objednání výsledků podle názvu dodavatele vede k výjimce za běhu (viz obrázek 8).

Obrázek 8: Pokus o seřazení výsledků podle výsledků dodavatele v následující výjimce za běhu

K této výjimce dochází, protože SortExpression GridView s SupplierName BoundField je nastavena na SupplierName. Název dodavatele v tabulce Suppliers se ve skutečnosti nazývá CompanyName, přičemž tento název sloupce byl přejmenován jako SupplierName. Klauzule OVER používaná ROW_NUMBER() funkcí však nemůže použít alias a musí použít skutečný název sloupce. Proto změňte SupplierName BoundField z SortExpression SupplierName na CompanyName (viz obrázek 9). Jak ukazuje obrázek 10, po této změně je možné výsledky seřadit podle dodavatele.

Obrázek 9: Změna pole SupplierName BoundField s SortExpression na CompanyName

Obrázek 10: Výsledky je teď možné seřadit podle dodavatele (kliknutím zobrazíte obrázek s plnou velikostí).

Shrnutí

Vlastní implementace stránkování, kterou jsme prozkoumali v předchozím kurzu, vyžadovala, aby pořadí, podle kterého se měly výsledky řadit, bylo specifikováno během návrhu. Stručně řečeno, to znamenalo, že vlastní implementace stránkování, kterou jsme implementovali, nemohla současně poskytovat možnosti řazení. V tomto kurzu jsme překonali toto omezení rozšířením původní uložené procedury o vstupní parametr @sortExpression, podle kterého se dají výsledky řadit.

Po vytvoření této uložené procedury a nových metod v DAL a BLL jsme byli schopni implementovat GridView, který nabídl řazení i vlastní stránkování tím, že jsme nakonfigurovali ObjectDataSource tak, aby předával aktuální vlastnost SortExpression z GridView do BLL SelectMethod.

Šťastné programování!

O autorovi

Scott Mitchell, autor sedmi knih ASP/ASP.NET a zakladatel 4GuysFromRolla.com, pracuje s webovými technologiemi Microsoftu od roku 1998. Scott pracuje jako nezávislý konzultant, trenér a spisovatel. Jeho nejnovější kniha je Sams: Nauč se ASP.NET 2.0 za 24 hodin. Může být dosažitelný na mitchell@4GuysFromRolla.comadrese .

Zvláštní díky

Tato série kurzů byla zkontrolována mnoha užitečnými recenzenty. Vedoucí recenzent pro tento kurz byl Carlos Santos. Chcete si projít nadcházející články MSDN? Pokud ano, napište mi zprávu na mitchell@4GuysFromRolla.com.

předchozíDalší