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); } }