- Hour 18: Working with Transactions
- Transactions and ADO.NET
- Transactions with Stored Procedures
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Transactions and ADO.NET
The ADO.NET Connection object is used to work with transactions. As you'll see in the next few sections, the Connection object is used to initiate the transaction. Any command objects that need to enlist in the transaction are then assigned to the transaction. The transaction object itself is used to save, roll back, and perform other actions on the transaction.
Starting a Transaction
In ADO.NET, you can start a database transaction by calling the BeginTransaction() method of the Connection object, as seen in Listing 18.1.
Listing 18.1 Starting a New Transaction
'Create Connection
Dim conn as SqlConnection = new SqlConnection("Data Source=(local);" +
"Initial Catalog=northwind;UID=sa;PWD=;")
'Create Command
Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
'Connection must be open to start transaction
conn.Open()
'Create Transaction and apply it to command object
Dim myTrans = conn.BeginTransaction("TransactionName")
NOTE
In order to begin a new transaction, the Connection object must be open. If you attempt to start a new transaction without first calling the Open() method of the Connection object, an InvalidOperationException is thrown.
Notice that the BeginTransaction() method returns a SqlTransaction object. For the life of the transaction, you will use this object to manipulate the database transaction, as needed.
At this point, you might be tempted to start executing commands against the database. However, before you can send any queries to the database using this transaction, you must assign the transaction object to the Transaction property of the Command object as seen in the last line of Listing 18.1. This is slightly counterintuitive, because the Command object already has an associated Connection object assigned, and transactions operate over a connection. However, you must still remember this step.
CAUTION
Do not forget to assign the SqlTransaction object returned by the BeginTransaction() method to the Transaction property of the Command object. If you forget, an InvalidOperationException will be thrown by your code the first time you attempt to execute a query.
Now you're ready to make some database changes using the Command object. Feel free to be creative. You can even delete an entire table, if you like. Because you're executing all these changes in a transaction, it's very easy to roll back your changes and return to the table's original state.
CAUTION
Keep in mind that only changes made to the database through this Connection object will be in the transaction. Therefore, if you were to modify the data through any other means, such as SQL Enterprise Manager, those changes will not be rolled back.
Rolling Back a Transaction
As you process each of the individual database changes that comprise your database transaction, it's possible to undo or roll back any of the changes you've made to the database since beginning the transaction. For instance, alluding to the monetary transaction example from earlier this hour, if there is an error removing the funds from bank account A after placing the money in account B, you would definitely want to roll back the changes you've made and either retry the transaction at a later time, or flag the record for later examination.
To roll a transaction back to its original state, you just call the Rollback() method of the transaction object, as in Listing 18.2.
Listing 18.2 Rolling a Transaction Back
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<LINK rel="stylesheet" type="text/css" href="Main.css">
<!-- End Style Sheet -->
<script language="VB" runat="server" >
Sub Page_Load(Source as Object, E as EventArgs)
If IsPostBack then
Dim sOutput as string
'Create Connection
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
"localhost;Initial Catalog=northwind;UID=sa;PWD=;")
'Create Command
Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
'Connection must be open to start transaction
conn.Open()
'Create Transaction and apply it to command object
Dim myTrans = conn.BeginTransaction("TransactionName")
cmd.Transaction = myTrans
'Show Database before modifications
GetAndBindData(datagrid1)
'Execute Database Change #1
ExecuteSQL(cmd, "UPDATE Fruits SET Quantity = 4 " + _
"WHERE Name = 'Apple'")
'Execute Database Change #2
Try
ExecuteSQL(cmd, "DELETE FROM FRUITS")
Throw New Exception("A random horrible database error")
myTrans.Commit()
Catch
myTrans.Rollback()
End Try
'Requery to make sure changes are gone
GetAndBindData(datagrid7)
conn.Close()
Else
'--- No post back ---
'Just display the fruit table as it is
GetAndBindData(datagrid1)
End If 'Postback
End Sub
Private Sub ExecuteSQL(cmd as SqlCommand, sSQL as string)
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
End Sub
Private Sub GetAndBindData( myDataGrid as _
System.Web.UI.WebControls.DataGrid )
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
localhost;Initial Catalog=Northwind;UID=sa;PWD=; ")
Dim cmd as SqlCommand = new SqlCommand("SELECT name, " + _
"description, quantity FROM Fruits", conn)
conn.Open()
myDataGrid.DataSource = cmd.ExecuteReader()
myDataGrid.DataBind()
conn.Close()
End Sub
</script>
</HEAD>
<BODY>
<h1>Working With Transactions</h1>
<hr>
<form runat="server" id=form1 name=form1>
<asp:label id=lblStatus runat="server" />
<p>
<input type="submit" Value="Run Queries">
<table align="center" cellpadding=10 cellspacing=10>
<tr>
<td colspan=2 valign="center">
<h3>Original Data:</h3>
<asp:datagrid id=datagrid1 runat="server" />
<br><br>
</td>
</tr>
</tr>
<td colspan=2>
<h3>After rolling back transaction to beginning:</h3>
<asp:datagrid id=datagrid7 runat="server" />
</td>
</tr>
</table>
</form>
<hr>
</BODY>
</HTML>
Listing 18.2 creates a transaction, performs some database changes, and then rolls these changes back. On line 28, the transaction is started using the BeginTransaction() method of the Connection object. On the following line, the transaction object is assigned to the Command object. Any queries executed using the cmd Command object are performed under the umbrella of this transaction.
The database is then displayed before modifications using the GetAndBindData() method on line 32. On lines 34–36, the first database change is made, changing the quantity of apples to 4. Lines 37–43 perform the next database change. Then, an error is simulated using the Throw() method on line 39, causing the Rollback() method to run in the Catch code block on line 41. Had there been no error, the transaction would have been committed and all database changes made final. However, because the Rollback() method was called instead, all changes made under this transaction are reverted back to their previous state.
Figure 18.1 The appearance of the Web form in Listing 18.2 after submitting the form.
){kind=link}
As you can see in Figure 18.1, the code in Listing 18.2 displays a Web form. After you click the button labeled Run Queries, a connection to the database is opened and a transaction started. Database change #1 is made, and then the second change is attempted. The second database change is wrapped in a Try...Catch block. This enables you to check for an error and handle that error gracefully. In this case, the transaction is rolled back, canceling any changes. Otherwise, the transaction is saved.
Committing a Transaction
In Listing 18.2, after all database modifications have been successfully performed, the transaction is saved by using the Commit() method of the transaction object. This ends the transaction and makes all database changes permanent.
Canceling a Transaction
If an error had occurred during the second database modification in Listing 18.2, the error would have been caught by the Try...Catch block and the Catch portion of the code would have been executed. In this case, the Rollback() method of the transaction object would have been called, reversing all changes made to the database, leaving it in its original state.
Saving a Transaction
When working with database transactions, it's possible to save the transaction at any point. However, the term "saving" when applied to transactions is a bit misleading. The process is more like bookmarking a site than saving a file. After saving at a point in a transaction, you can revert back to that point, canceling any changes you might have made after saving.
In other words, let's say you initiate a transaction, and save the transaction after updating several hundred fields in the database. Suppose that later, after several other database modifications, an error occurs. Rather than rolling all the way back to the beginning, you can save your transaction after the first batch of queries is executed. Then, later you can roll back your changes to that saved point. However, this method can be dangerous if any table relationships are missing, creating an unstable database.
To save a database transaction at any point, you need only call the Save() method of the transaction object, and pass it the name you would like to use to reference the saved point. Saving a transaction often makes more sense when working with hundreds or thousands of database changes in a single transaction. Without saving, you'd be forced to roll back all of these changes in the case of an error!
The example in Listing 18.3 demonstrates this concept. Just as in the previous example, a database connection is opened and a transaction initiated. Some changes are made to the data in the database, the transaction is saved, some more changes are made, and then the transaction is rolled back to the saved point. The transaction is then committed, finalizing all the changes made up to the saved point. Figure 18.2 shows the appearance of the Web form after submitting the form.
){kind=link}
Listing 18.3 Rolling a Transaction Back to a Saved Point
<%@ Page Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<LINK rel="stylesheet" type="text/css" href="Main.css">
<!-- End Style Sheet -->
<script language="VB" runat="server" >
Sub Page_Load(Source as Object, E as EventArgs)
If IsPostBack then
Dim sOutput as string
'Create Connection
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
"localhost;Initial Catalog=northwind;UID=sa;PWD=;")
'Create Command
Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
'Connection must be open to start transaction
conn.Open()
'Create Transaction and apply it to command object
Dim myTrans = conn.BeginTransaction("TransactionName")
cmd.Transaction = myTrans
'Show Database before modifications
GetAndBindData(datagrid1)
'Execute Database Change #1
ExecuteSQL(cmd, "UPDATE Fruits SET Quantity = 2 " + _
"WHERE Name = 'Apple'")
'Insert potentially hundreds of database changes here
'Save Transaction
myTrans.Save("SavePoint1")
'Execute Database Change #2
ExecuteSQL(cmd, "DELETE FROM FRUITS")
'Oh no! We've made a horrible mistake.
'Rollback to saved point
myTrans.Rollback("SavePoint1")
'Commit earlier changes
myTrans.Commit()
'Requery
GetAndBindData(datagrid7)
conn.Close()
Else
'--- No post back ---
'Just display the fruit table as it is
GetAndBindData(datagrid1)
End If 'Postback
End Sub
Private Sub ExecuteSQL(cmd as SqlCommand, sSQL as string)
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
End Sub
Private Sub GetAndBindData( myDataGrid as _
System.Web.UI.WebControls.DataGrid )
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
"localhost;Initial Catalog=Northwind;UID=sa;PWD=;")
Dim cmd as SqlCommand = new SqlCommand("SELECT name, " + _
"description, quantity FROM Fruits", conn)
conn.Open()
myDataGrid.DataSource = cmd.ExecuteReader()
myDataGrid.DataBind()
conn.Close()
End Sub
</script>
</HEAD>
<BODY>
<h1>Working With Transactions</h1>
<hr>
<form runat="server" id=form1 name=form1>
<asp:label id=lblStatus runat="server" />
<p>
<input type="submit" Value="Run Queries" id=submit1 name=submit1>
<table align="center" cellpadding=10 cellspacing=10>
<tr>
<td colspan=2 valign="center">
<h3>Original Data:</h3>
<asp:datagrid id=datagrid1 runat="server" />
<br><br>
</td>
</tr>
</tr>
<td colspan=2>
<h3>After rolling back transaction to saved point:</h3>
<asp:datagrid id=datagrid7 runat="server" />
</td>
</tr>
</table>
</form>
<hr>
</BODY>
</HTML>
Listing 18.3 is similar to the previous transaction code provided in Listing 18.2. Line 29 starts a transaction that is then assigned to the cmd Command object on the next line. The current state of the database is displayed using the GetAndBindData() method on line 33. A database change is then made on line 36, changing the quantity of apples from 2 to 4.
Shortly after on line 42, the transaction is saved using the Save() method of the transaction object. The following line of code then deletes all contents from the Fruit table. For the purposes of this example, that is undesirable. On line 49, the transaction is rolled back to the previous save point, before the deletion was performed. Line 52 uses the Commit() method to end the transaction and commit these database changes. Line 55 again shows the state of the database. As you can see in Figure 18.2, the quantity of apples is modified, but the results of the DELETE query were not saved.
Figure 18.2 Saving a database transaction.