Hi,

you've got a .Net SOA (Framework 2.0) and we want to migrate from MsSQL to MySql for persistance considerations.

I tried following sample with MsSql2005 (System.Data.SqlClient) and MySql5.x (MySql.Data.MySqlClient 5.0.3, 5.1.2->5.1.5, 5.2.1).
I try to use TransactionScope feature with MySql it seems to don't work. But it With MsSQL it work fine !

Does MySqlConnector/Net supports that feature ? Do we need specifical settings with MsDTC ?

--

MisterT.

My test code : Rollback done in in MsSQL but not in MySQL

using System.Transactions;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace ConsoleApplication3
{
    class Program
    {
        static string csMySQL = "server=localhost;uid=root;database=test;Pwd=xxxx;pooling=false";
        static string sqlMYSQL1 = "insert into te (c1,c2) values ('test MySQL',now());";

        static string csMSSQL = @"server=localhost\sqlexpress;uid=sa;Pwd=xxxx;database=test;";
        static string sqlMS1 = "insert into te (c1,c2) values ('test MsSQL',getdate());";

        static void Main(string[] args)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                testMS1();
                scope.Dispose();
            }
            using (TransactionScope scope = new TransactionScope())
            {
                testMY1();
                scope.Dispose();
            }       
        
        }

        private static void testMY1()
        { 
            using (MySqlConnection cnx = new MySqlConnection(csMySQL))
            {
                MySqlCommand cmd = new MySqlCommand(sqlMYSQL1, cnx);

                cnx.Open();
                cmd.ExecuteNonQuery();
                cnx.Close();
            }   
            
        }


        private static void testMS1()
        {
            using (SqlConnection cnx = new SqlConnection(csMSSQL))
            {
                SqlCommand cmd = new SqlCommand(sqlMS1, cnx);

                cnx.Open();
                cmd.ExecuteNonQuery();
                cnx.Close();
            }   
        }
    }
}

Hi

you are not using it correctly , instead , you need this :

        static void Main(string[] args)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                testMS1();
                testMY1();
                scope.Completed();
            }        
        }

you need to call scope.completed , and also no need to call dispose ... the Using Blok will take care of it ...

also you need to make sure that " Distributed transaction coordinator " service is running

hope it helps

thanks for your answer?

But, my problem is not to commit a transaction but to not commit it. I can replace "scope.Dispose();" by "throw new Exception("I want to rollback my trans !");" but it doesn't rollback mysql insertion !

In others words, I can every thing, transaction is always commit in MySql.

Any helps ?

seems there is a bug in MYSQL ,

i read a lot about that :

http://bugs.mysql.com/bug.php?id=28709

http://forums.mysql.com/read.php?38,103186,103186

It seems that you have to use the transaction on the connection level (old style).

It's a bad news because on 5.2.1 (alpha) that bug doesn't solved !

How can i share transaction between differents DAL methods (or  DAL objects) in my BLL objects ? It is so simple in MsSql !

Any suggestions ?

In this case you can't use the Transactions in the BLL Methods,

Instead you need to use the transaction inside the StoredProcedure , in this case your stored procedured may do more than one  operation ,

and you can call that stored procedures in the same you do for any stored procedure...

Another approach is to use the Transactions in the DLL methods, this is an example :

SqlConnection sqlConnection = newSqlConnection();
sqlConnection.ConnectionString =
ConfigurationSettings.AppSettings["CString"].ToString();
SqlCommand sqlCommand = new SqlCommand();
SqlTransaction sqlTransaction = null;
try
{
  sqlConnection.Open();
  sqlTransaction =sqlConnection.BeginTransaction();
  sqlCommand.Transaction = sqlTransaction;
  sqlCommand.CommandText = "Insert intocustomervalues(8,'Joydip')";
  sqlCommand.Connection = sqlConnection;
  sqlCommand.ExecuteNonQuery();
  sqlCommand.CommandText = "Insert intoproduct
  values(1287,'Colgate')";
  sqlCommand.Connection = sqlConnection;
  sqlCommand.ExecuteNonQuery();
  sqlTransaction.Commit();
}
 
catch (Exception e)
{
  sqlTransaction.Rollback();
}
 
finally
{
  sqlConnection.Close();
}

Note that for this approch , we don't handle the trasnaction in the Stored procedure , instead it will handled in the DAL Method .

 Hope it helps

I'll re-oriented my implementation to integrate your approches.

Many thanks for your advises.