Řazení dat s vlastním stránkováním (C#)

Scott Mitchell

Stáhnout PDF

V předchozím kurzu jsme se dozvěděli, jak implementovat vlastní stránkování při prezentování dat na webové stránce. V tomto kurzu se dozvíte, jak rozšířit předchozí příklad o podporu řazení vlastních stránkování.

Úvod

Ve srovnání s výchozím stránkováním může vlastní stránkování zlepšit výkon stránkování dat o několik řádů, takže vlastní stránkování je při stránkování velkých objemů dat volbou implementace de facto stránkování. Implementace vlastního stránkování je však důležitější než implementace výchozího stránkování, zejména při přidávání řazení do kombinace. V tomto kurzu rozšíříme příklad z předchozího o 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 chvíli zkopírujte deklarativní syntaxi v elementu <asp:Content> z předchozí webové stránky s kurzu (EfficientPaging.aspx) a vložte ji mezi <asp:Content> element na SortParameter.aspx stránce. Podrobnější informace o replikaci funkcí jedné ASP.NET stránky na jinou stránku najdete v kroku 1 v kurzu Přidání ověřovacích ovládacích prvků do úprav a vkládání rozhraní .

Krok 1: Opětovné vytvoření vlastní techniky stránkování

Aby vlastní stránkování fungovalo správně, musíme implementovat nějakou techniku, která dokáže efektivně zachytit konkrétní podmnožinu záznamů vzhledem k parametrům Spustit index řádků a Maximální počet řádků. Existuje několik technik, které se dají 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 hodnocení Microsoft SQL Server 2005. Stručně řečeno, ROW_NUMBER() funkce řazení přiřadí číslo řádku každému řádku vráceného dotazem seřazeným podle zadaného pořadí řazení. Příslušná podmnožina záznamů se pak získá vrácením konkrétní části číslovaných výsledků. Následující dotaz ukazuje, jak pomocí této techniky vrátit 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 v tomto případě seřazeno abecedně), ale dotaz je potřeba upravit tak, aby zobrazoval výsledky seřazené jiným výrazem řazení. V ideálním případě by se výše uvedený dotaz mohl přepsat tak, aby používal parametr v klauzuli OVER , 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í:

• Psát pevně zakódované dotazy pro každý z výrazů řazení, které mohou být použity; pak pomocí IF/ELSE příkazů T-SQL určete, který dotaz se má spustit.
• CASE Použijte příkaz k poskytnutí dynamických ORDER BY výrazů založených na n vstupním @sortExpressio parametru. Další informace najdete v části Použité k dynamickému řazení výsledků dotazů v části Příkazy T-SQLCASE.
• Vytvořte příslušný dotaz jako řetězec v uložené proceduře a pak použijte systémovou sp_executesql uloženou proceduru ke spuštění dynamického dotazu.

Každé z těchto alternativních řešení má určité nevýhody. První možnost není tak udržovatelná jako u ostatních dvou, protože vyžaduje vytvoření dotazu pro každý možný výraz řazení. Proto pokud se později rozhodnete přidat do objektu GridView nová pole s možností řazení, budete se také muset vrátit zpět a aktualizovat uloženou proceduru. Druhý přístup má určité jemnosti, které při řazení podle sloupců databáze bez řetězců přinášejí problémy s výkonem, a také trpí stejnými problémy s udržovatelností jako první. A třetí volba, která používá dynamický SQL, zavádí riziko útoku pomocí injektáže SQL, pokud je útočník schopen spustit uloženou proceduru a předat 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, které ostatní dva nemají. Útok injektáží SQL je navíc možné zneužít pouze v případě, že útočník dokáže spustit uloženou proceduru a předat vstupní parametry podle svého výběru. Vzhledem k tomu, že DAL používá parametrizované dotazy, bude ADO.NET chránit ty parametry, které se odesílají do databáze prostřednictvím architektury, což znamená, že ohrožení zabezpečení útoku injektáží SQL existuje pouze v případě, že útočník může přímo spustit uloženou proceduru.

Pokud chcete tuto funkci implementovat, vytvořte v databázi Northwind novou uloženou proceduru s názvem GetProductsPagedAndSorted. Tato uložená procedura by měla přijímat tři vstupní parametry: @sortExpression, vstupní parametr typu nvarchar(100), který určuje, jak mají být výsledky seřazeny a vloženy přímo za ORDER BY text v OVER klauzuli; a @startRowIndex a @maximumRows, stejné dva celočíselné vstupní parametry z GetProductsPaged uložené procedury zkoumané v předchozím kurzu. Pomocí následujícího skriptu vytvořte uloženou GetProductsPagedAndSorted proceduru:

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

Tento dynamický dotaz se sestaví zřetězením částí statického dotazu a @sortExpressionparametrů , @startRowIndexa @maximumRows . Vzhledem k tomu @startRowIndex , že a @maximumRows jsou celočíselné parametry, musí být převedeny na nvarchars, aby byly správně zřetězeny. Jakmile se tento dynamický dotaz SQL zkonstruuje, spustí se prostřednictvím sp_executesql.

Chvíli otestujte tuto uloženou proceduru @sortExpressions různými hodnotami parametrů , @startRowIndexa @maximumRows . V Průzkumníku serveru klikněte pravým tlačítkem na název uložené procedury a zvolte Spustit. Zobrazí se 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, jako hodnotu parametru @sortExpression použijte CategoryName. Pokud chcete výsledky seřadit podle názvu společnosti dodavatele, použijte Název_společnosti. Po zadání hodnot parametrů klikněte na 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í podle UnitPrice pořadí v sestupném pořadí.

Obrázek 1: Vyzkoušejte různé hodnoty pro tři vstupní parametry uložené procedury

Obrázek 2: Výsledky uložené procedury se zobrazují v okně Výstup (kliknutím zobrazíte obrázek v plné velikosti)

Poznámka

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

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

GetProductsPagedAndSorted Po vytvoření uložené procedury je naším dalším krokem poskytnutí prostředků ke spuštění této uložené procedury 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. Northwind.xsd Otevřete typovou sadu dat, klikněte pravým tlačítkem na ProductsTableAdaptera 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 chcete, aby nová metoda TableAdapter používala existující uloženou proceduru.

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

Chcete-li určit 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í jako výsledky sadu záznamů, takže na další obrazovce označí, že vrací tabulková data.

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

Nakonec vytvořte metody DAL, které používají vzory Fill a DataTable i Return a DataTable s názvy metod FillPagedAndSorted a GetProductsPagedAndSorted, v uvedeném pořadí.

Obrázek 6: Výběr názvů metod

Teď, když jsme prodlouž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, startRowIndexa maximumRows a měla by jednoduše volat dolů do metody DAL s GetProductsPagedAndSorted , například takto:

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

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

Po rozšíření DAL a BLL o metody, které využívají uloženou GetProductsPagedAndSorted proceduru, zbývá jen nakonfigurovat ObjectDataSource na SortParameter.aspx stránce tak, aby používal novou metodu BLL a předal SortExpression parametr na základě sloupce, podle kterého uživatel požadoval řazení výsledků.

Začněte změnou objektu ObjectDataSource SelectMethod z GetProductsPaged na GetProductsPagedAndSorted. To lze provést prostřednictvím průvodce Konfigurací zdroje dat, z okno Vlastnosti nebo přímo prostřednictvím deklarativní syntaxe. Dále musíme zadat hodnotu vlastnosti ObjectDataSource sSortParameterName. Pokud je tato vlastnost nastavena, ObjectDataSource se pokusí předat vlastnost GridView s SortExpression do objektu SelectMethod. Konkrétně ObjectDataSource hledá vstupní parametr, jehož název se rovná hodnotě SortParameterName vlastnosti. Vzhledem k tomu, že metoda BLL s GetProductsPagedAndSorted má vstupní parametr výrazu řazení s názvem sortExpression, nastavte vlastnost ObjectDataSource s SortExpression na sortExpression .

Po provedení těchto dvou změn by deklarativní syntaxe ObjectDataSource měla vypadat přibližně 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 neobsahuje 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, která nastaví vlastnost true GridView na AllowSorting a způsobí vykreslení textu záhlaví pro každý sloupec jako LinkButton. Když koncový uživatel klikne na jednu z hlaviček LinkButtons, dojde k zpětnému odeslání a následující kroky se objeví:

1. Objekt GridView aktualizuje svoji SortExpression vlastnost na hodnotu SortExpression pole, jehož odkaz záhlaví byl klikl.
2. ObjectDataSource vyvolá metodu BLL s GetProductsPagedAndSorted a předá vlastnost GridView s SortExpression jako hodnotu vstupního parametru metody sortExpression (spolu s příslušnými startRowIndex hodnotami a maximumRows vstupními hodnotami parametrů).
3. BLL vyvolá metodu DAL s GetProductsPagedAndSorted .
4. DAL spustí uloženou proceduru GetProductsPagedAndSorted a předá @sortExpression parametr (spolu s hodnotami vstupního parametru @startRowIndex a @maximumRows ).
5. Uložená procedura vrátí příslušnou podmnožinu dat do BLL, který ji vrátí ObjectDataSource; tato data se pak vážou na Objekt GridView, vykreslí se do HTML a odešlou se koncovému uživateli.

Obrázek 7 znázorňuje první stránku výsledků seřazených vzestupně UnitPrice .

Obrázek 7: Výsledky jsou seřazené podle JednotkováCena (kliknutím zobrazíte obrázek v plné velikosti).

I když aktuální implementace dokáže výsledky správně seřadit podle názvu produktu, názvu kategorie, množství na jednotku a jednotkové ceny, výsledkem pokusu o objednání výsledků podle názvu dodavatele je výjimka 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 modulu runtime

K této výjimce SortExpression dochází, protože objekt BoundField objektu SupplierName GridView je nastaven na SupplierNamehodnotu . Název dodavatele v tabulce se ale ve Suppliers skutečnosti označuje jako CompanyName název SupplierNametohoto sloupce. Klauzule OVER používaná ROW_NUMBER() funkcí však nemůže používat alias a musí používat 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ě může být výsledky seřazené podle dodavatele.

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

Obrázek 10: Výsledky se teď dají seřadit podle dodavatele (kliknutím zobrazíte obrázek v plné velikosti)

Souhrn

Implementace vlastního stránkování, kterou jsme prozkoumali v předchozím kurzu, vyžadovala, aby pořadí řazení výsledků bylo zadáno v době návrhu. Stručně řečeno, to znamená, že implementace vlastního stránkování, kterou jsme implementovali, nemohla současně poskytovat možnosti řazení. V tomto kurzu jsme toto omezení překonali rozšířením uložené procedury z první @sortExpression o vstupní parametr, podle kterého by se výsledky daly seřadit.

Po vytvoření této uložené procedury a vytvoření nových metod v DAL a BLL jsme byli schopni implementovat GridView, který nabízel jak řazení, tak vlastní stránkování konfigurací ObjectDataSource předat GridView aktuální SortExpression vlastnost BLL SelectMethod.

Všechno nejlepší na programování!

O autorovi

Scott Mitchell, autor sedmi knih o ASP/ASP.NET a zakladatel 4GuysFromRolla.com, pracuje s webovými technologiemi Microsoftu od roku 1998. Scott pracuje jako nezávislý konzultant, školitel a spisovatel. Jeho nejnovější kniha je Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Můžete ho zastihnout na mitchell@4GuysFromRolla.comadrese . nebo prostřednictvím jeho blogu, který najdete na adrese http://ScottOnWriting.NET.

Zvláštní poděkování

Tato série kurzů byla zkontrolována mnoha užitečnými recenzenty. Hlavním recenzentem pro tento kurz byl Carlos Santos. Chtěli byste si projít své nadcházející články na webu MSDN? Pokud ano, dejte mi řádek na mitchell@4GuysFromRolla.com.

PředchozíDalší