The code I have so far is as follows… not sure if its correct … so please fix where needed:
The only value I’m really interested in returning is pi_maxtransaction which I call MaxTransactionNumber in a OracleParameter…
public DataSet GetMaxTransactionValue_Package()
{
string ConnectionString = "Server=localhost;User Id=username;Sid=databasename;Password=password;Direct=True;";
OracleConnection Connection = new OracleConnection( ConnectionString );
Connection.Open();
OracleCommand Command = new OracleCommand();
Command.CommandText = "pkg_one.getmaxtransactionorders";
Command.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter MaxTransactionNumber = Command.Parameters.Add("PI_MAXTRANSACTION", OracleDbType.Number );
MaxTransactionNumber.Direction = ParameterDirection.Output;
OracleParameter Status = Command.Parameters.Add("PS_STATUS", OracleDbType.VarChar );
Status.Direction = ParameterDirection.Output;
OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();
oracleDataAdapter.SelectCommand = Command;
//IS THE FOLLOWING NEEDED…. COULD I JUST RETURN MaxTransactionNumber.value.ToString(); after the ExecuteNonQuery()
OracleCommandBuilder oraCommandBuilder = new OracleCommandBuilder();
oraCommandBuilder.DataAdapter = oracleDataAdapter;
DataSet ReturnData = new DataSet();
Command.ExecuteNonQuery();
oracleDataAdapter.Fill(ReturnData, "Table");
return ReturnData;
}
Here is an example of the Package header … its works fine but I;m not sure how to interface to it in code and what to return from the package stored procedure call?
CREATE PACKAGE Pkg_one
TYPE r_cursor IS REF CURSOR;
PROCEDURE GETMAXTRANSACTIONORDERS ( pi_maxtransaction OUT iNTEGER, ps_status out varchar2 );
PROCEDURE GETINVOICEINFO ( ps_invoicenumber IN VARCHAR2, prc_cursor OUT R_CURSOR, ps_status OUT VARCHAR2);
END Pkg_Plus;
Returning Value from stored procedure call
You can use the following code
Code: Select all
public DataSet GetMaxTransactionValue_Package() {
string ConnectionString = "Server=localhost;User Id=username;Sid=databasename;Password=password;Direct=True;";
OracleConnection Connection = new OracleConnection( ConnectionString );
Connection.Open();
OracleCommand Command = new OracleCommand();
Command.CommandText = "pkg_plus.GETINVOICEINFO";
Command.CommandType = System.Data.CommandType.StoredProcedure;
Command.CreateParameters();
command.ExecuteNonQuery();
OracleParameter p2 = command.Parameters["prc_cursor"];
OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();
DataSet ReturnData = new DataSet();
oracleDataAdapter.Fill(ReturnData, "Table", (OracleCursor)p2.Value);
return ReturnData;
}
Code: Select all
public DataSet GetMaxTransactionValue_Package() {
string ConnectionString = "Server=localhost;User Id=username;Sid=databasename;Password=password;Direct=True;";
OracleConnection Connection = new OracleConnection( ConnectionString );
Connection.Open();
OracleCommand Command = new OracleCommand();
Command.CommandText = "pkg_plus.GETINVOICEINFO";
Command.CommandType = System.Data.CommandType.StoredProcedure;
Command.CreateParameters();
OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();
oracleDataAdapter.SelectCommand = Command;
DataSet ReturnData = new DataSet();
oracleDataAdapter.Fill(ReturnData, "Table");
return ReturnData;
}