Usando transações em atualizações de múltiplas tabelas

VB.NET 2005 - Usando transações em atualizações de tabelas

Conhece a regra de ouro para atualizações de múltiplas tabelas em um processo ? Se você não conhece vai conhecer agora, a regra diz o seguinte : "Todas as atualizações realizadas em um único processo que afetam mais de uma tabela deverão estar sobre o controle de uma transação"

Pois é, vivendo e aprendendo.... E olha que não é a primeira vez que eu trato deste assunto em meus artigos. Para detalhes siga o link abaixo:

ADO.NET - Usando Transações. Usando transações com ADO.NET. Commit , RollBack , BeginTransaction,etc...

Indo diretamente ao assunto este artigo irá mostrar como usar transações em atualizações de tabelas no SQL Server 2005 fazendo, quando pertinente um paralelo com a ADO, assim se você esta migrando vai perceber que não há nada de novo debaixo deste céu...

Como irei fazer o acesso a uma base de dados SQL Server estarei usando o namespace System.Data.SqlClient e os provedores e objetos relacionados. Desta forma temos que o objeto SqlTransaction possibilita a habilidade de confirmar(commit) ou , em caso de uma exceção, cancelar (rollback) as atualizações que seriam realizadas durante o processo nas tabelas de uma fonte de dados do SQL Server.

Trabalhar com transações ADO.NET é muito parecido com os objetos fornecidos pela ADO em ADODB.Connection, os quais também incluem os métodos : BeginTrans, CommitTrans e RollBackTrans.

A ADO.NET fornece os seguintes métodos correspondentes no objeto SqlTransaction : BeginTransaction, CommitTransaction e RollBackTransaction.

Nota: A ADO.NET permite que você seletivamente alistar comandos em um transação ativa. A ADO não suporta este recurso.

A execução de uma transação ADO.NET pode ser dividida nas seguintes etapas:

Defina uma transação local como um objeto SqlTransaction, OleDbTransaction ou OdbcTransaction;
Faça a chamada do método BeginTransaction com um argumento enumeração IsolationLevel opcional;
Aliste os comandos na transação pela sua propriedade Transaction;
Invoque o método ExcecuteNonQuery para cada comando;
Invoque o método Commit da transação para efetivar as alterações;
Se ocorrer uma exceção chame o método RollBack da transação para desfazer as alterações.

Um trecho de código relacionado com as etapas descritas poderia ser escrito assim:

Private Shared Sub Teste()
'define a conexão
Dim con As SqlConnection = New SqlConnection("sua string de conexão")
'define o objeto SqlTransaction
Dim transaction As SqlTransaction
con.Open
'Inicia a Transação
transaction = con.BeginTransaction
Try
'relaciona os comandos e executa-os
call (New SqlCommand("INSERT INTO Tabela1 " + "(Texto) VALUES ('teste1');", con, transaction)).ExecuteNonQuery
call (New SqlCommand("INSERT INTO Tabela1 " + "(Texto) VALUES ('teste2');", con, transaction)).ExecuteNonQuery
call (New SqlCommand("INSERT INTO Tabela2 VALUES " + "('A', 'B', 'C');", con, transaction)).ExecuteNonQuery
'efetua o commit e confirma as alterações
transaction.Commit
Catch sqlError As SqlException
'se ocorreu uma exceção desfaz as alterações
transaction.Rollback
End Try
con.Close
End Sub

Existe um conceito que as transações suportam que é conhecido por nível de isolamento (isolation level). Um nível de isolamento representa uma estratégia particular de bloqueio empregada no sistema de banco de dados para melhorar a consistência dos dados. Quanto maior o nível de isolamento mais complexo a estratégia de bloqueio usada.

Assim, um nível de isolamento fornecido pelo banco de dados determina se uma transação irá encontrar comportamentos definidos na consistência dos dados.

A tabela abaixo relaciona os enumerações para os níveis de isolamento da ADO.NET(IsolationLevel) e da ADO(IsolationLevelEnum) traçando um paralelo entre as duas:

ADO.NET	ADODB	ADO.NET - Descrição
Chaos	AdXactChaos	Previne as mudanças pendentes das transações de maior isolamento de serem sobrescritas
ReadCommitted	AdXactReadCommitted	Evita os dirty reads mas permite non-repeatable e linhas fantasmas.
ReadUncommitted	AdXactReadUncommitted	Permite os dirty reads, linhas non-repetable e phantom rows (linhas fantasmas)
RepeatableRead	AdXactRepeatableRead	Evita non-repeatable reads mas permite phantom rows (linhas fantasmas)
Serializable	AdXactSerializable	Previne os dirty reads, non-repeatable reads e phantom rows pela colocação de um intervalo de bloqueio nos dados que estão sendo atualizados.
SnapShot	--- não possui --	Armazena a versão dos dados do SQL Server que os clientes podem ler quando outro cliente modifica os mesmos dados.
Unspecified	AdXactUnspecified	Indica que o provedor esta usando um nível de isolamento desconhecido.

Nota: As definições listadas a seguir ajudam a entender alguns do termos usados na tabela acima: (forma baseados no artigo: http://www.linhadecodigo.com.br/artigos.asp?id_ac=666 de autoria de Thiago Pastorello)

Dirty read
A conexão está lendo informações que ainda não sofreram um Commit, i,e, a informação esta sendo lida pode não existir ou mesmo ter sido modificada durante o processo.
Non-repeatable read
Quando em uma transação são lidas informações mais de uma vez, na primeira leitura pode ter vindo uma informação e na segunda e demais leituras podem ter vindo outras informações, ou seja, a consistência da informação dentro da mesma transação não esta garantida.
Phanton read
Este tipo de inconsistência ocorre quando buscamos a informação dentro de uma transação por um determinado critério, por exemplo, e o sistema retorna 100 registros, ao efetuar uma atualização (Update) apenas 99 são atingidos, entre a leitura e o Update um registro foi modificado e saiu da cláusula WHERE ou foi apagado; poderia também ao invés de ser 99 registros atingidos serem 101, 102. Entre o Select e o Update, os usuários podem ter inserido registros que foram cobertos pela cláusula WHERE. Por isto é chamado de phanton ou fantasma, aparecem e somem registros na mesma transação.

Read Commited
Os registros são bloqueados quando ao se efetuar a leitura , por isto o tipo de inconsistência Dirty Read não existe, lêem-se somente informações que sofreram um Commit. Ao efetuar um Update o registro também é bloqueado para a conexão até o commit ou rollback (implícito ou explicito).

Repeatable read
Segura os registros para a conexão na leitura até o fim da transação; nenhum usuário pode alterar ou apagar estes registros até que a conexão encontre um Commit ou Rolback (implícito ou explicito), Os Dirty´s Read´s e NonRepeatable Read´s não ocorrem, porém podem ocorrer os phantons. (Só utilizar este tipo de isolamento quando estritamente necessário.)

Serializable
Este é o tipo de isolamento mais restrito, não permite a leitura (Select), atualização (Update), inclusão (Insert) ou remoção (delete) de nenhum registro que está sendo lido, até que encontre o Commit ou Rollback (implícito ou explicito). Os Dirty Read´s, NonRepeatable Reads e Phanton não ocorrem. (Só utilizar este tipo de isolamento quando estritamente necessário.)

Obs: O nível de isolamento SnapShot é novo na ADO.NET e é suportado somente pelo SQL Server 2005.

Vejamos a seguir um exemplo completo feito no VB 2005 Express onde estamos reutilizando uma única SqlTransaction e SqlCommand para definir as atualizações nas tabelas Clientes e Estados do banco de dados Cadastro.mdf (Este banco de dados foi criado no SQL Server 2005, leia o artigo - VB.NET 2005 - Criando Objetos Básicos de dados. - que mostra as etapas para efetua a tarefa)

Crie um novo projeto no VB 2005 com o nome de transacoesNet e inclua no formulário padrão form1.vb um componente DataGridView e um botão de comando.

Ao executar o projeto iremos carregar o DataGridView com os dados da tabela Clientes do banco de dados Cadastro.mdf. O namespace usado neste projeto é :

Imports System.Data.SqlClient

A string de conexão com o banco de dados usada será armazenada usando o recurso My.Settings conforme os seguintes passos:

Clique com o botão direito sobre My Project na janela Solution Explorer e selecione Open.
Na janela a seguir selecione Settings e informe um nome para a string de conexão e para a string SQL na coluna Name. Eu usei os nomes connSQL. Salve a operação. Pronto ! já podemos recuperar esta informação a partir de My.Settings em tempo de execução.

O código para efetuar estar tarefa é feita pela rotina carregaDataGridView exibida a seguir:

   Private Sub carregaDataGridView()

        Dim conCadastro As SqlConnection = New SqlConnection(My.Settings.connSQL)

Try

            Dim strSql As String = "SELECT Codigo,Nome,Cidade, Estado FROM Clientes"

            Dim cmdGrid As New SqlCommand(strSql, conCadastro)

            cmdGrid.CommandType = CommandType.Text

            conCadastro.Open()


            Dim sdrGrid As SqlDataReader = cmdGrid.ExecuteReader

            Dim intCol As Integer


            With drGrid

                If .HasRows Then

                    dgvTransacao.Rows.Clear()

                    For intCol = 0 To .FieldCount - 1

                        dgvTransacao.Columns.Add(.GetName(intCol), .GetName(intCol))

                    Next

                    dgvTransacao.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

                    While .Read

                        Dim objCells(intCol) As Object

                        .GetValues(objCells)

                        dgvTransacao.Rows.Add(objCells)

                    End While

                    .Close()

                End If

            End With

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            conCadastro.Close()

        End Try

    End Sub

A rotina acima é chamada no evento Load do formulário: carregaDataGridView()

O botão de comando é responsável por chamar a rotina executaTransacao() que irá iniciar a transação que irá efetuar operações de Insert e Delete nas tabelas Clientes e Estados. Efetuamos um Commit nas operações e no final se houve alguma exceção damos um RollBack na transação desfazendo as operações feitas.

A seguir o código da rotina executaTransacao:

Public Sub executaTransacao()

'Inclue e exclui novo cliente e estado nas tabelas Clientes e Estado

Dim conCadastro As SqlConnection = New SqlConnection(My.Settings.connSQL)

'Especifica um objeto para a transação local

Dim tranClientesEstado As SqlTransaction = Nothing

Dim registrosAfetados As Integer

Dim transacaoSQL As String = Nothing

Try

conCadastro.Open()

Try

'Inicia a transação

tranClientesEstado = conCadastro.BeginTransaction(IsolationLevel.RepeatableRead)

'Define e execurta a instrução INSERT usando SqlCommand para um novo cliente

transacaoSQL = "INSERT "

Dim strSQL As String = "INSERT Clientes (Nome ,Endereco, Cidade, Estado, Email, Nascimento) VALUES ('Macoratti','Rua Projetada, 20','Lins','SP','macoratti@yahoo.com','09/09/1978')"

Dim cmdTrans As SqlCommand = New SqlCommand(strSQL, conCadastro)

cmdTrans.CommandType = CommandType.Text

'Alista o comando na transação

cmdTrans.Transaction = tranClientesEstado

registrosAfetados = cmdTrans.ExecuteNonQuery

'Inclui um novo estado na tabela Estado

strSQL = "INSERT Estados (NomeEstado, SiglaEstado) VALUES ('Rio Grande do Sul','RS')"

cmdTrans.CommandText = strSQL

registrosAfetados += cmdTrans.ExecuteNonQuery

'Da um Commit na transação (efetiva as alterações)

tranClientesEstado.Commit()

'Exibe no DataGridView

carregaDataGridView()

Application.DoEvents()

MsgBox("Clique em OK para continuar com a transação...")

'Exclui registros das tabelas Clientes e Estado

transacaoSQL = "DELETE "

tranClientesEstado = conCadastro.BeginTransaction(IsolationLevel.RepeatableRead)
' Para efetuar testes o critério código deve ser referido a um registro existente , verifique a tabela antes de informar

strSQL = "DELETE FROM Clientes WHERE Codigo = 25"

cmdTrans.CommandText = strSQL

'A transação anterior terminou desta forma temos que alistar novamente os comandos

cmdTrans.Transaction = tranClientesEstado

registrosAfetados += cmdTrans.ExecuteNonQuery

'define a instrução SQL para excluir um registro da tabela Estado
' Para efetuar testes o critério códigoEstado deve ser referido a um registro existente , verifique a tabela antes de informar

strSQL = "DELETE FROM Estados WHERE CodigoEstado = 18"

cmdTrans.CommandText = strSQL

registrosAfetados += cmdTrans.ExecuteNonQuery

'Da um Commit na transação que usa o DELETE

tranClientesEstado.Commit()

'exibe no DataGridView

carregaDataGridView()

Catch excTrans As SqlException

MsgBox(excTrans.Message + excTrans.StackTrace, , transacaoSQL + " Transação falhou")

Try

' cancela as operações realizadas

tranClientesEstado.Rollback()

Catch excRollback As SqlException

MsgBox(excTrans.Message + excTrans.StackTrace, , transacaoSQL + " Rollback Falhou")

End Try

Catch ex As Exception

MsgBox(ex.Message + ex.StackTrace)

Finally

'Fecha a conexao SqlConnection sempre

conCadastro.Close()

Dim strMsg As String

If registrosAfetados = 4 Then

strMsg = "Transações INSERT e DELETE realizadas com sucesso."

Else

strMsg = "As Transações INSERT e/ou DELETE falharam. " + " Verifique as tabelas..."

End If

End Try

End Sub

A rotina acima é chamada no evento Click do botão de comando: executaTransacao()

Executando o projeto temos a janela abaixo antes da execução da transação:

Após executar a transação temos na figura abaixo o registro que foi incluído e o registro 25 que foi excluído , confirmando que a transação foi executada com sucesso. Se verificarmos a tabela Estados veremos também o registro incluído.

Deve ficar bem claro que este é apenas um exemplo visto que em ambiente de produção seriam usadas stored procedures T-SQL BEGIN TRAN[SACTION], COMMIT TRAN[SACTION] e ROLLBACK TRAN[SACTION] para gerencia múltiplas atualizações.

Bom estudo e até o próximo artigo VB.NET

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

Quer migrar para o VB .NET ?

Veja mais sistemas completos para a plataforma .NET no Super DVD .NET , confira...
Curso Básico VB .NET - Vídeo Aulas

Quer aprender C# ??

Chegou o Super DVD C# com exclusivo material de suporte e vídeo aulas com curso básico sobre C#.
Curso C# Basico - Video Aulas

Gostou ? Compartilhe no Facebook Compartilhe no Twitter

Referências:

José Carlos Macoratti

C# - Tipos Anônimos (Anonymous Types) - Macoratti.net

LINQ - Visão geral - Macoratti.net

C# - Como funciona a palavra chave var ? - Macoratti.net

LINQ - Visão geral - Inicializadores de objetos e coleções