Ordinamento dei dati con suddivisione in pagine personalizzata (VB)

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

Introduzione

Rispetto al paging predefinito, il paging personalizzato può migliorare le prestazioni del paging tramite dati in base a diversi ordini di grandezza, rendendo personalizzata la scelta di paging del paging de facto durante il paging tramite grandi quantità di dati. L'implementazione di paging personalizzati è più importante dell'implementazione del paging predefinito, tuttavia, soprattutto quando si aggiunge l'ordinamento alla combinazione. In questa esercitazione verrà esteso 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 a copiare la sintassi dichiarativa all'interno <asp:Content> dell'elemento 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 della funzionalità di una pagina ASP.NET a un'altra.

Passaggio 1: Riexamining della tecnica di paging personalizzata

Per il corretto funzionamento del paging personalizzato, è necessario implementare alcune tecniche che possono 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 microsoft SQL Server 2005 la nuova ROW_NUMBER() funzione di classificazione. In breve, la ROW_NUMBER() funzione di classificazione assegna un numero di riga a ogni riga restituita da una query classificata da 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 in ordine alfabetico per :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 ordine di ordinamento specifico (ProductName ordinato alfabeticamente, in questo caso), ma la query deve essere modificata per visualizzare i risultati ordinati da un'espressione di ordinamento diversa. Idealmente, la query precedente potrebbe essere riscritta per usare un parametro nella OVER clausola, ad esempio:

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 quale query eseguire.
• Usare un'istruzione CASE per fornire espressioni dinamiche ORDER BY in base al @sortExpressio parametro di input n. Per altre informazioni, vedere la sezione Utilizzata per ordinare dinamicamente i risultati delle query nelle 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 introducono problemi di prestazioni durante l'ordinamento in base alle colonne di database non stringa e soffrono anche degli stessi problemi di gestibilità 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 della scelta.

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à gli altri due non. 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 della propria scelta. Poiché il dal servizio di accesso remoto usa query con parametri, ADO.NET proteggerà tali 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 come i risultati devono essere ordinati e inseriti direttamente dopo il ORDER BY testo nella OVER clausola e @startRowIndex@maximumRowse , gli stessi due parametri di input integer della GetProductsPaged stored procedure esaminata nell'esercitazione precedente. Creare la stored procedure usando lo GetProductsPagedAndSorted 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 da ProductID. Successivamente, viene creata la query SQL dinamica. Si noti che la query SQL dinamica qui differisce leggermente dalle query precedenti usate per recuperare tutte le righe dalla tabella Products. Negli esempi precedenti è stato ottenuto ogni nome di categoria associato del prodotto e dei fornitori usando una sottoquery. Questa decisione è stata presa nuovamente nell'esercitazione Creazione di un livello di accesso ai dati ed è stata eseguita in sostituzione dell'uso JOIN di s perché TableAdapter non può creare automaticamente i metodi di inserimento, aggiornamento ed eliminazione associati per tali query. La GetProductsPagedAndSorted stored procedure, tuttavia, deve essere usata JOIN per i risultati da ordinare in base alla categoria o ai nomi dei fornitori.

Questa query dinamica viene compilata concatenando le parti di query statiche e i parametri , @startRowIndexe @maximumRows@sortExpression. 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.

Eseguire il test di 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, usare CategoryName per il valore del parametro. Per ordinare in base al @sortExpression nome della società del fornitore, usare CompanyName. Dopo aver fornito i valori dei parametri, fare clic su OK. I risultati vengono visualizzati nella finestra Output. La figura 2 mostra i risultati durante la restituzione di prodotti classificati da 11 a 20 quando si ordina in base all'ordine UnitPrice decrescente.

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

Figura 2: i risultati della stored procedure vengono visualizzati nella finestra di output (fare clic per visualizzare l'immagine full-size)

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 i risultati vengono ordinati in base o se è presente un indice di copertura, ma può essere più costoso in caso contrario. Per migliorare le prestazioni per query di grandi dimensioni, è consigliabile aggiungere un indice non cluster per la colonna in base al quale i risultati vengono ordinati da. Per altre informazioni, vedere Funzioni di classificazione e prestazioni in SQL Server 2005.

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

Con la stored procedure creata, il passaggio successivo consiste nel fornire un mezzo per eseguire tale stored procedure tramite l'architettura GetProductsPagedAndSorted dell'applicazione. Ciò comporta l'aggiunta di un metodo appropriato a DAL e BLL. Iniziamo aggiungendo un metodo a DAL. Aprire Typed DataSet, fare clic con il Northwind.xsd pulsante destro del mouse su ProductsTableAdaptere 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 usare, 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, nella schermata successiva, indicare che restituisce dati tabulari.

Figura 5: Indicare che la stored procedure restituisce dati tabulari

Infine, creare metodi DAL che usano rispettivamente i modelli Fill a DataTable e Return a DataTable, denominando i metodi FillPagedAndSorted e GetProductsPagedAndSorted, rispettivamente.

Figura 6: Scegliere i nomi dei metodi

Ora che abbiamo esteso il DAL, siamo pronti a 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 deve semplicemente chiamare il metodo DAL, GetProductsPagedAndSorted come segue:

<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 incrementato il DAL e BLL per includere metodi che usano la GetProductsPagedAndSorted stored procedure, tutto ciò che rimane consiste nel configurare ObjectDataSource nella SortParameter.aspx pagina per usare il nuovo metodo BLL e passare SortExpression il parametro in base alla colonna richiesta dall'utente per ordinare i risultati.

Iniziare modificando objectDataSource s SelectMethod da GetProductsPaged a GetProductsPagedAndSorted. Questa operazione può essere eseguita tramite la procedura guidata Configura origine dati, dall'Finestra Proprietà o direttamente tramite la sintassi dichiarativa. È quindi necessario specificare un valore per la proprietà ObjectDataSource.SortParameterName 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 il parametro di input dell'espressione di GetProductsPagedAndSorted ordinamento denominato sortExpression, impostare la proprietà ObjectDataSource s SortExpression su ordinamentoExpression .

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, 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 i passaggi seguenti:

1. GridView aggiorna la proprietàSortExpression al valore del SortExpression campo il cui collegamento di intestazione è stato fatto clic
2. ObjectDataSource richiama il metodo BLL, passando la proprietà GridView s GetProductsPagedAndSortedSortExpression come valore per il parametro di input del sortExpression metodo (insieme ai valori appropriati startRowIndex e maximumRows dei parametri di input)
3. BLL richiama il metodo s 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 appropriato di dati al BLL, che lo restituisce all'oggetto 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 quando ordinata in UnitPrice ordine crescente.

Figura 7: i risultati vengono ordinati da UnitPrice (Fare clic per visualizzare l'immagine a dimensioni complete)

Sebbene l'implementazione corrente possa ordinare correttamente i risultati in base al nome del prodotto, al nome della categoria, alla quantità per unità e al prezzo unitario, tentando 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é l'oggetto SortExpression GridView s SupplierName BoundField è impostato su SupplierName. Tuttavia, il nome del fornitore nella Suppliers tabella viene effettivamente chiamato CompanyName che è stato aliasato questo nome di colonna 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 l'oggetto SupplierName BoundField da SortExpression SupplierName a CompanyName (vedere la figura 9). Come illustrato nella figura 10, dopo questa modifica i risultati possono essere ordinati dal fornitore.

Figura 9: Modificare l'ordinamento SortExpression di SupplierName in CompanyName

Figura 10: i risultati possono ora essere ordinati per fornitore (fare clic per visualizzare l'immagine full-size)

Riepilogo

L'implementazione personalizzata del paging esaminata nell'esercitazione precedente richiedeva che l'ordine in base al quale i risultati devono essere ordinati in fase di progettazione. In breve, ciò significava che l'implementazione personalizzata del paging 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 creato nuovi metodi in DAL e BLL, è stato possibile implementare un controllo GridView che ha offerto l'ordinamento e il paging personalizzato configurando ObjectDataSource per passare la proprietà corrente SortExpression di GridView al BLL SelectMethod.

Programmazione felice!

Informazioni sull'autore

Scott Mitchell, autore di sette libri ASP/ASP.NET e fondatore di 4GuysFromRolla.com, ha lavorato con le tecnologie Microsoft Web dal 1998. Scott lavora come consulente indipendente, allenatore e scrittore. Il suo ultimo libro è Sams Teach Yourself ASP.NET 2,0 in 24 Ore. Può essere raggiunto a mitchell@4GuysFromRolla.com. o tramite il suo blog, che può essere trovato in http://ScottOnWriting.NET.

Grazie speciali

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

IndietroAvanti