Sortowanie niestandardowo stronicowanych danych (C#)

Autor : Scott Mitchell

Pobierz plik PDF

W poprzednim samouczku przedstawiono sposób implementowania niestandardowego stronicowania podczas prezentowania danych na stronie internetowej. W tym samouczku przedstawiono sposób rozszerzania poprzedniego przykładu w celu uwzględnienia obsługi sortowania niestandardowych stronicowania.

Wprowadzenie

W porównaniu do domyślnego stronicowania niestandardowe stronicowanie może poprawić wydajność stronicowania danych o kilka rzędów wielkości, dzięki czemu niestandardowe stronicowanie de facto wybór implementacji stronicowania podczas stronicowania dużych ilości danych. Implementowanie niestandardowego stronicowania jest bardziej zaangażowane niż implementowanie domyślnego stronicowania, jednak szczególnie podczas dodawania sortowania do mieszanki. W tym samouczku rozszerzymy przykład z poprzedniego, aby uwzględnić obsługę sortowania i niestandardowego stronicowania.

Uwaga

Ponieważ ten samouczek opiera się na poprzednim, przed rozpoczęciem poświęć chwilę na skopiowanie składni deklaratywnej w <asp:Content> elemecie z poprzedniej strony internetowej samouczka (EfficientPaging.aspx) i wklejenie jej między <asp:Content> elementem na SortParameter.aspx stronie. Wróć do kroku 1 samouczka Dodawanie kontrolek walidacji do samouczka Edytowanie i wstawianie interfejsów , aby uzyskać bardziej szczegółowe omówienie replikowania funkcji jednej strony ASP.NET do innej.

Krok 1. Ponowne analizowanie niestandardowej techniki stronicowania

Aby niestandardowe stronicowanie działało prawidłowo, musimy zaimplementować pewną technikę, która może efektywnie pobierać określony podzbiór 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 microsoft SQL Server 2005. Krótko mówiąc, ROW_NUMBER() funkcja klasyfikacji przypisuje numer wiersza do każdego wiersza zwracanego 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 tych produktów ponumerowanych od 11 do 20 podczas klasyfikowania wyników uporządkowanych alfabetycznie przez :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 wyświetlenia wyników posortowanych według innego wyrażenia sortowania. W idealnym przypadku powyższe zapytanie może 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ść:

• Pisanie zakodowanych na kodzie zapytań dla każdego z wyrażeń sortowania, które mogą być używane; następnie użyj IF/ELSE instrukcji języka T-SQL, aby określić, które zapytanie ma zostać wykonane.
• Aby uzyskać więcej informacji, zobacz sekcję CASE Używane do dynamicznego ORDER BY@sortExpressio sortowania wyników zapytania w instrukcjach języka T-SQLCASE, korzystając z instrukcji dynamicznych.
• Utwórz odpowiednie zapytanie jako ciąg w procedurze składowanej, a następnie użyj systemowej sp_executesql procedury składowanej 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 pola sortowalne do kontrolki GridView, należy 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ż ciąg, a także ma te same problemy z utrzymaniem co pierwszy. A trzeci wybór, który używa dynamicznego kodu SQL, wprowadza ryzyko ataku polegający na wstrzyknięciu kodu 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 dwóch pozostałych nie. Ponadto atak polegający na wstrzyknięciu kodu SQL może zostać wykorzystany 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 chroni te parametry 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 akceptować trzy parametry wejściowe: @sortExpression, parametr wejściowy typu nvarchar(100), który określa sposób sortowania wyników i jest wstrzykiwany bezpośrednio po ORDER BY tekście w OVER klauzuli ; i i @startRowIndex@maximumRows, te same dwa parametry wejściowe liczby całkowitej z GetProductsPaged procedury składowanej 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 rozpoczyna się od upewnienia się, że określono wartość parametru @sortExpression . 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 kategorii skojarzonych z poszczególnymi produktami i dostawcami przy użyciu podzapytania. Ta decyzja została podjęta z powrotem w samouczku Tworzenie warstwy dostępu do danych i została wykonana zamiast użycia JOIN elementu s, ponieważ funkcja TableAdapter nie może automatycznie utworzyć skojarzonych metod wstawiania, aktualizowania i usuwania dla takich zapytań. Procedura GetProductsPagedAndSorted składowana musi jednak używać JOIN wartości s, aby wyniki są uporządkowane według kategorii lub nazw dostawców.

To dynamiczne zapytanie jest tworzone przez łączenie części statycznych zapytań i @sortExpressionparametrów , @startRowIndexi @maximumRows . Ponieważ @startRowIndex parametry i @maximumRows są parametrami całkowitymi, należy je przekonwertować na nvarchars, aby można je było poprawnie połączyć. Po utworzeniu tego dynamicznego zapytania SQL 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, @startRowIndexi @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 kolejności 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 danych wyjściowych (kliknij, aby wyświetlić obraz pełnowymiarowy)

Uwaga

Podczas klasyfikowania wyników według określonej ORDER BY kolumny w klauzuli OVER SQL Server musi posortować wyniki. Jest to szybka operacja, jeśli istnieje indeks grupowany w kolumnach, według których wyniki są uporządkowane według lub jeśli istnieje indeks obejmujący, ale w przeciwnym razie może być bardziej kosztowne. Aby zwiększyć wydajność dla wystarczająco dużych zapytań, rozważ dodanie indeksu nieklasowanego dla kolumny, według której wyniki są uporządkowane według. Aby uzyskać więcej informacji, zobacz Funkcje klasyfikacji i wydajność w 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 pośrednictwem naszej architektury aplikacji. Wiąże się to z dodaniem odpowiedniej metody zarówno do dal, jak i BLL. Zacznijmy od dodania metody do dal. Otwórz typowy zestaw Northwind.xsd danych, kliknij prawym przyciskiem myszy ProductsTableAdapterpozycję , 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. Wybieranie użycia istniejącej procedury składowanej

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

Rysunek 4. Użycie procedury składowanej GetProductsPagedAndSorted

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

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

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

Rysunek 6. Wybieranie nazw metod

Teraz, gdy rozszerzyliśmy dal, możemy przystąpić do korzystania z BLL. ProductsBLL Otwórz plik klasy i dodaj nową metodę GetProductsPagedAndSorted. Ta metoda musi akceptować trzy parametry sortExpressionwejściowe , 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 obiektu ObjectDataSource do przekazania w parametrze SortExpression

Po rozszerzeniu dal i BLL w celu uwzględnienia metod korzystających z GetProductsPagedAndSorted procedury składowanej, wystarczy skonfigurować obiekt ObjectDataSource na SortParameter.aspx stronie, aby użyć nowej metody BLL i przekazać SortExpression parametr 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 z okno 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, właściwość ObjectDataSource próbuje przekazać właściwość GridView SortExpression do obiektu SelectMethod. W szczególności obiekt ObjectDataSource szuka parametru wejściowego, którego nazwa jest równa wartości SortParameterName właściwości. Ponieważ metoda BLL GetProductsPagedAndSorted ma parametr wejściowy wyrażenia sortowania o nazwie sortExpression, 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

Podobnie jak w poprzednim samouczku, upewnij się, że właściwość ObjectDataSource nie zawiera parametrów wejściowych sortExpression, startRowIndex ani 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ść true GridView AllowSorting na i powoduje renderowanie tekstu nagłówka dla każdej kolumny jako linkButton. Gdy użytkownik końcowy kliknie jeden z nagłówków LinkButtons, następuje zakończenie ogłaszania zwrotnego i wykonanie następujących kroków:

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 SortExpression jako wartość parametru wejściowego metody sortExpression (wraz z odpowiednimi startRowIndex wartościami parametrów wejściowych i maximumRows wejściowych)
3. BLL wywołuje metodę DAL GetProductsPagedAndSorted
4. Funkcja DAL wykonuje procedurę GetProductsPagedAndSorted składowaną, przekazując @sortExpression parametr (wraz z wartościami parametrów @startRowIndex wejściowych 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 kolejności rosnącej UnitPrice .

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

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

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

Ten wyjątek występuje, ponieważ właściwość SortExpression BoundField kontrolki SupplierName GridView jest ustawiona na SupplierNamewartość . Jednak nazwa dostawcy w Suppliers tabeli jest w rzeczywistości nazywana CompanyName aliasem tej nazwy kolumny 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ń pole SupplierName BoundField z SortExpression SupplierName na CompanyName (zobacz Rysunek 9). Jak pokazano na rysunku 10, po tej zmianie wyniki można sortować według dostawcy.

Rysunek 9. Zmiana wartości SortExpression elementu SupplierName BoundField na CompanyName

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

Podsumowanie

Niestandardowa implementacja stronicowania zbadana w poprzednim samouczku wymagała określenia kolejności sortowania wyników 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ą od pierwszego do uwzględnienia parametru wejściowego @sortExpression , za pomocą którego można sortować wyniki.

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 sortowanie i stronicowanie niestandardowe, konfigurując obiekt ObjectDataSource w celu przekazania bieżącej SortExpression właściwości GridView do biblioteki 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 do niego dotrzeć pod adresem mitchell@4GuysFromRolla.com. Lub za pośrednictwem swojego bloga, który można znaleźć na stronie http://ScottOnWriting.NET.

Specjalne podziękowania

Ta seria samouczków została przejrzyona przez wielu przydatnych recenzentów. Głównym recenzentem tego samouczka był Carlos Santos. Chcesz przejrzeć nadchodzące artykuły MSDN? Jeśli tak, upuść mi wiersz pod adresemmitchell@4GuysFromRolla.com .

WsteczDalej