Does TransactionScope work really with MySql ?

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 ?