Lapozott adatok egyéni rendezése (C#)

által Scott Mitchell

PDF letöltése

Az előző oktatóanyagban megtanultuk, hogyan implementálhat egyéni lapozást, amikor adatokat jelenít meg egy weblapon. Ebben az oktatóanyagban bemutatjuk, hogyan terjesztheti ki az előző példát az egyéni lapozások rendezésének támogatásával.

Bevezetés

Az alapértelmezett lapozáshoz képest az egyéni lapozás több nagyságrenddel javíthatja az adatok lapozásának teljesítményét, így az egyéni lapozás a de facto lapozás implementációját teszi lehetővé nagy mennyiségű adat lapozásakor. Az egyéni lapozás implementálása nagyobb szerepet játszik, mint az alapértelmezett lapozás implementálása, különösen akkor, ha rendezést ad hozzá a mixhez. Ebben az oktatóanyagban kiterjesztjük az előző példát, hogy tartalmazza a rendezés és az egyéni lapozás támogatását.

Megjegyzés:

Mivel ez az oktatóanyag az előzőre épül, a kezdés előtt szánjon egy kis időt a deklaratív szintaxis <asp:Content> másolására az előző oktatóanyag weblapjáról (EfficientPaging.aspx) és illessze be a <asp:Content>SortParameter.aspx lap elemei közé. Az érvényesítési vezérlők hozzáadása a szerkesztési és beszúrási felületekhez oktatóanyag 1. lépésében talál részletesebb ismertetést az egyik ASP.NET lap funkcióinak egy másikba való replikálásával kapcsolatban.

1. lépés: Egyéni lapozási technika újravizsgálata

Ahhoz, hogy az egyéni lapozás megfelelően működjön, olyan technikát kell implementálnunk, amely hatékonyan megragadja a rekordok egy bizonyos részhalmazát a Kezdősor indexe és a Sorok maximális száma paraméterek alapján. Ennek a célnak a megvalósítására több technika is használható. Az előző oktatóanyagban ezt a Microsoft SQL Server 2005 új ROW_NUMBER() rangsorolási függvényével vizsgáljuk meg. Röviden: a ROW_NUMBER() rangsorolási függvény egy sorszámot rendel minden olyan sorhoz, amelyet egy adott rendezési sorrend szerint rangsorolt lekérdezés ad vissza. Ezután a rekordok megfelelő részhalmazát a számozott eredmények egy adott szakaszának visszaadásával nyeri ki. Az alábbi lekérdezés bemutatja, hogyan használhatja ezt a technikát a 11–20-ig számozott termékek visszaadásához, amikor az eredményeket betűrendbe rendezi a ProductNamekövetkező szerint:

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

Ez a technika jól használható egy adott rendezési sorrend (ProductName ebben az esetben betűrendbe rendezve) történő lapozáshoz, de a lekérdezést módosítani kell, hogy az eredményeket egy másik rendezési kifejezés szerint rendezze. Ideális esetben a fenti lekérdezést újra lehet írni, hogy egy paramétert használjon a OVER záradékban, például:

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

Sajnos a paraméteres ORDER BY záradékok nem engedélyezettek. Ehelyett létre kell hoznunk egy tárolt eljárást, amely elfogadja a bemeneti paramétert @sortExpression , de az alábbi megkerülő megoldások egyikét használja:

• Írjon kemény kóddal írt lekérdezéseket az egyes használandó rendezési kifejezésekhez; ezután T-SQL-utasításokkal IF/ELSE állapítsa meg, hogy melyik lekérdezést kell végrehajtania.
• Az n bemeneti paraméteren CASE alapuló dinamikus ORDER BY kifejezések megadásához használjon utasítást@sortExpressio. További információt a T-SQL-utasítások CASE lekérdezési eredmények dinamikus rendezése című szakaszában talál.
• A tárolt eljárásban sztringként készítse el a megfelelő lekérdezést, majd a sp_executesql rendszer által tárolt eljárással hajtsa végre a dinamikus lekérdezést.

Mindegyik kerülő megoldásnak vannak hátrányai. Az első lehetőség nem olyan karbantartható, mint a másik kettő, mivel minden lehetséges rendezési kifejezéshez létre kell hoznia egy lekérdezést. Ezért ha később úgy dönt, hogy új, rendezhető mezőket ad hozzá a GridView-hoz, vissza kell mennie, és frissítenie kell a tárolt eljárást. A második megközelítésben vannak olyan finomságok, amelyek teljesítményproblémákat vetnek fel a nem sztringes adatbázisoszlopok szerinti rendezéskor, és ugyanolyan karbantarthatósági problémákat tapasztalnak, mint az első. A dinamikus SQL-t használó harmadik választási lehetőség pedig egy SQL-injektálási támadás kockázatát növeli, ha a támadó képes végrehajtani a választott bemeneti paraméterértékeken áthaladó tárolt eljárást.

Bár egyik megközelítés sem tökéletes, azt hiszem, a harmadik lehetőség a legjobb a három közül. A dinamikus SQL használatával olyan szintű rugalmasságot biztosít, amelyet a másik kettő nem. Emellett az SQL-injektálási támadást csak akkor lehet kihasználni, ha a támadó képes végrehajtani a választott bemeneti paramétereknek megfelelő tárolt eljárást. Mivel a DAL paraméteres lekérdezéseket használ, ADO.NET védi azokat a paramétereket, amelyeket az architektúra küld az adatbázisba, ami azt jelenti, hogy az SQL-injektálási támadás biztonsági rése csak akkor létezik, ha a támadó közvetlenül végrehajtja a tárolt eljárást.

A funkció implementálásához hozzon létre egy új tárolt eljárást a Northwind adatbázisban GetProductsPagedAndSorted. Ennek a tárolt eljárásnak három bemeneti paramétert kell elfogadnia: @sortExpressionegy bemeneti paramétertnvarchar(100, amely meghatározza az eredmények rendezésének módját, és közvetlenül a záradékban szereplő ORDER BY szöveg után OVER lesz beszúrva; és @startRowIndex@maximumRows ugyanaz a két egész szám bemeneti paraméter az GetProductsPaged előző oktatóanyagban vizsgált tárolt eljárásból. Hozza létre a GetProductsPagedAndSorted tárolt eljárást a következő szkripttel:

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

A tárolt eljárás a paraméter értékének @sortExpression megadásával kezdődik. Ha hiányzik, az eredmények rangsorolása a ProductIDkövetkező szerint történik: . Ezután létre lesz építve a dinamikus SQL-lekérdezés. Vegye figyelembe, hogy az itt található dinamikus SQL-lekérdezés kissé eltér az összes sor lekéréséhez használt korábbi lekérdezésektől a Products táblából. A korábbi példákban minden termékhez társított kategóriák és szállítók neveit egy allekérdezés használatával szereztük be. Régen hozták meg ezt a döntést az Adatelérési réteg létrehozása oktatóanyagban, és JOIN helyett történt, mert a TableAdapter nem tudja automatikusan létrehozni az ilyen lekérdezésekhez tartozó beszúrási, frissítési és törlési metódusokat. A GetProductsPagedAndSorted tárolt eljárásnak azonban JOIN-t kell használnia ahhoz, hogy az eredmények a kategória vagy a szállító neve szerint rendeződjenek.

Ez a dinamikus lekérdezés a statikus lekérdezési részek és a @sortExpression, @startRowIndexés @maximumRows paraméterek összefűzésével jön létre. Mivel @startRowIndex az @maximumRows egész szám paramétereit nvarcharokká kell konvertálni, hogy megfelelően összefűzhetők legyenek. Miután a dinamikus SQL-lekérdezés létrejött, azt a sp_executesql-tal hajtjuk végre.

Szánjon egy kis időt a tárolt eljárás tesztelésére @sortExpressiona , @startRowIndexés @maximumRows paraméterek különböző értékeivel. A Kiszolgálókezelőben kattintson a jobb gombbal a tárolt eljárás nevére, és válassza a Végrehajtás parancsot. Ekkor megjelenik a Tárolt eljárás futtatása párbeszédpanel, amelybe beírhatja a bemeneti paramétereket (lásd az 1. ábrát). Az eredmények kategórianév szerinti rendezéséhez használja a CategoryName értéket a @sortExpression paraméterértékhez; a szállító cégneve szerinti rendezéshez használja a CompanyName nevet. A paraméterek értékeinek megadása után kattintson az OK gombra. Az eredmények a Kimenet ablakban jelennek meg. A 2. ábra a 11–20. helyen rangsorolt termékek visszaadásakor kapott eredményeket mutatja, ha csökkenő UnitPrice sorrendben rendel.

1. ábra: A tárolt eljárás három bemeneti paraméterének különböző értékeinek kipróbálása

2. ábra: A tárolt eljárás eredményei megjelennek a kimeneti ablakban (ide kattintva megtekintheti a teljes méretű képet)

Megjegyzés:

Ha az eredményeket a záradék megadott ORDER BY oszlopa szerint rangsorozza, az OVER SQL Servernek rendeznie kell az eredményeket. Ez egy gyors művelet, ha az oszlop(ok) feletti fürtözött index alapján rendezik az eredményeket, vagy ha van egy fedőindex, de egyébként költségesebb lehet. A megfelelő méretű lekérdezések teljesítményének javítása érdekében érdemes lehet egy nem fürtözött indexet hozzáadni ahhoz az oszlophoz, amellyel az eredményeket rendezi. További részletekért tekintse meg az SQL Server 2005 függvényeinek és teljesítményének rangsorolását .

2. lépés: Az adatelérési és üzleti logikai rétegek bővítése

GetProductsPagedAndSorted A tárolt eljárás létrehozásával a következő lépésünk az, hogy módot biztosítsunk a tárolt eljárás végrehajtására az alkalmazásarchitektúránkon keresztül. Ez azt jelenti, hogy a DAL-hoz és a BLL-hez is hozzá kell adni egy megfelelő módszert. Kezdjük azzal, hogy hozzáadunk egy metódust a DAL-hoz. Nyissa meg a Northwind.xsd Gépelt adatkészletet, kattintson a jobb gombbal a ProductsTableAdapterelemre, és válassza a Lekérdezés hozzáadása lehetőséget a helyi menüből. Ahogy az előző oktatóanyagban is tettük, ezt az új DAL metódust egy meglévő tárolt eljárás használatára szeretnénk konfigurálni – GetProductsPagedAndSortedebben az esetben. Kezdje azzal, hogy azt szeretné, hogy az új TableAdapter metódus egy meglévő tárolt eljárást használjon.

3. ábra: Meglévő tárolt eljárás használata

A használni kívánt tárolt eljárás megadásához válassza ki a GetProductsPagedAndSorted tárolt eljárást a következő képernyő legördülő listájából.

4. ábra: A GetProductsPagedAndSorted tárolt eljárás használata

Ez a tárolt eljárás eredményeként rekordhalmazt ad vissza, így a következő képernyőn azt jelzi, hogy táblázatos adatokat ad vissza.

5. ábra: Azt jelzi, hogy a tárolt eljárás táblázatos adatokat ad vissza

Végül hozzon létre olyan DAL-metódusokat, amelyek a DataTable kitöltése és a DataTable visszaadása mintákat használják, és nevezze el a metódusokat FillPagedAndSorted és GetProductsPagedAndSorted néven.

6. ábra: A metódusok neveinek kiválasztása

Most, hogy kiterjesztettük a DAL-t, készen állunk a BLL-re való fordulásra. Nyissa meg az ProductsBLL osztályfájlt, és adjon hozzá egy új metódust. GetProductsPagedAndSorted Ennek a metódusnak három bemeneti paramétert kell elfogadnia: sortExpression, startRowIndex, és maximumRows, és egyszerűen meg kell hívnia a DAL GetProductsPagedAndSorted metódusát, így:

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

3. lépés: Az ObjectDataSource átadásra való konfigurálása a SortExpression paraméterben

Miután bővítette a DAL-t és a BLL-t, hogy a tárolt eljárást használó GetProductsPagedAndSorted metódusokat is tartalmazzon, mindössze annyit kell tennie, hogy konfigurálja az ObjectDataSource-t a lapon az SortParameter.aspx új BLL-metódus használatára, és adja át a SortExpression paramétert azon oszlop alapján, amellyel a felhasználó az eredmények rendezését kérte.

Először változtassa meg az ObjectDataSource SelectMethod-t GetProductsPaged-ről GetProductsPagedAndSorted-re. Ezt az Adatforrás konfigurálása varázslóval, a Tulajdonságok ablakból vagy közvetlenül a deklaratív szintaxissal teheti meg. Ezután meg kell adnunk egy értéket az ObjectDataSource tulajdonságáhozSortParameterName. Ha ez a tulajdonság be van állítva, az ObjectDataSource megpróbálja átadni a GridView SortExpression tulajdonságát a SelectMethod-nek. Az ObjectDataSource olyan bemeneti paramétert keres, amelynek neve megegyezik a SortParameterName tulajdonság értékével. Mivel a BLL GetProductsPagedAndSorted metódusa rendelkezik a rendezési kifejezés bemeneti paraméterével sortExpression, állítsa az ObjectDataSource tulajdonságot SortExpression sortExpression értékre.

A két módosítás elvégzése után az ObjectDataSource deklaratív szintaxisának a következőhöz hasonlóan kell kinéznie:

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

Megjegyzés:

Az előző oktatóanyaghoz hasonlóan győződjön meg arról, hogy az ObjectDataSource nem tartalmazza a SortExpression, a startRowIndex vagy a maximumRows bemeneti paramétereket a SelectParameters gyűjteményben.

Ha engedélyezni szeretné a rendezést a GridView-ban, egyszerűen jelölje be a GridView intelligens címkéjének Rendezés engedélyezése jelölőnégyzetét, amely beállítja a GridView tulajdonságot AllowSortingtrue , és emiatt az egyes oszlopok fejlécszövege LinkButtonként jelenik meg. Amikor a végfelhasználó a LinkButtons fejléc egyikére kattint, megjelenik egy postback, és a következő lépések következnek be:

1. A GridView a saját tulajdonságátSortExpression annak a mezőnek az SortExpression értékére frissíti, amelynek fejléchivatkozására kattintottak.
2. Az ObjectDataSource meghívja a BLL metódusát GetProductsPagedAndSorted, és a GridView tulajdonságát SortExpression használja a metódus bemeneti paraméterének sortExpression értékeként (a megfelelő startRowIndex és maximumRows bemeneti paraméterértékekkel együtt).
3. A BLL meghívja a DAL metódust GetProductsPagedAndSorted
4. A DAL végrehajtja a GetProductsPagedAndSorted tárolt eljárást, átadva a @sortExpression paramétert (a @startRowIndex bemeneti paraméter @maximumRows értékeivel együtt)
5. A tárolt eljárás visszaadja az adatok megfelelő részhalmazát a BLL-nek, amely visszaadja az ObjectDataSource-nak; ezek az adatok ezután a GridView-hoz lesznek kötve, HTML-be lesznek renderelve, és elküldhetők a végfelhasználónak

A 7. ábrán a találatok első oldala látható, ha növekvő sorrendbe rendezik.UnitPrice

7. ábra: Az eredmények a UnitPrice szerint vannak rendezve (ide kattintva megtekintheti a teljes méretű képet)

Bár az aktuális implementáció megfelelően rendezi az eredményeket a terméknév, a kategória neve, az egységenkénti mennyiség és az egységár szerint, a szállító neve alapján történő eredménysorrendezés futásidejű kivételt eredményez (lásd a 8. ábrát).

8. ábra: Az eredmények rendezése a szállítói eredmények szerint a következő futtatókörnyezeti kivételben

Ez a kivétel azért fordul elő, mert a SortExpression GridView s SupplierName BoundField értéke SupplierName. Azonban a táblázatban a szállító neve valójában Suppliers, és ezt az oszlopnevet CompanyName-ként aliasoltuk.SupplierName A OVER függvény által ROW_NUMBER() használt záradék azonban nem használhatja az aliast, és a tényleges oszlopnevet kell használnia. Ezért módosítsa a SupplierName BoundField s értékét SortExpression a SupplierName értékről a CompanyName értékre (lásd a 9. ábrát). Ahogy a 10. ábra mutatja, a módosítás után az eredményeket a szállító rendezheti.

**

9. ábra: A SupplierName BoundField s SortExpression módosítása CompanyName értékre

10. ábra: Az eredmények szállító szerint rendezhetők (ide kattintva megtekintheti a teljes méretű képet)

Összefoglalás

Az előző oktatóanyagban megvizsgált egyéni lapozási implementáció megkövetelte, hogy az eredmények rendezési sorrendje a tervezéskor legyen megadva. Röviden, ez azt jelentette, hogy az egyéni lapozási implementáció, amelyet implementáltunk, nem biztosíthat rendezési képességeket. Ebben az oktatóanyagban felülbíráltuk ezt a korlátozást azáltal, hogy kiterjesztjük a tárolt eljárást az elsőről egy @sortExpression bemeneti paraméter hozzáadására, amellyel az eredmények rendezhetők.

Miután létrehozta ezt a tárolt eljárást, és új metódusokat hozott létre a DAL-ban és a BLL-ben, sikerült implementálni egy GridView-t, amely a rendezést és az egyéni lapozást is lehetővé tette az ObjectDataSource konfigurálásával, hogy a GridView aktuális SortExpression tulajdonságát a BLL-nek SelectMethodadja át.

Boldog programozást!

Tudnivalók a szerzőről

Scott Mitchell, hét ASP/ASP.NET-könyv szerzője és a 4GuysFromRolla.com alapítója, 1998 óta dolgozik a Microsoft webtechnológiáival. Scott független tanácsadóként, edzőként és íróként dolgozik. Legújabb könyve Sams Tanuld meg ASP.NET 2.0 24 óra alatt. Ő itt elérhető mitchell@4GuysFromRolla.com.

Külön köszönet

Ezt az oktatóanyag-sorozatot sok hasznos véleményező áttekintette. Az oktatóanyag vezető véleményezője Carlos Santos volt. Szeretné áttekinteni a közelgő MSDN-cikkeimet? Ha igen, írj egy sort a mitchell@4GuysFromRolla.com-ra.

ElőzőKövetkező