C # Call the Oracle stored procedure to return the result set

Create a stored procedure under Scott in Oracle:

(Note: Starting from 9i, sys_refcursor is available. In earlier Oracle versions, you must use ref cursor and put it in a package)

Create or replace procedure sp_getdept

(Result out sys_refcursor)

As

Begin

Open result for select * from Dept;

End;

/

 

 

========================================================== ==============

. NET environment (the windows program is basically the same as the web; the environment is vs2005)

= Remember to add reference system. Data. oracleclient first

Using system;

Using system. Collections. Generic;

Using system. componentmodel;

Using system. Data;

Using system. drawing;

Using system. text;

Using system. Windows. forms;

Using system. Data. oracleclient;

 

Namespace windowsapplication1

{

Public partial class form1: Form

{

Public form1 ()

{

Initializecomponent ();

}

 

Private void form1_load (Object sender, eventargs E)

{

Oracleconnection con = new oracleconnection ("Server = orcl; uid = Scott; Pwd = tiger ");

Oraclecommand cmd = new oraclecommand ("sp_getdept", con );

Cmd. commandtype = commandtype. storedprocedure;

Oracleparameter p1 = new oracleparameter ("result", oracletype. cursor );

P1.direction = system. Data. parameterdirection. output;

Cmd. Parameters. Add (P1 );

Oracledataadapter da = new oracledataadapter (CMD );

Dataset DS = new dataset ();

Da. Fill (DS );

This. Maid = Ds. Tables [0];

}

}

}