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.
Transaction is started by calling the
method of a
Connection object. You can set a
Command object to run in a transaction by setting
Transaction property to a
Transaction object connected to the same
Connection as the
object. An overloaded constructor for the
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
method can be called on the
Transaction to roll back all changes; otherwise,
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 ...