Ordinamento dei dati con suddivisione in pagine personalizzata (VB)

di Scott Mitchell

Scaricare 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 di paging personalizzato.

Introduzione

Rispetto al paging predefinito, il paging personalizzato può migliorare le prestazioni del paging dei dati di vari ordini di grandezza, rendendo il paging personalizzato la scelta standard per l'implementazione del paging quando si elaborano grandi quantità di dati. L'implementazione del paging personalizzato è più complessa rispetto all'implementazione del paging predefinito, specialmente quando si aggiunge l'ordinamento all'insieme. In questa esercitazione estenderemo l'esempio del precedente per includere il supporto per l'ordinamento e il paging personalizzato.

Annotazioni

Poiché questa esercitazione si basa su quella precedente, prima di iniziare, 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 del tutorial Aggiunta di controlli di convalida alle interfacce di modifica e inserimento per una discussione più dettagliata sulla replica delle funzionalità di una pagina ASP.NET ad un'altra.

Passaggio 1: Ripensare la tecnica di paging personalizzato

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 Inizio indice riga e Numero massimo di righe. 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 i prodotti numerati da 11 a 20 quando si classificano 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 con codice fisso per ciascuna delle espressioni di ordinamento che possono essere usate; quindi, utilizzare istruzioni T-SQL per determinare quale query 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 è manutenibile come gli altri due perché è necessario creare una query per ogni possibile espressione di ordinamento. Pertanto, se in un secondo momento 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 introducono 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, penso che la terza opzione sia la migliore delle tre. Con l'uso di SQL dinamico, offre un livello di flessibilità che 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 denominata 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 @startRowIndex@maximumRows , gli stessi due parametri di input integer della GetProductsPaged stored procedure esaminata nell'esercitazione precedente. Creare la procedura memorizzata GetProductsPagedAndSorted 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 a ProductID. 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 sono stati ottenuti i nomi di ogni prodotto associato a categorie e fornitori usando una sottoquery. Questa decisione è stata presa in precedenza nell'esercitazione Creazione di un livello di accesso ai dati ed è stata eseguita al posto di 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 costruita concatenando le parti statiche della query e i parametri @sortExpression, @startRowIndex e @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.

Prenditi un momento per testare questa stored procedure con valori diversi per i parametri @sortExpression, @startRowIndex e @maximumRows. Nell'Esplora risorse del server, fai clic con il pulsante destro del mouse sul nome della procedura archiviata e scegli 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 quando si ordina in base all'ordine UnitPrice 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 di output (fare clic per visualizzare l'immagine a dimensione intera)

Annotazioni

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 in cui vengono ordinati i risultati o se è presente un indice di copertura, ma può essere più costoso in caso contrario. Per migliorare le prestazioni delle query di dimensioni sufficientemente grandi, è consigliabile aggiungere un indice non clusterizzato per la colonna in base alla quale i risultati vengono ordinati. 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, proviamo ad aggiungere un metodo al DAL. Aprire il Northwind.xsd DataSet tipizzato, fare clic con il pulsante destro del mouse su ProductsTableAdapter e scegliere l'opzione Aggiungi query dal menu di scelta rapida. Come illustrato nell'esercitazione precedente, vogliamo configurare questo nuovo metodo DAL per utilizzare una stored procedure esistente, GetProductsPagedAndSorted in questo caso. Indica per iniziare che desideri che il nuovo metodo TableAdapter utilizzi una stored procedure esistente.

Figura 3: Scegliere di usare una stored procedure esistente

Per specificare la procedura memorizzata da usare, selezionare la GetProductsPagedAndSorted procedura memorizzata dall'elenco a discesa nella schermata successiva.

Figura 4: Usare la Stored Procedure GetProductsPagedAndSorted

Questa procedura memorizzata restituisce un insieme di record come risultati, perciò nella schermata successiva indica che restituisce dati tabellari.

Indicare che la procedura memorizzata restituisce dati tabulari

Figura 5: Indicare che la stored procedure restituisce dati tabulari

Infine, creare metodi DAL che usano 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 input: sortExpression, startRowIndex e maximumRows e deve semplicemente chiamare il metodo GetProductsPagedAndSorted della DAL, come indicato di seguito.

<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

Passaggio 3: Configurazione di ObjectDataSource per passare il parametro SortExpression

Dopo aver ampliato DAL e BLL per includere metodi che utilizzano la GetProductsPagedAndSorted stored procedure, tutto ciò che rimane consiste nel configurare ObjectDataSource nella SortParameter.aspx pagina in modo da usare il nuovo metodo BLL e 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, dalla finestra Proprietà o direttamente tramite la sintassi dichiarativa. Successivamente, è necessario fornire un valore per la proprietà ObjectDataSource sSortParameterName. Se questa proprietà è impostata, ObjectDataSource tenta di passare la proprietà GridView a SortExpressionSelectMethod. In particolare, ObjectDataSource cerca un parametro di input il cui nome è uguale al valore della SortParameterName proprietà. Poiché il metodo BLL s GetProductsPagedAndSorted ha il parametro di input dell'espressione di ordinamento denominato sortExpression, impostare la proprietà ObjectDataSource 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>

Annotazioni

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

Per abilitare l'ordinamento in GridView, è sufficiente selezionare la casella di controllo "Abilita ordinamento" nello smart tag di GridView, che imposta la proprietà del GridView su AllowSortingtrue e rende il testo dell'intestazione per ogni colonna come LinkButton. Quando l'utente finale fa clic su uno dei LinkButton nel titolo, si verifica un postback e si svolgono i seguenti passaggi:

1. GridView aggiorna la proprietàSortExpression al valore del SortExpression campo il cui collegamento di intestazione è stato selezionato
2. ObjectDataSource richiama il metodo della BLL GetProductsPagedAndSorted, passando la proprietà GridView SortExpression come valore per il parametro di input del sortExpression metodo (insieme ai valori appropriati dei parametri di input startRowIndex e maximumRows).
3. Il BLL richiama il metodo del DAL GetProductsPagedAndSorted
4. DAL esegue la procedura memorizzata GetProductsPagedAndSorted, passando il parametro @sortExpression insieme ai valori dei parametri di input @startRowIndex e @maximumRows.
5. La stored procedure restituisce il sottoinsieme 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

Nella figura 7 viene mostrata la prima pagina dei risultati in base all'ordinamento UnitPrice crescente.

Figura 7: I risultati vengono ordinati in base all'unità (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 effettivo della colonna. 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.

Cambiare l'attributo SortExpression del BoundField SupplierName in CompanyName

Figura 9: Modificare la proprietà 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)

Riassunto

L'implementazione di paging personalizzata che abbiamo esaminato nell'esercitazione precedente richiedeva che l'ordine di ordinamento dei risultati fosse specificato 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 abbiamo superato questa limitazione estendendo la stored procedure originale per includere un parametro di input @sortExpression tramite il quale è possibile ordinare i risultati.

Dopo aver creato questa stored procedure e 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 all'oggetto BLL SelectMethod.

Buon programmatori!

Informazioni sull'autore

Scott Mitchell, autore di sette libri ASP/ASP.NET e fondatore di 4GuysFromRolla.com, ha lavorato 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 a mitchell@4GuysFromRolla.com.

Grazie speciale a

Questa serie di esercitazioni è stata esaminata da molti revisori competenti. Il revisore principale per questa esercitazione è stato Carlos Santos. Si è interessati a esaminare i prossimi articoli MSDN? In tal caso, mandami un messaggio a mitchell@4GuysFromRolla.com.

PrecedenteAvanti