Class MySqlTransaction

Represents a SQL transaction to be made in a MySQL database. This class cannot be inherited.

Inheritance
System.Object
System.MarshalByRefObject
System.Data.Common.DbTransaction
MySqlTransaction
Implements
System.Data.IDbTransaction
System.IDisposable
System.IAsyncDisposable
Namespace: MySql.Data.MySqlClient
Assembly: MySql.Data.dll
Version: 9.1.0
Syntax
public sealed class MySqlTransaction : DbTransaction, IDbTransaction, IDisposable, IAsyncDisposable
Remarks

The application creates a MySqlTransaction object by calling BeginTransaction() on the MySqlConnection object. All subsequent operations associated with the transaction (for example, committing or aborting the transaction), are performed on the MySqlTransaction object.

Examples

The following example creates a MySqlConnection and a MySqlTransaction. It also demonstrates how to use the BeginTransaction(), Commit(), and Rollback() methods.

  public void RunTransaction(string myConnString)
  {
    MySqlConnection myConnection = new MySqlConnection(myConnString);
    myConnection.Open();
    MySqlCommand myCommand = myConnection.CreateCommand();
    MySqlTransaction myTrans;
    // Start a local transaction
    myTrans = myConnection.BeginTransaction();
    // Must assign both transaction object and connection
    // to Command object for a pending local transaction
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTrans;

    try
    {
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (MySqlException ex)
      {
        if (myTrans.Connection != null)
        {
          Console.WriteLine("An exception of type " + ex.GetType() +
          " was encountered while attempting to roll back the transaction.");
        }
      }

      Console.WriteLine("An exception of type " + e.GetType() +
      " was encountered while inserting the data.");
      Console.WriteLine("Neither record was written to database.");
    }
    finally
    {
      myConnection.Close();
    }
  }

Properties

Connection

Gets the MySqlConnection object associated with the transaction, or a null reference (Nothing in Visual Basic) if the transaction is no longer valid.

Declaration
public MySqlConnection Connection { get; set; }
Property Value
Type Description
MySqlConnection

The MySqlConnection object associated with this transaction.
Remarks

A single application may have multiple database connections, each with zero or more transactions. This property enables you to determine the connection object associated with a particular transaction created by BeginTransaction().

DbConnection

Gets the DbConnection object associated with the transaction, or a null reference if the transaction is no longer valid.

Declaration
protected override DbConnection DbConnection { get; }
Property Value
Type Description
System.Data.Common.DbConnection
Overrides
System.Data.Common.DbTransaction.DbConnection

IsolationLevel

Specifies the IsolationLevel for this transaction.

Declaration
public override IsolationLevel IsolationLevel { get; }
Property Value
Type Description
System.Data.IsolationLevel

The IsolationLevel for this transaction. The default is ReadCommitted.
Overrides
System.Data.Common.DbTransaction.IsolationLevel
Remarks

Parallel transactions are not supported. Therefore, the IsolationLevel applies to the entire transaction.

Methods

Commit()

Commits the database transaction.

Declaration
public override void Commit()
Overrides
System.Data.Common.DbTransaction.Commit()
Remarks

The Commit() method is equivalent to the MySQL SQL statement COMMIT.

CommitAsync(CancellationToken)

Asynchronously commits the database transaction.

Declaration
public override Task CommitAsync(CancellationToken cancellationToken = default(CancellationToken))
Parameters
Type Name Description
System.Threading.CancellationToken cancellationToken
Returns
Type Description
System.Threading.Tasks.Task

A task representing the asynchronous operation.
Overrides
System.Data.Common.DbTransaction.CommitAsync(System.Threading.CancellationToken)

Dispose(Boolean)

Releases the unmanaged resources used by the MySqlTransaction and optionally releases the managed resources

Declaration
protected override void Dispose(bool disposing)
Parameters
Type Name Description
System.Boolean disposing

If true, this method releases all resources held by any managed objects that this MySqlTransaction references.
Overrides
System.Data.Common.DbTransaction.Dispose(System.Boolean)

Finalize()

Declaration
protected void Finalize()

Rollback()

Rolls back a transaction from a pending state.

Declaration
public override void Rollback()
Overrides
System.Data.Common.DbTransaction.Rollback()
Remarks

The Rollback() method is equivalent to the MySQL statement ROLLBACK. The transaction can only be rolled back from a pending state (after BeginTransaction has been called, but before Commit is called).

RollbackAsync(CancellationToken)

Asynchronously rolls back a transaction from a pending state.

Declaration
public override Task RollbackAsync(CancellationToken cancellationToken = default(CancellationToken))
Parameters
Type Name Description
System.Threading.CancellationToken cancellationToken

The cancellation token.
Returns
Type Description
System.Threading.Tasks.Task

A task representing the asynchronous operation.
Overrides
System.Data.Common.DbTransaction.RollbackAsync(System.Threading.CancellationToken)

Implements

System.Data.IDbTransaction
System.IDisposable
System.IAsyncDisposable