Using the OracleCommand Object

In the previous chapter, you saw how to use the SqlCommand object to execute SQL statements and stored procedures on a SQL Server database. This chapter picks up where Chapter 8 leaves off, and here you see how to use the OracleCommand object to execute SQL statements and stored procedures on an Oracle 9i database. In the first part of this chapter, you learn how to use the visual OracleCommand object to execute simple SQL DDL action queries on the target database. Next, the chapter provides an example that illustrates how to execute an Oracle stored procedure with named parameters. In the final example, you see how to use the OracleCommand object to execute transactions.

--------------------------------------------------------------------

The OracleCommand object is used to execute SQL action queries, stored procedures, and packages on an Oracle 8i or later database. Two of the most essential database application actions, executing dynamic SQL statements and stored procedures, are the basis for most production ADO.NET applications. Dynamic SQL statements are parsed by the database server and executed when they are sent to the database server from the client application. In contrast, stored procedures are typically created in advance by the DBA and then called by the database applications. Stored procedures offer both a performance and security advantage over dynamic SQL. Packages and stored procedures enjoy a performance advantage because the jobs of parsing the SQL statements and creating the data access plan are enacted when the procedure is first created—not at runtime. They gain a security advantage because they allow the developer to funnel database access through a predefined interface supplied by the procedure rather than permitting access to the base tables. This helps to prevent hackers from using techniques such as SQL injection to gain knowledge about the underlying database structure and contents. The first example in this chapter illustrates how to use the OracleCommand object to execute a SQL DDL statement that creates a stored procedure. The next example illustrates how to call that stored procedure.

Using the Visual Studio OracleCommand Object

The simplest place to start using the OracleCommand object is the visual component provided as a part of Visual Studio.NET IDE. You can drag and drop an instance of the OracleCommand from Visual Studio.NET’s Data Toolbox onto the visual designer, which adds a new OracleCommand object to your project’s components pane, as shown in Figure 9-1.

Once the visual OracleCommand component has been added, it is ready to use. The following example illustrates how to execute a dynamic SQL DDL statement that creates a stored procedure with the visual OracleCommand component. Dynamic SQL provides an extremely flexible mechanism for working with the database. You can use

Figure 9-1. Adding the visual OracleCommand object to a Visual Studio.NET project

it to execute ad hoc queries and return the results from action queries, as well as to execute SQL DDL statements to create database objects as shown here:

Private Sub OracleCommandComponent(ByVal sServer As String, _
ByVal sUser As String, ByVal sPassword As String)
Cursor.Current = Cursors.WaitCursor
OracleConnection1.ConnectionString = "DATA SOURCE=" & sServer _
& ";UID=" & sUser & ";PWD=" & sPassword
'Set up a query
OracleCommand1.CommandText = _
"CREATE OR REPLACE PROCEDURE GETEMPDETAILS" _
& " ( I_EMPNO IN number," _
& "O_ENAME OUT varchar," _
& "O_JOB OUT varchar," _
& "O_SAL OUT number " _
& " ) IS " _
& "BEGIN" _
& " SELECT ENAME, JOB, SAL " _
& " INTO O_ENAME, O_JOB, O_SAL" _
& " FROM EMP WHERE EMPNO=I_EMPNO;" _
& " END; "
' Set the active connection
OracleCommand1.Connection = OracleConnection1
Try
' Open a connection and execute the command
OracleConnection1.Open()
OracleCommand1.ExecuteNonQuery()
Catch ex As Exception
Cursor.Current = Cursors.Default
MessageBox.Show("Connection error: :" & ex.ToString())
End Try
OracleConnection1.Close()
Cursor.Current = Cursors.Default
End Sub

The following is an example of the OracleCommandComponent subroutine written in C#:

private void OracleCommandComponent(string sServer, string sUser,
string sPassword)
{
Cursor.Current = Cursors.WaitCursor;
oracleConnection1.ConnectionString = "DATA SOURCE=" + sServer
+ ";UID=" + sUser + ";PWD=" + sPassword;
// Set up a query
oracleCommand1.CommandText =
"CREATE OR REPLACE PROCEDURE GETEMPDETAILS"
+ " ( I_EMPNO IN number,"
+ "O_ENAME OUT varchar,"
+ "O_JOB OUT varchar,"
+ "O_SAL OUT number "
+ " ) IS "
+ "BEGIN"
+ " SELECT ENAME, JOB, SAL "
+ " INTO O_ENAME, O_JOB, O_SAL"
+ " FROM EMP where EMPNO=I_EMPNO;"
+ " END; ";
// Set the active connection
oracleCommand1.Connection = oracleConnection1;
try
{
// Open a connection and execute the command
oracleConnection1.Open();
oracleCommand1.ExecuteNonQuery();
}
catch(Exception ex)
{
Cursor.Current = Cursors.Default;
MessageBox.Show("Connection error: :" + ex.ToString());
}
oracleConnection1.Close();
Cursor.Current = Cursors.Default;
}

In the beginning of the OracleCommandComponent routine, you can see where three variables are passed in as parameters. The sServer variable contains the name of the Oracle database system, the sUser variable contains the name of the database user, and the sPassword variable contains the password for the Oracle database login. This example login has been set up to use the Oracle sample SCOTT database. Next, the routine sets the OracleConnection1 object’s ConnectionString property using the DATA SOURCE, UID, and PWD keywords in conjunction with the variables passed in at the top of the subroutine. Then the OracleCommand1 object’s CommandText property is assigned a SQL statement that will create an Oracle stored procedure named GETEMPDETAILS. This stored procedure takes one input parameter, which consists of the employee number. It returns three parameters containing the employee name, job description, and salary that are retrieved from the EMP table.

The next statement sets the OracleCommand1 object’s Connection property to the OracleConnect1 object. Then a Try-Catch block is set up to open the connection to the Oracle database and execute the SQL statement. First the OracleConnection1.Open method is used to start a connection to the Oracle database and then the OracleCommand1 object’s ExecuteNonQuery method is used to execute the SQL statement. The OracleCommand object supports several different command execution methods, which are listed in Table 9-1.

After the ExecuteNonQuery method has finished, the OracleConnection object is closed and the subroutine ends. If an error occurs during the execution of either the Open method or the ExecuteNonQuery method, a message box appears showing the exception text.

Table 9-1. OracleCommand SQL Statement Execution Methods

Executing Stored Procedures with Output Parameters

Stored procedures are the core of most database applications. Stored procedures have significant performance benefits because they are compiled before they are used. This allows the database to forego the typical parsing steps that are required to process dynamic SQL, as well as skip the creation of an access plan. The OracleCommand object is the primary ADO.NET mechanism for executing stored procedures against an Oracle database. To use the OracleCommand class in your project, you first need to add an import directive for the System.Data.OracleClient Namespace in your project. You can find an example showing how to create an import directive for the System.Data.OracleClient in Chapter 5.

The following listing presents the code required to use the GETEMPDETAILS stored procedure that was created in the preceding example in this chapter. If you look back at the preceding code listing, you can see that the GETEMPDETAILS stored procedure accepts a single numeric input parameter. That’s used to identify the employee whose information will be retrieved. The GETEMPDETAILS stored procedure returns the employee name, job description, and salary from the EMP table in the Oracle sample SCOTT database. After the GETEMPDETAILS stored procedure has been created in the SCOTT database, it can then be called from your ADO.NET applications using the OracleCommand object. The following shows how to use the OracleCommand object to execute the GETEMPDETAILS stored procedure and retrieve the value that it returns:

Private Sub OracleCommandSPParms(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)
'Setup the Command object's Connection, SQL and parameter types
Dim cmd As New OracleCommand("GETEMPDETAILS", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim parmEMPNO = New OracleParameter("I_EMPNO", _
OracleType.Number)
parmEMPNO.Direction = ParameterDirection.Input
Dim parmENAME = New OracleParameter("O_ENAME", _
OracleType.VarChar, 10)
parmENAME.Direction = ParameterDirection.Output
Dim parmJOB = New OracleParameter("O_JOB", _
OracleType.VarChar, 9)
parmJOB.Direction = ParameterDirection.Output
Dim parmSAL = New OracleParameter("O_SAL", OracleType.Number)
parmSAL.Direction = ParameterDirection.Output
' Add the parameter objects to the Command Parameters collection
cmd.Parameters.Add(parmEMPNO)
cmd.Parameters.Add(parmENAME)
cmd.Parameters.Add(parmJOB)
cmd.Parameters.Add(parmSAL)
Try
' Open the connection & prepare the command
cn.Open()
' Execute the prepared SQL statement to retrieve EMPNO 7788
parmEMPNO.Value = 7788
cmd.ExecuteNonQuery()
Debug.WriteLine(parmENAME.Value)
Debug.WriteLine(parmJOB.Value)
Debug.WriteLine(parmSAL.Value)
Catch e As Exception
Cursor.Current = Cursors.Default
MsgBox(e.Message)
End Try
cn.Close()
Cursor.Current = Cursors.Default
End Sub

Following is the C# version of the OracleCommandSPParms subroutine:

private void OracleCommandSPParms(string sServer, string sUser,
string sPassword)
{
Cursor.Current = Cursors.WaitCursor;
OracleConnection cn = new OracleConnection("DATA SOURCE=" +
sServer + ";UID=" + sUser + ";PWD=" + sPassword);
//Setup the Command object's Connection, SQL and parameter types
OracleCommand cmd = new OracleCommand("GETEMPDETAILS", cn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parmEMPNO = new OracleParameter("I_EMPNO",
OracleType.Number);
parmEMPNO.Direction = ParameterDirection.Input;
OracleParameter parmENAME = new OracleParameter("O_ENAME",
OracleType.VarChar, 10);
parmENAME.Direction = ParameterDirection.Output;
OracleParameter parmJOB = new OracleParameter("O_JOB",
OracleType.VarChar, 9);
parmJOB.Direction = ParameterDirection.Output;
OracleParameter parmSAL = new OracleParameter("O_SAL",
OracleType.Number);
parmSAL.Direction = ParameterDirection.Output;
// Add the parameter objs to the Command Parameters collection
cmd.Parameters.Add(parmEMPNO);
cmd.Parameters.Add(parmENAME);
cmd.Parameters.Add(parmJOB);
cmd.Parameters.Add(parmSAL);
try
{
// Open the connection & prepare the command
cn.Open();
// Execute the prepared SQL statement to retrieve EMPNO 7788
parmEMPNO.Value = 7788;
cmd.ExecuteNonQuery();
Debug.WriteLine(parmENAME.Value);
Debug.WriteLine(parmJOB.Value);
Debug.WriteLine(parmSAL.Value);
}
catch (Exception ex)
{
Cursor.Current = Cursors.Default;
MessageBox.Show("Connection error: :" + ex.ToString());
}
cn.Close();
Cursor.Current = Cursors.Default;
}

Three string variables are passed as parameters into the beginning of this subroutine. The sServer variable contains the .NET Service name of the Oracle database. The sUser variable contains the Oracle user’s login, and the sPassword variable contains the user’s password. Next, the cn OracleConnection object is created, followed by an OracleCommand object named cmd. The OracleCommand object’s constructor uses two parameters. The first parameter of the OracleCommand object’s constructor is a string that contains the name of the stored procedure that will be executed: GETEMPDETAILS. For stored procedures, this parameter contains the name of the stored procedure but it can also contain SQL statements that will be executed. The second parameter contains the name of the active OracleConnection object. After the OracleCommand object has been created, its CommandType property is set to CommandType.StoredProcedure, which indicates that a stored procedure will be executed. The OracleCommand object’s CommandType property can accept any of the values shown in the table that follows.

After the OracleCommand object has been created and its CommandType property is set to CommandType.StoredProcedure, four OracleParameter objects are created. These OracleParameter objects are used to supply the input value to the GETEMPDETAILS stored procedure, as well as return the values in the output parameters to the ADO.NET application. In this example, the OracleParameter objects are instantiated using the OracleParameter object’s constructor. Two parameters are passed to the OracleParameter class constructor. The first parameter is a string containing the name of the parameter. The second parameter specifies the data type of the parameter. Because the OracleCommand object uses named parameters, this string must exactly match the name of the parameter as it is defined in the Oracle stored procedure. However, because named parameters are used, the order that the OracleParameters objects are created in or added to the OracleCommand object doesn’t matter. The names are used to identify each parameter, not their position. Here the first OracleParameter object uses the parameter name of the "I_EMPNO"— matching the name of the first parameter in the GETEPDETAILS stored procedure.

Note Oracle named parameters use the colon character to designate a named parameter. You can optionally use or omit the colon in your OracleParameter objects. In this case, the first parameter could have been named either I_EMPNO or :I_EMPNO.

The second parameter used in the OracleParameter object’s constructor specifies the data type that is used in the parameter. This is stored in the OracleType property. Table 9-2 shows the valid values for the OracleType property.

Unfortunately ADO.NET doesn’t provide a convenient way to set up the Direction property along with the other parameter attributes. The next line following the parameter object’s constructor sets the Direction property of the OracleParameter object. Table 9-3 lists the valid enumerations for the OracleParameter Direction property.

Once the OracleParameter objects have been created, they are added to the cmd OracleCommand object’s Parameters collection.

Next, a Try-Catch block is used to open the connection to the Oracle server and then set the value of the parmEMPNO inout parameter to 7788. Then the OracleCommand object’s ExecuteNonQuery method is used to execute the GETEMPDETAILS stored procedure. The ExecuteNonQuery method doesn’t return a resultset, but after the stored procedure has executed, the returned values will be available in the output parameter objects. Here, the contents of the parmENAME, parmJob, and parmSAL Value properties are written to the Debug console. If the connection or the stored procedure fails, a message box showing the error text appears. Closing the connection is the last action in the subroutine.

Table 9-2. OracleType Enumerations

Table 9-3. OracleParameterDirection Enumeration.

Executing Transactions with Parameterized SQL Statements

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.