Using Transactions with a DataSet (SQL)

Using Transactions with a DataSet (SQL) In Chapter 3, "Introduction to Structured Query Language," you saw how you can group SQL statements together into transactions.
Nội dung trích xuất từ tài liệu:
Using Transactions with a DataSet (SQL)Using Transactions with a DataSet (SQL)In Chapter 3, Introduction to Structured Query Language, you saw how you can groupSQL statements together into transactions. The transaction is then committed or rolledback as one unit. For example, in the case of a banking transaction, you might want towithdraw money from one account and deposit it into another account. You would thencommit both of these changes as one unit, or if theres a problem, rollback both changes.In Chapter 8, Executing Database Commands, you saw how to use a Transaction objectto represent a transaction.As you know, a DataSet doesnt have a direct connection to the database. Instead, you usethe Fill() and Update() methods of a DataAdapter to pull and push rows from and to thedatabase to your DataSet respectively. In fact, a DataSet has no knowledge of thedatabase at all. A DataSet simply stores a disconnected copy of the data. Because of this,a DataSet doesnt have any built-in functionality to handle transactions.How then do you use transactions with a DataSet? The answer is you must use theTransaction property of the Command objects stored in a DataAdapter.Using the DataAdapter Command Objects Transaction PropertyA DataAdapter stores four Command objects that you access using the SelectCommand,InsertCommand, UpdateCommand, and DeleteCommand properties. When you call theUpdate() method of a DataAdapter, it runs the appropriate InsertCommand,UpdateCommand, or DeleteCommand.You can create a Transaction object and set the Transaction property of the Commandobjects in your DataAdapter to this Transaction object. When you then modify yourDataSet and push the changes to the database using the Update() method of yourDataAdapter, the changes will use the same Transaction.The following example creates a SqlTransaction object named mySqlTransaction and setsthe Transaction property of each of the Command objects in mySqlDataAdapter tomySqlTransaction:SqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction();mySqlDataAdapter.SelectCommand.Transaction = mySqlTransaction;mySqlDataAdapter.InsertCommand.Transaction = mySqlTransaction;mySqlDataAdapter.UpdateCommand.Transaction = mySqlTransaction;mySqlDataAdapter.DeleteCommand.Transaction = mySqlTransaction;Each of the Command objects in mySqlDataAdapter will now use mySqlTransaction.Lets say you added, modified, and removed some rows from a DataTable contained in aDataSet named myDataSet. You can push these changes to the database using thefollowing example:mySqlDataAdapter.Update(myDataSet);All your changes to myDataSet are pushed to the database as part of the transaction inmySqlTransaction. You can commit those changes using the Commit() method ofmySqlTransaction:mySqlTransaction.Commit();You could also roll back those changes using the Rollback() method ofmySqlTransaction.Note A transaction is rolled back by default; therefore, you should always explicitly commit or roll back your transaction using Commit() or Rollback() to make it clear what your program is intended to do.