Ordinamento dei dati con suddivisione in pagine personalizzata (C#)

di Scott Mitchell

Scarica il PDF

Nell'esercitazione precedente si è appreso come implementare il paging personalizzato durante la presentazione dei dati in una pagina Web. In questa esercitazione viene illustrato come estendere l'esempio precedente per includere il supporto per l'ordinamento del paging personalizzato.

Introduzione

Rispetto al paging predefinito, il paging personalizzato può migliorare le prestazioni del paging tramite i dati in base a diversi ordini di grandezza, rendendo il paging personalizzato la scelta di implementazione del paging dei fatti durante il paging in grandi quantità di dati. L'implementazione del paging personalizzato è più interessata rispetto all'implementazione del paging predefinito, tuttavia, soprattutto quando si aggiunge l'ordinamento alla combinazione. In questa esercitazione si estenderà l'esempio da quello precedente per includere il supporto per l'ordinamento e il paging personalizzato.

Nota

Poiché questa esercitazione si basa su quella precedente, prima di iniziare richiedere qualche minuto per copiare la sintassi dichiarativa all'interno dell'elemento <asp:Content> dalla pagina Web dell'esercitazione precedente (EfficientPaging.aspx) e incollarla tra l'elemento <asp:Content> nella SortParameter.aspx pagina. Fare riferimento al passaggio 1 dell'esercitazione Aggiunta di controlli di convalida all'esercitazione Modifica e inserimento di interfacce per una discussione più dettagliata sulla replica delle funzionalità di una pagina ASP.NET a un'altra.

Passaggio 1: Rieseminare la tecnica di paging personalizzata

Per il corretto funzionamento del paging personalizzato, è necessario implementare una tecnica in grado di acquisire in modo efficiente un determinato subset di record in base ai parametri Start Row Index e Maximum Rows. Esistono alcune tecniche che possono essere usate per raggiungere questo obiettivo. Nell'esercitazione precedente è stata esaminata questa operazione usando la nuova ROW_NUMBER() funzione di classificazione di Microsoft SQL Server 2005. In breve, la ROW_NUMBER() funzione di classificazione assegna un numero di riga a ogni riga restituita da una query classificata in base a un ordine di ordinamento specificato. Il subset appropriato di record viene quindi ottenuto restituendo una sezione specifica dei risultati numerati. La query seguente illustra come usare questa tecnica per restituire tali prodotti numerati da 11 a 20 quando classifica i risultati ordinati alfabeticamente in base a 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

Questa tecnica funziona bene per il paging usando un ordinamento specifico (ProductName ordinato alfabeticamente, in questo caso), ma la query deve essere modificata per visualizzare i risultati ordinati in base a un'espressione di ordinamento diversa. Idealmente, la query precedente potrebbe essere riscritta per usare un parametro nella OVER clausola , come illustrato di seguito:

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

Sfortunatamente, le clausole con ORDER BY parametri non sono consentite. È invece necessario creare una stored procedure che accetta un @sortExpression parametro di input, ma usa una delle soluzioni alternative seguenti:

• Scrivere query hardcoded per ognuna delle espressioni di ordinamento che possono essere usate; usare IF/ELSE quindi istruzioni T-SQL per determinare la query da eseguire.
• Usare un'istruzione CASE per fornire espressioni dinamiche ORDER BY basate sul @sortExpressio parametro di input n. Per altre informazioni, vedere la sezione Used to Dynamically Sort Query Results in T-SQL Statements (Istruzioni T-SQLCASE).
• Creare la query appropriata come stringa nella stored procedure e quindi usare la sp_executesql stored procedure di sistema per eseguire la query dinamica.

Ognuna di queste soluzioni alternative presenta alcuni svantaggi. La prima opzione non è gestibile come le altre due, perché è necessario creare una query per ogni possibile espressione di ordinamento. Pertanto, se in seguito si decide di aggiungere nuovi campi ordinabili a GridView, sarà necessario tornare indietro e aggiornare la stored procedure. Il secondo approccio presenta alcune sottigliezze che comportano problemi di prestazioni durante l'ordinamento in base a colonne di database non stringa e presenta anche gli stessi problemi di manutenibilità del primo. La terza scelta, che usa SQL dinamico, introduce il rischio di un attacco SQL injection se un utente malintenzionato è in grado di eseguire la stored procedure passando i valori dei parametri di input scelti.

Anche se nessuno di questi approcci è perfetto, credo che la terza opzione sia la migliore delle tre. Con l'uso di SQL dinamico, offre un livello di flessibilità, gli altri due non lo fanno. Inoltre, un attacco SQL injection può essere sfruttato solo se un utente malintenzionato è in grado di eseguire la stored procedure passando i parametri di input di propria scelta. Poiché DAL usa query con parametri, ADO.NET proteggerà i parametri inviati al database tramite l'architettura, ovvero la vulnerabilità di attacco SQL injection esiste solo se l'utente malintenzionato può eseguire direttamente la stored procedure.

Per implementare questa funzionalità, creare una nuova stored procedure nel database Northwind denominato GetProductsPagedAndSorted. Questa stored procedure deve accettare tre parametri di input: @sortExpression, un parametro di input di tipo nvarchar(100) che specifica il modo in cui i risultati devono essere ordinati e inseriti direttamente dopo il ORDER BY testo nella OVER clausola e e @maximumRows@startRowIndex , gli stessi due parametri di input integer della GetProductsPaged stored procedure esaminata nell'esercitazione precedente. Creare la GetProductsPagedAndSorted stored procedure usando lo script seguente:

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

La stored procedure inizia assicurandosi che sia stato specificato un valore per il @sortExpression parametro . Se manca, i risultati vengono classificati in base ProductIDa . Viene quindi creata la query SQL dinamica. Si noti che la query SQL dinamica è leggermente diversa dalle query precedenti usate per recuperare tutte le righe dalla tabella Products. Negli esempi precedenti, ogni prodotto ha ottenuto i nomi delle categorie associate e dei fornitori usando una sottoquery. Questa decisione è stata presa nuovamente nell'esercitazione Creazione di un livello di accesso ai dati ed è stata eseguita al posto di s JOIN perché TableAdapter non è in grado di creare automaticamente i metodi di inserimento, aggiornamento ed eliminazione associati per tali query. La GetProductsPagedAndSorted stored procedure, tuttavia, deve utilizzare JOIN s per ordinare i risultati in base alla categoria o ai nomi dei fornitori.

Questa query dinamica viene compilata concatenando le parti di query statiche e i @sortExpressionparametri , @startRowIndexe @maximumRows . Poiché @startRowIndex e @maximumRows sono parametri integer, devono essere convertiti in nvarchars per essere concatenati correttamente. Dopo aver costruito questa query SQL dinamica, viene eseguita tramite sp_executesql.

Testare questa stored procedure con valori diversi per i @sortExpressionparametri , @startRowIndexe @maximumRows . In Esplora server fare clic con il pulsante destro del mouse sul nome della stored procedure e scegliere Esegui. Verrà visualizzata la finestra di dialogo Esegui stored procedure in cui è possibile immettere i parametri di input (vedere la figura 1). Per ordinare i risultati in base al nome della categoria, utilizzare CategoryName per il valore del @sortExpression parametro. Per ordinare in base al nome della società del fornitore, utilizzare CompanyName. Dopo aver specificato i valori dei parametri, fare clic su OK. I risultati vengono visualizzati nella finestra Output. La figura 2 mostra i risultati quando restituisce i prodotti classificati da 11 a 20 durante l'ordinamento UnitPrice in ordine decrescente.

Figura 1: Provare valori diversi per i tre parametri di input della stored procedure

Figura 2: I risultati della stored procedure vengono visualizzati nella finestra output (fare clic per visualizzare l'immagine a dimensione intera)

Nota

Quando si classificano i risultati in base alla colonna specificata ORDER BY nella OVER clausola , SQL Server deve ordinare i risultati. Si tratta di un'operazione rapida se è presente un indice cluster sulle colonne per cui i risultati vengono ordinati o se è presente un indice di copertura, ma può essere più costoso in caso contrario. Per migliorare le prestazioni per query di dimensioni sufficientemente grandi, è consigliabile aggiungere un indice non cluster per la colonna in base alla quale i risultati vengono ordinati in base a . Per altri dettagli, vedere Funzioni di classificazione e prestazioni in SQL Server 2005.

Passaggio 2: Aumento dei livelli di accesso ai dati e logica di business

Dopo aver creato la GetProductsPagedAndSorted stored procedure, il passaggio successivo consiste nell'eseguire tale stored procedure tramite l'architettura dell'applicazione. Ciò comporta l'aggiunta di un metodo appropriato sia a DAL che a BLL. Per iniziare, aggiungere un metodo a DAL. Aprire il Northwind.xsd set di dati tipizzato, fare clic con il pulsante destro del ProductsTableAdaptermouse su e scegliere l'opzione Aggiungi query dal menu di scelta rapida. Come illustrato nell'esercitazione precedente, si vuole configurare questo nuovo metodo DAL per usare una stored procedure esistente, GetProductsPagedAndSortedin questo caso . Iniziare indicando che si vuole che il nuovo metodo TableAdapter usi una stored procedure esistente.

Figura 3: Scegliere di usare una stored procedure esistente

Per specificare la stored procedure da utilizzare, selezionare la GetProductsPagedAndSorted stored procedure dall'elenco a discesa nella schermata successiva.

Figura 4: Usare la stored procedure GetProductsPagedAndSorted

Questa stored procedure restituisce un set di record come risultati, quindi, nella schermata successiva, indica che restituisce dati tabulari.

Figura 5: Indicare che la stored procedure restituisce dati tabulari

Creare infine metodi DAL che usano entrambi i modelli Fill a DataTable e Return a DataTable, denominando rispettivamente i metodi FillPagedAndSorted e GetProductsPagedAndSorted.

Figura 6: Scegliere i nomi dei metodi

Ora che abbiamo esteso il DAL, siamo pronti per passare al BLL. Aprire il file di ProductsBLL classe e aggiungere un nuovo metodo, GetProductsPagedAndSorted. Questo metodo deve accettare tre parametri di sortExpressioninput , startRowIndexe maximumRows e deve semplicemente chiamare nel metodo DAL, GetProductsPagedAndSorted come illustrato di seguito:

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

Passaggio 3: Configurazione di ObjectDataSource per passare il parametro SortExpression

Dopo aver aumentato DAL e BLL per includere i metodi che utilizzano la GetProductsPagedAndSorted stored procedure, tutto ciò che rimane consiste nel configurare ObjectDataSource nella SortParameter.aspx pagina per usare il nuovo metodo BLL e per passare il SortExpression parametro in base alla colonna richiesta dall'utente per ordinare i risultati.

Per iniziare, modificare ObjectDataSource s SelectMethod da GetProductsPaged a GetProductsPagedAndSorted. Questa operazione può essere eseguita tramite la procedura guidata Configura origine dati, dal Finestra Proprietà o direttamente tramite la sintassi dichiarativa. Successivamente, è necessario specificare un valore per la proprietà ObjectDataSource sSortParameterName. Se questa proprietà è impostata, ObjectDataSource tenta di passare la proprietà SelectMethodgridView a SortExpression . In particolare, ObjectDataSource cerca un parametro di input il cui nome è uguale al valore della SortParameterName proprietà . Poiché il metodo BLL ha GetProductsPagedAndSorted il parametro di input dell'espressione di ordinamento denominato sortExpression, impostare la proprietà ObjectDataSource s SortExpression su sortExpression .

Dopo aver apportato queste due modifiche, la sintassi dichiarativa di ObjectDataSource dovrebbe essere simile alla seguente:

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

Nota

Come per l'esercitazione precedente, assicurarsi che ObjectDataSource non includa i parametri di input sortExpression, startRowIndex o maximumRows nell'insieme SelectParameters.

Per abilitare l'ordinamento in GridView, selezionare semplicemente la casella di controllo Abilita ordinamento nello smart tag gridView di GridView, che imposta la proprietà true GridView su AllowSorting e causando il rendering del testo dell'intestazione per ogni colonna come linkbutton. Quando l'utente finale fa clic su una delle intestazioni LinkButtons, viene eseguito un postback e vengono visualizzati i passaggi seguenti:

1. GridView aggiorna la relativaSortExpression proprietà al valore del SortExpression campo il cui collegamento di intestazione è stato selezionato
2. ObjectDataSource richiama il metodo BLLGetProductsPagedAndSorted, passando la proprietà gridView s SortExpression come valore per il parametro di input del metodo (insieme ai valori appropriati startRowIndex e maximumRows dei parametri di sortExpression input)
3. Il BLL richiama il metodo DAL GetProductsPagedAndSorted
4. DAL esegue la GetProductsPagedAndSorted stored procedure, passando il @sortExpression parametro (insieme ai valori dei @startRowIndex parametri di input e @maximumRows )
5. La stored procedure restituisce il subset di dati appropriato al BLL, che lo restituisce a ObjectDataSource; questi dati vengono quindi associati a GridView, sottoposti a rendering in HTML e inviati all'utente finale

La figura 7 mostra la prima pagina dei risultati in base all'ordinamento UnitPrice crescente.

Figura 7: I risultati vengono ordinati in base a UnitPrice (fare clic per visualizzare l'immagine a dimensione intera)

Anche se l'implementazione corrente può ordinare correttamente i risultati in base al nome del prodotto, al nome della categoria, alla quantità per unità e al prezzo unitario, il tentativo di ordinare i risultati in base al nome del fornitore genera un'eccezione di runtime (vedere la figura 8).

Figura 8: Tentativo di ordinare i risultati in base ai risultati del fornitore nell'eccezione di runtime seguente

Questa eccezione si verifica perché gridView SortExpression s SupplierName BoundField è impostato su SupplierName. Tuttavia, il nome del fornitore nella Suppliers tabella viene effettivamente chiamato CompanyName che questo nome di colonna è stato aliasato come SupplierName. Tuttavia, la OVER clausola utilizzata dalla ROW_NUMBER() funzione non può usare l'alias e deve usare il nome di colonna effettivo. Modificare quindi BoundField SupplierName s SortExpression da SupplierName a CompanyName (vedere la figura 9). Come illustrato nella figura 10, dopo questa modifica i risultati possono essere ordinati in base al fornitore.

Figura 9: Modificare l'oggetto SortExpression di SupplierName BoundField in CompanyName

Figura 10: I risultati possono ora essere ordinati in base al fornitore (fare clic per visualizzare l'immagine a dimensione intera)

Riepilogo

L'implementazione di paging personalizzata esaminata nell'esercitazione precedente richiedeva l'ordinamento dei risultati da specificare in fase di progettazione. In breve, ciò significava che l'implementazione di paging personalizzata implementata non poteva, allo stesso tempo, fornire funzionalità di ordinamento. In questa esercitazione è stata superata questa limitazione estendendo la stored procedure dal primo per includere un @sortExpression parametro di input in base al quale è possibile ordinare i risultati.

Dopo aver creato questa stored procedure e aver creato nuovi metodi in DAL e BLL, è stato possibile implementare un controllo GridView che offriva l'ordinamento e il paging personalizzato configurando ObjectDataSource per passare la proprietà corrente SortExpression di GridView al BLL SelectMethod.

Buon programmatori!

Informazioni sull'autore

Scott Mitchell, autore di sette libri ASP/ASP.NET e fondatore di 4GuysFromRolla.com, lavora con le tecnologie Web Microsoft dal 1998. Scott lavora come consulente indipendente, formatore e scrittore. Il suo ultimo libro è Sams Teach Yourself ASP.NET 2.0 in 24 ore. Può essere raggiunto all'indirizzo mitchell@4GuysFromRolla.com. o tramite il suo blog, disponibile all'indirizzo http://ScottOnWriting.NET.

Grazie speciale a

Questa serie di esercitazioni è stata esaminata da molti revisori utili. Il revisore principale di questa esercitazione è Stato Carlos Santos. Si è interessati a esaminare i prossimi articoli MSDN? In tal caso, rilasciami una riga in mitchell@4GuysFromRolla.com.

IndietroAvanti