C# OracleCommand.ExecuteNonQuery方法代码示例

        public static void AddTrayORClass(string queryString, string project, string drawing, string spool,string trayorclass)
        {
            OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
            conn.Open();
            OracleTransaction trans = conn.BeginTransaction();
            OracleCommand cmd = new OracleCommand(queryString, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("project_in", OracleType.VarChar).Value = project;
            cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
            cmd.Parameters.Add("spool_in", OracleType.VarChar).Value = spool;
            cmd.Parameters.Add("trayorclass_in", OracleType.VarChar).Value = trayorclass;
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (OracleException ee)
            {
                trans.Rollback();
                MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        public static void UpdatePersonStatus( string queryString, string project, string drawing, string spool, string person, DateTime dt, string frmtxt)
        {
            OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
            conn.Open();
            //string queryString = "SP_UpdateBlankingStatus";
            OracleTransaction trans = conn.BeginTransaction();
            OracleCommand cmd = new OracleCommand(queryString, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("project_in", OracleType.VarChar).Value = project;
            cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
            cmd.Parameters.Add("spool_in", OracleType.VarChar).Value = spool;
            cmd.Parameters.Add("person_in", OracleType.VarChar).Value = person;
            cmd.Parameters.Add("datetime_in", OracleType.DateTime).Value = dt;
            cmd.Parameters.Add("frmtxt_in", OracleType.VarChar).Value = frmtxt;

            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (OracleException ee)
            {
                trans.Rollback();
                MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        private void bttFinalizar_Click(object sender, EventArgs e)
        {
            DateTime dataatual = new DateTime();
            dataatual = DateTime.Now;
            try
            {
                OracleCommand cmd = new OracleCommand("insert into aplicacao(IDAPLICACAO, AP_DATA, VALORAPLICACAO, IDFUNDOINVESTIMENTO, IDCLIENTE) values (INCREMENTADOR_APICACAO.NEXTVAL ,'" + dataatual.ToString() + "','" + ValorConvertido(double.Parse(txbValor.Text.ToString())) + "'," + idFundo().ToString() + "," + idCliente().ToString() + ")", Conexao.Conectar());
                cmd.ExecuteNonQuery();

                MessageBox.Show("Salvo com sucesso");
                txbValor.Clear();
                comboBox1.ResetText();
                comboBox2.ResetText();
            }
            catch (OracleException ex)
            {
                switch (ex.Code)
                {
                    case 02290:
                        MessageBox.Show("Valor menor que zero");
                        break;
                    default:

                        MessageBox.Show(ex.Message.ToString());
                        break;
                }
            }
        }

        public void NuevoTanque(CE_Tanque objce_tanque)
        {
            //el metodo me permite almacenar los datos del nuevo tanque.
            try
            {
                OracleConnection cnx = Conexion.ObtenerConexionOracle();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = cnx;
                cmd.CommandText = "sp_Nuevo_Tanque";
                //asignar paramentros al procedimiento almacenado
                cmd.Parameters.AddWithValue("idtanque", objce_tanque.idtanque );
                cmd.Parameters.AddWithValue("volumenactual", objce_tanque.volumenactual);
                cmd.Parameters.AddWithValue("volumenmaximo", objce_tanque.volumenmaximo);

                //abrir la conexion
                cnx.Open();
                //ejecutar el procedimiento almacenado
                cmd.ExecuteNonQuery();
                //Cerrar conexion
                cnx.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int NuevaArea(CE_Area objce_area)
        {
            //el metodo m
            try
            {
                int filasafectadas = 0;

                OracleConnection cnx = Conexion.ObtenerConexionOracle();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = cnx;
                cmd.CommandText = "sp_Nueva_Area";
                //asignar paramentros al procedimiento almacenado
                cmd.Parameters.AddWithValue("nombreareaX", objce_area.nombrearea);

                //abrir la conexion
                cnx.Open();
                //ejecutar el procedimiento almacenado
                filasafectadas = cmd.ExecuteNonQuery();
                //Cerrar conexion
                cnx.Close();
                return filasafectadas;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

		/// <summary>
		/// 执行多条SQL语句，实现数据库事务。
		/// </summary>
		/// <param name="SQLStringList">多条SQL语句</param>		
		public static void ExecuteSqlTran(ArrayList SQLStringList)
		{
			using (OracleConnection conn = new OracleConnection(connectionString))
			{
				conn.Open();
				OracleCommand cmd = new OracleCommand();
				cmd.Connection=conn;				
				OracleTransaction tx=conn.BeginTransaction();			
				cmd.Transaction=tx;				
				try
				{   		
					for(int n=0;n<SQLStringList.Count;n++)
					{
						string strsql=SQLStringList[n].ToString();
						if (strsql.Trim().Length>1)
						{
							cmd.CommandText=strsql;
							cmd.ExecuteNonQuery();
						}
					}										
					tx.Commit();					
				}
				catch(System.Data.OracleClient.OracleException E)
				{		
					tx.Rollback();
					throw new Exception(E.Message);
				}
			}
		}

        protected void lbuSubmitView2_Click(object sender, EventArgs e)
        {
            PersonnelSystem ps = PersonnelSystem.GetPersonnelSystem(this);
            Person loginPerson = ps.LoginPerson;

            using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING))
            {
                con.Open();
                using (OracleCommand command = new OracleCommand("UPDATE TB_INSIG_REQUEST SET IR_STATUS = :IR_STATUS, IR_CITIZEN_ID = :IR_CITIZEN_ID, IR_DATE_START = :IR_DATE_START, IR_RANK = :IR_RANK, IR_TITLE = :IR_TITLE, IR_NAME = :IR_NAME, IR_LASTNAME = :IR_LASTNAME, IR_GENDER = :IR_GENDER, IR_BIRTHDATE = :IR_BIRTHDATE, IR_DATE_INWORK = :IR_DATE_INWORK, IR_START_POSITION = :IR_START_POSITION, IR_START_DEGREE = :IR_START_DEGREE, IR_CURRENT_POSITION = :IR_CURRENT_POSITION, IR_TYPE = :IR_TYPE, IR_DEGREE = :IR_DEGREE, IR_CURRENT_SALARY = :IR_CURRENT_SALARY, IR_POSITION_SALARY = :IR_POSITION_SALARY WHERE IR_CITIZEN_ID = '" + loginPerson.CitizenID + "' AND IR_STATUS = 1", con))
                {
                    command.Parameters.Add(new OracleParameter("IR_STATUS", 2));
                    command.Parameters.Add(new OracleParameter("IR_CITIZEN_ID", lbCitizen.Text));
                    command.Parameters.Add(new OracleParameter("IR_DATE_START", DateTime.Now));
                    command.Parameters.Add(new OracleParameter("IR_RANK", lbRank.Text));
                    command.Parameters.Add(new OracleParameter("IR_TITLE", lbTitleName.Text));
                    command.Parameters.Add(new OracleParameter("IR_NAME", lbName.Text));
                    command.Parameters.Add(new OracleParameter("IR_LASTNAME", lbLastName.Text));
                    command.Parameters.Add(new OracleParameter("IR_GENDER", lbGender.Text));
                    command.Parameters.Add(new OracleParameter("IR_BIRTHDATE", Util.ToDateTimeOracle(Util.ToShortMonth(lbBirthDate.Text))));
                    command.Parameters.Add(new OracleParameter("IR_DATE_INWORK", Util.ToDateTimeOracle(Util.ToShortMonth(lbDateInwork.Text))));
                    command.Parameters.Add(new OracleParameter("IR_START_POSITION", lbPosiDateInwork.Text));
                    command.Parameters.Add(new OracleParameter("IR_START_DEGREE", lbDegreeDateInwork.Text));
                    command.Parameters.Add(new OracleParameter("IR_CURRENT_POSITION", lbPositionCurrent.Text));
                    command.Parameters.Add(new OracleParameter("IR_TYPE", lbType.Text));
                    command.Parameters.Add(new OracleParameter("IR_DEGREE", lbDegree.Text));
                    command.Parameters.Add(new OracleParameter("IR_CURRENT_SALARY", lbSalaryCurrent.Text));
                    command.Parameters.Add(new OracleParameter("IR_POSITION_SALARY", lbPositionSalary.Text));
                    command.ExecuteNonQuery();
                    MultiView1.ActiveViewIndex = 2;
                }

            }
        }

        public void NuevoTanqueDetalleMov(CE_TanqueDetalleMov objce_tanquedetallemov)
        {
            //el metodo me permite
            try
            {
              //  Imagenes newfoto = new Imagenes();
                //byte[] areglobytesnapshotpicture = newfoto.imageToByteArray(objce_tanquedetallemov.snapshotpicture);

                OracleConnection cnx = Conexion.ObtenerConexionOracle();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = cnx;
                cmd.CommandText = "SP_NUEVO_TANQUE_DETALLE_MOV";
                //asignar paramentros al procedimiento almacenado

                cmd.Parameters.AddWithValue("idtanque", objce_tanquedetallemov.idtanque);
                cmd.Parameters.AddWithValue("idregistro", objce_tanquedetallemov.idregistro);
                cmd.Parameters.AddWithValue("codigo_abastecimiento", objce_tanquedetallemov.codigo_abastecimiento);
                cmd.Parameters.AddWithValue("volumen_retirado", objce_tanquedetallemov.volumen_retirado);
                cmd.Parameters.AddWithValue("snapshotpicture", objce_tanquedetallemov.snapshotpicture);
                cmd.Parameters.AddWithValue("snapshotvideo", objce_tanquedetallemov.snapshotvideo);
                cmd.Parameters.AddWithValue("idtipooperacion", objce_tanquedetallemov.idtipooperacion);
                //abrir la conexion
                cnx.Open();
                //ejecutar el procedimiento almacenado
                cmd.ExecuteNonQuery();
                //Cerrar conexion
                cnx.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 /// <summary>
 ///  �������ݲ�����id
 /// </summary>
 /// <param name="strInsertSQL"> �����sql���</param>
 /// <param name="proName">�洢������</param>
 /// <param name="seqName">Ҫ�����ֶ����磺S_AgentBase</param>
 /// <returns></returns>
 public static string ExecuteProcedure(string strInsertSQL, string proName, string seqName)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         using (OracleCommand cmd = new OracleCommand(proName, connection))
         {
             try
             {
                 if (connection.State != ConnectionState.Open)
                     connection.Open();
                 cmd.CommandType = CommandType.StoredProcedure;
                 //��Parameters����ӵIJ������Ʊ��������ݿ��д洢�����еIJ������ơ����ͱ���һ��
                 cmd.Parameters.Add("strInsertSQL", OracleType.VarChar);
                 cmd.Parameters.Add("seqName", OracleType.VarChar);
                 cmd.Parameters.Add("ItemID", OracleType.VarChar, 20);
                 cmd.Parameters["ItemID"].Direction = ParameterDirection.Output; //ָ������IDΪ������͵�ֵ
                 cmd.Parameters["strInsertSQL"].Value = strInsertSQL; //��ȡ�������
                 cmd.Parameters["seqName"].Value = seqName; //��ȡ��������
                 cmd.ExecuteNonQuery(); //ִ�в���
                 string newID = cmd.Parameters["ItemID"].Value.ToString(); //��ȡid��ֵ
                 return newID; //����idֵ
             }
             catch (System.Exception ex)
             {
                 throw ex;
             }
             finally
             {
                 connection.Close();
             }
         }
     }
 }

        /// <summary>
        /// ������̺�
        /// </summary>
        /// <param name="heatno"></param>
        /// <param name="projectid"></param>
        /// <param name="spoolname"></param>
        /// <param name="materialname"></param>
        /// <param name="seqnum"></param>
        public static void AddHeatNo(string heatno, string projectid, string spoolname, string materialname, int seqnum)
        {
            OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//���conn����
            conn.Open();
            string queryString = "SP_AddHeatNo";
            OracleTransaction trans = conn.BeginTransaction();
            OracleCommand cmd = new OracleCommand(queryString, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("heatno", OracleType.VarChar).Value = heatno;
            cmd.Parameters.Add("pid", OracleType.VarChar).Value = projectid;
            cmd.Parameters.Add("spname", OracleType.VarChar).Value = spoolname;
            cmd.Parameters.Add("material", OracleType.VarChar).Value = materialname;
            cmd.Parameters.Add("seqnumber", OracleType.Number).Value = seqnum;

            cmd.Transaction = trans;
            try
            {
                //conn.Open();
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (OracleException ee)
            {
                trans.Rollback();
                MessageBox.Show(ee.Message.ToString(), "����", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                conn.Close();
            }
        }

        public int GeefChauffeurSpoor(int tramNr)
        {
            var cmds = new OracleCommand("GEEFSPOORCHAUFFEUR", conn);
            cmds.CommandType = CommandType.StoredProcedure;

            cmds.Parameters.Add("p_tramNr", OracleType.Number).Value = tramNr;

            cmds.Parameters.Add("p_spoorNr_out", OracleType.Number, 1);
            cmds.Parameters["p_spoorNr_out"].Direction = ParameterDirection.Output;

            OpenVerbinding();

            using (var da = new OracleDataAdapter(cmds))
            {
                try
                {
                    cmds.ExecuteNonQuery();
                }
                catch (Exception e)
                {

                  Console.WriteLine(e);
                }
            }

            //VERBINDING SLUITEN
            conn.Close();
            cmds.Dispose();

            //RETURN VALUE

            return  Convert.ToInt32(cmds.Parameters["p_spoorNr_out"].Value);
        }

        public bool BlokkeerSegment(int spoorNr, int segmentNr)
        {
            var cmds = new OracleCommand("BLOKKEERSEGMENT", conn);
            cmds.CommandType = CommandType.StoredProcedure;

            cmds.Parameters.Add("p_spoorNr", OracleType.Number).Value = spoorNr;
            cmds.Parameters.Add("p_segmentNr", OracleType.Number).Value = segmentNr;

            cmds.Parameters.Add("p_geslaagd_out", OracleType.Number, 1);
            cmds.Parameters["p_geslaagd_out"].Direction = ParameterDirection.Output;

            OpenVerbinding();

            using (var da = new OracleDataAdapter(cmds))
            {
                cmds.ExecuteNonQuery();
            }

            //VERBINDING SLUITEN
            conn.Close();
            cmds.Dispose();

            //RETURN VALUE
            if (cmds.Parameters["p_geslaagd_out"].Value.ToString() == "1")
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 传入单个执行DML的存储过程，返回影响行数
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="msg">接收异常信息</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>int</returns>
        public int executeNonQuery(String procedureName, ref String msg,params OracleParameter[] parameters)
        {
            int n = 0;
            try
            {
                using (OracleConnection _conn = new OracleConnection(connectionString))
                {
                    _conn.Open();
                    using (OracleCommand _cmd = new OracleCommand())
                    {
                        _cmd.Connection = _conn;
                        _cmd.CommandType = CommandType.StoredProcedure;
                        _cmd.CommandText = procedureName;
                        _cmd.Parameters.AddRange(parameters);

                        n = _cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                msg = e.Message;
            }
            return n;
        }

        public string AddComment(Video video, Comment comment)
        {
            //List<Comment> comments = GetComments(video);
            //int commentID = comments.Count() + 1;
            string query = "INSERT INTO SE_REACTIE (REACTIEID,VIDEOID,TEKST,GEBRUIKERSNAAM,DATUM,LIKES) VALUES (:commentID,:videoID,:commentText,:commentPoster,:commentPostedDate,'"+0+"')";
            // Located lower than usual, because GetComments(video) would close the connection.
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            OracleCommand command = new OracleCommand(query, connection);
            command.CommandType = CommandType.Text;
            command.Parameters.Add("commentID", comment.CommentID);
            command.Parameters.Add("videoID", video.VideoID);
            command.Parameters.Add("commentText", comment.Text);
            command.Parameters.Add("commentPoster", comment.Poster.Username);
            command.Parameters.Add("commentPostedDate", comment.PostedDate);

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return ex.ToString();
                // Catch if the command was not succesfully executed.
            }
            connection.Close();
            return "Reactie succesvol toegevoegd";
        }

        /// <summary>  
        /// 执行不带参数sql语句，返回所影响的行数  
        /// </summary>  
        /// <param name="cmdstr">增，删，改sql语句</param>  
        /// <returns>返回所影响的行数</returns>  
        public static int ExecuteNonQuery(string cmdText)
        {
            int count;

            try
            {

                init();

                cmd = new OracleCommand(cmdText, conn);

                count = cmd.ExecuteNonQuery();

                conn.Close();

            }

            catch (Exception ex)
            {

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

            }

            return count;
        }