private void cbDatabase_DropDown(object sender, System.EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
cbDatabase.Items.Clear();
cbSchema.Text = "";
PgSqlConnection databaseConnection = new PgSqlConnection();
databaseConnection.Host = cbHost.Text;
databaseConnection.UserId = edUser.Text;
databaseConnection.Password = edPassword.Text;
databaseConnection.Port = (int)edPort.Value;
try {
databaseConnection.Open();
System.Data.IDbCommand command = new PgSqlCommand("SELECT datname FROM pg_database WHERE datallowconn = true and datname <> 'template1'", databaseConnection);
using (System.Data.IDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
cbDatabase.Items.Add(reader[0]);
}
}
}
catch (Exception exception) {
MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally {
Cursor.Current = Cursors.Default;
databaseConnection.Close();
}
}
public void Conexion_DB()
{
StringBuilder v_cadena_conexion = new StringBuilder();
v_cadena_conexion.Append("User Id=");
v_cadena_conexion.Append(Pro_Usuario);
v_cadena_conexion.Append(";Password="******";Host=");
v_cadena_conexion.Append(Pro_host);
v_cadena_conexion.Append(";Database=");
v_cadena_conexion.Append(Pro_BaseDatos);
v_cadena_conexion.Append(";Port=");
v_cadena_conexion.Append(Pro_puerto);
string v_cadena = v_cadena_conexion.ToString();
pgConexion = new PgSqlConnection(v_cadena);
try
{
if (pgConexion.State != ConnectionState.Open)
{
pgConexion.Open();
}
}
catch (Exception e)
{
}
}
static void Main(string[] args)
{
PgSqlConnectionStringBuilder pgCSB = new PgSqlConnectionStringBuilder();
pgCSB.Host = "192.168.1.78";
pgCSB.Port = 5432;
pgCSB.UserId = "postgres";
pgCSB.Password = "******";
pgCSB.Database = "tms2";
pgCSB.MaxPoolSize = 150;
pgCSB.ConnectionTimeout = 30;
pgCSB.Unicode = true;
PgSqlConnection pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString);
try
{
pgSqlConnection.Open();
Count(pgSqlConnection);
}
catch (PgSqlException ex)
{
Console.WriteLine("Exception occurs: {0}", ex.Error);
}
finally
{
pgSqlConnection.Close();
Console.ReadLine();
}
}
public void PGCmd(PgSqlConnection conn, string insertStr)
{
conn.Open();
PgSqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmdPG.Connection = connPG;
cmdPG.CommandText = insertStr;
// PgSqlParameter parm = cmd.CreateParameter();
//parm.ParameterName = "@name";
//parm.Value = "SomeName";
//cmd.Parameters.Add(parm);
cmdPG.Prepare();
try
{
cmdPG.ExecuteScalar();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
tx.Commit();
conn.Close();
}
public IEnumerable <int> Execute(params SqlCommand[] sqlCommands)
{
using (var connection = new PgSqlConnection(_connectionString))
{
var allRecordsAffected = new List <int>();
connection.Open();
connection.BeginTransaction();
try
{
sqlCommands.ToList().ForEach(sqlCommand =>
{
var recordsAffected = connection.Execute(sqlCommand.Sql, sqlCommand.Param);
allRecordsAffected.Add(recordsAffected);
});
connection.Commit();
}
catch
{
connection.Rollback();
throw;
}
finally
{
connection.Close();
}
return(allRecordsAffected);
}
}
public static void RetrieveColumnInformation()
{
PgSqlConnection connection = new PgSqlConnection(Session.ConnectionString);
try
{
connection.Open();
DataTable tables = new DataTable(); //connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
// Print out the columns
Console.WriteLine("\nListing Column Metadata Information ...");
foreach (DataColumn column in tables.Columns)
{
//Console.WriteLine(column);
}
Console.WriteLine("\nListing Columns (TableName : ColumnName format)...");
foreach (DataRow row in tables.Rows)
{
// Console.WriteLine(row["TABLE_NAME"] + " : " + row["COLUMN_NAME"]);
}
}
catch (Exception ex)
{
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
public UsuarioDTO Autentica(UsuarioDTO objUsuario)
{
PgSqlConnection conn = new PgSqlConnection("User Id=postgres;Password=root;host=localhost;database=NIVEL_ACESSO;");
PgSqlCommand cmd = new PgSqlCommand();
cmd.CommandText = string.Format("SELECT * FROM public.\"TRABALHADORES\" WHERE \"LOGIN\" ='{0}' AND \"SENHA\" = '{1}';", objUsuario.Login, objUsuario.Senha);
cmd.Connection = conn;
conn.Open();
int aff = cmd.ExecuteNonQuery();
PgSqlDataReader ER;
UsuarioDTO usuario = new UsuarioDTO();
ER = cmd.ExecuteReader();
if (ER.Read())
{
usuario.Cpf = Convert.ToString(ER["cpf_usu"]);
usuario.Nome = Convert.ToString(ER["nome_usu"]);
usuario.Email = Convert.ToString(ER["email_usu"]);
usuario.Login = Convert.ToString(ER["login_usu"]);
usuario.Senha = Convert.ToString(ER["senha_usu"]);
usuario.NivelDeAcesso = Convert.ToInt16(ER["nivelAcesso_usu"]);
}
conn.Close();
return(usuario);
}
public static DateTime ObtenerHoraServidor(PgSqlConnection pConexion)
{
DateTime v_resultado = Convert.ToDateTime(null);
if (pConexion.State != System.Data.ConnectionState.Open)
{
pConexion.Open();
}
string sentencia = "SELECT * FROM arca_tesoros_conf.ft_view_variables_tiempo();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
v_resultado = pgDr.GetDateTime("fecha_hora_servidor");
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
pgComando = null;
return(v_resultado);
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, "Utilidades.cs", "ObtenerHoraServidor");
return(Convert.ToDateTime(null));
}
}
// Выполняет запрос к базе данных.
protected void ExecuteNonQuery(string commandText)
{
var command = new PgSqlCommand(commandText, _connection);
_connection.Open();
command.ExecuteNonQuery();
_connection.Close();
}
public IEnumerable <T> Query <T>(string sql, object param = null)
{
using (var connection = new PgSqlConnection(_connectionString))
{
connection.Open();
var records = connection.Query <T>(sql, param);
return(records);
}
}
public int Execute(string sql, object param = null)
{
using (var connection = new PgSqlConnection(_connectionString))
{
connection.Open();
var recordsAffected = connection.Execute(sql, param);
return(recordsAffected);
}
}
private async Task RunRealCase(bool openconn)
{
var dbname = "test_db";
CreateDB(dbname);
var customers = GetCustomers().ToList();
//var transaction = db.CreateTransaction();
var sdate = DateTime.Now.ToString("s");
var connection = new PgSqlConnection(GetConnection(dbname));
if (openconn)
{
connection.Open();
}
PgSqlCommand cmd = new PgSqlCommand();
var insertSql = @"INSERT INTO customers(
firstname, lasttname, deleted, createdutc, updatedat, createdatlocalnullable,
createatlocal, version, amount, age) values( @firstname, @lastname, @deleted, @createdutc, @updatedat, @createdatlocalnullable,
@createatlocal, @version, @amount, @age) ";
//Emulate the task scenario by parallel foreach loop
var tasks = new List <Task <Customers> >();
foreach (var i in customers)
{
var command = new CommandDefinition(insertSql, i);
var id = await connection.ExecuteScalarAsync <int>(command).ConfigureAwait(false);
var selectcommand = new CommandDefinition("select * from customers where id=@id", new { id });
var task = connection.QueryFirstOrDefaultAsync <Customers>(selectcommand);
tasks.Add(task);
Console.WriteLine($"Managed Thread Id: {Thread.CurrentThread.ManagedThreadId}");
}
await Task.WhenAll(tasks).ConfigureAwait(false);
}
private void LlamadoTickets()
{
ValidarConexion();
PgSqlConnection vConexion = new PgSqlConnection(Pro_Conexion.ConnectionString);
vConexion.Password = Pro_Conexion.Password;
vConexion.Open();
string sentencia = @"SELECT * FROM area_servicio.ft_view_consulta_llamados_tickets (
:p_agencia_servicio,
:p_cliente_servicio
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, vConexion);
pgComando.Parameters.Add("p_agencia_servicio", PgSqlType.Int).Value = Pro_Sucursal;
pgComando.Parameters.Add("p_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
v_ticket = pgDr.GetString("ticket");
v_posicion = pgDr.GetString("posicion");
v_tipo_ticket = pgDr.GetInt32("tipo_ticket");
v_primera_letra = pgDr.GetString("primera_letra");
v_segunda_letra = pgDr.GetString("segunda_letra");
v_tercera_letra = pgDr.GetString("tercera_letra");
v_cuarta_letra = pgDr.GetString("cuarta_letra");
v_quinta_letra = pgDr.GetString("quinta_letra");
v_sexta_letra = pgDr.GetString("sexta_letra");
v_longitud_ticket = pgDr.GetInt32("longitud_ticket");
ReproducirAudioLlamadoTicket();
}
pgDr.Close();
pgDr = null;
pgComando.Dispose();
vConexion.Close();
vConexion.Dispose();
sentencia = null;
}
catch (Exception Exc)
{
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"LlamadoTickets()");
v_depurador = null;
}
}
static void Main(string[] args)
{
String connectionString = null;
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
PgSqlConnection con;
con = new PgSqlConnection(connectionString);
con.Open();
string sql;
// Text - only has one query (single query behavior)
sql = "select * from pg_tables";
Test(con, sql, CommandType.Text,
CommandBehavior.SingleResult, "Text1");
// Text - only has one query (default behavior)
sql = "select * from pg_tables";
Test(con, sql, CommandType.Text,
CommandBehavior.Default, "Text2");
// Text - has three queries
sql =
"select * from pg_user;" +
"select * from pg_tables;" +
"select * from pg_database";
Test(con, sql, CommandType.Text,
CommandBehavior.Default, "Text3Queries");
// Table Direct
sql = "pg_tables";
Test(con, sql, CommandType.TableDirect,
CommandBehavior.Default, "TableDirect1");
// Stored Procedure
sql = "version";
Test(con, sql, CommandType.StoredProcedure,
CommandBehavior.Default, "SP1");
// Text - test a SQL Command (default behavior)
// Note: this not a SQL Query
sql = "SET DATESTYLE TO 'ISO'";
Test(con, sql, CommandType.Text,
CommandBehavior.Default, "TextCmd1");
con.Close();
}
private void btConnect_Click(object sender, System.EventArgs e)
{
connection.Close();
connection.UserId = edUser.Text;
connection.Password = edPassword.Text;
connection.Host = cbHost.Text;
connection.Port = Convert.ToInt32(edPort.Text);
connection.Database = cbDatabase.Text;
connection.Schema = cbSchema.Text;
try {
Cursor.Current = Cursors.WaitCursor;
connection.Open();
Cursor.Current = Cursors.Default;
DialogResult = DialogResult.OK;
}
catch (PgSqlException exception) {
Cursor.Current = Cursors.Default;
retries--;
if (retries == 0)
{
DialogResult = DialogResult.Cancel;
}
string msg = exception.Message.Trim();
if (msg == "FATAL: user \"" + edUser.Text + "\" does not exist" ||
msg == "FATAL: no PostgreSQL user name specified in startup packet")
{
ActiveControl = edUser;
}
else if (msg == "No such host is known")
{
ActiveControl = cbHost;
}
else if (msg == "No connection could be made because the target machine actively refused it")
{
ActiveControl = edPort;
}
else if (msg == "FATAL: Database \"" + cbDatabase.Text + "\" does not exist in the system catalog.")
{
ActiveControl = cbDatabase;
}
MessageBox.Show(msg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public DbConnection(bool autoconnect = false)
{
conn = new PgSqlConnection();
conn.Host = "localhost";
conn.Port = 5432;
conn.Database = "DataBase";
conn.UserId = "postgres";
conn.Password = "******";
if (autoconnect == true)
{
conn.Open();
}
}
public Neue_Liegenschaft(Form1 Hauptfenster)
{
Verbindung.ConnectionString = "user id = postgres;password = "******";host = " + Hauptfenster.host + ";port = " + Hauptfenster.port.ToString() + ";database = postgres;pooling = true;min pool size = 0;max pool size = 100;connection lifetime = 0;";
while (Verbindung.State.ToString() == "Closed")
{
try
{
Verbindung.Open();
}
catch (Exception)
{
}
}
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
userCLosePG = true;
if (textBox1.Text != "")
{
HF.pw = textBox1.Text;
HF.WriteLine(HF.txt_pfad, 1, textBox1.Text);
}
if (cbx_port.Checked && tbx_port.Text == "")
{
MessageBox.Show("Es wurde kein Port angegeben!");
}
else if (cbx_port.Checked && tbx_port.Text != "")
{
HF.port = tbx_port.Text;
HF.WriteLine(HF.txt_pfad, 5, tbx_port.Text);
}
if (cbx_host.Checked && tbx_host.Text == "")
{
MessageBox.Show("Es wurde kein Host angegeben!");
}
else if (cbx_host.Checked && tbx_host.Text != "")
{
HF.host = tbx_host.Text;
HF.WriteLine(HF.txt_pfad, 4, tbx_host.Text);
}
if (eins && tbx_port.Text != "" && tbx_host.Text != "" && textBox1.Text != "")
{
try
{
Verbindung2.ConnectionString = "user id = postgres;password = "******";host = " + tbx_host.Text + ";port = " + tbx_port.Text + ";database = postgres;pooling = true;min pool size = 0;max pool size = 100;connection lifetime = 0;";
Verbindung2.Open();
string neue_tabelle = "CREATE TABLE liegenschaften (liegenschafts_nr text primary key, strasse text, plz int, ort text)";
PgSqlCommand cmd_erzeuge = new PgSqlCommand(neue_tabelle, Verbindung2);
cmd_erzeuge.ExecuteNonQuery();
string dokumente = "CREATE TABLE dokumente (position serial primary key, name text, string text, format text, datum date, bemerkung text, liegenschafts_nr text, jahr int, dok_typ text)";
PgSqlCommand cmd_erzeuge2 = new PgSqlCommand(dokumente, Verbindung2);
cmd_erzeuge2.ExecuteNonQuery();
Verbindung2.Close();
}
catch (Exception ex)
{
}
}
this.Close();
}
public Neues_Dokument_scannen(Form1 aufrufendes_fenster)
{
Hauptfenster = aufrufendes_fenster;
Verbindung.ConnectionString = "user id = postgres;password = "******";host = " + Hauptfenster.host + ";port = " + Hauptfenster.port.ToString() + ";database = postgres;pooling = true;min pool size = 0;max pool size = 100;connection lifetime = 0;";
while (Verbindung.State.ToString() == "Closed")
{
try
{
Verbindung.Open();
}
catch (Exception)
{
}
}
InitializeComponent();
liegenschafts_nr = Hauptfenster.dataGridView2.CurrentRow.Cells["liegenschafts_nr"].Value.ToString();
}
private void CreateDB(string database = "customer_test")
{
var sql = @"
CREATE TABLE customers (
id SERIAL PRIMARY KEY ,
firstname varchar(100) NOT NULL,
lasttname varchar(100) NOT NULL,
deleted bool DEFAULT false NOT NULL,
CreatedUtc timestamp without time zone default (now() at time zone 'utc') NULL,
UpdatedAt timestamp without time zone default (now() at time zone 'utc') NULL,
CreatedAtLocalNullable timestamp without time zone default (now() at time zone 'utc') NULL,
CreateAtLocal timestamp without time zone default (now() at time zone 'utc') NOT NULL,
version varchar(100) NULL,
Amount numeric(10,2) DEFAULT 0 NOT NULL ,
Age int null
);";
var connection = new PgSqlConnection(DbConnectionString);
connection.Open();//if this line is commented then we will get connection is already open .
//I need to use single connection to use across all these queries to run async // single connection is to manage transaction.
var name = connection.ExecuteScalar <string>(
$"SELECT datname FROM pg_database WHERE datistemplate = false and datname='{database}'");
if (string.IsNullOrWhiteSpace(name))
{
connection.Execute($"create database {database}");
connection.Close();
connection = new PgSqlConnection(GetConnection(database));
connection.Execute(sql);
}
else
{
connection.Close();
}
}
private void CargarDatos()
{
PgSqlConnection v_conexion_temporal = new PgSqlConnection(Pro_Conexion.ConnectionString);
v_conexion_temporal.Password = Pro_Conexion.Password;
v_conexion_temporal.Open();
string sentencia = @"SELECT * FROM area_servicio.ft_view_dashboard_empleados_con_mas_tickets_atendidos(:p_id_cliente_servicio,
:p_id_agencia_servicio,
:p_desde,
:p_hasta);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, v_conexion_temporal);
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio;
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_Agencia_Servicio;
pgComando.Parameters.Add("p_desde", PgSqlType.Date).Value = Pro_Desde;
pgComando.Parameters.Add("p_hasta", PgSqlType.Date).Value = Pro_Hasta;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
lblNombreEmpleado.Text = pgDr.GetString("nombre_empleado");
lblNumeroTicketsAtendidos.Text = pgDr.GetString("numero_tickets_atendidos");
lblSucursalEmpleado.Text = pgDr.GetString("agencia_servicio");
}
pgDr.Close();
pgDr = null;
sentencia = null;
pgComando.Dispose();
v_conexion_temporal.Close();
v_conexion_temporal.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de cargar Dashboard \"EMPLEADO CON MAS TICKETS ATENDIDOS\"." + Exc.Message);
}
}
public DateTime ObtenerHoraServidor(PgSqlConnection pConexion)
{
if (pConexion.State != System.Data.ConnectionState.Open)
{
pConexion.Open();
}
string sentencia = "SELECT * FROM configuracion.ft_obtener_hora_servidor();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
try
{
return((DateTime)pgComando.ExecuteScalar());
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de recuperar la hora del servidor. ");
return(Convert.ToDateTime(null));
}
}
public int IncluirUsuario(UsuarioDTO objUsuarioDTO)
{
PgSqlConnection conn = new PgSqlConnection("User Id=postgres;Password=1254;host=localhost;database=Cadastro;");
PgSqlCommand cmd = new PgSqlCommand();
cmd.CommandText = string.Format("INSERT INTO public.\"tbUsuario\"(\"cpf_usu\", \"senha_usu\", \"nome_usu\",\"login_usu\", \"email_usu\", \"nivelAcesso_usu\") " +
"VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", objUsuarioDTO.Cpf, objUsuarioDTO.Senha, objUsuarioDTO.Nome, objUsuarioDTO.Login, objUsuarioDTO.Email, objUsuarioDTO.NivelDeAcesso);
cmd.Connection = conn;
conn.Open();
int aff = cmd.ExecuteNonQuery();
conn.Close();
return(aff);
}
public IList <UsuarioDTO> CarregarUsuario()
{
PgSqlConnection conn = new PgSqlConnection("User Id=postgres;Password=root;host=localhost;database=NIVEL_ACESSO;");
PgSqlCommand cmd = new PgSqlCommand();
cmd.CommandText = "SELECT * FROM public.\"TRABALHADORES\"";
cmd.Connection = conn;
conn.Open();
int aff = cmd.ExecuteNonQuery();
PgSqlDataReader ER;
IList <UsuarioDTO> listaUsuarioDTO = new List <UsuarioDTO>();
ER = cmd.ExecuteReader();
if (ER.HasRows)
{
while (ER.Read())
{
UsuarioDTO usuario = new UsuarioDTO();
usuario.Cpf = Convert.ToString(ER["CPF"]);
usuario.Nome = Convert.ToString(ER["NOME"]);
usuario.Email = Convert.ToString(ER["EMAIL"]);
usuario.Login = Convert.ToString(ER["LOGIN"]);
usuario.Status = Convert.ToChar(ER["STATUS"]);
usuario.Senha = Convert.ToString(ER["SENHA"]);
usuario.NivelDeAcesso = Convert.ToInt16(ER["NIVEL_DE_ACESSO"]);
listaUsuarioDTO.Add(usuario);
}
}
conn.Close();
return(listaUsuarioDTO);
}
public bool connect()
{
try
{
if (pgSqlConnection != null)
{
pgSqlConnection.Open();
IsConnected = true;
return(true);
}
else
{
return(false);
}
}
catch (PgSqlException ex)
{
Console.WriteLine("Exception occurs: {0}", ex.Error);
return(false);
}
}
/// <summary>
/// Envia uma solicitação para o servidor de Banco de dados para testar a validade da conexão.
/// </summary>
/// <param name="oConn">Objeto de conexão</param>
/// <returns>Verdadeiro caso a conexão seja estabelecida com sucesso.</returns>
public static bool Send(ConexoesDTO oConn)
{
try
{
PgSqlConnection connection = new PgSqlConnection("host=" + oConn.Servidor + ";Port=" + oConn.Porta + ";Database=" + oConn.Banco + ";User="******";Password="******";Unicode=False;Protocol=2");
connection.Open();
if (connection.State == ConnectionState.Open)
{
connection.Close();
return(true);
}
}
catch (Exception ex)
{
MessageBox.Show("Não foi possível efetuar a operação.\n\n" +
"Motivo: " + ex.Message, "Atenção", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(false);
}
public byte[] ConsultaEscalarArregloBytes(string sConsulta)
{
byte[] valor;
try
{
using (PgSqlConnection Con = new PgSqlConnection(this.sConexion))
{
Con.Open();
PgSqlCommand cmd = new PgSqlCommand(sConsulta, Con);
cmd.CommandType = CommandType.Text;
valor = cmd.ExecuteScalar() as byte[];
Con.Close();
}
}
catch (Exception)
{
throw;
}
return(valor);
}
public void ConstruirFormulario(PgSqlConnection pConexion,
int pAgenciaServicio,
int pID_ClienteServicio,
string pNombreAgenciaServicio,
string pIP_Host)
{
Pro_ID_AgenciaServicio = pAgenciaServicio;
Pro_Conexion = pConexion;
Pro_ID_ClienteServicio = pID_ClienteServicio;
Pro_NombreAgenciaServicio = pNombreAgenciaServicio;
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
ctlSeleccionTransaccion1.ConstruirControl(Pro_Conexion,
Pro_ID_AgenciaServicio,
Pro_ID_ClienteServicio,
Pro_NombreAgenciaServicio,
pIP_Host);
}
public static bool ConexionActiva(ref PgSqlConnection pConexion)
{
if (pConexion.State == ConnectionState.Open)
{
return(true);
}
pConexion.Open();
string sentencia_prueba = "SELECT 1";
PgSqlCommand pgComando = new PgSqlCommand(sentencia_prueba, pConexion);
try
{
pgComando.ExecuteNonQuery();
return(true);
}
catch (Exception)
{
PgSqlConnection v_conexion_temporal = new PgSqlConnection(pConexion.ConnectionString);
v_conexion_temporal.Password = pConexion.Password;
int v_intentos = 0;
while (v_intentos++ <= 30)
{
v_conexion_temporal = new PgSqlConnection(pConexion.ConnectionString);
v_conexion_temporal.Password = pConexion.Password;
try
{
v_conexion_temporal.Open();
pConexion = v_conexion_temporal;
return(true);
}
catch (Exception)
{ }
}
}
return(false);
}
private void CargarDatos()
{
PgSqlConnection v_conexion_temporal = new PgSqlConnection(Pro_Conexion.ConnectionString);
v_conexion_temporal.Password = Pro_Conexion.Password;
v_conexion_temporal.Open();
string sentencia = @"SELECT * FROM area_servicio.ft_view_dashboard_promedio_atencion(
:p_id_cliente_servicio,
:p_id_agencia_servicio,
:p_desde,
:p_hasta);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, v_conexion_temporal);
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_ClienteServicio;
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio;
pgComando.Parameters.Add("p_desde", PgSqlType.Date).Value = Pro_Desde;
pgComando.Parameters.Add("p_hasta", PgSqlType.Date).Value = Pro_Hasta;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
lblPromedioAtencion.Text = pgDr.GetString("promedio_atencion");
}
pgDr.Close();
pgDr = null;
sentencia = null;
pgComando.Dispose();
v_conexion_temporal.Close();
v_conexion_temporal.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de cargar Dashboard \"PROMEDIO DE ATENCION\"." + Exc.Message);
}
}
static void Main(string[] args) {
Console.WriteLine("Tests Start.");
Console.WriteLine("Creating PgSqlConnectioin...");
PgSqlConnection cnc = new PgSqlConnection ();
// possible PostgreSQL Provider ConnectionStrings
//string connectionString =
// "Server=hostname;" +
// "Database=database;" +
// "User ID=userid;" +
// "Password=password";
// or
//string connectionString =
// "host=hostname;" +
// "dbname=database;" +
// "user=userid;" +
// "password=password";
string connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
Console.WriteLine("Setting ConnectionString: " +
connectionString);
cnc.ConnectionString = connectionString;
Console.WriteLine("Opening database connection...");
cnc.Open();
Console.WriteLine("Do Tests....");
DoPostgresTest(cnc);
Console.WriteLine("Close database connection...");
cnc.Close();
Console.WriteLine("Tests Done.");
}
public static void Main() {
Console.WriteLine("** Start Test...");
String connectionString = null;
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
PgSqlConnection con;
Console.WriteLine("** Creating connection...");
con = new PgSqlConnection(connectionString);
Console.WriteLine("** opening connection...");
con.Open();
string tableName = "pg_type";
string sql;
sql = "SELECT * FROM PG_TABLES WHERE TABLENAME = :inTableName";
Console.WriteLine("** Creating command...");
PgSqlCommand cmd = new PgSqlCommand(sql, con);
// add parameter for inTableName
Console.WriteLine("** Create parameter...");
PgSqlParameter parm = new PgSqlParameter("inTableName", DbType.String);
Console.WriteLine("** set dbtype of parameter to string");
parm.DbType = DbType.String;
Console.WriteLine("** set direction of parameter to input");
parm.Direction = ParameterDirection.Input;
Console.WriteLine("** set value to the tableName string...");
parm.Value = tableName;
Console.WriteLine("** add parameter to parameters collection in the command...");
cmd.Parameters.Add(parm);
PgSqlDataReader rdr;
Console.WriteLine("** ExecuteReader()...");
rdr = cmd.ExecuteReader();
Console.WriteLine("[][] And now we are going to our results [][]...");
int c;
int results = 0;
do {
results++;
Console.WriteLine("Result Set " + results + "...");
// get the DataTable that holds
// the schema
DataTable dt = rdr.GetSchemaTable();
// number of columns in the table
Console.WriteLine(" Total Columns: " +
dt.Columns.Count);
// display the schema
foreach (DataRow schemaRow in dt.Rows) {
foreach (DataColumn schemaCol in dt.Columns)
Console.WriteLine(schemaCol.ColumnName +
" = " +
schemaRow[schemaCol]);
Console.WriteLine();
}
string output, metadataValue, dataValue;
int nRows = 0;
// Read and display the rows
while(rdr.Read()) {
Console.WriteLine(" Row " + nRows + ": ");
for(c = 0; c < rdr.FieldCount; c++) {
// column meta data
DataRow dr = dt.Rows[c];
metadataValue =
" Col " +
c + ": " +
dr["ColumnName"];
// column data
if(rdr.IsDBNull(c) == true)
dataValue = " is NULL";
else
dataValue =
": " +
rdr.GetValue(c);
// display column meta data and data
output = metadataValue + dataValue;
Console.WriteLine(output);
}
nRows++;
}
Console.WriteLine(" Total Rows: " +
nRows);
} while(rdr.NextResult());
Console.WriteLine("Total Result sets: " + results);
con.Close();
}
static void Main(string[] args) {
String connectionString = null;
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
PgSqlConnection con;
con = new PgSqlConnection(connectionString);
con.Open();
string sql;
// Text - only has one query (single query behavior)
sql = "select * from pg_tables";
Test(con, sql, CommandType.Text,
CommandBehavior.SingleResult, "Text1");
// Text - only has one query (default behavior)
sql = "select * from pg_tables";
Test(con, sql, CommandType.Text,
CommandBehavior.Default, "Text2");
// Text - has three queries
sql =
"select * from pg_user;" +
"select * from pg_tables;" +
"select * from pg_database";
Test(con, sql, CommandType.Text,
CommandBehavior.Default, "Text3Queries");
// Table Direct
sql = "pg_tables";
Test(con, sql, CommandType.TableDirect,
CommandBehavior.Default, "TableDirect1");
// Stored Procedure
sql = "version";
Test(con, sql, CommandType.StoredProcedure,
CommandBehavior.Default, "SP1");
// Text - test a SQL Command (default behavior)
// Note: this not a SQL Query
sql = "SET DATESTYLE TO 'ISO'";
Test(con, sql, CommandType.Text,
CommandBehavior.Default, "TextCmd1");
con.Close();
}
