VB .NET - Inserir, Selecionar, Atualizar e Deletar registros em uma única Stored Procedure no SQL Server

Às vezes há a necessidade de inserir, selecionar, atualizar e excluir registros de uma tabela usando um único procedimento armazenado em vez de criar procedimentos armazenados separados para cada operação.

Suponha que eu tenho uma página ASP .NET .aspx em que eu preciso inserir, selecionar, atualizar e excluir registros. Para fazer isso, em vez de criar quatro procedimentos armazenados para executar essas tarefas vamos criar um único procedimento armazenado para satisfazer as minhas necessidades e vamos acessá-lo via código no arquivo code-behind de acordo com a ação executada pelo usuário final em um evento clique de botão de comando.

O objetivo deste artigo é mostrar como criar stored procedures no SQL Server para realizar mais de uma tarefa. Como ele é destinado a iniciantes vou começar recordando alguns conceitos básicos...

O que é uma stored procedure ou procedimento armazenado ?

  1. As stored procedures podem ser vistas como programas que você armazena com um banco de dados que dá suporte a este recurso;
  2. Um procedimento armazenado é um grupo de comandos lógicos SQL para executar uma tarefa específica, como inserir, selecionar, atualizar e excluir operações em uma tabela e assim por diante, que é armazenado em um banco de dados SQL;
  3. Procedimento armazenado ou Stored Procedure é uma coleção de comandos em SQL que são armazenados em um Banco de dados que encapsula tarefas repetitivas, aceita parâmetros de entrada e retorna um valor de status (para indicar aceitação ou falha na execução). O procedimento armazenado pode reduzir o tráfego na rede, visto que, os comandos são executados diretamente no servidor. Além de melhorar a performance, criar mecanismos de segurança entre a manipulação dos dados do Banco de Dados. ( http://pt.wikipedia.org/wiki/Procedimento_armazenado)

A grande vantagem das stored procedures é que elas são pré-compiladas no banco de dados e assim serão executadas mais rapidamente. Siga a máxima: Use mas não Abuse...

Conceitos básicos sobre T-SQL (Transact SQL)

As stored procedures quando aplicadas ao SQL Server utilizam um conjunto de instruções Transact-SQL que são executadas no interior do banco de dados.

A Transact-SQL é como uma linguagem que utiliza comandos parecidos com os comandos de uma linguagem de programação como Visual Basic. Assim ele possui instruções de comparação (if), loops (while) operadores, variáveis, funções, etc.

Exemplo:

CREATE  PROCEDURE TESTE
AS
BEGIN
   SELECT   'EXEMPLO T-SQL - MACORATTI'
END

Note que temos o início do comando usando a palavra CREATE PROCEDURE seguido do nome da mesma - TESTE, e que temos a palavra BEGIN indicando o início do comando e a palavra END indicando o fim do comando:
SELECT   'EXEMPLO T-SQL - MACORATTI'

Essa é a estrutura básica de uma instrução T-SQL.

Podemos também utilizar instruções T-SQL que recebem parâmetros.

Exemplo:

CREATE PROCEDURE TESTE @PARAMETRO1 INT
AS
BEGIN

    UPDATE TABELA1 SET CAMPO1 = 'MACORATTI'
    WHERE CAMPO2 = @PARAMETRO1
END

A stored procedure acima utiliza um parâmetro chamado PARAMETRO1 do tipo INT que é usado na cláusula WHERE do comando SQL UPDATE para definir o critério de um campo para atualizar a tabela:
UPDATE TABELA1 SET CAMPO1 = 'MACORATTI' WHERE CAMPO2 = @PARAMETRO1

As instruções T-SQL podem também retornar valores que poderão ser capturados pela sua aplicação.

Exemplo:

CREATE PROCEDURE TESTE @PARAMETRO1 INT
AS
BEGIN
    SELECT @PARAMETRO1*100 AS VALOR
END

Neste exemplo poderemos capturar o retorno da Stored Procedure através do campo VALOR que contém o valor de retorno calculado como : @PARAMETRO1 * 100

Criando o banco de dados e a Stored Procedure

Vamos criar um banco de dados como exemplo para mostrar a criação da nossa stored procedure.

Eu poderia usar o SQL Server Management Studio ou outra ferramenta mas vou usar o Visual Basic Express 2010 Edition.

Abra o Visual Basic Express 2010 Edition e no menu View clique em Other Windows -> DataBase Explorer para exibir a janela do Database Explorer exibindo as conexões existentes:

A seguir clique com o botão direito do mouse sobre Data Connections e a seguir em Add Connection...

Na janela Add Connection escolha a opção para definir o Data Source. Estou usando a opção Microsoft SQL Server DataBase File;

Informe o nome do banco de dados que no exemplo será chamado de Cadastro e clique no botão OK;

Será solicitada a confirmação para criar o banco de dados. Clique no botão Sim;

O banco de dados será criado e exibido na janela DataBase Explorer. Vamos agora criar a tabela usada como exemplo no artigo.

Clique com o botão direito sobre Tables e selecione Add New Table;

A seguir vamos criar a tabela Funcionarios com a estrutura mostrada na figura abaixo:

Para incluir alguns dados na tabela clique com o botão direito sobre a tabela e selecione Show Table Data e digite os valores diretamente na tabela.

Agora que já temos o banco de dados Cadastro.mdf e a tabela Funcionarios já podemos criar nossa super poderosa Stored Procedure usando os comandos T-SQL

Clique com o botão direito do mouse sobre Stored Procedures e a seguir clique em Add New Stored Procedure;

Será aberta a janela para criação da stored procedure com um esqueleto da mesma já usando o comando CREATE PROCEDURE;

Abaixo temos a stored procedure ManutencaoFuncionario criada no banco de dados Cadastro.mdf e que permite incluir(Insert), selecionar(Select), atualizar (Update) e deletar (Delete) registros da tabela Funcionarios.

Criando a aplicação Windows Forms para testar a Stored Procedure

Eu vou usar um projeto Windows Forms bem simples apenas para mostrar que podemos usar a nossa stored procedure para realizar as operações CRUD em uma tabela do SQL Server;

Ainda no Visual Basic 2010 Express Edition crie um novo projeto do tipo Windows Forms Application com o nome ManutencaoFuncionarios;

Defina o formulário form1.vb os controles conforme mostra a figura abaixo onde temos:

Defina o seguinte namespace no formulário form1.vb:

Imports System.Data.SqlClient

A seguir, logo após a declaração da classe form1 defina as variáveis abaixo que representam a string de conexão com o banco de dados e a conexão SqlConnection:

Dim strSql As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\f5361091\Documents\Cadastro.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim conexao As SqlConnection

Agora em cada evento Click dos botões de comando vamos definir o respectivo código que usa a nossa Stored Procedure para a finalidade específica.

1- No evento Click do botão de comando btnIncluir temos o código onde usamos a stored procedure ManutencaoFuncionario para incluir um novo registro:

Private Sub btnIncluir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncluir.Click
        Try
            'cria a conexão com o banco de dados Teste
            conexao = New SqlConnection(strSql)
            conexao.Open()
            Dim cmd As New SqlCommand("ManutencaoFuncionario", conexao)
            'define o tipo stored procedure
            cmd.CommandType = CommandType.StoredProcedure
            'atribui os valores dos parametros
            cmd.Parameters.AddWithValue("@Action", "Insert")
            cmd.Parameters.AddWithValue("@nome", txtNome.Text)
            cmd.Parameters.AddWithValue("@sobrenome", txtSobrenome.Text)
            cmd.Parameters.AddWithValue("@email", txtEmail.Text)
            'executa a stored procedure
            cmd.ExecuteNonQuery()
            MsgBox("Dados incluídos com sucesso!")
        Catch ex As Exception
            MsgBox("Erro : " & ex.Message)
        Finally
            conexao.Close()
        End Try
    End Sub

2- No evento Click do botão de comando btnAtualizar temos o código onde usamos a stored procedure ManutencaoFuncionario para atualizar um registro:

 Private Sub btnAtualizar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAtualizar.Click
        Try
            'cria a conexão com o banco de dados Teste
            conexao = New SqlConnection(strSql)
            conexao.Open()
            Dim cmd As New SqlCommand("ManutencaoFuncionario", conexao)
            'define o tipo stored procedure
            cmd.CommandType = CommandType.StoredProcedure
            'atribui os valores dos parametros
            cmd.Parameters.AddWithValue("@Action", "Update")
            cmd.Parameters.AddWithValue("@id", txtCodigo.Text)
            cmd.Parameters.AddWithValue("@nome", txtNome.Text)
            cmd.Parameters.AddWithValue("@sobrenome", txtSobrenome.Text)
            cmd.Parameters.AddWithValue("@email", txtEmail.Text)
            'executa a stored procedure
            cmd.ExecuteNonQuery()
            MsgBox("Dados atualizado com sucesso!")
        Catch ex As Exception
            MsgBox("Erro : " & ex.Message)
        Finally
            conexao.Close()
        End Try
    End Sub

3- No evento Click do botão de comando btnExcluir temos o código onde usamos a stored procedure ManutencaoFuncionario para excluir um registro:

  Private Sub btnExcluir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcluir.Click
        Try
            'cria a conexão com o banco de dados Teste
            conexao = New SqlConnection(strSql)
            conexao.Open()
            Dim cmd As New SqlCommand("ManutencaoFuncionario", conexao)
            'define o tipo stored procedure
            cmd.CommandType = CommandType.StoredProcedure
            'atribui os valores dos parametros
            cmd.Parameters.AddWithValue("@Action", "Delete")
            cmd.Parameters.AddWithValue("@id", txtCodigo.Text)
            'executa a stored procedure
            cmd.ExecuteNonQuery()
            MsgBox("Dados excluídos com sucesso!")
        Catch ex As Exception
            MsgBox("Erro : " & ex.Message)
        Finally
            conexao.Close()
        End Try
    End Sub

4- No evento Click do botão de comando btnSelecionar temos o código onde usamos a stored procedure ManutencaoFuncionario para selecionar os registros da tabela Funcionarios:

Private Sub btnSelecionar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelecionar.Click
        Try
            'cria a conexão com o banco de dados Teste
            conexao = New SqlConnection(strSql)
            conexao.Open()
            'especifia a stored procedure usada
            Dim da As New SqlDataAdapter("ManutencaoFuncionario", conexao)
            'define o tipo stored procedure
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            'define o parametro
            da.SelectCommand.Parameters.Add(New SqlParameter("@Action", SqlDbType.VarChar, 10))
            'Atribui o valor do parametro
            da.SelectCommand.Parameters("@Action").Value = "Select"
            'cria e preenche o dataset
            Dim ds As New DataSet
            da.Fill(ds, "Funcionarios")
            'exibe o rsutlado
            gdvFuncionarios.DataSource = ds.Tables(0)
        Catch ex As Exception
            MsgBox("Erro : " & ex.Message)
        Finally
            conexao.Close()
        End Try
    End Sub

Em cada operação definimos os parâmetros necessários para que a stored procedure seja executada;

Abaixo vemos um exemplo de execução do projeto onde estamos incluindo um novo registro:

Eu não me preocupei em fazer validações no código pois o objetivo é apenas mostrar a utilização da stored procedure.

Pegue o projeto completo aqui: ManutencaoFuncionarios.zip

Mar 2:1 Alguns dias depois entrou Jesus outra vez em Cafarnaum, e soube-se que ele estava em casa.

Mar 2:2 Ajuntaram-se, pois, muitos, a ponta de não caberem nem mesmo diante da porta; e ele lhes anunciava a palavra.

Mar 2:3 Nisso vieram alguns a trazer-lhe um paralítico, carregado por quatro;

Mar 2:4 e não podendo aproximar-se dele, por causa da multidão, descobriram o telhado onde estava e, fazendo uma abertura, baixaram o leito em que jazia o paralítico.

Mar 2:5 E Jesus, vendo-lhes a fé, disse ao paralítico: Filho, perdoados são os teus pecados.

Referências:

José Carlos Macoratti