public IngresoDeProductos(PgSqlConnection p_conexion)
{
InitializeComponent();
pro_conexion = p_conexion;
CargarGrupoPruductos();
CargarDetalleProductos();
}
private void ValidarConexion()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
try
{
splashScreenManager2.ShowWaitForm();
Pro_Conexion.Open();
splashScreenManager2.CloseWaitForm();
}
catch (Exception Exc)
{
splashScreenManager2.ShowWaitForm();
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"ValidarConexion()");
v_depurador = null;
PgSqlConnection v_conexion = new PgSqlConnection(Pro_Conexion.ConnectionString);
v_conexion.Password = Pro_Conexion.Password;
Pro_Conexion = v_conexion;
Pro_Conexion.Open();
v_conexion = null;
splashScreenManager2.CloseWaitForm();
}
}
}
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);
}
}
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 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 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 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));
}
}
public static bool ActualizarBautismoEspiritu(PgSqlConnection pConexion, int pCodigoColaborador, bool pBautismoEspiritu)
{
bool v_resultado = false;
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_actualizar_bautismo_espiritu(:pCodigoColaborador,
:pBautismoEspiritu);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
pgComando.Parameters.Add("pCodigoColaborador", PgSqlType.Int).Value = pCodigoColaborador;
pgComando.Parameters.Add("pBautismoEspiritu", PgSqlType.Boolean).Value = pBautismoEspiritu;
;
try
{
v_resultado = Convert.ToBoolean(pgComando.ExecuteScalar());
if (!v_resultado)
{
MessageBox.Show("No se guardaron los cambios en indicador Bautismo Espiritú. Por favor intente nuevamente.");
return(v_resultado);
}
return(v_resultado);
}
catch (Exception Exc)
{
return(v_resultado);
}
}
public static bool ActualizarOtrosEquiposPrivilegio(PgSqlConnection pConexion, int pCodigoColaborador, string pOtrosEquipos)
{
bool v_resultado = false;
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_actualizar_otros_equipos_privilegio(:pCodigoColaborador,
:pOtrosEquipos);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
pgComando.Parameters.Add("pCodigoColaborador", PgSqlType.Int).Value = pCodigoColaborador;
pgComando.Parameters.Add("pOtrosEquipos", PgSqlType.VarChar).Value = pOtrosEquipos
;
try
{
v_resultado = Convert.ToBoolean(pgComando.ExecuteScalar());
if (!v_resultado)
{
MessageBox.Show("No se guardaron los cambios. Por favor intente nuevamente.");
return(v_resultado);
}
return(v_resultado);
}
catch (Exception Exc)
{
return(v_resultado);
}
}
public void ConstruirControl(PgSqlConnection pConexion, string pUsuario)
{
LimpiarCajasTexto();
Pro_Conexion = pConexion;
Pro_Usuario = pUsuario;
txtCompra.Focus();
}
public void ConstruirControl(PgSqlConnection pConexion,
Usuario pUsuario,
int pID_Area_Atencion)
{
Pro_Conexion = pConexion;
Pro_Usuario = pUsuario;
Pro_ID_Area_Atencion = pID_Area_Atencion;
NavegacionAsistencias.SelectedPage = PageListaAsistencia;
ObtenerActividad();
if (Pro_ID_Actividad == 0)
{
NavegacionAsistencias.SelectedPage = PageMensaje;
}
else
{
NavegacionAsistencias.SelectedPage = PageListaAsistencia;
ctlListaAsistencia1.ConstruirControl(Pro_Conexion,
Pro_Usuario,
Pro_ID_Actividad,
Pro_ID_Area_Atencion);
}
}
public frmOperaciones(PgSqlConnection pConexion,
int pID_Agencia_Servicio,
int pID_Cliente_Servicio,
int pID_Nivel_Acceso,
string pNombre_Empleado,
string pUsuario,
string pDescripcionNivelAcceso,
string pCargo,
string pCodigoEmpleado,
string pNombreAgencia)
{
InitializeComponent();
ctlOperacional1.OnCerrarSesion += ctlOperacional1_OnCerrarSesion;
ctlOperacional1.ConstruirControl(pConexion,
pID_Agencia_Servicio,
pID_Cliente_Servicio,
pID_Nivel_Acceso,
pDescripcionNivelAcceso,
pCargo,
pNombre_Empleado,
pUsuario,
pCodigoEmpleado,
pNombreAgencia
);
}
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 int ObtenerNumeroMes(PgSqlConnection pConexion)
{
string sentencia = "SELECT * FROM configuracion.ft_proc_obtener_numero_mes();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
return((int)pgComando.ExecuteScalar());
}
static void Insert(PgSqlConnection connection)
{
//insert
PgSqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO public._gps_log (_id,_uid,_status,_time,_validity,_lat,_lon,_speed,_course,_distance,_judgement,_or_lon,_or_lat,_satellites,_temperature,_voltage) VALUES (1681185,'_uid',2,'2012-10-01 18:32:50.553+08','a',25.062923,121.522705,0,0,0,0,0,0,3,35,100)";
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
//async
IAsyncResult cres = command.BeginExecuteNonQuery(null, null);
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 = cmd.ExecuteNonQuery();
* Console.WriteLine(aff + " rows were affected.");
*/
}
public static bool ActualizarEstatusDoctrinal(PgSqlConnection pConexion, int pCodigoColaborador, int pEstatusDoctrinal)
{
bool v_resultado = false;
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_actualizar_estatus_doctrinal(:pCodigoColaborador,
:pEstatusDoctrinal);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
pgComando.Parameters.Add("pCodigoColaborador", PgSqlType.Int).Value = pCodigoColaborador;
pgComando.Parameters.Add("pEstatusDoctrinal", PgSqlType.Int).Value = pEstatusDoctrinal;
try
{
v_resultado = Convert.ToBoolean(pgComando.ExecuteScalar());
if (!v_resultado)
{
MessageBox.Show("No se guardaron los cambios en el Estatus Doctrinal. Por favor intente nuevamente.");
return(v_resultado);
}
return(v_resultado);
}
catch (Exception Exc)
{
return(v_resultado);
}
}
static void Count(PgSqlConnection connection)
{
DataTable datatable = new DataTable();
PgSqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT COUNT
(_uid)
FROM public._gps_log";
Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
Console.Write(Environment.NewLine);
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");
}
}
finally
{
myReader.Close();
}
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.
}
}
Console.WriteLine("############");
Console.WriteLine(datatable.Rows[0].ItemArray[0].ToString());
}
public static bool ActualizarEdadAreaAtencion(PgSqlConnection pConexion, int pCodigoColaborador, int pID_Cargo, string pUsuario, int pID_AreaAtencion)
{
bool v_resultado = false;
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_actualiza_edad_area_atencion (
:p_id_colaborador,
:p_id_area_atencion,
:p_id_cargo,
:p_usuario
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
pgComando.Parameters.Add("p_id_colaborador", PgSqlType.Int).Value = pCodigoColaborador;
pgComando.Parameters.Add("p_id_area_atencion", PgSqlType.Int).Value = pID_AreaAtencion;
pgComando.Parameters.Add("p_id_cargo", PgSqlType.Int).Value = pID_Cargo;
pgComando.Parameters.Add("p_usuario", PgSqlType.VarChar).Value = pUsuario;
try
{
v_resultado = Convert.ToBoolean(pgComando.ExecuteScalar());
if (!v_resultado)
{
MessageBox.Show("No se guardaron los cambios. Por favor intente nuevamente.");
return(v_resultado);
}
return(v_resultado);
}
catch (Exception Exc)
{
return(v_resultado);
}
}
public void UseDataAdapter(PgSqlConnection pgConnection)
{
PgSqlDataAdapter myAdapter = new PgSqlDataAdapter("SELECT DeptNo, DName FROM Test.Dept", pgConnection);
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet myDataSet = new DataSet();
myAdapter.Fill(myDataSet, "Departments");
object[] rowVals = new object[2];
rowVals[0] = 40;
rowVals[1] = "Operations";
myDataSet.Tables["Departments"].Rows.Add(rowVals);
myAdapter.InsertCommand = new PgSqlCommand("INSERT INTO Test.Dept (DeptNo, DName) " +
"VALUES (:DeptNo, :DName)", pgConnection);
myAdapter.InsertCommand.Parameters.Add("DeptNo", PgSqlType.Int, 0, "DeptNo");
myAdapter.InsertCommand.Parameters.Add("DName", PgSqlType.VarChar, 15, "DName");
myAdapter.Update(myDataSet, "Departments");
//Get all data from all tables within the dataset
foreach (DataTable myTable in myDataSet.Tables)
{
foreach (DataRow myRow in myTable.Rows)
{
foreach (DataColumn myColumn in myTable.Columns)
{
Console.Write(myRow[myColumn] + "\t");
}
Console.WriteLine();
}
Console.WriteLine();
}
}
public static bool CrearUsuarioColaborador(PgSqlConnection pConexion, int pCodigoColaborador, int pRolUsuario, string pUsuario)
{
bool v_resultado = false;
string sentencia = @"SELECT * FROM arca_tesoros.ft_proc_creacion_usuarios (
:p_id_colaborador,
:p_usuario,
:p_rol_usuario
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
pgComando.Parameters.Add("p_id_colaborador", PgSqlType.Int).Value = pCodigoColaborador;
pgComando.Parameters.Add("p_usuario", PgSqlType.VarChar).Value = pUsuario;
pgComando.Parameters.Add("p_rol_usuario", PgSqlType.Int).Value = pRolUsuario;
try
{
v_resultado = Convert.ToBoolean(pgComando.ExecuteScalar());
if (!v_resultado)
{
MessageBox.Show("No se guardaron los cambios. Por favor intente nuevamente.");
return(v_resultado);
}
MessageBox.Show("Usuario creado con éxito");
return(v_resultado);
}
catch (Exception Exc)
{
return(v_resultado);
}
}
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)
{
}
}
public static bool ActualizaCorreoElectronicoColaborador(PgSqlConnection pConexion, int pCodigoColaborador, string pCorreoElectronico)
{
bool v_resultado = false;
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_actualiza_correo_electronico (
:p_id_colaborador,
:p_correo_electronico
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
pgComando.Parameters.Add("p_id_colaborador", PgSqlType.Int).Value = pCodigoColaborador;
pgComando.Parameters.Add("p_correo_electronico", PgSqlType.VarChar).Value = pCorreoElectronico;
try
{
v_resultado = Convert.ToBoolean(pgComando.ExecuteScalar());
if (!v_resultado)
{
MessageBox.Show("No se guardaron los cambios. Por favor intente nuevamente.");
return(v_resultado);
}
return(v_resultado);
}
catch (Exception Exc)
{
return(v_resultado);
}
}
//=====================================
// METODOS PostgreSQL
//=====================================
public DataTable BD_PostgreSQL(string EndServidor, string NomeBD, string NomeTabela, string Login, string Senha,
string Porta, string ClauSelect, string ClauWhere)
{
DataSet ds = new DataSet();
try
{
string strConexao = "Server=" + EndServidor + ";Port=" + Porta + ";Database=" + NomeBD + ";User Id=" + Login +
";Password="******"SELECT " + ClauSelect + " FROM " + NomeTabela + " WHERE " +
ClauWhere + ";", Postgreconn);
Postgreda.Fill(ds);
}
catch (PgSqlException Ex)
{
MessageBox.Show("Não foi possível carregar o banco de dados PostgreSQL.\r\n" + Ex.Message + "\r\n" +
Ex.DetailMessage + "\r\n" + Ex.ErrorCode.ToString());
ds.Tables.Add();
}
catch (Exception Ex)
{
MessageBox.Show("Não foi possível carregar o banco de dados PostgreSQL.\r\n" + Ex.Message);
ds.Tables.Add();
}
return(ds.Tables[0]);
}
public static bool ActualizaNecesitaTransporteColaborador(PgSqlConnection pConexion, int pCodigoColaborador, bool pNecesitaTransporte)
{
bool v_resultado = false;
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_actualizar_necesita_transporte (
:p_id_colaborador ,
:p_necesita_transporte
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
pgComando.Parameters.Add("p_id_colaborador", PgSqlType.Int).Value = pCodigoColaborador;
pgComando.Parameters.Add("p_necesita_transporte", PgSqlType.Boolean).Value = pNecesitaTransporte;
try
{
v_resultado = Convert.ToBoolean(pgComando.ExecuteScalar());
if (!v_resultado)
{
MessageBox.Show("No se guardaron los cambios. Por favor intente nuevamente.");
return(v_resultado);
}
return(v_resultado);
}
catch (Exception Exc)
{
return(v_resultado);
}
}
public void ConstruirControl(PgSqlConnection pConexion,
int pID_Colaborador,
Usuario pUsuario,
int pID_Actividad,
int pID_AreaAtencion,
int pID_Colaborador_Actividad = 0)
{
Pro_Conexion = pConexion;
Pro_ID_Colaborador = pID_Colaborador;
Pro_ID_Colaborar_Actividad = pID_Colaborador_Actividad;
Pro_Usuario = pUsuario;
Pro_ID_Actividad = pID_Actividad;
Pro_ID_AreaAtencion = pID_AreaAtencion;
Pro_ExtenderMaestros = false;
Pro_NoNecesitaCubrir = false;
Pro_CubrirConServidor = false;
ctlSeleccionMaestros_Ayudas1.ConstruirControl(Pro_Conexion,
Pro_Usuario.Pro_Usuario,
DateTime.Now.ToString(),
Pro_ID_Actividad,
Pro_ID_AreaAtencion,
false);
}
public static bool ActualizarFechaReconciliacion(PgSqlConnection pConexion, int pCodigoColaborador, DateTime pFechaReconciliacion)
{
bool v_resultado = false;
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_actualizar_fecha_reconciliacion(:pCodigoColaborador,
:pFechaReconciliacion);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
pgComando.Parameters.Add("pCodigoColaborador", PgSqlType.Int).Value = pCodigoColaborador;
pgComando.Parameters.Add("pFechaReconciliacion", PgSqlType.TimeStamp).Value = pFechaReconciliacion;
try
{
v_resultado = Convert.ToBoolean(pgComando.ExecuteScalar());
if (!v_resultado)
{
MessageBox.Show("No se guardaron los cambios en la Fecha de Reconciliación. Por favor intente nuevamente.");
return(v_resultado);
}
return(v_resultado);
}
catch (Exception Exc)
{
return(v_resultado);
}
}
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 ConstruirControl(PgSqlConnection pConexion,
int pID_AgenciaServicio,
int pID_ClienteServicio,
int pID_NivelAccesoEmpleado,
string pDescripcionNivelAccesoEmpleado,
string pCargoEmpleado,
string pNombreEmpleado,
string pUsuarioEmpleado,
string pCodigoEmpleado,
string pNombreAgencia
)
{
Pro_Conexion = pConexion;
Pro_ID_AgenciaServicio = pID_AgenciaServicio;
Pro_ID_ClienteServicio = pID_ClienteServicio;
Pro_ID_NivelAccesoEmpleado = pID_NivelAccesoEmpleado;
Pro_DescripcionNivelAcceso = Pro_DescripcionNivelAcceso;
Pro_Cargo = pCargoEmpleado;
Pro_NombreEmpleado = pNombreEmpleado;
Pro_Usuario = pUsuarioEmpleado;
Pro_CodigoEmpleado = pCodigoEmpleado;
Pro_Esta_En_Atencion = false;
Pro_Esta_En_ModoReducido = false;
lblNombreUsuario.Text = Pro_NombreEmpleado;
lblNumeroTicket.Text = "";
lblAgencia.Text = pNombreAgencia;
ctlListaTicketsEspera1.ConstruirControl(Pro_Conexion,
Pro_ID_AgenciaServicio,
Pro_ID_ClienteServicio,
Pro_Usuario);
}
public PGLayer()
{
string cs = "User Id=postgres;Host=localhost;Database=mydb;Password =123;Persist Security Info=True;Initial Schema=public";
connPG = new PgSqlConnection(cs);
cmdPG = new PgSqlCommand();
connPG.Charset = "UTF8";
}
public void ConstruirControl(PgSqlConnection pConexion)
{
splashScreenManager1.ShowWaitForm();
Pro_Conexion = pConexion;
Pro_Dia = Utilidades.ObtenerFechaServidor(Pro_Conexion);
ctlCumpleanierosDia1.ConstruirControl(Pro_Conexion, Pro_Dia);
ctlProximosCumpleanieros1.ConstruirControl(Pro_Conexion);
splashScreenManager1.CloseWaitForm();
}
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.");
}
PgSqlConnection getConnection()
{
PgSqlConnection con = new PgSqlConnection();
con.UserId = Global.Instance.config.User;
con.Password = Global.Instance.config.Password;
con.Host = Global.Instance.config.Host;
con.Port = Global.Instance.config.Port;
con.Database = Global.Instance.config.Database;
con.Schema = Global.Instance.config.Schema;
con.Charset = Global.Instance.config.Charset;
con.Unicode = Global.Instance.config.Unicode;
con.ConnectionTimeout = Global.Instance.config.ConnectionTimeout;
//con.ConnectionString = String.Format("HOST={0};PORT={1};PROTOCOL=3;DATABASE={2};USER ID={3};POOLING=True;Connection Lifetime=0;Min Pool Size=1;Max Pool Size=1024;INTEGRATED SECURITY=False;Password={4};Charset=UTF8",
// Global.Instance.config.Host, Global.Instance.config.Port, Global.Instance.config.Database, Global.Instance.config.User, Global.Instance.config.Password);
return con;
}
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();
}
static void Test(PgSqlConnection con, string sql,
CommandType cmdType, CommandBehavior behavior,
string testDesc)
{
PgSqlCommand cmd = null;
PgSqlDataReader rdr = null;
int c;
int results = 0;
Console.WriteLine("Test: " + testDesc);
Console.WriteLine("[BEGIN SQL]");
Console.WriteLine(sql);
Console.WriteLine("[END SQL]");
cmd = new PgSqlCommand(sql, con);
cmd.CommandType = cmdType;
Console.WriteLine("ExecuteReader...");
rdr = cmd.ExecuteReader(behavior);
if(rdr == null) {
Console.WriteLine("IDataReader has a Null Reference.");
}
else {
do {
// get the DataTable that holds
// the schema
DataTable dt = rdr.GetSchemaTable();
if(rdr.RecordsAffected != -1) {
// Results for
// SQL INSERT, UPDATE, DELETE Commands
// have RecordsAffected >= 0
Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
}
else if (dt == null)
Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
else {
// Results for
// SQL not INSERT, UPDATE, nor DELETE
// have RecordsAffected = -1
Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);
// Results for a SQL Command (CREATE TABLE, SET, etc)
// will have a null reference returned from GetSchemaTable()
//
// Results for a SQL SELECT Query
// will have a DataTable returned from GetSchemaTable()
results++;
Console.WriteLine("Result Set " + results + "...");
// 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();
}
int nRows = 0;
string output, metadataValue, dataValue;
// Read and display the rows
Console.WriteLine("Gonna do a Read() now...");
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);
rdr.Close();
}
}
