CODEWITHSUNDEEP
c#mysqltransactions
Steven Evers
Steven Evers

Reputation: 17196

Can you have multiple MySqlCommand's in a single transaction?

I want to do an insert and an update on 2 separate tables, but have them be in 1 transaction).

Essentially in pseudocode I want to do something like:

MySqlTransaction trans = null;
try
{
    _Connection.Open();
    trans = _Connection.BeginTransaction();
    insertCmd.Transaction = trans;
    updateCmd.Transaction = trans;

    Int32 id = insertCmd.ExecuteNonQuery();
    updateCmd.Parameters.Add(new MySqlParameter("oid", MySqlDbType.Int32).Value = id);
    updateCmd.ExecuteNonQuery();
}
catch(MySqlException)
{
    if(trans != null)
        trans.RollBack();
}
finally
{
    _Connection.Close();
}

is this possible or am I going about this the wrong way?

Upvotes: 6

Views: 1833

Answers (2)

Bob Fanger
Bob Fanger

Reputation: 29897

Yes you can if:

  • All the tables support it, (InnoDB tables support it, but MyIsam tables don't)
  • The queries don't affect the database-schema. (ALTER TABLE, DROP TABLE, CREATE TABLE, etc causes the transaction to commit)

Upvotes: 6

Elie
Elie

Reputation: 13853

Yes, you can execute multiple commands within a single transaction. This would let you do exactly what you are trying to do: all commands, or none of them.

Upvotes: 3

Related Questions