ASP.NET 2.0 - Paginando dados com SQLServer (2000/2005)

O banco de dados tornou-se o pilar da maioria das aplicações hoje em dia. Com seu uso cada vez mais crescente. De acordo com a quantidade de dados armazenados será um grande gargalo na aplicação. Dai para termos sucesso em nossas aplicações é fundamental a premissa básica. Somente trazer do banco de dados informações que realmente serão utilizadas.

por Ramon Durães

O banco de dados tornou-se o pilar da maioria das aplicações hoje em dia. Com seu uso cada vez mais crescente. De acordo com a quantidade de dados armazenados será um grande gargalo na aplicação. Dai para termos sucesso em nossas aplicações é fundamental a premissa básica. Somente trazer do banco de dados informações que realmente serão utilizadas.

Por mais simples que pareça a afirmação anterior é ainda hoje um dos erros mais comuns encontrados quando estou fazendo revisão de arquitetura nas empresas que visito. Geralmente encontro o famoso "Select * from Tabela", trazendo sempre tudo quando a necessidade era apenas duas colunas, e as vezes até uma linha.

Assim que você pensar em banco de dados, defina com antecedência que tipo de informação precisa e em qual quantidade. Feito isso prepare os filtros e faça suas consultas. Outro dia um cliente me questionou que ele tem uma base de mais de um milhão de registros e queria fazer essa recuperação de todas as linhas em uma tela Web, incluindo fazer a rolagem na tela de todos os registros, porque em Windows ele fazia isso. Esse tipo de questionamento é um "defeito de fabricação". Pois começou errado na aplicação Windows e ele quer levar pra Web. Você não precisa nem fazer cálculos matemáticos avançados para provar que será inviável uma pessoa analisar mais de um milhão de registros. Para casos como esse e muitos outros que vocês já estão acostumados é fundamental aplicar o máximo de filtros e fazer com que a sua consulta traga realmente os dados que importam para a avaliação conforme a regra de negocio do cliente.

Com a versão 2005 do banco de dados SqlServer passamos a contar com um recurso poderoso que é a numeração de linha "ROW_NUMBER ()". Com esse recurso você pode solicitar apenas um conjunto x de registros. Com isso você pode configurar seu GridView em conjunto com um ObjectDataSource para trazer do banco de dados apenas a pagina que ele realmente vai mostrar. Para que você possa testar esse mesmo exemplo em SQLServer 2000 estaremos utilizando uma procedure alternativa criada pelo Greg Hamilton de forma que você utilize como referencia caso ainda possua o SQL 2000. Confira ambas as procedures na Listagem 01.

Procedure SqlServer 2000/2005Para uso no SQLServer 2005

USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[up_PaginaProdutos2005]
(
@startRowIndex int,
@maximumRows int,
@rowsCount int output
)

AS
Begin

SET @startRowIndex=@startRowIndex+1
--- Output
set @rowsCount=(SELECT count(ProductID) FROM Products);
WITH ProductsPaging AS
(
SELECT ProductID, ProductName,UnitPrice,
ROW_NUMBER() OVER (order by ProductID)as RowNumber
FROM Products
)

SELECT ProductID,ProductName,UnitPrice
FROM ProductsPaging
WHERE RowNumber between @startRowIndex and @startRowIndex+@maximumRows ;
End

Para uso no SqlServer 2000 / 2005

USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[up_PaginaProdutos2000]
(
@startRowIndex int,
@maximumRows int,
@rowsCount int output
)

AS

Begin
DECLARE @first_id int
SET @startRowIndex=@startRowIndex+1
--- Obtem total registros
set @rowCount=(SELECT count(*) FROM Products)
--- Obtem o indice com ID
SET ROWCOUNT @startRowIndex
SELECT @first_id = ProductID FROM Products ORDER BY ProductID

SET ROWCOUNT @maximumRows

--- Executa select de retorno
SELECT ProductID,ProductName,UnitPrice from Products
WHERE ProductID >= @first_id
ORDER BY ProductID

SET ROWCOUNT 0
end

Listagem 01 - Procedures para paginação de registros.

Conforme a Listagem 01 você tem a opção de procedure para SqlServer 2000 e SqlServer 2005. Mantivemos o padrão de assinatura da procedure como (@startRowIndex int,@maximumRows int,@NumRows int output). Conforme Tabela 01.

Parâmetro	Comentário
@startRowIndex	Indica em qual registro começa a paginação.
@maximumRows	Indica quantidade de registros por pagina.
@rowsCount	Retorna quantidade total de registros.

Tabela 01 - Procedure para paginação de registros.

Com a procedure em mãos o que você precisa agora é só testar conforme a Listagem 02. Basta executar a query após criar a procedure.

TestandoDECLARE @RC int
DECLARE @startRowIndex int
DECLARE @maximumRows int
DECLARE @rowsCount int

EXECUTE @RC = [Northwind].[dbo].[up_PaginaProdutos2000]
1,10,@rowsCount OUTPUT

Select @rowsCount "Retorno @rowsCount"

Listagem 02 Testando Procedure para paginação de registros.

Após testar e comprovar o funcionando da procedure é chegado o momento de realizar sua implementação. Você pode escrever o código diretamente usando ADO.NET para chamar a procedure retornando os dados em uma coleção para que possamos carregar no GridView, ou criar todo acesso a dados utilizando Typed DataSet e TableAdapter que se encarregara de gerar todo código de acesso a dados. Nesse artigo iremos utilizar o TableAdapter conforme Figura 01. Não será nosso objetivo nesse artigo abordar o TableAdapter, caso não conheça você pode optar por utilizar a codificação tradicional do ADO.NET.


Figura 01 - TableAdapter e Typed DataSet

Após definir qual será sua implementação crie uma nova pagina e adicione um GridView conforme a Listagem 03 abaixo:

GridView
<asp:GridView ID="GridView1" runat="server" AllowPaging=True DataSourceID="ObjectDataSoruce1" AutoGenerateColumns="False" DataKeyNames="ProductID">

<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False"

ReadOnly="True" SortExpression="ProductID" />

<asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />

<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />

</Columns>

</asp:GridView>

<asp:ObjectDataSource ID="ObjectDataSoruce1" runat=server SelectMethod="ConsultaProdutos" TypeName="PaginarProdutos"

SelectCountMethod="RetornaTotalProdutos" EnablePaging=true>

</asp:ObjectDataSource>

Listagem 03 - GridView e ObjectDataSource

Conforme Listagem 03. Preste bastante atenção nos itens abaixo:

GridView:
- AllowPaging=True
- PageSize=10

ObjectDataSource:
- SelectMethod="ConsultaProdutos"
- TypeName="PaginarProdutos"
- SelectCountMethod="RetornaTotalProdutos"
- EnablePaging=true

Eles são fundamentais para vinculação das informações no GridView. Onde é habilitado a paginação e definido o número de registros por pagina. Depois no ObjectDataSource definimos o método responsável pela consulta, o nome da classe e o método responsável por obter a quantidade de registros, assim como habilitamos a paginação no DataSource. Na Listagem 04 você vai conferir a classe PaginarProdutos, onde criamos o método "ConsultaProdutos" e "RetornaTotalProdutos".

PaginarProdutos.cs
public class PaginarProdutos

{
private int _TotalProdutos=0;
public int RetornaTotalProdutos()

{
return _TotalProdutos;
}

public dsProdutos.up_PaginaProdutos2005DataTable ConsultaProdutos (int startRowIndex,int maximumRows)

{
Nullable<int> TotalProdutos = 0;
dsProdutos.up_PaginaProdutos2005DataTable Produtos = new dsProdutos.up_PaginaProdutos2005DataTable();

dsProdutosTableAdapters.up_PaginaProdutos2005TableAdapter Adapter = new dsProdutosTableAdapters.up_PaginaProdutos2005TableAdapter();

Adapter.Fill(Produtos,startRowIndex, maximumRows, ref TotalProdutos);
this._TotalProdutos = (int)TotalProdutos;
return Produtos;
}
}

Listagem 04 - GridView e ObjectDataSource

Conforme Listagem 04 o método ConsultaProdutos é responsável por obter os dados solicitados referentes ao índice atual e quantidade de registros. Toda implementação desse método foi feita utilizando o TableAdapter gerado pelo Visual Studio. Você pode substituir o código pelo código tradicional do ADO.NET.

O resultado final da aplicação rodando é um GridView paginando os dados, porém só retorna do servidor a quantidade de linhas e colunas que foram especificadas. Conforme Figura 02.


Figura 02 - GridView em ação

Enfim a parte mais importante que você conferiu nesse artigo é o conceito de só trazer as informações que realmente serão úteis, pois qualquer coisa inútil terá um custo alto para sua aplicação. As procedures apresentadas podem ser melhoradas a seu critério.

O que você gostaria saber?
Aguardo seu comentário! Ele é muito importante. Participe!!! Até próximo artigo!