Use. Net to call the Oracle stored procedure and return the record set

Recently, a project was created to improve database security. The customer asked all database operations to be stored in the stored procedure. It is easier to return a value for an update operation. However, if you want to return a record set, it is quite troublesome. I searched the internet for this. Article Not many. Here is a summary for your reference.
We will not introduce how. Net accesses the Oracle database here. There are also a lot of related information. If you are interested, you can refer to the following.
How to access the Oracle database using ASP. NET
Http://www.weiw.com/article/list.asp? Id = 649

Oracle stored procedures return record sets. The key is to use a cursor.
We certainly have a lot of contact with database cursor. We can use open, fetch, and close operations to control the cursor for various convenient operations. I will not repeat this example. We will introduce cursor variable ). Similar to a cursor, the cursor variable also points to the current row of a query result set. The difference is that the cursor variable can be opened for any type-similar query instead of bound to a specific query. With cursor variables, you can obtain more convenience in database data extraction.

First, create a table.

Create Table lihuan. bill_points
(
Points_id number (10, 0) not null,
Customer_id number (10, 0) not null,
Bill_point_no number (2, 0) default 1 not null,
Constraint pk_bill_points primary key (points_id)
)
/

Second, create a package
Create or replace package lihuan. yy_pkg_bill_point_no/* Get all the user's charged electricity numbers */
Is
Type t_cursor is ref cursor;

Procedure bill_point_no (p_customer_id bill_points.customer_id % type,
Re_cursor out t_cursor );
End;
/

Again, create the package body
Create or replace package body lihuan. yy_pkg_bill_point_no/* Get all the user's charged electricity numbers */
Is

Procedure bill_point_no (p_customer_id bill_points.customer_id % type,
Re_cursor out t_cursor)
Is
V_cursor t_cursor;
Begin
Open v_cursor
Select bill_point_no from bill_points where customer_id = p_customer_id;
Re_cursor: = v_cursor;
End;
End;
/

Finally, in. netProgramCall.
Public dataset bill_point_no (string customer_id) // OK
{
Dataset dataset = new dataset ();
Hashtable ht = new hashtable ();
Ht. Add ("p_customer_id", customer_id );
If (runprocedure ("re_cursor", oracletype. cursor, ref dataset, HT, bmsoracleuser + ". yy_pkg_bill_point_no.bill_point_no", bmsoracleconnectionstring ))
{
;
}
Else
{
Dataset = NULL;
}
Return dataset;
}

Public bool runprocedure (string returnparameter, oracletype paramtype, ref dataset, hashtable HT, string procedurename, string oracleconnection)
{
System. Data. oracleclient. oracleconnection dsconnection = new system. Data. oracleclient. oracleconnection (oracleconnection );
System. Data. oracleclient. oraclecommand dacommand = new system. Data. oracleclient. oraclecommand (procedurename, dsconnection );
Dsconnection. open ();
Dacommand. commandtype = commandtype. storedprocedure;
Idictionaryenumerator enumerator;
Enumerator = Ht. getenumerator ();
Object value = NULL;
Oracleparameter oracleparam;
Oracleparam = dacommand. Parameters. Add (New oracleparameter (returnparameter, paramtype ));
Oracleparam. Direction = parameterdirection. output;
While (enumerator. movenext ())
{
Value = enumerator. value;
Oracleparam = dacommand. Parameters. Add (New oracleparameter (enumerator. Key. tostring (), value ));
}
Oracledataadapter odadapter = new oracledataadapter (dacommand );
Try
{
Odadapter. Fill (Dataset );
Return true;
}
Catch (system. Exception E)
{
E. tostring ();
Return false;
}
Finally
{
Ht. Clear ();
Dacommand. Parameters. Clear ();
Dsconnection. Close ();
}
}