C# OracleCommand.ExecuteNonQuery方法代码示例

        public static bool AddIncident(Incident i, int missieUID)
        {
            using (OracleConnection o = new OracleConnection(connstring))
            {
                using (OracleCommand c = new OracleCommand())
                {
                    try
                    {
                        c.Connection = o;
                        o.Open();
                        c.CommandText = "INSERT INTO TIncident (Beschrijving) VALUES ('" + i.Beschrijving + "')";
                        c.ExecuteNonQuery();

                        c.CommandText = "SELECT MAX(ID) FROM TIncident";
                        OracleDataReader dr = c.ExecuteReader();

                        if (dr.Read())
                        {
                            c.CommandText = "INSERT INTO TMissie_Incident (MissieID, IncidentID) VALUES ('" + missieUID + "', '" + GetInt(dr[0]) + "')";
                            c.ExecuteNonQuery();
                        }
                        return true;
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                        return false;
                    }
                    finally
                    {
                        o.Close();
                    }
                }
            }
        }

        //voeg nieuw account aan DB(registeren)
        public void AddAccount(string Email, string Voornaam, string voorletters, string achternaam, string geslacht,
            DateTime Geboortedatum, string Postcode, string woonplaats, string adres,
            int telefoonnumer, int Mobielnummer, string wachtwoord)
        {
            int id = Database.Instance.KrijgHoogsteID3("Account");
            string formateddatum = Geboortedatum.ToString("dd-MM-yyyy");
            string query = string.Format("Insert into ACCOUNT (Klantnummer,Voorletters,Achternaam,Voornaam,Geslacht," +
                                         "Geboortedatum,Adres,Postcode,Woonplaats,TelefoonThuis,TelefoonMobiel,TelefoonWerk," +
                                         "Email,Wachtwoord,Bezorgvoorkeur,Emailvoorkeur,Cookievoorkeur,Productvoorkeur,Betaalvoorkeur" +
                                         ") values ({0},'{1}','{2}','{3}','{4}',to_date('{5}','dd/mm/yyyy'), " +
                                         "'{6}', '{7}', '{8}', {9}, {10}, {11}," +
                                         " '{12}', '{13}', '{14}',  '{15}', '{16}', '{17}', '{18}') ",
                id, voorletters, achternaam, Voornaam, geslacht, formateddatum, adres, Postcode, woonplaats,
                telefoonnumer, Mobielnummer, 0
                , Email, wachtwoord, "Toegestaan", "Niet toegestaan", "Nee", "Nee", "Belansrekening sluiten")
                ;


            using (OracleConnection conn = Database.Instance.GetConnection())
            {
                Console.WriteLine(query);
                conn.Open();
                using (OracleCommand cmd = new OracleCommand(query, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

 protected void btnadd_Click(object sender, EventArgs e)
 {
     string ctc, ctd, dcc, poc;
     string ad = "";
     int  nowh;
     ctc = txtctc.Text;
     ctd = txtctd.Text;
     dcc = ddldcc.SelectedValue;
     poc = txtpoc.Text;
     if (txtnowh.Text == "")
     {
         nowh = 0;
     }
     else
     {
         nowh = int.Parse(txtnowh.Text);
     }
     int m;
     string str = "select count(*) from ct where ctc='" + txtctc.Text + "' and dcc='"+ddldcc.Text+"'";
     con.Open();
     OracleCommand cmdcount = new OracleCommand(str, con);
     m = int.Parse(cmdcount.ExecuteScalar().ToString());
     if (m == 1)
     {
         lblctmsg.Text = "code already exists";
     }
     else
     {
         string s = "insert into ct values('" + ctc + "','" + ctd + "','" + poc + "','" + nowh + "','" + dcc + "')";
         OracleCommand cmd = new OracleCommand(s, con);
         cmd.ExecuteNonQuery();
         lblctmsg.Text = " values inserted successfully ";
     }
     con.Close();
 }

        public void Ins(Assunto u)
        {
            using (OracleConnection conn = BaseDAO.GetConnection())
            {
                //Define o comando
                using (OracleCommand cmd = new OracleCommand("ASSUNTO_tapi.ins", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    //Define parametros
                    cmd.Parameters.Add("p_ID_ASSUNTO", u.Id_Assunto);
                    cmd.Parameters.Add("p_NOME", u.Nome);

                    //Executa
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (OracleException ex)
                    {
                        throw new Exception("Ocorreu o erro(BD): " + ex.Message);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Erro: " + ex.Message);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) { conn.Close(); }
                    }
                }
            }
        }

        //发表评论
        public ActionResult AddComment(string content)
        {
            //假设登录用户为123，需换为session
            string loginuser = "123";
            //string loginuser = Session["username"] as string
            OracleConnection conn = new OracleConnection(oradb);
            conn.Open();

            string selectStr = "select textcommentid from textcomment";
            OracleCommand cmd = new OracleCommand(selectStr, conn);
            OracleDataReader dr = cmd.ExecuteReader();
            int temp = 0;
            while (dr.Read())
            {
                if (Convert.ToInt32(dr[0]) > temp)
                {
                    temp = Convert.ToInt32(dr[0]);
                }
            }
            temp++;

            string addStr = "insert into textcomment(textcommentid, textid, content, textcomment_date, username)values(" + temp + "," + textId + ",'" + content + "','" + DateTime.Now.ToString() + "','" + loginuser + "')";
            OracleCommand cmd2 = new OracleCommand(addStr, conn);
            int insertedLines = cmd2.ExecuteNonQuery();
            conn.Close();
            return RedirectToAction("Comment", "Account");
        }

        public int Insert(EmployeeTSI info)
        {
            OracleConnection conn = new OracleConnection(ConStr);

            try
            {
                int EmpId=0;
                OracleCommand command = new OracleCommand("EmpTSI_update", conn);
                 command.CommandType = CommandType.StoredProcedure;
                var _params = command.Parameters;
                conn.Open();
                SetParameterValue(_params, "p_empl_id_no", info.Empl_Id_No, OracleDbType.Int32);
                SetParameterValue(_params, "p_tsi", info.TSI, OracleDbType.Varchar2);
                SetParameterValue(_params, "p_user_id", this.UserId, OracleDbType.Varchar2);
                SetParameterValue(_params, "p_img", info.IMG, OracleDbType.Blob);
                SetParameterValue(_params, "p_rtf", info.RTF, OracleDbType.Blob);
                SetParameterValue(_params, "p_rif", info.RIF, OracleDbType.Blob);
                SetParameterValue(_params, "p_rmf", info.RMF, OracleDbType.Blob);
                SetParameterValue(_params, "p_rrf", info.RRF, OracleDbType.Blob);
                SetParameterValue(_params, "p_rsf", info.RSF, OracleDbType.Blob);
                SetParameterValue(_params, "p_ltf", info.LTF, OracleDbType.Blob);
                SetParameterValue(_params, "p_lif", info.LIF, OracleDbType.Blob);
                SetParameterValue(_params, "p_lmf", info.LMF, OracleDbType.Blob);
                SetParameterValue(_params, "p_lrf", info.LRF, OracleDbType.Blob);
                SetParameterValue(_params, "p_lsf", info.LSF, OracleDbType.Blob);

                command.ExecuteNonQuery();
                conn.Close();
                return EmpId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void agregarNoticia(string fecha_expiracionN, string contenidoN,string encabezadoN,string tituloN)
        {
            base.abrirConexion();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conexion;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "noticias_paquete.agregarNoticia";

            OracleParameter fechaExpiracionP = new OracleParameter("fecha_expiracionN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            fechaExpiracionP.Value = fecha_expiracionN;

            OracleParameter contenidoP = new OracleParameter("contenidoN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            contenidoP.Value = contenidoN;

            OracleParameter encabezadoP = new OracleParameter("encabezadoN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            encabezadoP.Value = encabezadoN;

            OracleParameter tituloP = new OracleParameter("tituloN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            tituloP.Value = tituloN;

            cmd.Parameters.AddRange(new OracleParameter[] { fechaExpiracionP, contenidoP, encabezadoP, tituloP});

            cmd.ExecuteNonQuery();

            cmd.Dispose();
            base.cerrarConexion();
        }

        /// <summary>
        /// Handles the specified request.
        /// </summary>
        /// <param name="request">The request.</param>
        public void Handle(DeployRequestDto request)
        {
            //Get the connection string
            var sqlInputs = GetConnectionString(request);

            //get the scripts folder
            var directory = request.IntermediateDirectory;

            //get the files in the folder
            string[] fileEntries = Directory.GetFiles(directory);
            foreach (string fileName in fileEntries)
            {
                try
                {
                    string content = string.Format(File.ReadAllText(fileName));
                    using (var oracleConnection = new OracleConnection(sqlInputs.ConnectionString))
                    {
                        oracleConnection.Open();
                        var command = new OracleCommand();
                        var script = content.Replace("\t", " ");
                        script = script.Replace("\n", Environment.NewLine);
                        command.Connection = oracleConnection;
                        command.CommandText = script;
                        var result = command.ExecuteNonQuery();
                        Debug.Write(result);
                    }
                }
                catch (Exception ex)
                {
                    Debug.Write("Exception message: " + ex.Message);
                }
            }
        }

 private void button1_Click(object sender, EventArgs e)
 {
     try
     {
         if (string.IsNullOrWhiteSpace(this.textBox1.Text))
         {
             MessageBox.Show("Fill the Group name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
         }
         else
         {
             if (conn.State != ConnectionState.Open)
             {
                 conn.Open();
             }
             OracleCommand cmd = new OracleCommand("INSERT INTO MED_GROUP(GNAME,GDESCRIPTION,NOTES) VALUES('" + textBox1.Text + "','"+richTextBox1.Text+"','"+richTextBox2.Text+"')", conn);
             int i = cmd.ExecuteNonQuery();
             if (i > 0)
             {
                 MessageBox.Show("Information Uploaded...");
                 this.Close();
             }
         }
     }
     catch (Exception exc)
     {
         MessageBox.Show(exc.Message);
     }
     conn.Close();
 }

        private void bt_Enregistrer_Click(object sender, EventArgs e)
        {
            string insert = "INSERT INTO EMPLOYES1 VALUES (SEQNUMEMP.NEXTVAL, :nom , :prenom , :salaire , :dateembauche , (SELECT CODEDEP FROM DEPARTEMENT WHERE NOM=:departement ), :responsable )";

            try
            {
                if (tb_Nom.Text.Equals(String.Empty)) throw new Exception("Le nom est vide.");
                if (tb_Prenom.Text.Equals(String.Empty)) throw new Exception("Le prénom est vide.");
                if (tb_Resp.Text.Equals(String.Empty)) throw new Exception("Le responsable est vide.");
                if (tb_Salaire.Text.Equals(string.Empty)) throw new Exception("Le salaire est vide.");
                if (double.Parse(tb_Salaire.Text) < 0) throw new Exception("Le salaire ne peut pas être négatif.");
                if (double.Parse(tb_Salaire.Text) >= 500000) throw new Exception("Le salaire doit être plus petit que 500 000.");

                OracleCommand cmd = new OracleCommand(insert, _conn);

                cmd.Parameters.Add(new OracleParameter("nom", tb_Nom.Text));
                cmd.Parameters.Add(new OracleParameter("prenom", tb_Prenom.Text));
                cmd.Parameters.Add(new OracleParameter("salaire", double.Parse(tb_Salaire.Text)));
                cmd.Parameters.Add(new OracleParameter("dateembauche", dtp_date.Value.Date));
                cmd.Parameters.Add(new OracleParameter("departement", cb_Dept.Text));
                cmd.Parameters.Add(new OracleParameter("responsable", tb_Resp.Text));

                cmd.ExecuteNonQuery();
                _conn.Close();

                MessageBox.Show("L'employé a été ajouté.");
                this.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public bool Alterar(Entrada entrada)
        {
            try
            {
                int cod_entrada = entrada.Cod_Entrada1;
                string tipo_entrada = entrada.Tipo_Entrada1;
                float valor = entrada.Valor1;
                DateTime data_entrada = entrada.Data_Entrada1;
                int cod_igreja = entrada.Cod_igreja1;

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = _conn.ObterConexao();
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "ALTERA_ENTRADA";
                    cmd.Parameters.Add("PK", OracleDbType.Int32).Value = entrada.Cod_Entrada1;
                    cmd.Parameters.Add("PIGREJA", OracleDbType.Int32).Value = entrada.Cod_igreja1;
                    cmd.Parameters.Add("PTIPOENTRADA", OracleDbType.Varchar2).Value = entrada.Tipo_Entrada1;
                    cmd.Parameters.Add("PVALOR", OracleDbType.BinaryFloat).Value = entrada.Valor1;
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
        }

 static void ExecuteDropCommand(OracleConnection connection, string statement)
 {
     using (var dropCmd = new OracleCommand(statement, connection))
     {
         dropCmd.ExecuteNonQuery();
     }
 }

 public void adicionarProfesional(string idProfesional,string passwordProfesional,string nombreProfesional,string apellidosProfesional,string cedulaProfesional ,string correoProfesional ,string areaProfesional)
 {
     base.abrirConexion();
     OracleCommand cmd = new OracleCommand();
     cmd.Connection = conexion;
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.CommandText = "profesionales_paquete.adicionarProfesional";
     OracleParameter idProfesionalP = new OracleParameter("idProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     idProfesionalP.Value = idProfesional;
     OracleParameter passwordProfesionalP = new OracleParameter("passwordProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     passwordProfesionalP.Value = passwordProfesional;
     OracleParameter nombreProfesionalP = new OracleParameter("nombreProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     nombreProfesionalP.Value = nombreProfesional;
     OracleParameter apellidosProfesionalP = new OracleParameter("apellidosProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     apellidosProfesionalP.Value = apellidosProfesional;
     OracleParameter cedulaProfesionalP = new OracleParameter("cedulaProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     cedulaProfesionalP.Value = cedulaProfesional;
     OracleParameter correoProfesionalP = new OracleParameter("correoProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     correoProfesionalP.Value = correoProfesional;
     OracleParameter areaProfesionalP = new OracleParameter("areaProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     areaProfesionalP.Value = areaProfesional;
     cmd.Parameters.AddRange(new OracleParameter[] { idProfesionalP, passwordProfesionalP, nombreProfesionalP, apellidosProfesionalP, cedulaProfesionalP, correoProfesionalP, areaProfesionalP });
     cmd.ExecuteNonQuery();
     cmd.Dispose();
     base.cerrarConexion();
 }

        /// <summary>
        /// Voegt een Bestemming toe aan de DB
        /// </summary>
        /// <param name="bestemming"></param>
        /// <returns></returns>
        public bool AddBestemming(Bestemming bestemming)
        {
            bool success = false;

            try
            {
                string sql = @"INSERT INTO Bestemming VALUES( :naam, :land)";
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.Parameters.Add("naam", OracleDbType.Varchar2, bestemming.Naam, ParameterDirection.Input);
                cmd.Parameters.Add("land", OracleDbType.Varchar2, bestemming.Land, ParameterDirection.Input);
                cmd.CommandType = CommandType.Text;
                Open();
                if (cmd.ExecuteNonQuery() == 0)
                    success = false;
                else
                    success = true;
            }
            catch
            {
                success = false;
            }
            finally
            {
                conn.Close();
            }
            return success;
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            int a;
            a = Convert.ToInt32(textBox4.Text);
            a = int.Parse(textBox4.Text);
            string oradb = "Data Source=XE;User Id=system;Password=admin;";

            OracleConnection conn = new OracleConnection(oradb);

            conn.Open();

            OracleCommand cmd = new OracleCommand();

            cmd.Connection = conn;

            cmd.CommandText = "update pharm.DRUG set QUANTITY =QUANTITY - " + a + "";

            int rowsUpdated = cmd.ExecuteNonQuery();

            if (rowsUpdated == 0)

                MessageBox.Show("Record not inserted");

            else

                MessageBox.Show("Success!");

            conn.Dispose();
        }