.NET - Melhorando o desempenho da Paginação de dados - Usando Stored Procedures

Neste artigo eu mostro uma alternativa para melhorar o desempenho da paginação de dados quando temos que navegar em uma grande quantidade de dados usando Stored Procedures.

Quando temos que percorrer uma grande quantidade de informações usando ADO .NET geralmente usamos um DataAdapter e nos deparamos com as limitações de desempenho do método Fill().

Para contornar este problema vamos criar uma stored procedure para realizar a paginação de dados.

Os recursos usados no exemplo deste artigo são:

  1. Visual C# 2010 Express Edition
  2. SQL Server 2008 Express Edition
  3. Banco de dados Northwind.mdf

Criando o projeto e a stored procedure

Vamos criar um novo projeto no Visual C# 2010 Express Edition acionando o menu File -> New Project e a seguir escolhendo o template Windows Forms Application;

Informe o nome PaginacaoDados_StoredProcedures e clique no botão OK;

A seguir abra a janela Database Explorer e clique no ícone Data Connections com o botão direito do mouse e selecione Add Connections;

Na janela Add Connection escolha o Data Source - Microsoft SQL Server Database File (SqlClient) - e informe o local do banco de dados Northwind.mdf;

Obs: Eu estou anexando o banco de dados Northwind.mdf no meu SQL Server.

Após clicar no botão OK você deverá ter acesso a todos os objetos do banco de dados Northwind.mdf no SQL Server.

Vamos criar um stored procedure que irá realizar a paginação na tabela Products.

Para isso clique com o botão direito do mouse sobre o item Stored Procedures e clique em Add New Stored Procedure;

A seguir vamos criar a Stored Procedure Paginacao_Produtos conforme o código abaixo:

CREATE PROCEDURE Paginacao_Produtos
(
    @PaginaComando nvarchar(10),
    @PrimeiroProdutoID int = null,
    @UltimoProdutoID int = null,
    @PaginaConta int output,
    @PaginaAtual int output
)
AS
    SET NOCOUNT ON

    SELECT @PaginaConta = CEILING(COUNT(*)/10) FROM Products

    -- primeira pagina é requerida OU pagina anterior quando a página atual já a primeirao
    IF @PaginaComando = 'PRIMEIRO' OR (@PaginaComando = 'ANTERIOR' AND
        @PaginaAtual <= 1)
    BEGIN
        SELECT TOP 10 *
        FROM Products
        ORDER BY ProductID

        SET @PaginaAtual = 1

        RETURN 0
    END

    -- a ultima pagina é requisita ou a proxima pagina quando a pagina atual for a ultima pagina
    IF @PaginaComando = 'ULTIMO' OR (@PaginaComando = 'PROXIMO' AND
        @PaginaAtual >= @PaginaConta)
    BEGIN
        SELECT a.*
        FROM
            (SELECT TOP 10 *
            FROM Products
            ORDER BY ProductID DESC) a
        ORDER BY ProductID

        SET @PaginaAtual = @PaginaConta

        RETURN 0
    END

    IF @PaginaComando = 'PROXIMO'
    BEGIN
        SELECT TOP 10 *
        FROM Products
        WHERE ProductID > @UltimoProdutoID
        ORDER BY ProductID

        SET @PaginaAtual = @PaginaAtual+1

        RETURN 0
    END

    IF @PaginaComando = 'ANTERIOR'
    BEGIN
        SELECT a.*
        FROM (
            SELECT TOP 10 *
            FROM Products
            WHERE ProductID < @PrimeiroProdutoID
            ORDER BY ProductID DESC) a
        ORDER BY ProductID

        SET @PaginaAtual = @PaginaAtual-1

        RETURN 0
    END

    RETURN 1

Nossa store Procedure Paginacao_Produtos retornará os 10 registros da tabela Products do banco de dados Northwind.mdf que correspondem às páginas primeira, última, próxima e anterior.

Esta stored procedure possui os seguintes argumentos:

As instruções SQL usadas sem bem simples. Todas elas usam um comando SELECT para obter os dados da tabela Products ordenados pelo campo ProductID

No formulário form1.cs do projeto Windows Forms inclua os seguintes controles:

Disponha os controles conforme o leiaute da figura abaixo

Agora vamos ao código onde iremos usar a nossa stored procedure para navegar pelos registros da tabela Products.

Declare os seguintes namespaces no início do formulário form1.cs:

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

A seguir vamos declarar as variáveis usadas no projeto logo após a declaração do formulário form1.cs:

private SqlDataAdapter da;
private DataTable dt;

private int paginaAtual;
private int primeiroProdutoId;
private int ultimoProdutoId;
private int _paginaConta;

No evento Load do formulário form1.cs vamos definir o código que se conecta ao banco de dados e define a stored procedure e seus parâmetros:

 private void Form1_Load(object sender, EventArgs e)
        {
            string sqlConnectString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\dados\Northwind.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True";

            string sqlSelect = @"SELECT ProductID, ProductName, UnitPrice FROM Products";

            try
            {
                // pega o schema
                da = new SqlDataAdapter(sqlSelect, sqlConnectString);
                dt = new DataTable("Produtos");
                da.FillSchema(dt, SchemaType.Source);

                // Cria e configura a stored procedure de paginação
                SqlConnection connection = new SqlConnection(sqlConnectString);
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "Paginacao_Produtos";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@PaginaComando", SqlDbType.NVarChar, 10);
                command.Parameters.Add("@PrimeiroProdutoID", SqlDbType.Int);
                command.Parameters.Add("@UltimoProdutoID", SqlDbType.Int);
                command.Parameters.Add("@PaginaConta", SqlDbType.Int).Direction = ParameterDirection.Output;
                command.Parameters.Add("@PaginaAtual", SqlDbType.Int).Direction = ParameterDirection.InputOutput;
                da = new SqlDataAdapter(command);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro : " + ex.Message);
            }
        }

Para cada evento Click de cada um dos botões de comandos temos o código que chama a rotina getDados() que receberá um valor para o parãmetro @PaginaComando;

private void btnPrimeiro_Click(object sender, System.EventArgs e)
        {
            lstDados.Items.Clear();
            GetDados("PRIMEIRO");
        }

        private void btnProximo_Click(object sender, System.EventArgs e)
        {
            lstDados.Items.Clear();
            GetDados("PROXIMO");
        }

        private void btnUltimo_Click(object sender, System.EventArgs e)
        {
            lstDados.Items.Clear();
            GetDados("ULTIMO");
        }

        private void btnAnterior_Click(object sender, System.EventArgs e)
        {
            lstDados.Items.Clear();
            GetDados("ANTERIOR");
        }    

O código da rotina GetDados() é visto abaixo:

public void GetDados(string _paginaComando)
        {
            da.SelectCommand.Parameters["@PaginaComando"].Value = _paginaComando;
            da.SelectCommand.Parameters["@PrimeiroProdutoID"].Value = primeiroProdutoId;
            da.SelectCommand.Parameters["@UltimoProdutoID"].Value = ultimoProdutoId;
            da.SelectCommand.Parameters["@PaginaAtual"].Value = paginaAtual;

            dt.Clear();
            da.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                primeiroProdutoId = (int)dt.Rows[0]["ProductID"];
                ultimoProdutoId = (int)dt.Rows[dt.Rows.Count - 1]["ProductID"];
            }
            else
            {
                primeiroProdutoId = ultimoProdutoId = -1;
            }

            _paginaConta = (int)da.SelectCommand.Parameters["@PaginaConta"].Value;
            paginaAtual = (int)da.SelectCommand.Parameters["@PaginaAtual"].Value;

            lstDados.Items.Add("\nProdutos: Pagina " + paginaAtual + " de " + _paginaConta);
            lstDados.Items.Add("-------------------------------------------------------------------------------------------------");
            lstDados.Items.Add("Linha\tCodigo\tProduto\t\tPreço");
            lstDados.Items.Add("-------------------------------------------------------------------------------------------------");
            lstDados.Items.Add(" ");
            exibicaoDaLinha(1, dt.Rows[0]);
            exibicaoDaLinha(2, dt.Rows[1]);
            lstDados.Items.Add("...");
            lstDados.Items.Add("...");
            exibicaoDaLinha(9, dt.Rows[8]);
            exibicaoDaLinha(10, dt.Rows[9]);
        }

Para concluir temos a rotina exibicaoDaLinha() cujo código é visto a seguir:

 public void exibicaoDaLinha(int n, DataRow row)
{
            lstDados.Items.Add(n.ToString() + "\t" + row["ProductID"] + "\t" + row["ProductName"] + "\t\t" + row["UnitPrice"]);
 }   

Executando a aplicação e percorrendo os registros pelo acionamento dos botões teremos o seguinte resultado:

PRIMEIRO PRÓXIMO
ULTIMO ANTERIOR

Você deve avaliar se a solução apresentada se ajusta ao seu projeto.

Pegue o projeto completo aqui: PaginacaoDados_StoredProcedures.zip

Efs 2:5 estando nós ainda mortos em nossos delitos, nos vivificou juntamente com Cristo (pela graça sois salvos),

Efs 2:6 e nos ressuscitou juntamente com ele, e com ele nos fez sentar nas regiões celestes em Cristo Jesus,

Efs 2:7 para mostrar nos séculos vindouros a suprema riqueza da sua graça, pela sua bondade para conosco em Cristo Jesus.

Referências:

José Carlos Macoratti