Shulou(Shulou.com)06/03 Report--

This article introduces the knowledge about how to use Oracle stored procedures to return result sets in C #. Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Question:

Stored procedures defined in MSSQLServer can directly return a dataset, such as:

Create procedure sp_getAllEmployeesasSELECT * FROM [NORTHWND]. [dbo]. [Employees]

It is wrong to define this in the Oracle database, how to solve it?

Methods:

Cursor can be used in Oracle to manipulate datasets, but Cursor errors are directly used in stored procedure output parameters. In this case, Cursor should be a keyword that defines cursors, not a type, so first define a package and declare global custom cursor types in the package, such as:

/ * * create a package called pkg_products * * / create or replace package pkg_products is-- defines a public cursor type cursor_pdt--ref that can pass the result set between programs-- opening cursor variables in one program, type cursor_pdt is ref cursor;-- declaring a stored procedure in another program processing data, and the cursor type parameter is output type procedure proc_GetAllProducts (cur_set out cursor_pdt); end pkg_products

This package is somewhat similar to the interface in OO and the file header in C++. When implementing this package, it is called package body, and the name should be the same, such as:

/ * * create a packet body * * / create or replace package body pkg_products is-- implement the stored procedure procedure proc_GetAllProducts (cur_set out cursor_pdt) as begin that is not implemented in the package-- Open the cursor, because using ref to process the cursor when defining the cursor can be deferred to the client side open cur_set for select id, name, producttype, price,picture, isout, mark, adddate from products; end;end

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

/ * * use stored procedures defined by procedure testing * / declare-- to define cursor type variables cur_set pkg_products.cursor_pdt;-- define row type pdtrow products%rowtype;begin-- execute stored procedure pkg_products.proc_GetAllProducts (cur_set);-- traverse data LOOP in cursors-- take the current row data and store it in pdtrow FETCH cur_set INTO pdtrow -- end the loop EXIT WHEN cur_set%NOTFOUND; if no data is obtained-- output the acquired data DBMS_OUTPUT.PUT_LINE (pdtrow.id | |','| | pdtrow.name); END LOOP; CLOSE cur_set; end

If you can get the result by running the above PL/SQL procedure script, the implementation of the defined package and package body is successful. You can use .NET, Java or other programs to access the defined stored procedure. For example, the script to access the stored procedure using C# is as follows:

/ / define the connection object OracleConnection conn = new OracleConnection ("Data Source=orcl;Persist Security Info=True;User ID=t52;Unicode=True;Password=t52"); / / Note: package name. The stored procedure name is in the form OracleCommand cmd = new OracleCommand ("pkg_products.proc_GetAllProducts", conn); / / sets the command type to stored procedure cmd.CommandType = CommandType.StoredProcedure / / define the parameter, note that the parameter name must be the same as the stored procedure definition, and the type must be OracleType.Cursor OracleParameter cur_set = new OracleParameter ("cur_set", OracleType.Cursor); / / set the parameter to the output type cur_set.Direction = ParameterDirection.Output; / / add the parameter cmd.Parameters.Add (cur_set) / / Open the connection conn.Open (); / / execute and return OracleDataReader. Note that return cmd.ExecuteReader (CommandBehavior.CloseConnection) is released after use.

Note that the project needs to add a reference assembly: System.Data.OracleClient, and using; in the code. In addition, if you use SQL statements to appear as strings in C #, try not to wrap lines or end with semicolons. The above code for accessing the database is not encapsulated. It is also possible to return DataSet,DataTable. The sample code is as follows:

Sample code download

Looking for a better way.

That's all for "how to use Oracle stored procedures to return result sets in C #". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.