C# OracleClient.OracleParameter类代码示例

        public void createPost(Member member)
        {
            try
            {

                String connstr = "Data source = studentoracle.students.ittralee.ie/orcl;User id = t00171832;Password=" + getPassword() + ";";
                OracleConnection conn = new OracleConnection(connstr);
                conn.Open();

                string query = "insert into posts values('" + member.Username + "', '" + member.Email + "' , " + member.Phone + " , :BlobParameter , '" + member.Image + "' , '"
                    + member.JoinDate + "' , " + member.MsgPrivate + " , '" + member.LastSeen  + ")";
                MessageBox.Show(query);
                //insert the byte as oracle parameter of type blob
                OracleParameter blobParameter = new OracleParameter();
                blobParameter.OracleType = OracleType.Blob;
                blobParameter.ParameterName = "BlobParameter";
                blobParameter.Value = member.Image;

                OracleCommand cmnd = new OracleCommand(query, conn);
                cmnd.Parameters.Add(blobParameter);
                cmnd.ExecuteNonQuery();
                cmnd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
            }
        }

        private void stdnt_payment_info_btn_Click(object sender, EventArgs e)
        {
            dbConnect conObj = new dbConnect();
            OracleConnection conn = conObj.connFunc();
            OracleParameter parOut = new OracleParameter();

            parOut.ParameterName = "res_cur";
            parOut.OracleType = OracleType.Cursor;
            parOut.Size = 32;
            parOut.Direction = System.Data.ParameterDirection.Output;

            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "student_payment_procedure";
            cmd.Parameters.Add(parOut);

            conn.Open();

            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet myset = new DataSet("res_cur");
            adapter.Fill(myset);
            stdnt_payment_dataGridView.DataSource = myset;
            stdnt_payment_dataGridView.DataSource = myset.Tables[0];

            conn.Close();
        }

 public SMZJ.Model.TB_QUOTA_DETAIL_TMP GetModel(int AUTO_NO)
 {
     StringBuilder builder = new StringBuilder();
     builder.Append("select AUTO_NO,PD_QUOTA_CODE,COMPANY_NAME,FILE_NAME,FILE_SYSNAME,COMPANY_CODE,FILE_TYPE from TB_QUOTA_DETAIL_TMP ");
     builder.Append(" where AUTO_NO=:AUTO_NO ");
     OracleParameter[] cmdParms = new OracleParameter[] { new OracleParameter(":AUTO_NO", OracleType.Number, 4) };
     cmdParms[0].Value = AUTO_NO;
     SMZJ.Model.TB_QUOTA_DETAIL_TMP tb_quota_detail_tmp = new SMZJ.Model.TB_QUOTA_DETAIL_TMP();
     DataSet set = DbHelperOra.Query(builder.ToString(), cmdParms);
     if (set.Tables[0].Rows.Count <= 0)
     {
         return null;
     }
     if (set.Tables[0].Rows[0]["AUTO_NO"].ToString() != "")
     {
         tb_quota_detail_tmp.AUTO_NO = int.Parse(set.Tables[0].Rows[0]["AUTO_NO"].ToString());
     }
     tb_quota_detail_tmp.PD_QUOTA_CODE = set.Tables[0].Rows[0]["PD_QUOTA_CODE"].ToString();
     tb_quota_detail_tmp.COMPANY_NAME = set.Tables[0].Rows[0]["COMPANY_NAME"].ToString();
     tb_quota_detail_tmp.FILE_NAME = set.Tables[0].Rows[0]["FILE_NAME"].ToString();
     tb_quota_detail_tmp.FILE_SYSNAME = set.Tables[0].Rows[0]["FILE_SYSNAME"].ToString();
     tb_quota_detail_tmp.COMPANY_CODE = set.Tables[0].Rows[0]["COMPANY_CODE"].ToString();
     tb_quota_detail_tmp.FILE_TYPE = set.Tables[0].Rows[0]["FILE_TYPE"].ToString();
     return tb_quota_detail_tmp;
 }

        public static string ExecuteNonQuery(string sql, OracleParameter[] param)
        {
            string ret = "false";
            try
            {
                OracleConnection conn = GetConnection();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 240;

                if (param != null)
                {
                    foreach (OracleParameter p in param)
                    {
                        if (p != null)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }
                }

                cmd.ExecuteNonQuery();
                ret = "true";
            }
            catch (Exception ex)
            {
                ret = "false|Exception " + ex.Message + "\n" + ex.StackTrace.ToString();
            }

            return ret;
        }

        /// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command.
        /// </summary>
        /// <param name="command">the OracleCommand to be prepared</param>
        /// <param name="connection">a valid OracleConnection, on which to execute this command</param>
        /// <param name="transaction">a valid OracleTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
        /// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
        private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
        {
            //if the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            //associate the connection with the command
            command.Connection = connection;

            //set the command text (stored procedure name or Oracle statement)
            command.CommandText = commandText;
            command.CommandTimeout = 200000;
            //if we were provided a transaction, assign it.
            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            //set the command type
            command.CommandType = commandType;

            //attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }

            return;
        }

        /// <summary>
        /// 将生成的材料信息更新到数据库中
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="excelinfo"></param>
        public static void UpdateExcelInfo(string sql,byte[] excelinfo)
        {
            try
            {
                byte[] file = excelinfo;
                using (OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr))
                {
                    conn.Open();
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        OracleParameter op = new OracleParameter("dfd", OracleType.Blob);
                        op.Value = file;
                        if (file.Length == 0)
                        {
                            MessageBox.Show("插入信息表不能为空！", "WARNNING", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                            return;
                        }
                        else
                        {
                            cmd.Parameters.Add(op);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    conn.Close();
                }

            }
            catch (IOException ee)
            {
                MessageBox.Show(ee.Message.ToString());
                return;
            }
        }

        public static bool EditFeature(string labelid,string title,string content)
        {
            StringBuilder sb = new StringBuilder();
            using (OracleConnection conn = new OracleConnection(OraHelper.str))
            {
                conn.Open();
                OracleCommand comm = conn.CreateCommand();
                OracleParameter[] parms = new OracleParameter[]{
                    new OracleParameter(":LABELID",OracleType.VarChar),
                    new OracleParameter(":TITLE",OracleType.VarChar),
                    new OracleParameter(":CONTENT",OracleType.VarChar)
                };
                parms[0].Value = labelid;
                parms[1].Value = title;
                parms[2].Value = content;

                comm.CommandText = SQL_UPDATE_LABEL;
                comm.CommandType = CommandType.Text;
                foreach (OracleParameter parm in parms)
                {
                    comm.Parameters.Add(parm);

                }
                comm.ExecuteNonQuery();
                conn.Close();
                return true;
            }
        }

 public override int Add(DB_OPT dbo)
 {
     StringBuilder builder = new StringBuilder();
     builder.Append("insert into gov_tc_db_ServicesRegister(");
     builder.Append("ServiceTypePK,Name,Path,ClassName,ContParameters,IfTwoCont,Method,IfTwoMet,Parameters,Discription,GetServiceType,StartSign,InTime,OutTime,FatherPK,IsHasBaby,Grade,PKPath,MaxNum)");
     builder.Append(" values (");
     builder.Append(":ServiceTypePK,:Name,:Path,:ClassName,:ContParameters,:IfTwoCont,:Method,:IfTwoMet,:Parameters,:Discription,:GetServiceType,:StartSign,:InTime,:OutTime,:FatherPK,:IsHasBaby,:Grade,:PKPath,:MaxNum)");
     OracleParameter[] parameters = new OracleParameter[] {
         new OracleParameter(":ServiceTypePK", OracleType.Char, 40), new OracleParameter(":Name", OracleType.VarChar, 40), new OracleParameter(":Path", OracleType.VarChar, 0xff), new OracleParameter(":ClassName", OracleType.VarChar, 40), new OracleParameter(":ContParameters", OracleType.VarChar, 0xff), new OracleParameter(":IfTwoCont", OracleType.Char, 1), new OracleParameter(":Method", OracleType.VarChar, 40), new OracleParameter(":IfTwoMet", OracleType.Char, 1), new OracleParameter(":Parameters", OracleType.VarChar, 0xff), new OracleParameter(":Discription", OracleType.VarChar, 0xff), new OracleParameter(":GetServiceType", OracleType.VarChar, 1), new OracleParameter(":StartSign", OracleType.VarChar, 1), new OracleParameter(":InTime", OracleType.DateTime, 40), new OracleParameter(":OutTime", OracleType.DateTime, 40), new OracleParameter(":FatherPK", OracleType.VarChar, 40), new OracleParameter(":IsHasBaby", OracleType.Char, 1),
         new OracleParameter(":Grade", OracleType.Int32, 4), new OracleParameter(":PKPath", OracleType.Clob), new OracleParameter(":MaxNum", OracleType.Int32, 4)
      };
     parameters[0].Value = base.ServiceTypePK;
     parameters[1].Value = base.Name;
     parameters[2].Value = base.Path;
     parameters[3].Value = base.ClassName;
     parameters[4].Value = base.ContParameters;
     parameters[5].Value = base.IfTwoCont;
     parameters[6].Value = base.Method;
     parameters[7].Value = base.IfTwoMet;
     parameters[8].Value = base.Parameters;
     parameters[9].Value = base.Discription;
     parameters[10].Value = base.GetServiceType;
     parameters[11].Value = base.StartSign;
     parameters[12].Value = base.InTime;
     parameters[13].Value = base.OutTime;
     parameters[14].Value = base.FatherPK;
     parameters[15].Value = base.IsHasBaby;
     parameters[0x10].Value = base.Grade;
     parameters[0x11].Value = base.PKPath;
     parameters[0x12].Value = base.MaxNum;
     return dbo.ExecutionIsSucess(builder.ToString(), parameters, "");
 }

        /// <summary>  
        /// 执行带参数sql语句或存储过程，返回所影响的行数  
        /// </summary>  
        /// <param name="cmdText">带参数的sql语句和存储过程名</param>  
        /// <param name="cmdType">命令类型</param>  
        /// <param name="cmdParms">参数集合</param>  
        /// <returns>返回所影响的行数</returns>  
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            int count;

            try
            {

                init();

                cmd = new OracleCommand();

                SetCommand(cmd, cmdText, cmdType, cmdParms);

                count = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();

                conn.Close();

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message.ToString());

            }

            return count;
        }

	public void run()
	{
		Exception exp = null;

		OracleParameter param = new OracleParameter("myParam",10);

		try
		{
			BeginCase("ctor");
			Compare(param != null, true);
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}

		try
		{
			BeginCase("name");
			Compare(param.ParameterName ,"myParam");
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}

		try
		{
			BeginCase("value");
			Compare(param.Value, 10);
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}
	}

        /// <summary>
        /// Read an order from the database
        /// </summary>
        /// <param name="orderId">Order Id</param>
        /// <returns>Details of the Order</returns>
        public OrderInfo GetOrder(int orderId)
        {
            //Create a parameter
            OracleParameter parm = new OracleParameter(PARM_ORDER_ID, OracleType.Number);
            parm.Value = orderId;

            //Execute a query to read the order
            using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringOrderDistributedTransaction, CommandType.Text, SQL_SELECT_ORDER, parm)) {
                if (rdr.Read()) {
                    //Generate an order header from the first row
                    AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null, "email");
                    AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null, "email");

                    OrderInfo order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), null, billingAddress, shippingAddress, rdr.GetDecimal(21), null, null);

                    IList<LineItemInfo> lineItems = new List<LineItemInfo>();
                    LineItemInfo item = null;

                    //Create the lineitems from the first row and subsequent rows
                    do {
                        item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25));
                        lineItems.Add(item);
                    } while (rdr.Read());

                    order.LineItems = new LineItemInfo[lineItems.Count];
                    lineItems.CopyTo(order.LineItems, 0);

                    return order;
                }
            }

            return null;
        }

        public static ApplicantList GetList()
        {
            ApplicantList applicants = new ApplicantList();
            using (OracleConnection localDbConn =
                    new OracleConnection(
                        ConnStringFactory.getConnString(
                            ConnStringFactory.ConnStringType.Oracle))) {
                localDbConn.Open();

                using (OracleCommand getAllApplicantCommand = new OracleCommand()) {
                    getAllApplicantCommand.CommandType = CommandType.StoredProcedure;
                    getAllApplicantCommand.CommandText = "ApplicantsPKG.getAllApplicants";
                    getAllApplicantCommand.Connection = localDbConn;

                    OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
                    outputCursor.Direction = ParameterDirection.Output;
                    getAllApplicantCommand.Parameters.Add(outputCursor);

                    using (OracleDataReader applicantListReader =
                        getAllApplicantCommand.ExecuteReader()) {
                        if (applicantListReader.HasRows) {
                            while (applicantListReader.Read()) {
                                applicants.Add(FillDataRecord(applicantListReader));
                            }
                        }
                    }
                }
            }
            return applicants;
        }

        public static Applicant GetItem(int Id)
        {
            Applicant applicant = null;
            using (OracleConnection loanDbConn =
                    new OracleConnection(
                            ConnStringFactory.getConnString(
                                ConnStringFactory.ConnStringType.Oracle))) {
                loanDbConn.Open();
                using (OracleCommand getApplicantByIdCommand = new OracleCommand()) {
                    getApplicantByIdCommand.CommandType = CommandType.StoredProcedure;
                    getApplicantByIdCommand.CommandText = "ApplicantsPKG.getApplicantById";
                    getApplicantByIdCommand.Connection = loanDbConn;
                    getApplicantByIdCommand.Parameters.AddWithValue("Id", Id);

                    OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
                    outputCursor.Direction = ParameterDirection.Output;
                    getApplicantByIdCommand.Parameters.Add(outputCursor);

                    using (OracleDataReader applicantReader =
                            getApplicantByIdCommand.ExecuteReader()) {
                        if (applicantReader.Read()) {
                            applicant = FillDataRecord(applicantReader);
                        }
                    }
                }
            }
            return applicant;
        }

 public PD_PROJECT_KAOPING_Model GetModel(int AUTO_ID)
 {
     StringBuilder builder = new StringBuilder();
     builder.Append("select AUTO_ID,KP_TYPEID,KP_DETAILID,KP_YEAR,KP_COMPANYPK,KH_TYPE,KP_SCORE from PD_PROJECT_KAOPING ");
     builder.Append(" where AUTO_ID=:AUTO_ID ");
     OracleParameter[] cmdParms = new OracleParameter[] { new OracleParameter(":AUTO_ID", OracleType.Number, 4) };
     cmdParms[0].Value = AUTO_ID;
     PD_PROJECT_KAOPING_Model model = new PD_PROJECT_KAOPING_Model();
     DataSet set = DbHelperOra.Query(builder.ToString(), cmdParms);
     if (set.Tables[0].Rows.Count <= 0)
     {
         return null;
     }
     if (set.Tables[0].Rows[0]["AUTO_ID"].ToString() != "")
     {
         model.AUTO_ID = new int?(int.Parse(set.Tables[0].Rows[0]["AUTO_ID"].ToString()));
     }
     if (set.Tables[0].Rows[0]["KP_TYPEID"].ToString() != "")
     {
         model.KP_TYPEID = new int?(int.Parse(set.Tables[0].Rows[0]["KP_TYPEID"].ToString()));
     }
     if (set.Tables[0].Rows[0]["KP_DETAILID"].ToString() != "")
     {
         model.KP_DETAILID = new int?(int.Parse(set.Tables[0].Rows[0]["KP_DETAILID"].ToString()));
     }
     model.KP_YEAR = set.Tables[0].Rows[0]["KP_YEAR"].ToString();
     model.KP_COMPANYPK = set.Tables[0].Rows[0]["KP_COMPANYPK"].ToString();
     model.KH_TYPE = set.Tables[0].Rows[0]["KH_TYPE"].ToString();
     if (set.Tables[0].Rows[0]["KP_SCORE"].ToString() != "")
     {
         model.KP_SCORE = new int?(int.Parse(set.Tables[0].Rows[0]["KP_SCORE"].ToString()));
     }
     return model;
 }

        public static bool AddFeature(string usrid,string usrname,string password,int privilege)
        {
            StringBuilder sb = new StringBuilder();
            using (OracleConnection conn = new OracleConnection(OraHelper.str))
            {
                conn.Open();
                OracleCommand comm = conn.CreateCommand();
                OracleParameter[] parms = new OracleParameter[]{
                    new OracleParameter(":USRID",OracleType.VarChar),
                    new OracleParameter(":USRNAME",OracleType.VarChar),
                    new OracleParameter(":PASSWORD",OracleType.VarChar),
                    new OracleParameter(":PRIVILEGE",OracleType.UInt32)
                };
                parms[0].Value = usrid;
                parms[1].Value = usrname;
                parms[2].Value = password;
                parms[3].Value = privilege;

                comm.CommandText = SQL_INSERT_USER;
                comm.CommandType = CommandType.Text;
                foreach (OracleParameter parm in parms)
                {
                    comm.Parameters.Add(parm);

                }
                comm.ExecuteNonQuery();
                conn.Close();
                return true;
            }
        }