你的位置:首页 > ASP.net教程

[ASP.net教程]asp.net oracle 存储过程


ORACLE代码

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

C#代码

 /// <summary>    /// 执行oracle存储过程返回多个结果集    /// </summary>    /// <param name="strProcName">存储过程名称</param>    /// <param name="ResultCount">返回个数</param>    /// <param name="paras">参数</param>    /// <returns>任意对象数组</returns>    public object[] ExcuteProc_N_Result(string strProcName, int ResultCount, params OracleParameter[] paras)    {      using (OracleConnection conn = new OracleConnection("User ID=用户名;Password=密码;Data Source=数据库;"))      {        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>     /// 将DataReader 转为 DataTable     /// </summary>     /// <param name="DataReader">OleDbDataReader</param>     protected DataTable ConvertDataReaderToDataTable(OracleDataReader reader)    {      DataTable objDataTable = new DataTable("TmpDataTable");      try      {        int intFieldCount = reader.FieldCount;//获取当前行中的列数;        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("转换出错出错!", ex);      }    }

调用方法

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);