Mysql transaction in C#

Question

1.00/5 (1 vote)

See more:

Hello,

I'm using your DLL to use a databaseMySQL in my C# project.
Update: I am basing this on the following article
Connect C# to MySQL[^]

I'm trying to perform a transaction, but it's never Rollback my request. Here is an example:

C#

using (MySqlConnection db = new MySqlConnection(connectionString))
           {
               MySqlTransaction transaction;

               db.Open();
               transaction = db.BeginTransaction();

               new MySqlCommand("UPDATE t_adresse SET `nomAdresse`='forbl' WHERE idAdresse=8;",db, transaction).ExecuteNonQuery();

               transaction.Rollback();}

What I have tried:

Here is my UnitTest for that (the name of the address at the start is test):

C#

using (MySqlConnection db = new MySqlConnection(connectionString))
            {
                MySqlTransaction transaction;

                db.Open();
                transaction = db.BeginTransaction();

                new MySqlCommand("UPDATE t_adresse SET `nomAdresse`='forbl' WHERE idAdresse=8;",db, transaction).ExecuteNonQuery();
               
                transaction.Rollback();

                MySqlCommand cmdUpdateAdresse = new MySqlCommand("SELECT nomAdresse FROM t_adresse WHERE idAdresse=8", db);
                MySqlDataReader data = cmdUpdateAdresse.ExecuteReader();
                while (data.Read())
                {
                    Assert.AreEqual("test", (string)data["nomAdresse"]);
                }
                db.Close();
            }

And this test return that the value expected ("test") doesn't match the actual value ("forbl")

Posted 17-Dec-19 0:39am

zutt

Updated 18-Dec-19 6:16am

MadMyche

v3

Add a Solution

Comments

Richard MacCutchan 17-Dec-19 7:56am

"I'm using your DLL"
Who is this message supposed to be directed to?

zutt 17-Dec-19 9:57am

Sorry I believed I asked the author ^^'
https://www.codeproject.com/articles/43438/connect-c-to-mysql

3 solutions

Solution 1

What you probably mean is that it isn't updating - because you don't Commit the transaction within that code - so regardless of whether your code calls Rollback or not, the transaction will immediately Rollback when the Connection object goes out of scope.

Check your DB carefully - I suspect that you aren't looking at the "right" db when you check.

Posted 17-Dec-19 1:19am

OriginalGriff

Comments

zutt 17-Dec-19 8:21am

I want to Rollback the UPDATE but its apply the change

Solution 2

I'm using your DLL to use a databaseMySQL in my C# project

What DLL are you using and where did it come from? If you found attached to an article that has been posted here it is best to contact the author of the article by using the Comments / Discussions widget at the bottom of the article.

That said, looking at your code I do not see anything that is not part of Oracle's MySql connector, so the documentation there is going to be pretty much a good outline to follow.
MySqlTransaction Class[^]

For your code, I do not see anything wrong with the code as it is; are you 100% sure that your data is different before and after?
If the nomAdresse address was already forbl or there is no idAdresse=8 present you would not see a change running this script.

I would suggest that for testing you expand out your query and better qualify or WHERE clause. Notice in the following block that I added an AND to the query itself as well as assigning a value to the NonQuery method. If RowsAffected equals 0 then there is nothing to roll back

C#

using (MySqlConnection db = new MySqlConnection(connectionString)) {
	MySqlTransaction transaction;

	db.Open();
	transaction = db.BeginTransaction();

	string qry = "UPDATE t_adresse SET `nomAdresse`='forbl' WHERE idAdresse=8 AND `nomAdresse` <> 'forbl';"; // only change if that value isn't already set
	MySqlCommand cmd = new MySqlCommand(qry, db, transaction);

	int RowsAffected = cmd.ExecuteNonQuery(); // this will show many rows were changed

	transaction.Rollback();}

Posted 17-Dec-19 1:38am

MadMyche

Comments

MadMyche 17-Dec-19 9:49am

I have merged this into your original question

zutt 17-Dec-19 9:59am

Oh sorry i didn't see

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

zutt · Accepted Answer · 2019-12-18T06:17:00

Solution 4

I found the solution.

My tables used MyISAM and MyISAM don't implements transaction. So now I use InnoDB

Posted 18-Dec-19 6:17am

zutt