Criando um DalHelper


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

    Private 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.

Observação: 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.

Observação: 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().

Observação: 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.

DALHelper.zip (47.35 kb)

Anúncios

2 comentários sobre “Criando um DalHelper

    • Tudo bem, pelo menos eu tentei :-p

      Vai aqui então a alteração que fiz para recuperar os parâmetros output das procedures (caso sirva pra alguém).

      Incluir junto com às propriedades da classe:

      Private _params() As SqlParameter

      Public ReadOnly Property GetParams() As SqlParameter()
      Get
      Return Me._params
      End Get
      End Property

      Na Function CriarComando eu fiz a seguinte alteração:

      Alterei essa parte:
      If Not (parameters Is Nothing) Then
      Dim param As SqlParameter
      For Each param In parameters
      cmd.Parameters.Add(param)
      Next param
      End If

      Por essa:
      Me._params = parameters
      If Not (parameters Is Nothing) Then
      cmd.Parameters.AddRange(Me._params)
      End If

      Na classe é isso.
      ——————————-

      Usando a classe, criar os parâmetros output com valores Nothing e após a execução recuperar os parametros como no exemplo a seguir:

      Dim RetornoID As Integer = dalHelper.GetParams(2).Value

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s