Usuário com melhor resposta
Como controlar uma transação entre dois banco de dados

Pergunta
-
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
Respostas
-
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/- Sugerido como Resposta Alisson Rodrigo Santana quinta-feira, 30 de abril de 2009 12:52
- Marcado como Resposta Henrylle Maia quinta-feira, 30 de abril de 2009 22:03
Todas as Respostas
-
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/- Sugerido como Resposta Alisson Rodrigo Santana quinta-feira, 30 de abril de 2009 12:52
- Marcado como Resposta Henrylle Maia quinta-feira, 30 de abril de 2009 22:03
-
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;)
-
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 -
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/- Sugerido como Resposta Alisson Rodrigo Santana quinta-feira, 30 de abril de 2009 12:52