Transaction in mysql

Question

I am working on a c# application with Mysql database(version 5.5). I am facing an issue where I need to catch an exception occurred in Mysql. It doesn't matter whether it is caught in Mysql or in dotNet. The problem is that I want to rollback all my previous work when an exception/error is occurred; be it of MySqlException or custom one. And yes, I tried using TransactionScope and MySqlTransaction but it didn't rollback my work. If anyone has any ideas or suggestions, please guide me. Thanks.

Edit:Code

        MySqlConnection con = new MySqlConnection(cnstring);
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        MySqlCommand cmd;
        TransactionScope ob = new TransactionScope();
        try
        {
            cmd = new MySqlCommand("testProcedureSignal", con);
            cmd.Parameters.Add(new MySqlParameter("pval", 1));
            cmd.Parameters.Add(new MySqlParameter("@intException", MySqlDbType.Int32) { Direction = ParameterDirection.Output });
            cmd.Parameters.Add(new MySqlParameter("@strErrorMessage", MySqlDbType.VarChar) { Direction = ParameterDirection.Output });
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException e)
        {
            con.Close();
            ob.Dispose();
            return;
        }
        ob.Complete();
        con.Close();

And this is the Procdeure:

CREATE PROCEDURE `testProcedureSignal`(
pval INT,
OUT intException INT,
OUT strErrorMessage VARCHAR(255)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
ROLLBACK;
END;
SET intException = 100;
START TRANSACTION;
INSERT INTO demo (data) values (intException);
SET intException = intException + 10;
INSERT INTO demo (data) values (intException);
DROP TABLE teeeee;-- this table does not exist
INSERT INTO demo2 (data1,data2) values ('demo2',intException + 10);
SET intException = 111;
SET intException = 2;
COMMIT;
END

If you're using InnoDB tables, I can't see any reason that TransactionScope would not work. If you're using MyISAM tables it will _not_ work since MyISAM has no transactions. — Joachim Isaksson, May 09 '13 at 07:59
Please share your - relevant - code with SO. We cannot say why it did not work for you without having something to look at. — Raxr, May 09 '13 at 08:00

score 1 · Accepted Answer · answered May 13 '13 at 11:39

I brought the thing to the notice of my superiors. And well, we have now MySQL version 5.6 where there is a simple and easy way to implement TRY-CATCH scenario. And now, I am using the following code in all my stored procedures.

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
ROLLBACK;
GET DIAGNOSTICS CONDITION 1
strErrorMessage = MESSAGE_TEXT;
SET intException = 53300;
END;

score 0 · Answer 2 · answered May 09 '13 at 08:06

Please check this article (I found it via Google, nothing hidden): http://zetcode.com/db/mysqlcsharptutorial/

Scroll down to the "Transaction Support" section. You do not have the .Commit() or .Rollback() methods used in your code.

You have to use the MySqlTransaction class to achieve the results you are looking for. You call .Commit() or .Rollback() accordingly, see the link I posted above for all required info and snippets.

Check this link as well: http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqltransaction.html. It describes the MySqlTransaction class with its methods and some best practices.

I have tried with `MySqlTransaction` too and also have mentioned it in my question, its just that I haven't written that code here. Thanks for the links, let me have a look at this and see whether it works or not — vaibhav, May 09 '13 at 08:27

Transaction in mysql

2 Answers2