Ordinamento dei dati con suddivisione in pagine personalizzata (C#)
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 dinamicheORDER 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 ProductID
a . 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 @sortExpression
parametri , @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
.
Testare questa stored procedure con valori diversi per i @sortExpression
parametri , @startRowIndex
e @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 ProductsTableAdapter
mouse 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, GetProductsPagedAndSorted
in 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 sortExpression
input , startRowIndex
e 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à SelectMethod
gridView 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:
- GridView aggiorna la relativa
SortExpression
proprietà al valore delSortExpression
campo il cui collegamento di intestazione è stato selezionato - ObjectDataSource richiama il metodo BLL
GetProductsPagedAndSorted
, passando la proprietà gridView sSortExpression
come valore per il parametro di input del metodo (insieme ai valori appropriatistartRowIndex
emaximumRows
dei parametri disortExpression
input) - Il BLL richiama il metodo DAL
GetProductsPagedAndSorted
- DAL esegue la
GetProductsPagedAndSorted
stored procedure, passando il@sortExpression
parametro (insieme ai valori dei@startRowIndex
parametri di input e@maximumRows
) - 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.