Classificação de dados personalizados paginados (VB)

por Scott Mitchell

Baixar PDF

No tutorial anterior, aprendemos a implementar a paginação personalizada ao apresentar dados em uma página da Web. Neste tutorial, vemos como estender o exemplo anterior para incluir suporte para classificar a paginação personalizada.

Introdução

Em comparação com a paginação padrão, a paginação personalizada pode melhorar o desempenho da paginação por meio de dados em várias ordens de magnitude, tornando a paginação personalizada a opção de implementação de paginação de fato ao paginar grandes quantidades de dados. A implementação da paginação personalizada é mais envolvida do que implementar a paginação padrão, especialmente ao adicionar classificação à combinação. Neste tutorial, estenderemos o exemplo do anterior para incluir suporte para classificação e paginação personalizada.

Observação

Como este tutorial se baseia no anterior, antes de começar, reserve um momento para copiar a sintaxe declarativa dentro do <asp:Content> elemento da página da Web do tutorial anterior (EfficientPaging.aspx) e cole-a entre o <asp:Content> elemento na SortParameter.aspx página. Consulte a Etapa 1 do tutorial Adicionando controles de validação às interfaces de edição e inserção para obter uma discussão mais detalhada sobre como replicar a funcionalidade de uma página ASP.NET para outra.

Etapa 1: Reexaminando a técnica de paginação personalizada

Para que a paginação personalizada funcione corretamente, devemos implementar alguma técnica que possa capturar com eficiência um subconjunto específico de registros, considerando os parâmetros Índice de Linha Inicial e Máximo de Linhas. Há algumas técnicas que podem ser usadas para atingir esse objetivo. No tutorial anterior, analisamos como fazer isso usando a nova ROW_NUMBER() função de classificação do Microsoft SQL Server 2005. Em resumo, a ROW_NUMBER() função de classificação atribui um número de linha a cada linha retornada por uma consulta que é classificada por uma ordem de classificação especificada. O subconjunto apropriado de registros é obtido retornando uma seção específica dos resultados numerados. A consulta a seguir ilustra como usar essa técnica para retornar esses produtos numerados de 11 a 20 ao classificar os resultados ordenados em ordem alfabética pelo 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

Essa técnica funciona bem para paginação usando uma ordem de classificação específica (ProductName classificada em ordem alfabética, nesse caso), mas a consulta precisa ser modificada para mostrar os resultados classificados por uma expressão de classificação diferente. O ideal é que a consulta acima possa ser reescrita para usar um parâmetro na OVER cláusula , da seguinte forma:

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

Infelizmente, cláusulas parametrizadas ORDER BY não são permitidas. Em vez disso, devemos criar um procedimento armazenado que aceite um @sortExpression parâmetro de entrada, mas use uma das seguintes soluções alternativas:

• Escreva consultas embutidas em código para cada uma das expressões de classificação que podem ser usadas; em seguida, use IF/ELSE instruções T-SQL para determinar qual consulta executar.
• Use uma CASE instrução para fornecer expressões dinâmicas ORDER BY com base no @sortExpressio parâmetro de entrada n; consulte a seção Usado para classificar dinamicamente os resultados da consulta em instruções T-SQL CASE para obter mais informações.
• Crie a consulta apropriada como uma cadeia de caracteres no procedimento armazenado e, em seguida, use o procedimento armazenado do sp_executesql sistema para executar a consulta dinâmica.

Cada uma dessas soluções alternativas tem algumas desvantagens. A primeira opção não é tão mantenedível quanto as outras duas, pois exige que você crie uma consulta para cada expressão de classificação possível. Portanto, se posteriormente você decidir adicionar novos campos classificáveis ao GridView, também precisará voltar e atualizar o procedimento armazenado. A segunda abordagem tem algumas sutilezas que introduzem preocupações de desempenho ao classificar por colunas de banco de dados que não são de cadeia de caracteres e também sofrem dos mesmos problemas de manutenção que o primeiro. E a terceira opção, que usa SQL dinâmico, introduz o risco de um ataque de injeção de SQL se um invasor for capaz de executar o procedimento armazenado passando os valores de parâmetro de entrada de sua escolha.

Embora nenhuma dessas abordagens seja perfeita, acho que a terceira opção é a melhor das três. Com o uso do SQL dinâmico, ele oferece um nível de flexibilidade que os outros dois não têm. Além disso, um ataque de injeção de SQL só poderá ser explorado se um invasor for capaz de executar o procedimento armazenado passando os parâmetros de entrada de sua escolha. Como o DAL usa consultas parametrizadas, ADO.NET protegerá os parâmetros enviados ao banco de dados por meio da arquitetura, o que significa que a vulnerabilidade de ataque de injeção de SQL só existirá se o invasor puder executar diretamente o procedimento armazenado.

Para implementar essa funcionalidade, crie um novo procedimento armazenado no banco de dados Northwind chamado GetProductsPagedAndSorted. Esse procedimento armazenado deve aceitar três parâmetros de entrada: @sortExpression, um parâmetro de entrada do tipo nvarchar(100) que especifica como os resultados devem ser classificados e são injetados diretamente após o ORDER BY texto na OVER cláusula; e e @startRowIndex@maximumRows, os mesmos dois parâmetros de entrada inteiros do GetProductsPaged procedimento armazenado examinado no tutorial anterior. Crie o GetProductsPagedAndSorted procedimento armazenado usando o seguinte script:

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

O procedimento armazenado começa garantindo que um valor para o @sortExpression parâmetro tenha sido especificado. Se ele estiver ausente, os resultados serão classificados por ProductID. Em seguida, a consulta SQL dinâmica é construída. Observe que a consulta SQL dinâmica aqui difere ligeiramente de nossas consultas anteriores usadas para recuperar todas as linhas da tabela Products. Em exemplos anteriores, obtivemos os nomes das categorias e fornecedores associados de cada produto usando uma subconsulta. Essa decisão foi tomada no tutorial Criando uma camada de acesso a dados e foi feita em vez de usar JOIN s porque o TableAdapter não pode criar automaticamente os métodos associados de inserção, atualização e exclusão para essas consultas. No GetProductsPagedAndSorted entanto, o procedimento armazenado deve usar JOIN s para que os resultados sejam ordenados pelos nomes de categoria ou fornecedor.

Essa consulta dinâmica é criada concatenando as partes de consulta estática e os @sortExpressionparâmetros , @startRowIndexe @maximumRows . Como @startRowIndex e @maximumRows são parâmetros inteiros, eles devem ser convertidos em nvarchars para serem concatenados corretamente. Depois que essa consulta SQL dinâmica tiver sido construída, ela será executada por meio de sp_executesql.

Reserve um momento para testar esse procedimento armazenado com valores diferentes para os @sortExpressionparâmetros , @startRowIndexe @maximumRows . Na Explorer servidor, clique com o botão direito do mouse no nome do procedimento armazenado e escolha Executar. Isso abrirá a caixa de diálogo Executar Procedimento Armazenado na qual você pode inserir os parâmetros de entrada (consulte a Figura 1). Para classificar os resultados pelo nome da categoria, use CategoryName para o valor do @sortExpression parâmetro; para classificar pelo nome da empresa do fornecedor, use CompanyName. Depois de fornecer os valores de parâmetros, clique em OK. Os resultados são exibidos na janela Saída. A Figura 2 mostra os resultados ao retornar produtos classificados de 11 a 20 ao ordenar pela UnitPrice ordem decrescente.

Figura 1: Experimentar valores diferentes para os três parâmetros de entrada do procedimento armazenado

Figura 2: Os resultados do procedimento armazenado são mostrados na janela saída (clique para exibir a imagem em tamanho real)

Observação

Ao classificar os resultados pela coluna especificada ORDER BY na OVER cláusula , SQL Server deve classificar os resultados. Essa será uma operação rápida se houver um índice clusterizado sobre as colunas pelas quais os resultados estão sendo ordenados ou se houver um índice de cobertura, mas pode ser mais caro de outra forma. Para melhorar o desempenho de consultas suficientemente grandes, considere adicionar um índice não clusterizado para a coluna pela qual os resultados são ordenados. Consulte Funções de classificação e desempenho no SQL Server 2005 para obter mais detalhes.

Etapa 2: Aumentar o acesso a dados e as camadas lógicas de negócios

Com o GetProductsPagedAndSorted procedimento armazenado criado, nossa próxima etapa é fornecer um meio de executar esse procedimento armazenado por meio de nossa arquitetura de aplicativo. Isso envolve a adição de um método apropriado ao DAL e à BLL. Vamos começar adicionando um método ao DAL. Abra o Northwind.xsd Conjunto de Dados Digitado, clique com o botão direito do ProductsTableAdaptermouse no e escolha a opção Adicionar Consulta no menu de contexto. Como fizemos no tutorial anterior, queremos configurar esse novo método DAL para usar um procedimento armazenado existente – GetProductsPagedAndSorted, nesse caso. Comece indicando que você deseja que o novo método TableAdapter use um procedimento armazenado existente.

Figura 3: Optar por usar um procedimento armazenado existente

Para especificar o procedimento armazenado a ser usado, selecione o GetProductsPagedAndSorted procedimento armazenado na lista suspensa na próxima tela.

Figura 4: Usar o procedimento armazenado GetProductsPagedAndSorted

Esse procedimento armazenado retorna um conjunto de registros como resultados para que, na próxima tela, indique que ele retorna dados tabulares.

Figura 5: Indicar que o procedimento armazenado retorna dados tabulares

Por fim, crie métodos DAL que usam os padrões Fill a DataTable e Return a DataTable, nomeando os métodos FillPagedAndSorted e GetProductsPagedAndSorted, respectivamente.

Figura 6: Escolher os nomes dos métodos

Agora que estendemos o DAL, estamos prontos para recorrer à BLL. Abra o arquivo de ProductsBLL classe e adicione um novo método, GetProductsPagedAndSorted. Esse método precisa aceitar três parâmetros sortExpressionde entrada , startRowIndexe maximumRows e deve simplesmente chamar para baixo no método da DAL s GetProductsPagedAndSorted , da seguinte forma:

<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

Etapa 3: Configurando o ObjectDataSource para passar o parâmetro SortExpression

Tendo aumentado o DAL e a BLL para incluir métodos que utilizam o GetProductsPagedAndSorted procedimento armazenado, tudo o que resta é configurar o ObjectDataSource na SortParameter.aspx página para usar o novo método BLL e passar o SortExpression parâmetro com base na coluna que o usuário solicitou para classificar os resultados.

Comece alterando o ObjectDataSource s SelectMethod de GetProductsPaged para GetProductsPagedAndSorted. Isso pode ser feito por meio do assistente Configurar Fonte de Dados, do janela Propriedades ou diretamente por meio da sintaxe declarativa. Em seguida, precisamos fornecer um valor para a propriedade objectDataSource sSortParameterName. Se essa propriedade for definida, o ObjectDataSource tentará passar a propriedade gridView para SortExpression o SelectMethod. Em particular, o ObjectDataSource procura um parâmetro de entrada cujo nome é igual ao valor da SortParameterName propriedade. Como o método S GetProductsPagedAndSorted da BLL tem o parâmetro de entrada de expressão de classificação chamado sortExpression, defina a propriedade s SortExpression ObjectDataSource como sortExpression .

Depois de fazer essas duas alterações, a sintaxe declarativa do ObjectDataSource deve ser semelhante à seguinte:

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

Observação

Assim como no tutorial anterior, verifique se o ObjectDataSource não inclui os parâmetros de entrada sortExpression, startRowIndex ou maximumRows em sua coleção SelectParameters.

Para habilitar a classificação no GridView, basta marcar caixa de seleção Habilitar Classificação na marca inteligente gridView, que define a propriedade true GridView como e fazendo com que o texto do AllowSorting cabeçalho para cada coluna seja renderizado como um LinkButton. Quando o usuário final clica em um dos LinkButtons de cabeçalho, um postback é seguido e as seguintes etapas ocorrem:

1. O GridView atualiza sua SortExpression propriedade para o valor do SortExpression do campo cujo link de cabeçalho foi clicado
2. O ObjectDataSource invoca o método S da GetProductsPagedAndSorted BLL, passando a propriedade GridView SortExpression como o valor para o parâmetro de entrada do sortExpression método (juntamente com os valores de parâmetro de entrada e maximumRows apropriadosstartRowIndex)
3. A BLL invoca o método DAL s GetProductsPagedAndSorted
4. O DAL executa o GetProductsPagedAndSorted procedimento armazenado, passando o @sortExpression parâmetro (juntamente com os valores de @startRowIndex parâmetro de entrada e @maximumRows )
5. O procedimento armazenado retorna o subconjunto apropriado de dados para a BLL, que os retorna ao ObjectDataSource; esses dados são então associados ao GridView, renderizados em HTML e enviados para o usuário final

A Figura 7 mostra a primeira página de resultados quando classificada pelo UnitPrice em ordem crescente.

Figura 7: Os resultados são classificados pelo UnitPrice (clique para exibir a imagem em tamanho real)

Embora a implementação atual possa classificar corretamente os resultados por nome do produto, nome da categoria, quantidade por unidade e preço unitário, tentar ordenar os resultados pelo nome do fornecedor resulta em uma exceção de runtime (consulte Figura 8).

Figura 8: tentando classificar os resultados pelos resultados do fornecedor na seguinte exceção de runtime

Essa exceção ocorre porque o SortExpression do BoundField do SupplierName GridView está definido como SupplierName. No entanto, o nome do fornecedor na Suppliers tabela é, na verdade, chamado CompanyName de nós fomos aliased esse nome de coluna como SupplierName. No entanto, a OVER cláusula usada pela ROW_NUMBER() função não pode usar o alias e deve usar o nome da coluna real. Portanto, altere os SupplierName BoundFields SortExpression de SupplierName para CompanyName (consulte Figura 9). Como mostra a Figura 10, após essa alteração, os resultados podem ser classificados pelo fornecedor.

Figura 9: Alterar a SortExpression de SupplierName BoundField para CompanyName

Figura 10: Os resultados agora podem ser classificados pelo fornecedor (clique para exibir a imagem em tamanho real)

Resumo

A implementação de paginação personalizada que examinamos no tutorial anterior exigia que a ordem pela qual os resultados fossem classificados fosse especificada em tempo de design. Em suma, isso significava que a implementação de paginação personalizada que implementamos não poderia, ao mesmo tempo, fornecer recursos de classificação. Neste tutorial, superamos essa limitação estendendo o procedimento armazenado do primeiro para incluir um @sortExpression parâmetro de entrada pelo qual os resultados poderiam ser classificados.

Depois de criar esse procedimento armazenado e criar novos métodos no DAL e na BLL, conseguimos implementar um GridView que oferecia classificação e paginação personalizada configurando o ObjectDataSource para passar a propriedade atual SortExpression do GridView para a BLL SelectMethod.

Programação feliz!

Sobre o autor

Scott Mitchell, autor de sete livros do ASP/ASP.NET e fundador da 4GuysFromRolla.com, trabalha com tecnologias da Microsoft Web desde 1998. Scott trabalha como consultor independente, treinador e escritor. Seu último livro é Sams Teach Yourself ASP.NET 2.0 em 24 Horas. Ele pode ser contatado em mitchell@4GuysFromRolla.com. ou através de seu blog, que pode ser encontrado em http://ScottOnWriting.NET.

Agradecimentos Especiais

Esta série de tutoriais foi revisada por muitos revisores úteis. O revisor principal deste tutorial foi Carlos Santos. Interessado em revisar meus próximos artigos do MSDN? Nesse caso, solte-me uma linha em mitchell@4GuysFromRolla.com.

AnteriorAvançar