public static void AccessMaster(string Action, string TableName, Hashtable hash)
{
using(NpgsqlConnection con = new NpgsqlConnection(CONNECTION_STRING))
{
string query = string.Format(@"SELECT * FROM ""{0}""", TableName);
using(NpgsqlCommand cmd = new NpgsqlCommand(@query, con))
{
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = cmd;
NpgsqlCommand actionCommand = null;
if(Action == "Entry"){
actionCommand = (new NpgsqlCommandBuilder(adapter)).GetInsertCommand();
} else if(Action == "Modify"){
actionCommand = (new NpgsqlCommandBuilder(adapter)).GetUpdateCommand();
} else if(Action == "Remove"){
actionCommand = (new NpgsqlCommandBuilder(adapter)).GetDeleteCommand();
}
try {
foreach(NpgsqlParameter param in actionCommand.Parameters){
if(hash[param.SourceColumn] != null)
param.Value = hash[param.SourceColumn];
}
actionCommand.ExecuteNonQuery();
} catch (Exception ex) {
MessageBox.Show(string.Format("{0}", ex.Message));
MessageBox.Show(string.Format("{0}", ex.StackTrace.ToString()));
}
}
}
}
/// <summary>
/// Get all CBS neighbourhoods, specifically its name, boundary (in WKT), and center.
/// </summary>
public void Enhance(IEnumerable<LocationDescription> locationDescriptions)
{
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
var streets = new List<string>();
foreach (var locationDescription in locationDescriptions)
{
var query = string.Format(Query, locationDescription.RdBoundary);
using (var command = new NpgsqlCommand(query, conn))
{
try
{
using (var dr = command.ExecuteReader())
{
while (dr.Read())
{
streets.Add(dr["straat"].ToString());
}
}
}
catch (SystemException e)
{
Console.WriteLine(e.Message);
}
}
locationDescription.Features.Add("straten", string.Join(";", streets));
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string checks;
string checkw;
string id_sali = DropDownList1.SelectedItem.ToString();
string id_wyp = DropDownList2.SelectedItem.ToString();
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from wyp_sali where id_sali = '" + id_sali + "'", conn);
NpgsqlCommand check2 = new NpgsqlCommand("select id_wyp from wyp_sali where id_sali = '" + id_sali + "'and id_wyp = '" + id_wyp + "'", conn);
checks = (String)check1.ExecuteScalar();
checkw = (String)check2.ExecuteScalar();
if (checks == id_sali && checkw == id_wyp)
{
Label2.Text = "";
Label3.Text = "Wyposażenie jest już przypisane do sali!";
}
else if (checkw != id_wyp)
{
NpgsqlCommand add = new NpgsqlCommand("insert into wyp_sali values ('" + id_wyp + "', '" + id_sali + "')", conn);
add.ExecuteScalar();
conn.Close();
Label2.Text = "Dodano wyposażenie do sali!";
Label3.Text = "";
}
}
public void Incluir(Model_Vo_LivroCaixa pLivroCaixa)
{
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 livrocaixa(datahora,descricao,idcontasareceber,tipodemovimento,valor) values (@datahora,@descricao,@idcontasareceber,@tipodemovimento,@valor);";
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();
pLivroCaixa.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public static NpgsqlConnection DBConnection(string serv_adress, string port, string user_id, string pass, string db_name)
//Соединение с базой данных
{
Npgsql.NpgsqlConnection connect = new Npgsql.NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", serv_adress, port, user_id, pass, db_name));
connect.Open();
return(connect);
}
// GET api/values
public IEnumerable<string> Get()
{
// return new string[] { "value1", "value2" };
var result = new List<string>();
using (var conn = new NpgsqlConnection())
{
conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=morty";
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText =
"SELECT name,amenity, ST_AsGeoJson(way) FROM planet_osm_point WHERE amenity = \'pub\' LIMIT 10;";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Add(reader.GetString(2));
}
}
}
}
return result;
}
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 IEnumerable<Routine> GetRoutines()
{
var list = new List<Routine>();
using (var sqlConnection = new NpgsqlConnection(connectionString))
{
using (var cmd = new NpgsqlCommand(@"
SELECT routine_name
FROM INFORMATION_SCHEMA.routines
WHERE
routine_schema <> 'pg_catalog'
and routine_schema <>'information_schema'
", sqlConnection))
{
sqlConnection.Open();
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Routine(reader.GetString(0)));
}
}
}
}
return list;
}
/// <summary>
/// Initialises the estatedata class.
/// </summary>
/// <param name="connectionString">connectionString.</param>
public void Initialise(string connectionString)
{
if (!string.IsNullOrEmpty(connectionString))
{
m_connectionString = connectionString;
_Database = new PGSQLManager(connectionString);
}
//Migration settings
using (NpgsqlConnection conn = new NpgsqlConnection(m_connectionString))
{
conn.Open();
Migration m = new Migration(conn, GetType().Assembly, "EstateStore");
m.Update();
}
//Interesting way to get parameters! Maybe implement that also with other types
Type t = typeof(EstateSettings);
_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in _Fields)
{
if (f.Name.Substring(0, 2) == "m_")
_FieldMap[f.Name.Substring(2)] = f;
}
}
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 static IEnumerable<TerrainService.shPoint> getPolygonPoints(string polygon_guid)
{
List<TerrainService.shPoint> pntsList = new System.Collections.Generic.List<TerrainService.shPoint>();
string sql = "select * from polygon_points where polygon_guid='" + polygon_guid + "' order by point_num";
using (NpgsqlConnection connection = new NpgsqlConnection(strPostGISConnection))
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, connection))
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.Reset();
da.Fill(ds);
dt = ds.Tables[0];
if (dt == null || dt.Rows == null || dt.Rows.Count == 0)
{
return null;
}
foreach (DataRow row in dt.Rows)
{
TerrainService.shPoint mp = new TerrainService.shPoint();
mp.x = System.Convert.ToDouble(row["pointx"]);
mp.y = System.Convert.ToDouble(row["pointy"]);
pntsList.Add(mp);
}
}
return pntsList;
}
//for testing
public void DeletePurchaserDataRecursive(long id)
{
Npgsql.NpgsqlConnection con = DB.DBConnector.GetConnection();
NpgsqlCommand command = new NpgsqlCommand(null, con);
command.CommandText = "DELETE FROM dienstverhaeltnis WHERE agid=@agid";
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
command = new NpgsqlCommand(null, con);
command.CommandText = "DELETE FROM auftraggeberdaten WHERE agid=@agid";
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
command.CommandText = "DELETE FROM auftraggeber WHERE agid=@agid";
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
command.CommandText = "DELETE FROM person WHERE personid=@agid";
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, id);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
con.Close();
}
public void Incluir(Model_Vo_Sala pSala)
{
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 sala(nome,capacidade,tipo,idproduto) values (@nome,@capacidade,@tipo,@idproduto);";
cmd.Parameters.AddWithValue("@nome", pSala.Nome);
cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade);
cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString());
cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto));
cn.Open();
pSala.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
/// <summary>
/// Inherited Abstract method that now overriden in this class to enable authentication of a user.
/// </summary>
public override void execute_action()
{
/// Instantiate a connection object to our PostGreSQL server
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ProudSourceDB"].ConnectionString);
Npgsql.NpgsqlDataAdapter da = new NpgsqlDataAdapter(this.query, conn);
/// prepare the data that will be compared against
da.SelectCommand.Parameters.AddWithValue("@UserName", this.Username);
da.SelectCommand.Parameters.AddWithValue("Password", this.PasswordHash);
System.Data.DataSet ds = new System.Data.DataSet();
try
{
conn.Open();
da.Fill(ds);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(String.Format("Error querying data base, user authentication. Error Data \r\n{0},\r\n{1}\r\n{2}", e.Message, e.InnerException, e.Data));
UserAuthenticated = false;
}
finally
{
conn.Close();
}
/// Check for success or failure
if (ds.Tables[0].Rows.Count == 1)
{
this.UserAuthenticated = true;
}
else
{
this.UserAuthenticated = true;
}
}
public static DBclasses.AsksCategory getAsksCategoriesById(int id)
{
DBclasses.AsksCategory result = new DBclasses.AsksCategory();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from askscategories where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.description = reader["description"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public static DBclasses.KindOfAnimal getKindOfAnimalsById(int id)
{
DBclasses.KindOfAnimal result = new DBclasses.KindOfAnimal();
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
string queue = "select * from kindofanimals where \"id\"=" + id.ToString();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
connection.Open();
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
result.id = id;
result.name = reader["name"].ToString();
result.photo = reader["photo"].ToString();
result.infoURL = reader["infoURL"].ToString();
}
catch (Exception ex)
{
connection.Close();
connection.Dispose();
return(null);
}
}
return(result);
}
public void SetUp()
{
Connection = new NpgsqlConnection(IntegrationTestOptions.Postgres.ConnectionString);
Processor = new PostgresProcessor(Connection, new PostgresGenerator(), new TextWriterAnnouncer(System.Console.Out), new ProcessorOptions(), new PostgresDbFactory());
Quoter = new PostgresQuoter();
Connection.Open();
}
public Guid Maintain_RunNotes(Models.Automation.Test_Run_Notes item, string username)
{
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"SELECT automation.maintain_runnote(@IdNum, @run_IdNum, @testset_idnum,@test_IdNum, @ID,@method_name, @notes, @username, @status);", con);
cmd.Parameters.Add("@IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.idnum;
cmd.Parameters.Add("@run_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.run_idnum;
cmd.Parameters.Add("@testset_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.testset_idnum;
cmd.Parameters.Add("@test_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.test_idnum;
cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.id;
cmd.Parameters.Add("@method_name", NpgsqlTypes.NpgsqlDbType.Text).Value = item.method_name ?? "";
cmd.Parameters.Add("@notes", NpgsqlTypes.NpgsqlDbType.Text).Value = item.notes ?? "";
cmd.Parameters.Add("@username", NpgsqlTypes.NpgsqlDbType.Text).Value = username;
cmd.Parameters.Add("@status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.Status;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Guid IDNUM = new Guid(Convert.ToString(ds.Tables[0].Rows[0]["maintain_runnote"]));
return(IDNUM);
}
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 List <Models.Automation.Test_Run_Notes> GetRunNote(Guid ID)
{
List <Models.Automation.Test_Run_Notes> list = new List <Models.Automation.Test_Run_Notes>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from automation.runnotes where row_idnum = @ID;", con);
cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = ID;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.Automation.Test_Run_Notes item = new Models.Automation.Test_Run_Notes
{
idnum = new Guid(Convert.ToString(DR["row_idnum"])),
run_idnum = new Guid(Convert.ToString(DR["run_idnum"])),
testset_idnum = new Guid(Convert.ToString(DR["testset_idnum"])),
test_idnum = new Guid(Convert.ToString(DR["test_idnum"])),
id = Convert.ToInt32(DR["id"]),
notes = Convert.ToString(DR["notes"]),
method_name = Convert.ToString(DR["method_name"]),
Status = Convert.ToInt32(DR["status"])
};
list.Add(item);
}
return(list);
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="connectionString"></param>
public void Open(string connectionString)
{
//throw new NotImplementedException();
this.Close();
dbc = new NpgsqlConnection(connectionString);
dbc.Open();
}
//Build changelog responsefile
public override void MakeChangeLog(int startChangeId, int count, string dbConnectInfo, string wfsUrl, string changeLogFileName, int datasetId)
{
Logger.Info("MakeChangeLog START");
try
{
if (OptimizedChangeLog.Count == 0)
{
//Connect to postgres database
Npgsql.NpgsqlConnection conn = null;
conn = new NpgsqlConnection(dbConnectInfo);
conn.Open();
//Get max changelogid
Int64 endChangeId = GetMaxChangeLogId(conn, datasetId);
//Prepare query against the changelog table in postgres
Npgsql.NpgsqlCommand command = null;
PrepareChangeLogQuery(conn, ref command, startChangeId, endChangeId, datasetId);
//Execute query against the changelog table and remove unnecessary transactions.
FillOptimizedChangeLog(ref command, ref OptimizedChangeLog, startChangeId);
conn.Close();
}
//Get features from WFS and add transactions to changelogfile
BuildChangeLogFile(count, wfsUrl, startChangeId, changeLogFileName, datasetId);
}
catch (System.Exception exp)
{
Logger.Error(exp, "MakeChangeLog function failed:");
throw new System.Exception("MakeChangeLog function failed", exp);
}
Logger.Info("MakeChangeLog END");
}
private void PrepareChangeLogQuery(Npgsql.NpgsqlConnection conn, ref Npgsql.NpgsqlCommand command, int startChangeId, Int64 endChangeId, int datasetId)
{
Logger.Info("PrepareChangeLogQuery START");
try
{
//20121021-Leg: Correction "endringsid >= :startChangeId"
//20121031-Leg: rad is now lokalId
string sqlSelectGmlIds = "SELECT tabell || '.' || lokalid, type, endringsid FROM " + PDbSchema + ".endringslogg WHERE endringsid >= :startChangeId AND endringsid <= :endChangeId ORDER BY endringsid";
//string sqlSelectGmlIds = "SELECT tabell || '.' || rad, type FROM tilbyder.endringslogg WHERE endringsid >= :startChangeId AND endringsid <= :endChangeId ORDER BY endringsid";
// string sqlSelectGmlIds = "SELECT tabell || '.' || rad, type FROM tilbyder.endringslogg WHERE endringsid > :startChangeId AND endringsid <= :endChangeId ";
command = new NpgsqlCommand(sqlSelectGmlIds, conn);
command.Parameters.Add(new NpgsqlParameter("startChangeId", NpgsqlDbType.Integer));
command.Parameters.Add(new NpgsqlParameter("endChangeId", NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = startChangeId;
command.Parameters[1].Value = endChangeId;
}
catch (System.Exception exp)
{
Logger.ErrorException("PrepareChangeLogQuery function failed:", exp);
throw new System.Exception("PrepareChangeLogQuery function failed", exp);
}
Logger.Info("PrepareChangeLogQuery END");
}
public List <Models.Application> GetApplications()
{
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = @NoParent order by name;", con);
cmd.Parameters.Add("@NoParent", NpgsqlTypes.NpgsqlDbType.Uuid).Value = new Guid();
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
List <Models.Application> LI = new List <Models.Application>();
foreach (DataRow I in ds.Tables[0].Rows)
{
Models.Application application = new Models.Application();
application.id = new Guid(Convert.ToString(I["row_idnumber"]));
application.Name = Convert.ToString(I["name"]);
application.ShortName = Convert.ToString(I["srtname"]);
LI.Add(application);
}
return(LI);
}
public void Incluir(Model_Vo_MovimentacaoEstoque pMovimentacaoEstoque)
{
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 movimentacaoestoque(datahora,idclientesolicitante,idproduto,idreservaorigem,quantidade,valorunitario,valortotal,tipodemovimento) values (@datahora,@idclientesolicitante,@idproduto,@idreservaorigem,@quantidade,@valorunitario,@valortotal,@tipodemovimento);";
cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr(pMovimentacaoEstoque.DataHora, false));
cmd.Parameters.AddWithValue("@idclientesolicitante", pMovimentacaoEstoque.IdClienteSolicitante);
cmd.Parameters.AddWithValue("@idproduto", pMovimentacaoEstoque.IdProduto);
cmd.Parameters.AddWithValue("@idreservaorigem", pMovimentacaoEstoque.IdReservaOrigem);
cmd.Parameters.AddWithValue("@quantidade", pMovimentacaoEstoque.Quantidade);
cmd.Parameters.AddWithValue("@valorunitario", pMovimentacaoEstoque.ValorUnitario);
cmd.Parameters.AddWithValue("@valortotal", pMovimentacaoEstoque.ValorTotal);
cmd.Parameters.AddWithValue("@tipodemovimento", pMovimentacaoEstoque.TipoDeMovimento.ToString());
cn.Open();
pMovimentacaoEstoque.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public List <Models.TestLevels.TestStructures> GetTopStructures()
{
List <Models.TestLevels.TestStructures> list = new List <Models.TestLevels.TestStructures>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = '00000000-0000-0000-0000-000000000000' order by name;", con);
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.TestLevels.TestStructures item = new Models.TestLevels.TestStructures();
item.id = new Guid(Convert.ToString(DR["row_idnumber"]));
item.Name = Convert.ToString(DR["name"]);
item.ShortName = Convert.ToString(DR["srtname"]);
item.Parent_IDNumber = new Guid(Convert.ToString(DR["parent_idnumber"]));
list.Add(item);
}
return(list);
}
void Initialize()
{
string connect = "Server=127.0.0.1;Port=5432;User Id=s;Database=practice;";
var connection = new NpgsqlConnection(connect);
if(connection.State == ConnectionState.Closed) { connection.Open(); }
users = new Table("users", connection);
}
public void Incluir(Model_Vo_MovimentacaoEstoque pMovimentacaoEstoque)
{
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 movimentacaoestoque(datahora,idclientesolicitante,idproduto,idreservaorigem,quantidade,valorunitario,valortotal,tipodemovimento) values (@datahora,@idclientesolicitante,@idproduto,@idreservaorigem,@quantidade,@valorunitario,@valortotal,@tipodemovimento);";
cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr(pMovimentacaoEstoque.DataHora, false));
cmd.Parameters.AddWithValue("@idclientesolicitante", pMovimentacaoEstoque.IdClienteSolicitante);
cmd.Parameters.AddWithValue("@idproduto", pMovimentacaoEstoque.IdProduto);
cmd.Parameters.AddWithValue("@idreservaorigem", pMovimentacaoEstoque.IdReservaOrigem);
cmd.Parameters.AddWithValue("@quantidade", pMovimentacaoEstoque.Quantidade);
cmd.Parameters.AddWithValue("@valorunitario", pMovimentacaoEstoque.ValorUnitario);
cmd.Parameters.AddWithValue("@valortotal", pMovimentacaoEstoque.ValorTotal);
cmd.Parameters.AddWithValue("@tipodemovimento", pMovimentacaoEstoque.TipoDeMovimento.ToString());
cn.Open();
pMovimentacaoEstoque.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
private void _connection_Disposed(object sender, EventArgs e)
{
// TODO: what happens if this is called from another thread?
// connections should not be shared across threads while in a transaction
_connection.Disposed -= new EventHandler(_connection_Disposed);
_connection = null;
}
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();
}
}
public pgWorker(int pos, string dsn,
ProgressChangedEventHandler onProgress,
RunWorkerCompletedEventHandler onComplete)
{
this.thNo = pos;
this.pg_conn = new NpgsqlConnection(dsn);
try
{
pg_conn.Open();
pg_error = "";
}
catch (Exception ex)
{
pg_conn = null;
pg_error = ex.Message;
}
if (pg_conn != null)
{
this.bw = new BackgroundWorker();
this.bw.WorkerReportsProgress = true;
this.bw.WorkerSupportsCancellation = true;
this.bw.DoWork += this.bwDoWork;
this.bw.ProgressChanged += onProgress;
this.bw.RunWorkerCompleted += onComplete;
}
}
public void Incluir(Model_Vo_Agenda pAgenda)
{
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 agenda(datahorareserva,idcliente,idsala) values (@datahorareserva,@idcliente,@idsala);";
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();
pAgenda.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public PostgresProcessor(NpgsqlConnection connection, IMigrationGenerator generator, IAnnouncer announcer, IMigrationProcessorOptions options)
: base(generator, announcer, options)
{
Connection = connection;
connection.Open();
Transaction = connection.BeginTransaction();
}
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();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string check;
string id_wyp = TextBox1.Text;
string nazwa = TextBox2.Text;
string opis = TextBox3.Text;
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check1 = new NpgsqlCommand("select id_wyp from wyposazenie where id_wyp = '" + id_wyp + "'", conn);
check = (String)check1.ExecuteScalar();
if (check != id_wyp)
{
NpgsqlCommand add = new NpgsqlCommand("insert into wyposazenie values ('" + id_wyp + "', '" + nazwa + "', '" + opis + "')", conn);
add.ExecuteScalar();
conn.Close();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
Label2.Text = "Dodano wyposażenie!";
Label3.Text = "";
}
else if (check == id_wyp)
{
Label2.Text = "";
Label3.Text = "Wyposażenie już istnieje!";
}
}
private PostgreSQLDBManager()
{
_connection = new NpgsqlConnection(Configuration.Settings("PostgreSQLDBConnectionString",
"User ID=postgres;Password=Passw0rd;Host=localhost;Port=5432;Database=postgres;Pooling=true;"));
try
{
_connection.Open();
}
catch (Exception exception)
{
Log.Error(ExceptionHelper.FormatStackTrace("Connect to PostgreSQL failed.",exception));
throw new RuntimeBinderException("Connect to Database Failed",exception);
}
var cmd = new NpgsqlCommand("select count(*) from content", _connection);
var count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
return;
var rootId = Configuration.Settings("Root", "42c5eb51-0e1c-4de1-976d-733bde24220a");
CreateSubItem(rootId, "Folder", "Data");
CreateSubItem(rootId, "Folder", "UI");
CreateSubItem(rootId, "Folder", "Translation");
CreateSubItem(rootId, "Folder", "Project");
CreateSubItem(rootId, "Folder", "Result");
CreateSubItem("", "Project", "Root", rootId);
}
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;
}
}
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();
}
}
}
}
}
}
public TableInfo GetTableInfo(string ConnectionString, string schema, string table_name)
{
var ret = new TableInfo();
var sql = @"SELECT column_name, column_default,is_nullable,data_type,character_maximum_length
FROM information_schema.columns
WHERE table_schema = :schema
AND table_name = :table";
var cnn = new Npgsql.NpgsqlConnection(ConnectionString);
var cmd = new Npgsql.NpgsqlCommand("select * from " + Globals.Compiler.GetQName(schema, table_name), cnn);
//cmd.Parameters.Add(new NpgsqlParameter()
//{
// ParameterName="schema",
// Value=schema
//});
//cmd.Parameters.Add(new NpgsqlParameter()
//{
// ParameterName = "table",
// Value = table_name
//});
DataTable tbl = new DataTable();
//var adp = new Npgsql.NpgsqlDataAdapter(cmd);
try
{
cnn.Open();
tbl = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly).GetSchemaTable();
//adp.Fill(tbl);
}
catch (Exception ex)
{
cnn.Close();
throw (ex);
}
finally
{
cnn.Close();
}
ret.Columns = tbl.Rows.Cast <DataRow>().Select(p => new ColumInfo()
{
Name = p["ColumnName"].ToString(),
IsUnique = (p["IsUnique"] == DBNull.Value) ? false : (bool)p["IsUnique"],
IsAutoIncrement = (p["IsAutoIncrement"] == DBNull.Value) ? false : (bool)p["IsAutoIncrement"],
IsKey = (p["IsKey"] == DBNull.Value) ? false : (bool)p["IsKey"],
AllowDBNull = (p["AllowDBNull"] == DBNull.Value) ? false : (bool)p["AllowDBNull"],
IsReadOnly = (p["IsReadOnly"] == DBNull.Value) ? false : (bool)p["IsReadOnly"],
IsExpression = (p["IsReadOnly"] == DBNull.Value) ? false : (bool)p["IsReadOnly"],
IsIdentity = (p["IsIdentity"] == DBNull.Value) ? false : (bool)p["IsIdentity"],
DataType = p["DataType"],
ColumnSize = (int)p["ColumnSize"]
//DefaultValue = p["column_default"].ToString(),
//IsAuto = p["column_default"].ToString().Split("(")[0] == "nextval",
//AutoConstraint = ((p["column_default"].ToString().Split("(")[0] == "nextval") ? p["column_default"].ToString().Split("(")[1].Split("::")[0] : "")
}).ToList();
return(ret);
}
public void TestDataRowToPOCO()
{
using var dbConnection =
new Npgsql.NpgsqlConnection("Host=localhost;Port=5432;Username=yim;Database=yim;Password=;");
dbConnection.Open();
var command = dbConnection.CreateCommand();
command.CommandText = "select p.* from public.\"Person\" as p";
var sqlAdapter = new NpgsqlDataAdapter(command);
var dataTable = new DataTable();
sqlAdapter.Fill(dataTable);
dbConnection.Close();
var person = dataTable.ToEntities <Person>();
person.ToList().ForEach(p =>
{
_testOutputHelper.WriteLine(p.Name);
_testOutputHelper.WriteLine(p.Adult.ToString());
_testOutputHelper.WriteLine(p.Age.ToString());
});
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
string user_id = TextBox1.Text;
string check;
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;");
conn.Open();
NpgsqlCommand check_user = new NpgsqlCommand("select id_prac from pracownicy where id_prac = '" + user_id + "'", conn);
check = (String)check_user.ExecuteScalar();
if (check == user_id)
{
NpgsqlCommand delete = new NpgsqlCommand("delete from pracownicy where id_prac = '" + user_id + "'", conn);
delete.ExecuteScalar();
TextBox1.Text = "";
TextBox2.Text = "";
Label3.Text = "Użytkownik został usunięty!";
Label2.Text = "";
}
else if (check != user_id)
{
Label2.Text = "Brak użytkownika w bazie!";
Label3.Text = "";
}
}
catch
{
}
}
public async Task should_create_new_database_and_allow_exec_query_2()
{
// PREPARE
var dbName = "test_db_2";
var cmdBuilder = new StringBuilder();
cmdBuilder.AppendLine($"CREATE DATABASE {dbName}");
cmdBuilder.AppendLine("CONNECTION LIMIT = -1");
// RUN
using (var conn = new Npgsql.NpgsqlConnection(_fixture.ConnectionString))
{
await conn.OpenAsync();
var cmd = new NpgsqlCommand(cmdBuilder.ToString(), conn);
cmd.ExecuteNonQuery();
// ASSERT
var dbExists = await new NpgsqlCommand($"SELECT datname FROM pg_catalog.pg_database WHERE datname = '{dbName}'", conn)
.ExecuteScalarAsync();
Assert.NotNull(dbExists);
}
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost; Database=dbprueba; Id=javi; password=sistemas";
NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString);
NpgsqlCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * from articulo";
NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter);
dbDataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dbDataAdapter.Fill (dataSet);
Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count");
foreach (DataTable dataTable in dataSet.Tables){
show (dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["nombre"] = DateTime.Now.ToString();
Console.WriteLine("Tabla con los cambios:");
show (dataSet.Tables[0]);
// dbDataAdapter.RowUpdated +- delegate(object dbDadapterSender, NpgsqlRowUpdatingEventArgs eventArgs){
// Console.WriteLine("RowUpdating command.CommandText= {0}", EventArgs.Command.CommantText);
//
// foreach (IDataParameter dataParameter in EventArgs.Command.Parameters)
// Console.WriteLine ("{0}={1}", dataParameter.ParameterName, dataParameter.Value);
};
dbDataAdapter.Update(dataSet.Tables[0]);
}
public void Incluir(Model_Vo_Produto pProduto)
{
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 produto(descricao,unidade,estoque,valordevenda,observacao) values (@descricao,@unidade,@estoque,@valordevenda,@observacao);";
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);
cn.Open();
pProduto.Id = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw new Exception("Servidor SQL Erro:" + ex.Number);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cn.Close();
}
}
public Sequence(NpgsqlConnection conn,RichTextBox t)
{
InitializeComponent();
this.conn = conn;
this.t = t;
comboBox1.Items.Add("CACHE");
comboBox1.Items.Add("NO CACHE");
PC = new Postgres_Connection();
richTextBox1.StyleResetDefault();
richTextBox1.Styles[Style.Default].Font = "Consolas";
richTextBox1.Styles[Style.Default].Size = 10;
richTextBox1.StyleClearAll();
richTextBox1.Styles[Style.Cpp.Default].ForeColor = Color.Silver;
richTextBox1.Styles[Style.Cpp.Comment].ForeColor = Color.FromArgb(0, 128, 0); // Green
richTextBox1.Styles[Style.Cpp.CommentLine].ForeColor = Color.FromArgb(0, 128, 0); // Green
richTextBox1.Styles[Style.Cpp.CommentLineDoc].ForeColor = Color.FromArgb(128, 128, 128); // Gray
richTextBox1.Styles[Style.Cpp.Number].ForeColor = Color.Red;
richTextBox1.Styles[Style.Cpp.Word].ForeColor = Color.Blue;
richTextBox1.Styles[Style.Cpp.Word2].ForeColor = Color.Fuchsia;
richTextBox1.Styles[Style.Cpp.String].ForeColor = Color.FromArgb(163, 21, 21); // Red
richTextBox1.Styles[Style.Cpp.Character].ForeColor = Color.FromArgb(163, 21, 21); // Red
richTextBox1.Styles[Style.Cpp.Verbatim].ForeColor = Color.FromArgb(163, 21, 21); // Red
richTextBox1.Styles[Style.Cpp.Operator].ForeColor = Color.Silver;
richTextBox1.Styles[Style.Cpp.Preprocessor].ForeColor = Color.Purple;
richTextBox1.Lexer = Lexer.Cpp;
richTextBox1.Margins[0].Width = 16;
//richTextBox1.SetKeywords(0, "abort absolute access action add admin after aggregate all also alter always analyse analyze and any array as asc assertion assignment asymmetric at attribute authorization backward before begin between bigint binary bit boolean both by cache called cascade cascaded case cast catalog chain char character characteristics check checkpoint class close cluster coalesce collate collation column comment comments commit committed concurrently configuration connection constraint constraints content continue conversion copy cost create cross csv current current_catalog current_date current_role current_schema current_time current_timestamp current_user cursor cycle data database day deallocate dec decimal declare default defaults deferrable deferred definer delete delimiter delimiters desc dictionary disable discard distinct do document domain double drop each else enable encoding encrypted end enum escape event except exclude excluding exclusive execute exists explain extension external extract false family fetch filter first float following for force foreign forward freeze from full function functions global grant granted greatest group handler having header hold hour identity if ilike immediate immutable implicit in including increment index indexes inherit inherits initially inline inner inout input insensitive insert instead int integer intersect interval into invoker is isnull isolation join key label language large last lateral lc_collate lc_ctype leading leakproof least left level like limit listen load local localtime localtimestamp location lock mapping match materialized maxvalue minute minvalue mode month move name names national natural nchar next no none not nothing notify notnull nowait null nullif nulls numeric object of off offset oids on only operator option options or order ordinality out outer over overlaps overlay owned owner parser partial partition passing password placing plans position preceding precision prepare prepared preserve primary prior privileges procedural procedure program quote range read real reassign recheck recursive ref references refresh reindex relative release rename repeatable replace replica reset restart restrict returning returns revoke right role rollback row rows rule savepoint schema scroll search second security select sequence sequences serializable server session session_user set setof share show similar simple smallint snapshot some stable standalone start statement statistics stdin stdout storage strict strip substring symmetric sysid system table tables tablespace temp template temporary text then time timestamp to trailing transaction treat trigger trim true truncate trusted type types unbounded uncommitted unencrypted union unique unknown unlisten unlogged until user using vacuum valid validate validator value values varchar variadic varying verbose version view views volatile when where whitespace window with within without work wrapper write xml xmlattributes xmlconcat xmlelement xmlexists xmlforest xmlparse xmlpi xmlroot xmlserialize year yes zone ABORT ABSOLUTE ACCESS ACTION ADD ADMIN AFTER AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC ASSERTION ASSIGNMENT ASYMMETRIC AT ATTRIBUTE AUTHORIZATION BACKWARD BEFORE BEGIN BETWEEN BIGINT BINARY BIT BOOLEAN BOTH BY CACHE CALLED CASCADE CASCADED CASE CAST CATALOG CHAIN CHAR CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS CONTENT CONTINUE CONVERSION COPY COST CREATE CROSS CSV CURRENT CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE DATA DATABASE DAY DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE DELIMITER DELIMITERS DESC DICTIONARY DISABLE DISCARD DISTINCT DO DOCUMENT DOMAIN DOUBLE DROP EACH ELSE ENABLE ENCODING ENCRYPTED END ENUM ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTENSION EXTERNAL EXTRACT FALSE FAMILY FETCH FILTER FIRST FLOAT FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS GLOBAL GRANT GRANTED GREATEST GROUP HANDLER HAVING HEADER HOLD HOUR IDENTITY IF ILIKE IMMEDIATE IMMUTABLE IMPLICIT IN INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE INNER INOUT INPUT INSENSITIVE INSERT INSTEAD INT INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION JOIN KEY LABEL LANGUAGE LARGE LAST LATERAL LC_COLLATE LC_CTYPE LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK MAPPING MATCH MATERIALIZED MAXVALUE MINUTE MINVALUE MODE MONTH MOVE NAME NAMES NATIONAL NATURAL NCHAR NEXT NO NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL NULLIF NULLS NUMERIC OBJECT OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR ORDER ORDINALITY OUT OUTER OVER OVERLAPS OVERLAY OWNED OWNER PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POSITION PRECEDING PRECISION PREPARE PREPARED PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROGRAM QUOTE RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFRESH REINDEX RELATIVE RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE SAVEPOINT SCHEMA SCROLL SEARCH SECOND SECURITY SELECT SEQUENCE SEQUENCES SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE SHOW SIMILAR SIMPLE SMALLINT SNAPSHOT SOME STABLE STANDALONE START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT STRIP SUBSTRING SYMMETRIC SYSID SYSTEM TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT THEN TIME TIMESTAMP TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE TRUNCATE TRUSTED TYPE TYPES UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED UNTIL USER USING VACUUM VALID VALIDATE VALIDATOR VALUE VALUES VARCHAR VARIADIC VARYING VERBOSE VERSION VIEW VIEWS VOLATILE WHEN WHERE WHITESPACE WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE XML XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLPARSE XMLPI XMLROOT XMLSERIALIZE YEAR YES ZONE");
//richTextBox1.SetKeywords(1, "update UPDATE");
}
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 geosyncEntities p_db;
public override string GetLastIndex(int datasetId)
{
Int64 endChangeId = 0;
try
{
//Connect to postgres database
Npgsql.NpgsqlConnection conn = null;
//logger.Info("PostGISChangelog.GetLastIndex" + " NpgsqlConnection:{0}", p_db.Datasets.);
conn = new NpgsqlConnection(PDbConnectInfo);
conn.Open();
//Get max changelogid
endChangeId = GetMaxChangeLogId(conn, datasetId);
conn.Close();
Logger.Info("GetLastIndexResponse endChangeId :{0}{1}", "\t", endChangeId);
}
catch (System.Exception exp)
{
Logger.ErrorException("GetLastIndex Exception:", exp);
throw new System.Exception("GetLastIndex function failed", exp);
}
return(endChangeId.ToString());
}
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 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();
}
}
}
private string GetForeignKeyReferenceTableName(string selectedTableName, string columnName)
{
var conn = new Npgsql.NpgsqlConnection(connectionStr);
conn.Open();
using (conn)
{
NpgsqlCommand tableCommand = conn.CreateCommand();
tableCommand.CommandText = String.Format(
@"
select pk.table_name
from information_schema.referential_constraints c
inner join information_schema.table_constraints fk on c.constraint_name = fk.constraint_name
inner join information_schema.table_constraints pk on c.unique_constraint_name = pk.constraint_name
inner join information_schema.key_column_usage cu on c.constraint_name = cu.constraint_name
inner join (
select i1.table_name, i2.column_name
from information_schema.table_constraints i1
inner join information_schema.key_column_usage i2 on i1.constraint_name = i2.constraint_name
where i1.constraint_type = 'PRIMARY KEY'
) pt on pt.table_name = pk.table_name
where fk.table_name = '{0}' and cu.column_name = '{1}'",
selectedTableName, columnName);
object referencedTableName = tableCommand.ExecuteScalar();
return((string)referencedTableName);
}
}
private void fillComboBox()
{
CellRenderer cellRenderer = new CellRendererText();
comboBox.PackStart(cellRenderer, false); //expand=false
comboBox.AddAttribute (cellRenderer, "text", 1);
ListStore listStore = new ListStore(typeof(string), typeof(string));
comboBox.Model = listStore;
string connectionString = "Server=localhost;Database=PruebaBD;User Id=ximo;Password=admin";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
dbConnection.Open ();
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = "select id, nombre from categoria";
IDataReader dataReader = dbCommand.ExecuteReader();
while (dataReader.Read ())
listStore.AppendValues (dataReader["id"].ToString (), dataReader["nombre"].ToString () );
dataReader.Close ();
dbConnection.Close ();
}
private static void TruncateAndCopy(string table, string[] columns, BlockingCollection <object[]> data, Action <string> log)
{
try
{
log("Connecting to database");
using (var c = new Npgsql.NpgsqlConnection("Host=/var/run/postgresql;Port=5433"))
{
c.Open();
using (var t = c.BeginTransaction())
{
log($"TRUNCATE {Q(table)}");
using (var cmd = new NpgsqlCommand($"TRUNCATE {Q(table)}", c))
cmd.ExecuteNonQuery();
log($"COPY {Q(table)}");
Copy(
c,
tablename: table,
colnames: columns,
data: data.GetConsumingEnumerable()
);
log("COMMIT");
t.Commit();
}
}
}
finally
{
log("Disposing data...");
data.CompleteAdding();
data.Dispose();
}
}
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;
}
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
{
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) ||
(isolation == IsolationLevel.Snapshot))
{
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.ExecuteBlind();
_conn.Connector.Transaction = this;
}
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;
}
}
}
// Zu Quell- und Zieldatenbank verbinden
private void connectFischFaunaButton_Click(object sender, EventArgs e)
{
DatabaseConnection sourceCon = (DatabaseConnection)sourceDatabaseConnetions.SelectedValue;
DatabaseConnection targetCon = (DatabaseConnection)targetDatabaseConnetions.SelectedValue;
NpgsqlConnectionStringBuilder pgConStrBuilder = new NpgsqlConnectionStringBuilder();
pgConStrBuilder.Host = sourceCon.ServerAddress;
pgConStrBuilder.UserName = sourceCon.UserName;
pgConStrBuilder.Password = sourceCon.Password;
pgConStrBuilder.Database = sourceCon.Database;
MySqlConnectionStringBuilder mySqlConStrBuilder = new MySqlConnectionStringBuilder();
mySqlConStrBuilder.Server = targetCon.ServerAddress;
mySqlConStrBuilder.UserID = targetCon.UserName;
mySqlConStrBuilder.Password = targetCon.Password;
mySqlConStrBuilder.Database = targetCon.Database;
mySqlConStrBuilder.AllowZeroDateTime = true;
_sourceCon = new NpgsqlConnection(pgConStrBuilder.ToString());
_targetCon = new MySqlConnection(mySqlConStrBuilder.ToString());
_mainLogic = new MainLogic(_sourceCon, _targetCon);
_mainLogic.CheckForImportedFieldInMySql();
FillImportsCombobox();
FillImportUsersCombobox();
FillRecordQualityCombobox();
FillSourceTypeCombobox();
FillCountryCombobox();
PreSelectTestData();
groupBox2.Enabled = true;
}
protected void SetUp()
{
//NpgsqlEventLog.Level = LogLevel.None;
//NpgsqlEventLog.Level = LogLevel.Debug;
//NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";
_conn = new NpgsqlConnection (TestConfiguration.NpgsqlConnectionString);
}
public static void Main(String[] args)
{
NpgsqlConnection conn = null;
try
{
conn = new NpgsqlConnection(NpgsqlTests.getConnectionString());
conn.Open();
Console.WriteLine("Connection completed");
NpgsqlCommand command = new NpgsqlCommand();
command.CommandText = "select count(*) from tablea";
command.Connection = conn;
Object result = command.ExecuteScalar();
Console.WriteLine(result.ToString());
}
catch(NpgsqlException e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (conn != null)
conn.Close();
}
}
public override IDbConnection GetIDbConnection(IConnectionInfo cxInfo)
{
var connectionString = cxInfo.GetPostgreSqlConnectionString();
var connection = new NpgsqlConnection(connectionString);
return connection;
}
public List <Models.TestSet.Test_Set> GetTestSet(Guid ID)
{
List <Models.TestSet.Test_Set> list = new List <Models.TestSet.Test_Set>();
con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin);
cmd = new NpgsqlCommand(@"select * from qadata.ref_testset where row_idnumber = @ID;", con);
cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = ID;
da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow DR in ds.Tables[0].Rows)
{
Models.TestSet.Test_Set item = new Models.TestSet.Test_Set
{
Id = new Guid(Convert.ToString(DR["row_idnumber"])),
Title = Convert.ToString(DR["testset_title"]),
Description = Convert.ToString(DR["testset_description"]),
testset_id = Convert.ToInt32(DR["testset_id"]),
Status = Convert.ToInt32(DR["status"])
};
list.Add(item);
}
return(list);
}
