Benutzerdefinierte ausgelagerte Daten sortieren (C#)

von Scott Mitchell

PDF herunterladen

Im vorherigen Lernprogramm haben wir gelernt, wie wir beim Darstellen von Daten auf einer Webseite benutzerdefinierte Seitennummerierung implementieren. In diesem Tutorial erfahren Sie, wie Sie das vorangehende Beispiel erweitern, um Unterstützung für das Sortieren von benutzerdefiniertem Paging einzuschließen.

Einleitung

Im Vergleich zur Standard-Paginierung kann die benutzerdefinierte Paginierung die Leistung der Paginierung durch Daten um mehrere Größenordnungen verbessern, was die benutzerdefinierte Paginierung zur bevorzugten Implementierungsoption macht, wenn es darum geht, große Datenmengen zu paginieren. Die Implementierung benutzerdefinierter Pagings ist aufwendiger als die Implementierung von Standard-Paging, insbesondere wenn Sortierung hinzugefügt wird. In diesem Tutorial erweitern wir das Beispiel aus dem vorherigen, um Unterstützung für das Sortieren und benutzerdefiniertes Paging hinzuzufügen.

Hinweis

Bevor Sie beginnen, nehmen Sie sich einen Moment Zeit, um die deklarative Syntax innerhalb des <asp:Content> Elements von der vorherigen Lernprogramm-Webseite (EfficientPaging.aspx) zu kopieren und zwischen das <asp:Content> Element auf der SortParameter.aspx Seite einzufügen. In Schritt 1 des Lernprogramms Hinzufügen von Überprüfungssteuerelementen zu den Schnittstellen zum Bearbeiten und Einfügen finden Sie eine ausführlichere Erläuterung, um die Funktionalität von einer ASP.NET-Seite auf eine andere zu replizieren.

Schritt 1: Erneute Überprüfung der benutzerdefinierten Pagingtechnik

Damit benutzerdefiniertes Paging korrekt funktioniert, müssen wir eine Technik implementieren, die eine bestimmte Teilmenge von Datensätzen effizient abrufen kann, wenn die Parameter "Start Row Index" und "Maximum Rows" angegeben sind. Es gibt eine Handvoll Techniken, die verwendet werden können, um dieses Ziel zu erreichen. Im vorherigen Lernprogramm haben wir uns damit beschäftigt, dies mithilfe der neuen ROW_NUMBER() Bewertungsfunktion von Microsoft SQL Server 2005 zu erreichen. Kurz gesagt, die ROW_NUMBER() Rangfolgenfunktion weist jeder Zeile, die von einer Abfrage zurückgegeben wird, eine Zeilennummer zu, die von einer angegebenen Sortierreihenfolge bewertet wird. Die entsprechende Teilmenge der Datensätze wird dann durch Zurückgeben eines bestimmten Abschnitts der nummerierten Ergebnisse abgerufen. Die folgende Abfrage veranschaulicht, wie Sie diese Technik verwenden, um die Produkte mit den Nummern 11 bis 20 zurückzugeben, wenn die Ergebnisse alphabetisch sortiert nach ProductName worden sind.

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 eignet sich gut für das Paging mithilfe einer bestimmten Sortierreihenfolge (ProductName alphabetisch sortiert in diesem Fall), die Abfrage muss jedoch geändert werden, um die Ergebnisse nach einem anderen Sortierausdruck sortiert anzuzeigen. Im Idealfall könnte die obige Abfrage umgeschrieben werden, um einen Parameter in der OVER Klausel zu verwenden, z. 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

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 verwendeten Sortierausdrücke; 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 "Used to Dynamically Sort Query Results" 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 bearbeitbar wie die anderen beiden, da sie erfordert, dass Sie eine Abfrage für jeden möglichen Sortierausdruck erstellen. Wenn Sie sich später für das Hinzufügen neuer, sortierbarer Felder zum GridView entscheiden, müssen Sie auch zurückkehren und die gespeicherte Prozedur aktualisieren. Der zweite Ansatz weist einige Feinheiten auf, die Leistungsprobleme beim Sortieren nach nicht-zeichnerischen Datenbankspalten einführen und leidet auch unter den gleichen Wartungsproblemen wie der erste Ansatz. Und die dritte Wahl, die dynamische SQL verwendet, führt das Risiko für einen SQL-Einfügeangriff ein, wenn ein Angreifer die gespeicherte Prozedur ausführen kann, die die Eingabeparameterwerte ihrer Wahl übergibt.

Obwohl keiner dieser Ansätze perfekt ist, denke ich, dass die dritte Option das Beste der drei ist. Mit der Verwendung dynamischer SQL bietet es ein Maß an Flexibilität, das die beiden anderen nicht. Darüber hinaus kann ein SQL-Einfügeangriff nur ausgenutzt werden, wenn ein Angreifer die gespeicherte Prozedur ausführen kann, 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-Einfügungsangriffen nur vorhanden ist, 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 namens 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 der OVER-Klausel eingefügt wird; sowie @startRowIndex und @maximumRows, die beiden ganzzahligen Eingabeparameter der GetProductsPaged gespeicherten Prozedur, die im vorherigen Tutorial untersucht wurde. Erstellen Sie die GetProductsPagedAndSorted gespeicherte Prozedur mithilfe des folgenden Skripts:

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 mit der Sicherstellung, dass ein Wert für den @sortExpression Parameter angegeben wurde. Wenn sie fehlt, werden die Ergebnisse nach ProductIDbewertet. 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 werden. In früheren Beispielen haben wir die zugehörigen Produktkategorien und Lieferantennamen mithilfe einer Unterabfrage abgerufen. Diese Entscheidung wurde in der Anleitung zum Erstellen einer Datenzugriffsebene getroffen und anstelle der Verwendung von JOIN getroffen, da der TableAdapter die zugehörigen Einfüge-, Aktualisierungs- und Löschmethoden für solche Abfragen nicht automatisch erstellen kann. Die GetProductsPagedAndSorted gespeicherte Prozedur muss jedoch JOIN verwenden, damit die Ergebnisse nach den Kategorie- oder Lieferantennamen sortiert werden.

Diese dynamische Abfrage wird erstellt, indem die statischen Abfrageabschnitte und die Parameter @sortExpression, @startRowIndex und @maximumRows verkettet werden. Da @startRowIndex und @maximumRows ganzzahlige Parameter sind, müssen sie in nvarchars konvertiert werden, um korrekt verkettet zu werden. Sobald 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 @sortExpression, @startRowIndexund @maximumRows Parameter zu testen. Klicken Sie im Server-Explorer 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. Klicken Sie nach dem Angeben der Parameterwerte auf "OK". Die Ergebnisse werden im Ausgabefenster angezeigt. Abbildung 2 zeigt die Ergebnisse bei der Rückgabe von Produkten, die beim Bestellen nach absteigender Reihenfolge von UnitPrice auf die Plätze 11 bis 20 kamen.

Abbildung 1: Probieren Sie unterschiedliche Werte für die drei Eingabeparameter der gespeicherten Prozedur aus.

Abbildung 2: Die Ergebnisse der gespeicherten Prozedur werden im Ausgabefenster angezeigt (Zum Anzeigen des Bilds mit voller Größe klicken)

Hinweis

Wenn die Ergebnisse nach der angegebenen ORDER BY Spalte in der OVER Klausel sortiert werden, muss SQL Server die Ergebnisse sortieren. Dies ist ein schneller Vorgang, wenn es einen gruppierten Index über den Spalten gibt, nach denen die Ergebnisse sortiert werden oder wenn ein abgedeckter Index vorhanden ist, andernfalls aber 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 Bewertungsfunktionen 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 zum DAL. Öffnen Sie das typisierte Northwind.xsd Dataset, machen Sie einen Rechtsklick auf das ProductsTableAdapter Dataset, und wählen Sie im Menü die Option "Abfrage hinzufügen" aus. Wie im vorherigen Lernprogramm möchten wir diese neue DAL-Methode so konfigurieren, dass eine vorhandene gespeicherte Prozedur verwendet wird – GetProductsPagedAndSortedin diesem Fall. Beginnen Sie, indem Sie angeben, 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 aus der Dropdownliste im nächsten Bildschirm aus.

Abbildung 4: Die gespeicherte Prozedur "GetProductsPagedAndSorted" verwenden

Diese gespeicherte Prozedur gibt eine Reihe von Datensätzen als Ergebnisse zurück, sodass im nächsten Bildschirm angegeben wird, 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 die Muster "DataTable ausfüllen" als auch "Return a DataTable" verwenden, und benennen Sie die Methoden FillPagedAndSorted bzw GetProductsPagedAndSorted.

Abbildung 6: Auswählen der Methodennamen

Nachdem wir nun die DAL erweitert haben, sind wir bereit, uns an die BLL zu wenden. Öffnen Sie die ProductsBLL Klassendatei, und fügen Sie eine neue Methode hinzu. GetProductsPagedAndSorted Diese Methode muss die drei Eingabeparameter sortExpression, startRowIndex und maximumRows akzeptieren und sollte einfach die DAL-Methode GetProductsPagedAndSorted wie folgt aufrufen:

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

Schritt 3: Konfigurieren der ObjectDataSource, um den SortExpression-Parameter zu übergeben

Nachdem die DAL und BLL um Methoden ergänzt wurden, die die GetProductsPagedAndSorted gespeicherte Prozedur nutzen, bleibt nur noch, die ObjectDataSource auf der SortParameter.aspx Seite so zu konfigurieren, dass sie die neue BLL-Methode verwendet und den SortExpression Parameter übergibt, basierend auf der Spalte, nach der der Benutzer die Ergebnisse sortieren möchte.

Starten Sie, indem Sie die SelectMethod von ObjectDataSource von GetProductsPaged zu GetProductsPagedAndSorted ändern. Dies kann über den Assistenten zum Konfigurieren von Datenquellen über das Eigenschaftenfenster oder direkt über die deklarative Syntax erfolgen. Als Nächstes müssen wir einen Wert für die ObjectDataSource s-EigenschaftSortParameterName angeben. Wenn diese Eigenschaft festgelegt ist, versucht die ObjectDataSource, die GridView-Eigenschaft SortExpression an die SelectMethod weiterzugeben. Insbesondere sucht objectDataSource nach einem Eingabeparameter, dessen Name dem Wert der SortParameterName Eigenschaft entspricht. Da die BLL-Methode den Eingabeparameter für den Sortierausdruck namens GetProductsPagedAndSorted hat, konfigurieren Sie die Eigenschaft sortExpression der ObjectDataSource auf sortExpression.

Nachdem Sie diese beiden Änderungen vorgenommen haben, 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 Lernprogramm sicher, dass die ObjectDataSource nicht die Eingabeparameter "sortExpression", "startRowIndex" oder "maximumRows" in der SelectParameters-Auflistung enthält.

Um die Sortierung in der GridView zu aktivieren, aktivieren Sie einfach das Kontrollkästchen "Sortierung aktivieren" im Smart-Tag der GridView. Dadurch wird die Eigenschaft der GridView AllowSorting auf true festgelegt und der Kopfzeilentext jeder Spalte als LinkButton dargestellt. Wenn der Endbenutzer auf eine der KopfzeilenlinkButtons klickt, folgt ein Postback und die folgenden Schritte:

1. Das GridView aktualisiert seine SortExpression Eigenschaft auf den Wert des SortExpression Felds, auf dessen Kopfzeilenlink geklickt wurde.
2. Die ObjectDataSource ruft die BLL-Methode GetProductsPagedAndSorted auf und übergibt die GridView-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 s-Methode GetProductsPagedAndSorted auf.
4. Die DAL führt die gespeicherte Prozedur GetProductsPagedAndSorted aus und übergibt den Parameter @sortExpression (zusammen mit den Werten der Eingabeparameter @startRowIndex 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 der UnitPrice aufsteigenden Reihenfolge sortiert wird.

Abbildung 7: Die Ergebnisse werden nach dem Einzelpreis sortiert (Klicken Sie, um das Bild in voller Größe anzuzeigen)

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

Abbildung 8: Sortieren der Ergebnisse nach Lieferantenergebnissen in der folgenden Laufzeitausnahme

Diese Ausnahme tritt auf, weil das BoundField des GridView-Steuerelements auf SortExpression gesetzt ist. Der Name des Lieferanten in der Suppliers-Tabelle lautet tatsächlich CompanyName, wir haben diesen Spaltennamen als SupplierName aliasiert. Die OVER von der ROW_NUMBER() Funktion verwendete Klausel kann jedoch nicht den Alias verwenden und muss den tatsächlichen Spaltennamen verwenden. Ändern Sie daher " SupplierName BoundField s SortExpression " von "SupplierName" in "CompanyName" (siehe Abbildung 9). Wie in Abbildung 10 dargestellt, können die Ergebnisse nach dieser Änderung vom Lieferanten sortiert werden.

Ändern Sie das SortExpression des SupplierName-BoundField auf CompanyName

Abbildung 9: Ändern des "SupplierName BoundField s SortExpression" in "CompanyName"

Abbildung 10: Die Ergebnisse können jetzt nach Lieferanten sortiert werden (Zum Anzeigen des Bilds mit voller Größe klicken)

Zusammenfassung

Die benutzerdefinierte Pagingimplementierung, die wir im vorherigen Lernprogramm untersucht haben, erforderte, dass die Reihenfolge, nach der die Ergebnisse sortiert werden sollen, zur Entwurfszeit angegeben werden musste. Kurz gesagt bedeutete dies, dass die implementierte benutzerdefinierte Pagingimplementierung nicht gleichzeitig Sortierfunktionen bereitstellen konnte. In diesem Lernprogramm haben wir diese Einschränkung überwunden, indem wir die gespeicherte Prozedur von der ersten Version um einen @sortExpression Eingabeparameter erweitert haben, nach dem die Ergebnisse sortiert werden können.

Nach dem Erstellen dieser gespeicherten Prozedur und dem Erstellen neuer Methoden in DAL und BLL konnten wir ein GridView implementieren, das sowohl Sortier- als auch benutzerdefinierte Paging bietet, indem wir die ObjectDataSource so konfigurieren, dass die aktuelle SortExpression Eigenschaft von GridView an die BLL SelectMethodübergeben wird.

Glückliche Programmierung!

Zum Autor

Scott Mitchell, Autor von sieben ASP/ASP.NET Büchern und Gründer von 4GuysFromRolla.com, arbeitet seit 1998 mit Microsoft Web Technologies zusammen. Scott arbeitet als unabhängiger Berater, Trainer und Schriftsteller. Sein neuestes Buch ist Sams Teach Yourself ASP.NET 2.0 in 24 Stunden. Er kann bei mitchell@4GuysFromRolla.comerreicht werden.

Besonderer Dank an

Diese Lernprogrammreihe wurde von vielen hilfreichen Prüfern überprüft. Lead Reviewer für dieses Lernprogramm war Carlos Santos. Möchten Sie meine bevorstehenden MSDN-Artikel überprüfen? Wenn ja, schicken Sie mir eine Nachricht an mitchell@4GuysFromRolla.com.

ZurückWeiter