How to return a resultset using .Net SP

856576

Hi,

I had a problem with using the .Net Stored Procedures.

How can i get the query resultset from a .Net SP?
DataSet and ref CURSOR doesn't work, because they can not be a parameter or return.

My Oracle is 10gR2,and I'm using Visual Studio 2005

Are there any ways to do that??

Thanks for your help!
Song

SURFThru

If you use the Oracle Data Provider you have access to the RefCursor. BUT you do not need to access that in .NET. You can just FILL your Dataset from the Data Adapater. Here is some code...


//Call the procedure

OracleConnection cn = new OracleConnection(Classes.GeneralMethods.GetConnectionString("userid=xxxxxx");
OracleCommand cmd = new OracleCommand("pkg_ora_package.prc_get_my_data", cn);
cmd.CommandType = CommandType.StoredProcedure;

//Bind params by name and not by position.
cmd.BindByName = true;

cmd.Parameters.Add(OParm.GetOracleParameter("iv_acct_no", ParameterDirection.Input, iv_acct_no));
cmd.Parameters.Add(OParm.GetOracleParameter("ocsr", OracleDbType.RefCursor, ParameterDirection.Output));

DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
try
{
cn.Open();
da.Fill(ds); ///FILL THE DATASET