0

I have a project where user click on save button:

1) INSERT query insert Data into MySQL DB in Table 1 . 2) Update Query update 1 fields value in Table 2 .

If Step 1 Fails then Step 2 will needs to stop and Step 1 needs to reverse. It means Step 1 and Step 2 needs to be completed together to complete the transaction. Otherwise transaction needs to fail.

Any Help?

Bittu
  • 1
  • 1
  • You already seem to know about transactions. Just use a transaction and rollback instead of committing if the first query goes wrong. – Asad Saeeduddin Sep 30 '14 at 20:20
  • See here: http://stackoverflow.com/questions/14252560/mysql-transaction-commit-and-rollback – Rick S Sep 30 '14 at 20:21
  • I assume you're using `SqlCommand`, so try using `SqlCommand.Transaction` property. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.transaction(v=vs.110).aspx – Alex Barac Sep 30 '14 at 20:21
  • show some code so we know which libraries you are using and can recommend something – Jonesopolis Sep 30 '14 at 20:21

2 Answers2

0

You can do something like

MySqlConnection con = new MySqlConnection( ... );

con.Open();

MySqlTransaction tx = con.BeginTransaction();

MySqlCommand cmd = new MySqlCommand( "", con );

cmd.Transaction = tx;

try
{

   cmd.CommandText = "INSERT query insert Data into MySQL DB in Table 1";

   cmd.ExecuteNonQuery();

   cmd.CommandText = "Update Query update 1 fields value in Table 2";

   cmd.ExecuteNonQuery();

   tx.Commit();

}
catch( MySqlException e )
{

  // TODO: report error?

  tx.Rollback();

}

Then if first insert query failed, you catch the error and rollback from there (to abort transaction) Or if 2nd query fails, again you rollback (to undo changes of first query). Otherwise commit everything.

Just remember to use Innodb engine for your tables, (otherwise transactions don't work in MyISAM).

onedevteam.com
  • 3,838
  • 10
  • 41
  • 74
Fernando Gonzalez Sanchez
  • 3,315
  • 4
  • 32
  • 45
0

You are actually giving the exact definition of Transactions:

Use transations where you need to execute two or more SQL commands in such a way that if any one of the statements fails, then no other statement will be able to change the database.

For more information refer to this link : http://www.codeproject.com/Articles/10223/Using-Transactions-in-ADO-NET

Hope this helps you

Bilel Chaouadi
  • 903
  • 1
  • 10
  • 28