Prepare() публичный метод

Creates a prepared version of the command on a PostgreSQL server.
public Prepare ( ) : void
Результат void
Пример #1
0
        //Mostra todos os registro do banco diretamente, sem fazer cast para objeto.
        public void ShowAll()
        {
            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_diretor";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
                sql.Prepare();

                NpgsqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    //Listar todos os campos automatizado
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        Console.Write("{0} ", dr[i].ToString());
                    }

                    Console.Write("\n");
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }
        }
Пример #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);
        }
Пример #3
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));
                     }
                 }
             }
         }
     }
 }
Пример #4
0
        //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();
            }
        }
Пример #5
0
        //Método responsável por retornar uma Categoria cujo ID seja igual o passado pelo parâmetro.
        public Categoria getOne(int id)
        {
            try
            {
                bd.OpenConnection();

                String query             = "SELECT * 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;

                NpgsqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Categoria c = new Categoria();
                    c.Cod_categoria  = dr.GetInt32(0);
                    c.Desc_categoria = dr.GetString(1);
                    return(c);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(null);
        }
Пример #6
0
        //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();
            }
        }
Пример #7
0
        //Método responsável por retornar todas as categorias fazendo um cast para objeto, retornando uma lista com todos os diretores do banco.
        public List <Categoria> getAll()
        {
            List <Categoria> retorno = new List <Categoria>();

            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_categoria";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
                sql.Prepare();

                NpgsqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Categoria c = new Categoria();
                    c.Cod_categoria  = dr.GetInt32(0);
                    c.Desc_categoria = dr.GetString(1);
                    retorno.Add(c);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(retorno);
        }
Пример #8
0
        //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();
            }
        }
Пример #9
0
        //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();
            }
        }
Пример #10
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();
                }
            }
        }
Пример #11
0
        //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();
            }
        }
Пример #12
0
        //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();
            }
        }
Пример #13
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));
 }
Пример #14
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();
        }
Пример #15
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));
            }
        }
Пример #16
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();
        }
Пример #17
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();
        }
Пример #18
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();
        }
Пример #19
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));
     }
 }
Пример #20
0
        //Método responsável por retornar um Filme com todos os seus diretores cujo ID seja igual o passado pelo parâmetro.
        public Filme getOne(int id)
        {
            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_filme WHERE cod_filme = :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;

                NpgsqlDataReader dr = sql.ExecuteReader();

                CategoriaDAO cdao = new CategoriaDAO();

                while (dr.Read())
                {
                    Filme f = new Filme();
                    f.Cod_filme  = dr.GetInt32(0);
                    f.Nome_filme = dr.GetString(1);
                    f.Data       = dr.GetDateTime(2);

                    //Como a classe filme tem um atributo do tipo Objeto Categoria,
                    //utilizo a instancia de CategoriaDAO pra pegar o objeto a partir do ID que o banco me retorna, com esse ID passo pro
                    //método getOne que retorna um Objeto do tipo Categoria.
                    f.Categoria = cdao.getOne(3);

                    //É utilizado o método getDiretores que retorna toda lista de diretores do filme para fazer a atribuição.
                    f.Diretores = this.getDiretores(f.Cod_filme);

                    return(f);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(null);
        }
Пример #21
0
        //Método responsável por retornar todos os filmes e seus diretores fazendo um cast para objeto, retornando uma lista com todos os filmes do banco.
        public List <Filme> getAll()
        {
            List <Filme> retorno = new List <Filme>();

            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_filme";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
                sql.Prepare();

                NpgsqlDataReader dr = sql.ExecuteReader();

                CategoriaDAO cdao = new CategoriaDAO();

                //Esse trecho é executado pra cada filme.
                while (dr.Read())
                {
                    Filme f = new Filme();
                    f.Cod_filme  = dr.GetInt32(0);
                    f.Nome_filme = dr.GetString(1);
                    f.Data       = dr.GetDateTime(2);

                    //Como a classe filme tem um atributo do tipo Objeto Categoria,
                    //é utilizada a instancia de CategoriaDAO pra pegar o objeto a partir do ID que o banco retorna, esse ID é passado para o
                    //método getOne que retorna um Objeto do tipo Categoria.
                    f.Categoria = cdao.getOne(3);

                    //É utilizado método getDiretores que retorna toda lista de diretores do filme para fazer a atribuição.
                    f.Diretores = this.getDiretores(f.Cod_filme);

                    retorno.Add(f);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(retorno);
        }
Пример #22
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();
        }
Пример #23
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();
        }
Пример #24
0
        //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();
            }
        }
Пример #25
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));
        }
Пример #26
0
        //Método responsável por retornar um Diretor cujo ID seja igual o passado pelo parâmetro.
        public Diretor getOne(int id)
        {
            try
            {
                bd.OpenConnection();

                String query             = "SELECT * 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;

                NpgsqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Diretor d = new Diretor();
                    d.Cod_diretor  = dr.GetInt32(0);
                    d.Nome_diretor = dr.GetString(1);

                    //É utilizado o método getFilmes que retorna toda lista de filmes do diretor para fazer a atribuição.
                    d.filmes = this.getFilmes(d.Cod_diretor);

                    return(d);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(null);
        }
Пример #27
0
        //Atualização passando como paramentro o ID e os novos atributos do Filme.
        public void Update(int id, string new_nome, DateTime new_data, Categoria new_categoria)
        {
            try
            {
                bd.OpenConnection();

                String query             = "UPDATE tab_filme SET nome_filme = :new_nome and data_filme = :new_data and cod_categoria = :new_categoria WHERE cod_filme = :id";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);

                sql.Parameters.Add(new NpgsqlParameter("new_nome", NpgsqlTypes.NpgsqlDbType.Varchar));
                sql.Parameters.Add(new NpgsqlParameter("new_data", NpgsqlTypes.NpgsqlDbType.Date));
                sql.Parameters.Add(new NpgsqlParameter("new_categoria", NpgsqlTypes.NpgsqlDbType.Integer));
                sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
                sql.Prepare();

                sql.Parameters[0].Value = new_nome;
                sql.Parameters[1].Value = new_data;
                sql.Parameters[2].Value = new_categoria.Cod_categoria;
                sql.Parameters[3].Value = id;
                int linhasAfetadas = sql.ExecuteNonQuery();

                if (Convert.ToBoolean(linhasAfetadas))
                {
                    Console.WriteLine("Filme atualizado com sucesso!");
                }
                else
                {
                    Console.WriteLine("Não existe esse filme!");
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }
        }
Пример #28
0
        //Método responsável por retornar todos os diretores fazendo um cast para objeto, retornando uma lista com todos os diretores do banco.
        public List <Diretor> getAll()
        {
            List <Diretor> retorno = new List <Diretor>();

            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_diretor";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
                sql.Prepare();

                NpgsqlDataReader dr = sql.ExecuteReader();

                //Esse trecho é executado para cada diretor
                while (dr.Read())
                {
                    Diretor d = new Diretor();
                    d.Cod_diretor  = dr.GetInt32(0);
                    d.Nome_diretor = dr.GetString(1);

                    //É utilizado o método getFilmes que retorna toda lista de filmes do diretor para fazer a atribuição.
                    d.filmes = this.getFilmes(d.Cod_diretor);

                    retorno.Add(d);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(retorno);
        }
Пример #29
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();
     }
 }
Пример #30
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();
     }
 }
Пример #31
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();
        }
Пример #32
0
        //Remove todas relaçoes de um filme a qualquer diretor.
        public void removerPorFilme(int id_filme)
        {
            try
            {
                bd.OpenConnection();

                String query             = "DELETE FROM tab_filme_diretor WHERE cod_filme = :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_filme;
                int linhasAfetadas = sql.ExecuteNonQuery();
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }
        }
Пример #33
0
        //Método responsavel por retornar todos os filmes do diretor.
        public List <Filme> getFilmes(int cod_diretor)
        {
            bd.OpenConnection();
            List <Filme> filmes = new List <Filme>();

            String query = "SELECT * FROM tab_filme_diretor WHERE cod_diretor = :cod_diretor";

            Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);

            sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer));
            sql.Prepare();
            sql.Parameters[0].Value = cod_diretor;

            NpgsqlDataReader dr = sql.ExecuteReader();

            while (dr.Read())
            {
                FilmeDAO fdao = new FilmeDAO();
                //Adiciona na lista de diretores que vai ser retornada, o diretor pelo metodo
                //getOne que recebe como parâmetro o ID retornado do banco e faz o retorno do Objeto.
                filmes.Add(fdao.getOne(dr.GetInt32(1)));
            }
            return(filmes);
        }
Пример #34
0
        private T RunStoredFunction <T>(string procedure, Func <NpgsqlDataReader, T> mapColumns, List <Npgsql.NpgsqlParameter> paramsNpgsqlParameters = null)
        {
            if (paramsNpgsqlParameters == null)
            {
                paramsNpgsqlParameters = new List <NpgsqlParameter>();
            }

            using (var connection = new Npgsql.NpgsqlConnection(_databaseConfig.ConnectionString))
            {
                connection.Open();

                using (var command = new Npgsql.NpgsqlCommand(procedure, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    foreach (var parameter in paramsNpgsqlParameters)
                    {
                        command.Parameters.Add(parameter);
                    }

                    command.Prepare();
                    return(mapColumns(command.ExecuteReader()));
                }
            }
        }
Пример #35
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);
        }
Пример #36
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();
 }
Пример #37
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));
 }
Пример #38
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();
 }
Пример #39
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);
     }
 }
Пример #40
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();
        }
Пример #41
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");
        }
Пример #42
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>());
 }
Пример #43
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");
        }
Пример #44
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();
 }
Пример #45
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();

      }
Пример #46
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();
      }
Пример #47
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();
      }
Пример #48
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();
      }