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();
}
private void GenerarTicket()
{
ValidarConexion();
splashScreenManager1.ShowWaitForm();
PgSqlTransaction pgTrans = Pro_Conexion.BeginTransaction();
string sentencia = @"SELECT * FROM configuracion.sp_proc_genera_correlativos_ticket (
:p_id_agencia_servicio,
:p_id_cliente_servicio,
:p_id_tipo_ticket_servicio,
:p_id_operacion_servicio,
:p_direccion_ip
);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio;
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio;
pgComando.Parameters.Add("p_id_tipo_ticket_servicio", PgSqlType.Int).Value = Pro_ID_Tipo_Ticket_Servicio;
pgComando.Parameters.Add("p_id_operacion_servicio", PgSqlType.Int).Value = Pro_ID_Operacion_Servicio;
pgComando.Parameters.Add("p_direccion_ip", PgSqlType.VarChar).Value = Pro_IP_Host;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
Pro_Ticket_Generado = pgDr.GetString("numero_ticket");
}
pgTrans.Commit();
pgDr.Close();
pgComando.Dispose();
sentencia = null;
splashScreenManager1.CloseWaitForm();
}
catch (Exception Exc)
{
splashScreenManager1.CloseWaitForm();
pgTrans.Rollback();
Pro_Ticket_Generado = null;
MessageBox.Show(Exc.Message, "FLUCOL");
}
}
public bool ActualizarEstadoTicket(PgSqlConnection pConexion,
int pEstadoTicket,
int pID_AgenciaServicio,
int pID_ClienteServicio,
string pTicketServicio,
string pUsuario)
{
Pro_Conexion = pConexion;
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM area_servicio.ft_mant_actualizar_estado_ticket (
:p_estado_ticket,
:p_id_agencia_servicio,
:p_id_cliente_servicio,
:p_ticket_servicio,
:p_usuario
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_estado_ticket", PgSqlType.Int).Value = (int)pEstadoTicket;
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = pID_AgenciaServicio;
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = pID_ClienteServicio;
pgComando.Parameters.Add("p_ticket_servicio", PgSqlType.VarChar).Value = pTicketServicio;
pgComando.Parameters.Add("p_usuario", PgSqlType.VarChar).Value = pUsuario;
PgSqlTransaction pgTrans = Pro_Conexion.BeginTransaction();
try
{
pgComando.ExecuteNonQuery();
pgTrans.Commit();
sentencia = null;
pgComando.Dispose();
return(true);
}
catch (Exception Exc)
{
pgTrans.Rollback();
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
"class Tiempos",
@"ActualizarEstadoTicket(PgSqlConnection pConexion,
int pEstadoTicket,
int pID_AgenciaServicio,
int pID_ClienteServicio,
string pTicketServicio,
string pUsuario)");
v_depurador = null;
MessageBox.Show(Exc.Message, "FLUCOL");
return(false);
}
}
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();
}
}
private void GuardarSolicitud()
{
if (!splashScreenManager1.IsSplashFormVisible)
{
splashScreenManager1.ShowWaitForm();
}
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_insertar_solicitud_coordinador_edad (
:p_tipo_solicitud,
:p_fecha_ejecucion,
:p_observaciones,
:p_id_colaborador_solicitante
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_tipo_solicitud", PgSqlType.Int).Value = glTipoSolicitud.EditValue;
pgComando.Parameters.Add("p_fecha_ejecucion", PgSqlType.Date).Value = dateFechaSolicitud.EditValue;
pgComando.Parameters.Add("p_observaciones", PgSqlType.VarChar).Value = txtObservacionesSolicitud.Text;
pgComando.Parameters.Add("p_id_colaborador_solicitante", PgSqlType.Int).Value = Pro_ID_Colaborador;
PgSqlTransaction pgTrans = Pro_Conexion.BeginTransaction();
try
{
pgComando.ExecuteNonQuery();
pgTrans.Commit();
sentencia = null;
pgComando.Dispose();
if (splashScreenManager1.IsSplashFormVisible)
{
splashScreenManager1.CloseWaitForm();
}
LimpiarCajasTexto();
Utilidades.MostrarDialogo(FindForm(), "Arca de los Tesoros", "¡La solicitud fue enviada al coordinador de día!", Utilidades.BotonesDialogo.Ok);
popupIngresarSolicitudes.HidePopup();
CargarMisSolicitudes();
}
catch (Exception Exc)
{
if (splashScreenManager1.IsSplashFormVisible)
{
splashScreenManager1.CloseWaitForm();
}
pgTrans.Rollback();
sentencia = null;
pgComando.Dispose();
Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "GuardarSolicitud");
}
}
private void btExecute_Click(object sender, System.EventArgs e)
{
const int len = 10;
int recCount = 0;
PgSqlCommand cursorCmd = new PgSqlCommand("fetch all in \"dept\"", pgSqlCommand.Connection);
// need to start transaction, because cursor is accessible only in transaction
PgSqlTransaction tr = pgSqlCommand.Connection.BeginTransaction();
try {
// executes stored procedure that opens cursor
pgSqlCommand.ExecuteScalar();
// fetch data from named cursor
PgSqlDataReader dataReader = cursorCmd.ExecuteReader();
if (dataReader.FieldCount > 0)
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
tbResult.AppendText(dataReader.GetName(i).PadRight(len).Substring(0, len) + " ");
}
tbResult.AppendText("\r\n");
for (int i = 0; i < dataReader.FieldCount; i++)
{
tbResult.AppendText(String.Empty.PadRight(len, '-').Substring(0, len) + " ");
}
tbResult.AppendText("\r\n");
while (dataReader.Read())
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
tbResult.AppendText(dataReader.GetValue(i).ToString().PadRight(len).Substring(0, len) + " ");
}
tbResult.AppendText("\r\n");
recCount++;
}
tbResult.AppendText("\r\n");
tbResult.AppendText(recCount.ToString() + " rows selected.\r\n");
}
else
{
tbResult.AppendText("Statement executed.\r\n");
}
tbResult.AppendText("\r\n");
dataReader.Close();
// commit transaction
tr.Commit();
}
catch (PgSqlException exception) {
// rollback transaction on error
tr.Rollback();
tbResult.AppendText(exception.Message + "\r\n\r\n");
throw;
}
}