Sortieren von benutzerdefinierten ausgelagerten Daten (C#)

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 Unterstützung für das Sortieren von benutzerdefinierten Pagings zu enthalten.

Einführung

Im Vergleich zum Standard paging kann benutzerdefiniertes Paging die Leistung des Pagings durch Daten um mehrere Größenordnungen verbessern, sodass die benutzerdefinierte Pagingimplementierung bei der Pagingdurchführung durch große Datenmengen de facto gewählt wird. Das Implementieren benutzerdefinierter Pagings ist jedoch wichtiger als die Implementierung von Standard paging, insbesondere beim Hinzufügen der Sortierung zur Mischung. In diesem Tutorial erweitern wir das Beispiel aus dem vorherigen, um Unterstützung für Sortierung und benutzerdefiniertes Paging zu enthalten.

Hinweis

Da dieses Tutorial auf dem vorherigen Tutorial aufbaut, nehmen Sie sich vor Beginn einen Moment Zeit, um die deklarative Syntax innerhalb des <asp:Content> Elements aus der vorherigen Tutorialwebseite (EfficientPaging.aspx) zu kopieren und zwischen dem <asp:Content> Element auf der SortParameter.aspx Seite einzufügen. Eine ausführlichere Erläuterung zum Replizieren der Funktionalität einer ASP.NET Seite finden Sie zurück zu Schritt 1 des Tutorials Hinzufügen von Validierungssteuerelementen zum Bearbeiten und Einfügen von Schnittstellen .

Schritt 1: Erneutes Überprüfen der benutzerdefinierten Pagingtechnik

Damit das benutzerdefinierte Paging ordnungsgemäß funktioniert, müssen wir eine Technik implementieren, die eine bestimmte Teilmenge von Datensätzen effizient abrufen kann, wenn die Parameter Zeilenindex starten und Maximale Zeilen angegeben sind. 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, die nach einer angegebenen Sortierreihenfolge sortiert wird, eine Zeilennummer zu. Die entsprechende Teilmenge der Datensätze wird dann abgerufen, indem ein bestimmter Abschnitt der nummerierten Ergebnisse zurückgegeben wird. Die folgende Abfrage veranschaulicht, wie diese Technik verwendet wird, um die 11 bis 20 nummerierten Produkte zurückzugeben, wenn die Ergebnisse alphabetisch nach sortiert werden 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

Diese Technik eignet sich gut für 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. Im Idealfall könnte 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 Eingabeparameter bereitzustellen. Weitere Informationen finden Sie im Abschnitt 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 daher auch die gespeicherte Prozedur aktualisieren. Der zweite Ansatz weist einige Feinheiten auf, die Leistungsprobleme beim Sortieren nach Nicht-Zeichenfolgendatenbankspalten mit sich bringen und auch unter den gleichen Verwaltbarkeitsproblemen wie der erste leiden. Und die dritte Wahl, die dynamische SQL verwendet, birgt das Risiko für einen SQL-Einschleusungsangriff, wenn ein Angreifer die gespeicherte Prozedur ausführen kann, indem die Eingabeparameterwerte seiner Wahl übergeben werden.

Obwohl keiner dieser Ansätze perfekt ist, denke ich, dass die dritte Option die beste der drei ist. Durch die Verwendung von dynamischem SQL bietet es ein Maß an Flexibilität, das die anderen beiden nicht haben. Darüber hinaus kann ein ANGRIFF auf SQL-Einschleusung nur ausgenutzt werden, wenn ein Angreifer die gespeicherte Prozedur ausführen kann, indem die Eingabeparameter seiner Wahl übergeben werden. Da die DAL parametrisierte Abfragen verwendet, schützt ADO.NET die Parameter, die über die Architektur an die Datenbank gesendet werden, was bedeutet, dass das Sicherheitsrisiko für sql-Einschleusung 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 mit dem Namen GetProductsPagedAndSorted. Diese gespeicherte Prozedur sollte drei Eingabeparameter akzeptieren: @sortExpression, einen Eingabeparameter vom Typ nvarchar(100), der angibt, wie die Ergebnisse sortiert 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 GetProductsPagedAndSorted gespeicherte 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, indem sichergestellt wird, dass ein Wert für den @sortExpression Parameter angegeben wurde. Wenn sie 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 mit einer Unterabfrage die Namen der einzelnen Produktkategorien und Lieferanten abgerufen. Diese Entscheidung wurde im Tutorial Erstellen einer Datenzugriffsebene getroffen und anstelle der Verwendung JOIN von s durchgeführt, da der TableAdapter die zugeordneten Einfüge-, Aktualisierungs- 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 geöffnet, 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. Um nach dem Firmennamen des Lieferanten zu sortieren, verwenden Sie CompanyName. Klicken Sie nach dem Angeben der Parameterwerte auf OK. Die Ergebnisse werden im Fenster Ausgabe angezeigt. Abbildung 2 zeigt die Ergebnisse bei der Rückgabe von Produkten mit Rang 11 bis 20 bei der Bestellung nach in UnitPrice absteigender Reihenfolge.

Abbildung 1: Ausprobieren verschiedener Werte für die drei Eingabeparameter der gespeicherten Prozedur

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

Hinweis

Beim Rangieren 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

Nachdem die GetProductsPagedAndSorted gespeicherte Prozedur erstellt wurde, besteht unser nächster Schritt darin, diese gespeicherte Prozedur über unsere Anwendungsarchitektur auszuführen. Dies bedeutet, dass sowohl der DAL als auch der BLL eine geeignete Methode hinzugefügt wird. Beginnen wir damit, dem DAL eine Methode hinzuzufügen. Öffnen Sie das Northwind.xsd Typisierte DataSet, klicken Sie mit der rechten Maustaste auf , ProductsTableAdapterund 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 auf dem 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 das Muster Fill a DataTable als auch Return a DataTable verwenden, wobei sie die Methoden FillPagedAndSorted bzw GetProductsPagedAndSorted. benennen.

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 wie folgt in die DAL s-Methode GetProductsPagedAndSorted 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 für die Übergabe des SortExpression-Parameters

Nachdem DAL und BLL erweitert wurden, um Methoden einzuschließen, die die GetProductsPagedAndSorted gespeicherte Prozedur verwenden, bleibt es nur noch, die ObjectDataSource auf der SortParameter.aspx Seite zu konfigurieren, um die neue BLL-Methode zu verwenden und den SortExpression Parameter basierend auf der Spalte zu übergeben, nach der der Benutzer die Ergebnisse sortieren angefordert hat.

Ändern Sie zunächst objectDataSource von SelectMethod in GetProductsPagedGetProductsPagedAndSorted. 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 SortParameterNameangeben. Wenn diese Eigenschaft festgelegt ist, versucht die ObjectDataSource, die GridView-Eigenschaft an SortExpression den SelectMethodzu übergeben. Insbesondere sucht ObjectDataSource nach einem Eingabeparameter, dessen Name dem Wert der SortParameterName Eigenschaft entspricht. Da die BLL-Methode GetProductsPagedAndSorted den Eingabeparameter sort expression namens sortExpressionaufweist, legen Sie die ObjectDataSource-Eigenschaft SortExpression auf sortExpression fest.

Nachdem Sie diese beiden Änderungen vorgenommen haben, sollte die deklarative Syntax von ObjectDataSource in etwa 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 objectDataSource die Eingabeparameter sortExpression, startRowIndex oder maximumRows nicht in der SelectParameters-Auflistung enthält.

Um die Sortierung in GridView zu aktivieren, aktivieren Sie einfach das Kontrollkästchen Sortierung aktivieren im Smarttag von GridView, wodurch die GridView-Eigenschaft AllowSorting auf true festgelegt wird und der Headertext für jede Spalte als LinkButton gerendert wird. Wenn der Endbenutzer auf eines 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. ObjectDataSource ruft die BLL-Methode s 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-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 objectDataSource zurückgibt. Diese Daten werden dann an gridView gebunden, in HTML gerendert und an den Endbenutzer gesendet.

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

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 den Lieferantenergebnissen in der folgenden Laufzeitausnahme zu sortieren

Diese Ausnahme tritt auf, weil der SortExpression von GridView s SupplierName BoundField auf SupplierNamefestgelegt ist. Der Name des Lieferanten in der Suppliers Tabelle heißt jedoch, CompanyName wir haben den Alias für diesen Spaltennamen als SupplierNameerhalten. 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-Werte 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 des SortExpression-Elements "SupplierName BoundField" 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

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

Nachdem wir diese gespeicherte Prozedur erstellt und neue Methoden in der DAL und BLL erstellt hatten, konnten wir ein GridView-Objekt implementieren, das sowohl Sortierung als auch benutzerdefinierte 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 Hours. Er kann unter mitchell@4GuysFromRolla.comoder über seinen Blog erreicht werden, der unter http://ScottOnWriting.NETzu finden ist.

Besonderer 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 bevorstehenden MSDN-Artikel lesen? Wenn dies der Fall ist, legen Sie eine Zeile unter abmitchell@4GuysFromRolla.com.

ZurückWeiter