0

I am using a transctionScope in main method to calling a method to perform a update in MYSQL table. This method get an open connection from another method. When I force rollBack with a exception throw nothing happens.

try
{
   using (TransactionScope scope = new TransactionScope ( ))
   {
      updateOnTable1();  // executes
      throw new Exception("xxx something has happened test xxx");
      scope.Complete();  // not executing because my exception 
   }
}
catch (Exception e)
{
    Console.WriteLine("Cannot complete transaction:\n"+e);
}

The updateOnTable1() method

    try     
    {
        DbConnection conn = getNewConnection();


        if (SGBDativo == MYSQL)
        {
            using (MySqlCommand sqlCommand = ((MySqlConnection)conn).CreateCommand())
            {
                sqlCommand.CommandType = CommandType.Text;

                sqlCommand.CommandText = ".......sql code.....";

                if (sqlCommand.ExecuteNonQuery() != 1)
                {
                    throw new InvalidProgramException("SQLuser_Client Err");
                }
            }
        }

        conn.Close();
    }
    catch (Exception e)
    {
        Console.WriteLine("Error: SQLuser_Clients - del() - \n" + e.ToString());
    }

The getNewConnection() method:

DbConnection connection = null;

try
{
    switch (SGBDpadrao)
    {
        case SQLSERVER:
            conn_sqlserver = new SqlConnection(string_conn_sqlserver);

            conn_sqlserver.Open();
            SGBDativo = SQLSERVER;

            connection = conn_sqlserver;
            break;

        case MYSQL:
            conn_mysql = new MySqlConnection(string_conn_mysql);

            conn_mysql.Open();
            SGBDativo = MYSQL;

            connection = conn_mysql;
            break;
    }
}
catch (Exception)
{
    return connection;
}

return connection;

But as I said before, this will not roll back. What am I missing?

  • 1
    Mysql does not support TransactionScope. You will have to use the connection transaction. – Craig Selbert Feb 15 '17 at 00:39
  • @craigSelbert, why do you believe mysql does not support tranaction scope? It [here](https://dev.mysql.com/doc/connector-net/en/connector-net-versions.html) says it does. I have not worked with ts, just regular transactions, so curious. – inquisitive Feb 15 '17 at 04:08
  • @inquisitive - I believe you are referring to the statement "Connector/Net now supports nested transactions in a scope where they use the same connection string". That is the connection transaction that I spoke of. For MySql you will need to use the MySqlConnection.BeginTransaction and Commit. – Craig Selbert Feb 15 '17 at 13:48

0 Answers0