ASP .NET - Usando Consultas parametrizadas com MS Access

ASP .NET - Usando Consultas parametrizadas com MS Access

Neste artigo eu vou abordar o acesso a um banco de dados Microsoft Access usando consultas parametrizadas para mostrar a sua sintaxe.

O Microsoft Access não é um banco de dados robusto como o SQL Server, MySQL, Oracle, PostGreSQL e outros SGBDs mas ainda é muito usado para pequenas aplicações e protótipos.

Vou usar o Visual Studio 2012 Express for web e criar uma pequena aplicação ASP .NET onde iremos realizar as principais operações de manutenção de dados como incluir, alterar, excluir, consultar em um banco de dados MS Access 2007.

Eu vou criar um banco de dados chamado Cadastro.mdb no Microsoft Access e uma tabela Clientes com a seguinte estrutura:

Após criar o banco de dados podemos abri-lo no IDE do Visual Studio abrindo da seguinte forma:

Abra o DataBase Explorer
Clique com o botão direito do mouse sobre Data Connections e selecione Add Connection;
Clique no botão Change em Data Source e selecione Microsoft Access DataBase File;
Informe o nome do banco de dados que deseja criar e clique em OK;
Pronto ! O arquivo será visualizado na janela DataBase Explorer;

O objetivo é mostrar a sintaxe usada nas instruções SQL que é diferente da sintaxe usada para com o SQL Server.

A linguagem usada no projeto será a linguagem VB .NET mas vou mostrar o código também na linguagem C#.

Criando a aplicação ASP .NET

Abra o Visual Studio 2012 Express for web e clique em New Project;

Selecione o template Visual Basic -> Web -> ASP .NET Emty Web Application e informe o nome ConsultasParametrizadas_MSAccess;

Será criado um projeto vazio contendo somente o arquivo Web.Config. Vamos incluir um formulário que será usado como interface com o usuário.

Clique no menu PROJECT -> Add New Item e a seguir selecione o template Web -> Web Form e informe o nome Default.aspx e clique no botão Add;

Vamos agora definir no arquivo Web.config a string de conexão de acesso ao nosso banco de dados.

Abra o arquivo Web.Config e inclua código destacado em azul abaixo para definir a string de conexão:

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
    <system.web>
      <compilation debug="true" strict="false" explicit="true" targetFramework="4.5" />
      <httpRuntime targetFramework="4.5"  />
    </system.web>
  <connectionStrings>
    <add name="ConexaoMSAccess" connectionString="Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Cadastro.mdb" 
providerName="System.Data.OleDb" />
  </connectionStrings>
</configuration>

O nome da conexão foi definida como ConexaoMSAccess e o arquivo Cadastro.mdb esta localizado na pasta App_Data.

Vamos então criar esta pasta no projeto. Clique no menu PROJECT -> Add ASP .NET Folder e selecione App_Data;

A seguir copie o arquivo Cadastro.mdb para esta pasta no projeto.

Para ler esta informação vamos usar a classe ConfigurationManager do namespace System.Configuration.

Agora vamos incluir alguns controles a partir da ToolBox na página Default.aspx.

Abra a página Default.aspx no modo Design e no menu clique em TABLE e inclua uma tabela com 10 linhas e duas colunas.

A seguir a partir da ToolBox inclua os controles na página Default.aspx:

Image = ID=Image1
4 TextBox - IDs = txtID, txtNome, txtEndereco, txtEmail
7 Button - IDs = btnLocalizarID, btnLocalizar,btnIncluir,btnAlterar, btnExcluir, btnEncerrar
1 GridView - ID = gdvClientes
1 Label - ID = lblmsg

Disponha os controles conforme o leiaute da figura abaixo:

Crie também uma pasta chamada Imagens e coloque uma imagem para ser exibida no controle Image. Eu estou exibindo o logotipo Macoratti .net. Para fazer isso clique em PROJECT -> New Folder e informe o nome Imagens.

Agora temos que definir o código para acessar os dados do banco de dados e realizar as operações de consulta, inclusão, exclusão e alteração.

Como vamos fazer isso ???

Colocando o código no arquivo code-behind Default.aspx.vb ???

Não, jamais.

Embora o projeto seja bem simples, e o objetivo do artigo não seja a criação de uma aplicação em camadas vamos criar uma classe e definir nela o código de acesso e persistência de dados.

Não vai ser a maravilha das boas práticas, mas vai ser bem melhor que colocar todo o código nos eventos dos controles no arquivo code-behind.

Assim estamos separando um pouco a responsabilidades.

Criando a classe para acesso aos dados

No menu PROJECT clique em Add Class e informe o nome AcessoDB.vb;

Vamos começar definindo os namespaces usados na classe. Como estamos acessando um banco de dados MS Access vamos usar os seguintes namespaces:

Imports System.Data
Imports System.Data.OleDb

A seguir vamos definir uma variável estática para a nossa string de conexão:

Public Shared ConexaoMS As String = Nothing

Vamos definir 4 métodos estáticos de forma que não iremos precisar instanciar a classe AcessoDB:

GetConnString - obtém a string de conexão a partir do arquivo Web.Config;
GetDados - Seleciona os dados da tabela Clientes e retorna um DataTable;
IncluirAlterarDados - Permite incluir ou alterar dados, retorna o número de registros afetados;
ExcluirDados - Realiza a exclusão de dados, retorna o número de registros afetados;

A palavra reservada shared define um método/variável como estático.

Você sabe o que são e como usar membros ou métodos Shared (estáticos) ?

As propriedades e métodos de uma classe podem ser membros de instância da classe ou membros compartilhados(shared) ou estáticos.

Membros de instância são associados com instâncias de um tipo , enquanto que membros estáticos (shared) são associados com a classe e não com uma instância em particular.

Métodos são métodos de instância a menos que você explicitamente os defina como métodos estáticos (shared) usando a palavra-chave Shared.

A grande maioria dos métodos são métodos de instância , isto significa que você deve acessá-los a partir de um objeto especifico. Quando você precisar de um método de classe , ou seja , um método que seja acessado diretamente da classe você deve definí-lo como estático ou Shared.

1- GetConnString

Public Shared Function GetConnString() As String

Return ConfigurationManager.ConnectionStrings("ConexaoMSAccess").ConnectionString

End Function

2 - GetDados

Public Shared Function GetDados(ByVal consultaSql As String, Optional ByVal nome As String = "") As DataTable
       
        Dim cmd As New OleDbCommand(consultaSql)

        If Not String.IsNullOrWhiteSpace(nome) Then
            cmd.Parameters.AddWithValue("_nome", nome)
        End If

        Dim dt As New DataTable

        ConexaoMS = GetConnString()
        Dim con As New OleDbConnection(ConexaoMS)
        Dim da As New OleDbDataAdapter
        cmd.CommandType = CommandType.Text
        cmd.Connection = con

        Try
            con.Open()
            da.SelectCommand = cmd
            da.Fill(dt)
            Return dt
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            da.Dispose()
            con.Dispose()
        End Try

    End Function

3- IncluirAlterarDados

Public Shared Function IncluirAlterarDados(ByVal consultaSql As String, ByVal nome As String, ByVal endereco As String, 
email As String, Optional ByVal id As Integer = 1) As Integer

        ConexaoMS = GetConnString()
        Dim registrosAfetados As Integer
        Using conn As New OleDbConnection(ConexaoMS)
            Using cmd As New OleDbCommand(consultaSql, conn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("_nome", nome)
                cmd.Parameters.AddWithValue("_endereco", endereco)
                cmd.Parameters.AddWithValue("_email", email)
                If Not String.IsNullOrWhiteSpace(id) Then
                    cmd.Parameters.AddWithValue("_id", id)
                End If
                conn.Open()
                Try
                    registrosAfetados = cmd.ExecuteNonQuery()
                    Return registrosAfetados
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Using
    End Function

4- ExcluirDados

Public Shared Function ExcluirDados(ByVal consultaSql As String, id As Integer) As Integer
     
        ConexaoMS = GetConnString()

        Using conn As New OleDbConnection(ConexaoMS)
            Using cmd As New OleDbCommand(consultaSql, conn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("_id", id)
                conn.Open()
                Try
                    Return cmd.ExecuteNonQuery()
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Using

  End Function

No métodos IncluirAlterarDados e GetDados estamos usando argumentos opcionais pois um dos argumentos poderá ser ou não passado.

No VB .NET os parâmetros podem ser declarados como opcionais usando a palavra chave Optional, como mostrado no seguinte código:
Sub Calcular(Optional ByVal Switch As Boolean = False)

Os parâmetros opcionais devem ser declarados com o valor padrão o qual é passado para o procedimento se o programa chamador não fornecer o valor deste parâmetro.

As seguintes regras se aplicam aos argumentos opcionais:

Cada argumento opcional deve especificar um valor padrão, e esse padrão deve ser uma expressão constante (e não uma variável);
Todo argumento seguindo um argumento opcional também deve ser opcional;

Note que em versões anteriores do VB, você podia omitir o valor padrão e, se o parâmetro fosse do tipo Variant, você podia usar a função IsMissing para determinar se um valor tinha sido fornecido. Isto não é possível no VB. NET, e a função IsMissing não é mais suportada.

Outro detalhe é que o nome usado para os parâmetros não afeta o código, podemos usar qualquer nome para os parâmetros.

Definindo o código da interface do usuário

Vamos agora definir o código na interface do usuário que é a página Default.aspx.

Nota: Para facilitar o entendimento eu não criei uma camada de negócios então teremos na camada de interface código que numa aplicação mais robusta deverá ser movido para camada de negócios.

No evento Load da página Default.aspx estamos chamando a rotina carregaGrid()

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        carregaGrid()
    End Sub

A rotina carregaGrid() esta invocando o método estático GetDados() passando a instrução SQL : Select * From clientes order by id"

Lembre que o método GetDados possui a seguinte assinatura:

Public Shared Function GetDados(ByVal consultaSql As String, Optional ByVal nome As String = "") As DataTable

ou seja, o método espera receber a instrução SQL e uma parâmetro opcional nome do tipo string, retornando um DataTable.

A rotina carregaGrid() esta passando apenas a instrução SQL para obter todos os registros da tabela Clientes ordenados pelo campo ID

Private Sub carregaGrid()
        gdvClientes.DataSource = AcessoDB.GetDados("Select * From clientes order by id")
        gdvClientes.DataBind()
    End Sub

O retorno é um DataTable que atribuímos à propriedade DataSource do GridView - gdvClientes - para exibição do resultado.

No evento click do botão de comando Localizar que esta na frente do TextBox para Nome temos o código que chama a rotina localizaDados():

Protected Sub btnLocalizar_Click(sender As Object, e As EventArgs) Handles btnLocalizar.Click
        localizaDados()
    End Sub

A rotina localizaDados() esta invocando o método estático GetDados() passando os seguintes argumentos:

1 - a instrução SQL => "Select * From clientes Where Nome = ?"
2 - o nome do cliente informando no textBox => txtNome.Text)

Observe que na sintaxe da instrução SQL temos o parâmetro Nome seguindo do ponto de interrogação (?) para indicar que é esperado o valor do parâmetro.

A sintaxe usada no SQL Server seria : "Select * From clientes Where Nome = @nome"

Private Sub localizaDados()
        gdvClientes.DataSource = AcessoDB.GetDados("Select * From clientes Where Nome = ?", txtNome.Text)
        gdvClientes.DataBind()
    End Sub

No evento Click do botão Incluir estamos usando o método estático IncluirAlterarDados() onde estamos passando os seguinte parâmetros:

Instrução SQL para incluir dados na tabela Clientes: "Insert Into Clientes (Nome, Endereco, Email) Values (?,?,?)"
Os valores para os parâmetros usados na instrução SQL são os argumentos : nome, endereco e email.

Um parâmetro representa um valor que o procedimento espera receber quando for chamado. A declaração do procedimento define os seus parâmetros;
Um argumento representa o valor que você passa para um parâmetro de procedimento quando você chama o procedimento. O código de chamada fornece os argumentos quando ele chama o procedimento;

Note que na instrução SQL usamos 3 pontos de interrogação (?) para indicar os valores dos parâmetros.

Recorde que o método IncluirAlterarDados() usa um parâmetro opcional para o código do cliente (id) que usamos para realizar operações de alteração de dados. Assim o método pode ser usado para incluir e alterar dados.

Protected Sub btnIncluir_Click(sender As Object, e As EventArgs) Handles btnIncluir.Click
        Dim sql As String = "Insert Into Clientes (Nome, Endereco, Email) Values (?,?,?)"

        Dim nome As String = txtNome.Text
        Dim endereco As String = txtEndereco.Text
        Dim email As String = txtEmail.Text

        If String.IsNullOrWhiteSpace(nome) Or String.IsNullOrWhiteSpace(endereco) Or String.IsNullOrWhiteSpace(email) Then
            lblmsg.Text = "Valores inválidos. Preenche o nome, endereço e email."
        Else
            Try
                AcessoDB.IncluirAlterarDados(sql, nome, endereco, email)
                lblmsg.Text = "Dados incluídos com sucesso"
            Catch ex As Exception
                lblmsg.Text = ex.Message
            End Try
        End If
    End Sub

No evento Click do botão Atualizar chama a rotina carregaGrid() apenas para atualizar os dados exibidos no controle GridView.

 Protected Sub btnAtualizar_Click(sender As Object, e As EventArgs) Handles btnAtualizar.Click
        carregaGrid()
    End Sub

No botão Localizar (localizado à frente do campo ID) usamos novamente o método GetDados() passando os argumentos:

Select * From clientes Where ID = ?" - A instrução SQL para obter o cliente pelo seu ID
Convert.ToInt32(txtID.Text) - O valor do ID informando no TextBox convertido para inteiro.

Protected Sub btnLocalizaID_Click(sender As Object, e As EventArgs) Handles btnLocalizaID.Click
        gdvClientes.DataSource = AcessoDB.GetDados("Select * From clientes Where ID = ?", Convert.ToInt32(txtID.Text))
        gdvClientes.DataBind()
    End Sub

No evento Click do botão Alterar estamos usando o método estático IncluirAlterarDados() onde estamos passando os seguintes argumentos:

Instrução SQL para incluir dados na tabela Clientes: Update Clientes set Nome=?, Endereco=?, Email=? Where ID=?

Observe que usamos novamente o ponto de interrogação para indicar o valor do parâmetro esperado.

 Protected Sub btnAlterar_Click(sender As Object, e As EventArgs) Handles btnAlterar.Click
        Dim sql As String = "Update Clientes set Nome=?, Endereco=?, Email=? Where ID=?"

        Dim nome As String = txtNome.Text
        Dim endereco As String = txtEndereco.Text
        Dim email As String = txtEmail.Text
        Dim id As Integer = Convert.ToInt32(txtID.Text)

        If String.IsNullOrWhiteSpace(nome) Or String.IsNullOrWhiteSpace(endereco) Or String.IsNullOrWhiteSpace(email) 
Or String.IsNullOrWhiteSpace(txtID.Text) Then
            lblmsg.Text = "Valores inválidos. Preenche os dados para alterar : nome e/ou endereço e/ou email."
        Else
            Try
                AcessoDB.IncluirAlterarDados(sql, nome, endereco, email, id)
                lblmsg.Text = "Dados alterados com sucesso"
            Catch ex As Exception
                lblmsg.Text = ex.Message
            End Try
        End If
    End Sub

o evento Click do botão Excluir estamos usando o método estático ExcluirDados() onde estamos passando os seguintes argumentos:

A Instrução SQL para excluir o cliente pelo id informado:"Delete From Clientes Where ID=?"

Observe que usamos novamente o ponto de interrogação para indicar o valor do parâmetro ID.

Protected Sub btnExcluir_Click(sender As Object, e As EventArgs) Handles btnExcluir.Click
        Dim sql As String = "Delete From Clientes Where ID=?"

        Dim id As Integer = Convert.ToInt32(txtID.Text)

        If String.IsNullOrWhiteSpace(txtID.Text) Then
            lblmsg.Text = "Informe o ID para exclusão"
        Else
            Try
                AcessoDB.ExcluirDados(sql, id)
                lblmsg.Text = "Dados excluidos com sucesso"
            Catch ex As Exception
                lblmsg.Text = ex.Message
            End Try
        End If
    End Sub

No evento Click do botão Encerrar estamos usando o método RegisterStartupScript que emite um bloco de script do lado do cliente na resposta da página.

RegisterStartupScript - registra o bloco de script com o objeto Page.(Gera o script antes do fechamento da tag </form> ) A sintaxe é:

Public Sub RegisterStartupScript ( _ type As Type, _ key As String, _ script As String _ )
Public Sub RegisterStartupScript ( _ type As Type, _ key As String, _ script As String _ , _boolean As Boolean)

Este método irá fechar a janela aberta e chamar a página fechar.html.

Protected Sub btnSair_Click(sender As Object, e As EventArgs) Handles btnSair.Click
        ClientScript.RegisterStartupScript(GetType(Page), "closePage", "window.open('fechar.html', '_self', null);", True)
End Sub

Para criar a página fechar.html usamos a opção PROJECT->Add New Item -> HTML Page.

O código da página HTML é mostrado abaixo:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Sua aplicação foi encerrada com sucesso.</title>
</head>
<body>
    <img alt="" src="Imagens/maco.jpg" style="height: 26px; width: 148px" />" 
    <p>
        <h1>Sua aplicação foi encerrada com sucesso.</h1>
    </p>
</body>
</html>

O código é bem simples, apenas exibe uma imagem e uma mensagem ao usuário que aplicação foi encerrada.

Abaixo vemos o projeto em execução no servidor ASP .NET exibindo a página Default.aspx:

Pegue o projeto completo aqui: ConsultasParametrizadas_MSAccess.zip

João 7:6 Disse-lhes, então, Jesus: Ainda não é chegado o meu tempo; mas o vosso tempo sempre está presente.

João 7:7 O mundo não vos pode odiar; mas ele me odeia a mim, porquanto dele testifico que as suas obras são más.

Referências:

José Carlos Macoratti

Usando consultas SQL no MS - Macoratti.net

ASP.NET - Usando consultas armazenadas no Access - Macoratti.net

ASP.NET - JavaScript - Macoratti.net