Aangepaste gepaginade gegevens sorteren (C#)

door Scott Mitchell

PDF downloaden

In de vorige zelfstudie hebben we geleerd hoe u aangepaste paging implementeert bij het presenteren van gegevens op een webpagina. In deze zelfstudie ziet u hoe u het voorgaande voorbeeld kunt uitbreiden met ondersteuning voor het sorteren van aangepaste paging.

Introductie

In vergelijking met standaard paging kan aangepaste paging de prestaties van het doorbladeren van gegevens verbeteren met verschillende ordes van grootte, waardoor aangepaste paging de standaardkeuze wordt voor het doorlopen van grote hoeveelheden gegevens. Het implementeren van aangepaste paginering is complexer dan het implementeren van standaard paginering, vooral bij het toevoegen van sortering aan het geheel. In deze zelfstudie breiden we het voorbeeld uit van de vorige met ondersteuning voor sorteren en aangepaste paging.

Opmerking

Aangezien deze zelfstudie voortbouwt op de vorige zelfstudie, neem even de tijd om voordat u begint de declaratieve syntaxis in het <asp:Content> element van de vorige webpagina van de zelfstudie (EfficientPaging.aspx) te kopiëren en deze te plakken tussen het <asp:Content> element in de SortParameter.aspx pagina. Raadpleeg stap 1 van de zelfstudie Validatiebesturingselementen toevoegen aan de zelfstudie Interfaces bewerken en invoegen voor een gedetailleerdere bespreking van het repliceren van de functionaliteit van een ASP.NET pagina naar een andere.

Stap 1: De techniek voor aangepaste paginering opnieuw gebruiken

Voor een goede werking van aangepaste paginering moeten we een techniek toepassen die een bepaalde subset van records efficiënt kan ophalen, gebaseerd op de parameters Start Row Index en Maximum Rows. Er zijn een aantal technieken die kunnen worden gebruikt om dit doel te bereiken. In de voorgaande zelfstudie hebben we gekeken naar het bereiken hiervan met behulp van de nieuwe ROW_NUMBER() classificatiefunctie van Microsoft SQL Server 2005. Kortom, de ROW_NUMBER() classificatiefunctie wijst een rijnummer toe aan elke rij die wordt geretourneerd door een query die wordt gerangschikt op een opgegeven sorteervolgorde. De juiste subset van records wordt vervolgens verkregen door een bepaalde sectie van de genummerde resultaten te retourneren. De volgende query laat zien hoe u deze techniek gebruikt om deze producten te retourneren die zijn genummerd 11 tot en met 20 bij het rangschikken van de resultaten alfabetisch gesorteerd op de 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

Deze techniek werkt goed voor paging met behulp van een specifieke sorteervolgorde (ProductName alfabetisch gesorteerd, in dit geval), maar de query moet worden gewijzigd om de resultaten weer te geven die zijn gesorteerd op een andere sorteerexpressie. In het ideale voorbeeld kan de bovenstaande query opnieuw worden geschreven om een parameter in de OVER component te gebruiken, zoals:

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

Helaas zijn geparameteriseerde ORDER BY componenten niet toegestaan. In plaats daarvan moeten we een opgeslagen procedure maken die een @sortExpression invoerparameter accepteert, maar een van de volgende tijdelijke oplossingen gebruikt:

• Schrijf in code vastgelegde query's voor elk van de sorteerexpressies die kunnen worden gebruikt; gebruik IF/ELSE vervolgens T-SQL-instructies om te bepalen welke query moet worden uitgevoerd.
• Gebruik een CASE instructie om dynamische ORDER BY expressies op te geven op basis van de @sortExpressio n invoerparameter. Zie de sectie Wordt gebruikt om queryresultaten dynamisch te sorteren in T-SQL-instructies CASE voor meer informatie.
• Maak de juiste query als een tekenreeks in de opgeslagen procedure en gebruik vervolgens de door het sp_executesql systeem opgeslagen procedure om de dynamische query uit te voeren.

Elk van deze tijdelijke oplossingen heeft enkele nadelen. De eerste optie is niet zo onderhoudbaar als de andere twee, omdat hiervoor een query moet worden gemaakt voor elke mogelijke sorteerexpressie. Dus als u later besluit nieuwe, sorteerbare velden toe te voegen aan de GridView, moet u ook teruggaan en de opgeslagen procedure bijwerken. De tweede benadering heeft enkele subtiliteiten die prestatieproblemen veroorzaken bij het sorteren op niet-tekenreeksdatabasekolommen en ook last heeft van dezelfde onderhoudbaarheidsproblemen als de eerste. En de derde keuze, die gebruikmaakt van dynamische SQL, introduceert het risico voor een SQL-injectieaanval als een aanvaller de opgeslagen procedure kan uitvoeren die de invoerparameterwaarden van hun keuze doorgeeft.

Hoewel geen van deze benaderingen perfect is, denk ik dat de derde optie het beste van de drie is. Dankzij het gebruik van dynamische SQL biedt het een flexibiliteitsniveau dat de andere twee niet hebben. Bovendien kan een SQL-injectieaanval alleen worden misbruikt als een aanvaller de opgeslagen procedure kan uitvoeren die de invoerparameters van zijn keuze doorgeeft. Omdat de DAL gebruikmaakt van geparameteriseerde query's, beveiligt ADO.NET deze parameters die via de architectuur naar de database worden verzonden, wat betekent dat het beveiligingsprobleem met SQL-injectie alleen bestaat als de aanvaller de opgeslagen procedure rechtstreeks kan uitvoeren.

Als u deze functionaliteit wilt implementeren, maakt u een nieuwe opgeslagen procedure in de Northwind-database met de naam GetProductsPagedAndSorted. Deze opgeslagen procedure moet drie invoerparameters accepteren: @sortExpression, een invoerparameter van het type nvarchar(100) die aangeeft hoe de resultaten moeten worden gesorteerd en direct na de tekst in de ORDER BYOVER component worden geïnjecteerd; en @startRowIndex@maximumRows, dezelfde twee gehele invoerparameters uit de GetProductsPaged opgeslagen procedure die in de vorige zelfstudie zijn onderzocht. Maak de GetProductsPagedAndSorted stored procedure met behulp van het volgende script:

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

De opgeslagen procedure begint door ervoor te zorgen dat er een waarde voor de @sortExpression parameter is opgegeven. Als deze ontbreekt, worden de resultaten gerangschikt op ProductID. Vervolgens wordt de dynamische SQL-query gemaakt. Houd er rekening mee dat de dynamische SQL-query hier enigszins verschilt van de vorige query's die worden gebruikt om alle rijen op te halen uit de tabel Producten. In eerdere voorbeelden hebben we elke productcategorie en leveranciersnamen verkregen met behulp van een subquery. Deze beslissing is gemaakt in de zelfstudie Een Gegevenstoegangslaag maken en is uitgevoerd in plaats van het gebruik JOIN van s omdat de TableAdapter niet automatisch de bijbehorende methoden voor invoegen, bijwerken en verwijderen voor dergelijke query's kan maken. De opgeslagen procedure GetProductsPagedAndSorted moet echter JOIN gebruiken om de resultaten te ordenen op basis van de categorie- of leveranciersnamen.

Deze dynamische query is opgebouwd door de statische querygedeelten en de @sortExpression, @startRowIndexen @maximumRows parameters samen te stellen. Omdat @startRowIndex en @maximumRows geheel getalparameters zijn, moeten ze worden geconverteerd naar nvarchars om correct te worden samengevoegd. Zodra deze dynamische SQL-query is gemaakt, wordt deze uitgevoerd via sp_executesql.

Neem even de tijd om deze opgeslagen procedure met verschillende waarden voor de @sortExpression, @startRowIndexen @maximumRows parameters te testen. Klik in Server Explorer met de rechtermuisknop op de naam van de opgeslagen procedure en kies Uitvoeren. Hiermee wordt het dialoogvenster Opgeslagen procedure uitvoeren weergegeven waarin u de invoerparameters kunt invoeren (zie afbeelding 1). Als u de resultaten wilt sorteren op de categorienaam, gebruikt u CategoryName voor de @sortExpression parameterwaarde. Als u wilt sorteren op de bedrijfsnaam van de leverancier, gebruikt u CompanyName. Nadat u de parameterwaarden hebt opgegeven, klikt u op OK. De resultaten worden weergegeven in het venster Uitvoer. Afbeelding 2 toont de resultaten bij het retourneren van producten gerangschikt op 11 tot en met 20 bij het UnitPrice bestellen op aflopende volgorde.

Afbeelding 1: Probeer verschillende waarden voor de drie invoerparameters van de opgeslagen procedure

Afbeelding 2: De resultaten van de opgeslagen procedure worden weergegeven in het uitvoervenster (klik om de afbeelding op volledige grootte weer te geven)

Opmerking

Wanneer u de resultaten rangschikt op basis van de opgegeven ORDER BY kolom in de OVER component, moet SQL Server de resultaten sorteren. Dit is een snelle bewerking als er een geclusterde index is op de kolom(en) waarop de resultaten worden gerangschikt of als er een dekkingsindex is, maar anders duurder kan zijn. Als u de prestaties voor voldoende grote query's wilt verbeteren, kunt u overwegen om een niet-geclusterde index toe te voegen voor de kolom waarop de resultaten zijn gerangschikt. Raadpleeg classificatiefuncties en prestaties in SQL Server 2005 voor meer informatie.

Stap 2: de gegevenstoegangs- en bedrijfslogicalagen uitbreiden

Nu de GetProductsPagedAndSorted opgeslagen procedure is gemaakt, is de volgende stap het bieden van een manier om die opgeslagen procedure uit te voeren via onze toepassingsarchitectuur. Dit houdt in dat u een geschikte methode toevoegt aan zowel de DAL als de BLL. Laten we beginnen met het toevoegen van een methode aan de DAL. Open de Northwind.xsd getypte gegevensset, klik met de rechtermuisknop op de ProductsTableAdaptergegevensset en kies de optie Query toevoegen in het contextmenu. Zoals we in de vorige zelfstudie hebben gedaan, willen we deze nieuwe DAL-methode configureren voor het gebruik van een bestaande opgeslagen procedure, GetProductsPagedAndSortedin dit geval. Begin door aan te geven dat u wilt dat de nieuwe TableAdapter-methode een bestaande opgeslagen procedure gebruikt.

Afbeelding 3: Een bestaande opgeslagen procedure gebruiken

Als u de opgeslagen procedure wilt opgeven die u wilt gebruiken, selecteert u de GetProductsPagedAndSorted opgeslagen procedure in de vervolgkeuzelijst in het volgende scherm.

Afbeelding 4: Gebruik de opgeslagen procedure GetProductsPagedAndSorted

Deze opgeslagen procedure retourneert een set records als resultaten, zodat in het volgende scherm wordt aangegeven dat er tabellaire gegevens worden geretourneerd.

Afbeelding 5: Geef aan dat de opgeslagen procedure tabellaire gegevens retourneert

Ten slotte maakt u DAL-methoden die gebruikmaken van zowel de Fill a DataTable als Return a DataTable-patronen, het benoemen van de methoden FillPagedAndSorted en GetProductsPagedAndSorted, respectievelijk.

Afbeelding 6: De namen van methoden kiezen

Nu we de DAL hebben uitgebreid, zijn we klaar om naar de BLL te gaan. Open het ProductsBLL klassebestand en voeg een nieuwe methode toe. GetProductsPagedAndSorted Deze methode moet drie invoerparameters sortExpressionstartRowIndexaccepteren en maximumRows moet gewoon in de DAL-methode GetProductsPagedAndSorted worden aangeroepen, zoals:

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

Stap 3: De ObjectDataSource configureren om de SortExpression-parameter door te geven.

Als u de DAL- en BLL-methoden hebt uitgebreid om methoden op te nemen die gebruikmaken van de GetProductsPagedAndSorted opgeslagen procedure, hoeft u alleen de ObjectDataSource op de SortParameter.aspx pagina te configureren om de nieuwe BLL-methode te gebruiken en de SortExpression parameter door te geven op basis van de kolom waarop de gebruiker heeft gevraagd de resultaten te sorteren.

Begin met het wijzigen van de ObjectDataSource SelectMethod van GetProductsPaged naar GetProductsPagedAndSorted. U kunt dit doen via de wizard Gegevensbron configureren, vanuit het venster Eigenschappen of rechtstreeks via de declaratieve syntaxis. Vervolgens moeten we een waarde opgeven voor de eigenschap ObjectDataSourceSortParameterName. Als deze eigenschap is ingesteld, probeert de ObjectDataSource de GridView-eigenschap via SortExpression door te geven naar SelectMethod. De ObjectDataSource zoekt met name naar een invoerparameter waarvan de naam gelijk is aan de waarde van de SortParameterName eigenschap. Omdat de BLL-methode GetProductsPagedAndSorted de invoerparameter voor de sorteeruitdrukking genaamd sortExpression heeft, stelt u de eigenschap ObjectDataSource SortExpression in op sortExpression.

Nadat u deze twee wijzigingen hebt aangebracht, moet de declaratieve syntaxis van ObjectDataSource er ongeveer als volgt uitzien:

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

Opmerking

Net als bij de voorgaande zelfstudie moet u ervoor zorgen dat de ObjectDataSource niet de invoerparameters sortExpression, startRowIndex of maximumRows in de verzameling SelectParameters bevat.

Als u sorteren in de GridView wilt inschakelen, vinkt u het selectievakje "Sorteren inschakelen" aan in de slimme tag van de GridView. Dit stelt de eigenschap AllowSorting van de GridView in op true, waardoor de koptekst voor elke kolom wordt weergegeven als een LinkButton. Wanneer de eindgebruiker op een van de LinkButtons in de kop klikt, vindt een postback plaats en worden de volgende stappen uitgevoerd:

1. De eigenschap GridView werkt SortExpression de eigenschap bij naar de waarde van het SortExpression veld waarvan op de koptekstkoppeling is geklikt
2. De ObjectDataSource roept de methode GetProductsPagedAndSorted van de BLL aan en geeft de eigenschap van de GridView SortExpression door als de waarde voor de invoerparameter van de methode sortExpression, samen met de juiste invoerparameterwaarden voor startRowIndex en maximumRows.
3. De BLL roept de DAL-methode GetProductsPagedAndSorted aan
4. De DAL voert de GetProductsPagedAndSorted opgeslagen procedure uit, waarbij de @sortExpression parameter wordt doorgegeven (samen met de @startRowIndex en @maximumRows invoerparameterwaarden)
5. De opgeslagen procedure retourneert de juiste subset van gegevens naar de BLL, die deze retourneert naar de ObjectDataSource; deze gegevens worden vervolgens gebonden aan de GridView, weergegeven in HTML en naar de eindgebruiker verzonden

Afbeelding 7 toont de eerste pagina met resultaten wanneer ze worden gesorteerd op de UnitPrice in oplopende volgorde.

Afbeelding 7: De resultaten worden gesorteerd op de prijs per eenheid (klik om de afbeelding op volledige grootte weer te geven)

Hoewel de huidige implementatie de resultaten correct kan sorteren op productnaam, categorienaam, hoeveelheid per eenheid en eenheidsprijs, wordt geprobeerd de resultaten te ordenen op basis van de naam van de leverancier in een runtime-uitzondering (zie afbeelding 8).

Afbeelding 8: De resultaten sorteren op de resultaten van de leverancier in de volgende runtime-uitzondering

Deze uitzondering treedt op omdat de SortExpression GridView s SupplierName BoundField is ingesteld op SupplierName. De naam van de leverancier in de Suppliers tabel heet eigenlijk CompanyName, we hebben deze kolomnaam hierdoor als SupplierName gealiasd. De OVER-clausule die door de ROW_NUMBER()-functie wordt gebruikt, kan echter niet de alias gebruiken en moet de werkelijke kolomnaam gebruiken. Wijzig daarom de SupplierName BoundField s SortExpression van SupplierName in CompanyName (zie afbeelding 9). Zoals in afbeelding 10 wordt weergegeven, kunnen de resultaten na deze wijziging worden gesorteerd door de leverancier.

Afbeelding 9: Wijzig de SortExpression van het Leveranciersnaam BoundField naar CompanyName

Afbeelding 10: De resultaten kunnen nu worden gesorteerd op leverancier (klik om de afbeelding op volledige grootte weer te geven)

Samenvatting

De aangepaste paging-implementatie die we in de vorige zelfstudie hebben onderzocht, vereist dat de volgorde waarin de resultaten moeten worden gesorteerd, tijdens het ontwerp moeten worden opgegeven. Kortom, dit betekende dat de aangepaste paging-implementatie die we hebben geïmplementeerd, niet tegelijkertijd sorteermogelijkheden konden bieden. In deze zelfstudie hebben we deze beperking overschreven door de opgeslagen procedure uit te breiden van de eerste om een @sortExpression invoerparameter op te nemen waarmee de resultaten kunnen worden gesorteerd.

Na het maken van deze opgeslagen procedure en het maken van nieuwe methoden in de DAL en BLL, konden we een GridView implementeren die zowel sortering als aangepaste paging bood door de ObjectDataSource te configureren om de huidige SortExpression eigenschap van GridView door te geven aan de BLL SelectMethod.

Veel plezier met programmeren!

Over de auteur

Scott Mitchell, auteur van zeven ASP/ASP.NET-boeken en oprichter van 4GuysFromRolla.com, werkt sinds 1998 met Microsoft-webtechnologieën. Scott werkt als onafhankelijk consultant, trainer en schrijver. Zijn laatste boek is Sams Teach Yourself ASP.NET 2.0 in 24 uur. Hij kan worden bereikt op mitchell@4GuysFromRolla.com.

Speciale dank aan

Deze tutorialreeks is beoordeeld door veel behulpzame beoordelers. Hoofdrecensent voor deze handleiding was Carlos Santos. Bent u geïnteresseerd in het bekijken van mijn aanstaande MSDN-artikelen? Zo ja, laat iets van je horen via mitchell@4GuysFromRolla.com.

VorigeVolgende