The easiest way of working with transactions is to use auto-commit. What this means is that each operation performed against the database is automatically treated as a transaction. For example, if we want to delete all rows that have a particular status:

DELETE FROM ORDERS WHERE STATUS='COMPLETE'

And the following code:

using (MimerConnection conn = new MimerConnection(connectionString))
{
     conn.Open();
     using (MimerCommand comm1 = new MimerCommand("DELETE FROM ORDERS WHERE STATUS='COMPLETE'", conn))
     {
         //
         // The following method call starts a transaction, performs the delete operation, and commits the transaction
         //
         comm1.ExecuteNonQuery();
     }
}

In the example the data is deleted from the order table. We only want a single SQL statement in this transaction, so we use auto-commit as this is the fastest way of doing this. The ExecuteNonQuery method will only perform a single round-trip to the server to perform the work.

The SQL query may delete zero, one, or several rows depending on how many rows have the status set to COMPLETED. Even though we use auto-commit, we are guaranteed that all rows as defined by the statement are all deleted together. If the transaction fails for some reason no rows will be affected.

Also note the using-clauses as they will guarantee that we release any resources in the server associated with the MimerConnection and the MimerCommand as each of these go out of scope. An alternative would be to use a try-clause and explicitly release these resources by calling comm1.Dispose and conn.Close().