Sortowanie niestandardowo stronicowanych danych (VB)

Autor: Scott Mitchell

Pobierz plik PDF

W poprzednim samouczku pokazano, jak zaimplementować niestandardowe stronicowanie podczas prezentowania danych na stronie internetowej. W tym samouczku zobaczymy, jak rozszerzyć poprzedni przykład, aby uwzględnić obsługę sortowania niestandardowych stronicowania.

Wprowadzenie

W porównaniu do domyślnego stronicowania niestandardowe stronicowanie może zwiększyć wydajność stronicowania za pośrednictwem 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. Implementacja niestandardowego stronicowania jest bardziej zaangażowana niż implementacja 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 kopiowania składni deklaratywnej w <asp:Content> elemecie z poprzedniej strony internetowej samouczka (EfficientPaging.aspx) i wklej ją między <asp:Content> elementem na SortParameter.aspx stronie. Zapoznaj się z artykułem Krok 1 samouczka Dodawanie kontrolek weryfikacji do samouczka Edytowanie i wstawianie interfejsów , aby uzyskać bardziej szczegółową dyskusję na temat 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 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, korzystając z 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 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 tych produktów numerowanych 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ą @sortExpression , która akceptuje 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.
• CASE Użyj instrukcji , aby podać wyrażenia dynamiczne ORDER BY na podstawie n parametru wejściowego. Aby uzyskać więcej informacji, zobacz sekcję Używane do dynamicznego @sortExpressio sortowania wyników zapytań w instrukcjach języka T-SQLCASE.
• Utwórz odpowiednie zapytanie jako ciąg w procedurze składowanej, a następnie użyj sp_executesql procedury składowanej systemu, aby wykonać zapytanie dynamiczne.

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, musisz również wrócić i zaktualizować procedurę składowaną. Drugie podejście ma pewne subtelności, które powodują problemy z wydajnością podczas sortowania według kolumn bazy danych innych niż ciąg, a także cierpi na te same problemy z utrzymaniem co pierwszy. Trzeci wybór, który korzysta z dynamicznego kodu 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 pozostałych dwóch nie. Ponadto atak polegający na wstrzyknięciu kodu SQL można wykorzystać 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 wysyłane do bazy danych za pośrednictwem architektury, co oznacza, że luka w zabezpieczeniach umożliwiająca atak polegający 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 zbadanej 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 brakuje go, wyniki są klasyfikowane według ProductID. Następnie tworzone jest dynamiczne zapytanie SQL. Zwróć uwagę, że dynamiczne zapytanie SQL w tym miejscu 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 podquerii. Ta decyzja została podjęta z powrotem w samouczku Tworzenie warstwy dostępu do danych i została wykonana zamiast użycia s JOIN , 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ć 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ą liczbą całkowitą, należy je przekonwertować na nvarchars w celu poprawnego łączenia. Po utworzeniu dynamicznego zapytania SQL jest ono wykonywane za pomocą polecenia sp_executesql.

Poświęć chwilę, aby przetestować tę procedurę składowaną 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 parametru CategoryName dla wartości parametru @sortExpression . 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 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 klastrowany w kolumnach, według których wyniki są uporządkowane według lub jeśli istnieje indeks obejmujący, ale może być bardziej kosztowne w przeciwnym razie. Aby zwiększyć wydajność dla wystarczająco dużych zapytań, rozważ dodanie indeksu nieklasowanego dla kolumny, według której wyniki są uporządkowane. 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 zapewnienie sposobu 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 Northwind.xsd zestaw danych, kliknij prawym przyciskiem myszy ProductsTableAdapterelement , a następnie wybierz opcję Dodaj zapytanie z menu kontekstowego. Tak jak w poprzednim samouczku, chcemy skonfigurować tę nową metodę DAL do korzystania 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 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żywanie 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ą zarówno metody Fill a DataTable, jak i Return a DataTable, nazewnictwa metod FillPagedAndSorted i GetProductsPagedAndSorted, odpowiednio.

Rysunek 6. Wybieranie nazw metod

Teraz, gdy rozszerzyliśmy dal, jesteśmy gotowi zwrócić się do BLL. ProductsBLL Otwórz plik klasy i dodaj nową metodę GetProductsPagedAndSorted. Ta metoda musi akceptować trzy parametry sortExpressionwejściowe , startRowIndexi maximumRows powinna po prostu wywołać metodę DAL GetProductsPagedAndSorted w następujący sposób:

<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. Konfigurowanie obiektuDataSource do przekazania w parametrze SortExpression

Rozszerzenie dal i BLL w celu uwzględnienia metod korzystających z GetProductsPagedAndSorted procedury składowanej polega na skonfigurowaniu obiektu ObjectDataSource na SortParameter.aspx stronie w celu użycia nowej metody BLL i przekazania parametru SortExpression na podstawie kolumny, według której użytkownik zażądał sortowania wyników.

Zacznij od zmiany obiektu ObjectDataSource z SelectMethodGetProductsPaged na GetProductsPagedAndSorted. Można to zrobić za pomocą Kreatora konfigurowania źródła danych z okno Właściwości lub bezpośrednio za pośrednictwem składni deklaratywnej. Następnie musimy podać wartość właściwości ObjectDataSource.SortParameterName Jeśli ta właściwość jest ustawiona, obiekt ObjectDataSource próbuje przekazać właściwość GridView SortExpression do SelectMethodobiektu . 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 s 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 przypadku poprzedniego samouczka, upewnij się, że obiekt 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ść 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