asp.net 调用ORACLE存储过程方法

  /// <summary>
        /// 创建 OracleCommand 对象实例(用来返回一个整数值) 
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand 对象实例</returns>
        private static OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, OracleParameter[] parameters)
        {
            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
           
            command.Parameters.Add(new OracleParameter("ReturnValue",
                OracleType.Int32, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

 

 

/// <summary>
        /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand</returns>
        private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, OracleParameter[] parameters)
        {
            OracleCommand command = new OracleCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (OracleParameter parameter in parameters)
                {
                    if (parameter.OracleType == OracleType.DateTime)
                    {
                        if (parameter.Value != System.DBNull.Value && (DateTime)parameter.Value == DateTime.MinValue)
                            parameter.Value = System.DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

 

 

 

      /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleDataReader</returns>
        public static OracleDataReader RunProcedure(string connectionString, string storedProcName, OracleParameter[] parameters)
        {
            OracleConnection connection = new OracleConnection(connectionString);
            OracleDataReader returnReader;
            connection.Open();
            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            return returnReader;
        }

 

调用：

 //prm.Direction = ParameterDirection.Input;
        OracleParameter[] parameters = {
     new OracleParameter("zBeginDate", OracleType.VarChar,50),
     new OracleParameter("zEndDate", OracleType.VarChar,50),
     new OracleParameter("Suppliers", OracleType.VarChar,100),
                    new OracleParameter("Userid", OracleType.VarChar,20),
                    new OracleParameter("Maxids", OracleType.VarChar,20)};
        if (Text_BeginDate.Text != String.Empty)
        {
            parameters[0].Value =Text_BeginDate.Text;
        }
        if (Text_EndDate.Text != String.Empty)
        {
            parameters[1].Value = Text_EndDate.Text;
        }
        if (stritem != null)
        {
            parameters[2].Value = stritem.ToString();
        }
        //存储过程返回的参数
        parameters[4].Direction = ParameterDirection.Output;
       
        user=(XT_TC_USER)Session["USER_MODEL"];
        parameters[3].Value = user.StafferCode;
        DbHelperSQL.ExcProcedure(DbHelperSQL.LocalSqlServer, "CONLLIGATE_MAIN", parameters);
        //接受返回参数
        string Maxids = parameters[4].Value.ToString();