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