Classificando dados paginados personalizados (C#)

por Scott Mitchell

Descarregar PDF

No tutorial anterior, aprendemos como 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 classificação de paginação personalizada.

Introdução

Em comparação com a paginação padrão, a paginação personalizada pode melhorar o desempenho na paginação através dos dados por várias vezes, fazendo dela a escolha inequívoca de implementação quando se trata de paginar grandes quantidades de dados. No entanto, a implementação da paginação personalizada envolve mais do que a implementação da paginação padrão, especialmente ao adicionar classificação à mistura. 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 colá-la entre o <asp:Content> elemento na SortParameter.aspx página. Consulte a Etapa 1 do tutorial Adicionando controles de validação ao tutorial Editando e inserindo interfaces para obter uma discussão mais detalhada sobre como replicar a funcionalidade de uma página de 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 eficientemente um subconjunto específico de registros, dados os parâmetros Start Row Index e Maximum Rows. Há um punhado de técnicas que podem ser usadas para alcançar este objetivo. No tutorial anterior, examinamos 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 é então obtido retornando uma seção específica dos resultados numerados. A consulta a seguir ilustra como usar essa técnica para retornar os produtos numerados de 11 a 20 ao classificar os resultados ordenados alfabeticamente 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 ordenada alfabeticamente, neste caso), mas a consulta precisa ser modificada para mostrar os resultados classificados por uma expressão de classificação diferente. Idealmente, a consulta acima poderia ser reescrita para usar um parâmetro na cláusula, assim OVER :

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:

• Escrever consultas codificadas 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 Utilizada para Ordenar 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 fácil de manter quanto as outras duas, pois requer que você crie uma consulta para cada expressão de classificação possível. Portanto, se mais tarde você decidir adicionar novos campos classificáveis ao GridView, você 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 sejam de cadeia de caracteres e também sofre dos mesmos problemas de manutenção que a primeira. 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 de SQL dinâmico, oferece um nível de flexibilidade que os outros dois não oferecem. Além disso, um ataque de injeção de SQL só pode ser explorado se um invasor for capaz de executar o procedimento armazenado passando os parâmetros de entrada de sua escolha. Como a 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ó existe 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. Este 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 é injetado diretamente após o ORDER BYOVER texto na cláusula, 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 faltar, os resultados sã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 as categorias associadas de cada produto e os nomes dos fornecedores usando uma subconsulta. Essa decisão foi tomada novamente 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 de inserção, atualização e exclusão associados para essas consultas. O GetProductsPagedAndSorted procedimento armazenado, no entanto, deve usar JOIN s para que os resultados sejam ordenados pelos nomes da categoria ou do fornecedor.

Essa consulta dinâmica é criada ao concatenar as partes da consulta estática e os parâmetros @sortExpression, @startRowIndex e @maximumRows. Uma vez que @startRowIndex e @maximumRows são parâmetros inteiros, eles devem ser convertidos em nvarchars para serem corretamente concatenados. Uma vez que essa consulta SQL dinâmica tenha sido construída, ela é executada via sp_executesql.

Reserve um momento para testar este procedimento armazenado com valores diferentes para o @sortExpression, @startRowIndexe @maximumRows parâmetros. No Gerenciador de Servidores, 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 pela designação da categoria, use CategoryName para o valor do parâmetro @sortExpression. Para classificar pela designação da empresa do fornecedor, use CompanyName. Depois de fornecer os valores dos parâmetros, clique em OK. Os resultados são exibidos na janela de saída. A Figura 2 mostra os resultados ao devolver produtos classificados de 11 a 20 ao encomendar por UnitPrice ordem decrescente.

Figura 1: Tente 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 de saída (Clique para visualizar a imagem em tamanho real)

Observação

Ao ordenar os resultados pela coluna especificada ORDER BY na cláusula OVER, o SQL Server tem de ordenar os resultados. Esta é uma operação rápida se houver um índice agrupado sobre a(s) coluna(s) pelos quais os resultados estão sendo ordenados ou se houver um índice de cobertura, mas pode ser mais caro caso contrário. 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 Classificação de funções e desempenho no SQL Server 2005 para obter mais detalhes.

Etapa 2: Aumentando o acesso a dados e as camadas de lógica 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. Isto implica adicionar um método adequado à 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 mouse no ProductsTableAdapter 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, neste caso. Comece indicando que você deseja que o novo método TableAdapter use um procedimento armazenado existente.

Figura 3: Escolha 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: Utilize o procedimento armazenado GetProductsPagedAndSorted

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

Figura 5: Indicar que o procedimento armazenado retorna dados tabulares

Finalmente, 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: Escolha os nomes dos métodos

Agora que alargámos o DAL, estamos prontos para recorrer à BLL. Abra o ProductsBLL arquivo de classe e adicione um novo método, GetProductsPagedAndSorted. Este método precisa aceitar três parâmetros de entrada, sortExpression, startRowIndex e maximumRows, e deve simplesmente invocar o método GetProductsPagedAndSorted do DAL, assim:

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

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

Tendo aumentado a DAL e a BLL para incluir métodos que utilizam o GetProductsPagedAndSorted procedimento armazenado, tudo o que resta é configurar o ObjectDataSource na página SortParameter.aspx para usar o novo método BLL e passar o parâmetro SortExpression com base na coluna pela qual o utilizador solicitou 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, na janela Propriedades, ou diretamente por meio da sintaxe declarativa. Em seguida, precisamos fornecer um valor para a propriedade s SortParameterNameObjectDataSource. Se essa propriedade estiver definida, o ObjectDataSource tentará passar a propriedade s SortExpression de GridView para o SelectMethod. Em particular, o ObjectDataSource procura um parâmetro de entrada cujo nome é igual ao valor da SortParameterName propriedade. Como o método BLL s GetProductsPagedAndSorted tem o parâmetro de entrada sort expression chamado sortExpression, defina a propriedade ObjectDataSource s SortExpression como sortExpression .

Depois de fazer essas duas alterações, a sintaxe declarativa de 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

Como no tutorial anterior, certifique-se de que o ObjectDataSource não inclua os parâmetros de entrada sortExpression, startRowIndex ou maximumRows em sua coleção SelectParameters.

Para habilitar a classificação no GridView, basta marcar a caixa de seleção Habilitar classificação na tag inteligente do GridView, que define a propriedade AllowSorting do GridView para true e faz com que o texto do cabeçalho de cada coluna seja exibido como um LinkButton. Quando o usuário final clica em um dos LinkButtons de cabeçalho, um postback ocorre e as seguintes etapas acontecem:

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

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

Figura 7: Os resultados são classificados pelo preço unitário (Clique para visualizar 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, a tentativa de ordenar os resultados pelo nome do fornecedor resulta em uma exceção de tempo de execução (consulte a Figura 8).

Figura 8: A tentativa de classificar os resultados pelo fornecedor resulta na seguinte exceção de tempo de execução

Esta exceção ocorre porque o BoundField do GridView está definido como SortExpression. No entanto, o nome do fornecedor na Suppliers tabela é realmente chamado CompanyName nós atribuímos a este nome de coluna o alias SupplierName. No entanto, a OVERROW_NUMBER() cláusula usada pela 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 a Figura 9). Como mostra a Figura 10, após essa alteração, os resultados podem ser classificados pelo fornecedor.

Figura 9: Alterar o SortExpression do BoundField SupplierName para CompanyName

Figura 10: Os resultados agora podem ser classificados por fornecedor (Clique para visualizar 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 deveriam ser classificados fosse especificada em tempo de design. Em resumo, isso significava que a implementação de paginação personalizada que implementamos não podia, 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 BLL, pudemos implementar um GridView que oferecia classificação e paginação personalizada configurando o ObjectDataSource para passar a propriedade atual SortExpression do GridView para o BLL SelectMethod.

Feliz Programação!

Sobre o Autor

Scott Mitchell, autor de sete livros sobre ASP/ASP.NET e fundador da 4GuysFromRolla.com, trabalha com tecnologias Web da Microsoft desde 1998. Scott trabalha como consultor, formador e escritor independente. Seu último livro é Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Ele pode ser contatado em mitchell@4GuysFromRolla.com.

Um agradecimento especial a

Esta série de tutoriais foi revisada por muitos revisores úteis. O revisor principal deste tutorial foi Carlos Santos. Interessado em rever meus próximos artigos do MSDN? Se for o caso, envie-me uma mensagem para mitchell@4GuysFromRolla.com.

AnteriorPróximo