public void Long([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
{
var builder = new StringBuilder("ABCDEééé", Conn.BufferSize);
builder.Append('X', Conn.BufferSize);
var expected = builder.ToString();
ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
var cmd = new NpgsqlCommand(@"INSERT INTO data (name) VALUES (@p)", Conn);
cmd.Parameters.Add(new NpgsqlParameter("p", expected));
cmd.ExecuteNonQuery();
const string queryText = @"SELECT name, 'foo', name, name, name, name FROM data";
cmd = new NpgsqlCommand(queryText, Conn);
var reader = cmd.ExecuteReader(behavior);
reader.Read();
var actual = reader[0];
Assert.That(actual, Is.EqualTo(expected));
if (IsSequential(behavior))
Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential");
else
Assert.That(reader[0], Is.EqualTo(expected));
Assert.That(reader.GetString(1), Is.EqualTo("foo"));
Assert.That(reader.GetFieldValue<string>(2), Is.EqualTo(expected));
Assert.That(reader.GetValue(3), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<string>(4), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue<char[]>(5), Is.EqualTo(expected.ToCharArray()));
}
public void ExceptionFieldsArePopulated()
{
const string dropTable = @"DROP TABLE IF EXISTS public.uniqueviolation";
const string createTable = @"CREATE TABLE public.uniqueviolation (id INT NOT NULL, CONSTRAINT uniqueviolation_pkey PRIMARY KEY (id))";
const string insertStatement = @"INSERT INTO public.uniqueviolation (id) VALUES(1)";
// In this case we'll test a simple unique violation, we're not too interested in testing more
// cases than this as the same code is executed in all error situations.
try
{
var command = new NpgsqlCommand(dropTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(createTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(insertStatement, Conn);
command.ExecuteNonQuery();
//Now cause the unique violation...
command.ExecuteNonQuery();
}
catch (NpgsqlException ex)
{
Assert.AreEqual("", ex.ColumnName); // Should not be populated for unique violations.
Assert.AreEqual("uniqueviolation", ex.TableName);
Assert.AreEqual("public", ex.SchemaName);
Assert.AreEqual("uniqueviolation_pkey", ex.ConstraintName);
Assert.AreEqual("", ex.DataTypeName); // Should not be populated for unique violations.
}
}
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();
}
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="intitString"></param>
public pgsql_API(string intitString)
{
// connectionstring=
string[] parameters = intitString.Replace("\r\n","\n").Split('\n');
foreach(string param in parameters){
if(param.ToLower().IndexOf("connectionstring=") > -1){
m_ConStr = param.Substring(17);
}
}
SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(m_ConStr);
string database = b.InitialCatalog;
b.InitialCatalog = "";
using(NpgsqlConnection con = new NpgsqlConnection(b.ToString().ToLower().Replace("data source","server"))){
con.Open();
// See if database exists
try{
con.ChangeDatabase(database);
}
catch{
// Database don't exist, try to create it
try{
con.Close();
con.ConnectionString = b.ToString().ToLower().Replace("data source","server");
con.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "create database \"" + database + "\"";
cmd.ExecuteNonQuery();
con.ChangeDatabase(database);
// Create tables
cmd.CommandText = ResManager.GetText("tables.sql",System.Text.Encoding.Default);
cmd.ExecuteNonQuery();
// Create procedures
cmd.CommandText = ResManager.GetText("procedures.sql",System.Text.Encoding.Default);
cmd.ExecuteNonQuery();
}
catch{
throw new Exception("Database '" + database + "' doesn''t exist ! Create failed, specified user doesn't have enough permisssions to create database ! Create database manually.");
}
}
}
}
//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();
}
}
//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();
}
}
public void Alterar(Model_Vo_LivroCaixa pLivroCaixa)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update livrocaixa set datahora = @datahora, descricao = @descricao, idcontasareceber = @idcontasareceber, tipodemovimento = @tipodemovimento, valor = @valor where id = @id;";
cmd.Parameters.AddWithValue("@id", pLivroCaixa.Id);
cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr(pLivroCaixa.DataHora, false));
cmd.Parameters.AddWithValue("@descricao", pLivroCaixa.Descricao);
cmd.Parameters.AddWithValue("@idcontasareceber", pLivroCaixa.IdContasAReceber);
cmd.Parameters.AddWithValue("@tipodemovimento", pLivroCaixa.TipoDeMovimento.ToString());
cmd.Parameters.AddWithValue("@valor", pLivroCaixa.Valor);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public void create_table(dbtable t)
{
StringBuilder cmd = new StringBuilder("create table " + t.name + "(");
for (int i = 0; i < t.fields.Length; i++)
{
if (i != 0)
{
cmd.Append(',');
}
cmd.Append(t.fields [i].name + " ");
string type_name;
if (t.fields [i].type == dbfield.types.str)
{
if (t.fields [i].len <= 0)
{
type_name = "text";
}
else
{
type_name = "varchar(" + t.fields [i].len.ToString() + ")";
}
}
else
{
type_name = type_names [(int)t.fields [i].type];
}
cmd.Append(type_name);
}
Npgsql.NpgsqlCommand c = new Npgsql.NpgsqlCommand(cmd.ToString(), con);
c.ExecuteNonQuery();
table_mapping [t.id] = new table_entry(t);
}
static void ClearCatalogue(string connString)
{
try
{
NpgsqlConnection conn = new NpgsqlConnection(connString);
conn.Open();
Console.Clear();
string clearTable = "DELETE FROM phone_book";
NpgsqlCommand cmd = new NpgsqlCommand(clearTable, conn);
cmd.ExecuteNonQuery();
Console.WriteLine(">>> Catalogue cleared");
Console.ReadKey();
Console.Clear();
conn.Close();
}
catch (Exception msg)
{
Console.WriteLine(msg.ToString());
throw;
}
}
public void Append(string name, byte[] data, long expectedVersion)
{
using (var conn = new NpgsqlConnection(_connectionString))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
var version = MakeSureLastVersionMatches(name, expectedVersion, conn, tx);
const string txt =
@"INSERT INTO ES_Events2 (CustomerId, Name, Version, Data)
VALUES(:customerId, :name, :version, :data)";
using (var cmd = new NpgsqlCommand(txt, conn, tx))
{
cmd.Parameters.AddWithValue(":name", name);
cmd.Parameters.AddWithValue(":version", version+1);
cmd.Parameters.AddWithValue(":data", data);
cmd.Parameters.AddWithValue(":customerId", customerId);
cmd.ExecuteNonQuery();
}
tx.Commit();
}
}
}
public void Alterar(Model_Vo_Sala pSala)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update sala set nome = @nome, capacidade = @capacidade, tipo = @tipo, idproduto = @idproduto where id = @id;";
cmd.Parameters.AddWithValue("@id", pSala.Id);
cmd.Parameters.AddWithValue("@nome", pSala.Nome);
cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString().ToString());
cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public void Create(IEnumerable<string> columnDefinitions)
{
var sb = new StringBuilder();
if(!string.IsNullOrEmpty(_schemaName))
sb.AppendFormat("CREATE SCHEMA \"{0}\";",_schemaName);
sb.Append("CREATE TABLE ");
sb.Append(NameWithSchema);
sb.Append(" (");
foreach (string definition in columnDefinitions)
{
sb.Append(definition);
sb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
var s = sb.ToString();
using (var command = new NpgsqlCommand(s, Connection, Transaction))
command.ExecuteNonQuery();
}
//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();
}
}
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
{
resman = new System.Resources.ResourceManager(this.GetType());
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
if ((isolation != IsolationLevel.ReadCommitted) &&
(isolation != IsolationLevel.Serializable))
throw new ArgumentOutOfRangeException(resman.GetString("Exception_UnsopportedIsolationLevel"), "isolation");
_conn = conn;
_isolation = isolation;
StringBuilder commandText = new StringBuilder("SET TRANSACTION ISOLATION LEVEL ");
if (isolation == IsolationLevel.ReadCommitted)
commandText.Append("READ COMMITTED");
else
commandText.Append("SERIALIZABLE");
commandText.Append("; BEGIN");
NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector);
command.ExecuteNonQuery();
_conn.Connector.Transaction = this;
}
public void Alterar(Model_Vo_Agenda pAgenda)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update agenda set datahorareserva = @datahorareserva, idcliente = @idcliente, idsala = @idsala where id = @id;";
cmd.Parameters.AddWithValue("@id", pAgenda.Id);
cmd.Parameters.AddWithValue("@datahorareserva", Dados.Model_Dao_Dados.ConverterDataToStr(pAgenda.DataHoraReserva, false));
cmd.Parameters.AddWithValue("@idcliente", pAgenda.IdCliente);
cmd.Parameters.AddWithValue("@idsala", pAgenda.IdSala);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
private void btnChange_Click(object sender, EventArgs e)
{
try
{
Postgres.ConnectionString = "Server=" + Properties.Settings.Default.Server + ";Port=" +
Properties.Settings.Default.Port +
";User Id=" + Properties.Settings.Default.UserId + ";Password="******";Database=" + Properties.Settings.Default.Database +
";";
Postgres.Open();
var commandUpdateDescr =
new NpgsqlCommand(
"UPDATE \"GPL\".\"INFORMATION\" SET \"DESCRIPTION\" = '" + txtDescription.Text + "' WHERE \"ID\" = " + _descrId + ";",
Postgres);
commandUpdateDescr.ExecuteNonQuery();
MessageBox.Show(@"Изменения применены", @"Изменение инструкции", MessageBoxButtons.OK,
MessageBoxIcon.Information);
Postgres.Close();
Close();
}
catch (Exception ex)
{
throw new Exception(@"Ошибка: ", ex);
}
}
//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();
}
}
public static bool ExecuteNonQuery(string catalog, NpgsqlCommand command)
{
try
{
if (command != null)
{
if (ValidateCommand(command))
{
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog)))
{
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
return true;
}
}
}
return false;
}
catch (NpgsqlException ex)
{
if (ex.Code.StartsWith("P"))
{
string errorMessage = GetDBErrorResource(ex);
throw new MixERPException(errorMessage, ex);
}
throw;
}
}
public void DeriveParametersVarious()
{
// This function returns record because of the two Out (InOut & Out) parameters
ExecuteNonQuery(@"CREATE OR REPLACE FUNCTION ""func""(IN param1 INT, OUT param2 text, INOUT param3 INT) RETURNS record AS
'
BEGIN
param2 = ''sometext'';
param3 = param1 + param3;
END;
' LANGUAGE 'plpgsql';");
var cmd = new NpgsqlCommand("func", Conn);
cmd.CommandType = CommandType.StoredProcedure;
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(3));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.InputOutput));
cmd.Parameters[0].Value = 5;
cmd.Parameters[2].Value = 4;
cmd.ExecuteNonQuery();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(5));
Assert.That(cmd.Parameters[1].Value, Is.EqualTo("sometext"));
Assert.That(cmd.Parameters[2].Value, Is.EqualTo(9));
}
public static bool ModificarUsuario(string nombre, string apellido, string direccion, string nombreusuario, string email)
{
try
{
NpgsqlCommand cmd = new NpgsqlCommand("update usuario set nombre = @nombre, apellido = @apellido, direccion = @direccion, email = @email where nombreusuario = @nombreusuario", Conexion.conexion);
cmd.Parameters.Add("nombre", nombre);
cmd.Parameters.Add("apellido", apellido);
cmd.Parameters.Add("direccion", direccion);
cmd.Parameters.Add("nombreusuario", nombreusuario);
cmd.Parameters.Add("email", email);
Conexion.abrirConexion();
if (cmd.ExecuteNonQuery() != -1)
{
Conexion.cerrarConexion();
return true;
}
else
{
Conexion.cerrarConexion();
return false;
}
}
catch (Exception ex)
{
throw new Exception("Hubo un error con la base de datos, intente de nuevo más tarde");
}
}
public void Alterar(Model_Vo_Produto pProduto)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update produto set descricao = @descricao, unidade = @unidade, estoque = @estoque, valordevenda = @valordevenda, observacao = @observacao where id = @id;";
cmd.Parameters.AddWithValue("@descricao", pProduto.Descricao);
cmd.Parameters.AddWithValue("@unidade", pProduto.Unidade);
cmd.Parameters.AddWithValue("@estoque", pProduto.Estoque);
cmd.Parameters.AddWithValue("@valordevenda", pProduto.ValorDeVenda);
cmd.Parameters.AddWithValue("@observacao", pProduto.Observacao);
cmd.Parameters.AddWithValue("@id", pProduto.Id);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
//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();
}
}
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);
}
//När användaren kilckar på "OK" sker följande:
private void OK_button_Click(object sender, EventArgs e)
{
//Deklarerar variabel klass
var klass = "";
//Om "A" är iklickat av användaren får variabel klass värdet A.
if (A_radioButton.Checked == true)
{
klass = "A";
}
//Om "B" är iklickat av användaren får variabel klass värdet B.
else if (B_radioButton.Checked == true)
{
klass = "B";
}
//Om "C" är iklickat av användaren får variabel klass värdet C.
else if (C_radioButton.Checked == true)
{
klass = "C";
}
//Skapar strängen anmaldeltagare.
//Strängen innehåller information om tävlingsdeltagare. Lägger in golfid, tävlingid och klass i databasen, i tabellen deltari.
string anmaldeltagare = "insert into deltari (golfid, tavlingid, klass) values ('" + Golfid_textBox.Text + "'," + Tävlingid_textBox.Text + ",'" + klass + "')";
//Skapar ett nytt Npgsql-kommando, command1.
NpgsqlCommand command1 = new NpgsqlCommand(anmaldeltagare, Huvudfönster.conn);
//Utför kommando, command1.
command1.ExecuteNonQuery();
//När allt ovan är utfört visas en meddelanderuta.
MessageBox.Show("Spelare är nu anmäld till tävling!");
//Sedan stängs hela detta form, AnmälDeltagare.
this.Close();
}
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
{
resman = new System.Resources.ResourceManager(this.GetType());
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
_conn = conn;
_isolation = isolation;
StringBuilder commandText = new StringBuilder("BEGIN; SET TRANSACTION ISOLATION LEVEL ");
if ( (isolation == IsolationLevel.RepeatableRead) ||
(isolation == IsolationLevel.Serializable)
)
commandText.Append("SERIALIZABLE");
else
{
// Set isolation level default to read committed.
_isolation = IsolationLevel.ReadCommitted;
commandText.Append("READ COMMITTED");
}
commandText.Append(";");
NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector);
command.ExecuteNonQuery();
_conn.Connector.Transaction = this;
}
public bool Update()
{
string sQuery = "UPDATE tbm_carrepair SET " +
"carid=@cid,dlt=@dlt,opedit=@opedit,pcedit=@pcedit,luedit=now()" +
"WHERE repairid = @rid";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
if (m_repairid != null)
{
cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = repairid;
}
else
{
cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (m_carid != null)
{
cmd.Parameters.Add("@cid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = carid;
}
else
{
cmd.Parameters.Add("@cid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (opedit != null)
{
cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = opedit;
}
else
{
cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (pcedit != null)
{
cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pcedit;
}
else
{
cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (luedit != null && luedit != DateTime.MinValue)
{
cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = luedit;
}
else
{
cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = DBNull.Value;
}
cmd.Parameters.Add("@dlt", NpgsqlTypes.NpgsqlDbType.Boolean).Value = dlt;
cmd.CommandText = sQuery;
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
}
private static void Run()
{
var connStr = ConfigurationManager.AppSettings["ConnectionString"];
using(NpgsqlConnection conn = new NpgsqlConnection(connStr))
using(NpgsqlConnection updateConn = new NpgsqlConnection(connStr))
{
conn.Open();
updateConn.Open();
// Define a query returning a single row result set
using (NpgsqlCommand command = new NpgsqlCommand("select id, file_name, fk_file_id from source_documents where thumbnail_created = false;", conn))
{
using (NpgsqlDataReader dr = command.ExecuteReader())
{
// Output rows
while (dr.Read())
{
CreateThumbnail((string)dr[1], (long)dr[2]);
using (NpgsqlCommand update = new NpgsqlCommand("update source_documents set thumbnail_created = true where id = " + dr[0], updateConn))
{
update.ExecuteNonQuery();
}
}
}
}
}
}
private void insertIntoBase()
{
try
{
short total = Convert.ToInt16( this.textBox1.Text.Trim());
int s_sum = Convert.ToInt32(this.textBox2.Text.Trim());
short d_sum = Convert.ToInt16(this.textBox3.Text.Trim());
short measure = Convert.ToInt16(this.textBox4.Text.Trim());
int _s = Convert.ToInt32(this.textBox5.Text.Trim());
short _d = Convert.ToInt16(this.textBox6.Text.Trim());
string _comm = this.richTextBox1.Text.Trim();
NpgsqlCommand _command = new NpgsqlCommand("Insert into tumor_size (total, d_sum, s_sum, s, d, measurable, pat_id, rasp_data, got_comment, brain, kidney, skin, bones, bone_marrow, lung, lymphatic, adrenal, os, hepar, lien, other, doc_id) values "
+ "( '" + total + "','" + d_sum + "','" + s_sum + "', '" + _s + "','" + _d + "','" + measure + "','" + this.searchPatientBox1.pIdN + "','" + this.dateTimePicker1.Value + "', :descr ,'" + this.checkedListBox1.GetItemChecked(0)
+ "','" + this.checkedListBox1.GetItemChecked(1) + "','" + this.checkedListBox1.GetItemChecked(2) + "','" + this.checkedListBox1.GetItemChecked(3) + "','" + this.checkedListBox1.GetItemChecked(4) + "','" + this.checkedListBox1.GetItemChecked(5)
+ "','" + this.checkedListBox1.GetItemChecked(6) + "','" + this.checkedListBox1.GetItemChecked(7) + "','" + this.checkedListBox1.GetItemChecked(8) + "','" + this.checkedListBox1.GetItemChecked(9) + "','" + this.checkedListBox1.GetItemChecked(10)
+ "','" + this.checkedListBox1.GetItemChecked(11) + "' , '" + DBExchange.Inst.dbUsrId + "') ;", DBExchange.Inst.connectDb);
using (_command)
{
_command.Parameters.Add(new NpgsqlParameter("descr", NpgsqlDbType.Text));
_command.Parameters[0].Value = _comm;
}
_command.ExecuteNonQuery();
clearForm();
}
catch (Exception exception)
{
Warnings.WarnLog log = new Warnings.WarnLog(); log.writeLog(MethodBase.GetCurrentMethod().Name, exception.Message.ToString(), exception.StackTrace.ToString());
}
}
public void Alterar(Model_Vo_Agenda pAgenda)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update agenda set datahorareserva = @datahorareserva, idcliente = @idcliente, idsala = @idsala where id = @id;";
cmd.Parameters.AddWithValue("@id", pAgenda.Id);
cmd.Parameters.AddWithValue("@datahorareserva", Dados.Model_Dao_Dados.ConverterDataToStr(pAgenda.DataHoraReserva, false));
cmd.Parameters.AddWithValue("@idcliente", pAgenda.IdCliente);
cmd.Parameters.AddWithValue("@idsala", pAgenda.IdSala);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public void FecharContasAReceber(Model_Vo_ContasAReceber pContasAReceber)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update contasareceber set recebido = @recebido where idreservaorigem = @idreservaorigem;";
cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem);
cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
void mainit_paroli()
{
switch (db.liet_grupa) {
case "Mācībspēks":
mainas_kom = new NpgsqlCommand("UPDATE macibspeki SET parole = @parole WHERE id_macsp = @lietotajs;", db.sav);
break;
case "Vadība":
mainas_kom = new NpgsqlCommand("UPDATE vadiba SET parole = @parole WHERE id_vadiba = @lietotajs;", db.sav);
break;
case "Students":
mainas_kom = new NpgsqlCommand("UPDATE studenti SET parole = @parole WHERE id_matr = @lietotajs;", db.sav);
break;
}
mainas_kom.Parameters.AddWithValue("@lietotajs", db.lietotajs);
mainas_kom.Parameters.AddWithValue("@parole", db.SHA1Parole(db.lietotajs, tb_jauna.Text));
db.sav.Open();
int ieraksti = mainas_kom.ExecuteNonQuery();
db.sav.Close();
if (ieraksti != -1) {
MessageBox.Show("Lietotāja parole veiksmīgi nomainīta!", "Parole nomainīta", MessageBoxButtons.OK, MessageBoxIcon.Information);
DialogResult = DialogResult.OK;
Close();
} else {
MessageBox.Show("Paroles maiņas procesā radās neparedzēta kļūda!\nMēģiniet atkārtot paroles maiņu vēlāk.", "Kļūda paroles maiņas procesā", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
public void Alterar(Model_Vo_Sala pSala)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update sala set nome = @nome, capacidade = @capacidade, tipo = @tipo, idproduto = @idproduto where id = @id;";
cmd.Parameters.AddWithValue("@id", pSala.Id);
cmd.Parameters.AddWithValue("@nome", pSala.Nome);
cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString().ToString());
cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public void Alterar(Model_Vo_Produto pProduto)
{
// conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update produto set descricao = @descricao, unidade = @unidade, estoque = @estoque, valordevenda = @valordevenda, observacao = @observacao where id = @id;";
cmd.Parameters.AddWithValue("@descricao", pProduto.Descricao);
cmd.Parameters.AddWithValue("@unidade", pProduto.Unidade);
cmd.Parameters.AddWithValue("@estoque", pProduto.Estoque);
cmd.Parameters.AddWithValue("@valordevenda", pProduto.ValorDeVenda);
cmd.Parameters.AddWithValue("@observacao", pProduto.Observacao);
cmd.Parameters.AddWithValue("@id", pProduto.Id);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
private void create_pass()
{
if (titleB.Text.Length != 0)
{
if (loginB.Text.Length != 0)
{
if (pswdB.Text.Length != 0)
{
if (linkB.Text.Length != 0)
{
mf1.connect_db();
string sql_insert = "INSERT INTO infos (title, login, password,";
if (noteB.Text.Length != 0)
{
sql_insert += " note,";
}
sql_insert += " link ) VALUES (";
sql_insert += "'" + titleB.Text + "', ";
sql_insert += "'" + loginB.Text + "', ";
sql_insert += "'" + pswdB.Text + "', ";
if (noteB.Text.Length != 0)
{
sql_insert += "'" + noteB.Text + "', ";
}
sql_insert += "'" + linkB.Text + "'); ";
NpgsqlCommand command = new NpgsqlCommand(sql_insert, mf1.connect_db());
try
{
command.ExecuteNonQuery();
mf1.connect_db().Close();
}
catch
{
MessageBox.Show("Что-то пошло нетак! ");
return;
}
MessageBox.Show("Новое устройство добавленно!");
}
else
{
MessageBox.Show("Поле IP - пусто!");
}
}
else
{
MessageBox.Show("Поле пароля - пусто!");
}
}
else
{
MessageBox.Show("Поле Логни - пусто!");
}
}
else
{
MessageBox.Show("Поле Названия - пусто!");
}
}
//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 string AccountCreation(string json)
{
string status = String.Empty;
try
{
using (NpgsqlConnection con = new NpgsqlConnection(_connectionString))
{
con.Open();
var command = new Npgsql.NpgsqlCommand("_bt_account_create", con);
command.CommandType = System.Data.CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.ParameterName = "input";
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json;
parameter.Value = json;
command.Parameters.Add(parameter);
int output = command.ExecuteNonQuery();
status = "ok";
}
}
catch (Exception ex)
{
status = ex.Message;
}
return(status);
}
public static Facture getAjoutFacture(Facture a)
{
NpgsqlConnection con = Connexion.Connection();
try
{
string insert = "insert into yvs_com_doc_ventes"
+ "(num_piece, type_doc, statut, client, supp, actif, "
+ "num_doc, entete_doc, heure_doc, montant_avance, solde, date_save, mouv_stock)"
+ "values ('" + a.NumPiece + "', '" + a.TypeDoc + "', '" + a.Statut + "', " + a.Client.Id + ", false, true, "
+ "'" + a.NumDoc + "', " + a.Entete.Id + ", '" + a.HeureDoc.ToString("T") + "', " + a.MontantAvance + ", " + a.Solde + ", '" + DateTime.Now + "', " + a.MouvStock + ")"; ;
if ((a.Categorie != null) ? a.Categorie.Id > 0 : false)
{
insert = "insert into yvs_com_doc_ventes"
+ "(num_piece, type_doc, statut, client, categorie_comptable, supp, actif, "
+ "num_doc, entete_doc, heure_doc, montant_avance, solde, date_save, mouv_stock)"
+ "values ('" + a.NumPiece + "', '" + a.TypeDoc + "', '" + a.Statut + "', " + a.Client.Id + ", " + a.Categorie.Id + ", false, true, "
+ "'" + a.NumDoc + "', " + a.Entete.Id + ", '" + a.HeureDoc.ToString("T") + "', " + a.MontantAvance + ", " + a.Solde + ", '" + DateTime.Now + "', " + a.MouvStock + ")";
}
NpgsqlCommand cmd = new NpgsqlCommand(insert, con);
cmd.ExecuteNonQuery();
a.Id = getCurrent();
return a;
}
catch (NpgsqlException e)
{
Messages.Exception(e);
return null;
}
finally
{
Connexion.Deconnection(con);
}
}
public void SalvarGrupo(GrupclienteDto entidade)
{
using (var conn = new NpgsqlConnection(Connectionstring))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
using (
var cmd =
new NpgsqlCommand(
"INSERT INTO grupcliente (grcl_nome, grcl_ativo,empr_codigo) values ('" + entidade.grcl_nome + "'," + entidade.grcl_ativo + ","+entidade.empr_codigo+")", conn,
trans))
{
cmd.ExecuteNonQuery();
trans.Commit();
}
}
catch (Exception)
{
trans.Rollback();
}
}
}
}
//När användaren kilckar på "Radera markerad" sker följande
private void RaderaMarkerad_button_Click(object sender, EventArgs e)
{
//Använder strängen id för att se vilket golfid det är som användaren har markerat och valt att radera.
string id = markeratid();
//Skapar strängen tabort1.
//Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen medlem.
String tabort1 = "delete from deltari where golfid = '" + id + "';";
//Skapar ett nytt Npgsql kommando, command11.
NpgsqlCommand command11 = new NpgsqlCommand(tabort1, Huvudfönster.conn);
//Utför kommando, command12.
command11.ExecuteNonQuery();
//Skapar strängen tabort1.
//Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen person.
String tabort2 = "delete from person where golfid = '" + id + "';";
//Skapar ett nytt Npgsql kommando, command12.
NpgsqlCommand command12 = new NpgsqlCommand(tabort2, Huvudfönster.conn);
//Utför kommando, command12.
command12.ExecuteNonQuery();
//Skapar strängen tabort2.
//Strängen innehåller information om vilken spelare som ska raderas. Raderar golfid från databasen, tabellen medlem.
String tabort3 = "delete from medlem where golfid = '" + id + "';";
//Skapar ett nytt Npgsql kommando, command122.
NpgsqlCommand command122 = new NpgsqlCommand(tabort3, Huvudfönster.conn);
//Utför kommando, command122.
command122.ExecuteNonQuery();
//När allt ovan är utfört visas en meddelanderuta.
MessageBox.Show("Vald spelare är nu borttagen ur databasen!");
}
public static void ProcessLinks(XmlReader reader, NpgsqlConnection connection)
{
var page_title = reader.GetAttribute("title");
List<string> titles;
XmlReader inner;
inner = reader.ReadSubtree();
titles = new List<string>();
while (inner.ReadToFollowing("link")) titles.Add(inner.ReadElementContentAsString());
if (titles.Count() == 0) return;
// Now we have a list of titles construct a subquery to get the ID's
// I could use this query, which looks nicer, but EXPLAIN says it costs a little bit more.
// SELECT id FROM pages WHERE title IN ('Apple','ElementalAllotropes')
// AND redirect IS NULL UNION SELECT p2.id FROM pages AS p1
// JOIN pages AS p2 ON p1.title IN ('Apple','ElementalAllotropes') AND p2.title = p1.redirect AND p2.redirect IS NULL;
var select_command_text = string.Format(PostgresSchema.LINK_ID_QUERY,
string.Join(",", (from val in Enumerable.Range(0, titles.Count)
select string.Format(":title_{0}", val))));
// Update our links to be the array of ID's using the subquery above
var command = new NpgsqlCommand(string.Format("UPDATE pages SET links = ARRAY({0}) WHERE title = :title", select_command_text), connection);
foreach (var val in Enumerable.Range(0, titles.Count)) command.Parameters.AddWithValue("title_"+val, titles[val]);
command.Parameters.Add(new NpgsqlParameter("title", page_title));
command.ExecuteNonQuery();
}
public int Salvarcliente(string nome, int codempresa)
{
using (var conn = new NpgsqlConnection(Connectionstring))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
int id;
using (var cmd = new NpgsqlCommand("SELECT MIN(PORT_VNC) FROM PORTAS WHERE PORT_VNC NOT iN(SELECT CLIE_IDVNC FROM CLIENTE)", conn, trans))
{
id = int.Parse(cmd.ExecuteScalar().ToString());
}
using (
var cmd =
new NpgsqlCommand(
"INSERT INTO cliente (clie_nome,clie_idvnc, empr_codigo) values ('" + nome + "'," + id + ","+codempresa+")", conn,
trans))
{
cmd.ExecuteNonQuery();
trans.Commit();
return id;
}
}
catch (Exception)
{
trans.Rollback();
return 0;
}
}
}
}
public void ColumnNameExceptionFieldIsPopulated()
{
const string dropTable = @"DROP TABLE IF EXISTS public.notnullviolation";
const string createTable = @"CREATE TABLE public.notnullviolation (id INT NOT NULL)";
const string insertStatement = @"INSERT INTO public.notnullviolation (id) VALUES(NULL)";
// Since the 5 error fields were added as of PostgreSQL 9.3, we'll skip testing for versions previous to that.
if (Conn.PostgreSqlVersion < new Version("9.3"))
Assert.Ignore("Postgres version is {0} (< 9.3))", Conn.PostgreSqlVersion);
try
{
var command = new NpgsqlCommand(dropTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(createTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(insertStatement, Conn);
//Cause the NOT NULL violation
command.ExecuteNonQuery();
}
catch (NpgsqlException ex)
{
Assert.AreEqual("public", ex.SchemaName);
Assert.AreEqual("notnullviolation", ex.TableName);
Assert.AreEqual("id", ex.ColumnName);
}
}
public void CreateIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
{
string indexName = data.ToString();
indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
indexName += "_" + index + @"_idx";
if (data == DataEnum.countries)
{
if (index == IndexEnum.gist)
indexName = "countries_geom_gist";
else return;
}
string createIndexCommandText =
@"DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = '" + indexName + @"'
AND n.nspname = 'public'
) THEN
CREATE INDEX " + indexName + @"
ON random_points_" + (int)dataSize + @"
USING " + index + @" (geom);
END IF;
END$$;";
using (var command = new NpgsqlCommand(createIndexCommandText, _conn))
command.ExecuteNonQuery();
}
public Boolean Excluir(int id)
{
//conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "delete from produto where id = " + Convert.ToString(id);
cn.Open();
int resultado = cmd.ExecuteNonQuery();
if (resultado != 1)
{
throw new Exception("Não foi possível excluir a produto " + Convert.ToString(id));
}
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
return(true);
}
public static void CleanTables(NpgsqlConnection connection)
{
if (connection == null) throw new ArgumentNullException("connection");
var script = GetStringResource(
typeof (PostgreSqlTestObjectsInitializer).Assembly,
"Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", string.Format("'{0}'", ConnectionUtils.GetSchemaName()));
//connection.Execute(script);
using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
using (var command = new NpgsqlCommand(script, connection, transaction))
{
command.CommandTimeout = 120;
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException ex)
{
throw;
}
}
}
public void Button1_Click(object sender, EventArgs e)
{
try
{
const string pgConstring = "Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=1234;";
NpgsqlConnection conx = new Npgsql.NpgsqlConnection(pgConstring);
if (conx.State != ConnectionState.Open)
{
conx.Open();
}
if (conx.State == ConnectionState.Open)
{
string cmd1 = string.Format("SELECT user_id, user_pass, firstname, lastname, user_name FROM public.user_login WHERE user_name ='{0}';"
, t3.Text
);
var rder = new Npgsql.NpgsqlDataAdapter(cmd1, conx);
DataSet ds = new DataSet();
rder.Fill(ds);
var usr = ConvertDataTable <user_login>(ds.Tables[0]);
if (usr.Count <= 0 || usr == null)
{
if (t1.Text == "" || t2.Text == "" || t3.Text == "" || t4.Text == "" || t5.Text == "")
{
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + "กรุณากรอกข้อมูลให้ครบ" + "');", true);
}
else
{
string text = string.Format("INSERT INTO user_login( user_pass, firstname, lastname, user_name,user_grade) VALUES ('{0}','{1}','{2}','{3}','{4}');"
, t4.Text
, t1.Text
, t2.Text
, t3.Text
, t5.Text);
NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(text, conx);
cmd.ExecuteNonQuery();
Response.Redirect("register2.aspx");
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + "มีuserนี้อยู่ในระบบอยู่แล้ว" + "');", true);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
public Boolean Excluir(int idMovimentacaoEstoque, int idAgenda)
{
//conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if ((idMovimentacaoEstoque > 0))
{
cmd.CommandText = "delete from movimentacaoestoque where id = " + Convert.ToString(idMovimentacaoEstoque);
}
else
{
cmd.CommandText = "delete from movimentacaoestoque where idreservaorigem = " + Convert.ToString(idAgenda);
}
cn.Open();
int resultado = cmd.ExecuteNonQuery();
if (resultado != 1)
{
if ((idAgenda > 0))
{
throw new Exception("Não foi possível excluir a MovimentacaoEstoque da Agenda: " + Convert.ToString(idAgenda));
}
else
{
throw new Exception("Não foi possível excluir a MovimentacaoEstoque " + Convert.ToString(idMovimentacaoEstoque));
}
}
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
return(true);
}
public void labeling(int code, string type)
{
cnx.Close();
cnx.Open();
alllabel = new LabelStyle()
{
CollisionDetection = true,
CollisionBuffer = new SizeF(10F, 10F),
Font = new Font(FontFamily.GenericSansSerif, 15),
Halo = new Pen(Color.White, 2)
};
if (type == "antenne")
{
cmd = new n.NpgsqlCommand("create or replace view labelantenne as select num,puissance,emp from antenne where num=" + code + ";", cnx);
cmd.ExecuteNonQuery();
lbanteene = new LabelLayer("lb")
{
LabelColumn = "puissance",
DataSource = new PostGIS(src, "labelantenne", "emp", "num")
};
lbanteene.Style = alllabel;
map1.Map.Layers.Add(lbanteene);
map1.Refresh();
cmd = new n.NpgsqlCommand("drop view labelantenne;", cnx);
cmd.ExecuteNonQuery();
}
else
{
if (type == "quartier")
{
cmd = new n.NpgsqlCommand("create or replace view labelquartier as select code,nom,zone from quartier where code=" + code + ";", cnx);
cmd.ExecuteNonQuery();
lbquartie = new LabelLayer("qrtt")
{
LabelColumn = "nom",
DataSource = new PostGIS(src, "labelquartier", "zone", "code")
};
lbquartie.Style = alllabel;
map1.Map.Layers.Add(lbquartie);
map1.Refresh();
cmd = new n.NpgsqlCommand("drop view labelquartier;", cnx);
cmd.ExecuteNonQuery();
}
}
cnx.Close();
}
public void Alterar(Model_Vo_Cliente pCliente)
{
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = (@"update cliente set nome = @nome, cpf = @cpf, rg = @rg, telefone = @telefone, email = @email, rua = @rua, numero = @numero, bairro = @bairro, complemento = @complemento, cidade = @cidade, cep = @cep, uf = @uf, " +
@"Nascimento = @Nascimento, " +
@"ValorMensalidade = @ValorMensalidade, " +
@"Observacao = @Observacao, " +
@"Ativo = @Ativo " +
@"where id = @id;");
cmd.Parameters.AddWithValue("@id", pCliente.Id);
cmd.Parameters.AddWithValue("@nome", pCliente.Nome);
cmd.Parameters.AddWithValue("@cpf", pCliente.Cpf);
cmd.Parameters.AddWithValue("@rg", pCliente.Rg);
cmd.Parameters.AddWithValue("@telefone", pCliente.Telefone);
cmd.Parameters.AddWithValue("@email", pCliente.Email);
cmd.Parameters.AddWithValue("@rua", pCliente.Rua);
cmd.Parameters.AddWithValue("@numero", pCliente.Numero);
cmd.Parameters.AddWithValue("@bairro", pCliente.Bairro);
cmd.Parameters.AddWithValue("@complemento", pCliente.Complemento);
cmd.Parameters.AddWithValue("@cidade", pCliente.Cidade);
cmd.Parameters.AddWithValue("@cep", pCliente.CEP);
cmd.Parameters.AddWithValue("@uf", pCliente.UF);
cmd.Parameters.AddWithValue("@Nascimento", pCliente.Nascimento);
cmd.Parameters.AddWithValue("@ValorMensalidade", pCliente.ValorMensalidade);
cmd.Parameters.AddWithValue("@Observacao", pCliente.Observacao);
cmd.Parameters.AddWithValue("@Ativo", pCliente.Ativo);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
private static void ApplyPostgresMigration(Stream stream, bool force, DatabaseInfo dbInfo)
{
if (!force)
{
CheckForce(stream);
}
using (var conn = new Npgsql.NpgsqlConnection(dbInfo.ConnectionString))
{
conn.Open();
var com = new Npgsql.NpgsqlCommand(stream);
com.Connection = conn;
com.ExecuteNonQuery();
conn.Close();
}
}
public void Incluir(Model_Vo_Cliente pCliente)
{
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = (@"insert into cliente(nome,cpf,rg,telefone,email,rua,numero,bairro," +
@"complemento,cidade,cep,uf, Nascimento, ValorMensalidade, Observacao, Ativo) " +
@"values " +
@" (@nome,@cpf,@rg,@telefone,@email,@rua,@numero,@bairro," +
@" @complemento,@cidade,@cep,@uf, @Nascimento, @ValorMensalidade, @Observacao, @Ativo);");
cmd.Parameters.AddWithValue("@nome", pCliente.Nome);
cmd.Parameters.AddWithValue("@cpf", pCliente.Cpf);
cmd.Parameters.AddWithValue("@rg", pCliente.Rg);
cmd.Parameters.AddWithValue("@telefone", pCliente.Telefone);
cmd.Parameters.AddWithValue("@email", pCliente.Email);
cmd.Parameters.AddWithValue("@rua", pCliente.Rua);
cmd.Parameters.AddWithValue("@numero", pCliente.Numero);
cmd.Parameters.AddWithValue("@bairro", pCliente.Bairro);
cmd.Parameters.AddWithValue("@complemento", pCliente.Complemento);
cmd.Parameters.AddWithValue("@cidade", pCliente.Cidade);
cmd.Parameters.AddWithValue("@cep", pCliente.CEP);
cmd.Parameters.AddWithValue("@uf", pCliente.UF);
cmd.Parameters.AddWithValue("@Nascimento", pCliente.Nascimento);
cmd.Parameters.AddWithValue("@ValorMensalidade", pCliente.ValorMensalidade);
cmd.Parameters.AddWithValue("@Observacao", pCliente.Observacao);
cmd.Parameters.AddWithValue("@Ativo", pCliente.Ativo);
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
/// <summary>
/// 执行sql update，insert等语句，返回影响行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
int rowsaffected = -1;
try
{
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, conn))
{
rowsaffected = command.ExecuteNonQuery();
}
Console.WriteLine("影响行数：{0}", rowsaffected);
}
catch (Exception ex)
{
Console.WriteLine("插入数据库失败" + ex.Message);
}
return(rowsaffected);
}
private void button5_Click(object sender, EventArgs e)
{
string strconn = string.Format("Server={0};Port={1};User ID={2};Password={3};Database={4}", textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, textBox5.Text);
NpgsqlConnection cnn = new NpgsqlConnection(strconn);
cnn.Open();
Npgsql.NpgsqlCommand myCommand = cnn.CreateCommand();
myCommand.CommandText = "Update udal set p2='Деточкин' WHERE P1=1;";
try
{
myCommand.ExecuteNonQuery();
MessageBox.Show("Выполнено!", "Информация", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
}
finally
{
cnn.Dispose();
}
}
public bool Delete()
{
string sQuery = " DELETE FROM tbm_carrepair WHERE repairid=@rid";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = repairid;
cmd.CommandText = sQuery;
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
}
public void quartier_traitement(int code)
{
cnx.Close();
cnx.Open();
n.NpgsqlCommand cm = new n.NpgsqlCommand("create or replace view buffer_all as select num,ST_buffer(emp,puissance)as bf from antenne;", cnx);
cm.ExecuteNonQuery();
n.NpgsqlCommand cma = new n.NpgsqlCommand("create or replace view quart as select st_intersection(quartier.zone,bf)as inter from quartier,buffer_all where st_intersects(quartier.zone,bf) and quartier.code=" + code + "", cnx);
cma.ExecuteNonQuery();
n.NpgsqlCommand sauvegarde = new n.NpgsqlCommand("CREATE OR REPLACE VIEW cell as select ST_UNION(inter) as geom from quart", cnx);
sauvegarde.ExecuteNonQuery();
n.NpgsqlCommand c = new n.NpgsqlCommand(" select ST_AREA(geom) as valeur from cell", cnx);
double i = 0;
var rea = c.ExecuteReader();
while (rea.Read())
{
i = Double.Parse(rea["valeur"].ToString());
}
n.NpgsqlCommand ca = new n.NpgsqlCommand(" select ST_AREA(zone) as valeur from quartier where code=" + code + "", cnx);
double ia = 0;
var re = ca.ExecuteReader();
while (re.Read())
{
ia = Double.Parse(re["valeur"].ToString());
}
tc.Text = ((i / ia) * 100) + "%";
supc.Text = i + "";
supnc.Text = (ia - i) + "";
n.NpgsqlCommand de = new n.NpgsqlCommand(" select densite as valeur from quartier where code=" + code + "", cnx);
double iaa = 0;
var e = de.ExecuteReader();
while (e.Read())
{
iaa = Double.Parse(e["valeur"].ToString());
}
pc.Text = (iaa * i) + "";
pnc.Text = ((ia - i) * iaa) + "";
}
public async Task Update(Registration entity, DateTimeOffset timeStamp)
{
using (var sqlConnection = new NpgsqlConnection(ConnectionString))
{
sqlConnection.Open();
using (var transaction = sqlConnection.BeginTransaction())
{
try
{
foreach (var entityEvent in entity.NewEvents)
{
var command = "INSERT INTO schooled.Registration (id, version, event_type, event, timestamp) VALUES (@id, @version, @event_type, @event, @timestamp)";
using (var sqlCommand = new Npgsql.NpgsqlCommand(command, sqlConnection))
{
sqlCommand.Parameters.AddWithValue("id", NpgsqlDbType.Uuid,
entity.Id);
sqlCommand.Parameters.AddWithValue("version", NpgsqlDbType.Integer,
entityEvent.Version);
sqlCommand.Parameters.AddWithValue("event_type", NpgsqlDbType.Varchar,
entityEvent.GetType());
sqlCommand.Parameters.AddWithValue("event", NpgsqlDbType.Jsonb,
JsonConvert.SerializeObject(entityEvent));
sqlCommand.Parameters.AddWithValue("timestamp", NpgsqlDbType.TimestampTZ,
timeStamp);
sqlCommand.ExecuteNonQuery();
}
}
await transaction.CommitAsync();
}
catch (Exception e)
{
Console.WriteLine(e);
await transaction.RollbackAsync();
throw;
}
}
}
}
public Boolean Excluir(int id, int piCtaReceb)
{
//conexao
SqlConnection cn = new SqlConnection();
try
{
cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao();
//command
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if ((piCtaReceb > 0))
{
cmd.CommandText = "delete from livrocaixa where idcontasareceber = " + Convert.ToString(piCtaReceb);
}
else
{
cmd.CommandText = "delete from livrocaixa where id = " + Convert.ToString(id);
}
cn.Open();
int resultado = cmd.ExecuteNonQuery();
if (resultado != 1)
{
//throw new Exception("Não foi possível excluir a LivroCaixa " + Convert.ToString(id));
}
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
return(true);
}
public bool SoftDelete()
{
string sQuery = "Update tbm_carrepair set dlt=true,opedit=@opedit,pcedit=@pcedit,luedit=now()" +
"where repairid=@rid";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = repairid;
cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = clsGlobal.strUserName;
cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = System.Windows.Forms.SystemInformation.ComputerName;
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
}
private void button2_Click(object sender, EventArgs e)
{
alabel.Text = "";
c.Text = "";
tc.Text = "";
supc.Text = "";
supnc.Text = "";
pc.Text = "";
pnc.Text = "";
c.Text = Text;
alabel.Text = "";
Nantenne.Text = "";
puissance.Text = "";
s.Text = "";
densite.Text = "";
nbrZone.Text = "";
NC.Text = "";
NCC.Text = "";
cnx.Close();
cnx.Open();
n.NpgsqlCommand caa = new n.NpgsqlCommand(" Create or replace view zonica as select ST_Difference(ST_UNION(quartier.zone),ST_UNION(st_buffer(antenne.emp,puissance))) as valeur from antenne,quartier;", cnx);
caa.ExecuteNonQuery();
VectorLayer nc = new VectorLayer("")
{
DataSource = new PostGIS(src, "zonica", "valeur", null)
};
nc.Style.Fill = Brushes.Red;
nc.Style.Outline = Pens.White;
nc.Style.EnableOutline = true;
map1.Map.Layers.Clear();
map1.Map.Layers.Add(quart);
map1.Map.Layers.Add(ant);
map1.Map.Layers.Add(nc);
map1.Map.ZoomToExtents();
map1.Refresh();
cnx.Close();
c.Text = "";
}
//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();
}
}
