Using Oracle stored procedures to return result sets in C #

Problem:

Stored procedures defined in MSSQLSERVER can return a data set directly, such as:

CREATE PROCEDURE Sp_getallemployeesasselect * from [Northwnd]. [dbo]. [Employees]

In Oracle database This definition is wrong , how to solve?

Way:

Oracle can use a cursor to manipulate a dataset, but a cursor error is used directly in the stored procedure output parameter, where the cursor should be a keyword that defines a cursor, not a type, so first define a package that declares a global custom cursor type in the package, such as:

/** Create a package named Pkg_products **/create or replace the packages pkg_products is--define a public cursor type Cursor_pdt--ref You can pass result sets between programs--open cursor variables in a program, process data in another program type CURSOR_PDT is ref cursor;--declares a stored procedure, cursor type parameter is output type procedure PROC_ Getallproducts (Cur_set out CURSOR_PDT); end pkg_products;

This package is a bit like the interface in Oo, the file header in C + +, it is called inclusion (package body), and the name is the same, such as:

/** Create a package body **/create or replace the packages body Pkg_products  is-implements a stored procedure that is not implemented in the package  procedure Proc_getallproducts (cur_ set out CURSOR_PDT)    as Begin       -opens a cursor that can be deferred to the client open      cur_set for select ID, name, ProductType, because the cursor is defined using ref processing. Price,picture, Isout, Mark, adddate from the products;      End;end;

If the definition succeeds and the compilation passes, you can test it in Oracle first, such as:

/** A stored procedure defined with a procedure test **/declare--define a variable cur_set the cursor type pkg_products.cursor_pdt;--define the row type Pdtrow products%rowtype;begin  -- Execute stored procedure  pkg_products.proc_getallproducts (cur_set);  --Traverse the data loop in the cursor-         -Fetch the current row data into the Pdtrow           FETCH cur_set into Pdtrow;           --End loop If no data is obtained           EXIT when cur_set%notfound;           --Dbms_output The data obtained by the output           . Put_Line (pdtrow.id| | ', ' | | Pdtrow.name);         END LOOP;         CLOSE Cur_set;  End

If you run the above PL/SQL process script to get the results, the implementation of the defined package and package is successful, and you can use. Net,java or other programs to access the defined stored procedure, such as the script that accesses the stored procedure using C #:

1             //Define Connection object 2             OracleConnection conn = new OracleConnection ("Data SOURCE=ORCL; Persist Security info=true; User id=t52; Unicode=true; Password=t52 "); 3             //NOTE: Package name. The form of the stored procedure name 4             oraclecommand cmd = new OracleCommand ("Pkg_products.proc_getallproducts", conn); 5             / /SET command type for stored procedure 6             cmd.commandtype = CommandType.StoredProcedure; 7             //define parameters, note that the parameter name must be consistent with the stored procedure definition, and the type is OracleType.Cursor 8             oracleparameter cur_set =  9                 New OracleParameter ("Cur_set", oracletype.cursor);             //Set parameter to output type one by one             Cur_set. Direction = parameterdirection.output;12             //Add parameter to             cmd. Parameters.Add (cur_set);             //Open connection to             Conn. Open (); +             //execute and return OracleDataReader, note after use release the             return cmd. ExecuteReader (commandbehavior.closeconnection);

Note Project to add a reference assembly: System.Data.OracleClient, use in code, and do not end with a semicolon when using SQL statements as strings in C #. The code above to access the database is not encapsulated, return dataset,datatable can also, the sample code is as follows:

Using Oracle stored procedures to return result sets in C #