ADO.NET: Use Database Transactions

Database transactions are used to control data commitment to databases. For example, in
standard account procedures, it is necessary to debit one account and credit another at
the same time. Since computers break down on occasion (power outages, network outages, and so on)
there is the potential for one record to be updated or added, but not the other. To
avoid these situations, transactions are used. Transactions in ADO.NET are, just as in ADO,
handled at the database level: your database must support transactions.

There are three basic commands for transactions: BeginTransaction, Commit, and Rollback. BeginTransaction
marks the beginning of a transaction. Anything that happens between the BeginTransaction and the next
command (either Rollback or Commit) is considered part of the transaction. The following code example demonstrates using transactions.

The example shows that if either insert fails, both are rolled back to their original states. If
each succeeds, then the transaction is committed.

As in classic ADO, you can control transactions through the connection object. In fact, when you
use the OleDbConnection, it uses the same underlying OLE DB transaction model. Thus, if you were
able to commit transactions against your database with ADO classic, you can still commit them with ADO.NET. The following code example demonstrates using the SqlConnection and SqlCommand to insert two records into "Region" table. If
either fails, then the changes are rolled back.

The DataSet object also has a commit model (AcceptChanges,RejectChanges) but it does
not affect the database. The commit model is for the cache of data in the DataSet alone.
To submit the Data from the DataSet into the database, use the Update method on the SqlDataAdapter.