Mysql 5 transaction

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
aspirany
Posts: 12
Joined: Mon 04 Sep 2006 16:51

Mysql 5 transaction

Post by aspirany » Wed 04 Mar 2009 13:36

Dear Support Team!

I'stack. Would like to use mysqltransaction with devart.

the code

MySqlConnection conn = new MySqlConnection("User Id=root;Password=attika;Host=localhost;Database=office;");
MySqlTransaction trans = null;
.
.
.
public void insert_data()
{
string query = "INSERT INTO munkalapok (munkalapszam,szlacegid,partnerId)";
query +="VALUES ('"+munkalapszam1+"','1','1')";

if (conn.State == ConnectionState.Closed)
{
conn.Open();
try
{
MySqlCommand command = new MySqlCommand(query, conn);
trans.Connection.BeginTransaction();
command.ExecuteNonQuery();
trans.Connection.Rollback();

}
catch (MySqlException exception)
{
MessageBox.Show(exception.Message.ToString());
trans.Connection.Rollback();
}
}

}


transaction never rolled back;

thanks your help

Shalex
Devart Team
Posts: 8072
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 04 Mar 2009 14:38

1. Please make sure trans.Connection is assigned to conn in your sample (trans.Connection=conn). We didn't find this assignment in the piece of code you have posted.
2. Be aware, some table storage engines don't support transactions. E.g., ENGINE INNODB has such support, but MyISAM - does not. So make sure your storage engine supports transactions (refer to the appropriate MySQL documentation).

If it doesn't help, please tell us your current dotConnect for MySQL version (the Visual Studio Tools menu | MySQL | About), the version of MySQL server you are using, and your table storage engine. We will try to reproduce the problem.

aspirany
Posts: 12
Joined: Mon 04 Sep 2006 16:51

not working

Post by aspirany » Wed 04 Mar 2009 15:48

my table type InnoDB suuport transaction.

code

MySqlConnection conn = new MySqlConnection("User Id=root;Password=attika;Host=localhost;Database=office;");
MySqlTransaction trans = null;
.
.
.
public void insert_data()
{
string query = "INSERT INTO munkalapok (munkalapszam,szlacegid,partnerId)";
query +="VALUES ('"+munkalapszam1+"','1','1')";

if (conn.State == ConnectionState.Closed)
{
conn.Open();
try
{
trans.Connection = conn;//error
MySqlCommand command = new MySqlCommand(query, conn);
trans.Connection.BeginTransaction();
command.ExecuteNonQuery();
trans.Connection.Rollback();

}
catch (MySqlException exception)
{
MessageBox.Show(exception.Message.ToString());
trans.Connection.Rollback();
}
}

}


make an error:
Error 1 Property or indexer 'Devart.Data.MySql.MySqlTransaction.Connection' cannot be assigned to -- it is read only C:\vs2008\office\Hibabejelento\Hibafelv.cs 153 21 Hibabejelento

thank you

Shalex
Devart Team
Posts: 8072
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 05 Mar 2009 10:42

Try using transaction like this:

Code: Select all

using (MySqlConnection conn = new MySqlConnection("uid=root;pwd=root;host=localhost;database=test;")){
  conn.Open();
  conn.BeginTransaction();
  MySqlCommand command = new MySqlCommand("insert into dept values (1,'a','a')", conn);
  command.ExecuteNonQuery();
  conn.Rollback();
}
Please notify us about the results.

Post Reply