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."); } } } }
//Remoção por id. public void Remove(int id) { try { bd.OpenConnection(); String query = "DELETE FROM tab_categoria WHERE cod_categoria = :id"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Prepare(); sql.Parameters[0].Value = id; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Categoria removida com sucesso!"); } else { Console.WriteLine("Não existe essa categoria!"); } } catch (NpgsqlException e) { Console.WriteLine(e); } finally { bd.CloseConnection(); } }
//Insert no banco recebendo como parametro uma Categoria. public void Add(Categoria c) { try { bd.OpenConnection(); String query = "INSERT INTO tab_categoria VALUES (:cod_categoria, :desc_categoria)"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("cod_categoria", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Parameters.Add(new NpgsqlParameter("desc_categoria", NpgsqlTypes.NpgsqlDbType.Varchar)); sql.Prepare(); sql.Parameters[0].Value = c.Cod_categoria; sql.Parameters[1].Value = c.Desc_categoria; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Categoria adicionada com sucesso!"); } } catch (NpgsqlException ex) { Console.WriteLine(ex); } finally { bd.CloseConnection(); } }
public void Alterar(Model_Vo_LivroCaixa pLivroCaixa) { // 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 livrocaixa set datahora = @datahora, descricao = @descricao, idcontasareceber = @idcontasareceber, tipodemovimento = @tipodemovimento, valor = @valor where id = @id;"; cmd.Parameters.AddWithValue("@id", pLivroCaixa.Id); cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr(pLivroCaixa.DataHora, false)); cmd.Parameters.AddWithValue("@descricao", pLivroCaixa.Descricao); cmd.Parameters.AddWithValue("@idcontasareceber", pLivroCaixa.IdContasAReceber); cmd.Parameters.AddWithValue("@tipodemovimento", pLivroCaixa.TipoDeMovimento.ToString()); cmd.Parameters.AddWithValue("@valor", pLivroCaixa.Valor); 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(); } }
public void create_table(dbtable t) { StringBuilder cmd = new StringBuilder("create table " + t.name + "("); for (int i = 0; i < t.fields.Length; i++) { if (i != 0) { cmd.Append(','); } cmd.Append(t.fields [i].name + " "); string type_name; if (t.fields [i].type == dbfield.types.str) { if (t.fields [i].len <= 0) { type_name = "text"; } else { type_name = "varchar(" + t.fields [i].len.ToString() + ")"; } } else { type_name = type_names [(int)t.fields [i].type]; } cmd.Append(type_name); } Npgsql.NpgsqlCommand c = new Npgsql.NpgsqlCommand(cmd.ToString(), con); c.ExecuteNonQuery(); table_mapping [t.id] = new table_entry(t); }
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 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 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(); } }
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(); }
//Adição na tabela associativa, recebendo o ID do filme e do diretor respectivamente. public void Add(int id_filme, int id_diretor) { try { bd.OpenConnection(); String query = "INSERT INTO tab_filme_diretor VALUES (:cod_filme, :cod_diretor)"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("cod_filme", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Prepare(); sql.Parameters[0].Value = id_filme; sql.Parameters[1].Value = id_diretor; int linhasAfetadas = sql.ExecuteNonQuery(); } catch (NpgsqlException ex) { Console.WriteLine(ex); } finally { bd.CloseConnection(); } }
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; }
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(); } }
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="******";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); } }
//Atualização passando como paramentro o ID e o novo atributo do Diretor. public void Update(int id, string new_nome_diretor) { try { bd.OpenConnection(); String query = "UPDATE tab_diretor SET nome_diretor = :new_nome_diretor WHERE cod_diretor = :id"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("new_nome_diretor", NpgsqlTypes.NpgsqlDbType.Varchar)); sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Prepare(); sql.Parameters[0].Value = new_nome_diretor; sql.Parameters[1].Value = id; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Diretor atualizado com sucesso!"); } else { Console.WriteLine("Não existe esse diretor!"); } } catch (NpgsqlException e) { Console.WriteLine(e); } finally { bd.CloseConnection(); } }
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 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)); }
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(); } }
//Insert no banco recebendo como parametro um Filme. public void Add(Filme f) { try { bd.OpenConnection(); String query = "INSERT INTO tab_filme VALUES (:cod_filme, :nome_filme, :data_filme, :cod_categoria)"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("cod_filme", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Parameters.Add(new NpgsqlParameter("nome_filme", NpgsqlTypes.NpgsqlDbType.Varchar)); sql.Parameters.Add(new NpgsqlParameter("data_filme", NpgsqlTypes.NpgsqlDbType.Date)); sql.Parameters.Add(new NpgsqlParameter("cod_categoria", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Prepare(); sql.Parameters[0].Value = f.Cod_filme; sql.Parameters[1].Value = f.Nome_filme; sql.Parameters[2].Value = f.Data; sql.Parameters[3].Value = f.Categoria.Cod_categoria; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Filme adicionado com sucesso!"); } } catch (NpgsqlException ex) { Console.WriteLine(ex); } finally { bd.CloseConnection(); } }
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); }
//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(); }
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; }
public bool Update() { string sQuery = "UPDATE tbm_carrepair SET " + "[email protected],[email protected],[email protected],[email protected],luedit=now()" + "WHERE repairid = @rid"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); if (m_repairid != null) { cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = repairid; } else { cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (m_carid != null) { cmd.Parameters.Add("@cid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = carid; } else { cmd.Parameters.Add("@cid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (opedit != null) { cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = opedit; } else { cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (pcedit != null) { cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pcedit; } else { cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (luedit != null && luedit != DateTime.MinValue) { cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = luedit; } else { cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = DBNull.Value; } cmd.Parameters.Add("@dlt", NpgsqlTypes.NpgsqlDbType.Boolean).Value = dlt; cmd.CommandText = sQuery; try { cmd.ExecuteNonQuery(); return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } }
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(); } } } } } }
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()); } }
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(); } }
public void FecharContasAReceber(Model_Vo_ContasAReceber pContasAReceber) { // 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 contasareceber set recebido = @recebido where idreservaorigem = @idreservaorigem;"; cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem); cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido); 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(); } }
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); } }
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(); } }
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 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("Поле Названия - пусто!"); } }
//Insert no banco recebendo como parametro um Diretor. public void Add(Diretor d) { try { bd.OpenConnection(); String query = "INSERT INTO tab_diretor VALUES (:cod_diretor, :nome_diretor)"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Parameters.Add(new NpgsqlParameter("nome_diretor", NpgsqlTypes.NpgsqlDbType.Varchar)); sql.Prepare(); sql.Parameters[0].Value = d.Cod_diretor; sql.Parameters[1].Value = d.Nome_diretor; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Diretor adicionado com sucesso!"); } } catch (NpgsqlException ex) { Console.WriteLine(ex); } finally { bd.CloseConnection(); } }
public string AccountCreation(string json) { string status = String.Empty; try { using (NpgsqlConnection con = new NpgsqlConnection(_connectionString)) { con.Open(); var command = new Npgsql.NpgsqlCommand("_bt_account_create", con); command.CommandType = System.Data.CommandType.StoredProcedure; var parameter = command.CreateParameter(); parameter.ParameterName = "input"; parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json; parameter.Value = json; command.Parameters.Add(parameter); int output = command.ExecuteNonQuery(); status = "ok"; } } catch (Exception ex) { status = ex.Message; } return(status); }
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); } }
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(); } } } }
//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!"); }
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 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; } } } }
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); } }
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(); }
public Boolean Excluir(int id) { //conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "delete from produto where id = " + Convert.ToString(id); cn.Open(); int resultado = cmd.ExecuteNonQuery(); if (resultado != 1) { throw new Exception("Não foi possível excluir a produto " + Convert.ToString(id)); } } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } return(true); }
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 void Button1_Click(object sender, EventArgs e) { try { const string pgConstring = "Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=1234;"; NpgsqlConnection conx = new Npgsql.NpgsqlConnection(pgConstring); if (conx.State != ConnectionState.Open) { conx.Open(); } if (conx.State == ConnectionState.Open) { string cmd1 = string.Format("SELECT user_id, user_pass, firstname, lastname, user_name FROM public.user_login WHERE user_name ='{0}';" , t3.Text ); var rder = new Npgsql.NpgsqlDataAdapter(cmd1, conx); DataSet ds = new DataSet(); rder.Fill(ds); var usr = ConvertDataTable <user_login>(ds.Tables[0]); if (usr.Count <= 0 || usr == null) { if (t1.Text == "" || t2.Text == "" || t3.Text == "" || t4.Text == "" || t5.Text == "") { ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + "กรุณากรอกข้อมูลให้ครบ" + "');", true); } else { string text = string.Format("INSERT INTO user_login( user_pass, firstname, lastname, user_name,user_grade) VALUES ('{0}','{1}','{2}','{3}','{4}');" , t4.Text , t1.Text , t2.Text , t3.Text , t5.Text); NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(text, conx); cmd.ExecuteNonQuery(); Response.Redirect("register2.aspx"); } } else { ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + "มีuserนี้อยู่ในระบบอยู่แล้ว" + "');", true); } } } catch (Exception ex) { Response.Write(ex.Message); } }
public Boolean Excluir(int idMovimentacaoEstoque, int idAgenda) { //conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; if ((idMovimentacaoEstoque > 0)) { cmd.CommandText = "delete from movimentacaoestoque where id = " + Convert.ToString(idMovimentacaoEstoque); } else { cmd.CommandText = "delete from movimentacaoestoque where idreservaorigem = " + Convert.ToString(idAgenda); } cn.Open(); int resultado = cmd.ExecuteNonQuery(); if (resultado != 1) { if ((idAgenda > 0)) { throw new Exception("Não foi possível excluir a MovimentacaoEstoque da Agenda: " + Convert.ToString(idAgenda)); } else { throw new Exception("Não foi possível excluir a MovimentacaoEstoque " + Convert.ToString(idMovimentacaoEstoque)); } } } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } return(true); }
public void labeling(int code, string type) { cnx.Close(); cnx.Open(); alllabel = new LabelStyle() { CollisionDetection = true, CollisionBuffer = new SizeF(10F, 10F), Font = new Font(FontFamily.GenericSansSerif, 15), Halo = new Pen(Color.White, 2) }; if (type == "antenne") { cmd = new n.NpgsqlCommand("create or replace view labelantenne as select num,puissance,emp from antenne where num=" + code + ";", cnx); cmd.ExecuteNonQuery(); lbanteene = new LabelLayer("lb") { LabelColumn = "puissance", DataSource = new PostGIS(src, "labelantenne", "emp", "num") }; lbanteene.Style = alllabel; map1.Map.Layers.Add(lbanteene); map1.Refresh(); cmd = new n.NpgsqlCommand("drop view labelantenne;", cnx); cmd.ExecuteNonQuery(); } else { if (type == "quartier") { cmd = new n.NpgsqlCommand("create or replace view labelquartier as select code,nom,zone from quartier where code=" + code + ";", cnx); cmd.ExecuteNonQuery(); lbquartie = new LabelLayer("qrtt") { LabelColumn = "nom", DataSource = new PostGIS(src, "labelquartier", "zone", "code") }; lbquartie.Style = alllabel; map1.Map.Layers.Add(lbquartie); map1.Refresh(); cmd = new n.NpgsqlCommand("drop view labelquartier;", cnx); cmd.ExecuteNonQuery(); } } cnx.Close(); }
public void Alterar(Model_Vo_Cliente pCliente) { 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 cliente set nome = @nome, cpf = @cpf, rg = @rg, telefone = @telefone, email = @email, rua = @rua, numero = @numero, bairro = @bairro, complemento = @complemento, cidade = @cidade, cep = @cep, uf = @uf, " + @"Nascimento = @Nascimento, " + @"ValorMensalidade = @ValorMensalidade, " + @"Observacao = @Observacao, " + @"Ativo = @Ativo " + @"where id = @id;"); cmd.Parameters.AddWithValue("@id", pCliente.Id); cmd.Parameters.AddWithValue("@nome", pCliente.Nome); cmd.Parameters.AddWithValue("@cpf", pCliente.Cpf); cmd.Parameters.AddWithValue("@rg", pCliente.Rg); cmd.Parameters.AddWithValue("@telefone", pCliente.Telefone); cmd.Parameters.AddWithValue("@email", pCliente.Email); cmd.Parameters.AddWithValue("@rua", pCliente.Rua); cmd.Parameters.AddWithValue("@numero", pCliente.Numero); cmd.Parameters.AddWithValue("@bairro", pCliente.Bairro); cmd.Parameters.AddWithValue("@complemento", pCliente.Complemento); cmd.Parameters.AddWithValue("@cidade", pCliente.Cidade); cmd.Parameters.AddWithValue("@cep", pCliente.CEP); cmd.Parameters.AddWithValue("@uf", pCliente.UF); cmd.Parameters.AddWithValue("@Nascimento", pCliente.Nascimento); cmd.Parameters.AddWithValue("@ValorMensalidade", pCliente.ValorMensalidade); cmd.Parameters.AddWithValue("@Observacao", pCliente.Observacao); cmd.Parameters.AddWithValue("@Ativo", pCliente.Ativo); 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 static void ApplyPostgresMigration(Stream stream, bool force, DatabaseInfo dbInfo) { if (!force) { CheckForce(stream); } using (var conn = new Npgsql.NpgsqlConnection(dbInfo.ConnectionString)) { conn.Open(); var com = new Npgsql.NpgsqlCommand(stream); com.Connection = conn; com.ExecuteNonQuery(); conn.Close(); } }
public void Incluir(Model_Vo_Cliente pCliente) { SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = (@"insert into cliente(nome,cpf,rg,telefone,email,rua,numero,bairro," + @"complemento,cidade,cep,uf, Nascimento, ValorMensalidade, Observacao, Ativo) " + @"values " + @" (@nome,@cpf,@rg,@telefone,@email,@rua,@numero,@bairro," + @" @complemento,@cidade,@cep,@uf, @Nascimento, @ValorMensalidade, @Observacao, @Ativo);"); cmd.Parameters.AddWithValue("@nome", pCliente.Nome); cmd.Parameters.AddWithValue("@cpf", pCliente.Cpf); cmd.Parameters.AddWithValue("@rg", pCliente.Rg); cmd.Parameters.AddWithValue("@telefone", pCliente.Telefone); cmd.Parameters.AddWithValue("@email", pCliente.Email); cmd.Parameters.AddWithValue("@rua", pCliente.Rua); cmd.Parameters.AddWithValue("@numero", pCliente.Numero); cmd.Parameters.AddWithValue("@bairro", pCliente.Bairro); cmd.Parameters.AddWithValue("@complemento", pCliente.Complemento); cmd.Parameters.AddWithValue("@cidade", pCliente.Cidade); cmd.Parameters.AddWithValue("@cep", pCliente.CEP); cmd.Parameters.AddWithValue("@uf", pCliente.UF); cmd.Parameters.AddWithValue("@Nascimento", pCliente.Nascimento); cmd.Parameters.AddWithValue("@ValorMensalidade", pCliente.ValorMensalidade); cmd.Parameters.AddWithValue("@Observacao", pCliente.Observacao); cmd.Parameters.AddWithValue("@Ativo", pCliente.Ativo); 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(); } }
/// <summary> /// 执行sql update,insert等语句,返回影响行数 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int ExecuteNonQuery(string sql) { int rowsaffected = -1; try { using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, conn)) { rowsaffected = command.ExecuteNonQuery(); } Console.WriteLine("影响行数:{0}", rowsaffected); } catch (Exception ex) { Console.WriteLine("插入数据库失败" + ex.Message); } return(rowsaffected); }
private void button5_Click(object sender, EventArgs e) { string strconn = string.Format("Server={0};Port={1};User ID={2};Password={3};Database={4}", textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, textBox5.Text); NpgsqlConnection cnn = new NpgsqlConnection(strconn); cnn.Open(); Npgsql.NpgsqlCommand myCommand = cnn.CreateCommand(); myCommand.CommandText = "Update udal set p2='Деточкин' WHERE P1=1;"; try { myCommand.ExecuteNonQuery(); MessageBox.Show("Выполнено!", "Информация", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information); } finally { cnn.Dispose(); } }
public bool Delete() { string sQuery = " DELETE FROM tbm_carrepair WHERE [email protected]"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = repairid; cmd.CommandText = sQuery; try { cmd.ExecuteNonQuery(); return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } }
public void quartier_traitement(int code) { cnx.Close(); cnx.Open(); n.NpgsqlCommand cm = new n.NpgsqlCommand("create or replace view buffer_all as select num,ST_buffer(emp,puissance)as bf from antenne;", cnx); cm.ExecuteNonQuery(); n.NpgsqlCommand cma = new n.NpgsqlCommand("create or replace view quart as select st_intersection(quartier.zone,bf)as inter from quartier,buffer_all where st_intersects(quartier.zone,bf) and quartier.code=" + code + "", cnx); cma.ExecuteNonQuery(); n.NpgsqlCommand sauvegarde = new n.NpgsqlCommand("CREATE OR REPLACE VIEW cell as select ST_UNION(inter) as geom from quart", cnx); sauvegarde.ExecuteNonQuery(); n.NpgsqlCommand c = new n.NpgsqlCommand(" select ST_AREA(geom) as valeur from cell", cnx); double i = 0; var rea = c.ExecuteReader(); while (rea.Read()) { i = Double.Parse(rea["valeur"].ToString()); } n.NpgsqlCommand ca = new n.NpgsqlCommand(" select ST_AREA(zone) as valeur from quartier where code=" + code + "", cnx); double ia = 0; var re = ca.ExecuteReader(); while (re.Read()) { ia = Double.Parse(re["valeur"].ToString()); } tc.Text = ((i / ia) * 100) + "%"; supc.Text = i + ""; supnc.Text = (ia - i) + ""; n.NpgsqlCommand de = new n.NpgsqlCommand(" select densite as valeur from quartier where code=" + code + "", cnx); double iaa = 0; var e = de.ExecuteReader(); while (e.Read()) { iaa = Double.Parse(e["valeur"].ToString()); } pc.Text = (iaa * i) + ""; pnc.Text = ((ia - i) * iaa) + ""; }
public async Task Update(Registration entity, DateTimeOffset timeStamp) { using (var sqlConnection = new NpgsqlConnection(ConnectionString)) { sqlConnection.Open(); using (var transaction = sqlConnection.BeginTransaction()) { try { foreach (var entityEvent in entity.NewEvents) { var command = "INSERT INTO schooled.Registration (id, version, event_type, event, timestamp) VALUES (@id, @version, @event_type, @event, @timestamp)"; using (var sqlCommand = new Npgsql.NpgsqlCommand(command, sqlConnection)) { sqlCommand.Parameters.AddWithValue("id", NpgsqlDbType.Uuid, entity.Id); sqlCommand.Parameters.AddWithValue("version", NpgsqlDbType.Integer, entityEvent.Version); sqlCommand.Parameters.AddWithValue("event_type", NpgsqlDbType.Varchar, entityEvent.GetType()); sqlCommand.Parameters.AddWithValue("event", NpgsqlDbType.Jsonb, JsonConvert.SerializeObject(entityEvent)); sqlCommand.Parameters.AddWithValue("timestamp", NpgsqlDbType.TimestampTZ, timeStamp); sqlCommand.ExecuteNonQuery(); } } await transaction.CommitAsync(); } catch (Exception e) { Console.WriteLine(e); await transaction.RollbackAsync(); throw; } } } }
public Boolean Excluir(int id, int piCtaReceb) { //conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; if ((piCtaReceb > 0)) { cmd.CommandText = "delete from livrocaixa where idcontasareceber = " + Convert.ToString(piCtaReceb); } else { cmd.CommandText = "delete from livrocaixa where id = " + Convert.ToString(id); } cn.Open(); int resultado = cmd.ExecuteNonQuery(); if (resultado != 1) { //throw new Exception("Não foi possível excluir a LivroCaixa " + Convert.ToString(id)); } } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } return(true); }
public bool SoftDelete() { string sQuery = "Update tbm_carrepair set dlt=true,[email protected],[email protected],luedit=now()" + "where [email protected]"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = repairid; cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = clsGlobal.strUserName; cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = System.Windows.Forms.SystemInformation.ComputerName; try { cmd.ExecuteNonQuery(); return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } }
private void button2_Click(object sender, EventArgs e) { alabel.Text = ""; c.Text = ""; tc.Text = ""; supc.Text = ""; supnc.Text = ""; pc.Text = ""; pnc.Text = ""; c.Text = Text; alabel.Text = ""; Nantenne.Text = ""; puissance.Text = ""; s.Text = ""; densite.Text = ""; nbrZone.Text = ""; NC.Text = ""; NCC.Text = ""; cnx.Close(); cnx.Open(); n.NpgsqlCommand caa = new n.NpgsqlCommand(" Create or replace view zonica as select ST_Difference(ST_UNION(quartier.zone),ST_UNION(st_buffer(antenne.emp,puissance))) as valeur from antenne,quartier;", cnx); caa.ExecuteNonQuery(); VectorLayer nc = new VectorLayer("") { DataSource = new PostGIS(src, "zonica", "valeur", null) }; nc.Style.Fill = Brushes.Red; nc.Style.Outline = Pens.White; nc.Style.EnableOutline = true; map1.Map.Layers.Clear(); map1.Map.Layers.Add(quart); map1.Map.Layers.Add(ant); map1.Map.Layers.Add(nc); map1.Map.ZoomToExtents(); map1.Refresh(); cnx.Close(); c.Text = ""; }
//Remoção por id. public void Remove(int id) { try { FilmeDiretorDAO fddao = new FilmeDiretorDAO(); //Método responsavel por remover todas as associções do diretor com qualquer filme //fazendo com que antes de remover o diretor, remova suas //dependências para evitar constraints errors. fddao.removerPorDiretor(id); bd.OpenConnection(); String query = "DELETE FROM tab_diretor WHERE cod_diretor = :id"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Prepare(); sql.Parameters[0].Value = id; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Diretor removido com sucesso!"); } else { Console.WriteLine("Não existe esse Diretor!"); } } catch (NpgsqlException e) { Console.WriteLine(e); } finally { bd.CloseConnection(); } }