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

  • 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
    quarta-feira, 29 de abril de 2009 00:01

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/
    quarta-feira, 29 de abril de 2009 13:12

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/
    quarta-feira, 29 de abril de 2009 13:12
  • 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

    ;)

    quarta-feira, 29 de abril de 2009 13:14
  • 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
    quarta-feira, 29 de abril de 2009 14:35
  • 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/
    quarta-feira, 29 de abril de 2009 14:49