public void MultipleCommands(bool[] queries) { using (var conn = OpenConnection()) { conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)"); var sb = new StringBuilder(); foreach (var query in queries) sb.Append(query ? "SELECT 1;" : "UPDATE data SET name='yo' WHERE 1=0;"); var sql = sb.ToString(); foreach (var prepare in new[] {false, true}) { using (var cmd = new NpgsqlCommand(sql, conn)) { if (prepare) cmd.Prepare(); using (var reader = cmd.ExecuteReader()) { var numResultSets = queries.Count(q => q); for (var i = 0; i < numResultSets; i++) { Assert.That(reader.Read(), Is.True); Assert.That(reader[0], Is.EqualTo(1)); Assert.That(reader.NextResult(), Is.EqualTo(i != numResultSets - 1)); } } } } } }
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); }
public void SchemaOnly([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare) { using (var conn = OpenConnection()) { conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)"); using (var cmd = new NpgsqlCommand( "SELECT 1 AS some_column;" + "UPDATE data SET name='yo' WHERE 1=0;" + "SELECT 1 AS some_other_column", conn)) { if (prepare == PrepareOrNot.Prepared) cmd.Prepare(); using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)) { Assert.That(reader.Read(), Is.False); var t = reader.GetSchemaTable(); Assert.That(t.Rows[0]["ColumnName"], Is.EqualTo("some_column")); Assert.That(reader.NextResult(), Is.True); Assert.That(reader.Read(), Is.False); t = reader.GetSchemaTable(); Assert.That(t.Rows[0]["ColumnName"], Is.EqualTo("some_other_column")); Assert.That(reader.NextResult(), Is.False); } // Close reader in the middle using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)) reader.Read(); } } }
public void Rollback([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare) { var tx = Conn.BeginTransaction(); var cmd = new NpgsqlCommand("INSERT INTO data (field_text) VALUES ('X')", Conn, tx); if (prepare == PrepareOrNot.Prepared) { cmd.Prepare(); } cmd.ExecuteNonQuery(); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM data"), Is.EqualTo(1)); tx.Rollback(); Assert.That(tx.Connection, Is.Null); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM data"), Is.EqualTo(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(); }
public void RawBinaryRoundtrip() { using (var conn = OpenConnection()) { conn.ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)"); //var iterations = Conn.BufferSize / 10 + 100; //var iterations = Conn.BufferSize / 10 - 100; var iterations = 500; // Preload some data into the table using (var cmd = new NpgsqlCommand("INSERT INTO data (field_text, field_int4) VALUES (@p1, @p2)", conn)) { cmd.Parameters.AddWithValue("p1", NpgsqlDbType.Text, "HELLO"); cmd.Parameters.AddWithValue("p2", NpgsqlDbType.Integer, 8); cmd.Prepare(); for (var i = 0; i < iterations; i++) { cmd.ExecuteNonQuery(); } } var data = new byte[10000]; int len = 0; using (var outStream = conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) TO STDIN BINARY")) { StateAssertions(conn); while (true) { var read = outStream.Read(data, len, data.Length - len); if (read == 0) break; len += read; } Assert.That(len, Is.GreaterThan(conn.BufferSize) & Is.LessThan(data.Length)); } conn.ExecuteNonQuery("TRUNCATE data"); using (var outStream = conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) FROM STDIN BINARY")) { StateAssertions(conn); outStream.Write(data, 0, len); } Assert.That(conn.ExecuteScalar("SELECT COUNT(*) FROM DATA"), Is.EqualTo(iterations)); } }
private void anm_button_Click(object sender, EventArgs e) { string sql = "INSERT INTO \"tävling_medlem\" (tävling_id, golf_id) VALUES (:tävling_id, :golf_id);"; NpgsqlCommand command = new NpgsqlCommand(sql, GolfReception.conn); command.Parameters.Add(new NpgsqlParameter("tävling_id", DbType.Int32)); command.Parameters.Add(new NpgsqlParameter("golf_id", DbType.String)); command.Prepare(); command.Parameters[0].Value = tävling_id; command.Parameters[1].Value = findPlayerComponent1.GolfId; command.ExecuteNonQuery(); this.Close(); }
public string getData(string latitude, string longitude) { //string latitude = latlon.Split(',')[0]; //string longitude = latlon.Split(',')[1]; string connstring = ConfigurationManager.ConnectionStrings["gisdb"].ConnectionString; NpgsqlConnection conn = new NpgsqlConnection(connstring); NpgsqlTransaction t = null; DataSet dsStops = new DataSet(); try { conn.Open(); t = conn.BeginTransaction(); //select * from nearest_stops_detail('51.4027','-0.2653'); NpgsqlCommand command = new NpgsqlCommand("select * from nearest_stops_detail(:latitude,:longitude)", conn); // Now add the parameter to the parameter collection of the command specifying its type. command.Parameters.Add(new NpgsqlParameter("latitude", NpgsqlTypes.NpgsqlDbType.Text)); command.Parameters.Add(new NpgsqlParameter("longitude", NpgsqlTypes.NpgsqlDbType.Text)); command.Prepare(); command.Parameters[0].Value = latitude; command.Parameters[1].Value = longitude; command.CommandType = CommandType.StoredProcedure; NpgsqlDataAdapter da = new NpgsqlDataAdapter(command); da.Fill(dsStops); } finally { t.Commit(); conn.Close(); } StringWriter sw = new StringWriter(); XmlWriter xw = XmlWriter.Create(sw); dsStops.WriteXml(xw); return sw.ToString(); }
/// <summary> /// /// </summary> /// <param name="newrow"></param> public void DELETE(Model newrow) { NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1; Database=Assignment2.Proj2; IntegratedSecurity=true;"); //NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=astronauta;Database=Assignment2.Proj2;"); conn.Open(); NpgsqlCommand command = new NpgsqlCommand("DELETE FROM animals WHERE animal_id = :animal_id ", conn); command.Parameters.Add(new NpgsqlParameter("animal_id", NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = newrow.id; command.ExecuteNonQuery(); }
public void Rollback([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare) { using (var conn = OpenConnection()) { conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)"); var tx = conn.BeginTransaction(); var cmd = new NpgsqlCommand("INSERT INTO data (name) VALUES ('X')", conn, tx); if (prepare == PrepareOrNot.Prepared) cmd.Prepare(); cmd.ExecuteNonQuery(); Assert.That(conn.ExecuteScalar("SELECT COUNT(*) FROM data"), Is.EqualTo(1)); tx.Rollback(); Assert.That(tx.Connection, Is.Null); Assert.That(conn.ExecuteScalar("SELECT COUNT(*) FROM data"), Is.EqualTo(0)); } }
/// <summary> /// /// </summary> /// <param name="newrow"></param> public void CREATE(Model newrow) { NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1; Database=Assignment2.Proj2; IntegratedSecurity=true;"); // NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=astronauta;Database=Assignment2.Proj2;"); conn.Open(); NpgsqlCommand command = new NpgsqlCommand("INSERT INTO animals(breed,weight1,age) VALUES (:breed,:weight1,:age) ", conn); command.Parameters.Add(new NpgsqlParameter("breed", NpgsqlDbType.Text)); command.Parameters.Add(new NpgsqlParameter("weight1", NpgsqlDbType.Double)); command.Parameters.Add(new NpgsqlParameter("age", NpgsqlDbType.Double)); command.Prepare(); command.Parameters[0].Value = newrow.breed; command.Parameters[1].Value = newrow.weight; command.Parameters[2].Value = newrow.age; command.ExecuteNonQuery(); }
/// <summary> /// /// </summary> /// <param name="newrow"></param> public void UPDATE(Model newrow) { NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1; Database=Assignment2.Proj2; IntegratedSecurity=true;"); //NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=astronauta;Database=Assignment2.Proj2;"); conn.Open(); NpgsqlCommand command = new NpgsqlCommand("UPDATE animals SET breed = :breed, weight1 = :weight1, age = :age WHERE animal_id = :animal_id ", conn); command.Parameters.Add(new NpgsqlParameter("breed", NpgsqlDbType.Text)); command.Parameters.Add(new NpgsqlParameter("weight1", NpgsqlDbType.Double)); command.Parameters.Add(new NpgsqlParameter("age", NpgsqlDbType.Double)); command.Parameters.Add(new NpgsqlParameter("animal_id", NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = newrow.breed; command.Parameters[1].Value = newrow.weight; command.Parameters[2].Value = newrow.age; command.Parameters[3].Value = newrow.id; command.ExecuteNonQuery(); }
public void RawBinaryRoundtrip() { //var iterations = Conn.BufferSize / 10 + 100; //var iterations = Conn.BufferSize / 10 - 100; var iterations = 500; // Preload some data into the table using (var cmd = new NpgsqlCommand("INSERT INTO data (field_text, field_int4) VALUES (@p1, @p2)", Conn)) { cmd.Parameters.AddWithValue("p1", NpgsqlDbType.Text, "HELLO"); cmd.Parameters.AddWithValue("p2", NpgsqlDbType.Integer, 8); cmd.Prepare(); for (var i = 0; i < iterations; i++) { cmd.ExecuteNonQuery(); } } var data = new byte[10000]; int len; using (var inStream = Conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) TO STDIN BINARY")) { StateAssertions(); len = inStream.Read(data, 0, data.Length); Assert.That(len, Is.GreaterThan(Conn.BufferSize) & Is.LessThan(data.Length)); Console.WriteLine("Exported binary dump, length=" + len); } ExecuteNonQuery("TRUNCATE data"); using (var outStream = Conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) FROM STDIN BINARY")) { StateAssertions(); outStream.Write(data, 0, len); } Assert.That(ExecuteScalar("SELECT COUNT(*) FROM DATA"), Is.EqualTo(iterations)); }
public void deleteAusgang(Ausgang r) { buildconnection(); NpgsqlCommand comm = null; try { string sql = "Delete from rechnungen_buchungen where rechnungid = @rechnungid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@rechnungid", r.Rechnungid); comm.Prepare(); comm.ExecuteNonQuery(); sql = "Delete from rechnungszeilen where rechnungid = @rechnungid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@rechnungid", r.Rechnungid); comm.Prepare(); comm.ExecuteNonQuery(); sql = "Delete from ausgang where rechnungid = @rechnungid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@rechnungid", r.Rechnungid); comm.Prepare(); comm.ExecuteNonQuery(); sql = "Delete from rechnungen where rechnungid = @rechnungid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@rechnungid", r.Rechnungid); comm.Prepare(); comm.ExecuteNonQuery(); r.Status = ObjectStates.Deleted; } catch (NpgsqlException exp) { throw new DALException("DAL: Rechnung konnte nicht gelöscht werden!", exp); } finally { comm.Dispose(); conn.Close(); } }
public void deleteAngebot(Angebot a) { buildconnection(); NpgsqlCommand comm = null; try { string sql = "Delete from angebote where angebotid = @angebotid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@angebotid", a.Angebotid); comm.Prepare(); comm.ExecuteNonQuery(); a.Status = ObjectStates.Deleted; } catch (NpgsqlException exp) { throw new DALException("DAL: Angebot konnte nicht gelöscht werden!", exp); } finally { comm.Dispose(); conn.Close(); } }
private void book_button_Click(object sender, EventArgs e) { String sql = "SELECT 1 FROM bokning WHERE datumtid = :date;"; NpgsqlCommand command = new NpgsqlCommand(sql, GolfReception.conn); command.Parameters.Add(new NpgsqlParameter("date", DbType.DateTime)); command.Prepare(); command.Parameters[0].Value = datumtid; NpgsqlDataReader ndr = command.ExecuteReader(); bool update = ndr.Read(); if (update) { sql = "UPDATE bokning SET (notering) = (:notering) WHERE \"datumtid\" = '" + datumtid.ToString(CultureInfo.CreateSpecificCulture("sv-SE")) + "' ;"; } else { sql = "INSERT INTO bokning (notering, datumtid) VALUES (:notering, :date);"; } ndr.Close(); command = new NpgsqlCommand(sql, GolfReception.conn); command.Parameters.Add(new NpgsqlParameter("notering", DbType.String)); if (!update) { command.Parameters.Add(new NpgsqlParameter("date", DbType.DateTime)); } command.Prepare(); command.Parameters[0].Value = note_textBox.Text; if (!update) { command.Parameters[1].Value = datumtid; } command.ExecuteNonQuery(); this.Close(); }
public void MultipleQueriesWithParameters([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare) { var cmd = new NpgsqlCommand("SELECT @p1; SELECT @p2", Conn); var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Integer); var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Text); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); if (prepare == PrepareOrNot.Prepared) { cmd.Prepare(); } p1.Value = 8; p2.Value = "foo"; var reader = cmd.ExecuteReader(); Assert.That(reader.Read(), Is.True); Assert.That(reader.GetInt32(0), Is.EqualTo(8)); Assert.That(reader.NextResult(), Is.True); Assert.That(reader.Read(), Is.True); Assert.That(reader.GetString(0), Is.EqualTo("foo")); Assert.That(reader.NextResult(), Is.False); reader.Close(); cmd.Dispose(); }
public void PrepareRequiresParamTypesSet() { using (var cmd = new NpgsqlCommand("SELECT @p", Conn)) { var p = new NpgsqlParameter("p", 8); cmd.Parameters.Add(p); Assert.That(() => cmd.Prepare(), Throws.InvalidOperationException); } }
public void TestErrorInPreparedStatementCausesReleaseConnectionToThrowException() { // This is caused by having an error with the prepared statement and later, Npgsql is trying to release the plan as it was successful created. var cmd = new NpgsqlCommand("sele", Conn); cmd.Prepare(); }
public void DoublePrepare() { ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT, int INTEGER)"); var cmd = new NpgsqlCommand("INSERT INTO data (name) VALUES (:p0)", Conn); cmd.Parameters.Add(new NpgsqlParameter("p0", NpgsqlDbType.Text)); cmd.Parameters["p0"].Value = "test"; cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO data (int) VALUES (:p0)"; cmd.Parameters.Clear(); cmd.Parameters.Add(new NpgsqlParameter("p0", NpgsqlDbType.Integer)); cmd.Parameters["p0"].Value = 8; cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.Dispose(); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(0), "Prepared statements are being leaked"); }
public void PreparedStatementWithParameters() { ExecuteNonQuery("CREATE TEMP TABLE data (int INTEGER, long BIGINT)"); var command = new NpgsqlCommand("select * from data where int = :a and long = :b;", Conn); command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32)); command.Parameters.Add(new NpgsqlParameter("b", DbType.Int64)); Assert.AreEqual(2, command.Parameters.Count); Assert.AreEqual(DbType.Int32, command.Parameters[0].DbType); command.Prepare(); command.Parameters[0].Value = 3; command.Parameters[1].Value = 5; var dr = command.ExecuteReader(); Assert.IsNotNull(dr); dr.Close(); }
public void Prepare() { ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)"); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(0)); using (var cmd = new NpgsqlCommand("INSERT INTO data (name) VALUES (:p0);", Conn)) { cmd.Parameters.Add(new NpgsqlParameter("p0", NpgsqlDbType.Text)); cmd.Prepare(); cmd.Parameters["p0"].Value = "test"; using (var dr = cmd.ExecuteReader()) { Assert.IsNotNull(dr); dr.Close(); Assert.That(dr.RecordsAffected, Is.EqualTo(1)); } Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(1)); } Assert.That(ExecuteScalar("SELECT COUNT(*) FROM data WHERE name = 'test'"), Is.EqualTo(1)); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(0), "Prepared statements are being leaked"); }
public void MultipleQueriesSingleRow([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare) { var cmd = new NpgsqlCommand("SELECT 1; SELECT 2", Conn); if (prepare == PrepareOrNot.Prepared) cmd.Prepare(); var reader = cmd.ExecuteReader(CommandBehavior.SingleRow); Assert.That(reader.Read(), Is.True); Assert.That(reader.GetInt32(0), Is.EqualTo(1)); Assert.That(reader.Read(), Is.False); Assert.That(reader.NextResult(), Is.False); reader.Close(); cmd.Dispose(); }
public void PreparedStatementNoParameters() { _conn.Open(); NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn); command.Prepare(); command.Prepare(); NpgsqlDataReader dr = command.ExecuteReader(); Console.WriteLine(dr == null); _conn.Close(); }
public void FunctionCallWithParametersPrepareReturnSingleValue() { _conn.Open(); NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32)); command.Prepare(); command.Parameters[0].Value = 4; Int64 result = (Int64) command.ExecuteScalar(); Console.WriteLine(result); //Assertion.AssertEquals(1, result); _conn.Close(); }
public void FunctionCallReturnSingleValueWithPrepare() { _conn.Open(); NpgsqlCommand command = new NpgsqlCommand("funcC()", _conn); command.CommandType = CommandType.StoredProcedure; command.Prepare(); Int64 result = (Int64) command.ExecuteScalar(); Console.WriteLine(result); //Assertion.AssertEquals(4, result); //reader.FieldCount _conn.Close(); }
private void ByteaParameterWithPrepareSupport_Internal() { var command = new NpgsqlCommand("select field_bytea from data where field_bytea = :bytesData", Conn); var bytes = new byte[] {1,2,3,4,5,34,39,48,49,50,51,52,92,127,128,255,254,253,252,251}; command.Parameters.Add(":bytesData", NpgsqlTypes.NpgsqlDbType.Bytea); command.Parameters[":bytesData"].Value = bytes; command.Prepare(); Object result = command.ExecuteNonQuery(); Assert.AreEqual(-1, result); }
public void PreparedDisposeWithOpenReader() { var cmd1 = new NpgsqlCommand("SELECT 1", Conn); var cmd2 = new NpgsqlCommand("SELECT 1", Conn); cmd1.Prepare(); cmd2.Prepare(); var reader = cmd2.ExecuteReader(); reader.Read(); cmd1.Dispose(); cmd2.Dispose(); reader.Close(); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(0)); }
public void PreparedStatementWithParameters() { _conn.Open(); NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn); command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32)); command.Parameters.Add(new NpgsqlParameter("b", DbType.Int64)); command.Prepare(); command.Parameters[0].Value = 3; command.Parameters[1].Value = 5; NpgsqlDataReader dr = command.ExecuteReader(); Console.WriteLine(dr == null); _conn.Close(); }
public void Dispose() { var cmd = new NpgsqlCommand("SELECT 1", Conn); cmd.Dispose(); Assert.That(() => cmd.ExecuteScalar(), Throws.Exception.TypeOf<ObjectDisposedException>()); Assert.That(() => cmd.ExecuteNonQuery(), Throws.Exception.TypeOf<ObjectDisposedException>()); Assert.That(() => cmd.ExecuteReader(), Throws.Exception.TypeOf<ObjectDisposedException>()); Assert.That(() => cmd.Prepare(), Throws.Exception.TypeOf<ObjectDisposedException>()); }