Desenvolvendo uma classe ADO .NET para acesso a dados SQL Server

Veja nesse artigo o desenvolvimento de uma classe ADO.NET para conexão, consumo e manipulação de dados com banco de dados SQL Server.

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:

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:

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.

Nota: A diferença de desempenho entre o ADO e Entity é baixíssima, pois ambos funcionam perfeitamente juntos.

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:

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:

  1. Definir o parâmetro na cadeia de comando SqlCommand;
  2. Declarar o objeto SqlParameter com propriedades aplicáveis;
  3. 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 } }
Listagem 1. Código da Classe completa

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); }
Listagem 2. Efetuando uma consulta com a classe de dados encapsulada

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); }
Listagem 3. Efetuando Insert com a classe de dados encapsulada

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; }
Listagem 4. Efetuando um DELETE com a classe de dados encapsulada

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; }
Listagem 5. Obtendo um objeto com informações com a classe de dados encapsulada

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; }
Listagem 6. Atualizando registros com a classe de dados encapsulada

Para finalizar, na Listagem 6 mostramos como efetuar um UPDATE utilizando nossa classe desenvolvida.

Observações:

Dicas:

  1. 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.
  2. 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!

Links:

Artigos relacionados