Neste artigo criaremos uma classe que irá encapsular toda a parte de acesso a dados ao SQL Server com .NET. Para isso, iremos utilizar o ADO .NET, que é um conjunto de classes do .NET Framework desenvolvidas para facilitar o acesso das aplicações que utilizam diversas bases de dados de diferentes tipos, especialmente bancos de dados como Access, SQL Server, Oracle, dentre outros.
Essa classe poderá funcionar como um framework, podendo assim ser reutilizada em diversos projetos. A seguir descreveremos os métodos existentes na nossa classe para um melhor entendimento:
- static SqlConnection connection(): é o método responsável por obter uma conexão com a nossa instância do SQL Server. Não há passagem de parâmetros e, por ser estático, deverá ser utilizado por praticamente todos os métodos na nossa classe, retornando um objeto SqlConnection;
- void Open(): é o método responsável por abrir a conexão com a nossa instância do SQL Server. Não há passagem de parâmetros e nem retorno;
- void Close(): é o método responsável por fechar a conexão com a nossa instância do SQL Server. Também não há passagem de parâmetros e nem retorno;
- void AdicionarParametro(string nome, SqlDbType tipo, int tamanho, object valor): é o método responsável por adicionar os parâmetros em uma consulta ou insert. Nele temos quatro parâmetros: nome, tipo (int, varchar, bool, etc), tamanho e o valor do mesmo. Não há retorno;
- void AdicionarParametro(string nome, SqlDbType tipo, object valor): é o método responsável por adicionar os parâmetros em uma consulta ou insert. Nele temos os mesmos quatro parâmetros do método anterior. Também não tem retorno;
- void RemoverParametro(string pNome): é o método responsável por remover um parâmetro informado. Não há retorno;
- void LimparParametros(): é o método responsável por limpar todos os parâmetros existentes. Também não tem retorno;
- DataTable ExecutaConsulta(string sql): é o método responsável por executar uma consulta SQL e tem como retorno um DataTable com as informações retornadas;
- int ExecutaAtualizacao(string sql): é o método responsável por executar um insert, update ou delete junto a nossa base de dados. O parâmetro passado é a nossa instrução SQL e tem como retorno a quantidade de linhas afetas;
O que é o ADO.NET
ADO.NET é um pacote que inclui provedores de dados que fornecem acesso à fonte de dados com SQL Server, além de executar comandos e recuperar resultados, fornecendo acesso a fontes de dados expostas do OLE DB e do ODBC. Com isso, todos os softwares que utilizam o ADO. NET terão acesso nativo ao SQL Server. Em outras palavras, o ADO .NET é uma tecnologia para banco de dados da plataforma de .NET.
Ele define os objetos DataSet e DataTable que são otimizados para mover e manipular um conjunto desconectado de dados através de redes e através de firewalls. Vem incluso também os objetos Connection e Command, assim como um objeto DataReader que se assemelha a um recordSet, somente para leitura.
O ADO .NET pode ser utilizado para acessar dados usando os novos fornecedores de dados nativos ou seja, os Data Providers a seguir:
- Fornecedor dos dados para usuário do SQL (System.Data.SqlClient) - para bancos de dados a partir da versão 7;
- Fornecedor dos dados para OLEDB (System.Data.OleDb) - para bancos de dados Access e SQL Server anteriores a versão 7;
- Fornecedor dos dados para ODBC (System.Data.Odbc) - para bancos de dados padrões;
- Fornecedor dos dados para Oracle (System.Data.OracleClient) - para bancos de dados Oracle;
Caso você necessite acessar outro banco de dados, indicamos que acesse o site do fabricante do mesmo para obter o drive necessário para a comunicação.
Como no nosso caso iremos utilizar SQL Server, não é necessário, pois o driver do mesmo já é nativo.
Para escrever o código seguro ADO.NET, você precisa entender os mecanismos de segurança disponíveis no banco de dados.Também é necessário considerar as implicações de segurança de outros recursos ou componentes que seu aplicativo pode conter.
Podemos encontrar as classes do ADO.NET no namespace System.Data.dll e que estão integradas com as classes XML, que podem ser encontradas no namespace System.Xml.dll.
Para criarmos uma classe que consiga acesso ao SQL Server precisaremos utilizar especificamente três componentes: SqlConnection, SqlCommand e SqlParameter.
O SqlConnection é o responsável pela conexão com o banco de dados e, para estabelecer essa conexão, é necessário que tenhamos uma ConnectionString.
O SqlCommand é o responsável por executar comandos SQL e os métodos disponíveis para executar estes são:
- ExecuteReader: Executa comandos SQL que retornam dados;
- ExecuteNonQuery: Executa comandos SQL que não retornam dados;
- ExecuteScalar: Retorna um único valor.
O SqlParameter é o responsável por efetuar a passagem de parâmetros para os comandos SQL. Você deve usar parâmetros para filtrar consultas de uma maneira segura, já que ele funciona em 3 etapas:
- Definir o parâmetro na cadeia de comando SqlCommand;
- Declarar o objeto SqlParameter com propriedades aplicáveis;
- E atribuir o objeto SqlParameter para o objeto SqlCommand.
Quando o SqlCommand executa, os parâmetros serão substituídos por valores especificados pelo objeto SqlParameter.
Com esses três componentes conseguiremos fazer as operações básicas junto ao banco de dados, que é obter uma conexão, abrir conexão, fechar conexão, e Incluir, Alterar e Excluir.
Para termos uma conexão aberta, a seguir demostraremos o método de obter conexão junto ao SQL Server. Lembre-se de usar os seguintes namespaces do ADO .NET: System.Data, System.Data.OleDb, System.Data.Odbc, System.Data.OracleClient e System.Data.SqlClient.
A Listagem 1 mostra o código completo da nossa classe, com todos os métodos comentados para melhor entendimento de cada passo necessário.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace FrameworkDevmedia.DAL
{
public class DataAccess
{
#region Objetos Estáticos
// Objeto Connection para obter acesso ao SQL Server
public static SqlConnection sqlconnection = new SqlConnection();
// Objeto SqlCommand para executar os com
public static SqlCommand comando = new SqlCommand();
// Objeto SqlParameter para adicionar os parâmetros
necessários em nossas consultas
public static SqlParameter parametro = new SqlParameter();
#endregion
#region Obter SqlConnection
public static SqlConnection connection()
{
try
{
// Obtemos os dados da conexão existentes no WebConfig
utilizando o ConfigurationManager
string dadosConexao = ConfigurationManager
.ConnectionStrings["NomeDaConnectionString"].ConnectionString;
// Instanciando o objeto SqlConnection
sqlconnection = new SqlConnection(dadosConexao);
//Verifica se a conexão esta fechada.
if (sqlconnection.State == ConnectionState.Closed)
{
//Abre a conexão.
sqlconnection.Open();
}
//Retorna o sqlconnection.
return sqlconnection;
}
catch (SqlException ex)
{
throw ex;
}
}
#endregion
#region Abre Conexão
public void Open()
{
sqlconnection.Open();
}
#endregion
#region Fecha Conexão
public void Close()
{
sqlconnection.Close();
}
#endregion
#region Adiciona Parâmetros
public void AdicionarParametro(string nome,
SqlDbType tipo, int tamanho, object valor)
{
// Cria a instância do Parâmetro e adiciona os valores
parametro = new SqlParameter();
parametro.ParameterName = nome;
parametro.SqlDbType = tipo;
parametro.Size = tamanho;
parametro.Value = valor;
// Adiciona ao comando SQL o parâmetro
comando.Parameters.Add(parametro);
}
#endregion
#region Adiciona Parâmetros
public void AdicionarParametro(string nome, SqlDbType tipo, object valor)
{
// Cria a instância do Parâmetro e adiciona os valores
SqlParameter parametro = new SqlParameter();
parametro.ParameterName = nome;
parametro.SqlDbType = tipo;
parametro.Value = valor;
// Adiciona ao comando SQL o parâmetro
comando.Parameters.Add(parametro);
}
#endregion
#region Remove os parâmetros
public void RemoverParametro(string pNome)
{
// Verifica se existe o parâmetro
if (comando.Parameters.Contains(pNome))
// Se exite remove o mesmo
comando.Parameters.Remove(pNome);
}
#endregion
#region Limpar Parâmetros
public void LimparParametros()
{
comando.Parameters.Clear();
}
#endregion
#region Executar Consulta SQL
public DataTable ExecutaConsulta(string sql)
{
try
{
// Pega conexão com a base SQL Server
comando.Connection = connection();
// Adiciona a instrução SQL
comando.CommandText = sql;
//Executa a query sql.
comando.ExecuteScalar();
// Ler os dados e passa para um DataTable
IDataReader dtreader = comando.ExecuteReader();
DataTable dtresult = new DataTable();
dtresult.Load(dtreader);
// Fecha a conexão
sqlconnection.Close();
// Retorna o DataTable com os dados da consulta
return dtresult;
}
catch (Exception ex)
{
// Retorna uma exceção simples que pode ser tratada por parte do desenvolvedor
// Exemplo: if (ex.Message.toString().Contains(‘Networkig’))
// Exemplo throw new Exception(‘Problema de rede detectado’);
throw ex;
}
}
#endregion
#region Executa uma instrução SQL: INSERT, UPDATE e DELETE
public int ExecutaAtualizacao(string sql)
{
try
{
//Instância o sqlcommand com a query sql que será executada e a conexão.
//comando = new SqlCommand(sql, connection());
comando.Connection = connection();
comando.CommandText = sql;
//Executa a query sql.
int result = comando.ExecuteNonQuery();
sqlconnection.Close();
// Retorna a quantidade de linhas afetadas
return result;
}
catch (Exception ex)
{
// Retorna uma exceção simples que pode ser tratada por parte do desenvolvedor
throw ex;
}
}
#endregion
}
}Com nossa classe criada fica simples consumir a mesma em nossos projetos.
Para fazer uso dela precisaremos antes de tudo instanciar a nossa classe criada para conseguir efetuar a chamada dos métodos existentes usando o seguinte exemplo:
NomeDaClasse instaciaDaClasse = new NomeDaClasse();
Cliente cli = new Cliente();
instaciaDaClasse.InserirCliente(cli);
public DataTable EfetuarConsultaPorCodigo(int codigo)
{
// Limpando parãmetros existentes
query.LimparParametros();
string SQL = @" SELECT
c.NR_MATRICULA,
c.DS_NOMECLIENTE AS NOME,
c.DS_ESTADO AS ESTADO,
c.DS_CIDADE AS CIDADE,
c.DS_ENDERECO AS ENDERECO,
c.DT_NASCIMENTO
FROM CLIENTES c WHERE c.NR_MATRICULA = @ NR_MATRICULA ";
// Adicionando o parâmetro para filtrar pelo código
query.AdicionarParametro("@NR_MATRICULA ", SqlDbType.Int, codigo);
// Retorna um DataTable com os dados da consulta
return query.ExecutaConsulta(SQL);
}Na Listagem 2 podemos ver de forma simples e organizada como obter os dados a partir de uma consulta SQL, tendo esses dados em um objeto DataTable para manipularmos de forma simples.
public bool InserirCliente(Cliente cliente)
{
// Limpa os parâmetros existente
query.LimparParametros();
string SQL = @"INSERT INTO CLIENTE
(NOME, DT_NASCIMENTO, NR_MATRICULA, DS_ENDERECO)
VALUES
(@NOME,@DT_NASCIMENTO,@NR_MATRICULA, @DS_ENDERECO)";
// Adiciona os parâmetros da instrução SQL
query.AdicionarParametro("@NOME", SqlDbType.VarChar, cliente.Nome);
query.AdicionarParametro("@DT_NASCIMENTO", SqlDbType.DateTime, cliente.Data_nasc);
query.AdicionarParametro("@NR_MATRICULA", SqlDbType.Int, cliente.Matricula);
query.AdicionarParametro("@DS_ENDERECO", SqlDbType.Varchar, cliente.Endereco);
// Retorna a quantidade de linhas afetadas
return (query.ExecutaAtualizacao(SQL) > 0);
}Na Listagem 3 efetuamos um INSERT na nossa base de dados utilizando os métodos da nossa classe. O método ExecutaAtualizacao retorna a quantidade de linhas afetadas, assim temos como saber se realmente nossa informação foi gravada com sucesso ou não na nossa base de dados.
public bool ExcluirCliente(int nrMatricula)
{
query.LimparParametros();
string SQL = @"DELETE FROM CLIENTE WHERE NR_MATRICULA = @NR_MATRICULA";
query.AdicionarParametro("@NR_MATRICULA", SqlDbType.Int,nrMatricula);
if (query.ExecutaAtualizacao(SQL) > 0)
return true;
return false;
}Na Listagem 4 efetuamos um DELETE na nossa base de dados fazendo de forma parecida com o método anterior para verificar se o registro foi realmente excluído.
public Cliente ObterDadosCliente(int nrMatricula)
{
// Limpando os parãmetros
query.LimparParametros();
string SQL = @"SELECT
c.NR_MATRICULA,
c.DS_NOMECLIENTE AS NOME,
c.DS_ESTADO AS ESTADO,
c.DS_CIDADE AS CIDADE,
c.DS_ENDERECO AS ENDERECO,
c.DT_NASCIMENTO
FROM CLIENTE c
WHERE c.NR_MATRICULA = @INR_MATRICULA";
// Adicionando novos parâmetros
query.AdicionarParametro("@NR_MATRICULA", SqlDbType.Int, nrMatricula);
// Obtendo um DataTable com as informações
DataTable dtResult = query.ExecutaConsulta(SQL);
// Preenchendo os dados do objeto
Cliente cliente = new Cliente();
cliente.nrMatricula = Convert.ToInt32(dtResult.Rows[0]["NR_MATRICULA"].ToString());
cliente.Nome = dtResult.Rows[0]["NOME"].ToString();
cliente.Estado = dtResult.Rows[0]["ESTADO"].ToString();
cliente.Cidade = dtResult.Rows[0]["CIDADE"].ToString();
cliente.Endereco = dtResult.Rows[0][" DS_ENDERECO "].ToString();
// Retornando o objeto com as informações carregadas da base de dados
return cliente;
}Na Listagem 5 demonstramos como obter um objeto com as informações preenchidas, obtidas da nossa base de dados.
public bool AlterarCliente(Cliente cliente)
{
// Limpando os parâmetros
query.LimparParametros();
string SQL = @"UPDATE CLIENTE SET
DS_NOMECLIENTE = @ DS_NOMECLIENTE,
DS_ENDERECO = @ DS_ENDERECO,
DT_NASCIMENTO = @ DT_NASCIMENTO,
WHERE NR_MATRICULA = @NR_MATRICULA ";
// Adicionando novos parâmetros
query.AdicionarParametro("@NR_MATRICULA ", SqlDbType.Int, cliente.NrMatricula);
query.AdicionarParametro("@DS_NOMECLIENTE ", SqlDbType.Varchar, cliente.NomeCliente);
query.AdicionarParametro("@DT_NASCIMENTO ", SqlDbType.DateTime, cliente.DataNascimento);
query.AdicionarParametro("@DS_ENDERECO", SqlDbType.Varchar, cliente.Endereco);
// Executando a atualização na base verificando se o update executou com sucesso
if (query.ExecutaAtualizacao(SQL) > 0)
return true;
return false;
}Para finalizar, na Listagem 6 mostramos como efetuar um UPDATE utilizando nossa classe desenvolvida.
Observações:
- No início de todos os métodos chamamos o método LimparParametros(), que por sua vez utiliza um único SqlCommand para evitar a criação de várias instâncias. Se não chamarmos o método para efetuar a limpeza dos parâmetros, poderemos ter problemas como, por exemplo, existir parâmetros de consultas anteriores de forma indesejável. Fica a critério instanciar o objeto a cada método ou utilizar esse método e ter apenas um objeto SqlCommand.
- Para manipulação dos dados não é necessário manter a conexão ativa a uma fonte de dados. Isso é muito importante para aplicações em termo de desempenho, o que, com certeza, foi o principal foco da equipe de desenvolvimento do ADO. NET e a principal mudança em relação ao ADO tradicional.
- O XML é um padrão aberto de dados e o ADO.NET consegue fazer a sua unificação diretamente em XML, facilitando a integração com diversas plataformas.
- Para acesso a bancos de dados SQL Server 6.5 e anteriores, Access e outros que não possuem classe específica criadas por seus fabricantes, é recomendado o uso do Provider OleDb;
Dicas:
- Podemos também ao invés de utilizar essa classe em todos os projetos, gerar uma dll dessa classe, importar e utilizar apenas a dll para ter acesso a todos os métodos que criamos na classe. Para gerá-la, basta efetuar a compilação do projeto e automaticamente ela estará disponível na pasta Bin da sua aplicação. Já para utilizá-la será necessário adicionar essa dll gerada nas referências do projeto e ai poderemos efetuar as chamadas dos seus métodos diretamente.
- Para estabelecer conexão com uma base de dados SQL Server é necessário ter algumas informações no WebConfig das nossas aplicações. As informações são as seguintes:
- Data Source: Identificação do Servidor. Pode ser a máquina local, domínio ou endereço de IP;
- Initial Catalog: Nome ou Alias do banco de dados que queremos acesso;
- Integrated Security: Definição do SSPI para efetuar a conexão com o usuário do Windows;
- User ID: Nome do usuário de acesso ao banco de dados;
- Password: Senha do usuário
Podemos concluir que o uso da classe ADO.NET encapsulada da forma explicada torna o código nos nossos projetos bem mais elegantes, evitando repetições de códigos. Espero que tenha gostado do assunto. Até o próximo artigo!
