Using the OracleCommand Object - Using the Visual Studio OracleCommand Object
(Page 2 of 4 )
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.
| Method | Description |
| ExecuteNonQuery | The ExecuteNonQuery method is used to execute a SQL statement on the connected data source. It is used for DDL statements and action queries such as Insert, Update, and Delete operations, as well as ad hoc queries. The number of rows affected is returned, but no output parameters or resultsets are returned. |
| ExecuteOracleNonQuery | Like the ExecuteNonQuery method, the ExecuteOracleNonQuery is used to execute a SQL statement on the connected data source. Unlike the ExecuteNonQuery method, the ExecuteOracleNonQuery method is able to accept a parameter containing the row ID of the server. The number of rows affected is returned, but no output parameters or resultsets are returned. |
| ExecuteOracleScalar | The ExecuteOracleScalar method is used to execute a stored procedure or a SQL statement that returns a single scalar value. This function returns the value as an Oracle data type. The first row of the first column of the resultset is returned to the calling application. Any other returned values are ignored. |
| ExecuteReader | The ExecuteReader method is used to execute a SQL Select statement on the data source. A fast forward-only result is returned. |
| ExecuteScalar | The ExecuteScalar method is used to execute a stored procedure or a SQL statement that returns a single scalar value. The first row of the first column of the resultset is returned to the calling application. Any other returned values are ignored. |
Table 9-1. OracleCommand SQL Statement Execution Methods
Next: Executing Stored Procedures with Output Parameters >>
More Database Articles
More By McGraw-Hill/Osborne
|
 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.
|
|
