Introdução

O acesso a bancos de dados é uma necessidade comum à maioria das aplicações comerciais desenvolvidas nos últimos anos, afinal, essa é uma das principais “razões de ser” desses programas: armazenar os dados em uma base segura e de fácil manipulação.

Diariamente executamos dezenas ou centenas de scripts com as mais diversas finalidades, tais como consulta e atualização nos dados de um banco. Muitas vezes também é preciso executar vários scripts em sequência, de forma que um pode depender do anterior. Assim, se um falhar, os próximos não podem ser executados e os dados não podem sofrer alteração a menos que todos tenham obtido sucesso na execução.

Em situações como essa, a integridade dos dados é o fator de maior importância, por tanto, deve-se garantir que todas as regras predeterminadas para a execução dessas instruções sejam seguidas. Por exemplo, precisamos inserir valores em duas tabelas, mas isso só pode ser feito “aos pares”, ou seja, não devemos permitir a inserção em apenas uma tabela. Se a inserção em uma delas falhar, a outra não pode ser alterada.

Nesse contexto entra o conceito de transação, que podemos definir, a grosso modo, como o agrupamento de instruções (em bancos de dados) em um bloco lógico, de forma que o sucesso ou fracasso da execução desse bloco só podem ser totais. Ou seja, ou todas as instruções são executadas com sucesso, ou nada é persistido no banco.

Termos/comandos relacionados à transação

Quando trabalhamos com transações, existem basicamente três comandos fundamentais que são sempre utilizados:

  • Begin Transaction: esse comando, como o nome sugere, inicia a transação, abrindo o bloco de comandos a serem executados. Todas as instruções que precisem ser executadas devem estar após esse comando.
  • Commit Transaction: o comando commit efetiva a transação, ou seja, persiste no banco todas as alterações efetuadas no bloco. Após a execução do commit, não é possível reverter as modificações sofridas pelos dados na base.
  • Rollback Transaction: contrário ao commit, o rollback cancela a transação. Assim, todos os comandos executados no bloco da transação são descartados e a base de dados não sofre nenhuma alteração. Esse comando é geralmente utilizado caso ocorra algum erro na execução de uma das instruções do bloco.

Controlando transações no SQL Server com C#

Neste ponto entramos no principal objetivo do presente artigo, que é explicar como controlar transações durante a execução de instruções SQL no banco de dados SQL Server utilizando a linguagem C# e as classes do namespace System.Data.SqlClient.

Para os exemplos que serão apresentados, tomaremos o seguinte cenário: é preciso inserir registros em duas tabelas (Tabela1 e Tabela2), mas essas instruções só podem ser feitas juntas, se uma das duas falhar, a outra não deve ser efetivada.

Na Listagem 1 está o código que pode ser utilizado para criação de um database com as duas tabelas em questão.

Listagem 1: Script de criação do banco e tabelas

create database DBTeste
go

use DBTeste
go

create table Tabela1
(
	CampoTexto varchar(10)
)
go

create table Tabela2
(
	CampoNumerico int
)
go

Utilizaremos então uma aplicação do tipo “Console Application” e trabalharemos apenas na classe principal (Main) da mesma. Assim, primeiramente vejamos o código onde as instruções de insert são executadas, sequencialmente, sem o uso de uma transação.

Listagem 2: Execução das instruções com sucesso sem transação

class Program
{
    static void Main(string[] args)
    {
        SqlConnection con = new SqlConnection("String de Conexão com o DBTeste");

        SqlCommand cmd1 = con.CreateCommand();
        SqlCommand cmd2 = con.CreateCommand();

        cmd1.CommandText = "insert into Tabela1 (CampoTexto) values ('UM')";
        cmd2.CommandText = "insert into Tabela2 (CampoNumerico) values (2)";
        con.Open();

        try
        {
            cmd1.ExecuteNonQuery();
            Console.WriteLine("Comando 1 executado com sucesso!");
            cmd2.ExecuteNonQuery();
            Console.WriteLine("Comando 2 executado com sucesso!");
        }
        catch (SqlException ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            con.Close();
        }

        Console.ReadLine();
    }
}

No código acima executamos os comandos da maneira mais tradicional, um após o outro e de forma independente. Executando a aplicação, temos as mensagens de sucesso apresentada no console e os dados persistidos corretamente no banco, como já se esperava.

Execução do primeiro exemplo

Figura 1: Execução do primeiro exemplo

Dados persistidos após o primeiro exemplo

Figura 2: Dados persistidos após o primeiro exemplo

Nesse caso os dois comandos foram executados com sucesso, nenhuma exceção ocorreu. Vamos então simular um erro na execução do segundo comando. Como sabemos que a coluna CampoNumerico é do tipo int, podemos tentar inserir um valor inválido, por exemplo, um texto.

Tentaremos assim inserir o valor ‘DOIS’ na Tabela2, o que gerará um erro. Porém, o primeiro comando deve ser executado sem problemas e apenas a primeira tabela sofrerá alterações.

Observação: os dados inseridos após cada exemplo são excluídos, para facilitar a visualização do exemplo seguinte.

Listagem 3: Execução de comandos com erro sem transação

class Program
{
    static void Main(string[] args)
    {
        SqlConnection con = new SqlConnection("String de Conexão com o DBTeste");

        SqlCommand cmd1 = con.CreateCommand();
        SqlCommand cmd2 = con.CreateCommand();

        cmd1.CommandText = "insert into Tabela1 (CampoTexto) values ('UM')";
        cmd2.CommandText = "insert into Tabela2 (CampoNumerico) values ('DOIS')";

        con.Open();

        try
        {
            cmd1.ExecuteNonQuery();
            Console.WriteLine("Comando 1 executado com sucesso!");
            cmd2.ExecuteNonQuery();
            Console.WriteLine("Comando 2 executado com sucesso!");
        }
        catch (SqlException ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            con.Close();
        }

        Console.ReadLine();
    }
}

Executando o código, temos as seguintes mensagens no console:

Erro na execução do segundo comando

Figura 3: Erro na execução do segundo comando

De fato houve um erro na execução do segundo comando (erro ao tentar converter o valor ‘DOIS’ para o tipo inteiro), porém, se listarmos os registros das duas tabelas, veremos que a primeira sofreu alterações, pois o registro foi inserido normalmente. Isso desrespeita a regra previamente definida, onde foi dito que o registros só poderiam ser inseridos aos pares.

Dados persistidos após o segundo exemplo

Figura 4: Dados persistidos após o segundo exemplo

Para corrigir essa falha utilizaremos a classe SqlTransaction, que nos permite controlar a transação, efetuando o commit ou rollback nos casos de sucesso ou falha, respectivamente, de todas as instruções executadas.

Listagem 4: Executando comandos dentro de uma transação

class Program
{
    static void Main(string[] args)
    {
        SqlConnection con = new SqlConnection("String de Conexão com o DBTeste");

        SqlCommand cmd1 = con.CreateCommand();        
        SqlCommand cmd2 = con.CreateCommand();

        cmd1.CommandText = "insert into Tabela1 (CampoTexto) values ('UM')";
        cmd2.CommandText = "insert into Tabela2 (CampoNumerico) values ('DOIS')";

        con.Open();

        SqlTransaction tran = con.BeginTransaction();       

        try
        {
            cmd1.Transaction = tran;
            cmd1.ExecuteNonQuery();
            Console.WriteLine("Comando 1 executado com sucesso!");

            cmd2.Transaction = tran;
            cmd2.ExecuteNonQuery();
            Console.WriteLine("Comando 2 executado com sucesso!");

            tran.Commit();
        }
        catch (SqlException ex)
        {
            tran.Rollback();
            Console.WriteLine(ex.Message);
        }
        finally
        {
            con.Close();
        }

        Console.ReadLine();
    }
}

Executando a aplicação, temos no console as mesmas mensagens exibidas no caso anterior, mas se verificarmos no banco, nenhum dado foi inserido em nenhuma das tabelas (Figura 4).

Dados persistidos após o terceiro exemplo

Figura 5: Dados persistidos após o terceiro exemplo

Basicamente foi feito o seguinte:

  • A SqlTransaction foi instanciada e aberta pelo método BeginTransaction da SqlConnection;
  • Os SqlCommands foram ligados à transação pela propriedade Transaction, associando-a ao objeto SqlTransaction criado;
  • Após a execução dos comandos, efetivamos a transação através do método Commit;
  • Caso ocorra alguma exceção, cancelamos a transação através do método Rollback.

Observação: aqui não foram aplicados conceitos como refatoração, a fim de deixar o código mais enxuto. Pelo contrário, o código foi deixado da forma mais detalhada possível para facilitar a compreensão.

Conclusão

Vimos nesse artigo como funcionam as transações e o quão úteis podem ser na garantia da integridade das informações a serem gravadas em bancos de dados. Além dos principais conceitos a cerca de transações, vimos como utilizá-las em aplicações desenvolvidas na linguagem C#.

O mesmo pode ser feito utilizando a linguagem Visual Basic .NET, posto que os recursos aqui utilizados são do .NET Framework, sobre o qual atuam as lingugens C#, VB.NET, entre outras.

Até a próxima publicação.