Como controlar uma transação entre dois banco de dados

Question

Olá gente,

Possuo uma aplicação na qual preciso inserir registros simultaneamente em dois banco de dados(Mysql e Sql Server). Como eu poderia trabalhar com transação em bancos dados, garantindo assim que a informação ou seja inserido nas duas bases ou então rollback.

Henrylle Maia - Desenvolvedor.Net

Answer 1

Salve, Henrylle. A classe TransactioScope vai resolver seu problema. Segue um código comentado sobre como se fazer isso. (fonte: MSDN)

// This function takes arguments for 2 connection strings and commands to create a transaction 
// involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the 
// transaction is rolled back. To test this code, you can connect to two different databases 
// on the same server by altering the connection string, or to another 3rd party RDBMS 
// by altering the code in the connection2 code block.
public int CreateTransactionScope(string connectString1, string connectString2, string commandText1, string commandText2)
{
 // Initialize the return value to zero and create a StringWriter to display results.
 int returnValue = 0;
 System.IO.StringWriter writer = new System.IO.StringWriter();
 try
 {
  // Create the TransactionScope to execute the commands, guaranteeing
  // that both commands can commit or roll back as a single unit of work.
  using (TransactionScope scope = new TransactionScope())
  {
   using (SqlConnection connection1 = new SqlConnection(connectString1))
   {
    // Opening the connection automatically enlists it in the 
    // TransactionScope as a lightweight transaction.
    connection1.Open();
    // Create the SqlCommand object and execute the first command.
    SqlCommand command1 = new SqlCommand(commandText1, connection1);
    returnValue = command1.ExecuteNonQuery();
    writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
    // If you get here, this means that command1 succeeded. By nesting
    // the using block for connection2 inside that of connection1, you
    // conserve server and network resources as connection2 is opened
    // only when there is a chance that the transaction can commit. 
    using (SqlConnection connection2 = new SqlConnection(connectString2))
    {
     // The transaction is escalated to a full distributed
     // transaction when connection2 is opened.
     connection2.Open();
     // Execute the second command in the second database.
     returnValue = 0;
     SqlCommand command2 = new SqlCommand(commandText2, connection2);
     returnValue = command2.ExecuteNonQuery();
     writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
    }
   }
   // The Complete method commits the transaction. If an exception has been thrown,
   // Complete is called and the transaction is rolled back.
   scope.Complete();
  }
 }
 catch (TransactionAbortedException ex)
 {
  writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
 }
 catch (ApplicationException ex)
 {
  writer.WriteLine("ApplicationException Message: {0}", ex.Message);
 }
 // Display messages.
 Console.WriteLine(writer.ToString());
 return returnValue;
}

 Abraço!

---

Visite meu blog - http://dicasdecodigo.blogspot.com/

Answer 2

Olá,

Você poderia utilizar a classe SqlTransaction(http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx), mas como irá utilizar em dois bancos diferentes, acredito que seja melhor utilizar a OleDBTransaction(http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbtransaction.aspx)

Dê uma olhada nesses dois links, acredito que possam te ajudar :)

Ah, veja esse também no CodeProject http://www.codeproject.com/KB/database/transactions.aspx

;)

Answer 3

Eu posso utilizar ela no Entity Framework para a mesma situação? DataModel com o Store para o Sql Server a um DataModel com Store para o MySql?

Muitíssimo obrigado pela ajuda com esse código comentado. Vou testar na minha aplicação e assim que o fizer darei um retorno.

---

Henrylle Maia - Desenvolvedor .NET

Answer 4

Até onde sei, pode sim.

Grosso modo, seria algo parecido com isso:

 

var dbSql = new MinhasEntitiesBancoSQL();

var dbMySQl = new MinhasEntitiesBancoMySQL();


using (var txscope = new TransactionScope())
{
	dbSql.Connection.Open();
	
	// query 1
	
	dbMySQl.Connection.Open();
	
	// query2
	
	txscope.Complete();

}


dbSql.Dispose();
dbMySQl.Dispose();

lógicamente vc precisa fazer os tratamentos de exceção e os demais ajustes a sua necessidade.

Abraço,
 

---

Visite meu blog - http://dicasdecodigo.blogspot.com/