Prepare() public method

Creates a prepared version of the command on a PostgreSQL server.
public Prepare ( ) : void
return void
Esempio n. 1
0
 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));
                     }
                 }
             }
         }
     }
 }
Esempio n. 2
0
        public static bool CleanupOrphans()
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_pages ");
            sqlCommand.Append("SET parentid = -1, parentguid = '00000000-0000-0000-0000-000000000000' ");
            sqlCommand.Append("WHERE parentid <> -1 AND parentid NOT IN (SELECT pageid FROM mp_pages ) ");
            sqlCommand.Append("");

            int rowsAffected = 0;

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

            return (rowsAffected > 0);
        }
Esempio n. 3
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();
                }
            }
        }
Esempio n. 4
0
 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));
 }
Esempio n. 5
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();
        }
Esempio n. 6
0
        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));
            }
        }
Esempio n. 7
0
        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();
        }
Esempio n. 8
0
        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();
        }
Esempio n. 9
0
        /// <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();
        }
Esempio n. 10
0
 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));
     }
 }
Esempio n. 11
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();
        }
Esempio n. 12
0
        /// <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();
        }
Esempio n. 13
0
        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));
        }
Esempio n. 14
0
 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();
     }
 }
Esempio n. 15
0
 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();
     }
 }
Esempio n. 16
0
        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();
        }
Esempio n. 17
0
 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();
 }
Esempio n. 18
0
 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);
     }
 }
Esempio n. 19
0
 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();
 }
Esempio n. 20
0
        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");
        }
Esempio n. 21
0
        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();
        }
Esempio n. 22
0
        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");
        }
Esempio n. 23
0
 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();
 }
Esempio n. 24
0
      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();
      }
Esempio n. 25
0
      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();
      }
Esempio n. 26
0
      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();
      }
Esempio n. 27
0
        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);
        }
Esempio n. 28
0
 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));
 }
Esempio n. 29
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();

      }
Esempio n. 30
0
 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>());
 }