Using the SQLCommand Object - Executing Transactions
(Page 5 of 5 )
Transactions enable you to group together multiple operations that can be performed as a single unit of work, which helps to ensure database integrity. For instance, transferring funds from your savings account to your checking account involves multiple database operations, and the transfer cannot be considered complete unless all of the operations are successfully completed. A typical transfer from your savings account to your checking account requires two separate but related operations: a withdrawal from your savings account and a deposit to your checking account. If either operation fails, the transfer is not completed. Therefore, both of these functions would be considered part of the same logical transaction. From the database standpoint, to ensure database integrity, both the withdrawal and the deposit would be grouped together as a single transaction. If the withdrawal operation succeeded but the deposit failed, the entire transaction could be rolled back, which would restore the database to the condition it had before the withdrawal operation was attempted. Using transactions is an essential part of most production-level database applications.
ADO.NET supports transactions using the Transaction classes. In order to incorporate transactions into your ADO.NET application you first need to create an instance of the SqlTransaction object and then execute the BeginTransaction method to mark the beginning of a transaction. Under the covers this will cause the database server to begin a transaction. For instance, using the SqlTransaction object to issue a BeginTransaction statement will send a T-SQL BEGIN TRANSACTION command to SQL Server. After the transaction has started, the database update operations are performed and then the Commit method is used to actually write the updates to the target database. If an error occurs during the process, then the RollBack operation is used to undo the changes. The following SQLCommandTransaction subroutine shows how to start a transaction and then either commit the results of the transaction to the database or roll back the transaction in the event of an error:
Private Sub SQLCommandTransaction(ByVal sServer As String, _
ByVal sDB As String)
Dim cn As New SqlConnection("SERVER=" & sServer _
& ";INTEGRATED SECURITY=True;DATABASE=" & sDB)
Dim cmd As New SqlCommand()
Dim trans As SqlTransaction
' Start a local transaction
cn.Open()
trans = cn.BeginTransaction()
cmd.Connection = cn
cmd.Transaction = trans
Try
' Insert a row transaction
cmd.CommandText = _
"INSERT INTO Department VALUES(100, 'Transaction 100' )"
cmd.ExecuteNonQuery()
' This will result in an error
cmd.CommandText = _
"INSERT INTO Department VALUES(100, 'Transaction 101' )"
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
MsgBox(e.Message)
trans.Rollback()
Finally
cn.Close()
End Try End Sub
end sub
You can see the C# version of the SQLCommandTransaction subroutine in the following listing:
private void SQLCommandTransaction(string sServer, string sDB)
{
SqlConnection cn = new SqlConnection("SERVER=" + sServer
+ ";INTEGRATED SECURITY=True;DATABASE=" + sDB);
SqlCommand cmd = new SqlCommand();
SqlTransaction trans;
// Start a local transaction
cn.Open();
trans = cn.BeginTransaction();
cmd.Connection = cn;
cmd.Transaction = trans;
try
{
// Insert a row transaction
cmd.CommandText =
"INSERT INTO Department VALUES(100, 'Transaction 100')";
cmd.ExecuteNonQuery();
// This will result in an error
cmd.CommandText =
"INSERT INTO Department VALUES(100, 'Transaction 101')";
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
trans.Rollback();
}
finally
{
cn.Close();
}
}In the beginning of this subroutine, you can see where new instances of the SqlConnection and SqlCommand objects are created, followed by the definition of a SqlTransaction object named trans. Next, a local transaction is started by first opening the connection and then using the cn SqlConnection object’s BeginTransaction method to create a new instance of a SqlTransaction object. Note that the connection must be open before you execute the BeginTransaction method. Next, the cmd SqlCommand Connection property is assigned with the cn SqlConnection and the Transaction property is assigned with the trans SqlTransaction object.
Within the Try-Catch block, two commands are issued that are within the local transaction scope. The first command is an INSERT statement that inserts two columns into the Department table that was created previously in this chapter. The first insert statement adds the DepartmentID of 100 along with a DepartmentName value of 'Transaction 100'. The SqlCommand ExecuteNonQuery method is then used to execute the SQL statement. Next, the cmd object’s CommandText property is set to another SQL INSERT statement. However, this statement will cause an error because it is attempting to insert a duplicate primary key value. In this second case, the DepartmentID of 100 is attempted to be inserted along with the DepartmentName value of 'Transaction 101'. This causes an error because the DepartmentID of 100 was just inserted by the previous INSERT statement. When the ExecuteNonQuery method is executed, the duplicate primary key error will be issued and the code in the Catch portion of the Try-Catch block will be executed.
Displaying the exception message in a message box is the first action that happens within the Catch block. You can see an example of this message in Figure 8-2.
Figure 8-2. A duplicate primary key error prevents the Commit operation.
After the message box is displayed, the trans SqlTransaction object’s RollBack method is used to roll back the attempted transaction. Note that because both insert statements were within the same transaction scope both insert operations will be rolled back. The resulting department table will not contain either DepartmentID 100 or DepartmentID 101.
In this example, a Finally block is used to close the cn SqlConnection object. If the Finally block is attached to the Try-Catch structure, the code in the Finally block will always be executed.
Summary
In this chapter, you saw several examples illustrating how to execute commands using the .NET Framework Data Provider for SQL Server’s SqlCommand object. The examples in the first part of the chapter illustrated how to use the SqlCommand object to execute SQL statements and stored procedures. In the second part of the chapter, you learned how to use transactions. The next chapter begins where this chapter leaves off: you’ll see how to execute commands using the OracleCommand object.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is taken from chapter 8 of ADO.NET: The Complete Reference, written by Michael Otey and Denielle Otey (McGraw-Hill/Osborne, 2004; ISBN: 0072228989). Check it out at your favorite bookstore. Buy this book now.
|
|
