public void Long([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior) { var builder = new StringBuilder("ABCDEééé", Conn.BufferSize); builder.Append('X', Conn.BufferSize); var expected = builder.ToString(); ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)"); var cmd = new NpgsqlCommand(@"INSERT INTO data (name) VALUES (@p)", Conn); cmd.Parameters.Add(new NpgsqlParameter("p", expected)); cmd.ExecuteNonQuery(); const string queryText = @"SELECT name, 'foo', name, name, name, name FROM data"; cmd = new NpgsqlCommand(queryText, Conn); var reader = cmd.ExecuteReader(behavior); reader.Read(); var actual = reader[0]; Assert.That(actual, Is.EqualTo(expected)); if (IsSequential(behavior)) Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential"); else Assert.That(reader[0], Is.EqualTo(expected)); Assert.That(reader.GetString(1), Is.EqualTo("foo")); Assert.That(reader.GetFieldValue<string>(2), Is.EqualTo(expected)); Assert.That(reader.GetValue(3), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<string>(4), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<char[]>(5), Is.EqualTo(expected.ToCharArray())); }
public void ExceptionFieldsArePopulated() { const string dropTable = @"DROP TABLE IF EXISTS public.uniqueviolation"; const string createTable = @"CREATE TABLE public.uniqueviolation (id INT NOT NULL, CONSTRAINT uniqueviolation_pkey PRIMARY KEY (id))"; const string insertStatement = @"INSERT INTO public.uniqueviolation (id) VALUES(1)"; // In this case we'll test a simple unique violation, we're not too interested in testing more // cases than this as the same code is executed in all error situations. try { var command = new NpgsqlCommand(dropTable, Conn); command.ExecuteNonQuery(); command = new NpgsqlCommand(createTable, Conn); command.ExecuteNonQuery(); command = new NpgsqlCommand(insertStatement, Conn); command.ExecuteNonQuery(); //Now cause the unique violation... command.ExecuteNonQuery(); } catch (NpgsqlException ex) { Assert.AreEqual("", ex.ColumnName); // Should not be populated for unique violations. Assert.AreEqual("uniqueviolation", ex.TableName); Assert.AreEqual("public", ex.SchemaName); Assert.AreEqual("uniqueviolation_pkey", ex.ConstraintName); Assert.AreEqual("", ex.DataTypeName); // Should not be populated for unique violations. } }
public void TestSubquery() { const string sql = @"SELECT testid FROM preparetest WHERE :p1 IN (SELECT varchar_notnull FROM preparetest)"; var cmd = new NpgsqlCommand(sql, TheConnection); var p1 = new NpgsqlParameter(":p1", DbType.String); p1.Value = "blahblah"; cmd.Parameters.Add(p1); cmd.ExecuteNonQuery(); // Succeeds cmd.Prepare(); // Fails cmd.ExecuteNonQuery(); }
/// <summary> /// Default constructor. /// </summary> /// <param name="intitString"></param> public pgsql_API(string intitString) { // connectionstring= string[] parameters = intitString.Replace("\r\n","\n").Split('\n'); foreach(string param in parameters){ if(param.ToLower().IndexOf("connectionstring=") > -1){ m_ConStr = param.Substring(17); } } SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(m_ConStr); string database = b.InitialCatalog; b.InitialCatalog = ""; using(NpgsqlConnection con = new NpgsqlConnection(b.ToString().ToLower().Replace("data source","server"))){ con.Open(); // See if database exists try{ con.ChangeDatabase(database); } catch{ // Database don't exist, try to create it try{ con.Close(); con.ConnectionString = b.ToString().ToLower().Replace("data source","server"); con.Open(); NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.CommandText = "create database \"" + database + "\""; cmd.ExecuteNonQuery(); con.ChangeDatabase(database); // Create tables cmd.CommandText = ResManager.GetText("tables.sql",System.Text.Encoding.Default); cmd.ExecuteNonQuery(); // Create procedures cmd.CommandText = ResManager.GetText("procedures.sql",System.Text.Encoding.Default); cmd.ExecuteNonQuery(); } catch{ throw new Exception("Database '" + database + "' doesn''t exist ! Create failed, specified user doesn't have enough permisssions to create database ! Create database manually."); } } } }
public void Alterar(Model_Vo_Agenda pAgenda) { // conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandType = CommandType.Text; cmd.CommandText = "update agenda set datahorareserva = @datahorareserva, idcliente = @idcliente, idsala = @idsala where id = @id;"; cmd.Parameters.AddWithValue("@id", pAgenda.Id); cmd.Parameters.AddWithValue("@datahorareserva", Dados.Model_Dao_Dados.ConverterDataToStr(pAgenda.DataHoraReserva, false)); cmd.Parameters.AddWithValue("@idcliente", pAgenda.IdCliente); cmd.Parameters.AddWithValue("@idsala", pAgenda.IdSala); cn.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation) { resman = new System.Resources.ResourceManager(this.GetType()); NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME); if ((isolation != IsolationLevel.ReadCommitted) && (isolation != IsolationLevel.Serializable)) throw new ArgumentOutOfRangeException(resman.GetString("Exception_UnsopportedIsolationLevel"), "isolation"); _conn = conn; _isolation = isolation; StringBuilder commandText = new StringBuilder("SET TRANSACTION ISOLATION LEVEL "); if (isolation == IsolationLevel.ReadCommitted) commandText.Append("READ COMMITTED"); else commandText.Append("SERIALIZABLE"); commandText.Append("; BEGIN"); NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector); command.ExecuteNonQuery(); _conn.Connector.Transaction = this; }
//När användaren kilckar på "OK" sker följande: private void OK_button_Click(object sender, EventArgs e) { //Deklarerar variabel klass var klass = ""; //Om "A" är iklickat av användaren får variabel klass värdet A. if (A_radioButton.Checked == true) { klass = "A"; } //Om "B" är iklickat av användaren får variabel klass värdet B. else if (B_radioButton.Checked == true) { klass = "B"; } //Om "C" är iklickat av användaren får variabel klass värdet C. else if (C_radioButton.Checked == true) { klass = "C"; } //Skapar strängen anmaldeltagare. //Strängen innehåller information om tävlingsdeltagare. Lägger in golfid, tävlingid och klass i databasen, i tabellen deltari. string anmaldeltagare = "insert into deltari (golfid, tavlingid, klass) values ('" + Golfid_textBox.Text + "'," + Tävlingid_textBox.Text + ",'" + klass + "')"; //Skapar ett nytt Npgsql-kommando, command1. NpgsqlCommand command1 = new NpgsqlCommand(anmaldeltagare, Huvudfönster.conn); //Utför kommando, command1. command1.ExecuteNonQuery(); //När allt ovan är utfört visas en meddelanderuta. MessageBox.Show("Spelare är nu anmäld till tävling!"); //Sedan stängs hela detta form, AnmälDeltagare. this.Close(); }
public static void CleanTables(NpgsqlConnection connection) { if (connection == null) throw new ArgumentNullException("connection"); var script = GetStringResource( typeof (PostgreSqlTestObjectsInitializer).Assembly, "Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", string.Format("'{0}'", ConnectionUtils.GetSchemaName())); //connection.Execute(script); using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable)) using (var command = new NpgsqlCommand(script, connection, transaction)) { command.CommandTimeout = 120; try { command.ExecuteNonQuery(); transaction.Commit(); } catch (NpgsqlException ex) { throw; } } }
public static bool ModificarUsuario(string nombre, string apellido, string direccion, string nombreusuario, string email) { try { NpgsqlCommand cmd = new NpgsqlCommand("update usuario set nombre = @nombre, apellido = @apellido, direccion = @direccion, email = @email where nombreusuario = @nombreusuario", Conexion.conexion); cmd.Parameters.Add("nombre", nombre); cmd.Parameters.Add("apellido", apellido); cmd.Parameters.Add("direccion", direccion); cmd.Parameters.Add("nombreusuario", nombreusuario); cmd.Parameters.Add("email", email); Conexion.abrirConexion(); if (cmd.ExecuteNonQuery() != -1) { Conexion.cerrarConexion(); return true; } else { Conexion.cerrarConexion(); return false; } } catch (Exception ex) { throw new Exception("Hubo un error con la base de datos, intente de nuevo más tarde"); } }
public void Alterar(Model_Vo_Produto pProduto) { // conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandType = CommandType.Text; cmd.CommandText = "update produto set descricao = @descricao, unidade = @unidade, estoque = @estoque, valordevenda = @valordevenda, observacao = @observacao where id = @id;"; cmd.Parameters.AddWithValue("@descricao", pProduto.Descricao); cmd.Parameters.AddWithValue("@unidade", pProduto.Unidade); cmd.Parameters.AddWithValue("@estoque", pProduto.Estoque); cmd.Parameters.AddWithValue("@valordevenda", pProduto.ValorDeVenda); cmd.Parameters.AddWithValue("@observacao", pProduto.Observacao); cmd.Parameters.AddWithValue("@id", pProduto.Id); cn.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
private void btnChange_Click(object sender, EventArgs e) { try { Postgres.ConnectionString = "Server=" + Properties.Settings.Default.Server + ";Port=" + Properties.Settings.Default.Port + ";User Id=" + Properties.Settings.Default.UserId + ";Password=" + Properties.Settings.Default.Password + ";Database=" + Properties.Settings.Default.Database + ";"; Postgres.Open(); var commandUpdateDescr = new NpgsqlCommand( "UPDATE \"GPL\".\"INFORMATION\" SET \"DESCRIPTION\" = '" + txtDescription.Text + "' WHERE \"ID\" = " + _descrId + ";", Postgres); commandUpdateDescr.ExecuteNonQuery(); MessageBox.Show(@"Изменения применены", @"Изменение инструкции", MessageBoxButtons.OK, MessageBoxIcon.Information); Postgres.Close(); Close(); } catch (Exception ex) { throw new Exception(@"Ошибка: ", ex); } }
public static bool CleanupOrphans() { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("UPDATE mp_pages "); sqlCommand.Append("SET parentid = -1, parentguid = '00000000-0000-0000-0000-000000000000' "); sqlCommand.Append("WHERE parentid <> -1 AND parentid NOT IN (SELECT pageid FROM mp_pages ) "); sqlCommand.Append(""); int rowsAffected = 0; // using scopes the connection and will close it /destroy it when it goes out of scope using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString.GetWriteConnectionString())) { conn.Open(); using (NpgsqlCommand command = new NpgsqlCommand(sqlCommand.ToString(), conn)) { //command.Parameters.Add(new NpgsqlParameter("pageguid", DbType.StringFixedLength, 36)); command.Prepare(); //command.Parameters[0].Value = pageGuid.ToString(); rowsAffected = command.ExecuteNonQuery(); } } return (rowsAffected > 0); }
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation) { resman = new System.Resources.ResourceManager(this.GetType()); NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME); _conn = conn; _isolation = isolation; StringBuilder commandText = new StringBuilder("BEGIN; SET TRANSACTION ISOLATION LEVEL "); if ( (isolation == IsolationLevel.RepeatableRead) || (isolation == IsolationLevel.Serializable) ) commandText.Append("SERIALIZABLE"); else { // Set isolation level default to read committed. _isolation = IsolationLevel.ReadCommitted; commandText.Append("READ COMMITTED"); } commandText.Append(";"); NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector); command.ExecuteNonQuery(); _conn.Connector.Transaction = this; }
private void insertIntoBase() { try { short total = Convert.ToInt16( this.textBox1.Text.Trim()); int s_sum = Convert.ToInt32(this.textBox2.Text.Trim()); short d_sum = Convert.ToInt16(this.textBox3.Text.Trim()); short measure = Convert.ToInt16(this.textBox4.Text.Trim()); int _s = Convert.ToInt32(this.textBox5.Text.Trim()); short _d = Convert.ToInt16(this.textBox6.Text.Trim()); string _comm = this.richTextBox1.Text.Trim(); NpgsqlCommand _command = new NpgsqlCommand("Insert into tumor_size (total, d_sum, s_sum, s, d, measurable, pat_id, rasp_data, got_comment, brain, kidney, skin, bones, bone_marrow, lung, lymphatic, adrenal, os, hepar, lien, other, doc_id) values " + "( '" + total + "','" + d_sum + "','" + s_sum + "', '" + _s + "','" + _d + "','" + measure + "','" + this.searchPatientBox1.pIdN + "','" + this.dateTimePicker1.Value + "', :descr ,'" + this.checkedListBox1.GetItemChecked(0) + "','" + this.checkedListBox1.GetItemChecked(1) + "','" + this.checkedListBox1.GetItemChecked(2) + "','" + this.checkedListBox1.GetItemChecked(3) + "','" + this.checkedListBox1.GetItemChecked(4) + "','" + this.checkedListBox1.GetItemChecked(5) + "','" + this.checkedListBox1.GetItemChecked(6) + "','" + this.checkedListBox1.GetItemChecked(7) + "','" + this.checkedListBox1.GetItemChecked(8) + "','" + this.checkedListBox1.GetItemChecked(9) + "','" + this.checkedListBox1.GetItemChecked(10) + "','" + this.checkedListBox1.GetItemChecked(11) + "' , '" + DBExchange.Inst.dbUsrId + "') ;", DBExchange.Inst.connectDb); using (_command) { _command.Parameters.Add(new NpgsqlParameter("descr", NpgsqlDbType.Text)); _command.Parameters[0].Value = _comm; } _command.ExecuteNonQuery(); clearForm(); } catch (Exception exception) { Warnings.WarnLog log = new Warnings.WarnLog(); log.writeLog(MethodBase.GetCurrentMethod().Name, exception.Message.ToString(), exception.StackTrace.ToString()); } }
void mainit_paroli() { switch (db.liet_grupa) { case "Mācībspēks": mainas_kom = new NpgsqlCommand("UPDATE macibspeki SET parole = @parole WHERE id_macsp = @lietotajs;", db.sav); break; case "Vadība": mainas_kom = new NpgsqlCommand("UPDATE vadiba SET parole = @parole WHERE id_vadiba = @lietotajs;", db.sav); break; case "Students": mainas_kom = new NpgsqlCommand("UPDATE studenti SET parole = @parole WHERE id_matr = @lietotajs;", db.sav); break; } mainas_kom.Parameters.AddWithValue("@lietotajs", db.lietotajs); mainas_kom.Parameters.AddWithValue("@parole", db.SHA1Parole(db.lietotajs, tb_jauna.Text)); db.sav.Open(); int ieraksti = mainas_kom.ExecuteNonQuery(); db.sav.Close(); if (ieraksti != -1) { MessageBox.Show("Lietotāja parole veiksmīgi nomainīta!", "Parole nomainīta", MessageBoxButtons.OK, MessageBoxIcon.Information); DialogResult = DialogResult.OK; Close(); } else { MessageBox.Show("Paroles maiņas procesā radās neparedzēta kļūda!\nMēģiniet atkārtot paroles maiņu vēlāk.", "Kļūda paroles maiņas procesā", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
//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 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 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)); }
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("Поле Названия - пусто!"); } }
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 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(); }
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(); } } } } } }
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 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; } } } }
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(); } } } }