Sortera anpassade siddata (VB)

av Scott Mitchell

Ladda ned PDF

I den föregående handledningen lärde vi oss hur vi implementerar anpassad sidindelning när vi presenterar data på en webbsida. I den här självstudien ser vi hur du utökar föregående exempel till att omfatta stöd för sortering av anpassad sidindelning.

Inledning

Jämfört med standardpaginering kan anpassad paginering förbättra prestandan vid paginering genom data med flera storleksordningar. Detta gör anpassad paginering till det naturliga valet för implementering när man hanterar stora mängder data. Det är mer komplicerat att implementera anpassad paginering än att implementera standardpaginering, särskilt när du lägger till sortering i kombinationen. I den här självstudien kommer vi att utöka exemplet från den föregående för att inkludera stöd för sortering och anpassad paginering.

Anmärkning

Eftersom den här självstudiekursen bygger på den föregående, ta en stund innan du börjar att kopiera den deklarativa syntaxen inom elementet <asp:Content> från webbsidan för den föregående självstudiekursen (EfficientPaging.aspx) och klistra in den mellan elementet <asp:Content> på SortParameter.aspx-sidan. Gå tillbaka till steg 1 i Lägga till verifieringskontroller i gränssnitten för redigering och infogning självstudien för en mer detaljerad diskussion om hur du replikerar funktionerna från en ASP.NET-sida till en annan.

Steg 1: Granska igen tekniken för anpassad bläddring

För att anpassad sidnumrering ska fungera som förväntat måste vi implementera en teknik som effektivt kan extrahera en specifik uppsättning poster utifrån parametrarna Startradsindex och Maximalt antal rader. Det finns en handfull tekniker som kan användas för att uppnå detta mål. I den föregående självstudien tittade vi på hur du gör detta med hjälp av den nya ROW_NUMBER() rankningsfunktionen i Microsoft SQL Server 2005. Kort och kort tilldelar rangordningsfunktionen ROW_NUMBER() ett radnummer till varje rad som returneras av en fråga som rangordnas efter en angiven sorteringsordning. Lämplig delmängd av poster erhålls sedan genom att returnera ett visst avsnitt av de numrerade resultaten. Följande fråga illustrerar hur du använder den här tekniken för att returnera dessa produkter numrerade 11 till 20 när resultaten rangordnas alfabetiskt efter 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

Den här metoden fungerar bra för paginering med hjälp av en specifik sorteringsordning (ProductName sorterade alfabetiskt, i det här fallet), men frågesträngen måste ändras för att visa resultaten sorterade efter ett annat sorteringsuttryck. Helst skulle ovanstående fråga kunna skrivas om för att använda en parameter i OVER -satsen, så här:

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

Tyvärr tillåts inte parametriserade ORDER BY satser. I stället måste vi skapa en lagrad procedur som accepterar en @sortExpression indataparameter, men som använder någon av följande lösningar:

• Skriv hårdkodade frågor för vart och ett av de sorteringsuttryck som kan användas. använd IF/ELSE sedan T-SQL-uttryck för att avgöra vilken fråga som ska köras.
• Använd en CASE instruktion för att tillhandahålla dynamiska ORDER BY uttryck baserat på parametern @sortExpressio n indata. Mer information finns i avsnittet Använd för att dynamiskt sortera frågeresultat i T-SQL-uttryckCASE.
• Skapa lämplig fråga som en sträng i den lagrade proceduren och använd sedan den sp_executesql system lagrade proceduren för att köra den dynamiska frågan.

Var och en av dessa lösningar har vissa nackdelar. Det första alternativet är inte lika underhållsbart som de andra två eftersom det kräver att du skapar en fråga för varje möjligt sorteringsuttryck. Om du senare bestämmer dig för att lägga till nya sorterbara fält i GridView måste du också gå tillbaka och uppdatera den lagrade proceduren. Den andra metoden har vissa subtiliteter som ger prestandaproblem vid sortering efter icke-strängdatabaskolumner och som också drabbas av samma underhållsproblem som den första. Och det tredje valet, som använder dynamisk SQL, introducerar risken för en SQL-inmatningsattack om en angripare kan köra den lagrade proceduren som skickar in de indataparametervärden som de väljer.

Även om inget av dessa tillvägagångssätt är perfekt, tror jag att det tredje alternativet är det bästa av de tre. Med dess användning av dynamisk SQL erbjuder den en flexibilitetsnivå som de andra två inte gör. Dessutom kan en SQL-inmatningsattack bara utnyttjas om en angripare kan köra den lagrade proceduren som skickar in de indataparametrar som han väljer. Eftersom DAL använder parametriserade frågor skyddar ADO.NET de parametrar som skickas till databasen via arkitekturen, vilket innebär att sårbarheten för SQL-inmatningsattacker bara finns om angriparen kan köra den lagrade proceduren direkt.

Om du vill implementera den här funktionen skapar du en ny lagrad procedur i Northwind-databasen med namnet GetProductsPagedAndSorted. Den här lagrade proceduren bör acceptera tre indataparametrar: @sortExpression, en indataparameter av typen nvarchar(100) som anger hur resultatet ska sorteras och matas in direkt efter ORDER BY texten i OVER satsen och @startRowIndex@maximumRows, samma två heltalsindataparametrar från den GetProductsPaged lagrade proceduren som undersöktes i föregående självstudie. Skapa den GetProductsPagedAndSorted lagrade proceduren med hjälp av följande 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

Den lagrade proceduren börjar med att se till att ett värde för parametern @sortExpression har angetts. Om den saknas rangordnas resultatet efter ProductID. Därefter skapas den dynamiska SQL-frågan. Observera att den dynamiska SQL-frågan här skiljer sig något från våra tidigare frågor som används för att hämta alla rader från tabellen Produkter. I tidigare exempel hämtade vi varje produkts associerade kategori- och leverantörsnamn med hjälp av en underfråga. Det här beslutet fattades i självstudien Skapa ett dataåtkomstlager och gjordes i stället för att använda JOIN s eftersom TableAdapter inte automatiskt kan skapa de associerade metoderna insert, update och delete för sådana frågor. Den GetProductsPagedAndSorted lagrade proceduren måste dock använda JOIN s för att resultaten ska ordnas efter kategori- eller leverantörsnamn.

Den här dynamiska frågan skapas genom att sammanlänka de statiska frågedelarna och parametrarna @sortExpression, @startRowIndexoch @maximumRows . Eftersom @startRowIndex och @maximumRows är heltalsparametrar måste de konverteras till nvarchars för att kunna sammanfogas korrekt. När den här dynamiska SQL-frågan har konstruerats körs den via sp_executesql.

Ta en stund att testa den här lagrade proceduren med olika värden för parametrarna @sortExpression, @startRowIndexoch @maximumRows . I ServerUtforskaren högerklickar du på namnet på den lagrade proceduren och väljer Kör. Då visas dialogrutan Kör lagrad procedur där du kan ange indataparametrarna (se bild 1). Om du vill sortera resultatet efter kategorinamnet använder du CategoryName för @sortExpression parametervärdet. Om du vill sortera efter leverantörens företagsnamn använder du CompanyName. När du har angett parametervärdena klickar du på OK. Resultatet visas i utdatafönstret. Bild 2 visar resultatet när du returnerar produkter rankade 11 till 20 när de UnitPrice sorteras efter i fallande ordning.

Bild 1: Prova olika värden för den lagrade procedurens tre indataparametrar

Bild 2: Den lagrade procedurens resultat visas i utdatafönstret (klicka om du vill visa en bild i full storlek)

Anmärkning

När resultatet rangordnas efter den angivna ORDER BY kolumnen i OVER -satsen måste SQL Server sortera resultatet. Detta är en snabb åtgärd om det finns ett grupperat index över de kolumner som resultaten sorteras efter eller om det finns ett täckande index, men kan bli dyrare annars. Om du vill förbättra prestandan för tillräckligt stora frågor kan du överväga att lägga till ett icke-grupperat index för kolumnen som resultaten sorteras efter. Mer information finns i Rankningsfunktioner och prestanda i SQL Server 2005 .

Steg 2: Utöka dataåtkomst- och affärslogiklagren

När den GetProductsPagedAndSorted lagrade proceduren har skapats är vårt nästa steg att tillhandahålla ett sätt att köra den lagrade proceduren via vår programarkitektur. Detta innebär att du lägger till en lämplig metod för både DAL och BLL. Vi börjar med att lägga till en metod i DAL. Öppna den Northwind.xsd typerade datauppsättningen, högerklicka på ProductsTableAdapter och välj alternativet Lägg till fråga på snabbmenyn. Precis som i föregående självstudie vill vi konfigurera den nya DAL-metoden så att den använder en befintlig lagrad procedur – GetProductsPagedAndSorted, i det här fallet. Börja med att ange att du vill att den nya TableAdapter-metoden ska använda en befintlig lagrad procedur.

Bild 3: Välj att använda en befintlig lagrad procedur

Om du vill ange den lagrade procedur som ska användas väljer du den GetProductsPagedAndSorted lagrade proceduren i listrutan på nästa skärm.

Bild 4: Använd den lagrade proceduren GetProductsPagedAndSorted

Den här lagrade proceduren returnerar en uppsättning poster som resultat, så ange på nästa skärm att den returnerar tabelldata.

Bild 5: Ange att den lagrade proceduren returnerar tabelldata

Slutligen skapar du DAL-metoder som använder både Fill a DataTable och Return a DataTable patterns ( Returnera ett DataTable-mönster) och namnger metoderna FillPagedAndSorted respektive GetProductsPagedAndSorted.

Bild 6: Välj namn på metoder

Nu när vi har utökat DAL är vi redo att vända oss till BLL. ProductsBLL Öppna klassfilen och lägg till en ny metod, GetProductsPagedAndSorted. Den här metoden måste acceptera tre indataparametrar sortExpression, startRowIndex, och maximumRows och ska helt enkelt anropa DAL:s GetProductsPagedAndSorted-metod, så här:

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

Steg 3: Konfigurera ObjectDataSource för att skicka in Parametern SortExpression

Efter att ha utökat DAL och BLL för att inkludera metoder som använder den GetProductsPagedAndSorted lagrade proceduren är allt som återstår att konfigurera ObjectDataSource på SortParameter.aspx sidan för att använda den nya BLL-metoden och skicka in parametern SortExpression baserat på kolumnen som användaren har begärt att sortera resultaten efter.

Börja med att ändra ObjectDataSource s SelectMethod från GetProductsPaged till GetProductsPagedAndSorted. Detta kan göras via guiden Konfigurera datakälla, från fönstret Egenskaper eller direkt via den deklarativa syntaxen. Därefter måste vi ange ett värde för egenskapen ObjectDataSource.SortParameterName Om den här egenskapen har angetts försöker ObjectDataSource skicka egenskapen GridView SortExpression till SelectMethod. I synnerhet söker ObjectDataSource efter en indataparameter vars namn är lika med värdet för SortParameterName egenskapen. Eftersom metoden i BLL har en indataparamer som heter GetProductsPagedAndSorted och är benämnd sortExpression, bör du ställa in ObjectDataSource-egenskapen till SortExpression sortExpression.

När du har gjort dessa två ändringar bör ObjectDataSources deklarativa syntax se ut ungefär så här:

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

Anmärkning

Precis som i föregående handledning, kontrollera att ObjectDataSource inte inkluderar indata-parametrarna sortExpression, startRowIndex eller maximumRows i samlingen SelectParameters.

Om du vill aktivera sortering i GridView markerar du kryssrutan Aktivera sortering i GridViews smarta tagg, som anger egenskapen GridView till AllowSortingtrue och gör att rubriktexten för varje kolumn återges som en LinkButton. När en slutanvändare klickar på en av sidhuvudets länkknappar, sker en postback och följande steg inträffar:

1. GridView uppdaterar sinSortExpression egenskap till värdet SortExpression för det fält vars rubriklänk klickades
2. ObjectDataSource anropar BLL-metoden GetProductsPagedAndSorted och skickar egenskapen GridView SortExpression som värde för metodens sortExpression indataparameter (tillsammans med lämpliga startRowIndex parametervärden och maximumRows indataparametervärden)
3. BLL anropar DAL s-metoden GetProductsPagedAndSorted
4. DAL kör den GetProductsPagedAndSorted lagrade proceduren och skickar in parametern @sortExpression (tillsammans med parametervärdena @startRowIndex och @maximumRows indata)
5. Den lagrade proceduren returnerar lämplig delmängd av data till BLL:n, som returnerar den till ObjectDataSource. dessa data binds sedan till GridView, renderas i HTML och skickas ned till slutanvändaren

Bild 7 visar den första sidan med resultat när den sorteras efter i UnitPrice stigande ordning.

Bild 7: Resultaten sorteras efter UnitPrice (Klicka om du vill visa en bild i full storlek)

Även om den aktuella implementeringen kan sortera resultatet efter produktnamn, kategorinamn, kvantitet per enhet och enhetspris, resulterar försök att sortera resultaten efter leverantörsnamnet i ett körningsundatag (se bild 8).

Bild 8: Ett försök att sortera resultaten efter leverantör resulterar i följande körningsundantag

Det här undantaget beror på att GridView's BoundField är inställt till SortExpression. Dock kallas leverantörens namn i Suppliers-tabellen faktiskt CompanyName, eftersom vi har gett detta kolumnnamn aliaset SupplierName. Den klausul som används av OVER-funktionen kan dock inte använda aliaset ROW_NUMBER() och måste använda det faktiska kolumnnamnet. SupplierName Ändra därför BoundFields SortExpression från SupplierName till CompanyName (se bild 9). Som bild 10 visar kan resultatet efter den här ändringen sorteras efter leverantören.

Bild 9: Ändra SupplierName BoundFields SortExpression till CompanyName

Bild 10: Resultatet kan nu sorteras efter leverantör (klicka om du vill visa en bild i full storlek)

Sammanfattning

Den anpassade pagineringen som vi undersökte i föregående handledning krävde att den ordning som resultaten skulle sorteras efter specificerades vid design. Kort sagt innebar detta att den anpassade pagingimplementeringen som vi implementerade inte samtidigt kunde erbjuda sorteringsfunktioner. I den här handledningen övervann vi denna begränsning genom att utöka den första lagrade proceduren till att inkludera en @sortExpression indataparameter som resultaten kunde sorteras efter.

Efter att ha skapat den här lagrade proceduren och skapat nya metoder i DAL och BLL kunde vi implementera en GridView som erbjöd både sortering och anpassad paginering genom att konfigurera ObjectDataSource för att skicka den aktuella SortExpression egenskapen för GridView till BLL SelectMethod.

Lycka till med programmerandet!

Om författaren

Scott Mitchell, författare till sju ASP/ASP.NET-böcker och grundare av 4GuysFromRolla.com, har arbetat med Microsofts webbtekniker sedan 1998. Scott arbetar som oberoende konsult, tränare och författare. Hans senaste bok är Sams Teach Yourself ASP.NET 2.0 på 24 timmar. Han kan nås på mitchell@4GuysFromRolla.com.

Särskilt tack till

Den här självstudieserien granskades av många användbara granskare. Ansvarig granskare för den här självstudien var Carlos Santos. Vill du granska mina kommande MSDN-artiklar? Om så är fallet, hör av dig på mitchell@4GuysFromRolla.com.

Föregåendenästa