Database Transactions

Transactions enable "all or nothing" actions across multiple SQL statements. Once started, SQL statements issued within the context of the transaction either all complete, or none of them complete. If you have ten data updates to perform, but the database fails after only five of them, you can roll back the transaction. The database reverses the earlier statements, restoring the data to what it was before the transaction began. (Updates from other users are not affected by the rollback.) If all statements succeed, you can commit the entire transaction, making all of its changes permanent.

For SQL Server databases, transactions are managed through the provider's SqlTransaction object. Like the other ADO.NET statements, it's easy to use. A transaction begins with a BeginTransaction method call on the connection.

Public atomicSet As SqlClient.SqlTransaction = _    libraryDB.BeginTransaction() 

To include a SQL statement in a transaction, assign the SqlTransaction object to the SqlCommand object's Transaction property.

sqlStatement.Transaction = atomicSet 

Then call the appropriate Execute method on the command. When all commands complete, use the transaction's Commit method to make the changes permanent.

atomicSet.Commit() 

If, instead, you need to abort the transaction, use the Rollback method.

atomicSet.Rollback()