Release Date: 5/28/2004
| Update Date: 5/28/2004
Bill Hamilton
April 2004
Suitable for: Microsoft Ado.NetMicrosoft SQL ServerMicrosoft .NET Oracle Provider Microsoft .NET Framework 1.1
Summary: Access Oracle stored procedures and functions using Microsoft .NET Oracle providers in Microsoft .NET Framework 1.1. (24 page print pages)
Content this page
Overview The stored procedure of the stored procedure does not return data The resulting set with the REF CURSOR package uses DataReader to return a single value stored procedure sequence Using the DataAPter Fill Data Set Use DataAPter Update Oracle Use multiple result set Summary Related Books This article discusses how to use ADO. NET Access the Oracle stored procedure (called SQL program block) and function (return to a single value program block). You can use the following managed data providers to connect to the Oracle database: Microsoft .NET Oracle provider, OLE DB .NET Provider, ODBC .NET Data Provider, and Oracle's ODP.NET Provider. This article uses the Microsoft® .NET Framework Data Provider for Oracle. Different features can be used when using the Oracle Odp.NET data provider or the Microsoft .NET framework data provider for OLE DB. Oracle .NET data provider is provided with .NET Framework 1.1. If you are using the .NET Framework 1.0, you will need to download .NET Managed Provider for Oracle. No matter which version, the data provider class is located in System.Data.OracleClient namespace. Overview PL / SQL is an Oracle implementation of SQL. It is similar to the T-SQL used by Microsoft? SQL Server, but there are some differences, this article discusses in detail later. Like T-SQL, PL / SQL extends standard SQL. PL / SQL is used to define named program blocks such as stored procedures, functions, and triggers.
Back to top
The class can perform the Oracle stored procedures and functions using the subset of the class category using System.Data.OracleClient namespace. The following table describes these classes:
Class Description OracleCommand's SQL statement for the stored procedure executed by the Oracle database. OracleConnection opens a database connection. The parameters of OracleParameter OracleCommand may also be mapped to Datacolumn. OracleParameterCollection OracleParameter. Collection. Oracletype Oracle data type and structure enumeration.
Back to top
Performing a stored procedure to perform an Oracle stored procedure similar to the execution SQL Server stored procedure. The following steps explain how to perform an Oracle stored procedure and retrieve the result it returns.
1. Create a stored procedure called count_job_history in the HR architecture to calculate the number of records in the job_history table. Create or new procedure count_job_history
(
Recount Out Number
)
IS
Begin
SELECT Count (*) Into Recount
From Job_History;
End count_job_history;
The HR architecture is an example in which the default Oracle installation is included. 2. Adding System.Data.OracleClient.dll (used for Oracle's Microsoft .NET Frame Data Provider) to the project. 3. Import the type in the OracleClient class using the USING instruction. Using System.data.OracleClient; 4. Create an OracleConnection object. OracleConnection conn = new oracleConnection ("data source = oracledb;
User ID = UserId; Password = password; ");
Replace the name, username, and password of the Oracle database with your value. 5. Create an OracleCommand object. Setting its Connection property to the connection created in step 4. Set its CommandText to the name of the stored procedure and set its CommandText property to CommandType.StoredProcedure. When you call an Execute () method introduced in step 8, the command object will perform the specified stored procedure. OracleCommand cmd = new oracleCommand ();
cmd.connection = conn;
cmd.comMandtext = "count_job_history";
cmd.commandtype = commandtype.storedProcedure;
If your stored procedure names contain special characters, you must use the escape sequence. You can reset the existing OracleCommand object by resetting the CommandText property. 6. Create an OracleParameter object for input, output, and return values and add it to the parameter set of OracleCommand objects. Cmd.Parameters.add ("Reccount", ORACLETYPE.NUMBER) .direction =
ParameterDirection.output;
The line code is a short-related form of the following two lines: cmd.parameters.add ("Reccount", ORACLEPE.NUMBER);
CMD.Parameters ["Recount"]. Direction = parameterDirection.output;
7. If you want to retrieve the result set, create a DataSet, DataTable or DataReader. In this example, we just get the counts in the output parameters created in step 6. 8. Open the connection and execute the stored procedure using the OracleCommand object, as shown below:
Method Description EXECUTEREADER generates OracleDataReader by performing a stored procedure that can return a result set. ExecuteNonQuery executes the query or process of the result set, returns the number of rows affected. ExecuteoraclenonQuery executes queries and returns the number of rows affected. This method also uses an ORACLESTRING parameter to return to Update, Insert or delete query the final row of the last row modified. ExecuteScalar executes a query or process and returns the return value of the query or process, or returns the value of the first column of the result set first row as a .NET framework data type. ExecuteOraclesCalar performs a query or process, and returns the return value of the query or process, or returns the value of the first column of the result set first row as an ORACLETYPE data type.
Don't forget to turn it off after use. CONN.Open (); cmd.executenonquery ();
CONN.CLOSE ();
If you want to use DataAdapter to populate DataTable or DataSet, you can rely on DataAdapter to open and close connections. 9. Processing results. In our example, you can get a record number in the output parameters displayed to the console: console.writeline (cmd.parameters ["Recount"]. Value);
Below is a code for performing a stored procedure and retrieval result developed in this example: OracleConnection conn = new OracleConnection ("Data Source = ORACLEDB;
User ID = UserId; Password = password; ");
OracleCommand cmd = new oracleCommand ();
cmd.connection = conn;
cmd.comMandtext = "count_job_history";
cmd.commandtype = commandtype.storedProcedure;
Cmd.Parameters.add ("Reccount", ORACLETYPE.NUMBER) .direction =
ParameterDirection.output;
Cn.open ();
cmd.executenonquery ();
CONN.CLOSE ();
Console.writeline (cmd.parameters ["Recount"]. Value);
Back to top
The executeraclenonquery () method of the OracleCommand class of the OracleCommand class is not returned to the data. The SQL statement or stored procedure is not returned. This method returns an int value, which is represented by the number of rows affected by Update, Insert, and Delete commands; if no row is affected, Return -1. If the INSERT, DELETE or UPDATE statement you execute just affects a row, the method has a single parameter ORACLESTRING OUT ROWID that uniquely identifies the affected rows in the Oracle database. This value can be used to optimize subsequent related queries. You can also use the executenonquery () method of the OracleCommand class to perform a stored procedure that does not return data, but you will not be able to get the unique line identifier described above. Although the above commands do not return any data, the output parameters and return values mapped to the parameters are still filled with data. This allows you to use any of the above commands to return one or more scales values from the stored procedure. The following Oracle stored procedures delete all work experiences of employees specified by a single input parameter and do not return any data. Create or new procedure delete_job_history
(
p_employee_id number
)
IS
Begin
Delete from job_history
WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
End delete_job_history;
The following code runs the stored procedure. // Create The Connection
OracleConnection conn = new oracleConnection ("data source = oracledb;
User ID = UserId; Password = password; ");
// Create the command for the stored procedure
OracleCommand cmd = new oracleCommand (); cmd.connection = conn;
cmd.comMandtext = "count_job_history";
cmd.commandtype = commandtype.storedProcedure;
// add the parameter specifying the Employee for Whom To Delete Records
Cmd.Parameters.Add ("p_employee_id", oracletype.number) .value = 102;
Oraclestring rowid;
// Execute the stored procedure
Cn.open ();
Int rowsaffected = cmd.executenonQuery ();
CONN.CLOSE ();
Console.writeline ("Rows Affected:" RowsAffected);
If you have not modified the default HR installation, the record of the employee 102 in the job_history table is deleted, and the following is output to the console: Rows Affected: 1
Access Return Value RETURN statement immediately returns the control from the stored procedure to the calling program. The RETURN statement in the Oracle store cannot be returned like a value as in T-SQL. The Oracle function is a subroutine that calculates and returns a single value. Their structure is similar to the stored procedure, and the difference is that they always have a return clause that must be returned. Here is a function that returns an email that specifies employees: create or new function get_employee_email (
p_employee_id number
)
Return varcha2
IS p_email varcha2 (25);
Begin
SELECT Email Into P_Email from Employees
WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
Return p_email;
END GET_EMPLOYEE_EMAIL;
The way the function is executed is the same as the execution stored procedure. You can use the parameterDirection.ReturnValue parameter to get the result returned by the function. The following code shows how to use: // CREATE THE Connection
OracleConnection conn = new oracleConnection ("data source = oracledb;
User ID = UserId; Password = password; ");
// Create the command for the function of THE FUNCTION
OracleCommand cmd = new oracleCommand ();
cmd.connection = conn;
cmd.commandtext = "get_employee_email";
cmd.commandtype = commandtype.storedProcedure;
// add the parameters, include the return parameter to retrieve
// the return value
Cmd.Parameters.Add ("p_employee_id", oracletype.number) .value = 101;
CMD.Parameters.Add ("p_email", oracletype.varchar, 25) .direction = parameterDirection.ReturnValue;
// Execute the function
Cn.open ();
cmd.executenonquery ();
CONN.CLOSE ();
// Output the result
Console.writeline ("Email Address is:" cmd.parameters ["p_email"]. Value);
The console outputs the email address of the employee 101. Email Address is: Nkochhar
Back to top
The result set and Ref Cursor can use the REF CURSOR data type to handle the Oracle result set. Ref cursor is a pointer to the result set returned to the PL / SQL query. Unlike ordinary cursors, Ref cursor is a variable that is a reference to the cursor, which can be set to point to different result sets at execution. Use the REF CURSOR output parameter to pass the result set of the Oracle structured program to call the application. By defining the output parameters of the ORACletype.Cursor data type in the calling application, you can access the result set points to the Ref Cursor. OracleConnection must remain open during the process of using the Ref Cursor.
Back to top
A significant difference between the stored procedures in PL / SQL and T-SQL is the Oracle packet structure used by PL / SQL. There is no equivalent element in T-SQL. The package is a container that is logically related programming blocks such as stored procedures and functions. It contains two parts:
• Specification: Define the name of the package and provide method signature (prototype) for each stored procedure or function in the package. The standard head also defines all global statements. The specification style is similar to the C or C header file. • Text: The code containing the stored procedures and functions defined in the header. Each stored procedure or parameter appears in parentheses and separated with a comma. Each parameter also marks one of the following three identifiers as needed:
• IN: This value passes from the calling application to the PL / SQL block. If an identifier is not specified, IN is the default transmission direction. • OUT: This value is generated by the stored procedure and passes the callback application. • inout: This value is passed to the PL / SQL block, which may be modified inside the block and then returns to the calling application. Each parameter is also marked to indicate the data type. The following package specification defines four processes, and they create, retrieve, update, and delete data in the Locations table of the HR architecture. Create or new package crud_locations as
TYPE T_CURSOR IS Ref Cursor;
Procedure getLocations (CUR_LOCATIONS OUT T_CURSOR);
Procedure Updatelocations (p_location_id in number,
p_street_address in varchar2,
p_postal_code in varchar2,
p_city in varchar2,
p_state_province in varchar2,
p_country_id in char);
Procedure deletelocations (p_location_id in number);
Procedure InsertLocations (p_location_id out number,
p_Street_address in varchar2, p_postal_code in varcha2,
p_city in varchar2,
p_state_province in varchar2,
p_country_id in char);
End crud_locations;
The following code is taken from the package of the above-described package specification, indicating the implementation details of the first process in the getLocations package: Create or new package body crud_locations as
Procedure getLocations (cur_locations out t_cursor)
IS
Begin
Open cur_locations for
SELECT * from Locations;
End getlocations;
- Implementation of Other Procedures Ommitted.
End crud_locations;
Back to top
Using DataReader can create OracleDataReader by calling the executeReader () method of the OracleCommand object. This section explains how to use DataReader to access the result set returned by stored procedure select_job_history. The following is a package specification: create or new package select_job_history as
TYPE T_CURSOR IS Ref Cursor;
Procedure getJobhistoryByemployeeid
(
p_employee_id in number,
CUR_Jobhistory Out T_cursor
);
End select_job_history;
Package Text defines a process, the process retrieves the result set of the work experience of the employee, and returns it as a REF CURSOR output parameter: create or new package body select_job_history as
Procedure getJobhistoryByemployeeid
(
p_employee_id in number,
CUR_Jobhistory Out T_cursor
)
IS
Begin
Open cur_jobhistory for
SELECT * from Job_history
WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
End GetJobhistoryByemployeeID;
End select_job_history;
The following code performs the process, creates DataReader according to the result set, and outputs DataReader's content to the console. // CREATE Connection
OracleConnection conn = new oracleConnection ("data source = oracledb;
User ID = UserId; Password = password; ");
// Create the command for the stored procedure
OracleCommand cmd = new oracleCommand ();
cmd.connection = conn;
cmd.commandtext = "SELECT_JOB_HISTORY.GETJOBHISTORYBYEMPLOYEEID";
cmd.commandtype = commandtype.storedProcedure;
// add the parameters for the storedure include incruding the Ref Cursor
// TO RETRIEVE The result setcmd.parameters.add ("p_employee_id", oracletype.number) .value = 101;
Cmd.Parameters.Add ("cur_jobhistory", oracletype.cursor) .direction =
ParameterDirection.output;
// Open the connection and create the datareader
Cn.open ();
OracleDataReader DR = cmd.executeReader ();
// Output the results and close the connection.
While (Dr.Read ())
{
For (int i = 0; i Console.write (DR [i] .tostring () ";"); Console.writeLine (); } CONN.CLOSE (); For the default installation of the HR architecture, the console outputs the fields that display each record in the two records of the employee 101 (separated by a semicolon): 101; 9/21/1989 12:00:00 AM; 10/27/1993 12:00:00 AM; AC_ACCOUNT; 110; 101; 10/28/1993 12:00:00 AM; 3/15/1997 12:00:00 AM; AC_MGR; 110; The above code shows that the process in the package is specified by the name of the package name (ELECT_JOB_HISTORY) and the number of processes (in this case GetJobhistoryByemPloyeID), with a sentence separation. The code also shows how to define the REF CURSOR parameters of the result set. Note that the data type is ORAMETYPE.CURSOR, the direction is ParameterDirection.output. Also note that all processes remain open during the entire process of accessing the result sets in Ref Cursor. If the package returns a plurality of cursors, DataReader accesss these cursors in order to add their order to the parameter collection, rather than accessing them in the order in which they appear in the process. You can use the DataRead's nextResult () method to advance to the next cursor. Back to top Returns a single value stored OracleCumeraclescalar () method for performing a single value as an SQL statement or stored procedure returned as an OracleType data type. If the command returns a result set, the method returns the value of the first line of the first line. If REF CURSOR is returned, the method returns an empty reference. The ExecuteScalar () method of the OracleCommand class is similar to the executeoraclescalar () method, but it returns the value as the .NET framework data type. Despite this, these two methods are useless when using Oracle stored procedures. The Oracle stored procedure cannot return a value as part of the RETURN statement, and can only return it as an OUT parameter. For information, see the stored procedure section that does not return data. At the same time, in addition to the parameters output by the Ref Cursor, you cannot return the result set. The next section will discuss this. You can only retrieve the return value of the Oracle function (as described in the previous section) without using the return parameter, and cannot be retrieved using one of the ExecuteScalarm. Back to top Sequence Oracle uses sequences to generate unique numbers instead of using the data type UNIQUEIDENTIFIER used in SQL Server. No matter which case, the main use is a series of unique numbers for the primary key. Unlike the UNIQUEIDentifier data type, the sequence is a database object that is not related to one or more tables that use it for primary key values. The Oracle sequence is an atomic object and is consistent. That is, once you visit a serial number, Oracle will automatically increase the next number before processed the next request, so that the repetition value will not occur. You can create an Oracle sequence using the CREATE Sequence command. The parameters of this command include increment, starting value, maximum, loop, and cache. The sequence value can be accessed using NextVal and CURRVAL keywords. NextVal returns the next number in the sequence, and CURRVAL provides access to the current value. Sequences in the HR architecture Locations_Seq is defined as follows: CREATE SEQUENCE LOCATIONS_SEQ INCREMENT BY 100 START WITH 1 MaxValue 9900 Minvalue 1 Nocycle Nocache Noorder Most sequence code is not self-definitive. NOCYCLE indicates that the sequence will not regenerate other values after reaching the minimum or maximum. NOCACHE indicates that the sequence value will not be allocated before being requested; the pre-allocation mechanism can be used to improve performance. NoORDER indicates that these numbers cannot be guaranteed in the order of the request number when generating numbers. The following code shows a stored procedure, requiring a sequence value, use it to set the primary key value when inserting records in the Locations table, and then returns the main key value in the OUT parameter. Create or new procedure add_location p_location_id out number, p_street_address in varchar2, p_postal_code in varchar2, p_city in varchar2, p_state_province in varchar2, p_country_id in char ) AS Begin INSERT INTO LOCATIONS Location_id, Street_address, Postal_code, CITY, State_Province, Country_id) VALUES Locations_seq.nextval, p_street_address, p_postal_code, p_city, p_state_province, p_country_id ); SELECT LOCATIONS_SEQ.CURRVAL INTO P_LOCATION_ID from DUAL End add_location; The following code calls the stored procedure to insert a record and retrieve the returned sequence value. // Create The Connection OracleConnection conn = new oracleConnection ("data source = oracledb; User ID = UserId; Password = password; "); // Create the command for the stored procedure OracleCommand cmd = new oracleCommand (); cmd.connection = conn; cmd.commandtext = "add_location"; cmd.commandtype = commandtype.storedProcedure; // add the parameters for the storedure incruding the location_id // sequence value That is returned in the output parameter p_location_id Cmd.Parameters.Add ("p_location_id", oracletype.number) .direction = ParameterDirection.output; Cmd.Parameters.Add ("p_street_address", oracletype.varchar) .value = "123 Any Street"; Cmd.Parameters.Add ("p_postal_code", oracletype.varchar) .value = "33040"; Cmd.Parameters.Add ("p_city", oracletype.varchar) .value = "key west"; Cmd.Parameters.Add ("p_state_province", oracletype.varchar) .value = "fl"; CMD.Parameters.Add ("p_country_id", oracletype.varchar) .value = "us"; // Execute the command to add the records Oraclestring rowid; Cn.open (); Int rowsaffected = cmd.executeoraclenonury (Out RowID); CONN.CLOSE (); // output the results Console.writeline ("Rows Affected:" RowsAffected); Console.WriteLine ("Location ID:" CMD.Parameters ["p_location_id"]. value); The console displays a record into this table while it is also inserted into the primary key value generated by the sequence. Rows Affected: 1 Location ID: 3300 Back to top Use the DataAdapter populated data set to populate the DataSet with the Ref Cursor. The following code utilizes the stored procedure GetJobhistoryByemPloyeeID defined in the DataReader section, and uses it to populate the DataSet with the result of the result returned in the REF CURSOR output parameter. Here is the code using DataAdapter populates DataSet: // Create The Connection OracleConnection conn = new oracleConnection ("data source = oracledb; User ID = UserId; Password = password; "); // Create the command for the stored procedure OracleCommand cmd = new oracleCommand (); cmd.connection = conn; cmd.commandtext = "SELECT_JOB_HISTORY.GETJOBHISTORYBYEMPLOYEEID"; CMD.CommandType = CommandType.StoredProcedure; // add the parameters for the stored propedure incruding the Ref cursor // To Retrieve The Result Set Cmd.Parameters.Add ("p_employee_id", oracletype.number) .value = 101; Cmd.Parameters.Add ("cur_jobhistory", oracletype.cursor) .direction = ParameterDirection.output; // Createt the DataAdapter from the command and use it to fill the // dataset OracleDataAdapter Da = New OracleDataAdapter (CMD); DataSet DS = New Dataset (); Da.fill (DS); // Output the results. Console.writeline (DS.Tables [0] .rows.count); For the default installation of the HR architecture, the output indicates that the employee 101 has two Job_history records. Back to top Update Oracle with DataAdapter When you use the Ref Cursor parameter to populate DataSet, you cannot simply use the OracleDataAdapter's Update () method. This is because Oracle does not provide the information you need to determine the table name and column name when performing a stored procedure. To use the DataAdapter's Update () method, you must create the process of updating, inserting, and deleting records in the base table. This method is similar to the method used in SQL Server. This section explains how to generate a package that can handle the desired creation, retrieve, update, and delete operations to be able to retrieve the local data from the Oracle database, and will also re-update the discontinuous changes made to DataSet data to the Oracle database. Baotou as follows: Create or New Package Crud_locations as TYPE T_CURSOR IS Ref Cursor; Procedure getLocations (CUR_LOCATIONS OUT T_CURSOR); Procedure Updatelocations p_location_id in number, p_street_address in varchar2, p_postal_code in varchar2, p_city in varchar2, p_state_province in varchar2, p_country_id in char); Procedure deletelocations (p_location_id in number); Procedure insertlocations p_location_id out number, p_street_address in varchar2, p_postal_code in varchar2, p_city in varchar2, p_state_province in varchar2, p_country_id in char); End crud_locations; Bao Zenwen as follows: Create or New Package Body Crud_locations as - Retrieve All Location Records Procedure getLocations (CUR_LOCATIONS OUT T_CURSOR) IS Begin Open cur_locations for SELECT * from Locations; End getlocations; - Update a location record Procedure Updatelocations p_location_id in number, p_street_address in varchar2, p_postal_code in varchar2, p_city in varchar2, p_state_province in varchar2, p_country_id in char) IS Begin Update Locations Set Street_address = p_street_address, Postal_code = p_postal_code, City = p_city, State_Province = P_State_Province, Country_ID = p_country_id WHERE Location_id = p_location_id; End updatelocations; DELETE A LOCATION RECORD Procedure deletelocations (p_location_id in number) IS Begin Delete from Locations WHERE LOCATION_ID = p_location_id; End deletelocations; - INSERT a Location Record Procedure InsertLocations ( p_location_id out number, p_street_address in varchar2, p_postal_code in varchar2, p_city in varchar2, p_state_province in varchar2, p_country_id in char ) AS Begin INSERT INTO LOCATIONS Location_id, Street_address, Postal_code, CITY, State_Province, Country_id) VALUES Locations_seq.nextval, p_street_address, p_postal_code, p_city, p_state_province, p_country_id ); SELECT LOCATIONS_SEQ.CURRVAL INTO P_LOCATION_ID from DUAL End insertlocations; End crud_locations; The following code defines a DataAdapter to create, retrieve, update, and delete data that supports DataAdapter using the procedures defined in the above package. DataAdapter can be used to retrieve data into a DataSet, or you can use changes made to DataSet to the Oracle database. // define the connection string String connString = "data source = ORACLEDB; user ID = userid; password = password;"; // Create the data adapter OracleDataAdapter Da = New OracleDataAdapter (); // define the select command for the data adapteroraclect SelectCommand = New OracleCommand ("Crud_locations.getlocations, New OracleConnection (Conntring)); SelectCommand.commandtype = commandtype.storedProcedure; SelectCommand.Parameters.Add ("Cur_locations", Oracletype.cursor) .direction = parameterDirection.output; Da.selectCommand = SELECTCOMMAND; // define the udpate command for the data adapter OracleCommand UpdateCommand = New OracleCommand ("crUd_locations.Updatelocations, New OracleConnection (Conntring)); UpdateCommand.commandtype = commandtype.storedProcedure; UpdateCommand.Parameters.Add ("p_location_id", oracletype.number, 4, "Location_ID"); UpdateCommand.Parameters.Add ("p_street_address", Oracletype.varchar, 40, "Street_address"); UpdateCommand.Parameters.Add ("p_postal_code", oracletype.varchar, 12, "Postal_code"); UpdateCommand.Parameters.Add ("p_city", oracletype.varchar, 30, "city"); UpdateCommand.Parameters.Add ("p_state_province", oracletype.varchar, 25, "State_Province"); UpdateCommand.Parameters.Add ("p_country_id", oracletype.char, 2, "Country_id"); Da.UpdateCommand = updateCommand; // define the delete command for the data adapter OracleCommand deleteCommand = New OracleCommand ("crUd_locations.deletelocations, New OracleConnection (Conntring)); DeleteCommand.commandtype = commandtype.storedProcedure; DeleteCommand.Parameters.Add ("p_location_id", oracletype.number, 4, "Location_ID"); Da.deleteCommand = deleteCommand; OracleCommand InsertCommand = New OracleCommand ("Crud_Locations.Isertlocations", New OracleConnection (Conntring); INSERTCOMMAND.COMMANDTYPE = CommandType.StoredProcedure; INSERTCOMMAND.PARAMETERS.ADD ("p_location_id", oracletype.number, 4, "Location_ID"); INSERTCOMMAND.Parameters.Add ("p_street_address", Oracletype.varchar, 40, "Street_address"); INSERTCOMMAND.Parameters.Add ("p_postal_code", oracletype.varchar, 12, "Postal_code"); INSERTCOMMAND.Parameters.Add ("p_city", oracletype.varchar, 30, "city"); INSERTCOMMAND.Parameters.add ("p_state_province", oracletype.varchar, 25, "State_Province"); INSERTCOMMAND.Parameters.add ("p_country_id", oracletype.char, 2, "Country_id"); Da.insertCommand = INSERTCOMMAND; // define a dataatable and fill it using the data adapter DataTable DT = New DataTable (); Da.fill (DT); // ... do Work That Adds, Edits, Updates, or deletes Records in the Table // Call the update () Method of the data adapter to update the oracle // Database with changes Made to the data Da.UPDATE (DT); Back to top Use multiple result set Oracle does not support bulk queries, so you cannot return multiple result sets from one command. When using a stored procedure, returns a plurality of result sets similar to returning a single result set; must use the REF CURSOR output parameter. To return multiple result sets, use multiple Ref cursor output parameters. The following is a package specification that returns two result sets (all EMPLOYEES and JOBS records): create or new package select_employees_jobs as TYPE T_CURSOR IS Ref Cursor; Procedure getEmployeesandjobs ( Cur_employees out t_cursor, CUR_JOBS OUT T_CURSOR ); End select_employees_jobs; The Bao Zhengwen as follows: Create or new package body select_employees_jobs as Procedure getEmployeesandjobs ( Cur_employees out t_cursor, CUR_JOBS OUT T_CURSOR ) IS Begin - RETURN All Employees Records Open cur_employees forselect * from Employees; - RETURN All Jobs Records Open CUR_JOBS for SELECT *. End geteMPloyeesandjobs; End select_employees_jobs; The following code shows how two related tables in DataSet are populated using two result sets returned from the above package: // Create The Connection OracleConnection conn = new oracleConnection ("data source = oracledb; User ID = UserId; Password = password; "); // define the command for the stored procedure OracleCommand cmd = new oracleCommand (); cmd.connection = conn; cmd.commandtext = "select_employees_jobs.getemployeesandjobs"; // add the parameters include the Two Ref Cursor Types to Retrieve // The Two Result Sets Cmd.Parameters.Add ("cur_employees", oracletype.cursor) .direction = ParameterDirection.output; Cmd.Parameters.Add ("cur_jobs", oracletype.cursor) .direction = ParameterDirection.output; cmd.commandtype = commandtype.storedProcedure; // Create the DataAdapter and map Tables OracleDataAdapter Da = New OracleDataAdapter (CMD); Da.TableMappings.Add ("Table", "Employees"); Da.TableMappings.Add ("Table1", "JOBS"); // Create and Fill the DataSet DataSet DS = New Dataset (); Da.fill (DS); // CREATE A RELATION DS.RELATIONS.ADD ("Employees_Jobs_RELATION", DS.Tables ["jobs"]. Column ["job_id"], DS. Tables ["Employees"]. Column ["JOB_ID"]); // output the second employee (zero-based array) and job title // Based on the relation Console.writeline ("Employee ID:" DS.Tables ["Employees"]. Rows [1] ["Employee_ID"] "Job Title:" DS.Tables ["Employees"]. Rows [1] .GetparenTrow "Job_title"] ["job_title"]); the console output shows the position of the second employee: Employee ID: 101; Job Title: Administration Vice President Back to top Small knots pass through the Oracle .NET data provider, it is convenient to perform stored procedures and access return values (regardless of the return value is a plurality of scalar values or result sets). The Oracle process can be used in conjunction with ORACLEDATAADAPTER to populate DataSet, process discontinuous data, and update the change to the Oracle database later. The main difference between the Oracle process and the Microsoft SQL Server stored procedure is that the Oracle process must return the value as an output parameter, and must use the output parameter to return the result set as the REF CURSOR object to the call. Back to top Related Books ADO.NET Cookbook ADO.NET INA NUTSHELL BILL HAMILTON is a software designer, which is dedicated to design, develop and implement distributed applications with Microsoft .NET and J2EE technology. As an early technical adopter, he often assessed, recommended new technologies and helped his customers effectively use new technologies. Bill has written two writings about ADO.NET
