Method for calling oracle stored procedure in asp. net

Stored procedure (Stored Procedure) is a set of SQL statements in a large database system to complete specific functions. It is stored in the database and called again after the first compilation without compiling again. Users execute it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

Stored procedure is an important object in database. Any well-designed database application should use stored procedure.

Needless to say, this article introduces the method of calling oracle stored procedure in asp. net in two ways. Please see the following code for specific content.

Call oracle stored procedure method 1:

ORACLE code

CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR,MYCS2 OUT SYS_REFCURSOR,a out varchar)as
BEGIN
 a:='test';
 OPEN MYCS1 FOR
 SELECT 1 from dual;
 OPEN MYCS2 FOR
 SELECT 2 from dual;

END;

C # code

 /// <summary>
 ///  Execute oracle Stored procedures return multiple result sets 
 /// </summary>
 /// <param name="strProcName"> Stored procedure name </param>
 /// <param name="ResultCount"> Number returned </param>
 /// <param name="paras"> Parameter </param>
 /// <returns> Arbitrary object array </returns>
 public object[] ExcuteProc_N_Result(string strProcName, int ResultCount, params OracleParameter[] paras)
 {
  using (OracleConnection conn = new OracleConnection("User ID= User name ;Password= Password ;Data Source= Database ;"))
  {
  OracleCommand cmd = new OracleCommand(strProcName, conn);
  if (paras != null && paras.Length > 0)
  {
   for (int j = 0; j < paras.Length; j++)
   {
   if (paras[j].Value == null)
   {
    paras[j].Value = DBNull.Value;
   }
   }
  }
  cmd.Parameters.AddRange(paras);
  cmd.CommandType = CommandType.StoredProcedure;
  conn.Open();
  cmd.ExecuteNonQuery();
  int i = 0;
  //int nOutputParametersCount = 0;
  object[] objResult = new object[ResultCount];
  foreach (OracleParameter p in cmd.Parameters)
  {
   if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.InputOutput)
   {
   if (p.Value is OracleDataReader)
   {
    OracleDataReader reader = p.Value as OracleDataReader;
    objResult[i++] = ConvertDataReaderToDataTable(reader);
   }
   else
   {
    objResult[i++] = p.Value;
   }
   }
  }
  return objResult;
  }
 }
 /// <summary> 
 ///  Will DataReader  Convert to  DataTable 
 /// </summary> 
 /// <param name="DataReader">OleDbDataReader</param> 
 protected DataTable ConvertDataReaderToDataTable(OracleDataReader reader)
 {
  DataTable objDataTable = new DataTable("TmpDataTable");
  try
  {
  int intFieldCount = reader.FieldCount;// Gets the number of columns in the current row; 
  for (int intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
  {
   objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
  }
  //populate datatable 
  objDataTable.BeginLoadData();
  //object[] objValues = new object[intFieldCount -1]; 
  object[] objValues = new object[intFieldCount];
  while (reader.Read())
  {
   reader.GetValues(objValues);
   objDataTable.LoadDataRow(objValues, true);
  }
  reader.Close();
  objDataTable.EndLoadData();
  return objDataTable;
  }
  catch (Exception ex)
  {
  throw new Exception(" Conversion error error !", ex);
  }
 }

Invoke method

OracleParameter[] oracleParameter = new OracleParameter[]{
new OracleParameter("MYCS1",OracleType.Cursor),
new OracleParameter("MYCS2",OracleType.Cursor),
new OracleParameter("a",OracleType.VarChar,200),
};
oracleParameter[0].Direction = ParameterDirection.Output;
oracleParameter[1].Direction = ParameterDirection.Output;
oracleParameter[2].Direction = ParameterDirection.Output;

object[] xxx = ExcuteProc_N_Result("gd_CURSOR", 3, oracleParameter);

Call oracle stored procedure method 2:

The structure of the stored procedure is as follows:

Create or Replace Procedure xx_yy
(
 i_OrderID in number,
 i_ReturnValue out number
)
is
 v_RealValue number;
 v_TotalValue number;
 v_AdvendorID number;
begin
  Just write it yourself 
end;

Let's talk about calling 1:

Table structure

create table ORDERTABLE
(
 ORDERID NUMBER not null,
 TEXT NUMBER not null
)

Stored procedure

(
 i_OrderID in number,
 i_ReturnValue out number
)
is
 spass ordertable.text%type;
begin
 select text into spass from ordertable where orderid=i_OrderID; 
 i_ReturnValue:=spass;
 exception
 when no_data_found
 then i_ReturnValue:=-1; 
end;

Source code:

using System.Data .OracleClient ;//( Don't forget to add )
OracleConnection Oraclecon = new OracleConnection ("Password=dloco;User ID=dloco;Data Source=dloco;");
  OracleCommand myCMD = new OracleCommand();
  OracleParameter[] parameters = { new OracleParameter("i_OrderID", OracleType.Number, 10),new OracleParameter("i_ReturnValue",OracleType.Number,10 )};
  parameters[0].Value = 1;
  parameters[1].Direction = ParameterDirection.Output;

  myCMD.Connection = Oraclecon;
  myCMD.CommandType = CommandType.StoredProcedure;
  myCMD.CommandText = "dloco.xx_yy";

  myCMD.Parameters .Add (parameters[0]);
  myCMD.Parameters .Add (parameters[1]);

  myCMD.Connection.Open();  

  myCMD.ExecuteNonQuery();  
  
  string result=myCMD.Parameters["i_ReturnValue"].Value.ToString();
  MessageBox.Show (result);

  Oraclecon.Close();

The above is the asp. net call oracle stored procedure of all content, I hope to help you.