Effizientes Durchblättern großer Datenmengen (VB)

von Scott Mitchell

PDF herunterladen

Die Standard-Paging-Option eines Datenpräsentationssteuerelements ist ungeeignet für die Arbeit mit großen Datenmengen, da das zugrunde liegende Steuerelement der Datenquelle alle Datensätze abruft, obwohl nur eine Teilmenge angezeigt wird. Unter solchen Umständen müssen wir uns an das benutzerdefinierte Paging wenden.

Einführung

Wie in unserem vorherigen Tutorial erläutert, kann Paging auf eine von zwei Arten implementiert werden.

• Standardmäßiges Paging kann implementiert werden, indem einfach die Option "Paging aktivieren" im Smarttag des Datenwebsteuerelements überprüft wird. Bei der Anzeige einer Datenseite ruft die ObjectDataSource jedoch alle Datensätze ab, obwohl nur eine Teilmenge auf der Seite angezeigt wird.
• Benutzerdefiniertes Paging verbessert die Leistung der Standard paging , indem nur die Datensätze aus der Datenbank abgerufen werden, die für die bestimmte Seite der vom Benutzer angeforderten Daten angezeigt werden müssen. Benutzerdefinierte Paging erfordert jedoch etwas mehr Aufwand, um dies zu implementieren als die Standard paging.

Aufgrund der einfachen Implementierung aktivieren Sie einfach ein Kontrollkästchen, und Sie sind fertig! Voreingestelltes Paging ist eine attraktive Option. Sein naiver Ansatz, alle Datensätze abzurufen, macht es jedoch zu einer unplausiblen Wahl, wenn man durch ausreichend große Datenmengen blättert oder für Websites mit vielen gleichzeitigen Benutzern. Unter solchen Umständen müssen wir uns an ein benutzerdefiniertes Paging wenden, um ein responsives System bereitzustellen.

Die Herausforderung der benutzerdefinierten Seitenverwaltung besteht darin, eine Abfrage zu schreiben, die den genauen Satz von Datensätzen zurückgibt, die für eine bestimmte Datenseite erforderlich sind. Glücklicherweise stellt Microsoft SQL Server 2005 ein neues Schlüsselwort für Bewertungsergebnisse bereit, mit dem wir eine Abfrage schreiben können, mit der die richtige Teilmenge von Datensätzen effizient abgerufen werden kann. In diesem Lernprogramm erfahren Sie, wie Sie dieses neue SQL Server 2005-Schlüsselwort verwenden, um benutzerdefinierte Paging in einem GridView-Steuerelement zu implementieren. Die Benutzeroberfläche für benutzerdefiniertes Paging ist zwar identisch mit der für das Standard-Paging, aber das Wechseln von einer Seite zur nächsten mit benutzerdefiniertem Paging kann um ein Vielfaches schneller sein als das Standard-Paging.

Schritt 1: Grundlegendes zum benutzerdefinierten Pagingprozess

Beim Ausblättern durch Daten hängen die genauen Datensätze, die auf einer Seite angezeigt werden, von der angeforderten Datenseite und der Anzahl der pro Seite angezeigten Datensätze ab. Stellen Sie sich z. B. vor, dass wir die 81 Produkte durchblättern wollten, wobei 10 Produkte pro Seite angezeigt werden. Beim Anzeigen der ersten Seite würden wir Produkte 1 bis 10 sehen wollen; beim Anzeigen der zweiten Seite würden wir dann Produkte 11 bis 20 betrachten und so weiter.

Es gibt drei Variablen, die bestimmen, welche Datensätze abgerufen werden müssen und wie die Pagingschnittstelle gerendert werden soll:

• Start Row Index, der Index der ersten Zeile auf der Datenseite, die angezeigt werden soll; dieser Index kann berechnet werden, indem man den Seitenindex mit den Datensätzen multipliziert, die pro Seite angezeigt werden sollen, und eins hinzufügt. Wenn Sie beispielsweise die Datensätze 10 gleichzeitig durchlaufen, für die erste Seite (deren Seitenindex 0 ist), lautet der Zeilenindex "Anfang" 0 * 10 + 1 oder 1; für die zweite Seite (deren Seitenindex 1 ist), ist der Zeilenindex "Start" 1 * 10 + 1 oder 11.
• Maximale Zeilenanzahl die maximale Anzahl von Zeilen, die pro Seite angezeigt werden. Diese Variable wird als maximale Zeilen bezeichnet, da für die letzte Seite möglicherweise weniger Datensätze zurückgegeben werden als die Seitengröße. Wenn Sie z. B. die 81 Produkte 10 Datensätze pro Seite durchlaufen, hat die neunte und letzte Seite nur einen Datensatz. Auf keiner Seite werden jedoch mehr Datensätze als der Wert für maximale Zeilen angezeigt.
• Gesamtanzahl der Datensätze, die insgesamt durchgesehen werden. Diese Variable ist zwar nicht erforderlich, um zu bestimmen, welche Datensätze für eine bestimmte Seite abgerufen werden sollen, aber sie diktieren die Pagingschnittstelle. Wenn beispielsweise 81 Produkte durchgeblättert werden, kann die Paginierungsschnittstelle neun Seitenzahlen in der Seiten-UI anzeigen.

Bei der Standardmäßigen Paginierung wird der Startzeilenindex als Produkt des Seitenindex und der Seitengröße plus eins berechnet, während die Höchstanzahl an Zeilen lediglich der Seitengröße entspricht. Da die Standardpaginierung beim Rendern einer beliebigen Datenseite alle Datensätze aus der Datenbank abruft, ist der Index für jede Zeile bekannt, was es zu einer einfachen Aufgabe macht, zum Anfangszeilenindex zu wechseln. Darüber hinaus ist die Gesamtzahl der Datensätze leicht verfügbar, da sie einfach die Anzahl der Datensätze in der DataTable (oder das Objekt, das zum Speichern der Datenbankergebnisse verwendet wird) ist.

Angesichts der Variablen "Startzeilenindex" und "Maximale Zeilen" darf eine benutzerdefinierte Pagingimplementierung nur die genaue Teilmenge von Datensätzen zurückgeben, die beim Startzeilenindex beginnt und bis zu der Anzahl von maximalen Datensätzen danach. Benutzerdefiniertes Paging bietet zwei Herausforderungen:

• Wir müssen jedem Zeilenindex effizient eine Zeile in der gesamten Datenstruktur zuordnen können, die durchgeblättert wird, damit wir am angegebenen Startzeilenindex Datensätze zurückgeben können.
• Wir müssen die Gesamtanzahl der Datensätze angeben, die durchgeblättert werden.

In den nächsten beiden Schritten untersuchen wir das SQL-Skript, das erforderlich ist, um auf diese beiden Herausforderungen zu reagieren. Zusätzlich zum SQL-Skript müssen wir auch Methoden in DAL und BLL implementieren.

Schritt 2: Zurückgeben der Gesamtanzahl der Datensätze, die paginiert werden

Bevor wir untersuchen, wie die genaue Teilmenge von Datensätzen für die angezeigte Seite abgerufen wird, sehen wir uns zunächst an, wie die Gesamtanzahl der Datensätze zurückgegeben wird, die durchsucht werden. Diese Informationen werden benötigt, um die Paging-Benutzeroberfläche ordnungsgemäß zu konfigurieren. Die Gesamtzahl der von einer bestimmten SQL-Abfrage zurückgegebenen Datensätze kann mithilfe der COUNT Aggregatfunktion abgerufen werden. Um beispielsweise die Gesamtanzahl der Datensätze in der Products Tabelle zu ermitteln, können wir die folgende Abfrage verwenden:

SELECT COUNT(*)
FROM Products

Fügen wir unserer DAL eine Methode hinzu, die diese Informationen zurückgibt. Insbesondere erstellen wir eine DAL-Methode namens TotalNumberOfProducts(), die die oben gezeigte SELECT-Anweisung ausführt.

Öffnen Sie zunächst die Northwind.xsd Typed DataSet-Datei im App_Code/DAL Ordner. Klicken Sie als Nächstes mit der rechten Maustaste auf den ProductsTableAdapter Designer, und wählen Sie "Abfrage hinzufügen" aus. Wie wir in früheren Lernprogrammen gesehen haben, können wir dem DAL eine neue Methode hinzufügen, die beim Aufrufen eine bestimmte SQL-Anweisung oder gespeicherte Prozedur ausführt. Wie bei unseren TableAdapter-Methoden in früheren Lernprogrammen, wählen Sie für dieses Tutorial die Verwendung einer Ad-hoc-SQL-Anweisung.

Abbildung 1: Verwenden einer Ad-hoc-SQL-Anweisung

Auf dem nächsten Bildschirm können wir angeben, welche Art von Abfrage erstellt werden soll. Da diese Abfrage einen einzelnen skalaren Wert, nämlich die Gesamtanzahl der Datensätze in der Products-Tabelle, zurückgibt, wählen Sie die Option SELECT, die einen einzelnen Wert zurückgibt.

Abbildung 2: Konfigurieren der Abfrage für die Verwendung einer SELECT-Anweisung, die einen einzelnen Wert zurückgibt

Nachdem der typ der zu verwendenden Abfrage angegeben wurde, müssen wir die Abfrage als nächstes angeben.

Abbildung 3: Verwenden der SELECT COUNT(*) FROM Products-Abfrage

Geben Sie schließlich den Namen für die Methode an. Wie bereits erwähnt, verwenden wir TotalNumberOfProducts.

Abbildung 4: Benennen der DAL-Methode TotalNumberOfProducts

Nachdem Sie auf "Fertig stellen" geklickt haben, fügt der Assistent die TotalNumberOfProducts Methode dem DAL hinzu. Die skalaren Rückgabemethoden im DAL geben Nullable-Typen zurück, falls das Ergebnis aus der SQL-Abfrage NULL ist. Unsere COUNT Abfrage gibt jedoch immer einen Nicht-Wert zurück. Unabhängig davon gibt die DAL-MethodeNULL eine nullable ganze Zahl zurück.

Neben der DAL-Methode benötigen wir auch eine Methode in der BLL. Öffnen Sie die ProductsBLL Klassendatei, und fügen Sie eine TotalNumberOfProducts Methode hinzu, die einfach die DAL-Methode TotalNumberOfProducts aufruft:

Public Function TotalNumberOfProducts() As Integer
    Return Adapter.TotalNumberOfProducts().GetValueOrDefault()
End Function

Die DAL s-Methode TotalNumberOfProducts gibt eine nullable ganze Zahl zurück. Wir haben jedoch die Methode der ProductsBLL Klasse TotalNumberOfProducts erstellt, sodass sie eine standardzahlige Zahl zurückgibt. Daher müssen wir die ProductsBLL-Klassenmethode TotalNumberOfProducts so gestalten, dass sie den Wertteil der von der DAL-Methoden TotalNumberOfProducts zurückgegebenen nullablen ganzen Zahl zurückgibt. Der Aufruf von GetValueOrDefault() gibt den Wert der nullable Ganzzahl zurück, falls vorhanden; ist die nullable Ganzzahl jedoch null, wird der Standardwert für ganze Zahlen, 0, zurückgegeben.

Schritt 3: Zurückgeben der genauen Teilmenge von Datensätzen

Unsere nächste Aufgabe besteht darin, Methoden in dal und BLL zu erstellen, die die zuvor behandelten Variablen "Anfang Zeile Index" und "Maximale Zeilen" akzeptieren und die entsprechenden Datensätze zurückgeben. Bevor wir dies tun, sehen wir uns zuerst das erforderliche SQL-Skript an. Die Herausforderung besteht darin, dass wir in der Lage sein müssen, jeder Zeile in den gesamten Ergebnissen, die durchgeblättert werden, effizient einen Index zuzuweisen, damit wir nur jene Datensätze zurückgeben können, die beim Startzeilenindex beginnen und bis zu der maximalen Anzahl an Datensätzen reichen.

Dies ist keine Herausforderung, wenn bereits eine Spalte in der Datenbanktabelle vorhanden ist, die als Zeilenindex dient. Auf den ersten Blick könnten wir denken, dass die Tabelle ProductsProductID ausreichen würde, da das erste Produkt ein Feld von 1, das zweite ein Feld von 2 und so weiter hat. Das Löschen eines Produkts hinterlässt jedoch eine Lücke in der Sequenz, wobei dieser Ansatz aufgehoben wird.

Es gibt zwei allgemeine Techniken, die verwendet werden, um einen Zeilenindex effizient mit den Daten zu assoziieren, um durch sie die genaue Teilmenge von Datensätzen abzurufen:

• Die in SQL Server 2005 neue ROW_NUMBER()-Schlüsselwort ordnet jedem zurückgegebenen Datensatz basierend auf einer bestimmten Sortierung eine Rangfolge zu. Diese Rangfolge kann als Zeilenindex für jede Zeile verwendet werden.

• Die Verwendung einer Table Variable- und SET ROWCOUNT SQL Server-AnweisungSET ROWCOUNT kann verwendet werden, um anzugeben, wie viele Datensätze eine Abfrage verarbeiten soll, bevor sie beendet werden. Tabellenvariablen sind lokale T-SQL-Variablen, die tabellarische Daten enthalten können, ähnlich wie temporäre Tabellen. Dieser Ansatz eignet sich gleichermaßen gut für Microsoft SQL Server 2005 und SQL Server 2000 (während der ROW_NUMBER() Ansatz nur mit SQL Server 2005 funktioniert).

  Die Idee ist hier, eine Tabellenvariable zu erstellen, die eine IDENTITY-Spalte und Spalten für die Primärschlüssel der Tabelle enthält, deren Daten seitenweise betrachtet werden. Als Nächstes wird der Inhalt der Tabelle, deren Daten durchgegangen wird, in die Tabellenvariable kopiert, wobei ein sequenzieller Zeilenindex (über die IDENTITY Spalte) jedem Datensatz in der Tabelle zugeordnet wird. Nachdem die Tabellenvariable aufgefüllt wurde, kann eine SELECT Anweisung für die Tabellenvariable, die mit der zugrunde liegenden Tabelle verknüpft ist, ausgeführt werden, um die jeweiligen Datensätze abzurufen. Die SET ROWCOUNT Anweisung wird verwendet, um die Anzahl der Datensätze, die in der Tabellenvariablen gedumpt werden müssen, intelligent zu begrenzen.

  Die Effizienz dieses Ansatzes basiert auf der angeforderten Seitenzahl, da dem SET ROWCOUNT Wert der Wert des Startzeilenindex plus der maximalen Zeilen zugewiesen wird. Beim Durchblättern von Seiten mit niedrigen Nummern, wie den ersten Seiten mit Daten, ist dieser Ansatz sehr effizient. Es zeigt jedoch beim Abrufen einer Seite am Ende standardmäßige Paging-ähnliche Leistung an.

Dieses Tutorial implementiert benutzerdefiniertes Paging mithilfe des ROW_NUMBER()-Schlüsselworts. Weitere Informationen zur Verwendung der Tabellenvariablen und SET ROWCOUNT-Technik finden Sie unter A More Efficient Method for Paging Through Large Result Sets.

Das Schlüsselwort ROW_NUMBER() ordnet eine Rangfolge jedem Datensatz zu, der aufgrund einer bestimmten Reihenfolge zurückgegeben werden, mithilfe der folgenden Syntax:

SELECT columnList,
       ROW_NUMBER() OVER(orderByClause)
FROM TableName

ROW_NUMBER() gibt einen numerischen Wert zurück, der den Rang für jeden Datensatz in Bezug auf die angegebene Reihenfolge angibt. Um z. B. die Rangfolge für jedes Produkt anzuzeigen, das von den teuersten bis zum geringsten bestellt wurde, könnten wir die folgende Abfrage verwenden:

SELECT ProductName, UnitPrice,
       ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
FROM Products

Abbildung 5 zeigt die Ergebnisse dieser Abfrage beim Ausführen des Abfragefensters in Visual Studio. Beachten Sie, dass die Produkte nach Preis bestellt werden, zusammen mit einem Preisrang für jede Zeile.

Abbildung 5: Der Preisrang ist für jeden zurückgegebenen Datensatz enthalten

Wenn die Ergebnisse nach der angegebenen ORDER BY Spalte in der OVER Klausel (UnitPriceim obigen Beispiel) 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, aber 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 zu den Leistungsaspekten finden Sie unter Bewertungsfunktionen und Leistung in SQL Server 2005 .

Die von ROW_NUMBER() zurückgegebenen Ranking-Informationen können nicht direkt in der WHERE Klausel verwendet werden. Eine abgeleitete Tabelle kann jedoch verwendet werden, um das ROW_NUMBER() Ergebnis zurückzugeben, das dann in der WHERE Klausel angezeigt werden kann. Die folgende Abfrage verwendet beispielsweise eine abgeleitete Tabelle, um die Spalten "ProductName" und "UnitPrice" zusammen mit dem ROW_NUMBER() Ergebnis zurückzugeben. Anschließend wird eine WHERE Klausel verwendet, um nur die Produkte zurückzugeben, deren Preisrang zwischen 11 und 20 liegt:

SELECT PriceRank, ProductName, UnitPrice
FROM
   (SELECT ProductName, UnitPrice,
       ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
    FROM Products
   ) AS ProductsWithRowNumber
WHERE PriceRank BETWEEN 11 AND 20

Um dieses Konzept noch etwas weiter zu erweitern, können wir diesen Ansatz verwenden, um eine bestimmte Seite mit Daten abzurufen, die den gewünschten Anfangszeilenindex und die maximalen Zeilenwerte aufweisen:

SELECT PriceRank, ProductName, UnitPrice
FROM
   (SELECT ProductName, UnitPrice,
       ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
    FROM Products
   ) AS ProductsWithRowNumber
WHERE PriceRank > <i>StartRowIndex</i> AND
    PriceRank <= (<i>StartRowIndex</i> + <i>MaximumRows</i>)

Nachdem wir nun erläutert haben, wie ROW_NUMBER() eine bestimmte Seite von Daten anhand der Werte "Startzeilenindex" und "Maximale Zeilenanzahl" abgerufen werden kann, sollten wir diese Logik nun als Methoden in DAL und BLL implementieren.

Beim Erstellen dieser Abfrage müssen wir die Reihenfolge bestimmen, nach der die Ergebnisse bewertet werden; sortieren wir die Produkte nach ihrem Namen in alphabetischer Reihenfolge. Dies bedeutet, dass wir mit der Implementierung des benutzerdefinierten Paging in diesem Lernprogramm keinen benutzerdefinierten, seitenbezogenen Bericht erstellen können, der auch sortiert werden kann. Im nächsten Lernprogramm sehen wir jedoch, wie diese Funktionalität bereitgestellt werden kann.

Im vorherigen Abschnitt haben wir die DAL-Methode als Ad-hoc-SQL-Anweisung erstellt. Leider ähnelt der T-SQL-Parser in Visual Studio, der vom TableAdapter-Assistenten verwendet wird, nicht die OVER syntax, die von der ROW_NUMBER() Funktion verwendet wird. Daher müssen wir diese DAL-Methode als gespeicherte Prozedur erstellen. Wählen Sie im Menü "Ansicht" den Server-Explorer aus (oder drücken Sie STRG+ALT+S), und erweitern Sie den NORTHWND.MDF Knoten. Wenn Sie eine neue gespeicherte Prozedur hinzufügen möchten, klicken Sie mit der rechten Maustaste auf den Knoten "Gespeicherte Prozeduren", und wählen Sie "Neue gespeicherte Prozedur hinzufügen" aus (siehe Abbildung 6).

Abbildung 6: Hinzufügen einer neuen gespeicherten Prozedur zum Blättern durch die Produkte.

Diese gespeicherte Prozedur sollte zwei ganzzahlige Eingabeparameter - @startRowIndex und @maximumRows - akzeptieren und die ROW_NUMBER()-Funktion verwenden, die nach dem ProductName-Feld sortiert ist. Es sollen nur diejenigen Zeilen zurückgegeben werden, die größer als das angegebene @startRowIndex und kleiner oder gleich @startRowIndex + @maximumRow sind. Geben Sie das folgende Skript in die neue gespeicherte Prozedur ein, und klicken Sie dann auf das Symbol "Speichern", um die gespeicherte Prozedur der Datenbank hinzuzufügen.

CREATE PROCEDURE dbo.GetProductsPaged
(
    @startRowIndex int,
    @maximumRows int
)
AS
    SELECT     ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
               UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
               CategoryName, SupplierName
FROM
   (
       SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
              UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
              (SELECT CategoryName
               FROM Categories
               WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName,
              (SELECT CompanyName
               FROM Suppliers
               WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName,
              ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
        FROM Products
    ) AS ProductsWithRowNumbers
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

Nehmen Sie sich nach dem Erstellen der gespeicherten Prozedur einen Moment Zeit, um sie zu testen. Klicken Sie im Server-Explorer mit der rechten Maustaste auf den Namen der GetProductsPaged gespeicherten Prozedur, und wählen Sie die Option "Ausführen" aus. Visual Studio fordert Sie dann zur Eingabeparameter @startRowIndex und @maximumRow s auf (siehe Abbildung 7). Probieren Sie unterschiedliche Werte aus, und überprüfen Sie die Ergebnisse.

Geben Sie einen Wert für die Parameter @startRowIndex und ein." />

Abbildung 7: Geben Sie einen Wert für die @startRowIndex- und die @maximumRows-Parameter ein.

Nachdem Sie diese Eingabeparameterwerte ausgewählt haben, zeigt das Ausgabefenster die Ergebnisse an. Abbildung 8 zeigt die Ergebnisse beim Übergeben von 10 sowohl für den Parameter @startRowIndex als auch für den Parameter @maximumRows.

Abbildung 8: Die Datensätze, die auf der zweiten Seite der Daten angezeigt werden, werden zurückgegeben (Klicken Sie, um das Bild in voller Größe anzuzeigen)

Nachdem diese gespeicherte Prozedur erstellt wurde, können wir die ProductsTableAdapter Methode erstellen. Öffnen Sie das Northwind.xsd typierte DataSet, klicken Sie mit der rechten Maustaste in das ProductsTableAdapterFeld, und wählen Sie die Option "Abfrage hinzufügen" aus. Statt die Abfrage mithilfe einer Ad-hoc-SQL-Anweisung zu erstellen, erstellen Sie sie mithilfe einer vorhandenen gespeicherten Prozedur.

Abbildung 9: Erstellen der DAL-Methode mithilfe einer vorhandenen gespeicherten Prozedur

Als Nächstes werden wir aufgefordert, die gespeicherte Prozedur auszuwählen, die aufgerufen werden soll. Wählen Sie die GetProductsPaged gespeicherte Prozedur aus der Dropdown-Liste aus.

Abbildung 10: Auswählen der gespeicherten GetProductsPaged-Prozedur aus der Dropdownliste

Im nächsten Bildschirm werden Sie gefragt, welche Art von Daten von der gespeicherten Prozedur zurückgegeben wird: tabellarische Daten, ein einzelner Wert oder kein Wert. Da die GetProductsPaged gespeicherte Prozedur mehrere Datensätze zurückgeben kann, geben Sie an, dass sie tabellarische Daten zurückgibt.

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

Geben Sie schließlich die Namen der Methoden an, die Sie erstellt haben möchten. Wie in unseren vorherigen Anleitungen, erstellen Sie Methoden sowohl mit der Methode "Fill a DataTable" als auch mit "Return a DataTable". Benennen Sie die erste Methode FillPaged und die zweite GetProductsPaged.

Abbildung 12: Benennen der Methoden FillPaged und GetProductsPaged

Zusätzlich zur Erstellung einer DAL-Methode zur Rückgabe einer bestimmten Seite von Produkten müssen wir auch solche Funktionen in der BLL bereitstellen. Wie bei der DAL-Methode muss die BLL-Methode GetProductsPaged zwei ganzzahlige Eingaben für die Angabe des Anfangszeilenindex und der maximalen Zeilen akzeptieren und nur die Datensätze zurückgeben, die innerhalb des angegebenen Bereichs liegen. Erstellen Sie eine solche BLL-Methode in der ProductsBLL-Klasse, die lediglich die DAL s GetProductsPaged-Methode aufruft, z. B.:

<System.ComponentModel.DataObjectMethodAttribute( _
    System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsPaged(startRowIndex As Integer, maximumRows As Integer) _
    As Northwind.ProductsDataTable
    Return Adapter.GetProductsPaged(startRowIndex, maximumRows)
End Function

Sie können jeden beliebigen Namen für die Eingabeparameter der BLL-Methode verwenden, aber wie wir in Kürze sehen werden, sparen uns die Wahl von startRowIndex und maximumRows ein wenig zusätzliche Arbeit, wenn wir eine ObjectDataSource so konfigurieren, dass sie diese Methode nutzt.

Schritt 4: Konfigurieren der ObjectDataSource für die Verwendung von benutzerdefiniertem Paging

Mit den BLL- und DAL-Methoden für den Zugriff auf eine bestimmte Teilmenge von Datensätzen gehen wir dazu über, ein GridView-Steuerelement zu erstellen, das mithilfe von benutzerdefiniertem Paging durch die zugrunde liegenden Datensätze blättert. Öffnen Sie zunächst die EfficientPaging.aspx Seite im PagingAndSorting Ordner, fügen Sie der Seite eine GridView hinzu, und konfigurieren Sie sie für die Verwendung eines neuen ObjectDataSource-Steuerelements. In unseren früheren Lernprogrammen hatten wir häufig die ObjectDataSource so konfiguriert, dass sie die Methode GetProducts der Klasse ProductsBLL verwendet. Dieses Mal möchten wir jedoch stattdessen die GetProductsPaged Methode verwenden, da die GetProducts Methode alle Produkte in der Datenbank zurückgibt, während GetProductsPaged nur eine bestimmte Teilmenge von Datensätzen zurückgegeben wird.

Abbildung 13: Konfigurieren der ObjectDataSource für die Verwendung der GetProductsPaged-Methode der ProductsBLL-Klasse

Da wir ein schreibgeschütztes GridView-Objekt erstellen, nehmen Sie sich einen Moment Zeit, um die Dropdownliste der Methode in den Registerkarten INSERT, UPDATE und DELETE auf (Keine) festzulegen.

Als Nächstes fordert der ObjectDataSource-Assistent uns auf, die Quellen für die Werte der GetProductsPaged-Methoden-startRowIndex- und maximumRows-Eingabeparameter anzugeben. Diese Eingabeparameter werden tatsächlich automatisch von gridView festgelegt, lassen Sie die Quelle also einfach auf "Keine" festgelegt, und klicken Sie auf "Fertig stellen".

Abbildung 14: Belassen der Eingabeparameterquellen als "Keine"

Nach Abschluss des ObjectDataSource-Assistenten enthält die GridView ein BoundField- oder CheckBoxField-Element für jedes der Produktdatenfelder. Sie können die Darstellung von GridView nach Ihren Wünschen anpassen. Ich habe mich entschieden, nur die ProductName, , CategoryName, SupplierName, QuantityPerUnitund UnitPrice BoundFields anzuzeigen. Konfigurieren Sie außerdem die GridView so, dass paging unterstützt wird, indem Sie das Kontrollkästchen "Paging aktivieren" in ihrem Smarttag aktivieren. Nach diesen Änderungen sollte das deklarative Markup "GridView" und "ObjectDataSource" wie folgt aussehen:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="ProductID" DataSourceID="ObjectDataSource1" AllowPaging="True">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="Product"
            SortExpression="ProductName" />
        <asp:BoundField DataField="CategoryName" HeaderText="Category"
            ReadOnly="True" SortExpression="CategoryName" />
        <asp:BoundField DataField="SupplierName" HeaderText="Supplier"
            SortExpression="SupplierName" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="Qty/Unit"
            SortExpression="QuantityPerUnit" />
        <asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}"
            HeaderText="Price" HtmlEncode="False" SortExpression="UnitPrice" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    OldValuesParameterFormatString="original_{0}" SelectMethod="GetProductsPaged"
    TypeName="ProductsBLL">
    <SelectParameters>
        <asp:Parameter Name="startRowIndex" Type="Int32" />
        <asp:Parameter Name="maximumRows" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>

Wenn Sie die Seite jedoch über einen Browser besuchen, ist GridView nirgendwo zu finden.

Abbildung 15: Die GridView wird nicht angezeigt.

Die GridView fehlt, da die ObjectDataSource derzeit 0 als Werte für die GetProductsPagedstartRowIndex Parameter und maximumRows Eingabeparameter verwendet. Daher gibt die resultierende SQL-Abfrage keine Datensätze zurück und daher wird die GridView nicht angezeigt.

Um dies zu beheben, müssen wir die ObjectDataSource so konfigurieren, dass benutzerdefinierte Paging verwendet wird. Dies kann in den folgenden Schritten durchgeführt werden:

1. Legen Sie die ObjectDataSource-Eigenschaft EnablePaging so true fest, dass sie an die ObjectDataSource übergeben werden muss, um die SelectMethod beiden zusätzlichen Parameter anzugeben: einen, um den Startzeilenindex (StartRowIndexParameterName) anzugeben, und eine, um die maximale Zeile (MaximumRowsParameterName) anzugeben.
2. Legen Sie die StartRowIndexParameterName- und MaximumRowsParameterName-Eigenschaften der ObjectDataSource entsprechend fest, und die StartRowIndexParameterName- und MaximumRowsParameterName-Eigenschaften geben die Namen der Eingabeparameter an, die an SelectMethod für benutzerdefiniertes Paging übergeben werden. Standardmäßig sind startIndexRow diese Parameternamen und maximumRows, weshalb ich beim Erstellen der GetProductsPaged Methode in der BLL diese Werte für die Eingabeparameter verwendet habe. Wenn Sie für die BLL-Methode andere Parameternamen wie GetProductsPaged, startIndex oder maxRows verwenden möchten, müssen Sie die Eigenschaften StartRowIndexParameterName und MaximumRowsParameterName der ObjectDataSource entsprechend festlegen (zum Beispiel StartRowIndexParameterName für startIndex und MaximumRowsParameterName für maxRows).
3. Legen Sie die Eigenschaft von ObjectDataSource SelectCountMethod auf den Namen der Methode fest, die die Gesamtanzahl der Datensätze zurückgibt, die durchlaufen werden (TotalNumberOfProducts). Erinnern Sie sich daran, dass die Methode der ProductsBLL-Klasse TotalNumberOfProducts die Gesamtanzahl der Datensätze zurückgibt, die mithilfe einer DAL-Methode, die eine SELECT COUNT(*) FROM Products-Abfrage ausführt, durchlaufen werden. Diese Informationen werden von ObjectDataSource benötigt, um die Paging-Schnittstelle korrekt zu rendern.
4. Entfernen Sie die startRowIndex- und maximumRows<asp:Parameter>-Elemente aus dem deklarativen Markup der ObjectDataSource. Beim Konfigurieren der ObjectDataSource über den Assistenten hat Visual Studio automatisch zwei <asp:Parameter>-Elemente für die Eingabeparameter der GetProductsPaged-Methode hinzugefügt. Durch das Setzen von EnablePaging auf true werden die Parameter automatisch übergeben. Wenn sie ebenfalls in der deklarativen Syntax auftauchen, versucht der ObjectDataSource, vier Parameter an die GetProductsPaged-Methode und zwei Parameter an die TotalNumberOfProducts-Methode zu übergeben. Wenn Sie vergessen, diese <asp:Parameter> Elemente zu entfernen, erhalten Sie beim Aufrufen der Seite über einen Browser eine Fehlermeldung wie: ObjectDataSource 'ObjectDataSource1' konnte keine nicht generische Methode 'TotalNumberOfProducts' mit Parametern finden: startRowIndex, maximumRowRows.

Nachdem Sie diese Änderungen vorgenommen haben, sollte die deklarative Syntax von ObjectDataSource wie folgt aussehen:

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

Beachten Sie, dass die EnablePaging und SelectCountMethod Eigenschaften festgelegt wurden und die <asp:Parameter> Elemente entfernt wurden. Abbildung 16 zeigt einen Screenshot der Eigenschaftenfenster, nachdem diese Änderungen vorgenommen wurden.

Abbildung 16: Zum Verwenden von benutzerdefiniertem Paging das ObjectDataSource-Steuerelement konfigurieren

Nachdem Sie diese Änderungen vorgenommen haben, besuchen Sie diese Seite über einen Browser. Sie sollten 10 Produkte sehen, die aufgelistet und alphabetisch sortiert sind. Nehmen Sie sich einen Moment Zeit, um die Daten einzeln zu durchlaufen. Obwohl es aus Sicht des Endbenutzers keinen visuellen Unterschied zwischen Standard-Paging und benutzerdefiniertem Paging gibt, arbeitet benutzerdefiniertes Paging effizienter mit großen Datenmengen, da nur die Datensätze abgerufen werden, die für eine bestimmte Seite angezeigt werden müssen.

Abbildung 17: Die Daten, sortiert nach dem Namen des Produkts, werden mit benutzerdefiniertem Paging seitengesteuert (Klicken Sie, um das Bild mit voller Größe anzuzeigen)

Durch Klicken auf den Link zur letzten Seite führt zu einem Postback und weist die GridView an, ihre PageIndex-Eigenschaft zu aktualisieren. Wenn auf den letzten Seitenlink geklickt wird, weist das GridView seine PageIndex Eigenschaft einem Wert zu, der kleiner als seine PageCount Eigenschaft ist. Wenn der Ansichtszustand deaktiviert ist, geht der PageCount Wert über Postbacks hinweg verloren, und dem PageIndex Wert wird stattdessen der maximale ganzzahlige Wert zugewiesen. Als Nächstes versucht gridView, den Anfangszeilenindex durch Multiplizieren der PageSizePageCount Eigenschaften zu bestimmen. Dies führt zu einem OverflowException, da das Produkt die maximal zulässige Ganzzahlengröße überschreitet.

Implementieren von benutzerdefiniertem Paging und Sortieren

Unsere aktuelle Implementierung für benutzerdefinierten Seitenumbruch erfordert, dass wir die Reihenfolge, in der die Daten statisch durchlaufen werden, beim Erstellen der gespeicherten Prozedur GetProductsPaged angeben. Möglicherweise haben Sie jedoch bemerkt, dass das Smarttag des GridView zusätzlich zur Option "Paging aktivieren" ein Kontrollkästchen "Sortierung aktivieren" enthält. Leider werden beim Hinzufügen der Sortierunterstützung zu GridView mit unserer aktuellen benutzerdefinierten Pagingimplementierung nur die Datensätze auf der aktuell angezeigten Datenseite sortiert. Wenn Sie beispielsweise GridView so konfigurieren, dass Paging unterstützt wird, und dann, beim Anzeigen der ersten Datenseite, nach Produktnamen in absteigender Reihenfolge sortieren, wird die Reihenfolge der Produkte auf Seite 1 umgekehrt. Wie Abbildung 18 zeigt, wird Carnarvon Tigers als erstes Produkt bei der Sortierung in umgekehrter alphabetischer Reihenfolge angezeigt, wobei die 71 anderen Produkte, die alphabetisch nach Carnarvon Tigers kommen, ignoriert werden; nur die Datensätze auf der ersten Seite werden bei der Sortierung berücksichtigt.

Abbildung 18: Nur die auf der aktuellen Seite angezeigten Daten sind sortiert (Zum Anzeigen des Bilds mit voller Größe klicken)

Die Sortierung gilt nur für die aktuelle Datenseite, da die Sortierung erfolgt, nachdem die Daten aus der BLL-Methode GetProductsPaged abgerufen wurden, und diese Methode gibt nur diese Datensätze für die jeweilige Seite zurück. Um die Sortierung korrekt zu implementieren, müssen wir den Sortierausdruck an die GetProductsPaged Methode übergeben, damit die Daten entsprechend bewertet werden können, bevor die spezifische Datenseite zurückgegeben wird. In unserem nächsten Lernprogramm erfahren Sie, wie Sie dies erreichen können.

Implementieren von benutzerdefiniertem Paging und Löschen

Wenn Sie die Löschfunktionalität in einer GridView aktivieren, deren Daten mit benutzerdefinierten Paging-Techniken paginiert werden, werden Sie feststellen, dass beim Löschen des letzten Datensatzes von der letzten Seite die GridView verschwindet, anstatt die GridView ordnungsgemäß zu verringern. Um diesen Fehler zu reproduzieren, aktivieren Sie das Löschen für das soeben erstellte Lernprogramm. Wechseln Sie zur letzten Seite (Seite 9), auf der Sie ein einzelnes Produkt sehen sollten, da wir durch 81 Produkte blättern, 10 Produkte pro Seite. Dieses Produkt löschen.

Beim Löschen des letzten Produkts sollte gridView automatisch zur achten Seite wechseln, und diese Funktionalität wird mit standardseitigem Paging angezeigt. Bei der benutzerdefinierten Seitennummerierung wird das GridView-Steuerelement nach dem Löschen des letzten Produkts auf der letzten Seite jedoch einfach vollständig vom Bildschirm entfernt. Der genaue Grund , warum dies geschieht, liegt etwas außerhalb des Umfangs dieses Lernprogramms; siehe Löschen des letzten Datensatzes auf der letzten Seite aus einer GridView mit benutzerdefiniertem Paging für die Details auf niedriger Ebene in Bezug auf die Quelle dieses Problems. Zusammenfassend ist dies auf die folgende Abfolge der Schritte zurückzuführen, die von gridView ausgeführt werden, wenn auf die Schaltfläche "Löschen" geklickt wird:

1. Datensatz löschen
2. Abrufen der passenden Datensätze zur Anzeige für die angegebenen PageIndex und PageSize
3. Überprüfen Sie, ob die PageIndex die Anzahl der Datenseiten in der Datenquelle nicht übersteigt; falls dies der Fall ist, wird die Eigenschaft PageIndex von GridView automatisch verringert.
4. Binden Sie die entsprechende Datenseite mithilfe der in Schritt 2 abgerufenen Datensätze an die GridView.

Das Problem ergibt sich aus der Tatsache, dass in Schritt 2, wenn die anzuzeigenden Datensätze mit PageIndex abgerufen werden, immer noch das PageIndex der letzten Seite verwendet wird, deren einziger Datensatz gerade gelöscht wurde. Daher werden in Schritt 2 keine Datensätze zurückgegeben, da diese letzte Datenseite keine Datensätze mehr enthält. In Schritt 3 erkennt das GridView, dass seine PageIndex Eigenschaft größer als die Gesamtanzahl der Seiten in der Datenquelle ist (da wir den letzten Datensatz auf der letzten Seite gelöscht haben) und verringert daher seine PageIndex Eigenschaft. In Schritt 4 versucht die GridView, sich an die in Schritt 2 abgerufenen Daten zu binden; In Schritt 2 wurden jedoch keine Datensätze zurückgegeben, was zu einer leeren GridView führt. Bei der standardmäßigen Paginierung tritt dieses Problem nicht auf, da in Schritt 2 alle Datensätze aus der Datenquelle abgerufen werden.

Um dies zu beheben, haben wir zwei Optionen. Die erste besteht darin, einen Ereignishandler für den GridView-Ereignishandler RowDeleted zu erstellen, der bestimmt, wie viele Datensätze auf der Seite angezeigt wurden, die gerade gelöscht wurde. Wenn nur ein Datensatz vorhanden war, muss der soeben gelöschte Datensatz der letzte sein, und wir müssen die GridView s PageIndexverringern. Natürlich möchten wir das PageIndex nur dann aktualisieren, wenn der Löschvorgang tatsächlich erfolgreich war, was dadurch bestimmt werden kann, dass die e.Exception-Eigenschaft null ist.

Dieser Ansatz funktioniert, da er nach Schritt 1, aber vor Schritt 2 PageIndex aktualisiert wird. Daher wird in Schritt 2 der entsprechende Datensatz zurückgegeben. Verwenden Sie dazu einen Code wie den folgenden:

Protected Sub GridView1_RowDeleted(sender As Object, e As GridViewDeletedEventArgs) _
    Handles GridView1.RowDeleted
    ' If we just deleted the last row in the GridView, decrement the PageIndex
    If e.Exception Is Nothing AndAlso GridView1.Rows.Count = 1 Then
        ' we just deleted the last row
        GridView1.PageIndex = Math.Max(0, GridView1.PageIndex - 1)
    End If
End Sub

Eine alternative Problemumgehung besteht darin, einen Ereignishandler für das ObjectDataSource-Ereignis RowDeleted zu erstellen und die AffectedRows Eigenschaft auf einen Wert von 1 festzulegen. Nach dem Löschen des Datensatzes in Schritt 1 (vor dem erneuten Abrufen der Daten in Schritt 2) aktualisiert das GridView seine PageIndex Eigenschaft, wenn eine oder mehrere Zeilen vom Vorgang betroffen waren. Die AffectedRows Eigenschaft wird jedoch nicht von ObjectDataSource festgelegt, daher wird dieser Schritt weggelassen. Eine Möglichkeit, diesen Schritt auszuführen, besteht darin, die AffectedRows Eigenschaft manuell festzulegen, wenn der Löschvorgang erfolgreich abgeschlossen ist. Dies kann mithilfe von Code wie den folgenden erfolgen:

Protected Sub ObjectDataSource1_Deleted( _
    sender As Object, e As ObjectDataSourceStatusEventArgs) _
    Handles ObjectDataSource1.Deleted
    ' If we get back a Boolean value from the DeleteProduct method and it's true, then
    ' we successfully deleted the product. Set AffectedRows to 1
    If TypeOf e.ReturnValue Is Boolean AndAlso CType(e.ReturnValue, Boolean) = True Then
        e.AffectedRows = 1
    End If
End Sub

Der Code für beide dieser Ereignishandler finden Sie in der CodeBehind-Klasse des EfficientPaging.aspx Beispiels.

Vergleich der Leistung von Standard-Paging und benutzerdefiniertem Paging

Da das benutzerdefinierte Paging nur die erforderlichen Datensätze abruft, während das Standard-Paging alle Datensätze für jede angezeigte Seite zurückgibt, ist klar, dass das benutzerdefinierte Paging effizienter ist als das Standard-Paging. Aber wie viel effizienter ist das benutzerdefinierte Paging? Welche Leistungssteigerungen können Sie feststellen, indem Sie vom Standard-Paging zum benutzerdefinierten Paging wechseln?

Leider gibt es hier keine Universallösung. Der Leistungsgewinn hängt von einer Reihe von Faktoren ab, wobei die wichtigsten zwei die Anzahl der Datensätze sind, durch die geblättert wird, sowie die Last, die auf dem Datenbankserver und den Kommunikationskanälen zwischen dem Webserver und dem Datenbankserver liegt. Bei kleinen Tabellen mit nur wenigen Dutzend Datensätzen kann der Leistungsunterschied vernachlässigbar sein. Bei großen Tabellen mit Tausenden bis Hunderten von Zeilen ist der Leistungsunterschied jedoch akut.

Ein Artikel mit "Benutzerdefiniertes Paging in ASP.NET 2.0 mit SQL Server 2005" enthält einige Leistungstests, die ich ausgeführt habe, um die Unterschiede bei der Leistung zwischen diesen beiden Pagingtechniken beim Paging durch eine Datenbanktabelle mit 50.000 Datensätzen zu zeigen. In diesen Tests habe ich sowohl die Zeit zum Ausführen der Abfrage auf SQL Server-Ebene (mit SQL Profiler) als auch auf der ASP.NET Seite mit ASP.NET Ablaufverfolgungsfeatures untersucht. Denken Sie daran, dass diese Tests in meinem Entwicklungsfeld mit einem einzelnen aktiven Benutzer ausgeführt wurden und daher nicht wissenschaftlich sind und keine typischen Websitelademuster nachahmen. Unabhängig davon veranschaulichen die Ergebnisse die relativen Unterschiede bei der Ausführungszeit für standard- und benutzerdefinierte Paging bei der Arbeit mit ausreichend großen Datenmengen.

Wie Sie sehen können, erfordert das Abrufen einer bestimmten Seite mit Daten 354 weniger Lesevorgänge im Durchschnitt und abgeschlossen in einem Bruchteil der Zeit. Auf der ASP.NET-Seite konnte die angepasste Seite in nahezu 1/100^tel der Zeit gerendert werden, die beim Verwenden des Standard-Pagings benötigt wurde.

Zusammenfassung

Standardmäßiges Paging ist kinderleicht zu implementieren: Einfach nur das Kontrollkästchen "Paging aktivieren" im Smarttag des Datenwebsteuerelements aktivieren. Allerdings geht diese Einfachheit mit Leistungsbeeinträchtigungen einher. Bei standardmäßigem Paging, wenn ein Benutzer eine Seite von Daten anfordert, werden alle Datensätze zurückgegeben, auch wenn nur ein kleiner Bruchteil davon angezeigt wird. Um diesen Leistungsaufwand zu verringern, bietet die ObjectDataSource eine alternative Option des benutzerdefinierten Pagings an.

Während das benutzerdefinierte Paging Leistungsprobleme des Standard-Pagings verbessert, indem nur die Datensätze abgerufen werden, die angezeigt werden müssen, ist die Implementierung des benutzerdefinierten Pagings jedoch aufwendiger. Zunächst muss eine Abfrage geschrieben werden, die ordnungsgemäß (und effizient) auf die bestimmte Teilmenge der angeforderten Datensätze zugreift. Dies kann auf verschiedene Arten erreicht werden; Die in diesem Lernprogramm untersuchte Funktion besteht darin, sql Server 2005 s neue ROW_NUMBER() Funktion zum Rangieren von Ergebnissen zu verwenden und dann nur die Ergebnisse zurückzugeben, deren Rangfolge innerhalb eines angegebenen Bereichs liegt. Darüber hinaus müssen wir ein Mittel hinzufügen, um die Gesamtanzahl der Datensätze zu bestimmen, die durchlaufen werden. Nach dem Erstellen dieser DAL- und BLL-Methoden müssen wir auch die ObjectDataSource so konfigurieren, dass sie bestimmen kann, wie viele Datensätze durchgeblattt werden, und die Werte für den Startzeilenindex und die maximalen Zeilenwerte ordnungsgemäß an die BLL übergeben können.

Die Implementierung von benutzerdefiniertem Seitenumbruch erfordert zwar eine Reihe von Schritten und ist nicht so einfach wie der Standard-Seitenumbruch, aber benutzerdefinierter Seitenumbruch ist eine Notwendigkeit, wenn man durch ausreichend große Datenmengen blättert. Wie die untersuchten Ergebnisse gezeigt haben, kann benutzerdefiniertes Paging Sekunden von der Renderzeit der ASP.NET-Seiten einsparen und die Last auf dem Datenbankserver um eine oder mehrere Größenordnungen reduzieren.

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.