How to code a StoredProc that takes params and returns data?
Posted: Thu 15 Mar 2012 00:29
(This is a C# Windows forms app in Visual Studio 2010)
If I have an Oracle Stored Procedure like this:
...and an OracleCommand component on my form that is set to a valid connection, has a CommandType of StoredProcedure, and its CommandText is set to the StoredProc shown (partially) above, what do I need to do to set its parameters in code?
The user selects the dates, and I want the result set to display in a DatagridView control.
I tried to adapt the code here:
http://www.devart.com/forums/viewtopic. ... 2e9ef99ed8
But the following won't compile - doesn't know what "pres" is - neither do I.
Also, the code from the link above has the final line calling .ExecuteNonQuery, but as the SP in question DOES contain a select statement and should reflect rows/result set/cursor, I changed it to .ExecuteCursor(). Is that right?
If I have an Oracle Stored Procedure like this:
Code: Select all
CREATE OR REPLACE PROCEDURE REPORT.CONN_THRU_DOTNET
( iStartDate IN DATE
, iEndDate IN DATE
, iCATEGORYID IN NUMBER
, C_REF IN OUT SYS_REFCURSOR
) AS
...
The user selects the dates, and I want the result set to display in a DatagridView control.
I tried to adapt the code here:
http://www.devart.com/forums/viewtopic. ... 2e9ef99ed8
But the following won't compile - doesn't know what "pres" is - neither do I.
Code: Select all
private void button1_Click(object sender, EventArgs e)
{
//pRes = new OracleParameter("RETURN_VALUE", OracleDbType.Cursor);
pRes = new OracleParameter("C_REF", OracleDbType.Cursor);
pRes.Direction = ParameterDirection.ReturnValue;
oracleCommand1.Parameters.Clear();
oracleCommand1.Parameters.Add("iStartDate", "1/1/2011");
oracleCommand1.Parameters.Add("iEndDate", "12/12/2011");
oracleCommand1.Parameters.Add("iCATEGORYID", 114);
oracleCommand1.Parameters.Add(pRes);
oracleCommand1.ExecuteCursor(); //ExecuteNonQuery();
}