VB.NET - Criando Procedimentos Armazenados no MsAccess

Em diversos artigos já publicados eu tratei do assunto procedimentos armazenados, veja relação abaixo. Creio que a maioria tratou do assunto relacionado com o SQL Server , devido as limitações do banco de dados Microsoft Access.

VB.NET - Usando stored procedures no SQL Server
ADO - Usando Procedimentos Armazenados
ADO - Exibindo Stored Procedures do SQL Server
VB.NET - Brincando com Stored Procedures no SQL Server
ASP - Stored Procedures : migrando do Access para o SQL Server

Acontece que nas novas versões do Access a Microsoft realizou um grande esforço para superar algumas destas limitações e trazer o produto MsAccess mais perto de um verdadeiro banco de dados relacional.. Uma boa notícia é que as Stored Procedures podem ser usadas no Access , mais precisamente a partir versão do Jet Engine 4.0. (No Access97 você já podia trabalhar com consultas armazenadas)

Como os procedimentos armazenados (Stored Procedures) funcionam no Access

Os procedimentos armazenados não possuem uma interface com o usuário e não podem ser criados usando uma interface. (nada é perfeito...)

A única forma , pelo menos até a versão que conheço que é a 2002, é via código. Isto mesmo você tem que codificar para poder usar e trabalhar com eles no Access.

Quando um procedimento armazenado é incluído no Access o Jet Engine 'compila' o procedimento armazenado em um objeto de consulta. A vantagem é que isto fica transparente para o desenvolvedor de modo que você pode abstrair a camada de dados tornando a sua aplicação mais escalável , ou seja, você pode migrar para outro banco de dados mais facilmente sem ter que alterar todo o seu código.

Criando Procedimentos Armazenados

Vamos então criar nosso primeiro procedimento armazenado no Access. Eu vou usar o banco de dados Northwind.mdb que vem com o Access, assim todos podem acompanhar , e eu não tenho que enviar o banco de dados. OK !. Vou trabalhar com a tabela Products pois não é uma tabela com muitos registros nem muitos campos. Só para lembrar a estrutura desta tabela é dada abaixo:

Vou usar também a tabela Categories no exemplo da inclusão de registros. A estrutura desta tabela é a seguinte :

Nosso primeiro procedimento armazenado irá selecionar todos os registros da tabela Products. A sintaxe é a seguinte :

"CREATE PROC spListaProdutos AS SELECT * FROM Products"

Este código cria o procedimento armazenado - spListaProdutos. (Obs: sp são as iniciais de stored procedures)

parte do código que cria efetivamente o procedimento armazenado é : CREATE PROC spListaProdutos

a parte que segue a palavra chave AS refere a consulta Seleção SQL : SELECT * FROM Products

Se você deseja poder passar um valor para ser usado pela consulta SQL pode fazer isto sem problema. Vamos supor que você queira deletar uma linha da tabela Products que tenha um determinado código de Produto (ProductID). Veja abaixo como ficaria este procedimento:

"CREATE PROC spDeletaProduto(CodigoProduto LONG) AS DELETE FROM Products WHERE ProductID = CodigoProduto;"

perceba que após a utilização do comando para criar o procedimento armazenado foram usados parênteses para definir o parâmetro CodigoProduto como sendo do tipo LONG. Esta variável é que será usada para informar o valor do código do produto que você quer excluir.

Vamos ver agora como criar um procedimento armazenado para inclusão de dados na tabela Categories.

"CREATE PROC  spIncluiCategoria(nomeCategoria VARCHAR(15),descricao TEXT, foto IMAGE) " & _
"INSERT INTO Categories(CategoryName,Description,Picture) " & _
"VALUES(nomeCategoria,descricao,foto)"

Se fosse fazer para a tabela Products teria o seguinte resultado:

"CREATE PROC spIncluiProduto(nomeProduto VARCHAR(40), codFornecedor LONG, codCategoria LONG, quantidade VARCHAR(20), " & _

"precoUnitario MONEY, uniEstoque LONG, uniPedido LONG, nivelUnidade LONG, continuado byte) " & _

"AS INSERT INTO Products (ProductName, SupplerID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) " & _

"VALUES (nomeProduto, codFornecedor, codCategoria, quantidade, precoUnitario, uniEstoque, uniPedido, nivelUnidade, continuado)"

Agora um exemplo para atualizar a tabela:

"CREATE PROC spAutalizaProdutos(codigoProduto LONG, nomeProduto VARCHAR(40)) AS UPDATE Products SET ProductName = " & _
                                              "nomeProduto  WHERE ProductID = codigoProduto;"

Para encerrar esta primeira parte cabe dizer que existem algumas limitações :

- Parâmetros de saída ainda não são permitidos (pelo menos até a versão 2002)
- Tabelas temporárias não são disponíveis
- A sintaxe Transact SQL usada no SQL Server não é totalmente compatível , portanto , cuidado com o copiar e colar.

Bem , agora vamos mostrar o código VB.NET que irá criar todas estes procedimentos no banco de dados Northwind.

Crie um novo projeto no VS.NET do tipo Console Application e inclua o Módulo CriaSP() conforme o código abaixo:

Imports System
Imports System.Data
Imports System.Data.OleDb

Module criaSP

    Sub Main()

        ProcedimentosArmazenadosProdutos()

    End Sub

    ' Procedimentos Armazenados a serem incluidos no banco de dados
    Sub ProcedimentosArmazenadosProdutos()
        Dim strSQL As String

        ' spListaProdutos - retorna todos os registros da tabela produtos
        strSQL = "CREATE PROC spListaProdutos AS SELECT * FROM Products;"
        criaProcedimentoArmazeando(strSQL)

        ' spDeletaProduto - Efetua a exclusão de uma linha da tabela Products com base 
        ' em um código de produto informado como parãmetro
        strSQL = "CREATE PROC spDeletaProduto(codigoProduto LONG) " & _
               "AS DELETE FROM Products WHERE ProductID = codigoProduto;"
        criaProcedimentoArmazeando(strSQL)

      'spIncluiProduto - Inclua uma linha na tabela Products
      strSQL = "CREATE PROC spIncluiProduto(nomeProduto VARCHAR(40), codFornecedor LONG, codCategoria LONG, quantidade VARCHAR(20), " & _
                "precoUnitario MONEY, uniEstoque LONG, uniPedido LONG, nivelUnidade LONG, continuado byte) " & _
                "AS INSERT INTO Products (ProductName, SupplerID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) " & _
                "VALUES (nomeProduto, codFornecedor, codCategoria, quantidade, precoUnitario, uniEstoque, uniPedido, nivelUnidade, continuado)"
        criaProcedimentoArmazeando(strSQL)

        ' spAtualizaProdutos - atualiza itens da tabela Products
        strSQL = "CREATE PROC spAtualizaProdutos(codigoProduto LONG, nomeProduto VARCHAR(40)) " & _
                     "AS UPDATE Products SET ProductName = nomeProduto WHERE ProductID = codigoProduto;"
        criaProcedimentoArmazeando(strSQL)

    End Sub

    ' Executa a criação dos procedimentos armazenados
    Sub criaProcedimentoArmazeando(ByVal strSQL As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand
        Dim da As OleDbDataAdapter

        ' Verifique se a localização do banco de dados é mesma no seu ambiente
        Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=d:\teste\Northwind.mdb"

        con = New OleDbConnection(sConStr)

        cmd.Connection = con
        cmd.CommandText = strSQL

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

End Module

 

No código acima temos duas rotinas básicas:

1 -  ProcedimentosArmazenadosProdutos() - que constrói e  monta cada um dos procedimentos armazenados

2-   criaProcedimentoArmazeando(strSQL) - que efetua a conexão com o banco de dados e excuta o comando para criar o procedimento armazenado no banco de dados Northwind.mdb

Após executar o código acima se você abrir o banco de dados Northwind.mdb e ir na guia Consultas irá ver os procedimentos armazenados ali criados:



Agora com um recurso tão poderoso a sua disposição você deve começar a pensar a usá-lo em suas aplicações com acesso a dados.

O código completo você pega aqui : spAccess.zip

Veja a continuação deste artigo que mostra como usar os procedimentos em :

VB.NET - Usando Procedimentos Armazenados no MsAccess I

Até breve ...


Veja os Destaques e novidades do SUPER DVD VB (sempre atualizado) : clique e confira !

Quer migrar para o VB .NET ?

Veja mais sistemas completos para a plataforma .NET no Super DVD .NET , confira...

Quer aprender C# ??

Chegou o Super DVD C# com exclusivo material de suporte e vídeo aulas com curso básico sobre C#
 

   Gostou ?   Compartilhe no Facebook    Compartilhe no Twitter

Referências:

José Carlos Macoratti