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 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 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 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();
}
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 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();
}
}
}
// Выполняет запрос к базе данных.
protected void ExecuteNonQuery(string commandText)
{
var command = new PgSqlCommand(commandText, _connection);
_connection.Open();
command.ExecuteNonQuery();
_connection.Close();
}
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;
}
}
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);
}
}
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 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();
}
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();
}
}
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);
}
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 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 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);
}
/// <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 bool disconnect()
{
try
{
if (pgSqlConnection != null)
{
pgSqlConnection.Close();
IsConnected = false;
return(true);
}
else
{
return(false);
}
}
catch (PgSqlException ex)
{
Console.WriteLine("Exception occurs: {0}", ex.Error);
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.");
}
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_tickets_atendidos_no_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_Cliente_Servicio;
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_Agencia_Servicio;
pgComando.Parameters.Add("p_desde", PgSqlType.Date).Value = Pro_Desde;
pgComando.Parameters.Add("p_hasta", PgSqlType.Date).Value = Pro_Hasta;
try
{
dsDashboards.dtTicketsAtendidos_NoAtendidos.Clear();
new PgSqlDataAdapter(pgComando).Fill(dsDashboards.dtTicketsAtendidos_NoAtendidos);
chrt_TicketsAtendidos_NoAtendidos.Show();
chrt_TicketsAtendidos_NoAtendidos.RefreshData();
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 el dashboard \"TICKETS ATENDIDOS Y NO ATENDIDOS\". " + Exc.Message);
}
}
private void CargarColaTickets()
{
ValidarConexion();
PgSqlConnection vConexion = new PgSqlConnection(Pro_Conexion.ConnectionString);
vConexion.Password = Pro_Conexion.Password;
vConexion.Open();
string sentencia = @"SELECT * FROM area_servicio.ft_view_cola_tickets (
:p_cliente_servicio,
:p_agencia_servicio
);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, vConexion);
pgComando.Parameters.Add("p_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente;
pgComando.Parameters.Add("p_agencia_servicio", PgSqlType.Int).Value = Pro_Sucursal;
try
{
dsTicketsPosiciones1.dtTicketsPosiciones.Clear();
new PgSqlDataAdapter(pgComando).Fill(dsTicketsPosiciones1.dtTicketsPosiciones);
sentencia = null;
pgComando.Dispose();
vConexion.Close();
vConexion.Dispose();
}
catch (Exception Exc)
{
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"CargarColaTickets()");
v_depurador = null;
}
}
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_visitas_segun_prioridad_servicio(
: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
{
dsDashboards1.dtVisitasSegunPrioridadServicio.Clear();
new PgSqlDataAdapter(pgComando).Fill(dsDashboards1.dtVisitasSegunPrioridadServicio);
chartControl1.Show();
chartControl1.RefreshData();
sentencia = null;
pgComando.Dispose();
v_conexion_temporal.Close();
v_conexion_temporal = null;
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de cargar dashboard \"VISITAS SEGUN PRIORIDAD DE SERVICIO\". " + Exc.Message);
}
}
/// <summary>
/// Consulta que retorna in valor del tipo que se defina en su ejecución
/// </summary>
/// <param name="sConsulta">Query</param>
/// <returns>valor de tipo que fue declarado en la ejecución del método</returns>
public T ConsultaEscalar <T>(string sConsulta)
{
T valorDeRetorno = default(T);
try
{
using (PgSqlConnection Con = new PgSqlConnection(this.sConexion))
{
Con.Open();
PgSqlCommand cmd = new PgSqlCommand(sConsulta, Con);
cmd.CommandType = CommandType.Text;
object valor = cmd.ExecuteScalar();
TryParse(valor, out valorDeRetorno);
Con.Close();
}
}
catch (Exception)
{
throw;
}
return(valorDeRetorno);
}
private void cbSchema_DropDown(object sender, System.EventArgs e)
{
cbSchema.Items.Clear();
if (cbDatabase.Text.Length == 0)
{
return;
}
Cursor.Current = Cursors.WaitCursor;
PgSqlConnection databaseConnection = new PgSqlConnection();
databaseConnection.Host = cbHost.Text;
databaseConnection.UserId = edUser.Text;
databaseConnection.Password = edPassword.Text;
databaseConnection.Port = (int)edPort.Value;
databaseConnection.Database = cbDatabase.Text;
try {
databaseConnection.Open();
DataTable schemasTab = databaseConnection.GetSchema("Schemas");
foreach (DataRow row in schemasTab.Rows)
{
if (row["nsptyp"] != null && row["nsptyp"] != DBNull.Value && (int)row["nsptyp"] == 2)
{
cbSchema.Items.Add(row["name"]);
}
}
}
catch (Exception exception) {
MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally {
Cursor.Current = Cursors.Default;
databaseConnection.Close();
}
}
public bool disconnect()
{
try
{
if (pgSqlConnection != null)
{
pgSqlConnection.Close();
IsConnected = false;
return(true);
}
else
{
return(false);
}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Disconnect exception occurs: {0}", ex.Error);
log.Error("Disconnect exception occurs: " + ex.Error);
Console.ResetColor();
return(false);
}
}
public void Close()
{
conn.Close();
}
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();
}
public void modify(string cmd)
{
PgSqlCommand command = null;
PgSqlTransaction myTrans = null;
using (PgSqlConnection pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString))
try
{
{
//insert
command = pgSqlConnection.CreateCommand();
command.UnpreparedExecute = true;
command.CommandText = cmd;
//command.CommandTimeout = 30;
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
//pgSqlConnection.BeginTransaction();
//async
int RowsAffected;
lock (accessLock)
{
pgSqlConnection.Open();
myTrans = pgSqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = myTrans;
//IAsyncResult cres = command.BeginExecuteNonQuery();
//RowsAffected = command.EndExecuteNonQuery(cres);
//lock (accessLock)
RowsAffected = command.ExecuteNonQuery();
myTrans.Commit();
pgSqlConnection.Close();
}
//IAsyncResult cres=command.BeginExecuteNonQuery(null,null);
//Console.Write("In progress...");
//while (!cres.IsCompleted)
//{
//Console.Write(".");
//Perform here any operation you need
//}
/*
* if (cres.IsCompleted)
* Console.WriteLine("Completed.");
* else
* Console.WriteLine("Have to wait for operation to complete...");
*/
//int RowsAffected = command.EndExecuteNonQuery(cres);
//Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
//sync
//int aff = command.ExecuteNonQuery();
//Console.WriteLine(RowsAffected + " rows were affected.");
//command.Dispose();
command = null;
//pgSqlConnection.Commit();
/*
* ThreadPool.QueueUserWorkItem(callback =>
* {
*
* Console.ForegroundColor = ConsoleColor.Cyan;
* Console.WriteLine(RowsAffected + " rows were affected.");
* Console.WriteLine(
* "S++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
* Console.WriteLine("sql Write:\r\n" + cmd);
* Console.WriteLine(
* "E++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
* Console.ResetColor();
* log.Info("sql Write:\r\n" + cmd);
* });
*/
// Format and display the TimeSpan value.
}
}
catch (PgSqlException ex)
{
if (myTrans != null)
{
myTrans.Rollback();
}
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Modify exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
SiAuto.Main.LogError(cmd);
Console.ResetColor();
//pgSqlConnection.Rollback();
//command.Dispose();
command = null;
}
finally
{
pgSqlConnection.Close();
}
}
/*
* public void LoadDatatable(DataTable dt)
* {
* using (PgSqlConnection pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString))
* {
* using (PgSqlLoader loader = new PgSqlLoader())
* {
* try
* {
* loader.Connection = pgSqlConnection;
* loader.TableName = "custom.WhatsUpDeviceStatus";
* pgSqlConnection.Open();
* loader.Open();
* //loader.CreateColumns();
* loader.LoadTable(dt);
* }
* catch (Exception e)
* {
*
* Console.WriteLine("error:" + e.ToString());
* SiAuto.Main.LogException(e);
* }
* finally
* {
* loader.Close();
* pgSqlConnection.Close();
* }
* }
* }
* }
*/
/*
* public void SqlScriptCmd(string script)
* {
* using (PgSqlConnection pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString))
* {
* try
* {
* PgSqlScript pgscScript = new PgSqlScript(script, pgSqlConnection);
* pgscScript.Progress += pgscScript_Progress;
* pgscScript.Error += pgscScript_Error;
* pgSqlConnection.Open();
* pgscScript.Execute();
* }
* catch (Exception e)
* {
* Console.WriteLine("error:" + e.ToString());
* SiAuto.Main.LogException(e);
* }
* finally
* {
* pgSqlConnection.Close();
* }
* }
* }
*/
/*
* void pgscScript_Error(object sender, Devart.Common.ScriptErrorEventArgs e)
* {
* e.Ignore = true;
* Console.WriteLine(e.Text);
* Console.WriteLine(" Failed.");
* SiAuto.Main.LogError(e.Text);
* }
*
* void pgscScript_Progress(object sender, Devart.Common.ScriptProgressEventArgs e)
* {
* Console.WriteLine(e.Text);
* SiAuto.Main.LogText("SqlScript",e.Text);
* Console.WriteLine(" Successfully executed.");
* }
*/
//For SELECT statements
public DataTable get_DataTable(string cmd)
{
PgSqlCommand command = null;
using (DataTable datatable = new DataTable())
using (var pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString))
{
try
{
//if (pgSqlConnection != null && IsConnected)
//{
//pgSqlConnection.Open();
//DataTable datatable = new DataTable();
command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//command.CommandTimeout = 30;
//Console.WriteLine("Starting asynchronous retrieval of data...");
PgSqlDataReader myReader;
//IAsyncResult cres = command.BeginExecuteReader();
//Console.Write("In progress...");
//while (!cres.IsCompleted)
{
//Console.Write(".");
//Perform here any operation you need
}
//if (cres.IsCompleted)
//Console.WriteLine("Completed.");
//else
//Console.WriteLine("Have to wait for operation to complete...");
//PgSqlDataReader myReader = command.EndExecuteReader(cres);
//PgSqlDataReader myReader = command.ExecuteReader();
//try
//{
lock (accessLock)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
//IAsyncResult cres = command.BeginExecuteReader();
//myReader = command.EndExecuteReader(cres);
//lock (accessLock)
pgSqlConnection.Open();
myReader = command.ExecuteReader();
//stopWatch.Stop();
// Get the elapsed time as a TimeSpan value.
TimeSpan ts = stopWatch.Elapsed;
// Format and display the TimeSpan value.
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
SiAuto.Main.AddCheckpoint(Level.Debug, "sql query1 take time:" + elapsedTime, cmd);
// printing the column names
stopWatch.Reset();
stopWatch.Start();
for (int i = 0; i < myReader.FieldCount; i++)
{
//Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
//stopWatch.Stop();
ts = stopWatch.Elapsed;
elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
SiAuto.Main.AddCheckpoint(Level.Debug, "sql query2 take time:" + elapsedTime, cmd);
//Console.Write(Environment.NewLine);
stopWatch.Reset();
stopWatch.Start();
while (myReader.Read())
{
DataRow dr = datatable.NewRow();
for (int i = 0; i < myReader.FieldCount; i++)
{
//Console.Write(myReader.GetString(i) + "\t");
dr[i] = myReader.GetString(i);
}
datatable.Rows.Add(dr);
//Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
myReader.Close();
pgSqlConnection.Close();
stopWatch.Stop();
ts = stopWatch.Elapsed;
elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
SiAuto.Main.AddCheckpoint(Level.Debug, "sql query3 take time:" + elapsedTime, cmd);
//myReader.Dispose();
}
//}
//finally
//{
//}
/*
* foreach (DataRow row in datatable.Rows) // Loop over the rows.
* {
* Console.WriteLine("--- Row ---"); // Print separator.
* foreach (var item in row.ItemArray) // Loop over the items.
* {
* Console.Write("Item: "); // Print label.
* Console.WriteLine(item); // Invokes ToString abstract method.
* }
* }
*/
Stopwatch stopWatch2 = new Stopwatch();
stopWatch2.Start();
//if (command != null)
//command.Dispose();
command = null;
using (DataTable returnTable = datatable.Copy())
{
stopWatch2.Stop();
SiAuto.Main.AddCheckpoint(Level.Debug, "sql query4 take time(ms):" + stopWatch2.ElapsedMilliseconds, cmd);
return(returnTable);
}
//DataTable returnTable = datatable.Copy();
//}
//else
//{
//return null;
//}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("GetDataTable exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
Console.ResetColor();
SiAuto.Main.LogError(cmd);
//if (command != null)
//command.Dispose();
command = null;
return(null);
}
}
}
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();
}
