Conhecendo os métodos ExecuteNonQuery, ExecuteReader e ExecuteScalar da ADO.NET

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (8)  (0)

Veja nesse artigo como manipular bancos de dados utilizando ADO.NET framework, utilizando os métodos ExecuteNonQuery, ExecuteReader e ExecuteScalar.

Introdução

O ADO.NET Framework é uma biblioteca de classes rica e extremamente poderosa, construída para realizar o acesso e a manipulação de dados em aplicações .NET de maneira simples e elegante.

A arquitetura ADO.NET é baseada em provedores de dados. Cada SGBD (Sistema Gerenciador de Banco de Dados) possui uma implementação própria de provedores de dados que podem realizar conexões com o banco, atualizar dados e realizar consultas. Atualmente, os principais SGBD''s do mercado possuem implementações de provedores de dados para a plataforma .NET, como SQL Server, Oracle, MySQL, Firebird, DB2, entre outros.

Dentro da arquitetura do ADO.NET framework, contamos com a classe Command, cujos objetos herdados representam instruções SQL ou Stored Procedures a serem executadas em um banco de dados.

Neste artigo iremos utilizar o provedor nativo da plataforma .NET para o SQL Server, que é encontrado no namespace System.Data.SqlClient.

Arquitetura ADO.NET

Arquitetura ADO.NET Framework

Figura 1: Arquitetura ADO.NET Framework

Observação: Para a criação deste artigo foi utilizado o Visual Studio 2012 Express for WEB e o SQL Server 2012 Express, acessando uma instância LocalDB.

As ferramentas citadas podem ser baixadas gratuitamente no site da Microsoft no endereço a seguir: http://www.microsoft.com/visualstudio/ptb/downloads.

Criação do Projeto

Utilizando o SQL Server (ou o banco de dados de sua preferência), crie um novo banco com o nome de sistemaweb.

Crie uma nova tabela de acordo com o diagrama abaixo, com o nome de contatos.

Diagrama da tabela Contatos no SQL Server 2012

Figura 2: Diagrama da tabela Contatos no SQL Server 2012

Utilizando o Visual Studio, crie uma nova aplicação do tipo ASP.NET Empty Web Application e no campo Name digite ContatosWEB, conforme a imagem abaixo:

Criação de um novo projeto ASP.NET em branco

Figura 3: Criação de um novo projeto ASP.NET em branco

Adicione um novo WebForm ao Projeto com o nome Contatos.

Adicione os seguintes controles visuais ao WebForm:

  • 08 controles do tipo TextBox (Nome, Endereço, Cidade, Telefone Fixo, Telefone Celular, Data de Nascimento, Observações, Pesquisa);
  • 01 controle do tipo DropDownList (Estado);
  • 04 controles do tipo Button (Inserir, Gravar, Pesquisar, Quantidade);
  • 01 controle do tipo Label (Quantidade de Registros)
  • 01 controle do tipo GridView;

Observação: utilize a propriedade Items do DropDownList para adicionar os Estados Brasileiros. Na tabela Contatos a coluna estado foi criada com o tipo nvarchar(20) para persistir o nome do Estado por extenso.

WebForm Contatos

Figura 4: WebForm Contatos

Altere a propriedade Name dos controles da seguinte forma:

  • tbxNome
  • tbxEndereco
  • tbxCidade
  • ddlEstado
  • tbxTelefoneFixo
  • tbxTelefoneCelular
  • tbxDataNascimento
  • tbxObservacoes
  • tbxPesquisar
  • btnInserir
  • btnGravar
  • btnQuantidade
  • lblQuantidade
  • grdContatos (gridview1)

Observações: altere a propriedade TextMode do controle tbxDataNascimento para Date.

Altere a propriedade TextMode do controle tbxObservacoes para MultiLine.

Adicione ao projeto uma nova classe, com o nome de ContatosModel. Esta classe será utilizada para transferir dados da camada de apresentação para a camada de persistência.

Listagem 1: Getters e Setters da classe ContatosModel

 public class ContatosModel
{
        // getters and setters utilizando propriedades automáticas
        public string nome { get; set; }
        public string endereco { get; set; }
        public string cidade { get; set; }
        public string estado { get; set; }
        public string telefonefixo { get; set; }
        public string telefonecelular { get; set; }
        public string datadenascimento { get; set; }
        public string observacoes { get; set; }
}

Adicione ao projeto uma nova classe, com o nome de ContatosDAO. Esta classe será responsável por acessar o banco de dados e executar instruções SQL.

Observação: declare os namespaces System.Data e System.Data.SqlClient.

Listagem 2: String de conexão e objeto SqlTransaction

namespace ContatosWEB
{
    public class ContatosDAO
    {       
           // Observe que estamos utilizando uma instância LocalDB do SQL Server
        private string db = @"Server=(localdb)\v11.0; Database = sistemaweb; Integrated Security = SSPI;";
           private SqlTransaction transacao;

Para executar uma instrução SQL utilizando ADO.NET, utilizamos objetos do tipo Command (SqlCommand por exemplo) em conjunto com os seguintes métodos:

  • ExecuteNonQuery;
  • ExecuteReader;
  • ExecuteScalar;

Observação: os objetos SqlCommand necessitam de uma conexão aberta com um banco de dados para executar instruções SQL e/ou Stored Procedures.

ExecuteNonQuery

O método ExecuteNonQuery é utilizado para executar instruções SQL que não retornam dados, como Insert, Update, Delete, e Set.

Durante a manipulação de um banco de dados utilizando o método ExecuteNonQuery, é muito importante utilizar transações. Para mais informações sobre transações consulte o artigo: Controle de transações com ADO.NET e C#.

Adicione o seguinte método à classe ContatosDAO:

Listagem 3: Método Gravar da Classe ContatosDAO

public void gravar(ContatosModel contatos)
 {
          // instruções SQL para inserir novos contatos
         string sql = " Insert into contatos (nome, endereco, cidade, estado, telefonefixo, " +
                            " telefonecelular, datadenascimento, observacoes) values (@nome,      " +
                            " @endereco, @cidade, @estado, @telefonefixo, @telefonecelular,       " +
                            " @datadenascimento, @observacoes)";
        try 
       {        
        // cria a conexao utilizando a String db
        using (var conn = new SqlConnection(db))
                {
                   conn.Open(); // abre a conexão.
                   // inicia a transação com o banco
      transacao = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                
                   // cria um objeto do tipo SqlCommand 
      using (var command = new SqlCommand(sql, conn, transacao))
                    {
                        //preenche os parâmetros da instrução SQL com o objeto contatos
                       command.Parameters.AddWithValue("@nome", contatos.nome);
                        command.Parameters.AddWithValue("@endereco", contatos.endereco);
                        command.Parameters.AddWithValue("@cidade", contatos.cidade);
                        command.Parameters.AddWithValue("@estado", contatos.estado);
                        command.Parameters.AddWithValue("@telefonefixo", contatos.telefonefixo);
                        command.Parameters.AddWithValue("@telefonecelular", contatos.telefonecelular);
                        command.Parameters.AddWithValue("@datadenascimento", contatos.datadenascimento);
                        command.Parameters.AddWithValue("@observacoes", contatos.observacoes);

                        command.ExecuteNonQuery(); // executa a instrução SQL no banco
                        transacao.Commit(); // Conclui a transação
                    }
                    conn.Close(); // fecha a conexão
             }
            }
       catch(SqlException)
            {
                transacao.Rollback(); // caso ocorra uma Exceção cancela as alterações no banco.
            }
 }

ExecuteReader

O método ExecuteReader é utilizado para executar uma instrução SQL que retorna um DataReader.

Um DataReader é preenchido com o resultado de uma instrução SQL do tipo SELECT.

Adicione o seguinte método à classe ContatosDAO.

Listagem 4: Método pesquisar da Classe ContatosDAO

// método publico que retorna um DataTable 
public DataTable pesquisar(ContatosModel contatos)
{
            string pesquisar = "Select * from contatos where nome like ''%'' + @nome + ''%''";
            var dt = new DataTable();
       
            try
            {
               // cria um objetos do tipo SqlConnection
               using (var conn = new SqlConnection(db))
                {
                    conn.Open();    // abre a conexão
                   
       // cria um objeto do tipo SqlCommand
                    using (var command = new SqlCommand(pesquisar, conn))
                    {
                       // preenche o parâmetro da instrução SQL com o objeto contatos 
                       command.Parameters.AddWithValue("@nome", contatos.nome);
                       // cria um DataReader para executar a instrução SQL no Bano 
          SqlDataReader reader = command.ExecuteReader();
                       //Preenche o DataTable com os dados retornado no DataReader 
          dt.Load(reader);
          reader.Close();    // Fecha o DataReader  
                    }
                    conn.Close(); // Fecha a conexão com o banco de dados
                }
                return dt; // retorna o DataTable
            }
            catch (SqlException)
            {
                return null; // em caso de Exceções retorna null
            }
    }

ExecuteScalar

O método ExecuteScalar é utilizado para executar instruções SQL utilizando funções agregadas, como COUNT, SUM, MIN, MAX e AVG. Este método retorna apenas um valor como resultado.

Adicione o seguinte método à classe ContatosDAO:

Listagem 5: Exemplo de utilização do método ExecuteScalar

      
      // método para retornar um Inteiro Anulável
       public Nullable <int> quantidadecontatos()
        {
            // string contendo a instrução SQL
            string count = "Select COUNT(*) from contatos";


            // cria variável do tipo inteiro
            int quantidade = 0;

            try
            {
                // cria objeto do tipo SqlConnection
                using (var conn = new SqlConnection(db))
                {
                    conn.Open(); // abre conexão

                    // cria objeto do tipo SqlCommand
                    using (var command = new SqlCommand(count, conn))
                    {
                        // variável quantidade recebe o resultado da execução do método ExecuteScalar
                        quantidade = (int)command.ExecuteScalar();
                    }
                    conn.Close(); // fecha a conexao
                }
                return quantidade; // retorna a variável quantidade
            }
            catch (SqlException)
            {
                return null;
            }
       }

Voltando ao WebForm Contatos, implemente os métodos a seguir:

Digite o código abaixo no evento Click do botão Inserir.

Listagem 6: Método do botão Inserir

protected void btnInserir_Click(object sender, EventArgs e)
{ 
           // limpa dos controles para inserir um novo registro
            tbxNome.Text = "";
            tbxEndereco.Text = "";
            tbxCidade.Text = "";
            tbxTelefoneFixo.Text = "";
            tbxTelefoneCelular.Text = "";
            tbxDataNascimento.Text = "";
            tbxObservacoes.Text = "";
            tbxNome.Focus();
}

Digite o código abaixo no evento Click do botão Gravar.

Listagem 7:Método do botão Gravar

protected void btnGravar_Click(object sender, EventArgs e)
 {        
            var model = new ContatosModel(); // cria objeto model
            var dao = new ContatosDAO(); // cria objeto dao
            
            // tranfere dados do WebForm para o objeto model
            model.nome = tbxNome.Text;
            model.endereco = tbxEndereco.Text;
            model.cidade = tbxCidade.Text;
            model.estado = ddlEstado.Text;
            model.telefonefixo = tbxTelefoneFixo.Text;
            model.telefonecelular = tbxTelefoneCelular.Text;
            model.datadenascimento = tbxDataNascimento.Text;
            model.observacoes = tbxObservacoes.Text;
            dao.gravar(model); // grava os dados no banco
}

Digite o código abaixo no evento Click do botão Pesquisar.

Listagem 8: Método do botão Pesquisar

 protected void btnPesquisa_Click(object sender, EventArgs e)
 {
            var model = new ContatosModel(); // cria objeto model
            var dao = new ContatosDAO(); // cria objeto dao
            model.nome = tbxPesquisar.Text;

            // executa a pesquisa no banco e lista os dados no GridView
            grdContatos.DataSource = dao.pesquisar(model);
            grdContatos.DataBind();
}

Digite o código abaixo no evento Click do botão Quantidade.

Listagem 9: Método do botão Quantidade

 protected void btnQuantidade_Click(object sender, EventArgs e)
{          
            var dao = new ContatosDAO(); // cria objeto dao
            // executa consulta e exibe o resultado no lblQuantidade
            lblQuantidade.Text = Convert.ToString(dao.quantidadecontatos()); 
}

Conclusão

Neste artigo aprendemos um pouco mais sobre a arquitetura e o funcionamento do ADO.NET Framework, aprendemos a trabalhar com o provedor de dados nativo para SQL Server e a diferença na utilização dos métodos ExecuteNonQuery, ExecuteReader e ExecuteScalar.

Teste o código, refatore e use a criatividade para implementar outros métodos para o projeto.

Um abraço.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?