Using the OracleCommand Object - Executing Transactions with Parameterized SQL Statements
(Page 4 of 4 )
Transactions are an essential part of most production-level database applications. Transactions ensure database integrity by enabling you to group together multiple operations as a single unit of work. For instance, when a product is shipped, an update would be required to the shipping table to indicate an in-transit status and a corresponding update would be required to the inventory table to reflect the new inventory levels. To ensure database integrity, both the shipment and the inventory update are combined into a single transaction. If either operation fails, the transaction is not completed and both operations need to be rolled back, restoring the database to its original condition.
To incorporate Oracle database transactions into your ADO.NET applications, you first need to create an instance of the OracleTransaction object and then execute the BeginTransaction method to mark the beginning of a transaction. After the transaction has started, the database update operations are performed and then the Commit method is used to write the updates to the target database. If an error occurs during the process, the RollBack operation is used to undo the changes. In the following OracleCommandTransaction subroutine, you’ll see how to commit and roll back database transactions using the OracleCommand object.
Private Sub OracleCommandTransaction(ByVal sServer As String, _
ByVal sUser As String, ByVal sPassword As String)
Cursor.Current = Cursors.WaitCursor
Dim cn As New OracleConnection("DATA SOURCE=" & sServer
_
& ";UID=" & sUser & ";PWD=" & sPassword)
Dim cmd As New OracleCommand
cmd.CommandType = CommandType.Text
Dim trans As OracleTransaction
' Start a local transaction
cn.Open()
cmd.Connection = cn
trans = cn.BeginTransaction()
cmd.Transaction = trans
cmd.CommandText = _
"INSERT INTO DEPT(DEPTNO, DNAME, LOC) " & _
"VALUES(:I_DEPTNO, :I_DNAME, :I_LOC)"
'Create the input parameters
Dim parmDEPTNO = _
New OracleParameter("I_DEPTNO", OracleType.Number)
parmDEPTNO.Direction = ParameterDirection.Input
parmDEPTNO.Value = 50
Dim parmDNAME = _
New OracleParameter("I_DNAME", OracleType.VarChar, 14)
parmDNAME.Direction = ParameterDirection.Input
parmDNAME.Value = "IT"
Dim parmLOC = _
New OracleParameter("I_LOC", OracleType.VarChar, 13)
parmLOC.Direction = ParameterDirection.Input
parmLOC.Value = "SAN JOSE"
' Add the parameter objects to the Parameter's collection
cmd.Parameters.Add(parmDEPTNO)
cmd.Parameters.Add(parmDNAME)
cmd.Parameters.Add(parmLOC)
Try
' Insert a row transaction
cmd.Prepare()
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
trans.Rollback()
Cursor.Current = Cursors.Default
MsgBox(e.Message)
Finally
cn.Close()
End Try
Cursor.Current = Cursors.Default
End Sub
You can see the C# version of the OracleCommandTransaction subroutine in the following listing:
private void OracleCommandTransaction(string sServer,
string sUser, string sPassword)
{
Cursor.Current = Cursors.WaitCursor;
OracleConnection cn = new OracleConnection("DATA SOURCE=" +
sServer + ";UID=" + sUser + ";PWD=" + sPassword);
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
OracleTransaction trans;
// Start a local transaction
cn.Open();
cmd.Connection = cn;
cmd.CommandText = "DELETE DEPT WHERE DEPTNO = 50";
cmd.ExecuteNonQuery();
trans = cn.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandText =
"INSERT INTO DEPT(DEPTNO, DNAME, LOC) " +
"VALUES(:I_DEPTNO, :I_DNAME, :I_LOC)";
// Create the input parameters
OracleParameter parmDEPTNO =
new OracleParameter("I_DEPTNO", OracleType.Number);
parmDEPTNO.Direction = ParameterDirection.Input;
parmDEPTNO.Value = 50;
OracleParameter parmDNAME =
new OracleParameter("I_DNAME", OracleType.VarChar, 14);
parmDNAME.Direction = ParameterDirection.Input;
parmDNAME.Value = "IT";
OracleParameter parmLOC =
new OracleParameter("I_LOC", OracleType.VarChar, 13);
parmLOC.Direction = ParameterDirection.Input;
parmLOC.Value = "SAN JOSE";
// Add the parameter objects to the Parameter's collection
cmd.Parameters.Add(parmDEPTNO);
cmd.Parameters.Add(parmDNAME);
cmd.Parameters.Add(parmLOC);
try
{
// Insert a row transaction
cmd.Prepare();
cmd.ExecuteNonQuery();
trans.Commit();
}
catch(Exception ex)
{
trans.Rollback();
Cursor.Current = Cursors.Default;
MessageBox.Show("Connection error: :" + ex.ToString());
}
finally
{
cn.Close();
}
Cursor.Current = Cursors.Default;
}
At the top of the OracleCommandTransaction subroutine, you can see where new instances of the OracleConnection and OracleCommand objects are created. Next, an instance of an OracleTransaction object named trans is instantiated. The database transaction is started by first opening the connection and then using the OracleConnection object’s BeginTransaction method. Next, the cmd OracleComm and Transaction is assigned with the new OracleTransaction object named trans.
The next section of code sets up the parameterized SQL statement and its associated parameter objects. First, the OracleCommand object’s CommandText property is assigned a SQL Insert statement. The important thing to note about this line is the use of the named parameters. Following the Oracle conventions, all of the parameter names begin with a colon. The next section of code creates three OracleParameter objects: parmDEPTNO, parmDNAME, and parmLOC. These OracleParameter objects are used to supply the input values to the parameterized Insert statement. In this example, the first parameter associated with the parmDEPTNO object is set to a value of 50. The second parameter represented by the parmDname object is set to a value of "IT". And the third parameter represented by the parmLOC OracleParameter object is assigned the value of "SAN JOSE". Then all of the OracleParameter objects are added to the Parameters collection of the OracleCommand object named cmd.
In the next section of code, a Try-Catch-Finally block is used to prepare the SQL statement and then execute the statement and commit the transactions to the database. The Prepare method parses the SQL statement and causes the database to create an execution plan. The ExecuteNonQuery method is then used to execute the Insert statement. At this point, the transaction can still be rolled back. The transaction is committed to the database using the OracleTransaction object’s Commit method. If an error occurs, the code in the Catch block is executed. Within the Catch block, the transaction is first rolled back using the OracleTransaction object’s Rollback method. Then a message box displays the error message to the end user. A Finally block is used to close the cn OracleConnection object. The Finally block is always executed, which in this case means that the Oracle connection is always closed at the end of the Try-Catch-Finally block.
--------------------------------------------------------------------
Summary
The examples in this chapter illustrated how to use the OracleCommand object to execute SQL statements and stored procedures. The next chapter continues the discussion of the ADO.NET Command objects by illustrating how to use the OleDbCommand 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 9 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.
|
|
