Sortieren von benutzerdefinierten ausgelagerten Daten (VB)

von Scott Mitchell

PDF herunterladen

Im vorherigen Tutorial haben wir gelernt, wie Sie benutzerdefiniertes Paging bei der Darstellung von Daten auf einer Webseite implementieren. In diesem Tutorial erfahren Sie, wie Sie das vorherige Beispiel erweitern, um die Unterstützung für das Sortieren von benutzerdefiniertem Paging einzubeziehen.

Einführung

Im Vergleich zum Standardpa paging kann benutzerdefiniertes Paging die Leistung des Pagings durch Daten um mehrere Größenordnungen verbessern, sodass benutzerdefinierte Pagingimplementierung beim Durchlaufen großer Datenmengen die De-facto-Pagingimplementierungsoption ist. Die Implementierung benutzerdefinierter Pagings ist jedoch wichtiger als die Implementierung von Standardpa paging, insbesondere beim Hinzufügen der Sortierung zur Mischung. In diesem Tutorial erweitern wir das Beispiel aus dem vorherigen, um die Unterstützung für Sortierung und benutzerdefiniertes Paging zu enthalten.

Hinweis

Da dieses Tutorial auf dem vorherigen Tutorial basiert, nehmen Sie sich einen Moment Zeit, um die deklarative Syntax innerhalb des Elements von der <asp:Content> vorherigen Tutorial-Webseite (EfficientPaging.aspx) zu kopieren und zwischen das <asp:Content> Element auf der SortParameter.aspx Seite einzufügen. Unter Schritt 1 des Tutorials Zum Hinzufügen von Validierungssteuerelementen zum Bearbeiten und Einfügen von Schnittstellen finden Sie eine ausführlichere Diskussion zum Replizieren der Funktionalität einer ASP.NET Seite auf eine andere.

Schritt 1: Erneutes Testen der benutzerdefinierten Pagingtechnik

Damit das benutzerdefinierte Paging ordnungsgemäß funktioniert, müssen wir eine Technik implementieren, mit der eine bestimmte Teilmenge von Datensätzen unter Berücksichtigung der Parameter Startzeilenindex und Maximale Zeilen effizient erfasst werden kann. Es gibt eine Handvoll Techniken, die verwendet werden können, um dieses Ziel zu erreichen. Im vorherigen Tutorial haben wir dies mithilfe der neuen ROW_NUMBER() Rangfolgefunktion von Microsoft SQL Server 2005 untersucht. Kurz gesagt, die Rangfolgenfunktion weist jeder Zeile, die ROW_NUMBER() von einer Abfrage zurückgegeben wird, eine Zeilennummer zu, die nach einer angegebenen Sortierreihenfolge sortiert wird. Die entsprechende Teilmenge der Datensätze wird dann abgerufen, indem ein bestimmter Abschnitt der nummerierten Ergebnisse zurückgegeben wird. Die folgende Abfrage veranschaulicht, wie Sie diese Technik verwenden, um die Produkte mit den Nummern 11 bis 20 zurückzugeben, wenn die Ergebnisse alphabetisch nach sortiert ProductNamewerden:

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

Diese Technik funktioniert gut für das Paging mit einer bestimmten Sortierreihenfolge (ProductName in diesem Fall alphabetisch sortiert), aber die Abfrage muss geändert werden, um die Ergebnisse nach einem anderen Sortierausdruck sortiert anzuzeigen. Idealerweise kann die obige Abfrage wie folgt umgeschrieben werden, um einen Parameter in der OVER -Klausel zu verwenden:

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

Leider sind parametrisierte ORDER BY Klauseln nicht zulässig. Stattdessen müssen wir eine gespeicherte Prozedur erstellen, die einen @sortExpression Eingabeparameter akzeptiert, aber eine der folgenden Problemumgehungen verwendet:

• Schreiben Sie hartcodierte Abfragen für jeden der sortierausdrücke, die verwendet werden können; Verwenden Sie IF/ELSE dann T-SQL-Anweisungen, um zu bestimmen, welche Abfrage ausgeführt werden soll.
• Verwenden Sie eine CASE -Anweisung, um dynamische ORDER BY Ausdrücke basierend auf dem @sortExpressio n-Eingabeparameter bereitzustellen. Weitere Informationen finden Sie im Abschnitt Verwendet zum dynamischen Sortieren von Abfrageergebnissen in T-SQL-AnweisungenCASE.
• Erstellen Sie die entsprechende Abfrage als Zeichenfolge in der gespeicherten Prozedur, und verwenden Sie dann die sp_executesql gespeicherte Systemprozedur , um die dynamische Abfrage auszuführen.

Jede dieser Problemumgehungen hat einige Nachteile. Die erste Option ist nicht so verwaltbar wie die anderen beiden, da Sie eine Abfrage für jeden möglichen Sortierausdruck erstellen müssen. Wenn Sie sich später entscheiden, der GridView neue, sortierbare Felder hinzuzufügen, müssen Sie auch die gespeicherte Prozedur aktualisieren. Der zweite Ansatz hat einige Feinheiten, die Leistungsprobleme beim Sortieren nach nicht zeichenfolgenbasierten Datenbankspalten mit sich bringen und auch unter den gleichen Wartungsproblemen wie der erste leiden. Und die dritte Wahl, die dynamisches SQL verwendet, führt das Risiko für einen SQL-Einschleusungsangriff ein, wenn ein Angreifer in der Lage ist, die gespeicherte Prozedur auszuführen, die die Eingabeparameterwerte seiner Wahl übergibt.

Obwohl keiner dieser Ansätze perfekt ist, denke ich, dass die dritte Option die beste der drei ist. Mit seiner Verwendung von dynamischem SQL bietet es ein Maß an Flexibilität, das die anderen beiden nicht haben. Darüber hinaus kann ein SQL-Einschleusungsangriff nur ausgenutzt werden, wenn ein Angreifer in der Lage ist, die gespeicherte Prozedur auszuführen, die die Eingabeparameter seiner Wahl übergibt. Da die DAL parametrisierte Abfragen verwendet, schützt ADO.NET die Parameter, die über die Architektur an die Datenbank gesendet werden, was bedeutet, dass die Sicherheitsanfälligkeit bei sql-Einschleusung nur dann besteht, wenn der Angreifer die gespeicherte Prozedur direkt ausführen kann.

Um diese Funktionalität zu implementieren, erstellen Sie eine neue gespeicherte Prozedur in der Northwind-Datenbank mit dem Namen GetProductsPagedAndSorted. Diese gespeicherte Prozedur sollte drei Eingabeparameter akzeptieren: @sortExpression, einen Eingabeparameter vom Typ nvarchar(100), der angibt, wie die Ergebnisse sortiert werden sollen und direkt nach dem ORDER BY Text in die OVER -Klausel eingefügt werden sollen; und @startRowIndex und und @maximumRows, die gleichen zwei ganzzahligen Eingabeparameter aus der gespeicherten Prozedur, die GetProductsPaged im vorherigen Tutorial untersucht wurden. Erstellen Sie die gespeicherte GetProductsPagedAndSorted Prozedur mit dem folgenden Skript:

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

Die gespeicherte Prozedur beginnt damit, dass ein Wert für den @sortExpression Parameter angegeben wurde. Wenn es fehlt, werden die Ergebnisse nach ProductIDsortiert. Als Nächstes wird die dynamische SQL-Abfrage erstellt. Beachten Sie, dass sich die dynamische SQL-Abfrage hier geringfügig von den vorherigen Abfragen unterscheidet, die zum Abrufen aller Zeilen aus der Tabelle Products verwendet wurden. In früheren Beispielen haben wir die Namen der einzelnen Produktkategorien und Lieferanten mithilfe einer Unterabfrage abgerufen. Diese Entscheidung wurde im Tutorial Erstellen einer Datenzugriffsebene wieder getroffen und wurde anstelle der Verwendung JOIN von s durchgeführt, da tableAdapter die zugeordneten Einfüge-, Update- und Löschmethoden für solche Abfragen nicht automatisch erstellen kann. Die GetProductsPagedAndSorted gespeicherte Prozedur muss jedoch s verwenden JOIN , damit die Ergebnisse nach den Kategorien- oder Lieferantennamen sortiert werden.

Diese dynamische Abfrage wird erstellt, indem die statischen Abfrageteile und die @sortExpressionParameter , @startRowIndexund @maximumRows verkettet werden. Da @startRowIndex und @maximumRows ganzzahlige Parameter sind, müssen sie in nvarchars konvertiert werden, um ordnungsgemäß verkettet zu werden. Nachdem diese dynamische SQL-Abfrage erstellt wurde, wird sie über sp_executesqlausgeführt.

Nehmen Sie sich einen Moment Zeit, um diese gespeicherte Prozedur mit unterschiedlichen Werten für die @sortExpressionParameter , @startRowIndexund @maximumRows zu testen. Klicken Sie im Explorer Server mit der rechten Maustaste auf den Namen der gespeicherten Prozedur, und wählen Sie Ausführen aus. Dadurch wird das Dialogfeld Gespeicherte Prozedur ausführen angezeigt, in das Sie die Eingabeparameter eingeben können (siehe Abbildung 1). Um die Ergebnisse nach dem Kategorienamen zu sortieren, verwenden Sie CategoryName für den @sortExpression Parameterwert. Verwenden Sie CompanyName, um nach dem Firmennamen des Lieferanten zu sortieren. Nachdem Sie die Parameterwerte angegeben haben, klicken Sie auf OK. Die Ergebnisse werden im Ausgabefenster angezeigt. Abbildung 2 zeigt die Ergebnisse bei der Rückgabe von Produkten, die bei der Bestellung nach UnitPrice in absteigender Reihenfolge den Rang 11 bis 20 haben.

Abbildung 1: Testen unterschiedlicher Werte für die drei Eingabeparameter der gespeicherten Prozedur

Abbildung 2: Die Ergebnisse der gespeicherten Prozeduren werden im Ausgabefenster angezeigt (Klicken Sie, um das bild in voller Größe anzuzeigen)

Hinweis

Beim Sortieren der Ergebnisse nach der angegebenen ORDER BY Spalte in der OVER -Klausel müssen SQL Server die Ergebnisse sortieren. Dies ist ein schneller Vorgang, wenn ein gruppierter Index über den Spalten vorhanden ist, nach denen die Ergebnisse sortiert werden, oder wenn ein abdeckender Index vorhanden ist, der andernfalls teurer sein kann. Um die Leistung für ausreichend große Abfragen zu verbessern, sollten Sie einen nicht gruppierten Index für die Spalte hinzufügen, nach der die Ergebnisse sortiert werden. Weitere Informationen finden Sie unter Rangfolgefunktionen und Leistung in SQL Server 2005.

Schritt 2: Erweitern der Datenzugriffs- und Geschäftslogikebenen

Mit der GetProductsPagedAndSorted erstellten gespeicherten Prozedur besteht unser nächster Schritt darin, eine Möglichkeit zum Ausführen dieser gespeicherten Prozedur über unsere Anwendungsarchitektur bereitzustellen. Dies bedeutet, dass sowohl der DAL als auch der BLL eine geeignete Methode hinzugefügt wird. Beginnen wir mit dem Hinzufügen einer Methode zur DAL. Öffnen Sie das Northwind.xsd Typisierte DataSet, klicken Sie mit der rechten Maustaste auf das ProductsTableAdapter, und wählen Sie im Kontextmenü die Option Abfrage hinzufügen aus. Wie im vorherigen Tutorial möchten wir diese neue DAL-Methode so konfigurieren, dass eine vorhandene gespeicherte Prozedur verwendet wird– GetProductsPagedAndSortedin diesem Fall. Geben Sie zunächst an, dass die neue TableAdapter-Methode eine vorhandene gespeicherte Prozedur verwenden soll.

Abbildung 3: Auswählen einer vorhandenen gespeicherten Prozedur

Um die zu verwendende gespeicherte Prozedur anzugeben, wählen Sie die GetProductsPagedAndSorted gespeicherte Prozedur in der Dropdownliste auf dem nächsten Bildschirm aus.

Abbildung 4: Verwenden der gespeicherten GetProductsPagedAndSorted-Prozedur

Diese gespeicherte Prozedur gibt eine Reihe von Datensätzen als Ergebnisse zurück, sodass sie auf dem nächsten Bildschirm angeben, dass tabellarische Daten zurückgegeben werden.

Abbildung 5: Angeben, dass die gespeicherte Prozedur tabellarische Daten zurückgibt

Erstellen Sie schließlich DAL-Methoden, die sowohl das Muster Fill a DataTable als auch Return a DataTable verwenden, wobei die Methoden bzwGetProductsPagedAndSorted. die -Methoden FillPagedAndSorted benannt werden.

Abbildung 6: Auswählen der Methodennamen

Nachdem wir die DAL erweitert haben, sind wir bereit, uns der BLL zuzuwenden. Öffnen Sie die ProductsBLL Klassendatei, und fügen Sie die neue Methode hinzu. GetProductsPagedAndSorted Diese Methode muss drei Eingabeparameter sortExpressionakzeptieren, startRowIndexund maximumRows und sollte einfach in die DAL-Methode GetProductsPagedAndSorted wie folgt aufrufen:

<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

Schritt 3: Konfigurieren der ObjectDataSource für die Übergabe im SortExpression-Parameter

Nachdem die DAL und BLL um Methoden erweitert wurden, die die GetProductsPagedAndSorted gespeicherte Prozedur verwenden, müssen Sie nur noch die ObjectDataSource auf der SortParameter.aspx Seite konfigurieren, um die neue BLL-Methode zu verwenden und den Parameter basierend auf der SortExpression Spalte zu übergeben, nach der der Benutzer zum Sortieren der Ergebnisse angefordert hat.

Beginnen Sie mit dem Ändern der ObjectDataSource s SelectMethod von GetProductsPaged in GetProductsPagedAndSorted. Dies kann über den Assistenten Zum Konfigurieren von Datenquellen, über die Eigenschaftenfenster oder direkt über die deklarative Syntax erfolgen. Als Nächstes müssen wir einen Wert für die ObjectDataSource-Eigenschaft SortParameterNamebereitstellen. Wenn diese Eigenschaft festgelegt ist, versucht objectDataSource, die GridView-Eigenschaft SortExpression an den SelectMethodzu übergeben. Insbesondere sucht die ObjectDataSource nach einem Eingabeparameter, dessen Name dem Wert der SortParameterName -Eigenschaft entspricht. Da die BLL-Methode GetProductsPagedAndSorted den Eingabeparameter sort expression namens sortExpressionhat, legen Sie die ObjectDataSource-Eigenschaft SortExpression auf sortExpression fest.

Nach diesen beiden Änderungen sollte die deklarative Syntax von ObjectDataSource wie folgt aussehen:

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

Hinweis

Stellen Sie wie im vorherigen Tutorial sicher, dass die ObjectDataSource die Eingabeparameter sortExpression, startRowIndex oder maximumRows in der SelectParameters-Auflistung nicht enthält.

Um die Sortierung in der GridView zu aktivieren, aktivieren Sie einfach das Kontrollkästchen Sortierung aktivieren im Smarttag des GridView-Tags, wodurch die GridView-Eigenschaft AllowSorting auf true festgelegt wird und der Headertext für jede Spalte als LinkButton gerendert wird. Wenn der Endbenutzer auf eine der LinkButtons-Header klickt, wird ein Postback ausgeführt, und die folgenden Schritte werden ausgeführt:

1. GridView aktualisiert seine SortExpression Eigenschaft auf den Wert des SortExpression Felds, auf dessen Headerlink geklickt wurde.
2. Die ObjectDataSource ruft die BLL-Methode auf GetProductsPagedAndSorted und übergibt die GridView s-Eigenschaft SortExpression als Wert für den Eingabeparameter der Methode sortExpression (zusammen mit den entsprechenden startRowIndex Und maximumRows Eingabeparameterwerten).
3. Die BLL ruft die DAL-Methode auf.GetProductsPagedAndSorted
4. Die DAL führt die GetProductsPagedAndSorted gespeicherte Prozedur aus und übergibt den @sortExpression Parameter (zusammen mit den @startRowIndex Eingabeparameterwerten und @maximumRows ).
5. Die gespeicherte Prozedur gibt die entsprechende Teilmenge der Daten an die BLL zurück, die sie an die ObjectDataSource zurückgibt. Diese Daten werden dann an die GridView gebunden, in HTML gerendert und an den Endbenutzer gesendet.

Abbildung 7 zeigt die erste Seite der Ergebnisse, wenn sie nach aufsteigender UnitPrice Reihenfolge sortiert wird.

Abbildung 7: Die Ergebnisse werden nach UnitPrice sortiert (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Während die aktuelle Implementierung die Ergebnisse ordnungsgemäß nach Produktname, Kategoriename, Menge pro Einheit und Stückpreis sortieren kann, führt der Versuch, die Ergebnisse nach dem Lieferantennamen zu sortieren, zu einer Laufzeitausnahme (siehe Abbildung 8).

Abbildung 8: Versuch, die Ergebnisse nach dem Lieferanten zu sortieren, führt zur folgenden Laufzeitausnahme

Diese Ausnahme tritt auf, weil das SortExpression von GridView s SupplierName BoundField auf SupplierNamefestgelegt ist. Der Name des Lieferanten in der Suppliers Tabelle heißt jedoch tatsächlich CompanyName , dass wir diesen Spaltennamen als SupplierNamealiasen haben. Die von der OVERROW_NUMBER() Funktion verwendete Klausel kann jedoch nicht den Alias verwenden und muss den tatsächlichen Spaltennamen verwenden. Ändern Sie daher die SupplierName BoundField s SortExpression von SupplierName in CompanyName (siehe Abbildung 9). Wie Abbildung 10 zeigt, können die Ergebnisse nach dieser Änderung nach dem Lieferanten sortiert werden.

Abbildung 9: Ändern von SupplierName BoundField s SortExpression in CompanyName

Abbildung 10: Die Ergebnisse können jetzt nach Lieferanten sortiert werden (Klicken Sie hier, um das bild in voller Größe anzuzeigen)

Zusammenfassung

Für die benutzerdefinierte Pagingimplementierung, die wir im vorherigen Tutorial untersucht haben, musste zur Entwurfszeit die Reihenfolge angegeben werden, nach der die Ergebnisse sortiert werden sollen. Kurz gesagt bedeutete dies, dass die implementierte Implementierung des benutzerdefinierten Pagings nicht gleichzeitig Sortierfunktionen bereitstellen konnte. In diesem Tutorial haben wir diese Einschränkung überwunden, indem wir die gespeicherte Prozedur von der ersten auf einen @sortExpression Eingabeparameter erweitern, nach dem die Ergebnisse sortiert werden können.

Nach dem Erstellen dieser gespeicherten Prozedur und dem Erstellen neuer Methoden in der DAL und BLL konnten wir eine GridView implementieren, die sowohl Sortierung als auch benutzerdefiniertes Paging bot, indem wir die ObjectDataSource so konfiguriert haben, dass die aktuelle SortExpression GridView-Eigenschaft an die BLL SelectMethodübergeben wird.

Viel Spaß beim Programmieren!

Zum Autor

Scott Mitchell, Autor von sieben ASP/ASP.NET-Büchern und Gründer von 4GuysFromRolla.com, arbeitet seit 1998 mit Microsoft-Webtechnologien. Scott arbeitet als unabhängiger Berater, Trainer und Autor. Sein neuestes Buch ist Sams Teach Yourself ASP.NET 2.0 in 24 Stunden. Er kann unter mitchell@4GuysFromRolla.comoder über seinen Blog erreicht werden, der unter http://ScottOnWriting.NETzu finden ist.

Besonderen Dank an

Diese Tutorialreihe wurde von vielen hilfreichen Prüfern überprüft. Leitender Prüfer für dieses Tutorial war Carlos Santos. Möchten Sie meine anstehenden MSDN-Artikel lesen? Wenn dies der Fall ist, legen Sie eine Zeile unter abmitchell@4GuysFromRolla.com.

ZurückWeiter