Utilizando Stored Procedures com Transaction em MySql e C#

Natal DevMedia: Assine hoje e ganhe 3 meses gr�tis

Nesse artigo eu utilizei a transaction dentro da SP (Stored Procedure), mas o C# contem recursos que auxiliam a utilizar as transa��es na propria linguagem, mas isso fica para um proximo artigo.

Bem, vamos primeiro apresentar alguns conceitos:

Stored Procedure:

� um procedimento que fica armazenado no pr�prio banco de dados, dando a possibilidade de trabalhar com SQL de uma maneira mais parecida com a que usamos em linguagem de programa��o que n�o seja SQL. Por exemplo podemos usar variaveis, instru��es condicionais, loops, alem de podermos agrupar varios comandos SQL dentro de um s� procedimeito, ai que entram as transactions.
Transaction:
Podemos definir uma transaction como sendo um meio de garantir que um ou mais comandos SQL sejam executados sem erros, tendo a possibilidade de desfazer os comandos que estejam dentro da transaction caso ocorra algum problema.

Um exemplo � onde temos duas tabelas quaisquer, onde temos que inserir dados em ambas, porem, caso a inser��o na segunda tabela de algum erro n�s teremos como reverter a inser��o da primeira tabela. Isso ficara mais claro no decorrer do artigo. Para usar transactions � necessario que as tabelas sejam criadas com a engine InnoDB.

Para nosso caso, criaremos duas tabelas (cliente e endereco), eu utilizei o Mysql Query Browser para cria-las, utilizando um script tab, mas voc� pode, se quiser, utilizar a linha de comando do mysql, fica a seu crit�rio.

Segue os comandos de cria��o das tabelas:

tabela cliente:

CREATE TABLE cliente (
  codCliente INT NOT NULL AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
  PRIMARY KEY(codCliente)
)Engine=InnoDB;

Tabela endereco:

CREATE TABLE endereco (
  codEndereco INT NOT NULL AUTO_INCREMENT,
  idCliente INT NOT NULL,
  rua VARCHAR(100) NOT NULL,
  PRIMARY KEY(codEndereco),
  FOREIGN KEY(idCliente) REFERENCES cliente(codCliente)
)Engine=InnoDB;

Como voc�s puderam ver, a tabela cliente cont�m somente codigo e nome do cliente. A tabela endereco contem o c�digo do endere�o, o id co Cliente e a rua(ja esta bom s� pra aprender xD).

S� pode existir um endere�o se existir um cliente ao qual esse endere�o sera apontado (campo idCliente da tabela endereco), por isso foi feito uma chave estrangeira apontando o endereco.idCliente para cliente.codCliente.

Vamos criar a stored procedure que ser� responsavel pela inser��o dos dados nas duas tabelas. Abra um novo Script Tab e cole o conteudo a seguir. A procedure esta documentada para um f�cil entendimento.
N�o assuste com o tamanho da procedure, pois a maioria abaixo s�o comentarios.

/* Mudando o delimitador de comandos, para que possamos utilizar
  o ;(ponto e virgula) no final de cada linha em nossa procedure
*/
DELIMITER $$

/* Caso ela ja exista, apagamos a procedure para depois criarmos novamente */
DROP PROCEDURE IF EXISTS `spInsere` $$

/* Aqui damos inicio � cria��o da procedure. A Sintaxe � a seguinte:
  CREATE PROCEDURE `nomeprocedure` (lista de parametros separados por virgula)
  Os parametros tem o seguinte formato:
  IN nomeparametro tipo
  Esse IN indica que esse par�metro sera de entrada, ou seja, sera atravez
  dele que ser�o passados dados para nossa procedure.
  Tambem poderiamos ter ao inv�s de IN, um ou mais parametros OUT, caso
  quisessemos enviar dados de dentro da procedure para fora, mas no caso do
  arquivo utilizei somente selects mesmo
  para passar as mensagens para fora da procedure.
*/
CREATE PROCEDURE `spInsere`(IN n VARCHAR(100), IN r VARCHAR(100))
/* Aqui inicia-se o corpo da procedure */
BEGIN
/* Abaixo fica a declara��o da variavel excessao que sera um
  inteiro pequeno e � inicializada com 0;
  A segunda linha declare indica que quando ocorrer alguma excess�o em
  algum comando, essa vari�vel excessao ser� preenchida com o valor 1, tornando
  possivel verificar se houve algum problema.
*/
  DECLARE excessao SMALLINT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET excessao = 1;

/* Aqui verificamos se os par�metros n�o est�o vazios, para n�o inserirmos
  campos em branco no banco.
  Caso ambos estejam preenchidos, inicio a transaction com o comando
  START TRANSACTION.
*/
  IF (n <> "" AND r <> "") THEN
    START TRANSACTION;

    /* aqui inserimos o nome passado por par�metro na tabela cliente */
    INSERT INTO cliente VALUES(null, n);

    /* Caso a variavel contenha 1, ou seja, caso tenha ocorrido algum erro
      Retornamos uma mensagem de erro, atravez da variavel Msg, e logo em
      seguida executamos o comando ROLLBACK.
      Esse comando que � o responsavel por desfazer toda e qualquer
      altera��o que tenhamos feito no banco.
    */
    IF excessao = 1
    THEN
      SELECT 'Erro ao inserir na tabela cliente' AS Msg;
      ROLLBACK;
    ELSE
      /* Caso excessao ainda seja 0, ele continua aqui, onde executamos
      o proximo comando cuja fun��o � retornar o ultimo ID(chave primaria)
      que foi inserido na tabela cliente e o guarda na variavel @idCliente
      */
      SELECT DISTINCT LAST_INSERT_ID() INTO @idCliente FROM cliente;
      /* De novo, caso tenha ocorrido erro, imprime mensagem e execura
      um ROLLBACK */
      IF excessao = 1
      THEN
        SELECT 'Erro ao selecionar o ultim ID inserido' AS Msg;
        ROLLBACK;
      ELSE
        /* Aqui iremos inserir os dados na tabela endereco, utilizando a
        variavel com o id do ultimo cliente inserido, que pegamos no
        comando SELECT acima, e tambem o parametro contendo a rua.
        */
        INSERT INTO endereco VALUES(null, @idCliente, r);
        /* caso tenha erro, mensagem e ROLLBACK */
        IF excessao = 1
        THEN
          SELECT 'Erro ao inserir na tabela  endereco' AS Msg;
          ROLLBACK;
        ELSE
          /* Se chegamos at� esse ponto da procedure, � por que todos os
          comandos foram bem sucedidos ent�o podemos executar o
          comando COMMIT.
          O COMMIT diz ao banco que todos os comandos foram executados
          com sucesso e pode finalizar a transa��o.
          */
          SELECT 'Cadastro efetuado com sucesso' AS Msg;
          COMMIT;
        END IF;
      END IF;
    END IF;
  ELSE
    /* Como est�o faltando parametros, est�o vazios, imprime-se a mensagem
     a seguir.*/
    SELECT 'Parametros necessarios' AS Msg;
  END IF;

/* aqui finalizamos a procedure*/
END $$

/* Nesse pondo indicamos que agora usaremos novamente o 
delimitador ;(ponto e virgula) para separar comandos
*/
DELIMITER ;

Ap�s isso, vamos criar nosso projeto no Visual Studio. Aqui eu utilizei o VS 2008, porem acredito que o exemplo funcionar� tambem com o 2005. O unico requisito � ter o conector do mysql para .NET que pode ser adiquirido no endere�p http://dev.mysql.com/downloads/connector/net/5.2.html. � s� baixar a vers�o noinstall, descompactar, e adicinar a referencia em seu projeto ao arquivo mysql.data.dll que se encontra na pasta do conector do mysql. Logo estarei colocando algum artigo explicando em detalhes como configurar o visual studio para se conectar ao mysql.

Crie um novo projeto Windows Forms utilizando a linguagem C#. No meu caso coloquei o nome do projeto como spTransaction. Ap�s criar o projeto, insira dois textbox, dois labels, e um bot�o no formulario e altere as propriedades como a seguir:

Obs: Coloque os componentes na sequencia que foi listada abaixo. De cima para baixo.

Primeiro label:
nome: lbNome;
text: Nome do cliente

Primeiro TextBox:
nome: txtNome;

Segundo label:
nome: lbEndereco;
text: Endere�o do cliente

Segundo TextBox:
nome: txtEndereco;

Botao:
nome: btnSalvar;
text: Salvar Dados;

Depois de inserir os componentes e alterar os atributos acima citados, de um duplo clique no bot�o btnSalvar e deixe o m�todo click dele como o a seguir. Explicarei o m�todo nos coment�rios do mesmo:

private void btnSalvar_Click(object sender, EventArgs e)
{
   /* variavel que contem a string de conex�o que usaremos.
      mude os campos da conex�o conforme o seu banco de dados, colocando
     o host, nome do banco, usuario e senha.
     A �ltima op��o: Use Procedure Bodies=false; � usada para que
     possamos usar stored procedures do mysql usando o conector
     que baixamos.
   */
   string strCon = "Server=localhost;Database=meubanco;" +
                   "Uid=meuusuario;Pwd=minhasenha;" +
                   "Use Procedure Bodies=false;";
   /* Abaixo criamos a conex�o em si, utilizando a string acima*/
   MySqlConnection con = new MySqlConnection(strCon);
   /* Agora � o bloco try que executara todos os comandos*/
   try
      {
         /* abrindo a conex�o */ 
         con.Open();
         /* criando o comando sql indicando a nossa conex�o e a nossa
         procedure */
         MySqlCommand cmd = new MySqlCommand("spInsere", con);
         /* aqui indicamos que usaremos stored procedure como tipo de comando*/
         cmd.CommandType = CommandType.StoredProcedure;
         /* aqui passamos os parametros para a procedure spInsere que criamos
         de acordo com os textbox*/
         cmd.Parameters.AddWithValue("n", txtNome.Text);
         cmd.Parameters.AddWithValue("r", txtEndereco.Text);
         /* Nessa linha, executamos nosso comando, pegando a variavel Msg
         que criamos em nossa procedure que contem as mensagems
         de erro ou sucesso do procedimento.
         */
         string retorno = cmd.ExecuteScalar().ToString();
         /* mostramos a mensagem na tela.*/
         MessageBox.Show(retorno);
      }
      catch (Exception ex)
      {
         MessageBox.Show("Houve problemas. Erro: \n\n" + ex.Message);
      }
      finally
      {
         /* se a conex�o esta aberta, a fechamos */
         if (con.State == ConnectionState.Open) con.Close();
      }
}

Pronto, agora � s� dar um F5 e verificar se est� tudo correto.

Tente inserir algo deixando um ou ambos os campos em branco, para ver se a valida��o da procedure esta valento. Um teste que podemos fazer, � alterar o nome da variavel @idCliente na inser��o do endere�o, para que de um erro e n�o seja possivel inserir nada em endere�o. Fazendo assim, poderemos constatar que mesmo a inser��o de cliente tendo ocorrido antes, o ROLLBACK desfez essa inser��o, deixando o banco com sua integridade inicial.

Bem, � isso ai.

Espero o comentario de todos, pois com cr�ticas melhoramos cada vez mais.

At� o proximo artigo.

Qualquer duvida, entre em contato: contato@thiagoapadua.com.br

Confira outros conte�dos:

Por Thiago Em 2010

Coment�rios nesta publica��o Escrever um coment�rio sobre conte�do

Cesar Murilo

Eu gostaria que, se o delete falhasse, tamb�m revertesse a cria��o da tabela de backup, mas isto n�o est� acontecendo, eu cancelo o script no terminal linux, com ctrl+c, e depois quando vou ver a tabela de backup permanece, por que?

/usr/bin/mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf $ <<EOFMYSQL
DELIMITER $$
USE $ $$
DROP PROCEDURE IF EXISTS sp_monthly_table_backup $$
CREATE PROCEDURE sp_monthly_table_backup(IN tb varchar(30), IN tmclmn varchar(30), OUT Msg varchar(100))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
SELECT "FAILED: Routine failed" As Msg;
END;

START TRANSACTION;

SELECT CONCAT(tb,"_",YEAR(NOW()), MONTH(NOW())-1) INTO @tb_pmonth;
SET @str=CONCAT("CREATE TABLE IF NOT EXISTS $.",@tb_pmonth," SELECT * FROM ",tb,";");
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @str2=CONCAT("DELETE FROM ",tb," WHERE ",tmclmn," < date_sub(NOW(), INTERVAL 1 MONTH)");
PREPARE stmt2 FROM @str2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
SELECT "SUCCESS: routine successfully executed" As Msg;

COMMIT;
END$$
DELIMITER ;
EOFMYSQL

há +1 ano

Ver coment�rio anterior

Renato Dias

fala Cesar! Conforme � demonstrado no c�digo de exemplo desse artigo voc� precisa criar uma vari�vel que registra um valor se ocorrer uma exce��o. Ao verificar o valor da vari�vel com um "if" se houver exce��o realize um ROLLBACK e sen�o realiza o COMMIT.

há +1 ano

Cesar Murilo

Ser� que h� algo errado no meu c�digo abaixo, porque quando pe�o para listar as procedures, trava e fica s� o '->' no terminal. Se puderem me ajudar com urg�ncia, agradeceria muito; � problema no servi�o.

DELIMITER $$ DROP PROCEDURE IF EXISTS 'sp_radacct_montly_backup' $$
CREATE PROCEDURE 'sp_radacct_montly_backup'()
BEGIN
DECLARE radacct_pmonth varchar(100);
DECLARE excessao SMALLINT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET excessao = 1;
DECLARE radacct_pmonth varchar(100);
SET raddacct_pmonth:=(SELECT CONCAT('radacct_',YEAR(NOW()), MONTH (NOW())-1 AS radacct_pmonth);
START TRANSACTION
CREATE TABLE IF NOT EXISTS radacct_pmonth;
IF excessao = 1
THEN
SELECT 'Erro ao criar copia da tabela raddacct' As Msg;
ROLLBACK;
ELSE
DELETE FROM radius.radacct WHERE acctstoptime < date_sub(NOW(), INTERVAL 1 MONTH);
IF excessao = 1
THEN
SELECT 'Erro ao remover registros com mais de um mes' AS Msg; ROLLBACK;
ELSE
COMMIT;
END IF;
END IF;
END $$ DELIMITER;

MariaDB [radius]> SHOW PROCEDURE STATUS;
->

há +1 ano

Ver coment�rio anterior

Renato Dias

oi Cesar! Voc� cometeu alguns erros pequenos mas que comprometeram a instru��o. Reproduzi essa instru��o em um banco de teste e fiz as devidas corre��es para executar todas tarefas delimitadas. vou listar aqui e voc� pode usar como base.

DELIMITER $$ 
 $$ USE teste $$ 
 $$ DROP PROCEDURE IF EXISTS backup_mensal $$ 
 CREATE PROCEDURE backup_mensal() 
 BEGIN 
 DECLARE pessoas_mensal varchar(100); 
 DECLARE excessao SMALLINT DEFAULT 0; 
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET excessao = 1; 
 
 SET pessoas_mensal = (SELECT CONCAT('pessoas_',YEAR(NOW()), MONTH (NOW())-1)); 
 START TRANSACTION; 
 CREATE TABLE IF NOT EXISTS pessoas_mensal; 
 IF excessao = 1 
 THEN 
 SELECT 'Erro ao criar copia da tabela pessoas_mensal' As Msg; 
 ROLLBACK; 
 ELSE 
 DELETE FROM pessoas WHERE gravadoEm < date_sub(NOW(), INTERVAL 1 MONTH); 
 IF excessao = 1 
 THEN 
 SELECT 'Erro ao remover registros com mais de um mes' AS Msg; ROLLBACK; 
 ELSE 
 COMMIT; 
 END IF; 
 END IF; 
 END; 
 $$ 
 DELIMITER ;