ADO.NET in a Nutshell

Manual Transactions

Manual transactions use explicit statements to control the boundaries of a transaction. Transactions are started, and subsequently either committed or rolled back. The SQL .NET data provider allows savepoints to be defined that allow a transaction to be partially rolled back. The OLE DB .NET data provider allows new, or nested, transactions to be started within the boundaries of the parent transaction. If transactions are nested, the parent can’t commit until all nested transactions have committed.

A Transaction is started by calling the BeginTransaction( ) method of a Connection object. You can set a Command object to run in a transaction by setting its Transaction property to a Transaction object connected to the same Connection as the Command object. An overloaded constructor for the Command object allows this to be done in a single statement.

Once running in a Transaction, commands can be executed on the Command object within a try/catch block. If an exception is raised, the Rollback( ) method can be called on the Transaction to roll back all changes; otherwise, the Commit( ) method persists the changes.

The following example demonstrates these concepts. Order and order detail records are inserted within a transaction, thereby ensuring that either both or neither record is added:

String connString = "Data Source=(local);Integrated security=SSPI;" + "Initial Catalog=Northwind;"; SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlTransaction ...