Section 10.6. Updating Data with Transactions
An important feature of most industrial-strength databases is support for transactions . A transaction is a set of database operations that must all complete or fail together. That is, either all the operations must complete successfully ( commit the transaction), or all must be undone ( roll back the transaction) so the database is left in the state it was in before the transaction began .
The canonical transaction is transferring money at an ATM. If I transfer $50 from checking to savings, the bank will first reduce my checking account by $50 and then increase my savings account by $50. If it does the first step but not the second, I will be annoyed.
The bank system treats the entire set of reducing one account and increasing the other as a single transaction. The entire transaction occurs or none of it occurs; it is not valid for it to occur "partially."
10.6.1. The ACID Test
Database designers define the requirements of a transaction with the so-called "ACID" test. ACID is an acronym for A tomic, C onsistent, I solated, and D urable. Here's a brief summary of what each of these terms means:
-
Atomic
-
An atomic interaction is indivisible, i.e., it cannot be partially implemented. Every transaction must be atomic. For instance, in the previous banking example, it must be impossible to decrement my checking account but fail to increment my savings account. If the transaction fails, it must return the database to the state it would have been in without the transaction.
All transactions, even failed ones, affect the database in trivial ways: resources are expended, performance is affected, and the log file is updated. The atomic requirement only implies that, if a transaction is rolled back, all of the tables and data (other than log tables) will be in the state they would have been in had the transaction not been attempted at all.
-
Consistent
-
The database is presumed to be in a consistent state before the transaction begins, and the transaction must leave it in a consistent state when it completes. While the transaction is being processed , the database need not be in a consistent state. To continue with our example of transferring money, the database need not be consistent during the transaction. (It is okay to decrement my checking account before incrementing my savings account.) It must end in a consistent state, i.e., when the transaction completes, the books must balance.
-
Isolated
-
Transactions are not processed one at a time. Typically, a database may be processing many transactions at once, switching its attention among various operations. This creates the possibility that a transaction can view and act upon data that reflects intermediate changes from another transaction that is still in progress and that, therefore, currently has its data in an inconsistent state. Transaction isolation is designed to prevent this problem. For a transaction to be isolated, the effects of the transaction must be exactly as if the transaction were acted on alone; there can be no effects on or dependencies on other database activities. For more information, see the sidebar "Data Isolation"
-
Durable
-
Once a transaction is committed, the effect on the database is permanent.
10.6.2. Implementing Transactions
You can implement transactions in ASP.NET in two ways. You can allow the database to manage the transaction by using transactions within your stored procedure, or you can use connection-based transactions. In the latter case, the transaction is created and enforced outside of the database. This allows you to add transaction support to databases that do not otherwise provide for it or to wrap several stored procedures and other database calls inside a single transaction.
Data IsolationCreating fully isolated transactions in a multithreaded environment is a non-trivial exercise. Isolation can be violated in three ways:
Database experts identify four degrees of isolation:
|
In the following example, you will update the Order table and the Order Details table. To get started, create a new web site named Transactions.
The Default.aspx page has a GridView to display the orders ( OrdersGridView ) and a panel in which you will place a second GridView to display the order details ( DetailsGridView ).
Below the panel, you'll create a table in which you'll add a number of controls for updating the Orders and Order Details tables through a database transaction or through an ASP.NET connection-based transaction. The complete design view is shown in Figure 10-16.
The default.aspx file to create this is shown in Example 10-12.
Figure 10-16. Transaction user interface
Example 10-12. Transactions default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Transactions</title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="OrdersGridView" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" DataKeyNames="OrderID" AutoGenerateColumns="False" PagerSettings-Mode="Numeric" AllowPaging="true" PageSize="5" OnSelectedIndexChanged="OnSelectedIndexChangedHandler" OnPageIndexChanging="OrdersGridView_PageIndexChanging" > <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> <Columns> <asp:ButtonField ButtonType="Button" CommandName="Select" Text="Details" /> <asp:BoundField DataField="OrderID" HeaderText="Order ID" /> <asp:BoundField DataField="OrderDate" HeaderText="Order Date" /> <asp:BoundField DataField="CompanyName" HeaderText="Company" /> <asp:BoundField DataField="ContactTitle" HeaderText="Contact" /> <asp:BoundField DataField="Phone" HeaderText="Phone" /> <asp:BoundField DataField="ShipperName" HeaderText="Ship Via" /> </Columns> </asp:GridView> <asp:Panel ID="OrderDetailsPanel" runat="server" Height="50px" Width="125px"> <asp:GridView ID="DetailsGridView" runat="server" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None"> <FooterStyle BackColor="Tan" /> <Columns> <asp:BoundField DataField="OrderDate" HeaderText="Order Date" /> <asp:BoundField DataField="ProductName" HeaderText="Product" /> <asp:BoundField DataField="UnitPrice" HeaderText="Price" /> <asp:BoundField DataField="Quantity" HeaderText="Quantity" /> </Columns> <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" /> <HeaderStyle BackColor="Tan" Font-Bold="True" /> <AlternatingRowStyle BackColor="PaleGoldenrod" /> </asp:GridView> </asp:Panel> <br /> <h3> Place new order</h3> <asp:Table ID="tblAddOrder" runat="server"> <asp:TableRow> <asp:TableCell>Customer:</asp:TableCell> <asp:TableCell> <asp:DropDownList ID="ddlCustomer" DataValueField="CustomerID" DataTextField="CompanyName" runat="server" Width="160" /> </asp:TableCell> <asp:TableCell>Ship Via:</asp:TableCell> <asp:TableCell> <asp:DropDownList ID="ddlShipper" DataValueField="ShipperID" DataTextField="CompanyName" runat="server" Width="120" /> </asp:TableCell> <asp:TableCell>Product:</asp:TableCell> <asp:TableCell> <asp:DropDownList ID="ddlProduct" DataValueField="ProductID" DataTextField="ProductName" runat="server" Width="160px" /> </asp:TableCell> </asp:TableRow> <asp:TableRow> <asp:TableCell>Unit Price:</asp:TableCell> <asp:TableCell> <asp:TextBox ID="txtUnitPrice" runat="server" Width="48px" /> </asp:TableCell> <asp:TableCell>Quantity:</asp:TableCell> <asp:TableCell> <asp:TextBox ID="txtQuantity" runat="server" Width="48px" /> </asp:TableCell> <asp:TableCell>Discount:</asp:TableCell> <asp:TableCell> <asp:TextBox ID="txtDiscount" runat="server" Width="48px" /> </asp:TableCell> </asp:TableRow> <asp:TableRow> <asp:TableCell ColumnSpan="4"> <asp:RadioButtonList ID="rbTransactionType" runat="server" RepeatDirection="Horizontal"> <asp:ListItem Value="DB" Selected="true"> Data Base Transaction</asp:ListItem> <asp:ListItem Value="Connection"> Connection Transaction</asp:ListItem> </asp:RadioButtonList> </asp:TableCell> <asp:TableCell> <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="btnAdd_Click"/> </asp:TableCell> <asp:TableCell> <asp:Label ID="lblNewOrderID" runat="server" Text="" /> </asp:TableCell> </asp:TableRow> </asp:Table> </div> </form> </body> </html>
In a previous example, you made a single database call to get all the order details records, and when an order was selected, you filtered on that view to get the details you wanted. This time, to show another approach, when you select an order, you'll go to the database with a query that only extracts the order detail records for the appropriate order ID.
The complete code-behind is shown in Example 10-13 and analysis follows .
Example 10-13. Default.aspx.cs for Transactions
using System; using System.Text; using System.Data; using System.Data.SqlClient; // necessary for data access using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindGrid( ); ddlCustomer.DataSource = GetDataReader("Customers"); ddlCustomer.DataBind( ); ddlShipper.DataSource = GetDataReader("Shippers"); ddlShipper.DataBind( ); ddlProduct.DataSource = GetDataReader("Products"); ddlProduct.DataBind( ); } } private void BindGrid( ) { DataSet ds = CreateDataSet(WhichTable.Orders); OrdersGridView.DataSource = ds.Tables[0]; OrdersGridView.DataBind( ); } private SqlDataReader GetDataReader(string whichTable) { // connection string to connect to the Bugs Database string connectionString = "Data Source=Brahams;Initial Catalog=Northwind;Integrated Security=True"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection( connectionString); connection.Open( ); // Create a SqlCommand object and assign the connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection; // hard code the select statement command.CommandText = "select * from " + whichTable; // return the data reader return command.ExecuteReader( CommandBehavior.CloseConnection); } // get order details public void OnSelectedIndexChangedHandler( Object sender, EventArgs e) { UpdateDetailsGrid( ); } private void UpdateDetailsGrid( ) { BindGrid( ); DataSet ds = CreateDataSet(WhichTable.OrderDetails); if (ds.Tables[0].Rows.Count > 0) { this.OrderDetailsPanel.Visible = true; this.DetailsGridView.DataSource = ds.Tables[0]; this.DetailsGridView.DataBind( ); } else { this.OrderDetailsPanel.Visible = false; } } private enum WhichTable { Orders, OrderDetails, }; private DataSet CreateDataSet(WhichTable theTable) { // connection string to connect to the Orders Database string connectionString = "Data Source=Brahams;Initial Catalog=Northwind;Integrated Security=True"; // Create connection object, initialize with // connection string and open the connection System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); DataSet dataSet = new DataSet( ); try { connection.Open( ); // Create a SqlCommand object and assign the connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection; StringBuilder sb = new StringBuilder( ); if (theTable == WhichTable.Orders) { sb.Append("select OrderID, c.CompanyName, c.ContactName, "); sb.Append(" c.ContactTitle, c.Phone, orderDate, "); sb.Append(" s.CompanyName as ShipperName"); sb.Append(" from orders o "); sb.Append(" join customers c on c.CustomerID = o.CustomerID"); sb.Append(" join shippers s on s.ShipperID = o.ShipVia"); sb.Append(" ORDER BY OrderID DESC"); } else { int index = OrdersGridView.SelectedIndex; int theOrderID = -1; if (index != -1) { // get the order id from the data grid DataKey key = OrdersGridView.DataKeys[index]; theOrderID = (int)key.Value; } sb.Append("Select od.OrderID, OrderDate, p.ProductID, "); sb.Append(" ProductName, od.UnitPrice, Quantity "); sb.Append("from Orders o "); sb.Append("join [Order Details] od on o.orderid = od.orderid "); sb.Append("join products p on p.productID = od.productid "); sb.Append("where od.OrderID = " + theOrderID); } command.CommandText = sb.ToString( ); SqlDataAdapter dataAdapter = new SqlDataAdapter( ); dataAdapter.SelectCommand = command; dataAdapter.TableMappings.Add("Table", theTable.ToString( )); dataAdapter.Fill(dataSet); } finally { connection.Close( ); } return dataSet; } protected void btnAdd_Click(object sender, EventArgs e) { string whichTransaction = this.rbTransactionType.SelectedValue.ToString( ); if (whichTransaction == "DB") UpdateDBTransaction( ); else UpdateConnectionTransaction( ); } private void UpdateConnectionTransaction( ) { string connectionString = "Data Source=Brahams;Initial Catalog=Northwind;Integrated Security=True"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); // declare the command object for the sql statements System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); // declare an instance of SqlTransaction SqlTransaction transaction = null; int OrderID = -1; try { // connection string to connect to the Bugs Database connection.Open( ); // begin the transaction transaction = connection.BeginTransaction( ); // attach the transaction to the command command.Transaction = transaction; // attach connection to the command command.Connection = connection; command.CommandText = "spAddOrder"; command.CommandType = CommandType.StoredProcedure; // declare the parameter object System.Data.SqlClient.SqlParameter param; // add each parameter and set its direciton and value param = command.Parameters.Add("@CustomerID", SqlDbType.NChar); param.Direction = ParameterDirection.Input; param.Value = this.ddlCustomer.SelectedItem.Value; param = command.Parameters.Add( "@ShipperID", SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = this.ddlShipper.SelectedValue; param = command.Parameters.Add( "@OrderID", SqlDbType.Int); param.Direction = ParameterDirection.Output; command.ExecuteNonQuery( ); // execute the sproc // retrieve the identity column OrderID = Convert.ToInt32(command.Parameters["@OrderID"].Value); // formulate the string to update the orderDetails string strAddOrderDetails = "Insert into [Order Details] " + "(OrderID, ProductID, UnitPrice, Quantity, Discount) " + "values(" + OrderID + ", " + this.ddlProduct.SelectedValue + ", " + this.txtUnitPrice.Text + ", " + this.txtQuantity.Text + ", " + this.txtDiscount.Text + ")"; // set up the command object to update the bug hsitory command.CommandType = CommandType.Text; command.CommandText = strAddOrderDetails; // execute the insert statement command.ExecuteNonQuery( ); // commit the transaction transaction.Commit( ); } catch (Exception e) { Trace.Write(e.Message); transaction.Rollback( ); } finally { connection.Close( ); } this.txtDiscount.Text = string.Empty; this.txtQuantity.Text = string.Empty; this.txtUnitPrice.Text = string.Empty; this.lblNewOrderID.Text = OrderID.ToString( ); } private void UpdateDBTransaction( ) { // connection string to connect to the Bugs Database string connectionString = "Data Source=Brahams;Initial Catalog=Northwind;Integrated Security=True"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // create a second command object for the bugs hisotry table System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection; command.CommandText = "spAddOrderTransactions"; command.CommandType = CommandType.StoredProcedure; // declare the parameter object System.Data.SqlClient.SqlParameter param; // add each parameter and set its direciton and value string customerID = this.ddlCustomer.SelectedValue.ToString( ); param = command.Parameters.AddWithValue("@CustomerID", customerID); param.DbType = DbType.StringFixedLength; param.Direction = ParameterDirection.Input; int shipperID = Convert.ToInt32(this.ddlShipper.SelectedValue); param = command.Parameters.AddWithValue("@ShipperID", shipperID); param.DbType = DbType.Int32; param.Direction = ParameterDirection.Input; int productID = Convert.ToInt32(this.ddlProduct.SelectedValue); param = command.Parameters.AddWithValue("@ProductID", productID); param.DbType = DbType.Int32; param.Direction = ParameterDirection.Input; decimal price = Convert.ToDecimal(this.txtUnitPrice.Text); param = command.Parameters.AddWithValue("@UnitPrice", price); param.DbType = DbType.Decimal; param.Direction = ParameterDirection.Input; Int16 quantity = Convert.ToInt16(this.txtQuantity.Text); param = command.Parameters.AddWithValue("@Quantity", quantity); param.DbType = DbType.Int16; param.Direction = ParameterDirection.Input; double discount = Convert.ToDouble(this.txtDiscount.Text); param = command.Parameters.AddWithValue("@Discount", discount); param.DbType = DbType.Double; param.Direction = ParameterDirection.Input; param = command.Parameters.AddWithValue("@orderID", 0); param.DbType = DbType.Int32; param.Direction = ParameterDirection.Output; command.ExecuteNonQuery( ); // execute the sproc param = command.Parameters["@OrderID"]; int newOrderID = (int) param.Value; this.lblNewOrderID.Text = newOrderID.ToString( ); } protected void OrdersGridView_PageIndexChanging( object sender, GridViewPageEventArgs e) { OrdersGridView.PageIndex = e.NewPageIndex; this.OrderDetailsPanel.Visible = false; BindGrid( ); } } // end class In Example 10-13, you factor out the common code for getting the Order and Order Details records into a single method, CreateDataSet , that takes an enumerated constant called WhichTable as an argument to indicate which table's records you are retrieving:
private enum WhichTable { Orders, OrderDetails, }; The CreateDataSet method tests the enumeration passed in to decide whether to query the Orders table or the Order Details table:
if (theTable == WhichTable.Orders) { sb.Append("select OrderID, c.CompanyName, c.ContactName, "); sb.Append(" c.ContactTitle, c.Phone, orderDate, "); sb.Append(" s.CompanyName as ShipperName"); sb.Append(" from orders o "); sb.Append(" join customers c on c.CustomerID = o.CustomerID"); sb.Append(" join shippers s on s.ShipperID = o.ShipVia"); sb.Append(" ORDER BY OrderID DESC"); } else // get order details { int index = OrdersGridView.SelectedIndex; int theOrderID = -1; if (index != -1) { // get the order id from the data grid DataKey key = OrdersGridView.DataKeys[index]; theOrderID = (int)key.Value; } sb.Append("Select od.OrderID, OrderDate, p.ProductID, "); sb.Append(" ProductName, od.UnitPrice, Quantity "); sb.Append("from Orders o "); sb.Append("join [Order Details] od on o.orderid = od.orderid "); sb.Append("join products p on p.productID = od.productid "); sb.Append("where od.OrderID = " + theOrderID); } When the page is first loaded the data grid is bound by calling BindGrid :
if (!IsPostBack) { BindGrid( ); //... } BindGrid gets the dataset for Orders and binds the OrdersGridView to that dataset's first table:
private void BindGrid( ) { DataSet ds = CreateDataSet(WhichTable.Orders); OrdersGridView.DataSource = ds.Tables[0]; OrdersGridView.DataBind( ); } The full Page_Load method then goes on to fill the three drop- downs ( Customers , Shippers , and Products ):
if (!IsPostBack) { BindGrid( ); ddlCustomer.DataSource = GetDataReader("Customers"); ddlCustomer.DataBind( ); ddlShipper.DataSource = GetDataReader("Shippers"); ddlShipper.DataBind( ); ddlProduct.DataSource = GetDataReader("Products"); ddlProduct.DataBind( ); } The common work of getting the DataReader to bind to is factored out into the GetdataReader method, which takes the name of the table as a parameter:
private SqlDataReader GetDataReader(string whichTable) { // connection string to connect to the Bugs Database string connectionString = "Data Source=Brahams;Initial Catalog=Northwind;Integrated Security=True"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection( connectionString); connection.Open( ); // Create a SqlCommand object and assign the connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection = connection; // set the stored procedure to get the bug records command.CommandText = "select * from " + whichTable; // return the data reader return command.ExecuteReader( CommandBehavior.CloseConnection); } The drop-down's job is to present the text to the user and to return the ID to the program. This is accomplished by declaring the DataValueField and the DataTextField for each drop-down, as shown in Example 10-12:
<asp:DropDownList ID="ddlShipper" runat="server" DataValueField="ShipperID" DataTextField="CompanyName" Width="120" /> To create a new order using transactions, the user chooses a customer, shipper, and product from the drop-downs and then fills in the Unit Price, Quantity, and Discount. To illustrate the different code for DB-based versus connection-based transactions, you'll ask the user to select which type of transaction support to use (via the radio buttons ) and then to click the Add button, as shown in Figure 10-17.
Figure 10-17. Placing a new order
10.6.2.1. Data Base transactions
If the user chooses Data Base Transaction, the code branches to the method UpdateDBTransaction . This invokes the stored procedure spAddOrderTransactions shown in Example 10-14.
Example 10-14. spAddOrderTransactions stored procedure
CREATE PROCEDURE spAddOrderTransactions @CustomerID nchar (5), @ShipperID int, @ProductID int, @UnitPrice money, @Quantity smallint, @Discount real, @OrderID int output AS Begin Transaction -- declare @OrderID int Insert into Orders (CustomerID, ShipVia) values (@CustomerID, @ShipperID) select @OrderID = @@identity if @@Error <> 0 goto ErrorHandler Insert into [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) values ( @OrderID, @ProductID, @UnitPrice, @Quantity, @Discount) if @@Error <> 0 goto ErrorHandler commit transaction return ErrorHandler: rollback transaction return GO
The responsibility for managing the transaction, committing it if both tables are updated, and rolling it back if anything goes wrong, is entirely within the stored procedure. The only job of UpdateDBTransactions is to invoke the stored procedure and pass in the appropriate parameters.
10.6.2.2. Connection transactions
If, on the other hand, the user chooses to use connection transactions , then your code will be more complex. The steps are as follows:
-
Create the connection string and the SqlConnection object.
-
Create the SqlCommand object and assign the connection to its Connection property:
command.Connection = connection;
-
Open a try block in which you will try to update the two tables. If an exception is thrown, you will catch the exception and roll back the transaction; otherwise, you'll commit the transaction.
-
Open the connection.
-
Instantiate a SqlTransaction object by calling the BeginTransaction method of the SqlConnection object:
transaction = connection.BeginTransaction( );
-
Set the SqlCommand object's transaction property to the SqlTransaction object you've instantiated :
command.Transaction = transaction;
-
Set the SqlCommand object's Connection property to the SqlConnection object you've created.
Your transaction, in this case, will consist of a call to a stored procedure to add the record to the Orders table ( spAddOrder ) which will return the OrderID of the new order, as shown in Example 10-15.
Example 10-15. spAddOrder
CREATE PROCEDURE spAddOrder @CustomerID nChar, @ShipperID integer, @OrderID integer out as Insert into Orders values ( @CustomerID, 1, GetDate(), GetDate( ), GetDate( ), @ShipperID, 0, null, null, null, null, null, null)
You'll then use direct SQL code to add to the OrdersDetail page. If both steps succeed, you will commit the transaction; otherwise, you'll roll back the entire transaction.
You accomplish this by putting all the work into a try/catch block. If an exception is caught, something went wrong, and you will roll back the transaction; otherwise, you commit it.
try { // db work done here transaction.Commit( ); } catch (Exception e) { Trace.Write(e.Message); transaction.Rollback( ); } finally { connection.Close( ); } The last line in the TRy block is the Commit statement. This will only be called if no exception has been thrown.
EAN: 2147483647
Pages: 173