Database Transactions and Updates

for RuBoard

When the data adapter updates the data source, it is NOT done as a single transaction. If you want all the inserts , updates, and deletes done in one transaction, you must handle the transaction programmatically.

The SqlConnection object has a BeginTransaction method that returns a SqlTransaction object. When you invoke the BeginTransaction method, you can optionally specify the isolation level. If you know what you are doing, and understand the trade-offs, you can improve the performance and scalability of your application by setting the appropriate isolation level. If you set the isolation level incorrectly or inappropriately, you can have inconsistent or incorrect data results. ^[10]

^[10] Discussing isolation levels in detail would remove our focus from .NET to database programming. Any good intermediate to advanced book on database programming would discuss the concept of isolation levels and locking. For specific information about the SQL Server locking mechanism you can read the Microsoft Press Inside SQL Server books, among others. Tim Ewald's book Transactional COM+ has a good chapter on the issue of isolation and its relation to building scalable applications.

The SqlTransaction class has Commit and Rollback methods to commit or abort the transaction. You open the SqlConnection , invoke the BeginTransaction method, use the SqlDataAdapter as normal, and then call SqlTransaction.Commit or SqlTransaction.Rollback as appropriate. Then close the connection. The Save method on SqlTransaction can be used to set a savepoint in the transaction.

In order to minimize the database resources you hold, and therefore increase the scalability of your application, you want to minimize the time between calling BeginTransaction and the call to Commit or Rollback .

Here is some code from the Transactions example. It uses the AirlineBroker database introduced later in the chapter. Note that we only open the connection right before the Fill , and the transaction statements bracket the Update .

 conn = new SqlConnection(ConnString);  da = new SqlDataAdapter();  ds = new DataSet();  da.SelectCommand = new SqlCommand(selectCmd, conn);  da.InsertCommand = new SqlCommand(insertCmd, conn);  ...  conn.Open();  da.Fill(ds, "Airlines");  ...  trans = conn.BeginTransaction();  da.InsertCommand.Transaction = trans;  da.Update(ds, "Airlines");  trans.Commit();  ... 

To ensure that the SQL Server data provider operates properly, you should use the Commit and Rollback methods on the SqlTransaction object to commit or roll back the transactions started with SqlConnection.BeginTransaction . Do not use the SQL Server transaction statements.

If you use stored procedures for your database work, you can certainly issue SQL Server transaction statements inside the stored procedures instead of using the SqlTransaction object. Stored procedures can be used to encapsulate transactional changes. The MakeReservation stored procedure in the HotelBroker database does just that.

for RuBoard