Criando um DALHelper (Data Application Layer - Camada de Dados)

Como a maioria dos sistemas atuais necessitam de Base de Dados, sempre quando precisamos acessar, inserir, alterar ou excluir um determinado registro, precisamos criar a conexão com a Base de Dados, definir a query, os parâmetros e escolher a opção para executá-la. Para não ficarmos fazendo a todo momento todos esses passos, ou seja, criar conexão, parâmetros, etc., explicarei nesse artigo como criar um DALHelper.

por Israel Aéce

Como a maioria dos sistemas atuais necessitam de Base de Dados, sempre quando precisamos acessar, inserir, alterar ou excluir um determinado registro, precisamos criar a conexão com a Base de Dados, definir a query, os parâmetros e escolher a opção para executá-la (ExecuteNonQuery, ExecuteScalar ou ExecuteReader).

Para não ficarmos fazendo a todo momento todos esses passos, ou seja, criar conexão, parâmetros, etc., explicarei nesse artigo como criar um DALHelper. DALHelper quer dizer: Data Application Layer (Camada de Dados) e Helper (Ajudante), que auxiliará no acesso aos dados, reduzindo assim a quantidade de código em nossa Aplicação.

Para isso criaremos uma Classe onde encapsularemos código ADO.NET. Só para recapitular, Encapsulamento é quando as funções internas de um determinado objeto não interessa ao utilizador do mesmo, ou seja, temos a descrição das operações que o objeto executa que é acessível ao cliente, a implementação de tais operações fica encapsulada e só é visível ao próprio objeto.

Criaremos uma Windows Application chamada DALHelper e nela criaremos também um arquivo chamado "DB.vb" onde ficará a nossa Classe chamada "DB" que será responsável pelo Acesso aos Dados.

Devemos importar as seguintes Namespaces:

Imports System.Data Imports System.Data.SqlClient

Código 1 - Importando Namespaces necessários.

Agora criaremos a nossa Classe "DB":

Imports System.Data Imports System.Data.SqlClient Public Class DB End Class

Código 2 - Criando a Classe que controlará o acesso à Base de Dados.

Devemos declarar os seguintes membros:

Public Class DB Protected m_connectionString As String Private m_manterConexao As Boolean Private conexao As SqlConnection End Class

Código 3 - Declarando os membros da Classe.

A propriedade ConnectionString será responsável para receber a conexão com a Base de Dados que deverá ser informada pela Aplicação.

Public Property ConnectionString() As String Get Return Me.m_connectionString End Get Set(ByVal Value As String) Me.m_connectionString = Value End Set End Property

Código 4 - ConnectionString.

E a propriedade ManterConexão será responsável para sabermos se após executarmos um determinada ação na Base de Dados deixaremos ou não a conexão aberta.

Public Property ManterConexao() As Boolean Get Return Me.m_manterConexao End Get Set(ByVal Value As Boolean) Me.m_manterConexao = Value End Set End Property

Código 5 - Manter ou não a conexão aberta.

OBS.: Devemos tomar muito cuidado para não deixarmos a conexão aberta desnecessariamente.

O próximo passo é criarmos os construtores da Classe. Aqui chamo a atenção para um detalhe: criaremos duas opções para os contrutores (Overload ou Sobrecarga), para que já possamos preencher as propriedades ConnectionString e ManterConexao.

Public Sub New(ByVal connectionString As String) Me.m_connectionString = connectionString End Sub Public Sub New(ByVal connectionString As String, ByVal manterConexao As Boolean) Me.m_connectionString = connectionString Me.m_manterConexao = manterConexao End Sub

Código 6 - Construtores Sobrecarregados.

Podemos reparar que no primeiro construtor, onde há apenas um único parâmetro, que nesse caso é a String de conexão com a Base de Dados. Já no segundo construtor é passada a String de conexão e um valor Booelano que indicará se a conexão com a Base de Dados permanecerá aberta após a execução do comando.

Vamos criar uma Sub-Rotina para podermos fechar a conexão com a Base de Dados. Mas antes de chamarmos o método Close() da SqlConnection, devemos verificar se a variável "conexao" ainda encontra-se ativa.

Public Sub Fechar() If Not (conexao Is Nothing) Then conexao.Close() End If End Sub

Código 7 - Sub-Rotina para fechar o acesso à Base de Dados.

Vamos iniciar pelo ExecuteNonQuery. O ExecuteNonQuery retorna o número de registros afetados pela instrução SQL.

Criaremos para o ExecuteNonQuery, ExecuteReader e ExecuteScalar dois métodos sobrecarregados (Overloads). Um deles receberá como parâmetro: o tipo do comando (CommandType, (Text ou Stored Procedure)) e o nome da Stored Procedure ou a Query SQL. E o outro além desses parâmetros, receberá um terceiro parâmetro ParamArray do tipo SqlParameter que conterá um Array com os parâmetros que serão passados para a Stored Procedure ou Query.

Nesse primeiro método do ExecuteNonQuery será apenas passado o comando (CommandText) e seu tipo (CommandType), já a Query ou Stored Procedure não necessitam parâmetros.

Public Overloads Function ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer Return ExecuteNonQuery(cmdText, cmdType, Nothing) End Function

Código 8 - Executa o ExecuteNonQuery e retorna o quantidade de registros afetados.

Agora, veremos o mesmo método (sobrecarregado) com mais um parâmetro, que como explicado acima, será um Array contendo os parâmetros que serão utilizados na Query ou Stored Procedure:

Public Overloads Function ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal ParamArray parameters() As SqlParameter) As Integer Dim cmd As SqlCommand = CriarCommando(cmdText, cmdType, parameters) Dim retVal As Integer Try If ManterConexao = False Then retVal = cmd.ExecuteNonQuery() Me.Fechar() Else retVal = cmd.ExecuteNonQuery() End If Return retVal Catch e As Exception Me.Fechar() Throw e End Try End Function

Código 9 - Criamos um objeto do tipo SqlCommand que será responsável pela execução da Query ou Stored Procedure e atribuimos a ele o retorno da Função CriarCommando(), qual veremos mais adiante a sua utilidade e funcionamento. Atente-se também à condicional que é feita para verificar se devemos ou não mantermos a conexão com a Base de Dados aberta. E retornamos um inteiro que é o número de registros afetados pela instrução SQL.

OBS.: Mas o utilize somente quando a instrução SQL necessite de parâmetros.Devemos fazer o mesmo para os que restaram: ExecuteScalar e ExecuteReader, mas temos que prestar atenção no valor retorno dos mesmos. Vamos então ver como funciona o ExecuteScalar:

Public Overloads Function ExecuteScalar(ByVal cmdText As String, ByVal cmdType As CommandType) As Object Return ExecuteScalar(cmdText, cmdType, Nothing) End Function

Código 10 - Executa o ExecuteScalar e seu retorno é do tipo Object.

E a da mesma forma que fizemos com o ExecuteNonQuery quando ele necessita passar parâmetros para a Query ou Stored Procedure será feito para o ExecuteScalar:

Public Overloads Function ExecuteScalar(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal ParamArray parameters() As SqlParameter) As Object Dim cmd As SqlCommand = CriarCommando(cmdText, cmdType, parameters) Dim retVal As Object Try If ManterConexao = False Then retVal = cmd.ExecuteScalar() Me.Fechar() Else retVal = cmd.ExecuteScalar() End If Return retVal Catch e As Exception Me.Fechar() Throw e End Try End Function

Código 11 - A única diferença além do método à ser executado, é o retorno do mesmo. Nesse quase será retornado um Object.

Agora com o ExecuteReader dos dois metódos sobrecarregados (Overloads), precisaremos de um método adicional, qual chamaremos de GetDataReader que receberá como parâmetro o SqlCommand e passará pela mesma condicional para verificar se deve ou não manter a conexão com a Base de Dados.

Public Function GetDataReader(ByVal cmd As SqlCommand) As SqlDataReader Try If ManterConexao = False Then Return cmd.ExecuteReader(CommandBehavior.CloseConnection) Else Return cmd.ExecuteReader() End If Return retVal Catch e As Exception Me.Fechar() Throw e End Try End Function

Código 12 - Executa o método ExecuteReader.

Public Overloads Function ExecuteReader(ByVal cmdText As String, ByVal cmdType As CommandType) As SqlDataReader Dim cmd As SqlCommand = CriarComando(cmdText, cmdType, Nothing) Return GetDataReader(cmd) End Function

Public Overloads Function ExecuteReader(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal ParamArray parameters() As SqlParameter) As SqlDataReader Dim cmd As SqlCommand = CriarComando(cmdText, cmdType, parameters) Return GetDataReader(cmd) End Function

Código 13 - A diferença entre esses dois métodos são os parâmetros que podem ou não serem necessários para a Query ou Stored Procedure. Reparem que é feito da mesma forma que nos métodos anteriores, ou seja, é criado um SqlCommand e chamamos a função CriarComando. No retorno de ambas as funções, ele chama o método GetDataReader, passando o Command como parâmetro.

Depois de todos esses passos devemos construir o método CriarComando, que como o próprio nome diz, cria e configura um SqlCommand informando o tipo (CommandType), a Query (CommandText) e os parâmetros (SqlParameter) quando necessário.

Public Function CriarComando(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal ParamArray parameters() As SqlParameter) As SqlCommand Dim cmd As New SqlCommand(cmdText) cmd.CommandType = cmdType If Not (parameters Is Nothing) Then Dim param As SqlParameter For Each param In parameters cmd.Parameters.Add(param) Next param End If VerificarConexao(cmd) Return cmd End Function

Código 14 - Através deste método, definimos o Comando que será executado (CommandText), o Tipo de Comando (CommandType) e em seguida verificamos se há algum parâmetro necessário. Caso exista um ou mais parâmetros, é realizado um laço For..Each atribuindo cada um à coleção de parâmetros do SqlCommand.

Podemos ver que ainda existe uma última Sub Rotina (VerificarConexao) para fecharmos essa primeira parte, que tem por finalidade verificar e/ou criar a conexão com a Base de Dados.

Public Sub VerificarConexao(ByRef cmd As SqlCommand) If conexao Is Nothing Then conexao = New SqlConnection(Me.ConnectionString) conexao.Open() Else If conexao.State <> ConnectionState.Open Then conexao.Open() End If End If cmd.Connection = conexao Return End Sub

Código 15 - Aqui apenas fazemos a verificação com relação ao estado da conexão com a Base de Dados. Depois apenas adicionamos à propriedade Connection do objeto SqlCommand que vem como parâmetro (ByRef).

Bem, depois de tudo isso, parece ser algo completamente complicado. Mas tentarei mostrar o processo através de imagens.

1.º Caso: Abrir a conexão, executar apenas UM determinado método e fechá-la logo em seguida:

Figura 1 - Mantendo a conexão aberta durante a execução de apenas um método.

2.º Caso: Abrir a conexão, executar MAIS DE UM método e fechá-la logo em seguida:

Figura 2 - Neste caso, podemos ver que a conexão se mantém durante a execução de várias métodos.

Ainda faltam três funções que serão acessadas fora da Classe: CriarParametro, GetString e GetInt32.

A função CriarParametro servirá para que possamos criar os parâmetros que serão passados para os métodos que vimos anteriormente (ExecuteNonQuery, ExecuteScalar ou ExecuteReader). Lembrando que os parâmetros são passados como ParamArray.

Public Function CriarParametro(ByVal name As String, ByVal type As SqlDbType, ByVal value As Object) As SqlParameter Dim param As New SqlParameter() param.ParameterName = name param.SqlDbType = type If value Is Nothing Then param.Value = DBNull.Value Else If type = SqlDbType.VarChar And value.ToString().Length = 0 Then param.Value = DBNull.Value Else param.Value = value End If End If Return param End Function

Código 16 - A função recebe o nome, tipo e valor do parâmetro. Trata esses dados para que eles possam ser adicionados à coleção de parâmetros do comando (SqlCommand).

Para finalizar temos apenas mais dois métodos: GetString e GetInt32. Funções quais retornarão valores em Strings e Inteiros, respectivamente do SqlDataReader. Ambas funções recebem como parâmetro o SqlDataReader e um número inteiro que representará o campo que será resgatado desse mesmo SqlDataReader:

Public Shared Function GetString(ByRef dr As SqlDataReader, ByVal ordinal As Integer) As String If Not dr.IsDBNull(ordinal) Then Return dr.GetString(ordinal) Else Return Nothing End If End Function

Public Shared Function GetInt32(ByRef dr As SqlDataReader, ByVal ordinal As Integer) As Integer If Not dr.IsDBNull(ordinal) Then Return dr.GetInt32(ordinal) Else Return Nothing End If End Function

Código 17 - Tanto para o método GetString quanto para o método GetInt32 é verificado se o campo solicitado é ou não nulo. Se não for, seu valor é retornado, caso contrário, não será retornado nada.

OBS.: Podemos também criarmos os métodos GetBoolean, GetDateTime, etc., mas nesse artigo apenas usaremos os dois métodos citados acima.

Enfim, nosso DALHelper pronto para ser colocado em ação. Criaremos então duas Tabelas na Base de Dados para testá-lo. Abaixo a estrutura das Tabelas:

Figura 3 - Estrutura das Tabelas de Exemplo.

Temos também que criarmos um Formulário para que possamos interagir com o Usuário. Abaixo o formulário que usaremos como exemplo:

Figura 4 - Usando 2 TextBox, 1 Button e 1 ListBox.

Abaixo as Stored Procedures que utilizaremos no decorrer do nosso exemplo. Em um dos casos utilizarei o código SQL diretamente.

CREATE PROCEDURE BuscarClientes AS SELECT ClienteID, Nome + " - " + Email AS DadosCliente FROM Clientes ORDER BY ClienteID ASC GO

CREATE PROCEDURE AdicionarCliente @Nome AS Varchar(50), @Email AS Varchar(50) AS INSERT INTO Clientes (Nome, Email) VALUES (@Nome, @Email) GO

Código 18 - A primeira Stored Procedure retorna todos os Clientes. Já a segunda será utilizada para adicionarmos um novo cliente.

Depois de nosso DALHelper pronto, as Tabelas da Base de Dados e das Stored Procedures vamos começar a codificar o Front-End. Primeiramente incluirei mais dois WinForm ao Projeto para que possamos entender os dois casos como vimos acima.

Figura 5 - Temos dois WinForms para entendermos os dois casos.

1.º Caso - Fechar a Conexão logo após executar um determinado Comando.

Vamos analisar a Sub-Rotina CarregaListBox que será responsável por carregar os dados da Base de Dados (Tabela Clientes) no ListBox:

Private Sub CarregaListBox() Me.lstClientes.Items.Clear() Dim dalHelper As New DalHelper.DB(strConexao, False) Dim dr As SqlDataReader Try dr = dalHelper.ExecuteReader("BuscarClientes", CommandType.StoredProcedure) While dr.Read() Me.lstClientes.Items.Add(dalHelper.GetInt32(dr, 0) & " - " & dalHelper.GetString(dr, 1)) End While Catch ex As Exception MessageBox.Show("Ocorreu um erro ao tentar acessar a Base de Dados", "Base de Dados", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub

Código 19 - No construtor da Classe DB passamos como parâmetro a ConnectionString que está armazenada em uma constante declarada no topo do WinForm e o segundo parâmetro diz que após efetuar o comando deve ser fechada a conexão com a Base de Dados.

Podemos ver que após instanciarmos a Classe DB, podemos utilizar seus métodos e propriedades. No código acima, criamos um SqlDataReader e atribuímos à ele o retorno da função ExecuteReader que recebe como parâmetro o Nome do Stored Procedure ou Query e o CommandType, que por sua vez também retorna um SqlDataReader. Através de um laço While percorremos o SqlDataReader e adicionamos cada registro ao ListBox. Utilizamos o GetInt32 quando o campo à ser resgatado do SqlDataReader é do tipo Inteiro, já o GetString o utilizamos quando é o campo é do tipo Texto.

No evento Click do botão Adicionar devemos incluir um novo registro no Base de Dados e logo após fecharmos a conexão com a mesma. Veja o código:

Private Sub btnCadastrar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCadastrar.Click Dim dalHelper As New DalHelper.DB(strConexao, False) Dim iRegistrosAfetados As Integer = 0 Try Dim param() As SqlParameter = { _ dalHelper.CriarParametro("@Nome", SqlDbType.VarChar, Me.txtNome.Text.Trim), _ dalHelper.CriarParametro("@Email", SqlDbType.VarChar, Me.txtEmail.Text.Trim)} iRegistrosAfetados = dalHelper.ExecuteNonQuery("AdicionarCliente", CommandType.StoredProcedure, param) Catch ex As Exception MessageBox.Show("Ocorreu um erro ao tentar acessar a Base de Dados", "Base de Dados", MessageBoxButtons.OK, MessageBoxIcon.Error) Finally If iRegistrosAfetados = 1 Then Call Me.CarregaListBox() MessageBox.Show("Registro incluído com sucesso.") Else MessageBox.Show("Ocorreu um erro ao tentar incluir o registro.") End If End Try End Sub

Código 20 - Ao clicarmos no botão para adicionarmos um novo registro na Base de Dados, instanciamos a Classe DB e novamente informamos à ela a ConnectionString e dizemos através do segundo parâmetro que após a execução, deve ser fechada a conexão com a Base de Dados, criamos uma variável chamada param() que será um Array de SqlParameter e utilizamos o método ExecuteNonQuery.

Com o Array podemos incluir quantos parâmetros forem necessários. Basta utilizar o método CriarParametro e informar o Nome, o Tipo de Dados e o Valor, separando cada um por uma vírgula ( , ). No caso desse exemplo, os dois parâmetros são do Tipo Varchar e recebem os TextBox que estão no WinForm.

O método ExecuteNonQuery retornará o número de registros afetados pela instrução SQL. No bloco Finally verificamos se a variável iRegistrosAfetados for igual a 1 é porque o registro foi incluído com sucesso, caso contrário houve uma falha.

2.º Caso - Manter a Conexão Aberta durante a execução de vários Comandos.

Agora iremos alterar a rotina que adiciona um novo registro. Vamos manter a conexão aberta durante a executação de duas instruções SQL. Para isso, após a execução da Stored Procedure AdicionarCliente, vamos adicionar à Tabela Logs um novo registro e para isso utilizaremos a mesma conexão, apenas mudando o CommandType:

Private Sub btnCadastrar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCadastrar.Click Dim dalHelper As New DalHelper.DB(strConexao, True) Dim iRegistrosAfetados As Integer = 0 Try Dim param() As SqlParameter = { _ dalHelper.CriarParametro("@Nome", SqlDbType.VarChar, Me.txtNome.Text.Trim), _ dalHelper.CriarParametro("@Email", SqlDbType.VarChar, Me.txtEmail.Text.Trim)} iRegistrosAfetados = dalHelper.ExecuteNonQuery("AdicionarCliente", CommandType.StoredProcedure, param) dalHelper.ExecuteNonQuery("INSERT INTO Logs (Data) VALUES (GetDate())", CommandType.Text) Catch ex As Exception MessageBox.Show("Ocorreu um erro ao tentar acessar a Base de Dados", "Base de Dados", MessageBoxButtons.OK, MessageBoxIcon.Error) Finally dalHelper.Fechar() If iRegistrosAfetados = 1 Then MessageBox.Show("Registro incluído com sucesso.") Call Me.CarregaListBox() Else MessageBox.Show("Ocorreu um erro ao tentar incluir o registro.") End If End Try End Sub

Código 21 - A diferença aqui se encontra no segundo parâmetro que é informado ao construtor da Classe DB, que nesse caso é True, que diz que depois de executar o comando, a conexão deve permanecer aberta.

Chamo a atenção para a linha onde temos:

dalHelper.ExecuteNonQuery("INSERT INTO Logs (Data) VALUES (GetDate())", CommandType.Text)

Código 22 - Utilizando instruções SQL diretamente.

Neste caso informamos a Query diretamente, ou seja, sem Stored Procedures. O procedimento é o mesmo, apenas temos que mudar o CommandType para CommandType.Text. A criação de parâmetros continua sendo da mesma forma das Stored Procedures, ou seja, utilizando o método CriarParametro.

Um dos principais cuidados que devemos ter quando mantermos a conexão aberta, é que após finalizarmos todo o processo, devemos fechar a conexão com a Base de Dados explicitamente através do método Fechar().

OBS.: Procure não deixar a String de Conexão com a Base de Dados em Hard-Code, ou seja, deixá-la juntamente com o código, pois isso implicaria em cada vez que mudar a Base de Dados/String de Conexão, recompilar a aplicação.

Conclusão: Utilizando o DALHelper escrevemos muito menos código do que quando utilizamos as declarações de SqlCommand, SqlParameter, SqlCommand, etc. normalmente, pois todos esses códigos ADO.NET já estão encapsulados dentro do DALHelper. Podemos também definir o Tipo de Comando (CommandType), manter a conexão aberta ou não após a execução de um comando, entre outras funcionalidades/facilidades.

Artigo desenvolvido utilizando: * Visual Studuo .NET 2002 * .NET Framework 1.0 * SQL Server 2000 * Windows XP Professional Clique aqui para fazer Download do Código.