/// <summary> /// Repeatedly attempts to rollback, to support timeout-triggered rollbacks that occur while the connection is busy. /// </summary> void RollbackLocal() { Debug.Assert(_connector != null, "No connector"); Debug.Assert(_localTx != null, "No local transaction"); var attempt = 0; while (true) { try { _localTx.Rollback(); return; } catch (NpgsqlOperationInProgressException) { // This really shouldn't be necessary, but just in case if (attempt++ == MaximumRollbackAttempts) { throw new Exception($"Could not roll back after {MaximumRollbackAttempts} attempts, aborting. Transaction is in an unknown state."); } Log.Logger.LogWarning(NpgsqlEventId.ConnectionInUseDuringRollback, "[{ConnectorId}] Connection in use while trying to rollback, will cancel and retry (localid={TransactionId}", _connector.Id, _txId); _connector.CancelRequest(); // Cancellations are asynchronous, give it some time Thread.Sleep(500); } } }
void RollbackLocal() { Log.Debug($"Single-phase transaction rollback (localid={_txId})", _connector.Id); var attempt = 0; while (true) { try { _localTx.Rollback(); return; } catch (NpgsqlOperationInProgressException) { // Repeatedly attempts to rollback, to support timeout-triggered rollbacks that occur // while the connection is busy. // This really shouldn't be necessary, but just in case if (attempt++ == MaximumRollbackAttempts) { throw new Exception($"Could not roll back after {MaximumRollbackAttempts} attempts, aborting. Transaction is in an unknown state."); } Log.Warn($"Connection in use while trying to rollback, will cancel and retry (localid={_txId}", _connector.Id); _connector.CancelRequest(); // Cancellations are asynchronous, give it some time Thread.Sleep(500); } } }
public void Rollback(SinglePhaseEnlistment singlePhaseEnlistment) { NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Rollback"); // try to rollback the transaction with either the // ADO.NET transaction or the callbacks that managed the // two phase commit transaction. if (_npgsqlTx != null) { _npgsqlTx.Rollback(); _npgsqlTx.Dispose(); _npgsqlTx = null; singlePhaseEnlistment.Aborted(); } else if (_callbacks != null) { if (_rm != null) { _rm.RollbackWork(_callbacks.GetName()); singlePhaseEnlistment.Aborted(); } else { _callbacks.RollbackTransaction(); singlePhaseEnlistment.Aborted(); } _callbacks = null; } _inTransaction = false; }
public void Rollback(SinglePhaseEnlistment singlePhaseEnlistment) { Log.Debug("Rollback"); // try to rollback the transaction with either the // ADO.NET transaction or the callbacks that managed the // two phase commit transaction. if (_npgsqlTx != null) { _npgsqlTx.Rollback(); _npgsqlTx.Dispose(); _npgsqlTx = null; singlePhaseEnlistment.Aborted(); _connection.PromotableLocalTransactionEnded(); } else if (_callbacks != null) { if (_rm != null) { _rm.RollbackWork(_callbacks.GetName()); singlePhaseEnlistment.Aborted(); } else { _callbacks.RollbackTransaction(); singlePhaseEnlistment.Aborted(); } _callbacks = null; } _inTransaction = false; }
/// <summary> /// Executes the command. /// </summary> /// <param name="dbCommand">The current sql command.</param> /// <param name="commandText">The command text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>-1 if command execution failed.</returns> public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText, CommandType commandType, string connectionString, params DbParameter[] values) { // Initial connection objects. dbCommand = null; Int32 returnValue = -1; PostgreSqlClient.NpgsqlConnection pgConnection = null; PostgreSqlClient.NpgsqlTransaction pgTransaction = null; try { // Create a new connection. using (pgConnection = new PostgreSqlClient.NpgsqlConnection(connectionString)) { // Open the connection. pgConnection.Open(); // Start a new transaction. pgTransaction = pgConnection.BeginTransaction(); // Create the command and assign any parameters. dbCommand = new PostgreSqlClient.NpgsqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.PostgreSqlDataType, commandText), pgConnection); dbCommand.CommandType = commandType; dbCommand.Transaction = pgTransaction; if (values != null) { foreach (PostgreSqlClient.NpgsqlParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Execute the command. returnValue = dbCommand.ExecuteNonQuery(); // Commit the transaction. pgTransaction.Commit(); // Close the database connection. pgConnection.Close(); } // Return true. return(returnValue); } catch (Exception ex) { try { // Attempt to roll back the transaction. if (pgTransaction != null) { pgTransaction.Rollback(); } } catch { } // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (pgConnection != null) { pgConnection.Close(); } } }
public int ExecuteNonqueryTr(string sql, out NpgsqlTransaction transaction, params NpgsqlParameter[] parameters) { transaction = this.Connection.BeginTransaction(); try { using(NpgsqlCommand cmd = this.Connection.CreateCommand()) { cmd.CommandText = sql; foreach(NpgsqlParameter param in parameters) cmd.Parameters.Add(param); return cmd.ExecuteNonQuery(); } } catch { transaction.Rollback(); throw; } }
/// <summary> /// Lấy dữ liệu từ câu lệnh chứa trong thuộc tính Command /// </summary> /// <returns></returns> private DataSet getData() //Get data with SQLcommand { Connect(); trans = conn.BeginTransaction(); DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = Command; cmd.CommandType = Commandtype; cmd.Transaction = trans; NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd); try { adapter.Fill(ds); trans.Commit(); Disconnect(); return ds; } catch (NpgsqlException ex) { Error = ex.Message; trans.Rollback(); } return null; }
/// <summary> /// thực thi SQL, thường dùng cho các dòng lệnh MAX,AVG,COUNT,..... /// </summary> /// <returns>Object</returns> private object executeScalar()// Execute a command return only 1 value { Connect(); trans = conn.BeginTransaction(); cmd.CommandText = Command; cmd.CommandType = Commandtype; cmd.Transaction = trans; cmd.Connection = conn; object obj = null; try { obj = cmd.ExecuteScalar(); trans.Commit(); } catch (NpgsqlException ex) { trans.Rollback(); if (ex.Code == "23505") { if(ex.BaseMessage.Contains("quyet_dinh")) throw new Exception("Mã quyết định đã tồn tại, xin vui lòng kiểm tra lại."); else if(ex.BaseMessage.Contains("ma_nv")) throw new Exception("Mã nhân viên đã tồn tại, xin vui lòng kiểm tra lại."); } throw new Exception(ex.Message); } Disconnect(); return obj; }
/// <summary> /// Thực Thi các câu lệnh SQL (thường là UPDATE) /// </summary> /// <returns>giá trị các dòng có ảnh hưởng</returns> private int executeNonQuery()// Execute a command without return { Connect(); trans = conn.BeginTransaction(); cmd.CommandText = Command; cmd.CommandType = Commandtype; cmd.Connection = conn; cmd.Transaction = trans; int rowEffect = 0; try { rowEffect = cmd.ExecuteNonQuery(); trans.Commit(); } catch (NpgsqlException ex) { Error = ex.Message; trans.Rollback(); } Disconnect(); return rowEffect; }
/// <summary> /// Lấy Dữ Liệu Từng Dòng /// </summary> /// <returns>IDataReader</returns> private IDataReader getDataReader() // Đọc dữ liệu dạng Datareader { Connect(); trans = conn.BeginTransaction(); NpgsqlDataReader datard = null; try { cmd.CommandText = Command; cmd.CommandType = Commandtype; cmd.Transaction = trans; datard = cmd.ExecuteReader(CommandBehavior.CloseConnection); trans.Commit(); } catch (NpgsqlException ex) { Error = ex.Message; trans.Rollback(); } // Disconnect(); return datard; }