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.
Dim myConnection as SQLConnection = new SQLConnection("server=(local)\NetSDK;Integrated Security=SSPI;database=northwind")
Dim myCommand as SQLCommand = new SqlCommand()
Dim myTrans as SqlTransaction
' Open the connection.
myConnection.Open()
' Assign the connection property.
myCommand.Connection = myConnection
' Begin the transaction.
myTrans = myConnection.BeginTransaction()
' Assign transaction object for a pending local transaction
myCommand.Transaction = myTrans
try
' Restore database to its original condition so sample will work correctly.
myCommand.CommandText = "DELETE FROM Region WHERE (RegionID = 100) OR (RegionID = 101)"
myCommand.ExecuteNonQuery()
' Insert the first record.
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'MidWestern')"
myCommand.ExecuteNonQuery()
' Insert the second record.
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'MidEastern')"
myCommand.ExecuteNonQuery()
myTrans.Commit()
Console.Write("Both Records are written to the database!")
catch e as Exception
myTrans.Rollback()
Console.WriteLine(e.ToString())
Console.Write("Neither record is written to the database!")
finally
myConnection.Close()
end try
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.
[Run Sample] | [View Source] |