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
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.
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.
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:
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.
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.
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.
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:
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.
// 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:
// 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.
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.
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.
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.
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.
