ExecuteNonQuery() public method

Executes a SQL statement against the connection and returns the number of rows affected.
public ExecuteNonQuery ( ) : Int32
return System.Int32
Beispiel #1
1
        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()));
        }
Beispiel #2
0
        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.
            }
        }
Beispiel #3
0
        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();
        }
Beispiel #4
0
		/// <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);
     }
 }
Beispiel #12
0
        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);
        }
Beispiel #13
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;
        }
Beispiel #14
0
        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());

            }
        }
Beispiel #15
0
        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);
            }
        }
        //När användaren kilckar på "Radera markerad" sker följande
        private void RaderaMarkerad_button_Click(object sender, EventArgs e)
        {
            //Använder strängen id för att se vilket golfid det är som användaren har markerat och valt att radera.
            string id = markeratid();

            //Skapar strängen tabort1.
            //Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen medlem.
            String tabort1 = "delete from deltari where golfid = '" + id + "';";
            //Skapar ett nytt Npgsql kommando, command11.
            NpgsqlCommand command11 = new NpgsqlCommand(tabort1, Huvudfönster.conn);
            //Utför kommando, command12.
            command11.ExecuteNonQuery();

            //Skapar strängen tabort1.
            //Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen person.
            String tabort2 = "delete from person where golfid = '" + id + "';";
            //Skapar ett nytt Npgsql kommando, command12.
            NpgsqlCommand command12 = new NpgsqlCommand(tabort2, Huvudfönster.conn);
            //Utför kommando, command12.
            command12.ExecuteNonQuery();

            //Skapar strängen tabort2.
            //Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen medlem.
            String tabort3 = "delete from medlem where golfid = '" + id + "';";
            //Skapar ett nytt Npgsql kommando, command122.
            NpgsqlCommand command122 = new NpgsqlCommand(tabort3, Huvudfönster.conn);
            //Utför kommando, command122.
            command122.ExecuteNonQuery();

            //När allt ovan är utfört visas en meddelanderuta.
            MessageBox.Show("Vald spelare är nu borttagen ur databasen!");
        }
Beispiel #17
0
 public static Facture getAjoutFacture(Facture a)
 {
     NpgsqlConnection con = Connexion.Connection();
     try
     {
         string insert = "insert into yvs_com_doc_ventes"
                 + "(num_piece, type_doc, statut, client,  supp, actif, "
                 + "num_doc, entete_doc, heure_doc, montant_avance, solde, date_save, mouv_stock)"
                 + "values ('" + a.NumPiece + "', '" + a.TypeDoc + "', '" + a.Statut + "', " + a.Client.Id + ",  false, true, "
                 + "'" + a.NumDoc + "', " + a.Entete.Id + ", '" + a.HeureDoc.ToString("T") + "', " + a.MontantAvance + ", " + a.Solde + ", '" + DateTime.Now + "', " + a.MouvStock + ")"; ;
         if ((a.Categorie != null) ? a.Categorie.Id > 0 : false)
         {
             insert = "insert into yvs_com_doc_ventes"
                 + "(num_piece, type_doc, statut, client, categorie_comptable, supp, actif, "
                 + "num_doc, entete_doc, heure_doc, montant_avance, solde, date_save, mouv_stock)"
                 + "values ('" + a.NumPiece + "', '" + a.TypeDoc + "', '" + a.Statut + "', " + a.Client.Id + ", " + a.Categorie.Id + ", false, true, "
                 + "'" + a.NumDoc + "', " + a.Entete.Id + ", '" + a.HeureDoc.ToString("T") + "', " + a.MontantAvance + ", " + a.Solde + ", '" + DateTime.Now + "', " + a.MouvStock + ")";
         }
         NpgsqlCommand cmd = new NpgsqlCommand(insert, con);
         cmd.ExecuteNonQuery();
         a.Id = getCurrent();
         return a;
     }
     catch (NpgsqlException e)
     {
         Messages.Exception(e);
         return null;
     }
     finally
     {
         Connexion.Deconnection(con);
     }
 }
Beispiel #18
0
        public void DeriveParametersVarious()
        {
            // This function returns record because of the two Out (InOut & Out) parameters
            ExecuteNonQuery(@"CREATE OR REPLACE FUNCTION ""func""(IN param1 INT, OUT param2 text, INOUT param3 INT) RETURNS record AS 
                              '
                              BEGIN
                                      param2 = ''sometext'';
                                      param3 = param1 + param3;
                              END;
                              ' LANGUAGE 'plpgsql';");

            var cmd = new NpgsqlCommand("func", Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            NpgsqlCommandBuilder.DeriveParameters(cmd);
            Assert.That(cmd.Parameters, Has.Count.EqualTo(3));
            Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
            Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
            Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.InputOutput));
            cmd.Parameters[0].Value = 5;
            cmd.Parameters[2].Value = 4;
            cmd.ExecuteNonQuery();
            Assert.That(cmd.Parameters[0].Value, Is.EqualTo(5));
            Assert.That(cmd.Parameters[1].Value, Is.EqualTo("sometext"));
            Assert.That(cmd.Parameters[2].Value, Is.EqualTo(9));
        }
Beispiel #19
0
        private void create_pass()
        {
            if (titleB.Text.Length != 0)
            {
                if (loginB.Text.Length != 0)
                {
                    if (pswdB.Text.Length != 0)
                    {
                        if (linkB.Text.Length != 0)
                        {
                            mf1.connect_db();
                            string sql_insert = "INSERT INTO infos (title, login, password,";
                            if (noteB.Text.Length != 0)
                            {
                                sql_insert += " note,";
                            }
                            sql_insert += " link ) VALUES (";
                            sql_insert += "'" + titleB.Text + "', ";
                            sql_insert += "'" + loginB.Text + "', ";
                            sql_insert += "'" + pswdB.Text + "', ";
                            if (noteB.Text.Length != 0)
                            {
                                sql_insert += "'" + noteB.Text + "', ";
                            }
                            sql_insert += "'" + linkB.Text + "'); ";
                            NpgsqlCommand command = new NpgsqlCommand(sql_insert, mf1.connect_db());
                            try
                            {
                                command.ExecuteNonQuery();
                                mf1.connect_db().Close();
                            }
                            catch

                            {
                                MessageBox.Show("Что-то пошло нетак! ");
                                return;
                            }
                                MessageBox.Show("Новое устройство добавленно!");
                        }

                        else
                        {
                            MessageBox.Show("Поле IP - пусто!");
                        }
                    }
                    else
                    {
                        MessageBox.Show("Поле пароля - пусто!");
                    }
                 }
                else
                {
                    MessageBox.Show("Поле Логни - пусто!");
                }
            }
            else
            {
                MessageBox.Show("Поле Названия - пусто!");
            }
        }
Beispiel #20
0
 public void Alterar(Model_Vo_Sala pSala)
 {
     // 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 sala set nome = @nome, capacidade = @capacidade, tipo = @tipo, idproduto = @idproduto where id = @id;";
         cmd.Parameters.AddWithValue("@id", pSala.Id);
         cmd.Parameters.AddWithValue("@nome", pSala.Nome);
         cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
         cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString().ToString());
         cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
         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();
     }
 }
Beispiel #21
0
        public static bool ExecuteNonQuery(string catalog, NpgsqlCommand command)
        {
            try
            {
                if (command != null)
                {
                    if (ValidateCommand(command))
                    {
                        using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog)))
                        {
                            command.Connection = connection;
                            connection.Open();

                            command.ExecuteNonQuery();
                            return true;
                        }
                    }
                }

                return false;
            }
            catch (NpgsqlException ex)
            {
                if (ex.Code.StartsWith("P"))
                {
                    string errorMessage = GetDBErrorResource(ex);
                    throw new MixERPException(errorMessage, ex);
                }

                throw;
            }
        }
        public void CreateIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
        {
            string indexName = data.ToString();
            indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
            indexName += "_" + index + @"_idx";

            if (data == DataEnum.countries)
            {
                if (index == IndexEnum.gist)
                    indexName = "countries_geom_gist";
                else return;
            }

            string createIndexCommandText =
                @"DO $$
                BEGIN
                IF NOT EXISTS (
                    SELECT 1
                    FROM   pg_class c
                    JOIN   pg_namespace n ON n.oid = c.relnamespace
                    WHERE  c.relname = '" + indexName + @"'
                    AND    n.nspname = 'public'
                    ) THEN
                    CREATE INDEX " + indexName + @"
                    ON random_points_" + (int)dataSize + @"
                    USING " + index + @" (geom);
                END IF;
                END$$;";

            using (var command = new NpgsqlCommand(createIndexCommandText, _conn))
                command.ExecuteNonQuery();
        }
Beispiel #23
0
        private static void Run()
        {
            var connStr = ConfigurationManager.AppSettings["ConnectionString"];
            using(NpgsqlConnection conn = new NpgsqlConnection(connStr))
            using(NpgsqlConnection updateConn = new NpgsqlConnection(connStr))
            {
                conn.Open();
                updateConn.Open();

                // Define a query returning a single row result set
                using (NpgsqlCommand command = new NpgsqlCommand("select id, file_name, fk_file_id from source_documents where thumbnail_created = false;", conn))
                {
                    using (NpgsqlDataReader dr = command.ExecuteReader())
                    {
                        // Output rows
                        while (dr.Read())
                        {
                            CreateThumbnail((string)dr[1], (long)dr[2]);
                            using (NpgsqlCommand update = new NpgsqlCommand("update source_documents set thumbnail_created = true where id = " + dr[0], updateConn))
                            {
                                update.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }
Beispiel #24
0
        public void ColumnNameExceptionFieldIsPopulated()
        {
            const string dropTable = @"DROP TABLE IF EXISTS public.notnullviolation";
            const string createTable = @"CREATE TABLE public.notnullviolation (id INT NOT NULL)";
            const string insertStatement = @"INSERT INTO public.notnullviolation (id) VALUES(NULL)";

            // Since the 5 error fields were added as of PostgreSQL 9.3, we'll skip testing for versions previous to that.
            if (Conn.PostgreSqlVersion < new Version("9.3"))
                Assert.Ignore("Postgres version is {0} (< 9.3))", Conn.PostgreSqlVersion);

            try
            {
                var command = new NpgsqlCommand(dropTable, Conn);
                command.ExecuteNonQuery();

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

                command = new NpgsqlCommand(insertStatement, Conn);
                //Cause the NOT NULL violation
                command.ExecuteNonQuery();

            }
            catch (NpgsqlException ex)
            {
                Assert.AreEqual("public", ex.SchemaName);
                Assert.AreEqual("notnullviolation", ex.TableName);
                Assert.AreEqual("id", ex.ColumnName);
            }
        }
Beispiel #25
0
        public void Append(string name, byte[] data, long expectedVersion)
        {
            using (var conn = new NpgsqlConnection(_connectionString))
            {
                conn.Open();
                using (var tx = conn.BeginTransaction())
                {
                    var version = MakeSureLastVersionMatches(name, expectedVersion, conn, tx);

                    const string txt =
                           @"INSERT INTO ES_Events2 (CustomerId, Name, Version, Data)
                                VALUES(:customerId, :name, :version, :data)";

                    using (var cmd = new NpgsqlCommand(txt, conn, tx))
                    {
                        cmd.Parameters.AddWithValue(":name", name);
                        cmd.Parameters.AddWithValue(":version", version+1);
                        cmd.Parameters.AddWithValue(":data", data);
                        cmd.Parameters.AddWithValue(":customerId", customerId);
                        cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
            }
        }
        public static void ProcessLinks(XmlReader reader, NpgsqlConnection connection)
        {
            var page_title = reader.GetAttribute("title");
            List<string> titles;
            XmlReader inner;
            inner = reader.ReadSubtree();
            titles = new List<string>();
            while (inner.ReadToFollowing("link")) titles.Add(inner.ReadElementContentAsString());

            if (titles.Count() == 0) return;

            // Now we have a list of titles construct a subquery to get the ID's
            // I could use this query, which looks nicer, but EXPLAIN says it costs a little bit more.
            // SELECT id FROM pages WHERE title IN ('Apple','ElementalAllotropes')
            // AND redirect IS NULL UNION SELECT p2.id FROM pages AS p1
            // JOIN pages AS p2 ON p1.title IN ('Apple','ElementalAllotropes') AND p2.title = p1.redirect AND p2.redirect IS NULL;
            var select_command_text = string.Format(PostgresSchema.LINK_ID_QUERY,
                                string.Join(",", (from val in Enumerable.Range(0, titles.Count)
                                                select string.Format(":title_{0}", val))));

            // Update our links to be the array of ID's using the subquery above
            var command = new NpgsqlCommand(string.Format("UPDATE pages SET links = ARRAY({0}) WHERE title = :title", select_command_text), connection);
            foreach (var val in Enumerable.Range(0, titles.Count)) command.Parameters.AddWithValue("title_"+val, titles[val]);
            command.Parameters.Add(new NpgsqlParameter("title", page_title));
            command.ExecuteNonQuery();
        }
        public void Create(IEnumerable<string> columnDefinitions)
        {
            var sb = new StringBuilder();

            if(!string.IsNullOrEmpty(_schemaName))
                sb.AppendFormat("CREATE SCHEMA \"{0}\";",_schemaName);

            sb.Append("CREATE TABLE ");

            sb.Append(NameWithSchema);
            sb.Append(" (");

            foreach (string definition in columnDefinitions)
            {
                sb.Append(definition);
                sb.Append(", ");
            }

            sb.Remove(sb.Length - 2, 2);
            sb.Append(")");

            var s = sb.ToString();
            using (var command = new NpgsqlCommand(s, Connection, Transaction))
                command.ExecuteNonQuery();
        }
 public int Salvarcliente(string nome, int codempresa)
 {
     using (var conn = new NpgsqlConnection(Connectionstring))
     {
         conn.Open();
         using (var trans  = conn.BeginTransaction())
         {
             try
             {
                 int id;
                 using (var cmd = new NpgsqlCommand("SELECT MIN(PORT_VNC) FROM PORTAS WHERE PORT_VNC NOT iN(SELECT CLIE_IDVNC FROM CLIENTE)", conn, trans))
                 {
                    id = int.Parse(cmd.ExecuteScalar().ToString());
                     
                 }
                 using (
                     var cmd =
                         new NpgsqlCommand(
                             "INSERT INTO cliente (clie_nome,clie_idvnc, empr_codigo) values ('" + nome + "'," + id + ","+codempresa+")", conn,
                             trans))
                 {
                     cmd.ExecuteNonQuery();
                     trans.Commit();
                     return id;
                 }
             }
             catch (Exception)
             {
                 trans.Rollback();
                 return 0;
             }
         }
     }
 }
Beispiel #29
0
        static void ClearCatalogue(string connString)
        {
            try
            {
                NpgsqlConnection conn = new NpgsqlConnection(connString);
                conn.Open();
                Console.Clear();

                string clearTable = "DELETE FROM phone_book";

                NpgsqlCommand cmd = new NpgsqlCommand(clearTable, conn);
                cmd.ExecuteNonQuery();

                Console.WriteLine(">>> Catalogue cleared");

                Console.ReadKey();
                Console.Clear();
                conn.Close();
            }
            catch (Exception msg)
            {
                Console.WriteLine(msg.ToString());
                throw;
            }
        }
 public void SalvarGrupo(GrupclienteDto entidade)
 {
     using (var conn = new NpgsqlConnection(Connectionstring))
     {
         conn.Open();
         using (var trans = conn.BeginTransaction())
         {
             try
             {
                 using (
                     var cmd =
                         new NpgsqlCommand(
                             "INSERT INTO grupcliente (grcl_nome, grcl_ativo,empr_codigo) values ('" + entidade.grcl_nome + "'," + entidade.grcl_ativo + ","+entidade.empr_codigo+")", conn,
                             trans))
                 {
                     cmd.ExecuteNonQuery();
                     trans.Commit();
                 }
             }
             catch (Exception)
             {
                 trans.Rollback();
             }
         }
     }
 }