C# NpgsqlCommand.ExecuteNonQuery方法代码示例

        public void Long([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
        {
            var builder = new StringBuilder("ABCDEééé", Conn.BufferSize);
            builder.Append('X', Conn.BufferSize);
            var expected = builder.ToString();
            ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
            var cmd = new NpgsqlCommand(@"INSERT INTO data (name) VALUES (@p)", Conn);
            cmd.Parameters.Add(new NpgsqlParameter("p", expected));
            cmd.ExecuteNonQuery();

            const string queryText = @"SELECT name, 'foo', name, name, name, name FROM data";
            cmd = new NpgsqlCommand(queryText, Conn);
            var reader = cmd.ExecuteReader(behavior);
            reader.Read();

            var actual = reader[0];
            Assert.That(actual, Is.EqualTo(expected));

            if (IsSequential(behavior))
                Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential");
            else
                Assert.That(reader[0], Is.EqualTo(expected));

            Assert.That(reader.GetString(1), Is.EqualTo("foo"));
            Assert.That(reader.GetFieldValue<string>(2), Is.EqualTo(expected));
            Assert.That(reader.GetValue(3), Is.EqualTo(expected));
            Assert.That(reader.GetFieldValue<string>(4), Is.EqualTo(expected));
            Assert.That(reader.GetFieldValue<char[]>(5), Is.EqualTo(expected.ToCharArray()));
        }

        public void ExceptionFieldsArePopulated()
        {
            const string dropTable = @"DROP TABLE IF EXISTS public.uniqueviolation";
            const string createTable = @"CREATE TABLE public.uniqueviolation (id INT NOT NULL, CONSTRAINT uniqueviolation_pkey PRIMARY KEY (id))";
            const string insertStatement = @"INSERT INTO public.uniqueviolation (id) VALUES(1)";

            // In this case we'll test a simple unique violation, we're not too interested in testing more
            // cases than this as the same code is executed in all error situations.
            try
            {
                var command = new NpgsqlCommand(dropTable, Conn);
                command.ExecuteNonQuery();

                command = new NpgsqlCommand(createTable, Conn);
                command.ExecuteNonQuery();

                command = new NpgsqlCommand(insertStatement, Conn);
                command.ExecuteNonQuery();

                //Now cause the unique violation...
                command.ExecuteNonQuery();

            }
            catch (NpgsqlException ex)
            {
                Assert.AreEqual("", ex.ColumnName); // Should not be populated for unique violations.
                Assert.AreEqual("uniqueviolation", ex.TableName);
                Assert.AreEqual("public", ex.SchemaName);
                Assert.AreEqual("uniqueviolation_pkey", ex.ConstraintName);
                Assert.AreEqual("", ex.DataTypeName); // Should not be populated for unique violations.
            }
        }

        public void TestSubquery()
        {
            const string sql = @"SELECT testid FROM preparetest WHERE :p1 IN (SELECT varchar_notnull FROM preparetest)";
            var cmd = new NpgsqlCommand(sql, TheConnection);
            var p1 = new NpgsqlParameter(":p1", DbType.String);
            p1.Value = "blahblah";
            cmd.Parameters.Add(p1);
            cmd.ExecuteNonQuery(); // Succeeds

            cmd.Prepare(); // Fails
            cmd.ExecuteNonQuery();
        }

		/// <summary>
		/// Default constructor.
		/// </summary>
		/// <param name="intitString"></param>
		public pgsql_API(string intitString)
		{
			// connectionstring=
			string[] parameters = intitString.Replace("\r\n","\n").Split('\n');
			foreach(string param in parameters){
				if(param.ToLower().IndexOf("connectionstring=") > -1){
					m_ConStr = param.Substring(17);
				}
			}
         
            SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(m_ConStr);
            string database = b.InitialCatalog;
            b.InitialCatalog = "";              
            using(NpgsqlConnection con = new NpgsqlConnection(b.ToString().ToLower().Replace("data source","server"))){                    
                con.Open();

                // See if database exists
                try{
                    con.ChangeDatabase(database);
                }
                catch{
                    // Database don't exist, try to create it

                    try{
                        con.Close();
                        con.ConnectionString = b.ToString().ToLower().Replace("data source","server");
                        con.Open();


                        NpgsqlCommand cmd = new NpgsqlCommand();
                        cmd.Connection = con;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "create database \"" + database + "\"";
                        cmd.ExecuteNonQuery();
                        con.ChangeDatabase(database);

                        // Create tables
                        cmd.CommandText = ResManager.GetText("tables.sql",System.Text.Encoding.Default);
                        cmd.ExecuteNonQuery();

                        // Create procedures
                        cmd.CommandText = ResManager.GetText("procedures.sql",System.Text.Encoding.Default);
                        cmd.ExecuteNonQuery();
                    }
                    catch{
                        throw new Exception("Database '" + database + "' doesn''t exist ! Create failed, specified user doesn't have enough permisssions to create database ! Create database manually.");
                    }                    
                }
            }
		}

 public void Alterar(Model_Vo_Agenda pAgenda)
 {
     // conexao
     SqlConnection cn = new SqlConnection();
     try
     {
         cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = cn;
         cmd.CommandType = CommandType.Text;
         cmd.CommandText = "update agenda set datahorareserva = @datahorareserva, idcliente = @idcliente, idsala = @idsala where id = @id;";
         cmd.Parameters.AddWithValue("@id", pAgenda.Id);
         cmd.Parameters.AddWithValue("@datahorareserva", Dados.Model_Dao_Dados.ConverterDataToStr(pAgenda.DataHoraReserva, false));
         cmd.Parameters.AddWithValue("@idcliente", pAgenda.IdCliente);
         cmd.Parameters.AddWithValue("@idsala", pAgenda.IdSala);
         cn.Open();
         cmd.ExecuteNonQuery();
     }
     catch (SqlException ex)
     {
         throw new Exception("Servidor SQL Erro:" + ex.Number);
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     finally
     {
         cn.Close();
     }
 }

        internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
        {
            resman = new System.Resources.ResourceManager(this.GetType());

            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
            if ((isolation != IsolationLevel.ReadCommitted) &&
                    (isolation != IsolationLevel.Serializable))
                throw new ArgumentOutOfRangeException(resman.GetString("Exception_UnsopportedIsolationLevel"), "isolation");

            _conn = conn;
            _isolation = isolation;

            StringBuilder commandText = new StringBuilder("SET TRANSACTION ISOLATION LEVEL ");

            if (isolation == IsolationLevel.ReadCommitted)
                commandText.Append("READ COMMITTED");
            else
                commandText.Append("SERIALIZABLE");

            commandText.Append("; BEGIN");

            NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector);
            command.ExecuteNonQuery();
            _conn.Connector.Transaction = this;
        }

        //När användaren kilckar på "OK" sker följande:
        private void OK_button_Click(object sender, EventArgs e)
        {
            //Deklarerar variabel klass
            var klass = "";

            //Om "A" är iklickat av användaren får variabel klass värdet A.
            if (A_radioButton.Checked == true)
            {
                klass = "A";
            }
            //Om "B" är iklickat av användaren får variabel klass värdet B.
            else if (B_radioButton.Checked == true)
            {
                klass = "B";
            }
            //Om "C" är iklickat av användaren får variabel klass värdet C.
            else if (C_radioButton.Checked == true)
            {
                klass = "C";
            }

            //Skapar strängen anmaldeltagare.
            //Strängen innehåller information om tävlingsdeltagare. Lägger in golfid, tävlingid och klass i databasen, i tabellen deltari.
            string anmaldeltagare = "insert into deltari (golfid, tavlingid, klass) values ('" + Golfid_textBox.Text + "'," + Tävlingid_textBox.Text + ",'" + klass + "')";
            //Skapar ett nytt Npgsql-kommando, command1.
            NpgsqlCommand command1 = new NpgsqlCommand(anmaldeltagare, Huvudfönster.conn);
            //Utför kommando, command1.
            command1.ExecuteNonQuery();

            //När allt ovan är utfört visas en meddelanderuta.
            MessageBox.Show("Spelare är nu anmäld till tävling!");
            //Sedan stängs hela detta form, AnmälDeltagare.
            this.Close();
        }

        public static void CleanTables(NpgsqlConnection connection)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            var script = GetStringResource(
                typeof (PostgreSqlTestObjectsInitializer).Assembly,
                "Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", string.Format("'{0}'", ConnectionUtils.GetSchemaName()));

			//connection.Execute(script);

			using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
			using (var command = new NpgsqlCommand(script, connection, transaction))
			{
				command.CommandTimeout = 120;
				try
				{
					command.ExecuteNonQuery();
					transaction.Commit();
				}
				catch (NpgsqlException ex)
				{
					throw;
				}
			}
		}

        public static bool ModificarUsuario(string nombre, string apellido, string direccion, string nombreusuario, string email)
        {
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand("update usuario set nombre = @nombre, apellido = @apellido, direccion = @direccion, email = @email where nombreusuario = @nombreusuario", Conexion.conexion);
                cmd.Parameters.Add("nombre", nombre);
                cmd.Parameters.Add("apellido", apellido);
                cmd.Parameters.Add("direccion", direccion);
                cmd.Parameters.Add("nombreusuario", nombreusuario);
                cmd.Parameters.Add("email", email);
                Conexion.abrirConexion();
                if (cmd.ExecuteNonQuery() != -1)
                {
                    Conexion.cerrarConexion();
                    return true;
                }
                else
                {
                    Conexion.cerrarConexion();
                    return false;
                }

            }
            catch (Exception ex)
            {
                throw new Exception("Hubo un error con la base de datos, intente de nuevo más tarde");
            }
        }

 public void Alterar(Model_Vo_Produto pProduto)
 {
     // conexao
     SqlConnection cn = new SqlConnection();
     try
     {
         cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = cn;
         cmd.CommandType = CommandType.Text;
         cmd.CommandText = "update produto set descricao = @descricao, unidade = @unidade, estoque = @estoque, valordevenda = @valordevenda, observacao = @observacao where id = @id;";
         cmd.Parameters.AddWithValue("@descricao", pProduto.Descricao);
         cmd.Parameters.AddWithValue("@unidade", pProduto.Unidade);
         cmd.Parameters.AddWithValue("@estoque", pProduto.Estoque);
         cmd.Parameters.AddWithValue("@valordevenda", pProduto.ValorDeVenda);
         cmd.Parameters.AddWithValue("@observacao", pProduto.Observacao);
         cmd.Parameters.AddWithValue("@id", pProduto.Id);
         cn.Open();
         cmd.ExecuteNonQuery();
     }
     catch (SqlException ex)
     {
         throw new Exception("Servidor SQL Erro:" + ex.Number);
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     finally
     {
         cn.Close();
     }
 }

 private void btnChange_Click(object sender, EventArgs e)
 {
     try
     {
         Postgres.ConnectionString = "Server=" + Properties.Settings.Default.Server + ";Port=" +
                       Properties.Settings.Default.Port +
                       ";User Id=" + Properties.Settings.Default.UserId + ";Password=" +
                       Properties.Settings.Default.Password + ";Database=" + Properties.Settings.Default.Database +
                       ";";
         Postgres.Open();
         var commandUpdateDescr =
             new NpgsqlCommand(
                 "UPDATE \"GPL\".\"INFORMATION\" SET \"DESCRIPTION\" = '" + txtDescription.Text + "' WHERE \"ID\" = " + _descrId + ";",
                 Postgres);
         commandUpdateDescr.ExecuteNonQuery();
         MessageBox.Show(@"Изменения применены", @"Изменение инструкции", MessageBoxButtons.OK,
             MessageBoxIcon.Information);
         Postgres.Close();
         Close();
     }
     catch (Exception ex)
     {
         throw new Exception(@"Ошибка: ", ex);
     }
 }

        public static bool CleanupOrphans()
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_pages ");
            sqlCommand.Append("SET parentid = -1, parentguid = '00000000-0000-0000-0000-000000000000' ");
            sqlCommand.Append("WHERE parentid <> -1 AND parentid NOT IN (SELECT pageid FROM mp_pages ) ");
            sqlCommand.Append("");

            int rowsAffected = 0;

            // using scopes the connection and will close it /destroy it when it goes out of scope
            using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString.GetWriteConnectionString()))
            {
                conn.Open();
                using (NpgsqlCommand command = new NpgsqlCommand(sqlCommand.ToString(), conn))
                {
                    //command.Parameters.Add(new NpgsqlParameter("pageguid", DbType.StringFixedLength, 36));
                    command.Prepare();
                    //command.Parameters[0].Value = pageGuid.ToString();
                    rowsAffected = command.ExecuteNonQuery();
                }
            }

            return (rowsAffected > 0);
        }

        internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
        {
            resman = new System.Resources.ResourceManager(this.GetType());

            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
            
            _conn = conn;
            _isolation = isolation;

            StringBuilder commandText = new StringBuilder("BEGIN; SET TRANSACTION ISOLATION LEVEL ");

            if ( (isolation == IsolationLevel.RepeatableRead) || 
                 (isolation == IsolationLevel.Serializable)
               )
                commandText.Append("SERIALIZABLE");
            else
            {
                // Set isolation level default to read committed.
                _isolation = IsolationLevel.ReadCommitted;
                commandText.Append("READ COMMITTED");
            }

            commandText.Append(";");

            NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector);
            command.ExecuteNonQuery();
            _conn.Connector.Transaction = this;
        }

        private void insertIntoBase()
        {
            try
            {

            short total = Convert.ToInt16( this.textBox1.Text.Trim());
            int s_sum = Convert.ToInt32(this.textBox2.Text.Trim());
            short d_sum = Convert.ToInt16(this.textBox3.Text.Trim());

            short measure = Convert.ToInt16(this.textBox4.Text.Trim());
            int _s = Convert.ToInt32(this.textBox5.Text.Trim());
            short _d = Convert.ToInt16(this.textBox6.Text.Trim());

            string _comm = this.richTextBox1.Text.Trim();

            NpgsqlCommand _command = new NpgsqlCommand("Insert into tumor_size (total, d_sum, s_sum, s, d, measurable, pat_id, rasp_data, got_comment, brain, kidney, skin, bones, bone_marrow, lung, lymphatic, adrenal, os, hepar, lien, other, doc_id) values "
                + "( '" + total + "','" + d_sum + "','" + s_sum + "', '" + _s + "','" + _d + "','" + measure + "','" + this.searchPatientBox1.pIdN + "','" + this.dateTimePicker1.Value + "', :descr ,'" + this.checkedListBox1.GetItemChecked(0)
                + "','" + this.checkedListBox1.GetItemChecked(1) + "','" + this.checkedListBox1.GetItemChecked(2) + "','" + this.checkedListBox1.GetItemChecked(3) + "','" + this.checkedListBox1.GetItemChecked(4) + "','" + this.checkedListBox1.GetItemChecked(5)
                + "','" + this.checkedListBox1.GetItemChecked(6) + "','" + this.checkedListBox1.GetItemChecked(7) + "','" + this.checkedListBox1.GetItemChecked(8) + "','" + this.checkedListBox1.GetItemChecked(9) + "','" + this.checkedListBox1.GetItemChecked(10)
                + "','" + this.checkedListBox1.GetItemChecked(11) + "' , '" + DBExchange.Inst.dbUsrId + "') ;", DBExchange.Inst.connectDb);
                using (_command)
                {
                    _command.Parameters.Add(new NpgsqlParameter("descr", NpgsqlDbType.Text));
                    _command.Parameters[0].Value = _comm;
                }

                _command.ExecuteNonQuery();
                clearForm();
            }
            catch (Exception exception)
            {
                Warnings.WarnLog log = new Warnings.WarnLog();    log.writeLog(MethodBase.GetCurrentMethod().Name, exception.Message.ToString(), exception.StackTrace.ToString());

            }
        }

        void mainit_paroli()
        {
            switch (db.liet_grupa) {
                case "Mācībspēks":
                    mainas_kom = new NpgsqlCommand("UPDATE macibspeki SET parole = @parole WHERE id_macsp = @lietotajs;", db.sav);
                    break;
                case "Vadība":
                    mainas_kom = new NpgsqlCommand("UPDATE vadiba SET parole = @parole WHERE id_vadiba = @lietotajs;", db.sav);
                    break;
                case "Students":
                    mainas_kom = new NpgsqlCommand("UPDATE studenti SET parole = @parole WHERE id_matr = @lietotajs;", db.sav);
                    break;
            }

            mainas_kom.Parameters.AddWithValue("@lietotajs", db.lietotajs);
            mainas_kom.Parameters.AddWithValue("@parole", db.SHA1Parole(db.lietotajs, tb_jauna.Text));

            db.sav.Open();
            int ieraksti = mainas_kom.ExecuteNonQuery();
            db.sav.Close();

            if (ieraksti != -1) {
                MessageBox.Show("Lietotāja parole veiksmīgi nomainīta!", "Parole nomainīta", MessageBoxButtons.OK, MessageBoxIcon.Information);

                DialogResult = DialogResult.OK;
                Close();
            } else {
                MessageBox.Show("Paroles maiņas procesā radās neparedzēta kļūda!\nMēģiniet atkārtot paroles maiņu vēlāk.", "Kļūda paroles maiņas procesā", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }