Sortowanie niestandardowo stronicowanych danych (C#)

Autor : Scott Mitchell

Pobierz plik PDF

W poprzednim samouczku dowiedzieliśmy się, jak zaimplementować niestandardowe stronicowanie podczas prezentowania danych na stronie internetowej. W tym samouczku dowiemy się, jak rozszerzyć poprzedni przykład o obsługę sortowania w niestandardowym stronicowaniu.

Wprowadzenie

W porównaniu z domyślnym stronicowaniem, niestandardowe stronicowanie może znacznie poprawić wydajność przeglądania danych, czyniąc je de facto wyborem przy implementacji stronicowania dużych ilości danych. Implementowanie niestandardowego stronicowania jest bardziej skomplikowane niż implementowanie domyślnego stronicowania, zwłaszcza kiedy dochodzi kwestia sortowania. W tym samouczku rozszerzymy przykład z poprzedniego samouczka, aby uwzględnić obsługę sortowania i paginacji według własnego uznania.

Uwaga / Notatka

Ponieważ ten samouczek opiera się na poprzednim samouczku, zanim rozpoczniesz, poświęć chwilę na skopiowanie składni deklaratywnej w elemencie <asp:Content> z poprzedniej strony internetowej samouczka (EfficientPaging.aspx) i wklej ją w elemencie <asp:Content> na stronie SortParameter.aspx. Zapoznaj się z krokiem 1 samouczka Dodawania kontrolek weryfikacji do interfejsów edytowania i wstawiania, aby szczegółowo przeanalizować temat przenoszenia funkcjonalności z jednej strony ASP.NET na drugą.

Krok 1. Ponowne analizowanie niestandardowej techniki stronicowania

Aby niestandardowe stronicowanie działało prawidłowo, musimy zaimplementować pewną technikę, która może efektywnie pobrać określony podzestaw rekordów, biorąc pod uwagę parametry Indeks wiersza początkowego i Maksymalna liczba wierszy. Istnieje kilka technik, których można użyć do osiągnięcia tego celu. W poprzednim samouczku przyjrzeliśmy się temu za pomocą nowej ROW_NUMBER() funkcji klasyfikacji programu Microsoft SQL Server 2005. Krótko mówiąc, ROW_NUMBER() funkcja klasyfikacji przypisuje numer wiersza do każdego wiersza zwróconego przez zapytanie sklasyfikowane według określonej kolejności sortowania. Odpowiedni podzbiór rekordów jest następnie uzyskiwany przez zwrócenie określonej sekcji ponumerowanych wyników. Poniższe zapytanie ilustruje sposób użycia tej techniki w celu zwrócenia produktów numerowanych od 11 do 20, gdy wyniki są uporządkowane alfabetycznie według 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

Ta technika dobrze sprawdza się w przypadku stronicowania przy użyciu określonej kolejności sortowania (ProductName posortowanej alfabetycznie w tym przypadku), ale zapytanie musi zostać zmodyfikowane w celu pokazania wyników posortowanych według innego wyrażenia sortowania. Najlepiej, aby powyższe zapytanie mogło zostać przepisane, aby użyć parametru w klauzuli OVER , w następujący sposób:

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

Niestety, klauzule sparametryzowane ORDER BY nie są dozwolone. Zamiast tego musimy utworzyć procedurę składowaną, która akceptuje @sortExpression parametr wejściowy, ale używa jednego z następujących obejść:

• Zapisz zakodowane na stałe zapytania dla każdego wyrażenia sortowania, które mogą być użyte; następnie użyj instrukcji T-SQL, aby określić, które zapytanie wykonać.
• Użyj instrukcji CASE, aby dostarczyć dynamiczne wyrażenia ORDER BY bazujące na parametrze wejściowym @sortExpressio n; zobacz sekcję Używane do dynamicznego sortowania wyników zapytań w instrukcjach T-SQL CASE dla uzyskania dodatkowych informacji.
• Utwórz odpowiednie zapytanie jako ciąg w procedurze składowanej, a następnie i użyj procedury przechowywanej systemowosp_executesql do wykonania zapytania dynamicznego.

Każde z tych obejść ma pewne wady. Pierwsza opcja nie jest tak możliwa do utrzymania, jak pozostałe dwa, ponieważ wymaga utworzenia zapytania dla każdego możliwego wyrażenia sortowania. W związku z tym, jeśli później zdecydujesz się dodać nowe, sortowalne pola do kontrolki GridView, musisz również wrócić i zaktualizować procedurę składowaną. Drugie podejście ma pewne subtelności, które wprowadzają problemy z wydajnością podczas sortowania według kolumn bazy danych innych niż tekstowe, i również cierpi na te same problemy z utrzymaniem jak pierwsze. A trzeci wybór, który korzysta z dynamicznego języka SQL, wprowadza ryzyko ataku iniekcyjnego SQL, jeśli osoba atakująca może wykonać procedurę składowaną przekazującą wybrane wartości parametrów wejściowych.

Chociaż żadne z tych podejść nie jest idealne, myślę, że trzecia opcja jest najlepsza z trzech. Dzięki użyciu dynamicznego języka SQL oferuje ona poziom elastyczności, których nie robią pozostałe dwa. Ponadto atak polegający na wstrzyknięciu kodu SQL może być wykorzystywany tylko wtedy, gdy osoba atakująca może wykonać procedurę składowaną przekazującą wybrane parametry wejściowe. Ponieważ funkcja DAL używa sparametryzowanych zapytań, ADO.NET będzie chronić te parametry, które są wysyłane do bazy danych za pośrednictwem architektury, co oznacza, że luka w zabezpieczeniach polegających na wstrzyknięciu kodu SQL istnieje tylko wtedy, gdy osoba atakująca może bezpośrednio wykonać procedurę składowaną.

Aby zaimplementować tę funkcję, utwórz nową procedurę składowaną w bazie danych Northwind o nazwie GetProductsPagedAndSorted. Ta procedura składowana powinna przyjmować trzy parametry wejściowe: @sortExpression, parametr wejściowy typu nvarchar(100, który określa sposób, w jaki wyniki powinny być sortowane i jest umieszczany bezpośrednio po tekście ORDER BY w klauzuli OVER; oraz @startRowIndex i @maximumRows, te same dwa parametry wejściowe typu całkowitego z procedury składowanej GetProductsPaged, badanej w poprzednim samouczku. Utwórz procedurę GetProductsPagedAndSorted składowaną przy użyciu następującego skryptu:

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

Procedura składowana zaczyna się od sprawdzenia, czy dla parametru @sortExpression określono wartość. Jeśli go brakuje, wyniki są klasyfikowane według ProductID. Następnie tworzone jest dynamiczne zapytanie SQL. Należy pamiętać, że dynamiczne zapytanie SQL różni się nieco od naszych poprzednich zapytań używanych do pobierania wszystkich wierszy z tabeli Products. W poprzednich przykładach uzyskaliśmy nazwy poszczególnych produktów skojarzonych z kategoriami i dostawcami przy użyciu podzapytania. Ta decyzja została podjęta w samouczku Tworzenie warstwy dostępu do danych i zamiast użycia JOIN została zrealizowana, ponieważ narzędzie TableAdapter nie może automatycznie utworzyć skojarzonych metod wstawiania, aktualizowania i usuwania dla takich zapytań. Procedura GetProductsPagedAndSorted składowana musi jednak używać elementów JOIN, aby wyniki można było porządkować według kategorii lub nazw dostawców.

To dynamiczne zapytanie jest tworzone przez łączenie statycznych części zapytania i parametrów @sortExpression, @startRowIndex oraz @maximumRows. @startRowIndex i @maximumRows są całkowitymi parametrami, które należy przekonwertować na ciągi znaków nvarchar, aby mogły być poprawnie połączone. Gdy tylko dynamiczne zapytanie SQL zostanie skonstruowane, jest ono wykonywane za pomocą polecenia sp_executesql.

Poświęć chwilę na przetestowanie tej procedury składowanej z różnymi wartościami parametrów @sortExpression, @startRowIndex, i @maximumRows. W Eksploratorze serwera kliknij prawym przyciskiem myszy nazwę procedury składowanej i wybierz polecenie Wykonaj. Spowoduje to wyświetlenie okna dialogowego Uruchamianie procedury składowanej, w którym można wprowadzić parametry wejściowe (zobacz Rysunek 1). Aby posortować wyniki według nazwy kategorii, użyj wartości parametru @sortExpression CategoryName, aby posortować według nazwy firmy dostawcy, użyj nazwy firmy. Po podaniu wartości parametrów kliknij przycisk OK. Wyniki są wyświetlane w oknie Dane wyjściowe. Rysunek 2 przedstawia wyniki zwracania produktów w rankingu od 11 do 20 podczas zamawiania według UnitPrice kolejności malejącej.

Rysunek 1. Spróbuj użyć różnych wartości dla trzech parametrów wejściowych procedury składowanej

Rysunek 2: Wyniki procedury składowanej są wyświetlane w oknie wyników (kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Uwaga / Notatka

Podczas klasyfikowania wyników według określonej ORDER BY kolumny w klauzuli OVER program SQL Server musi sortować wyniki. Jest to szybka operacja, jeśli istnieje indeks klastrowany dla kolumn, według których wyniki są uporządkowane, albo jeśli istnieje indeks obejmujący, ale w przeciwnym razie może to być bardziej zasobożerne. Aby zwiększyć wydajność dla wystarczająco dużych zapytań, rozważ dodanie indeksu nieklastrowanego dla kolumny, według której są uporządkowane wyniki. Aby uzyskać więcej informacji, zobacz Ranking Functions and Performance in SQL Server 2005 (Funkcje klasyfikacji i wydajność w programie SQL Server 2005 ).

Krok 2. Rozszerzanie warstw dostępu do danych i logiki biznesowej

Po utworzeniu GetProductsPagedAndSorted procedury składowanej następnym krokiem jest dostarczenie środków do wykonania tej procedury składowanej za pomocą naszej architektury aplikacji. Wiąże się to z dodaniem odpowiedniej metody zarówno do DAL, jak i BLL. Zacznijmy od dodania metody do warstwy dostępu do danych. Otwórz Northwind.xsd edytowalny zestaw danych, kliknij prawym przyciskiem myszy pozycję ProductsTableAdapter, a następnie wybierz opcję Dodaj zapytanie z menu kontekstowego. Tak jak w poprzednim samouczku, chcemy skonfigurować tę nową metodę DAL tak, aby korzystała z istniejącej procedury składowanej — GetProductsPagedAndSortedw tym przypadku . Zacznij od wskazania, że nowa metoda TableAdapter ma używać istniejącej procedury składowanej.

Rysunek 3. Wybierz użycie istniejącej procedury składowanej

Aby określić procedurę składowaną do użycia, na następnym ekranie wybierz procedurę składowaną GetProductsPagedAndSorted z listy rozwijanej.

Rysunek 4. Użyj procedury składowanej GetProductsPagedAndSorted

Ta procedura składowana zwraca zestaw rekordów jako wyniki, dlatego na następnym ekranie należy wskazać, że zwraca dane tabelaryczne.

Rysunek 5. Wskazuje, że procedura składowana zwraca dane tabelaryczne

Na koniec utwórz metody DAL, które używają wzorców Fill a DataTable i Return a DataTable, i nazwij metody odpowiednio FillPagedAndSorted i GetProductsPagedAndSorted.

Rysunek 6. Wybieranie nazw metod

Teraz, gdy rozszerzyliśmy DAL, jesteśmy gotowi przejść do BLL. ProductsBLL Otwórz plik klasy i dodaj nową metodę GetProductsPagedAndSorted. Ta metoda wymaga zaakceptowania trzech parametrów wejściowych sortExpression, startRowIndexi maximumRows i powinna po prostu wywołać metodę DAL GetProductsPagedAndSorted w następujący sposób:

[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: Konfigurowanie ObjectDataSource w celu przekazania parametru SortExpression

Po rozszerzeniu DAL i BLL, aby uwzględnić metody korzystające z procedury składowanej GetProductsPagedAndSorted, wszystko, co pozostało, to skonfigurować kontrolkę ObjectDataSource na stronie SortParameter.aspx, aby użyć nowej metody BLL i przekazać parametr SortExpression na podstawie kolumny, według której użytkownik zażądał sortowania wyników.

Zacznij od zmiany parametru ObjectDataSource s SelectMethod z GetProductsPaged na GetProductsPagedAndSorted. Można to zrobić za pomocą Kreatora konfigurowania źródła danych w oknie Właściwości lub bezpośrednio za pomocą składni deklaratywnej. Następnie musimy podać wartość właściwości ObjectDataSource.SortParameterName Jeśli ta właściwość jest ustawiona, ObjectDataSource próbuje przekazać właściwość GridView SortExpression do SelectMethod. W szczególności obiekt ObjectDataSource szuka parametru wejściowego, którego nazwa jest równa wartości SortParameterName właściwości. Metoda BLL ma parametr wejściowy wyrażenia sortowania o nazwie GetProductsPagedAndSorted, więc ustaw właściwość ObjectDataSource sortExpression na sortExpression.

Po wprowadzeniu tych dwóch zmian składnia deklaratywna obiektu ObjectDataSource powinna wyglądać podobnie do następującej:

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

Uwaga / Notatka

Podobnie jak w poprzednim samouczku, upewnij się, że właściwość ObjectDataSource nie zawiera parametrów wejściowych sortExpression, startRowIndex lub maximumRows w kolekcji SelectParameters.

Aby włączyć sortowanie w kodzie GridView, po prostu zaznacz pole wyboru Włącz sortowanie w tagu inteligentnym GridView, które ustawia właściwość GridView AllowSorting na true i powoduje renderowanie tekstu nagłówka dla każdej kolumny jako linkButton. Gdy użytkownik końcowy kliknie jeden z nagłówków przycisków łącza, następuje odświeżenie strony i zostają wykonane następujące kroki:

1. Obiekt GridView aktualizuje jego SortExpression właściwość do wartości SortExpression pola, którego link nagłówka został kliknięty
2. Obiekt ObjectDataSource wywołuje metodę BLL GetProductsPagedAndSorted, przekazując właściwość GridView jako wartość SortExpression parametru wejściowego metody sortExpression (wraz z odpowiednimi wartościami startRowIndex i maximumRows parametrów wejściowych).
3. BLL wywołuje metodę DAL GetProductsPagedAndSorted
4. DAL wykonuje procedurę składowaną GetProductsPagedAndSorted, przekazując parametr @sortExpression wraz z wartościami parametrów wejściowych @startRowIndex i @maximumRows.
5. Procedura składowana zwraca odpowiedni podzbiór danych do biblioteki BLL, która zwraca je do obiektu ObjectDataSource; te dane są następnie powiązane z elementem GridView, renderowane w kodzie HTML i wysyłane do użytkownika końcowego

Rysunek 7 przedstawia pierwszą stronę wyników po posortowaniu według UnitPrice kolejności rosnącej.

Rysunek 7. Wyniki są sortowane według wartości UnitPrice (Kliknij, aby wyświetlić obraz o pełnym rozmiarze)

Podczas gdy bieżąca implementacja może poprawnie sortować wyniki według nazwy produktu, nazwy kategorii, ilości na jednostkę i ceny jednostkowej, próba zamówienia wyników według nazwy dostawcy powoduje wyjątek środowiska uruchomieniowego (patrz Rysunek 8).

Rysunek 8. Próba sortowania wyników według wyników dostawcy w następującym wyjątku środowiska uruchomieniowego

Ten wyjątek występuje, ponieważ SortExpression właściwość GridView SupplierName BoundField jest ustawiona na SupplierName wartość. Jednak nazwa dostawcy w tabeli Suppliers jest faktycznie nazywana CompanyName, a nadaliśmy nazwę aliasu tej kolumnie jako SupplierName. Jednak klauzula OVER używana przez ROW_NUMBER() funkcję nie może używać aliasu i musi używać rzeczywistej nazwy kolumny. W związku z tym zmień wartości SupplierName BoundField s SortExpression z SupplierName na CompanyName (patrz Rysunek 9). Jak pokazano na rysunku 10, po tej zmianie wyniki można sortować według dostawcy.

Rysunek 9. Zmiana nazwy Dostawcy BoundField s SortExpression na CompanyName

Rysunek 10. Wyniki można teraz sortować według dostawcy (kliknij, aby wyświetlić obraz pełnowymiarowy)

Podsumowanie

Niestandardowa implementacja stronicowania zbadana w poprzednim samouczku wymagała, aby kolejność sortowania wyników była określana w czasie projektowania. Krótko mówiąc, oznacza to, że zaimplementowana przez nas niestandardowa implementacja stronicowania nie mogła jednocześnie zapewnić możliwości sortowania. W tym samouczku pokonaliśmy to ograniczenie, rozszerzając procedurę składowaną z pierwszej o uwzględnienie parametru wejściowego @sortExpression, który pozwala na sortowanie wyników.

Po utworzeniu tej procedury składowanej i utworzeniu nowych metod w DAL i BLL udało nam się zaimplementować kontrolkę GridView, która oferowała zarówno sortowanie, jak i niestandardowe stronicowanie, konfigurując obiekt ObjectDataSource, aby przekazać bieżącą SortExpression właściwość GridView do BLL SelectMethod.

Szczęśliwe programowanie!

Informacje o autorze

Scott Mitchell, autor siedmiu książek ASP/ASP.NET i założyciel 4GuysFromRolla.com, współpracuje z technologiami internetowymi firmy Microsoft od 1998 roku. Scott pracuje jako niezależny konsultant, trener i pisarz. Jego najnowsza książka to Sams Teach Yourself ASP.NET 2.0 w ciągu 24 godzin. Można go uzyskać pod adresem mitchell@4GuysFromRolla.com.

Specjalne podziękowania

Ta seria samouczków została omówiona przez wielu przydatnych recenzentów. Główny recenzent tego samouczka to Carlos Santos. Chcesz przejrzeć nadchodzące artykuły MSDN? Jeśli tak, napisz do mnie na adres mitchell@4GuysFromRolla.com.

PoprzedniNastępny