Classificação de dados personalizados paginados (C#)

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 classificação de paginação personalizada.

Introdução

Em comparação com a paginação padrão, a paginação personalizada pode melhorar significativamente o desempenho ao navegar por dados, tornando-se a opção preferida ao paginar grandes quantidades de dados. A implementação da paginação personalizada é mais complexa do que a implementação da paginação padrão, especialmente ao adicionar ordenação ao conjunto. 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 ao tutorial De Edição e Inserção de Interfaces para 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 obter com eficiência um subconjunto específico de registros, considerando os parâmetros Índice de Linha Inicial e Máximo de Linhas. Há um punhado de 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 suma, a ROW_NUMBER() função de classificação atribui um número de linha a cada linha retornada por uma consulta 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 maneira:

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 codificadas 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 instrução CASE para fornecer expressões dinâmicas ORDER BY com base no parâmetro de entrada @sortExpressio n; veja a seção Classificação Dinâmica dos Resultados da Consulta em Instruções T-SQL CASE para mais informações.
• Crie a consulta apropriada como uma cadeia de caracteres no procedimento armazenado e 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 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 não 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 usam. 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ó 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. 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 @startRowIndex , os @maximumRowsmesmos 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 Produtos. Em exemplos anteriores, obtivemos os nomes de 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 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 de categoria ou fornecedor.

Essa consulta dinâmica é criada concatenando as partes de consulta estática e os parâmetros @sortExpression, @startRowIndex e @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 este procedimento armazenado com valores diferentes para os parâmetros @sortExpression, @startRowIndex e @maximumRows. 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 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 dos parâmetros, clique em OK. Os resultados são exibidos na janela Saída. A Figura 2 mostra os resultados ao retornar os produtos classificados nas posições de 11 a 20 ao ordenar pela UnitPrice em ordem decrescente.

Figura 1: Experimente 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 exibir a imagem em tamanho real)

Observação

Ao ordenar os resultados pela coluna especificada ORDER BY na cláusula OVER, o SQL Server deve ordenar os resultados. Essa é uma operação rápida se houver um índice clusterizado sobre as colunas 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 as funções de classificação e o desempenho no SQL Server 2005 para obter mais detalhes.

Etapa 2: Aumentando 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 para executar esse procedimento armazenado por meio de nossa arquitetura de aplicativo. Isso implica 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 Tipado, clique com o botão direito do mouse em 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 – GetProductsPagedAndSortednesse 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: Uso da procedura armazenada GetProductsPagedAndSorted

Esse procedimento armazenado retorna um conjunto de registros como seus resultados, portanto, na próxima tela, indica 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 voltar para a BLL. Abra o arquivo de ProductsBLL classe e adicione um novo método. GetProductsPagedAndSorted Esse método precisa aceitar três parâmetros de entrada sortExpression, startRowIndex e maximumRows e simplesmente chamar o método da DAL GetProductsPagedAndSorted, da seguinte maneira:

[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 aprimorado o DAL e a BLL com 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 para passar o parâmetro SortExpression com base na coluna pela qual o usuário solicitou ordenar 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 ObjectDataSourceSortParameterName. Se essa propriedade estiver definida, o ObjectDataSource tentará passar a propriedade GridView SortExpression 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 GetProductsPagedAndSorted tem o parâmetro de entrada de expressão de classificação nomeado sortExpression, defina a propriedade SortExpression da ObjectDataSource para a expressão de classificação.

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 a caixa de seleção "Habilitar Classificação" na smart tag do GridView, que define a propriedade AllowSorting do GridView para true, fazendo com que o texto do cabeçalho para cada coluna seja renderizado como um LinkButton. Quando o usuário clica em um dos botões de link do cabeçalho, ocorre um postback e as seguintes etapas ocorrem:

1. O GridView atualiza sua SortExpression propriedade para o valor do SortExpression 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 valor para o parâmetro de entrada do método sortExpression (juntamente com os valores apropriados dos parâmetros de entrada startRowIndex e maximumRows).
3. A BLL invoca o método DAL GetProductsPagedAndSorted
4. DAL executa a GetProductsPagedAndSorted procedura armazenada, passando @sortExpression como parâmetro (juntamente com os valores de parâmetro de entrada @startRowIndex e @maximumRows)
5. O procedimento armazenado retorna o subconjunto apropriado de dados para a BLL, que a retorna ao ObjectDataSource; esses dados sã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 ordenados por 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, a tentativa de solicitar 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 BoundField do SupplierName GridView está definido como SupplierName. No entanto, o nome do fornecedor na tabela Suppliers é na verdade CompanyName, pois nomeamos esta coluna com um alias: 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 de SupplierName BoundFields SortExpression 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 SortExpression do SupplierName BoundField para CompanyName

Figura 10: Os resultados agora podem ser classificados por 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 no momento do 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 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.

Divirta-se programando!

Sobre o autor

Scott Mitchell, autor de sete livros 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 alcançado em mitchell@4GuysFromRolla.com.

Agradecimentos Especiais a

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? Se assim for, deixe-me uma linha em mitchell@4GuysFromRolla.com.

AnteriorPróximo