Creating Transactions

By default ADO.NET connections do not have an active transaction. The transaction must be started explicitly using the connection's BeginTransaction method. This returns a DbTransaction object that controls the lifetime of the transaction.

Once the transaction has been created, all commands executed on the connection must have their DbTransaction property set to that transaction object. Only one transaction may be active on the connection at once and all commands must be executed under that transaction.

SingleStoreConnection connection;
SingleStorerCommand cmd = new SingleStoreCommand(conn);

// Thsi creates a new transaction and registers it with the command
DbTransaction tran = conn.BeginTransaction();
cmd.DbTransaction = tran;

// Both of these statements execute in the transaction
cmd.CommandText = "INSERT INTO ExampleTable (ExampleCol) VALUES ('ExampleVal1')";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO ExampleTable (ExampleCol) VALUES ('ExampleVal2')";
cmd.ExecuteNonQuery();

// This ends the current transaction and unregisters it from the command
tran.Commit();
cmd.DbTransaction = null;

Committing and Rolling Back Transactions

The transaction object is used to commit and rollback the transaction. The transaction may be commtited using the transaction's Commit method. Committing accepts all the changes in the transaction and persists them if they are consistent:

tran.Commit();

The transaction may be rolled back using the transaction's Rollback method. Rolling back undoes all changes performed in the transaction.

tran.Rollback();