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();
}
//For UPDATE, INSERT, and DELETE statements
public bool modify(string cmd)
{
try
{
if (pgSqlConnection != null && IsConnected)
{
//insert
PgSqlCommand command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
pgSqlConnection.BeginTransaction();
//async
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 = cmd.ExecuteNonQuery();
* Console.WriteLine(aff + " rows were affected.");
*
*/
pgSqlConnection.Commit();
return(true);
}
else
{
return(false);
}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Modify exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
log.Error("Modify exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd);
Console.ResetColor();
pgSqlConnection.Rollback();
return(false);
}
}
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();
}
}
//For UPDATE, INSERT, and DELETE statements
public bool modify(string cmd)
{
Stopwatch stopWatch = new Stopwatch();
PgSqlCommand command = null;
stopWatch.Start();
try
{
if (pgSqlConnection != null && IsConnected)
{
//insert
command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
pgSqlConnection.BeginTransaction();
//async
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 = cmd.ExecuteNonQuery();
* Console.WriteLine(aff + " rows were affected.");
*
*/
pgSqlConnection.Commit();
ThreadPool.QueueUserWorkItem(callback =>
{
Console.ForegroundColor = ConsoleColor.Cyan;
Console.WriteLine(
"S++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
Console.WriteLine("sql Write:\r\n" + cmd);
Console.WriteLine(
"E++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
Console.ResetColor();
});
if (command != null)
{
command.Dispose();
}
command = null;
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 modify take time:" + elapsedTime, cmd);
return(true);
}
else
{
return(false);
}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Modify exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
log.Error("Modify exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd);
Console.ResetColor();
pgSqlConnection.Rollback();
if (command != null)
{
command.Dispose();
}
command = null;
return(false);
}
}
