C# NpgsqlCommand.Prepare方法代码示例

 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();
     }
 }