//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(); } }
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 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)); } } } } } }
//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(); } }
//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); }
//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(); } }
//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); }
//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(); } }
//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(); } }
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(); } } }
//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(); } }
//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(); } }
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)); } }
//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); }
//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); }
/// <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(); }
//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(); } }
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)); }
//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); }
//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(); } }
//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); }
public void deleteAusgang(Ausgang r) { buildconnection(); NpgsqlCommand comm = null; try { string sql = "Delete from rechnungen_buchungen where rechnungid = @rechnungid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@rechnungid", r.Rechnungid); comm.Prepare(); comm.ExecuteNonQuery(); sql = "Delete from rechnungszeilen where rechnungid = @rechnungid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@rechnungid", r.Rechnungid); comm.Prepare(); comm.ExecuteNonQuery(); sql = "Delete from ausgang where rechnungid = @rechnungid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@rechnungid", r.Rechnungid); comm.Prepare(); comm.ExecuteNonQuery(); sql = "Delete from rechnungen where rechnungid = @rechnungid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@rechnungid", r.Rechnungid); comm.Prepare(); comm.ExecuteNonQuery(); r.Status = ObjectStates.Deleted; } catch (NpgsqlException exp) { throw new DALException("DAL: Rechnung konnte nicht gelöscht werden!", exp); } finally { comm.Dispose(); conn.Close(); } }
public void deleteAngebot(Angebot a) { buildconnection(); NpgsqlCommand comm = null; try { string sql = "Delete from angebote where angebotid = @angebotid"; comm = new NpgsqlCommand(sql, conn); comm.Parameters.AddWithValue("@angebotid", a.Angebotid); comm.Prepare(); comm.ExecuteNonQuery(); a.Status = ObjectStates.Deleted; } catch (NpgsqlException exp) { throw new DALException("DAL: Angebot konnte nicht gelöscht werden!", exp); } finally { comm.Dispose(); conn.Close(); } }
private void book_button_Click(object sender, EventArgs e) { String sql = "SELECT 1 FROM bokning WHERE datumtid = :date;"; NpgsqlCommand command = new NpgsqlCommand(sql, GolfReception.conn); command.Parameters.Add(new NpgsqlParameter("date", DbType.DateTime)); command.Prepare(); command.Parameters[0].Value = datumtid; NpgsqlDataReader ndr = command.ExecuteReader(); bool update = ndr.Read(); if (update) { sql = "UPDATE bokning SET (notering) = (:notering) WHERE \"datumtid\" = '" + datumtid.ToString(CultureInfo.CreateSpecificCulture("sv-SE")) + "' ;"; } else { sql = "INSERT INTO bokning (notering, datumtid) VALUES (:notering, :date);"; } ndr.Close(); command = new NpgsqlCommand(sql, GolfReception.conn); command.Parameters.Add(new NpgsqlParameter("notering", DbType.String)); if (!update) { command.Parameters.Add(new NpgsqlParameter("date", DbType.DateTime)); } command.Prepare(); command.Parameters[0].Value = note_textBox.Text; if (!update) { command.Parameters[1].Value = datumtid; } command.ExecuteNonQuery(); this.Close(); }
//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(); } }
//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); }
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())); } } }
private void ByteaParameterWithPrepareSupport_Internal() { var command = new NpgsqlCommand("select field_bytea from data where field_bytea = :bytesData", Conn); var bytes = new byte[] {1,2,3,4,5,34,39,48,49,50,51,52,92,127,128,255,254,253,252,251}; command.Parameters.Add(":bytesData", NpgsqlTypes.NpgsqlDbType.Bytea); command.Parameters[":bytesData"].Value = bytes; command.Prepare(); Object result = command.ExecuteNonQuery(); Assert.AreEqual(-1, result); }
public void MultipleQueriesWithParameters([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare) { var cmd = new NpgsqlCommand("SELECT @p1; SELECT @p2", Conn); var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Integer); var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Text); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); if (prepare == PrepareOrNot.Prepared) { cmd.Prepare(); } p1.Value = 8; p2.Value = "foo"; var reader = cmd.ExecuteReader(); Assert.That(reader.Read(), Is.True); Assert.That(reader.GetInt32(0), Is.EqualTo(8)); Assert.That(reader.NextResult(), Is.True); Assert.That(reader.Read(), Is.True); Assert.That(reader.GetString(0), Is.EqualTo("foo")); Assert.That(reader.NextResult(), Is.False); reader.Close(); cmd.Dispose(); }
public void PreparedDisposeWithOpenReader() { var cmd1 = new NpgsqlCommand("SELECT 1", Conn); var cmd2 = new NpgsqlCommand("SELECT 1", Conn); cmd1.Prepare(); cmd2.Prepare(); var reader = cmd2.ExecuteReader(); reader.Read(); cmd1.Dispose(); cmd2.Dispose(); reader.Close(); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(0)); }
public void TestErrorInPreparedStatementCausesReleaseConnectionToThrowException() { // This is caused by having an error with the prepared statement and later, Npgsql is trying to release the plan as it was successful created. var cmd = new NpgsqlCommand("sele", Conn); cmd.Prepare(); }
public void PrepareRequiresParamTypesSet() { using (var cmd = new NpgsqlCommand("SELECT @p", Conn)) { var p = new NpgsqlParameter("p", 8); cmd.Parameters.Add(p); Assert.That(() => cmd.Prepare(), Throws.InvalidOperationException); } }
public void PreparedStatementWithParameters() { ExecuteNonQuery("CREATE TEMP TABLE data (int INTEGER, long BIGINT)"); var command = new NpgsqlCommand("select * from data where int = :a and long = :b;", Conn); command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32)); command.Parameters.Add(new NpgsqlParameter("b", DbType.Int64)); Assert.AreEqual(2, command.Parameters.Count); Assert.AreEqual(DbType.Int32, command.Parameters[0].DbType); command.Prepare(); command.Parameters[0].Value = 3; command.Parameters[1].Value = 5; var dr = command.ExecuteReader(); Assert.IsNotNull(dr); dr.Close(); }
public void DoublePrepare() { ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT, int INTEGER)"); var cmd = new NpgsqlCommand("INSERT INTO data (name) VALUES (:p0)", Conn); cmd.Parameters.Add(new NpgsqlParameter("p0", NpgsqlDbType.Text)); cmd.Parameters["p0"].Value = "test"; cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO data (int) VALUES (:p0)"; cmd.Parameters.Clear(); cmd.Parameters.Add(new NpgsqlParameter("p0", NpgsqlDbType.Integer)); cmd.Parameters["p0"].Value = 8; cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.Dispose(); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(0), "Prepared statements are being leaked"); }
public void 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>()); }
public void Prepare() { ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)"); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(0)); using (var cmd = new NpgsqlCommand("INSERT INTO data (name) VALUES (:p0);", Conn)) { cmd.Parameters.Add(new NpgsqlParameter("p0", NpgsqlDbType.Text)); cmd.Prepare(); cmd.Parameters["p0"].Value = "test"; using (var dr = cmd.ExecuteReader()) { Assert.IsNotNull(dr); dr.Close(); Assert.That(dr.RecordsAffected, Is.EqualTo(1)); } Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(1)); } Assert.That(ExecuteScalar("SELECT COUNT(*) FROM data WHERE name = 'test'"), Is.EqualTo(1)); Assert.That(ExecuteScalar("SELECT COUNT(*) FROM pg_prepared_statements"), Is.EqualTo(0), "Prepared statements are being leaked"); }
public void MultipleQueriesSingleRow([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare) { var cmd = new NpgsqlCommand("SELECT 1; SELECT 2", Conn); if (prepare == PrepareOrNot.Prepared) cmd.Prepare(); var reader = cmd.ExecuteReader(CommandBehavior.SingleRow); Assert.That(reader.Read(), Is.True); Assert.That(reader.GetInt32(0), Is.EqualTo(1)); Assert.That(reader.Read(), Is.False); Assert.That(reader.NextResult(), Is.False); reader.Close(); cmd.Dispose(); }
public void PreparedStatementWithParameters() { _conn.Open(); NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn); command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32)); command.Parameters.Add(new NpgsqlParameter("b", DbType.Int64)); command.Prepare(); command.Parameters[0].Value = 3; command.Parameters[1].Value = 5; NpgsqlDataReader dr = command.ExecuteReader(); Console.WriteLine(dr == null); _conn.Close(); }
public void PreparedStatementNoParameters() { _conn.Open(); NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn); command.Prepare(); command.Prepare(); NpgsqlDataReader dr = command.ExecuteReader(); Console.WriteLine(dr == null); _conn.Close(); }
public void FunctionCallWithParametersPrepareReturnSingleValue() { _conn.Open(); NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32)); command.Prepare(); command.Parameters[0].Value = 4; Int64 result = (Int64) command.ExecuteScalar(); Console.WriteLine(result); //Assertion.AssertEquals(1, result); _conn.Close(); }
public void FunctionCallReturnSingleValueWithPrepare() { _conn.Open(); NpgsqlCommand command = new NpgsqlCommand("funcC()", _conn); command.CommandType = CommandType.StoredProcedure; command.Prepare(); Int64 result = (Int64) command.ExecuteScalar(); Console.WriteLine(result); //Assertion.AssertEquals(4, result); //reader.FieldCount _conn.Close(); }